
-- Extend profiles table
ALTER TABLE public.profiles
  ADD COLUMN IF NOT EXISTS mobile TEXT UNIQUE,
  ADD COLUMN IF NOT EXISTS username TEXT UNIQUE,
  ADD COLUMN IF NOT EXISTS status TEXT NOT NULL DEFAULT 'active',
  ADD COLUMN IF NOT EXISTS last_login TIMESTAMPTZ;

-- Custom Roles table
CREATE TABLE public.custom_roles (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  description TEXT DEFAULT '',
  status TEXT NOT NULL DEFAULT 'active',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE public.custom_roles ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Staff can view roles" ON public.custom_roles FOR SELECT TO authenticated USING (is_staff(auth.uid()));
CREATE POLICY "Admins can insert roles" ON public.custom_roles FOR INSERT TO authenticated WITH CHECK (has_role(auth.uid(), 'admin'::app_role));
CREATE POLICY "Admins can update roles" ON public.custom_roles FOR UPDATE TO authenticated USING (has_role(auth.uid(), 'admin'::app_role));
CREATE POLICY "Admins can delete roles" ON public.custom_roles FOR DELETE TO authenticated USING (has_role(auth.uid(), 'admin'::app_role));

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

-- Permissions table
CREATE TABLE public.permissions (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  module TEXT NOT NULL,
  description TEXT DEFAULT '',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE public.permissions ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Authenticated can view permissions" ON public.permissions FOR SELECT TO authenticated USING (true);
CREATE POLICY "Admins can manage permissions" ON public.permissions FOR ALL TO authenticated USING (has_role(auth.uid(), 'admin'::app_role)) WITH CHECK (has_role(auth.uid(), 'admin'::app_role));

-- Role-Permission mapping
CREATE TABLE public.role_permissions (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  role_id UUID NOT NULL REFERENCES public.custom_roles(id) ON DELETE CASCADE,
  permission_id UUID NOT NULL REFERENCES public.permissions(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(role_id, permission_id)
);

ALTER TABLE public.role_permissions ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Staff can view role_permissions" ON public.role_permissions FOR SELECT TO authenticated USING (is_staff(auth.uid()));
CREATE POLICY "Admins can manage role_permissions" ON public.role_permissions FOR ALL TO authenticated USING (has_role(auth.uid(), 'admin'::app_role)) WITH CHECK (has_role(auth.uid(), 'admin'::app_role));

-- User permission overrides (optional per-user)
CREATE TABLE public.user_permission_overrides (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL,
  permission_id UUID NOT NULL REFERENCES public.permissions(id) ON DELETE CASCADE,
  granted BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(user_id, permission_id)
);

ALTER TABLE public.user_permission_overrides ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Staff can view overrides" ON public.user_permission_overrides FOR SELECT TO authenticated USING (is_staff(auth.uid()));
CREATE POLICY "Admins can manage overrides" ON public.user_permission_overrides FOR ALL TO authenticated USING (has_role(auth.uid(), 'admin'::app_role)) WITH CHECK (has_role(auth.uid(), 'admin'::app_role));

-- Add role_id to profiles for custom role mapping
ALTER TABLE public.profiles ADD COLUMN IF NOT EXISTS custom_role_id UUID REFERENCES public.custom_roles(id);

-- Seed default permissions
INSERT INTO public.permissions (name, module, description) VALUES
  ('create_sale', 'sales', 'Create new sale'),
  ('edit_sale', 'sales', 'Edit existing sale'),
  ('delete_sale', 'sales', 'Delete sale'),
  ('view_sales', 'sales', 'View sales list'),
  ('add_product', 'product', 'Add new product'),
  ('edit_product', 'product', 'Edit product'),
  ('delete_product', 'product', 'Delete product'),
  ('view_products', 'product', 'View products'),
  ('add_customer', 'customer', 'Add customer'),
  ('edit_customer', 'customer', 'Edit customer'),
  ('view_customers', 'customer', 'View customers'),
  ('delete_customer', 'customer', 'Delete customer'),
  ('view_accounts', 'accounts', 'View accounts'),
  ('add_payment', 'accounts', 'Add payment'),
  ('refund_access', 'accounts', 'Access refunds'),
  ('user_management', 'admin', 'Manage users'),
  ('role_management', 'admin', 'Manage roles'),
  ('permission_control', 'admin', 'Control permissions'),
  ('view_returns', 'returns', 'View returns'),
  ('create_return', 'returns', 'Create return'),
  ('view_reports', 'reports', 'View reports')
ON CONFLICT (name) DO NOTHING;

-- Seed default custom roles
INSERT INTO public.custom_roles (name, description) VALUES
  ('Admin', 'Full system access'),
  ('Manager', 'Sales, stock and customer management'),
  ('Sales', 'POS and sales access only'),
  ('Accountant', 'Accounts and payment management')
ON CONFLICT (name) DO NOTHING;

-- Function to check user permission
CREATE OR REPLACE FUNCTION public.has_permission(_user_id UUID, _permission TEXT)
RETURNS BOOLEAN
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT
    -- Admin role always has all permissions
    EXISTS (SELECT 1 FROM public.user_roles WHERE user_id = _user_id AND role = 'admin')
    OR
    -- Check user override (granted = true)
    EXISTS (
      SELECT 1 FROM public.user_permission_overrides upo
      JOIN public.permissions p ON p.id = upo.permission_id
      WHERE upo.user_id = _user_id AND p.name = _permission AND upo.granted = true
    )
    OR
    (
      -- Check role permissions (if no deny override)
      NOT EXISTS (
        SELECT 1 FROM public.user_permission_overrides upo
        JOIN public.permissions p ON p.id = upo.permission_id
        WHERE upo.user_id = _user_id AND p.name = _permission AND upo.granted = false
      )
      AND EXISTS (
        SELECT 1 FROM public.profiles pr
        JOIN public.role_permissions rp ON rp.role_id = pr.custom_role_id
        JOIN public.permissions p ON p.id = rp.permission_id
        WHERE pr.id = _user_id AND p.name = _permission
      )
    )
$$;
