
-- Create unified product_pricing table
CREATE TABLE public.product_pricing (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  product_id uuid NOT NULL,
  unit_name text NOT NULL DEFAULT 'Piece',
  unit_name_bn text NOT NULL DEFAULT 'পিস',
  conversion_factor numeric NOT NULL DEFAULT 1,
  price_type text NOT NULL DEFAULT 'retail',
  min_qty numeric NOT NULL DEFAULT 1,
  regular_price numeric NOT NULL DEFAULT 0,
  purchase_price numeric NOT NULL DEFAULT 0,
  offer_price numeric DEFAULT NULL,
  offer_percent numeric DEFAULT NULL,
  offer_start timestamptz DEFAULT NULL,
  offer_end timestamptz DEFAULT NULL,
  barcode text DEFAULT '',
  is_base_unit boolean NOT NULL DEFAULT false,
  is_active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

-- Indexes
CREATE INDEX idx_product_pricing_product ON public.product_pricing(product_id);
CREATE INDEX idx_product_pricing_unit ON public.product_pricing(product_id, unit_name);
CREATE INDEX idx_product_pricing_type ON public.product_pricing(product_id, price_type);

-- Enable RLS
ALTER TABLE public.product_pricing ENABLE ROW LEVEL SECURITY;

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

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

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

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

-- Trigger for updated_at
CREATE TRIGGER update_product_pricing_updated_at
  BEFORE UPDATE ON public.product_pricing
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
