-- Sale Status System: 8-status workflow with stock + accounts integration

-- 1. Add columns to sales_orders
ALTER TABLE public.sales_orders
  ADD COLUMN IF NOT EXISTS delivered_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS cancelled_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS cancelled_reason TEXT,
  ADD COLUMN IF NOT EXISTS delivery_notes TEXT,
  ADD COLUMN IF NOT EXISTS stock_deducted BOOLEAN NOT NULL DEFAULT false;

-- 2. Partial delivery tracking table
CREATE TABLE IF NOT EXISTS public.sales_order_deliveries (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  sales_order_id UUID NOT NULL,
  sales_order_item_id UUID NOT NULL,
  delivered_qty NUMERIC NOT NULL DEFAULT 0,
  delivered_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  delivered_by UUID,
  note TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE public.sales_order_deliveries ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Staff can view deliveries" ON public.sales_order_deliveries
  FOR SELECT TO authenticated USING (is_staff(auth.uid()));
CREATE POLICY "Staff can insert deliveries" ON public.sales_order_deliveries
  FOR INSERT TO authenticated WITH CHECK (is_staff(auth.uid()));
CREATE POLICY "Staff can update deliveries" ON public.sales_order_deliveries
  FOR UPDATE TO authenticated USING (is_staff(auth.uid()));
CREATE POLICY "Admins can delete deliveries" ON public.sales_order_deliveries
  FOR DELETE TO authenticated USING (has_role(auth.uid(), 'admin'::app_role));

CREATE INDEX IF NOT EXISTS idx_so_deliveries_order ON public.sales_order_deliveries(sales_order_id);

-- 3. Status transition validation
CREATE OR REPLACE FUNCTION public.sales_orders_validate_status_fn()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  allowed BOOLEAN := false;
BEGIN
  IF TG_OP = 'INSERT' OR OLD.status IS NOT DISTINCT FROM NEW.status THEN
    RETURN NEW;
  END IF;

  -- transition matrix
  allowed := CASE
    WHEN OLD.status = 'pending'           AND NEW.status IN ('confirmed','cancelled') THEN true
    WHEN OLD.status = 'confirmed'         AND NEW.status IN ('processing','cancelled') THEN true
    WHEN OLD.status = 'processing'        AND NEW.status IN ('completed','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
    -- admin override path: allow back to pending from non-terminal
    WHEN NEW.status = 'pending' AND OLD.status IN ('confirmed') THEN true
    ELSE false
  END;

  IF NOT allowed THEN
    RAISE EXCEPTION 'Invalid status transition: % -> %', OLD.status, NEW.status;
  END IF;

  -- timestamp stamps
  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;
$$;

DROP TRIGGER IF EXISTS trg_sales_orders_validate_status ON public.sales_orders;
CREATE TRIGGER trg_sales_orders_validate_status
  BEFORE UPDATE ON public.sales_orders
  FOR EACH ROW EXECUTE FUNCTION public.sales_orders_validate_status_fn();

-- 4. Stock movement on status change
CREATE OR REPLACE FUNCTION public.sales_orders_stock_movement_fn()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  it RECORD;
BEGIN
  -- Deduct stock on first move into completed/delivered
  IF NEW.status IN ('completed','delivered','partial_delivered')
     AND COALESCE(OLD.stock_deducted, false) = false THEN
    FOR it IN SELECT product_id, quantity FROM public.sales_order_items WHERE sales_order_id = NEW.id LOOP
      UPDATE public.products
        SET stock_quantity = COALESCE(stock_quantity,0) - it.quantity
        WHERE id = it.product_id;
    END LOOP;
    UPDATE public.sales_orders SET stock_deducted = true WHERE id = NEW.id;
  END IF;

  -- Restore stock on cancel (only if previously deducted)
  IF NEW.status = 'cancelled' AND COALESCE(OLD.stock_deducted, false) = true THEN
    FOR it IN SELECT product_id, quantity FROM public.sales_order_items WHERE sales_order_id = NEW.id LOOP
      UPDATE public.products
        SET stock_quantity = COALESCE(stock_quantity,0) + it.quantity
        WHERE id = it.product_id;
    END LOOP;
    UPDATE public.sales_orders SET stock_deducted = false, due_amount = 0 WHERE id = NEW.id;
  END IF;

  -- Restore stock on return
  IF NEW.status = 'returned' AND OLD.status <> 'returned' AND COALESCE(OLD.stock_deducted, false) = true THEN
    FOR it IN SELECT product_id, quantity FROM public.sales_order_items WHERE sales_order_id = NEW.id LOOP
      UPDATE public.products
        SET stock_quantity = COALESCE(stock_quantity,0) + it.quantity
        WHERE id = it.product_id;
    END LOOP;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_sales_orders_stock_movement ON public.sales_orders;
CREATE TRIGGER trg_sales_orders_stock_movement
  AFTER UPDATE OF status ON public.sales_orders
  FOR EACH ROW EXECUTE FUNCTION public.sales_orders_stock_movement_fn();

-- 5. Customer order stats helper
CREATE OR REPLACE FUNCTION public.get_customer_order_stats(_customer_id UUID)
RETURNS JSON
LANGUAGE sql
STABLE SECURITY DEFINER
SET search_path = public
AS $$
  SELECT json_build_object(
    'total',     COUNT(*),
    'pending',   COUNT(*) FILTER (WHERE status IN ('pending','confirmed','processing')),
    'completed', COUNT(*) FILTER (WHERE status IN ('completed','delivered','partial_delivered')),
    'returned',  COUNT(*) FILTER (WHERE status = 'returned'),
    'cancelled', COUNT(*) FILTER (WHERE status = 'cancelled'),
    'total_amount', COALESCE(SUM(total_amount), 0),
    'total_due',    COALESCE(SUM(due_amount), 0)
  )
  FROM public.sales_orders
  WHERE customer_id = _customer_id;
$$;