
-- Suppliers table
CREATE TABLE public.suppliers (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  mobile TEXT NOT NULL UNIQUE,
  email TEXT DEFAULT '',
  address TEXT DEFAULT '',
  company_name TEXT DEFAULT '',
  opening_balance NUMERIC NOT NULL DEFAULT 0,
  total_purchase NUMERIC NOT NULL DEFAULT 0,
  total_paid NUMERIC NOT NULL DEFAULT 0,
  current_due NUMERIC NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'active',
  is_deleted BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE public.suppliers ENABLE ROW LEVEL SECURITY;

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

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

-- Supplier transactions / ledger table
CREATE TABLE public.supplier_transactions (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  supplier_id UUID NOT NULL REFERENCES public.suppliers(id) ON DELETE CASCADE,
  type TEXT NOT NULL DEFAULT 'purchase',
  amount NUMERIC NOT NULL DEFAULT 0,
  method TEXT NOT NULL DEFAULT 'cash',
  reference_id TEXT DEFAULT '',
  note TEXT DEFAULT '',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE public.supplier_transactions ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Staff can view supplier transactions" ON public.supplier_transactions FOR SELECT TO authenticated USING (is_staff(auth.uid()));
CREATE POLICY "Staff can insert supplier transactions" ON public.supplier_transactions FOR INSERT TO authenticated WITH CHECK (is_staff(auth.uid()));
