
-- Purchases table
CREATE TABLE public.purchases (
  id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  supplier_id uuid NOT NULL,
  invoice_no text NOT NULL DEFAULT '',
  purchase_date date NOT NULL DEFAULT CURRENT_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',
  note text DEFAULT '',
  status text NOT NULL DEFAULT 'completed',
  created_by uuid,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE public.purchases ENABLE ROW LEVEL SECURITY;

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

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

-- Purchase Items table
CREATE TABLE public.purchase_items (
  id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  purchase_id uuid NOT NULL REFERENCES public.purchases(id) ON DELETE CASCADE,
  product_id uuid NOT NULL,
  quantity numeric NOT NULL DEFAULT 0,
  purchase_price numeric NOT NULL DEFAULT 0,
  total numeric NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE public.purchase_items ENABLE ROW LEVEL SECURITY;

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

-- Stock Batches table (core of multi-purchase-price system)
CREATE TABLE public.stock_batches (
  id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  product_id uuid NOT NULL,
  purchase_id uuid REFERENCES public.purchases(id) ON DELETE SET NULL,
  quantity numeric NOT NULL DEFAULT 0,
  remaining_qty numeric NOT NULL DEFAULT 0,
  purchase_price numeric NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE public.stock_batches ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Staff can view stock batches" ON public.stock_batches FOR SELECT TO authenticated USING (is_staff(auth.uid()));
CREATE POLICY "Staff can insert stock batches" ON public.stock_batches FOR INSERT TO authenticated WITH CHECK (is_staff(auth.uid()));
CREATE POLICY "Staff can update stock batches" ON public.stock_batches FOR UPDATE TO authenticated USING (is_staff(auth.uid()));

CREATE INDEX idx_stock_batches_product ON public.stock_batches(product_id);
CREATE INDEX idx_stock_batches_remaining ON public.stock_batches(product_id, remaining_qty) WHERE remaining_qty > 0;
CREATE INDEX idx_purchase_items_purchase ON public.purchase_items(purchase_id);
CREATE INDEX idx_purchases_supplier ON public.purchases(supplier_id);
CREATE INDEX idx_purchases_date ON public.purchases(purchase_date);
