-- 1. Product categories table (extensible)
CREATE TABLE IF NOT EXISTS public.product_categories (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id UUID NULL, -- NULL = global (available to all companies)
  name TEXT NOT NULL,
  color TEXT NOT NULL DEFAULT '#3b82f6',
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(company_id, name)
);

ALTER TABLE public.product_categories ENABLE ROW LEVEL SECURITY;

CREATE POLICY "View categories" ON public.product_categories
  FOR SELECT TO authenticated
  USING (company_id IS NULL OR company_id = public.get_user_company(auth.uid()) OR public.is_super_admin(auth.uid()));

CREATE POLICY "Admins manage categories" ON public.product_categories
  FOR ALL TO authenticated
  USING (public.is_super_admin(auth.uid()) OR (public.has_role(auth.uid(), 'company_admin') AND company_id = public.get_user_company(auth.uid())))
  WITH CHECK (public.is_super_admin(auth.uid()) OR (public.has_role(auth.uid(), 'company_admin') AND company_id = public.get_user_company(auth.uid())));

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

-- 2. Employee → category access
CREATE TABLE IF NOT EXISTS public.employee_categories (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  category_id UUID NOT NULL REFERENCES public.product_categories(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(user_id, category_id)
);

ALTER TABLE public.employee_categories ENABLE ROW LEVEL SECURITY;

CREATE POLICY "View own assignments" ON public.employee_categories
  FOR SELECT TO authenticated USING (user_id = auth.uid() OR public.is_super_admin(auth.uid()) OR public.has_role(auth.uid(),'company_admin'));

CREATE POLICY "Admins manage assignments" ON public.employee_categories
  FOR ALL TO authenticated
  USING (public.is_super_admin(auth.uid()) OR public.has_role(auth.uid(),'company_admin'))
  WITH CHECK (public.is_super_admin(auth.uid()) OR public.has_role(auth.uid(),'company_admin'));

-- 3. Helper: does user have access to category?
CREATE OR REPLACE FUNCTION public.user_has_category(_user_id UUID, _category_id UUID)
RETURNS BOOLEAN LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$
  SELECT
    public.is_super_admin(_user_id)
    OR public.has_role(_user_id, 'company_admin')
    OR _category_id IS NULL
    OR EXISTS (SELECT 1 FROM public.employee_categories WHERE user_id = _user_id AND category_id = _category_id)
    OR NOT EXISTS (SELECT 1 FROM public.employee_categories WHERE user_id = _user_id);
$$;

-- 4. Extend clients
ALTER TABLE public.clients
  ADD COLUMN IF NOT EXISTS mobile_secondary TEXT,
  ADD COLUMN IF NOT EXISTS category_id UUID REFERENCES public.product_categories(id) ON DELETE SET NULL;

CREATE INDEX IF NOT EXISTS idx_clients_serial_no ON public.clients(serial_no);
CREATE INDEX IF NOT EXISTS idx_clients_mobile ON public.clients(mobile);
CREATE INDEX IF NOT EXISTS idx_clients_renewal ON public.clients(next_renewal_date);
CREATE INDEX IF NOT EXISTS idx_clients_company ON public.clients(company_id);
CREATE INDEX IF NOT EXISTS idx_clients_category ON public.clients(category_id);

-- 5. Seed BUSY + Tally as global categories
INSERT INTO public.product_categories (company_id, name, color)
VALUES (NULL, 'BUSY', '#3b82f6'), (NULL, 'Tally', '#10b981')
ON CONFLICT DO NOTHING;

-- 6. Auto-promote super admin email
CREATE OR REPLACE FUNCTION public.auto_promote_super_admin()
RETURNS TRIGGER LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  IF NEW.email = 'ganpatiratia@gmail.com' THEN
    INSERT INTO public.user_roles (user_id, role) VALUES (NEW.user_id, 'super_admin')
    ON CONFLICT DO NOTHING;
  END IF;
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS auto_promote_super_admin_trigger ON public.profiles;
CREATE TRIGGER auto_promote_super_admin_trigger
  AFTER INSERT ON public.profiles
  FOR EACH ROW EXECUTE FUNCTION public.auto_promote_super_admin();

-- 7. Promote NOW if profile already exists
INSERT INTO public.user_roles (user_id, role)
SELECT user_id, 'super_admin'::app_role FROM public.profiles WHERE email = 'ganpatiratia@gmail.com'
ON CONFLICT DO NOTHING;