==============================================================================
SAMPLE RLS POLICY EXAMPLES
==============================================================================

1. SUPER ADMIN - Full Access (policies table)
---------------------------------------------------------------------------
CREATE POLICY "super_admin_policies_full_access" ON policies
  FOR ALL
  USING (
    EXISTS (
      SELECT 1 FROM public.users
      WHERE id = auth.uid()
      AND role = 'super_admin'
    )
  )
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM public.users
      WHERE id = auth.uid()
      AND role = 'super_admin'
    )
  );

COMMENT: Super admins have unrestricted access to all policies across
all organizations for platform administration.

==============================================================================

2. ADMIN - Organization Access (policies table)
---------------------------------------------------------------------------
CREATE POLICY "admin_policies_org_full_access" ON policies
  FOR ALL
  USING (
    EXISTS (
      SELECT 1 FROM public.users
      WHERE id = auth.uid()
      AND role = 'admin'
      AND organization_id = policies.organization_id
    )
  )
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM public.users
      WHERE id = auth.uid()
      AND role = 'admin'
      AND organization_id = policies.organization_id
    )
  );

COMMENT: Admins have full CRUD access to all policies within their
organization, including soft-deleted records.

==============================================================================

3. AGENT - Assigned Customer Access (policies SELECT)
---------------------------------------------------------------------------
CREATE POLICY "agent_policies_assigned_select" ON policies
  FOR SELECT
  USING (
    deleted_at IS NULL
    AND EXISTS (
      SELECT 1 FROM public.users u
      JOIN public.agent_customer_assignments aca ON aca.agent_id = u.id
      WHERE u.id = auth.uid()
      AND u.role = 'agent'
      AND aca.customer_id = policies.customer_id
      AND u.organization_id = policies.organization_id
      AND aca.is_active = true
    )
  );

COMMENT: Agents can view policies only for customers assigned to them
within their organization. Soft-deleted policies are hidden.

==============================================================================

4. AGENT - Assigned Customer Access (policies INSERT)
---------------------------------------------------------------------------
CREATE POLICY "agent_policies_assigned_insert" ON policies
  FOR INSERT
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM public.users u
      JOIN public.agent_customer_assignments aca ON aca.agent_id = u.id
      WHERE u.id = auth.uid()
      AND u.role = 'agent'
      AND aca.customer_id = policies.customer_id
      AND u.organization_id = policies.organization_id
      AND aca.is_active = true
    )
  );

COMMENT: Agents can create policies only for customers assigned to them
within their organization.

==============================================================================

5. CUSTOMER - Own Policies Access (policies SELECT)
---------------------------------------------------------------------------
CREATE POLICY "customer_policies_own_select" ON policies
  FOR SELECT
  USING (
    deleted_at IS NULL
    AND EXISTS (
      SELECT 1 FROM public.users
      WHERE id = auth.uid()
      AND role = 'customer'
      AND id = policies.customer_id
    )
  );

COMMENT: Customers can only view their own active policies. No write
access. Soft-deleted policies are hidden.

==============================================================================

6. READONLY - Organization Access (policies SELECT)
---------------------------------------------------------------------------
CREATE POLICY "readonly_policies_org_select" ON policies
  FOR SELECT
  USING (
    deleted_at IS NULL
    AND EXISTS (
      SELECT 1 FROM public.users
      WHERE id = auth.uid()
      AND role = 'readonly'
      AND organization_id = policies.organization_id
    )
  );

COMMENT: Readonly users can view all active policies within their
organization. No write access.

==============================================================================

7. AGENT - Documents Access (child table example)
---------------------------------------------------------------------------
CREATE POLICY "agent_policy_documents_assigned_select" ON policy_documents
  FOR SELECT
  USING (
    deleted_at IS NULL
    AND EXISTS (
      SELECT 1 FROM public.users u
      JOIN public.agent_customer_assignments aca ON aca.agent_id = u.id
      JOIN policies p ON p.id = policy_documents.policy_id
      WHERE u.id = auth.uid()
      AND u.role = 'agent'
      AND aca.customer_id = p.customer_id
      AND u.organization_id = policy_documents.organization_id
      AND aca.is_active = true
      AND p.deleted_at IS NULL
    )
  );

COMMENT: Agents can view documents for policies of assigned customers.
Note the JOIN through policies table for cascading access control.

==============================================================================

8. CUSTOMER - Limited Audit Log Access
---------------------------------------------------------------------------
CREATE POLICY "customer_policy_audit_log_own_select" ON policy_audit_log
  FOR SELECT
  USING (
    event_type IN ('viewed', 'exported', 'printed')
    AND EXISTS (
      SELECT 1 FROM public.users u
      JOIN policies p ON p.id = policy_audit_log.policy_id
      WHERE u.id = auth.uid()
      AND u.role = 'customer'
      AND u.id = p.customer_id
      AND p.deleted_at IS NULL
    )
  );

COMMENT: Customers can view limited audit logs (view/export/print events
only) for their own policies. Sensitive administrative events are hidden.

==============================================================================

KEY PATTERNS:

1. Multi-Tenant Isolation:
   - All policies check: u.organization_id = table.organization_id

2. Soft Delete Handling:
   - Regular users: deleted_at IS NULL
   - Admin/Super Admin: No deleted_at filter

3. Agent Assignment:
   - JOIN agent_customer_assignments for validation
   - Check: aca.is_active = true

4. Cascading Access:
   - Child tables JOIN parent policy table
   - Check: p.deleted_at IS NULL (parent not deleted)

5. Customer Self-Service:
   - Check: u.id = policies.customer_id
   - SELECT only, no INSERT/UPDATE/DELETE

==============================================================================