
-- Sales Returns table
CREATE TABLE public.sales_returns (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  invoice_id TEXT NOT NULL DEFAULT '',
  customer_id UUID REFERENCES public.customers(id),
  product_id UUID REFERENCES public.products(id) NOT NULL,
  quantity NUMERIC NOT NULL DEFAULT 1,
  return_type TEXT NOT NULL DEFAULT 'normal' CHECK (return_type IN ('fresh', 'replacement', 'normal')),
  replacement_product_id UUID REFERENCES public.products(id),
  refund_amount NUMERIC NOT NULL DEFAULT 0,
  reason TEXT DEFAULT '',
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'completed', 'rejected')),
  processed_by UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE public.sales_returns ENABLE ROW LEVEL SECURITY;

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

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

-- Refunds table
CREATE TABLE public.refunds (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  customer_id UUID REFERENCES public.customers(id) NOT NULL,
  return_id UUID REFERENCES public.sales_returns(id),
  amount NUMERIC NOT NULL DEFAULT 0,
  payment_method TEXT NOT NULL DEFAULT 'cash' CHECK (payment_method IN ('cash', 'bank', 'mobile')),
  note TEXT DEFAULT '',
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'completed')),
  processed_by UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE public.refunds ENABLE ROW LEVEL SECURITY;

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

-- Damage Stock table
CREATE TABLE public.damage_stock (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  product_id UUID REFERENCES public.products(id) NOT NULL,
  quantity NUMERIC NOT NULL DEFAULT 0,
  source TEXT NOT NULL DEFAULT 'return' CHECK (source IN ('return', 'replacement')),
  reference_id UUID,
  note TEXT DEFAULT '',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE public.damage_stock ENABLE ROW LEVEL SECURITY;

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