"""Auth RLS policies for password_reset_tokens and audit_log.

Allows crm_auth to:
- SELECT/UPDATE/INSERT on password_reset_tokens (for password reset flow)
- INSERT on audit_log (for audit logging during auth)
- UPDATE on users (for password hash update during reset)

The tenant_isolation policy for crm_api/crm_worker is preserved.
crm_auth gets scoped access without full tenant context for token lookup,
but INSERT/UPDATE on tenant tables still requires tenant context.

Revision ID: 0088
Revises: 0087
"""

from __future__ import annotations

from alembic import op
import sqlalchemy as sa

revision = "0088"
down_revision = "0087"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # ── password_reset_tokens: replace policy for crm_auth access ──
    op.execute("DROP POLICY IF EXISTS password_reset_tokens_tenant_isolation ON public.password_reset_tokens")
    op.execute("DROP POLICY IF EXISTS password_reset_tokens_auth_lookup ON public.password_reset_tokens")
    op.execute("DROP POLICY IF EXISTS password_reset_tokens_auth_update ON public.password_reset_tokens")
    op.execute("DROP POLICY IF EXISTS password_reset_tokens_auth_insert ON public.password_reset_tokens")

    # crm_auth: SELECT without tenant context (token lookup)
    op.execute("""
        CREATE POLICY password_reset_tokens_auth_lookup
        ON public.password_reset_tokens
        FOR SELECT TO crm_auth
        USING (true)
    """)

    # crm_auth: UPDATE without tenant context (mark token used)
    op.execute("""
        CREATE POLICY password_reset_tokens_auth_update
        ON public.password_reset_tokens
        FOR UPDATE TO crm_auth
        USING (true)
        WITH CHECK (true)
    """)

    # crm_auth: INSERT with tenant context (create new token)
    op.execute("""
        CREATE POLICY password_reset_tokens_auth_insert
        ON public.password_reset_tokens
        FOR INSERT TO crm_auth
        WITH CHECK (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid)
    """)

    # crm_api, crm_worker: full tenant isolation
    op.execute("""
        CREATE POLICY password_reset_tokens_tenant_isolation
        ON public.password_reset_tokens
        FOR ALL TO crm_api, crm_worker
        USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid)
        WITH CHECK (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid)
    """)

    # ── Grants for crm_auth ──
    op.execute("GRANT SELECT, INSERT, UPDATE ON public.password_reset_tokens TO crm_auth")
    op.execute("GRANT UPDATE ON public.users TO crm_auth")

    # ── audit_log: allow crm_auth INSERT with tenant context ──
    op.execute("DROP POLICY IF EXISTS audit_log_auth_insert ON public.audit_log")
    op.execute("""
        CREATE POLICY audit_log_auth_insert
        ON public.audit_log
        FOR INSERT TO crm_auth
        WITH CHECK (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid)
    """)
    op.execute("GRANT INSERT ON public.audit_log TO crm_auth")


def downgrade() -> None:
    op.execute("DROP POLICY IF EXISTS password_reset_tokens_auth_lookup ON public.password_reset_tokens")
    op.execute("DROP POLICY IF EXISTS password_reset_tokens_auth_update ON public.password_reset_tokens")
    op.execute("DROP POLICY IF EXISTS password_reset_tokens_auth_insert ON public.password_reset_tokens")
    op.execute("DROP POLICY IF EXISTS audit_log_auth_insert ON public.audit_log")
    op.execute("REVOKE SELECT, INSERT, UPDATE ON public.password_reset_tokens FROM crm_auth")
    op.execute("REVOKE UPDATE ON public.users FROM crm_auth")
    op.execute("REVOKE INSERT ON public.audit_log FROM crm_auth")

    # Restore original tenant isolation policy
    op.execute("""
        CREATE POLICY password_reset_tokens_tenant_isolation
        ON public.password_reset_tokens
        FOR ALL TO crm_api, crm_worker, crm_auth
        USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid)
        WITH CHECK (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid)
    """)