
-- Create role enum
CREATE TYPE public.app_role AS ENUM ('admin', 'staff', 'user');

-- Create user_roles table
CREATE TABLE public.user_roles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
  role app_role NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (user_id, role)
);

ALTER TABLE public.user_roles ENABLE ROW LEVEL SECURITY;

-- Security definer function to check roles (created BEFORE policies that use it)
CREATE OR REPLACE FUNCTION public.has_role(_user_id uuid, _role app_role)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.user_roles
    WHERE user_id = _user_id AND role = _role
  )
$$;

CREATE OR REPLACE FUNCTION public.is_staff(_user_id uuid)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.user_roles
    WHERE user_id = _user_id AND role IN ('admin', 'staff')
  )
$$;

-- user_roles policies
CREATE POLICY "Users can view own roles" ON public.user_roles
  FOR SELECT TO authenticated
  USING (auth.uid() = user_id);

CREATE POLICY "Admins can manage roles" ON public.user_roles
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin'))
  WITH CHECK (public.has_role(auth.uid(), 'admin'));

-- ========== Replace write policies on all business tables ==========

-- PRODUCTS
DROP POLICY IF EXISTS "Authenticated users can insert products" ON public.products;
DROP POLICY IF EXISTS "Authenticated users can update products" ON public.products;
CREATE POLICY "Staff can insert products" ON public.products FOR INSERT TO authenticated WITH CHECK (public.is_staff(auth.uid()));
CREATE POLICY "Staff can update products" ON public.products FOR UPDATE TO authenticated USING (public.is_staff(auth.uid()));

-- CUSTOMERS
DROP POLICY IF EXISTS "Authenticated users can insert customers" ON public.customers;
DROP POLICY IF EXISTS "Authenticated users can update customers" ON public.customers;
CREATE POLICY "Staff can insert customers" ON public.customers FOR INSERT TO authenticated WITH CHECK (public.is_staff(auth.uid()));
CREATE POLICY "Staff can update customers" ON public.customers FOR UPDATE TO authenticated USING (public.is_staff(auth.uid()));

-- CUSTOMER_TRANSACTIONS
DROP POLICY IF EXISTS "Authenticated users can insert customer transactions" ON public.customer_transactions;
CREATE POLICY "Staff can insert customer transactions" ON public.customer_transactions FOR INSERT TO authenticated WITH CHECK (public.is_staff(auth.uid()));

-- CATEGORIES
DROP POLICY IF EXISTS "Authenticated users can insert categories" ON public.categories;
DROP POLICY IF EXISTS "Authenticated users can update categories" ON public.categories;
CREATE POLICY "Staff can insert categories" ON public.categories FOR INSERT TO authenticated WITH CHECK (public.is_staff(auth.uid()));
CREATE POLICY "Staff can update categories" ON public.categories FOR UPDATE TO authenticated USING (public.is_staff(auth.uid()));

-- SUBCATEGORIES
DROP POLICY IF EXISTS "Authenticated users can insert subcategories" ON public.subcategories;
DROP POLICY IF EXISTS "Authenticated users can update subcategories" ON public.subcategories;
CREATE POLICY "Staff can insert subcategories" ON public.subcategories FOR INSERT TO authenticated WITH CHECK (public.is_staff(auth.uid()));
CREATE POLICY "Staff can update subcategories" ON public.subcategories FOR UPDATE TO authenticated USING (public.is_staff(auth.uid()));

-- UNITS
DROP POLICY IF EXISTS "Authenticated users can insert units" ON public.units;
DROP POLICY IF EXISTS "Authenticated users can update units" ON public.units;
CREATE POLICY "Staff can insert units" ON public.units FOR INSERT TO authenticated WITH CHECK (public.is_staff(auth.uid()));
CREATE POLICY "Staff can update units" ON public.units FOR UPDATE TO authenticated USING (public.is_staff(auth.uid()));

-- PRODUCT_TYPES
DROP POLICY IF EXISTS "Authenticated users can insert product types" ON public.product_types;
DROP POLICY IF EXISTS "Authenticated users can update product types" ON public.product_types;
CREATE POLICY "Staff can insert product types" ON public.product_types FOR INSERT TO authenticated WITH CHECK (public.is_staff(auth.uid()));
CREATE POLICY "Staff can update product types" ON public.product_types FOR UPDATE TO authenticated USING (public.is_staff(auth.uid()));

-- STOCK_ADJUSTMENTS
DROP POLICY IF EXISTS "Authenticated users can insert stock adjustments" ON public.stock_adjustments;
CREATE POLICY "Staff can insert stock adjustments" ON public.stock_adjustments FOR INSERT TO authenticated WITH CHECK (public.is_staff(auth.uid()));
