"""Add Row-Level Security policies for multi-tenant isolation

Revision ID: 004_add_rls_policies
Revises: 003_add_settings_and_master_deck_fields
Create Date: 2026-02-27 18:30:00.000000

This migration implements database-level multi-tenant isolation using PostgreSQL
Row-Level Security (RLS) policies. This provides defense-in-depth security so that
even if application-level filtering fails, data remains isolated at the DB layer.
"""

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '004_add_rls_policies'
down_revision = '003_add_settings_and_master_deck_fields'
branch_labels = None
depends_on = None


def upgrade():
    """Enable RLS and create policies for client-scoped tables."""

    # Enable RLS on client-scoped tables
    op.execute("ALTER TABLE presentations ENABLE ROW LEVEL SECURITY;")
    op.execute("ALTER TABLE master_decks ENABLE ROW LEVEL SECURITY;")
    op.execute("ALTER TABLE brand_configs ENABLE ROW LEVEL SECURITY;")
    op.execute("ALTER TABLE slides ENABLE ROW LEVEL SECURITY;")
    op.execute("ALTER TABLE templates ENABLE ROW LEVEL SECURITY;")

    # Policy for presentations table
    op.execute("""
        CREATE POLICY presentation_client_isolation ON presentations
        FOR ALL
        USING (
            -- Super admin sees everything
            current_setting('app.user_role', true) = 'super_admin'
            OR
            -- Client admin sees only accessible clients via team memberships
            client_id IN (
                SELECT DISTINCT t.client_id
                FROM teams t
                JOIN team_memberships tm ON tm.team_id = t.id
                WHERE tm.user_id = current_setting('app.current_user_id', true)::uuid
            )
            OR
            -- Users see their own presentations
            owner_id = current_setting('app.current_user_id', true)::uuid
            OR
            -- Allow NULL client_id (for backward compatibility)
            client_id IS NULL
        );
    """)

    # Policy for master_decks table
    op.execute("""
        CREATE POLICY master_deck_client_isolation ON master_decks
        FOR ALL
        USING (
            current_setting('app.user_role', true) = 'super_admin'
            OR
            client_id IN (
                SELECT DISTINCT t.client_id
                FROM teams t
                JOIN team_memberships tm ON tm.team_id = t.id
                WHERE tm.user_id = current_setting('app.current_user_id', true)::uuid
            )
            OR
            client_id IS NULL
        );
    """)

    # Policy for brand_configs table
    op.execute("""
        CREATE POLICY brand_config_client_isolation ON brand_configs
        FOR ALL
        USING (
            current_setting('app.user_role', true) = 'super_admin'
            OR
            client_id IN (
                SELECT DISTINCT t.client_id
                FROM teams t
                JOIN team_memberships tm ON tm.team_id = t.id
                WHERE tm.user_id = current_setting('app.current_user_id', true)::uuid
            )
        );
    """)

    # Policy for slides table (inherits from presentation access)
    op.execute("""
        CREATE POLICY slide_presentation_isolation ON slides
        FOR ALL
        USING (
            presentation IN (
                SELECT id FROM presentations
                -- presentations table RLS will apply here
            )
        );
    """)

    # Policy for templates table
    op.execute("""
        CREATE POLICY template_client_isolation ON templates
        FOR ALL
        USING (
            current_setting('app.user_role', true) = 'super_admin'
            OR
            client_id IN (
                SELECT DISTINCT t.client_id
                FROM teams t
                JOIN team_memberships tm ON tm.team_id = t.id
                WHERE tm.user_id = current_setting('app.current_user_id', true)::uuid
            )
            OR
            client_id IS NULL
        );
    """)

    print("✅ RLS policies created successfully")
    print("⚠️  IMPORTANT: Update AuthMiddleware to set session variables:")
    print("   - app.current_user_id")
    print("   - app.user_role")


def downgrade():
    """Remove RLS policies and disable RLS."""

    # Drop policies
    op.execute("DROP POLICY IF EXISTS presentation_client_isolation ON presentations;")
    op.execute("DROP POLICY IF EXISTS master_deck_client_isolation ON master_decks;")
    op.execute("DROP POLICY IF EXISTS brand_config_client_isolation ON brand_configs;")
    op.execute("DROP POLICY IF EXISTS slide_presentation_isolation ON slides;")
    op.execute("DROP POLICY IF EXISTS template_client_isolation ON templates;")

    # Disable RLS
    op.execute("ALTER TABLE presentations DISABLE ROW LEVEL SECURITY;")
    op.execute("ALTER TABLE master_decks DISABLE ROW LEVEL SECURITY;")
    op.execute("ALTER TABLE brand_configs DISABLE ROW LEVEL SECURITY;")
    op.execute("ALTER TABLE slides DISABLE ROW LEVEL SECURITY;")
    op.execute("ALTER TABLE templates DISABLE ROW LEVEL SECURITY;")

    print("✅ RLS policies removed")
