
-- Extend sales_orders trigger to also auto-recompute due_amount
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;
  v_paid NUMERIC;
  v_total NUMERIC;
BEGIN
  v_paid := COALESCE(NEW.paid_amount, 0);
  v_total := COALESCE(NEW.total_amount, 0);

  -- Auto compute due
  NEW.due_amount := GREATEST(v_total - v_paid, 0);

  -- Auto compute payment status
  IF v_paid <= 0 THEN
    v_new_pay := 'due';
  ELSIF v_paid >= v_total AND v_total > 0 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;
$$;

-- Customer transactions -> bump sales_orders.paid_amount when a payment is recorded
CREATE OR REPLACE FUNCTION public.apply_payment_to_sales_order_fn()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_order_id UUID;
BEGIN
  IF NEW.type IN ('payment','due_collection','collection','received') AND NEW.reference_id IS NOT NULL AND NEW.reference_id <> '' THEN
    SELECT id INTO v_order_id FROM public.sales_orders
      WHERE order_no = NEW.reference_id OR id::text = NEW.reference_id
      LIMIT 1;
    IF v_order_id IS NOT NULL THEN
      UPDATE public.sales_orders
        SET paid_amount = COALESCE(paid_amount,0) + NEW.amount
        WHERE id = v_order_id;
    END IF;
  END IF;
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_apply_payment_to_sales_order ON public.customer_transactions;
CREATE TRIGGER trg_apply_payment_to_sales_order
  AFTER INSERT ON public.customer_transactions
  FOR EACH ROW EXECUTE FUNCTION public.apply_payment_to_sales_order_fn();

-- Backfill: recalc due_amount + payment_status for existing rows
UPDATE public.sales_orders SET paid_amount = paid_amount;
