DROP TRIGGER IF EXISTS trg_sales_orders_status_log ON public.sales_orders;
DROP TRIGGER IF EXISTS trg_sales_orders_before_write ON public.sales_orders;
DROP TRIGGER IF EXISTS trg_sales_orders_after_write_log ON public.sales_orders;

CREATE OR REPLACE FUNCTION public.sales_orders_before_write_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);

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

  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;
  RETURN NEW;
END;
$$;

CREATE OR REPLACE FUNCTION public.sales_orders_after_write_log_fn()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  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;
$$;

CREATE TRIGGER trg_sales_orders_before_write
  BEFORE INSERT OR UPDATE ON public.sales_orders
  FOR EACH ROW EXECUTE FUNCTION public.sales_orders_before_write_fn();

CREATE TRIGGER trg_sales_orders_after_write_log
  AFTER INSERT OR UPDATE ON public.sales_orders
  FOR EACH ROW EXECUTE FUNCTION public.sales_orders_after_write_log_fn();