
CREATE TABLE public.product_units (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  product_id UUID NOT NULL,
  unit_name TEXT NOT NULL DEFAULT '',
  unit_name_bn TEXT NOT NULL DEFAULT '',
  conversion_factor NUMERIC NOT NULL DEFAULT 1,
  price NUMERIC NOT NULL DEFAULT 0,
  purchase_price NUMERIC NOT NULL DEFAULT 0,
  barcode TEXT NULL DEFAULT '',
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

CREATE INDEX idx_product_units_product_id ON public.product_units(product_id);

ALTER TABLE public.product_units ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Staff can view product units"
  ON public.product_units FOR SELECT TO authenticated
  USING (is_staff(auth.uid()));

CREATE POLICY "Staff can insert product units"
  ON public.product_units FOR INSERT TO authenticated
  WITH CHECK (is_staff(auth.uid()));

CREATE POLICY "Staff can update product units"
  ON public.product_units FOR UPDATE TO authenticated
  USING (is_staff(auth.uid()));

CREATE POLICY "Admins can delete product units"
  ON public.product_units FOR DELETE TO authenticated
  USING (has_role(auth.uid(), 'admin'::app_role));

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