"""authorization policy engine

Revision ID: 000000000018
Revises: 000000000017
Create Date: 2026-03-31

Creates the policies and policy_evaluation_logs tables for the composable
authorization policy engine.
"""

from alembic import op

revision = "000000000018"
down_revision = "000000000017"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute("""
        CREATE TABLE IF NOT EXISTS policies (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            application_id UUID NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
            name VARCHAR(255) NOT NULL,
            description TEXT,
            effect VARCHAR(10) NOT NULL DEFAULT 'allow',
            conditions JSONB NOT NULL DEFAULT '{}'::jsonb,
            priority INTEGER NOT NULL DEFAULT 0,
            is_active BOOLEAN NOT NULL DEFAULT true,
            metadata JSONB DEFAULT '{}'::jsonb,
            created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
        );
    """)

    # Unique constraint: one policy name per application
    op.execute("""
        CREATE UNIQUE INDEX IF NOT EXISTS uq_policies_app_name
        ON policies (application_id, name);
    """)

    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_policies_app_active
        ON policies (application_id, is_active)
        WHERE is_active = true;
    """)

    op.execute("""
        CREATE TABLE IF NOT EXISTS policy_evaluation_logs (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            policy_id UUID REFERENCES policies(id) ON DELETE SET NULL,
            application_id UUID NOT NULL,
            user_id UUID,
            policy_name VARCHAR(255) NOT NULL,
            decision VARCHAR(10) NOT NULL,
            reasons JSONB NOT NULL DEFAULT '[]'::jsonb,
            evaluation_ms INTEGER NOT NULL DEFAULT 0,
            context_snapshot JSONB,
            created_at TIMESTAMPTZ NOT NULL DEFAULT now()
        );
    """)

    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_policy_eval_logs_app_created
        ON policy_evaluation_logs (application_id, created_at DESC);
    """)

    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_policy_eval_logs_policy_name
        ON policy_evaluation_logs (application_id, policy_name);
    """)


def downgrade() -> None:
    op.execute("DROP TABLE IF EXISTS policy_evaluation_logs;")
    op.execute("DROP TABLE IF EXISTS policies;")