
-- Godowns table
CREATE TABLE public.godowns (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  code TEXT DEFAULT '',
  address TEXT DEFAULT '',
  manager_name TEXT DEFAULT '',
  mobile TEXT DEFAULT '',
  status TEXT NOT NULL DEFAULT 'active',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE public.godowns ENABLE ROW LEVEL SECURITY;

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

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

-- Godown stocks table (per product per godown)
CREATE TABLE public.godown_stocks (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  product_id UUID NOT NULL,
  godown_id UUID NOT NULL REFERENCES public.godowns(id) ON DELETE CASCADE,
  quantity NUMERIC NOT NULL DEFAULT 0,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(product_id, godown_id)
);

ALTER TABLE public.godown_stocks ENABLE ROW LEVEL SECURITY;

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

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

-- Stock transfers table
CREATE TABLE public.stock_transfers (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  from_godown_id UUID NOT NULL REFERENCES public.godowns(id),
  to_godown_id UUID NOT NULL REFERENCES public.godowns(id),
  product_id UUID NOT NULL,
  quantity NUMERIC NOT NULL DEFAULT 0,
  transfer_date DATE NOT NULL DEFAULT CURRENT_DATE,
  note TEXT DEFAULT '',
  transferred_by UUID,
  status TEXT NOT NULL DEFAULT 'completed',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE public.stock_transfers ENABLE ROW LEVEL SECURITY;

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