
-- Sale invoice edit tracking
ALTER TABLE public.sales
  ADD COLUMN IF NOT EXISTS is_edited boolean NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS edited_by uuid,
  ADD COLUMN IF NOT EXISTS edited_at timestamptz,
  ADD COLUMN IF NOT EXISTS edit_count integer NOT NULL DEFAULT 0;

-- Audit log table
CREATE TABLE IF NOT EXISTS public.sale_edit_logs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  sale_id uuid NOT NULL,
  old_data jsonb NOT NULL DEFAULT '{}'::jsonb,
  new_data jsonb NOT NULL DEFAULT '{}'::jsonb,
  changed_fields text[] NOT NULL DEFAULT '{}',
  edited_by uuid,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_sale_edit_logs_sale_id ON public.sale_edit_logs(sale_id);
CREATE INDEX IF NOT EXISTS idx_sale_edit_logs_created_at ON public.sale_edit_logs(created_at DESC);

ALTER TABLE public.sale_edit_logs ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Staff can view sale edit logs"
  ON public.sale_edit_logs FOR SELECT TO authenticated
  USING (public.is_staff(auth.uid()));

CREATE POLICY "Staff can insert sale edit logs"
  ON public.sale_edit_logs FOR INSERT TO authenticated
  WITH CHECK (public.is_staff(auth.uid()));
