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

-- SELECT policy
CREATE POLICY "Users with read permission can view"
  ON public.table_name FOR SELECT
  USING (public.me_has_permission('resource:read'));

-- INSERT policy
CREATE POLICY "Users with create permission can insert"
  ON public.table_name FOR INSERT
  WITH CHECK (public.me_has_permission('resource:create'));

-- UPDATE policy
CREATE POLICY "Users with update permission can modify"
  ON public.table_name FOR UPDATE
  USING (public.me_has_permission('resource:update'));

-- DELETE policy
CREATE POLICY "Users with delete permission can remove"
  ON public.table_name FOR DELETE
  USING (public.me_has_permission('resource:delete'));

ALTER TABLE public.tickets ENABLE ROW LEVEL SECURITY;

-- Read: full access or read permission
CREATE POLICY "tickets_select_policy"
  ON public.tickets FOR SELECT
  USING (
    public.me_has_permission('work_orders:read') OR
    public.me_has_permission('work_orders:full_access') OR
    (public.me_has_permission('work_orders:read_own') AND created_by = auth.uid())
  );

-- Insert: create permission
CREATE POLICY "tickets_insert_policy"
  ON public.tickets FOR INSERT
  WITH CHECK (
    public.me_has_permission('work_orders:create') OR
    public.me_has_permission('work_orders:full_access')
  );

-- Update: full access permission
CREATE POLICY "tickets_update_policy"
  ON public.tickets FOR UPDATE
  USING (public.me_has_permission('work_orders:full_access'));

-- Delete: delete or full access permission
CREATE POLICY "tickets_delete_policy"
  ON public.tickets FOR DELETE
  USING (
    public.me_has_permission('work_orders:delete') OR
    public.me_has_permission('work_orders:full_access')
  );

ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;

-- Read: users:read permission
CREATE POLICY "users_select_policy"
  ON public.users FOR SELECT
  USING (public.me_has_permission('users:read'));

-- Insert: users:create or full_access
CREATE POLICY "users_insert_policy"
  ON public.users FOR INSERT
  WITH CHECK (
    public.me_has_permission('users:create') OR
    public.me_has_permission('users:full_access')
  );

-- Update: full_access OR self-update (own profile)
CREATE POLICY "users_update_policy"
  ON public.users FOR UPDATE
  USING (
    public.me_has_permission('users:full_access') OR
    (id = auth.uid()) -- Users can update their own profile
  );

-- Delete: users:delete permission
CREATE POLICY "users_delete_policy"
  ON public.users FOR DELETE
  USING (public.me_has_permission('users:delete'));

ALTER TABLE public.assignees ENABLE ROW LEVEL SECURITY;

CREATE POLICY "assignees_select_policy"
  ON public.assignees FOR SELECT
  USING (public.me_has_permission('assignees:read'));

CREATE POLICY "assignees_insert_policy"
  ON public.assignees FOR INSERT
  WITH CHECK (public.me_has_permission('assignees:full_access'));

CREATE POLICY "assignees_update_policy"
  ON public.assignees FOR UPDATE
  USING (public.me_has_permission('assignees:full_access'));

CREATE POLICY "assignees_delete_policy"
  ON public.assignees FOR DELETE
  USING (public.me_has_permission('assignees:delete'));

ALTER TABLE public.roles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.permissions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.role_permissions ENABLE ROW LEVEL SECURITY;

-- Only users with manage_permissions can view/modify
CREATE POLICY "roles_all_policy"
  ON public.roles FOR ALL
  USING (public.me_has_permission('rbac:manage_permissions'));

CREATE POLICY "permissions_all_policy"
  ON public.permissions FOR ALL
  USING (public.me_has_permission('rbac:manage_permissions'));

CREATE POLICY "role_permissions_all_policy"
  ON public.role_permissions FOR ALL
  USING (public.me_has_permission('rbac:manage_permissions'));

ALTER TABLE public.user_roles ENABLE ROW LEVEL SECURITY;

-- Users can view their own roles
CREATE POLICY "user_roles_select_policy"
  ON public.user_roles FOR SELECT
  USING (
    user_id = auth.uid() OR
    public.me_has_permission('rbac:manage_roles')
  );

-- Only RBAC managers can modify role assignments
CREATE POLICY "user_roles_insert_policy"
  ON public.user_roles FOR INSERT
  WITH CHECK (public.me_has_permission('rbac:manage_roles'));

CREATE POLICY "user_roles_delete_policy"
  ON public.user_roles FOR DELETE
  USING (public.me_has_permission('rbac:manage_roles'));

ALTER TABLE public.locations ENABLE ROW LEVEL SECURITY;

-- All authenticated users can read locations
CREATE POLICY "locations_select_policy"
  ON public.locations FOR SELECT
  USING (auth.uid() IS NOT NULL);

-- Admins with locations:full_access can modify
CREATE POLICY "locations_insert_policy"
  ON public.locations FOR INSERT
  WITH CHECK (public.me_has_permission('locations:full_access'));

CREATE POLICY "locations_update_policy"
  ON public.locations FOR UPDATE
  USING (public.me_has_permission('locations:full_access'));

CREATE POLICY "locations_delete_policy"
  ON public.locations FOR DELETE
  USING (public.me_has_permission('locations:delete'));

ALTER TABLE public.notification_deliveries ENABLE ROW LEVEL SECURITY;

-- Users can only see their own notifications
CREATE POLICY "notification_deliveries_select_policy"
  ON public.notification_deliveries FOR SELECT
  USING (recipient_user_id = auth.uid());

-- Users can mark their own notifications as read
CREATE POLICY "notification_deliveries_update_policy"
  ON public.notification_deliveries FOR UPDATE
  USING (recipient_user_id = auth.uid());

-- Check if RLS is enabled
SELECT tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
  AND tablename IN ('tickets', 'users', 'assignees', 'roles');

-- List all policies on a table
SELECT policyname, cmd, qual, with_check
FROM pg_policies
WHERE tablename = 'tickets';

-- Test as specific user
SET LOCAL ROLE authenticated;
SET LOCAL "request.jwt.claim.sub" = 'user-uuid-here';
SELECT * FROM public.tickets; -- Should enforce policies