CREATE TABLE public.sales_orders (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  order_no text NOT NULL DEFAULT '',
  customer_id uuid,
  branch_id uuid,
  order_date date NOT NULL DEFAULT CURRENT_DATE,
  delivery_date date,
  total_amount numeric NOT NULL DEFAULT 0,
  discount numeric NOT NULL DEFAULT 0,
  paid_amount numeric NOT NULL DEFAULT 0,
  due_amount numeric NOT NULL DEFAULT 0,
  payment_method text NOT NULL DEFAULT 'cash',
  status text NOT NULL DEFAULT 'pending',
  note text DEFAULT '',
  created_by uuid,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE public.sales_order_items (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  sales_order_id uuid NOT NULL REFERENCES public.sales_orders(id) ON DELETE CASCADE,
  product_id uuid NOT NULL,
  quantity numeric NOT NULL DEFAULT 0,
  price numeric NOT NULL DEFAULT 0,
  total numeric NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE public.sales_orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.sales_order_items ENABLE ROW LEVEL SECURITY;

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

CREATE POLICY "Staff can view sales order items" ON public.sales_order_items FOR SELECT TO authenticated USING (is_staff(auth.uid()));
CREATE POLICY "Staff can insert sales order items" ON public.sales_order_items FOR INSERT TO authenticated WITH CHECK (is_staff(auth.uid()));
CREATE POLICY "Staff can update sales order items" ON public.sales_order_items FOR UPDATE TO authenticated USING (is_staff(auth.uid()));
CREATE POLICY "Staff can delete sales order items" ON public.sales_order_items FOR DELETE TO authenticated USING (is_staff(auth.uid()));

CREATE TRIGGER trg_sales_orders_updated BEFORE UPDATE ON public.sales_orders
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

CREATE OR REPLACE FUNCTION public.generate_sales_order_no()
RETURNS trigger LANGUAGE plpgsql SET search_path = public AS $$
DECLARE n integer;
BEGIN
  IF NEW.order_no IS NULL OR NEW.order_no = '' THEN
    SELECT COALESCE(MAX(CAST(NULLIF(regexp_replace(order_no, '\D', '', 'g'), '') AS integer)), 0) + 1
      INTO n FROM public.sales_orders;
    NEW.order_no := 'SO-' || LPAD(n::text, 6, '0');
  END IF;
  RETURN NEW;
END; $$;

CREATE TRIGGER trg_sales_orders_no BEFORE INSERT ON public.sales_orders
  FOR EACH ROW EXECUTE FUNCTION public.generate_sales_order_no();

CREATE INDEX idx_sales_orders_customer ON public.sales_orders(customer_id);
CREATE INDEX idx_sales_orders_branch ON public.sales_orders(branch_id);
CREATE INDEX idx_sales_orders_status ON public.sales_orders(status);
CREATE INDEX idx_sales_orders_date ON public.sales_orders(order_date);
CREATE INDEX idx_sales_order_items_order ON public.sales_order_items(sales_order_id);