"""Create PostgreSQL RLS policies for row-level security on contacts.

Revision ID: 0052
Revises: 0051
Create Date: 2026-07-29

This migration enables PostgreSQL Row-Level Security on the contacts table
and creates policies that enforce visibility based on:
1. System admin sees everything
2. Owner sees own rows
3. Tenant-owned (owner_id IS NULL) visible to all
4. Shared via entity_permissions
"""

from alembic import op

revision = "0052"
down_revision = "0051"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # Enable RLS on contacts table
    op.execute("ALTER TABLE contacts ENABLE ROW LEVEL SECURITY")
    
    # Policy: System admin sees everything
    op.execute("""
        CREATE POLICY contacts_admin_visible ON contacts
        FOR ALL
        USING (current_setting('app.is_system_admin', true) = 'true')
    """)
    
    # Policy: Owner sees own rows
    op.execute("""
        CREATE POLICY contacts_owner_visible ON contacts
        FOR ALL
        USING (
            owner_id::text = current_setting('app.current_user_id', true)
        )
    """)
    
    # Policy: Tenant-owned (owner_id IS NULL) visible to all in tenant
    op.execute("""
        CREATE POLICY contacts_tenant_owned_visible ON contacts
        FOR ALL
        USING (owner_id IS NULL)
    """)
    
    # Policy: Shared via entity_permissions
    op.execute("""
        CREATE POLICY contacts_shared_visible ON contacts
        FOR ALL
        USING (
            EXISTS (
                SELECT 1 FROM entity_permissions ep
                WHERE ep.entity_type = 'contact'
                AND ep.entity_id = contacts.id
                AND ep.tenant_id = contacts.tenant_id
                AND ep.permission_level != 'none'
                AND (
                    ep.expires_at IS NULL OR ep.expires_at > NOW()
                )
                AND (
                    (ep.principal_type = 'user' 
                     AND ep.principal_id::text = current_setting('app.current_user_id', true))
                    OR
                    (ep.principal_type = 'group'
                     AND ep.principal_id::text = ANY(
                         string_to_array(current_setting('app.current_user_groups', true), ',')
                     ))
                    OR
                    (ep.principal_type = 'role'
                     AND ep.principal_id IN (
                         SELECT ut.role_id FROM user_tenants ut
                         WHERE ut.user_id::text = current_setting('app.current_user_id', true)
                         AND ut.tenant_id = contacts.tenant_id
                     ))
                )
            )
        )
    """)


def downgrade() -> None:
    op.execute("DROP POLICY IF EXISTS contacts_shared_visible ON contacts")
    op.execute("DROP POLICY IF EXISTS contacts_tenant_owned_visible ON contacts")
    op.execute("DROP POLICY IF EXISTS contacts_owner_visible ON contacts")
    op.execute("DROP POLICY IF EXISTS contacts_admin_visible ON contacts")
    op.execute("ALTER TABLE contacts DISABLE ROW LEVEL SECURITY")