-- 1. Add columns to sales table
ALTER TABLE public.sales
  ADD COLUMN IF NOT EXISTS sale_status TEXT NOT NULL DEFAULT 'pending',
  ADD COLUMN IF NOT EXISTS delivery_status TEXT NOT NULL DEFAULT 'pending',
  ADD COLUMN IF NOT EXISTS completed_at TIMESTAMPTZ;

-- Backfill: existing sales are treated as completed
UPDATE public.sales
  SET sale_status = 'completed', delivery_status = 'delivered', completed_at = COALESCE(completed_at, created_at)
  WHERE sale_status = 'pending' AND created_at < now() - interval '1 minute';

CREATE INDEX IF NOT EXISTS idx_sales_sale_status ON public.sales(sale_status);
CREATE INDEX IF NOT EXISTS idx_sales_delivery_status ON public.sales(delivery_status);

-- 2. Create sale_status_logs table
CREATE TABLE IF NOT EXISTS public.sale_status_logs (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  sale_id UUID NOT NULL,
  change_type TEXT NOT NULL DEFAULT 'sale_status',
  old_value TEXT,
  new_value TEXT,
  changed_by UUID,
  note TEXT DEFAULT '',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_sale_status_logs_sale_id ON public.sale_status_logs(sale_id);

ALTER TABLE public.sale_status_logs ENABLE ROW LEVEL SECURITY;

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

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

CREATE POLICY "Admins can update sale status logs"
  ON public.sale_status_logs FOR UPDATE TO authenticated
  USING (public.has_role(auth.uid(), 'admin'::app_role));

CREATE POLICY "Admins can delete sale status logs"
  ON public.sale_status_logs FOR DELETE TO authenticated
  USING (public.has_role(auth.uid(), 'admin'::app_role));

-- 3. Trigger: auto set completed_at + log status changes
CREATE OR REPLACE FUNCTION public.sales_status_log_fn()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    IF NEW.sale_status IN ('delivered','completed') AND NEW.completed_at IS NULL THEN
      NEW.completed_at := now();
    END IF;
  ELSIF TG_OP = 'UPDATE' THEN
    IF NEW.sale_status IN ('delivered','completed') AND NEW.completed_at IS NULL THEN
      NEW.completed_at := now();
    END IF;
  END IF;
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS sales_status_before_write ON public.sales;
CREATE TRIGGER sales_status_before_write
  BEFORE INSERT OR UPDATE ON public.sales
  FOR EACH ROW
  EXECUTE FUNCTION public.sales_status_log_fn();

CREATE OR REPLACE FUNCTION public.sales_status_log_after_fn()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO public.sale_status_logs(sale_id, change_type, old_value, new_value, changed_by)
    VALUES (NEW.id, 'created', NULL, NEW.sale_status, NEW.created_by);
  ELSIF TG_OP = 'UPDATE' THEN
    IF OLD.sale_status IS DISTINCT FROM NEW.sale_status THEN
      INSERT INTO public.sale_status_logs(sale_id, change_type, old_value, new_value, changed_by)
      VALUES (NEW.id, 'sale_status', OLD.sale_status, NEW.sale_status, NEW.edited_by);
    END IF;
    IF OLD.delivery_status IS DISTINCT FROM NEW.delivery_status THEN
      INSERT INTO public.sale_status_logs(sale_id, change_type, old_value, new_value, changed_by)
      VALUES (NEW.id, 'delivery_status', OLD.delivery_status, NEW.delivery_status, NEW.edited_by);
    END IF;
  END IF;
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS sales_status_after_write ON public.sales;
CREATE TRIGGER sales_status_after_write
  AFTER INSERT OR UPDATE ON public.sales
  FOR EACH ROW
  EXECUTE FUNCTION public.sales_status_log_after_fn();
