
-- Transfers table (multi-item, with approval workflow)
CREATE TABLE public.transfers (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  transfer_no TEXT NOT NULL DEFAULT '',
  from_godown_id UUID NOT NULL REFERENCES public.godowns(id),
  to_godown_id UUID NOT NULL REFERENCES public.godowns(id),
  transfer_date DATE NOT NULL DEFAULT CURRENT_DATE,
  status TEXT NOT NULL DEFAULT 'pending',
  note TEXT DEFAULT '',
  total_items INTEGER NOT NULL DEFAULT 0,
  created_by UUID,
  approved_by UUID,
  approved_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE public.transfers ENABLE ROW LEVEL SECURITY;

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

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

-- Transfer items table
CREATE TABLE public.transfer_items (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  transfer_id UUID NOT NULL REFERENCES public.transfers(id) ON DELETE CASCADE,
  product_id UUID NOT NULL,
  quantity NUMERIC NOT NULL DEFAULT 0,
  unit TEXT NOT NULL DEFAULT 'pcs',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE public.transfer_items ENABLE ROW LEVEL SECURITY;

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

-- Auto-generate transfer_no
CREATE OR REPLACE FUNCTION public.generate_transfer_no()
RETURNS TRIGGER AS $$
DECLARE
  next_num INTEGER;
BEGIN
  SELECT COALESCE(MAX(CAST(SUBSTRING(transfer_no FROM 4) AS INTEGER)), 0) + 1
  INTO next_num
  FROM public.transfers;
  NEW.transfer_no := 'TR-' || LPAD(next_num::TEXT, 6, '0');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SET search_path = public;

CREATE TRIGGER set_transfer_no
BEFORE INSERT ON public.transfers
FOR EACH ROW
WHEN (NEW.transfer_no = '' OR NEW.transfer_no IS NULL)
EXECUTE FUNCTION public.generate_transfer_no();
