
ALTER TABLE public.sales_orders
  ADD COLUMN IF NOT EXISTS sale_status text NOT NULL DEFAULT 'draft';

CREATE INDEX IF NOT EXISTS idx_sales_orders_status ON public.sales_orders(status);
CREATE INDEX IF NOT EXISTS idx_sales_orders_sale_status ON public.sales_orders(sale_status);

CREATE OR REPLACE FUNCTION public.sales_orders_validate_status_fn()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $fn$
DECLARE allowed BOOLEAN := false;
BEGIN
  IF TG_OP = 'INSERT' OR OLD.status IS NOT DISTINCT FROM NEW.status THEN RETURN NEW; END IF;
  allowed := CASE
    WHEN OLD.status = 'pending'             AND NEW.status IN ('confirmed','cancelled') THEN true
    WHEN OLD.status = 'confirmed'           AND NEW.status IN ('processing','cancelled','pending') THEN true
    WHEN OLD.status = 'processing'          AND NEW.status IN ('ready_for_delivery','completed','cancelled') THEN true
    WHEN OLD.status = 'ready_for_delivery'  AND NEW.status IN ('delivered','partial_delivered','cancelled') THEN true
    WHEN OLD.status = 'completed'           AND NEW.status IN ('delivered','partial_delivered','returned') THEN true
    WHEN OLD.status = 'partial_delivered'   AND NEW.status IN ('delivered','returned') THEN true
    WHEN OLD.status = 'delivered'           AND NEW.status IN ('returned') THEN true
    ELSE false
  END;
  IF NOT allowed THEN
    RAISE EXCEPTION 'Invalid status transition: % -> %', OLD.status, NEW.status;
  END IF;
  IF NEW.status = 'delivered' AND NEW.delivered_at IS NULL THEN NEW.delivered_at := now(); END IF;
  IF NEW.status = 'cancelled' AND NEW.cancelled_at IS NULL THEN NEW.cancelled_at := now(); END IF;
  RETURN NEW;
END;
$fn$;

CREATE OR REPLACE FUNCTION public.sales_orders_set_sale_status_fn()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $fn$
BEGIN
  IF NEW.status = 'returned' THEN
    NEW.sale_status := 'returned';
  ELSIF NEW.status = 'cancelled' THEN
    NEW.sale_status := 'draft';
  ELSIF NEW.bill_status = 'rough' THEN
    NEW.sale_status := 'draft';
  ELSIF COALESCE(NEW.paid_amount,0) <= 0 THEN
    NEW.sale_status := 'pending_payment';
  ELSIF COALESCE(NEW.paid_amount,0) >= COALESCE(NEW.total_amount,0) AND COALESCE(NEW.total_amount,0) > 0 THEN
    NEW.sale_status := 'paid';
  ELSE
    NEW.sale_status := 'partially_paid';
  END IF;
  RETURN NEW;
END;
$fn$;

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

UPDATE public.sales_orders
  SET sale_status = CASE
    WHEN status = 'returned' THEN 'returned'
    WHEN status = 'cancelled' THEN 'draft'
    WHEN bill_status = 'rough' THEN 'draft'
    WHEN COALESCE(paid_amount,0) <= 0 THEN 'pending_payment'
    WHEN COALESCE(paid_amount,0) >= COALESCE(total_amount,0) AND COALESCE(total_amount,0) > 0 THEN 'paid'
    ELSE 'partially_paid'
  END;
