
ALTER TABLE public.sales_orders
  ADD COLUMN IF NOT EXISTS bill_status TEXT NOT NULL DEFAULT 'rough',
  ADD COLUMN IF NOT EXISTS payment_status TEXT NOT NULL DEFAULT 'due',
  ADD COLUMN IF NOT EXISTS approved_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS approved_by UUID;

CREATE TABLE IF NOT EXISTS public.sales_order_status_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  sales_order_id UUID NOT NULL REFERENCES public.sales_orders(id) ON DELETE CASCADE,
  change_type TEXT NOT NULL,
  old_value TEXT,
  new_value TEXT,
  changed_by UUID,
  note TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE public.sales_order_status_log ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "status_log_select_auth" ON public.sales_order_status_log;
CREATE POLICY "status_log_select_auth" ON public.sales_order_status_log
  FOR SELECT TO authenticated USING (true);

DROP POLICY IF EXISTS "status_log_insert_auth" ON public.sales_order_status_log;
CREATE POLICY "status_log_insert_auth" ON public.sales_order_status_log
  FOR INSERT TO authenticated WITH CHECK (true);

CREATE INDEX IF NOT EXISTS idx_sostatus_log_order ON public.sales_order_status_log(sales_order_id);

CREATE OR REPLACE FUNCTION public.sales_orders_status_log_fn()
RETURNS TRIGGER LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_new_pay TEXT;
BEGIN
  -- Auto compute payment status
  IF NEW.paid_amount <= 0 THEN
    v_new_pay := 'due';
  ELSIF NEW.paid_amount >= NEW.total_amount THEN
    v_new_pay := 'paid';
  ELSE
    v_new_pay := 'partial';
  END IF;
  NEW.payment_status := v_new_pay;

  IF TG_OP = 'INSERT' THEN
    INSERT INTO public.sales_order_status_log(sales_order_id, change_type, old_value, new_value, changed_by)
    VALUES (NEW.id, 'created', NULL, NEW.bill_status, NEW.created_by);
    INSERT INTO public.sales_order_status_log(sales_order_id, change_type, old_value, new_value, changed_by)
    VALUES (NEW.id, 'payment_status', NULL, NEW.payment_status, NEW.created_by);
  ELSE
    IF OLD.bill_status IS DISTINCT FROM NEW.bill_status THEN
      INSERT INTO public.sales_order_status_log(sales_order_id, change_type, old_value, new_value, changed_by)
      VALUES (NEW.id, 'bill_status', OLD.bill_status, NEW.bill_status, NEW.approved_by);
    END IF;
    IF OLD.payment_status IS DISTINCT FROM NEW.payment_status THEN
      INSERT INTO public.sales_order_status_log(sales_order_id, change_type, old_value, new_value, changed_by)
      VALUES (NEW.id, 'payment_status', OLD.payment_status, NEW.payment_status, NULL);
    END IF;
    IF OLD.status IS DISTINCT FROM NEW.status THEN
      INSERT INTO public.sales_order_status_log(sales_order_id, change_type, old_value, new_value, changed_by)
      VALUES (NEW.id, 'status', OLD.status, NEW.status, NULL);
    END IF;
  END IF;
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_sales_orders_status_log ON public.sales_orders;
CREATE TRIGGER trg_sales_orders_status_log
  BEFORE INSERT OR UPDATE ON public.sales_orders
  FOR EACH ROW EXECUTE FUNCTION public.sales_orders_status_log_fn();
