"""Initial schema: tenants, agents, channel_connections, portal_users with RLS

Revision ID: 001
Revises:
Create Date: 2026-03-23

This migration:
1. Creates the konstruct_app application role (non-superuser)
2. Creates all four tables matching the SQLAlchemy models
3. Applies Row Level Security (RLS) with FORCE ROW LEVEL SECURITY to
   tenant-scoped tables (agents, channel_connections)
4. Creates RLS policies that scope rows to app.current_tenant session variable
5. Grants appropriate permissions to konstruct_app role

CRITICAL: FORCE ROW LEVEL SECURITY is applied to agents and channel_connections.
This means even the table owner cannot bypass RLS. The integration test
`test_tenant_isolation.py` must verify this is in effect.
"""

from __future__ import annotations

from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects.postgresql import UUID

# revision identifiers, used by Alembic.
revision: str = "001"
down_revision: Union[str, None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None

# All valid channel types — kept in sync with ChannelType StrEnum in message.py
_CHANNEL_TYPES = ("slack", "whatsapp", "mattermost", "rocketchat", "teams", "telegram", "signal")


def upgrade() -> None:
    # -------------------------------------------------------------------------
    # 1. Create application role (idempotent)
    # -------------------------------------------------------------------------
    op.execute("""
        DO $$
        BEGIN
            IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'konstruct_app') THEN
                CREATE ROLE konstruct_app WITH LOGIN PASSWORD 'konstruct_dev';
            END IF;
        END
        $$
    """)

    op.execute("GRANT USAGE ON SCHEMA public TO konstruct_app")

    # -------------------------------------------------------------------------
    # 2. Create channel_type ENUM (using raw SQL to avoid SQLAlchemy auto-emit)
    # We use op.execute with raw DDL so SQLAlchemy does NOT auto-emit
    # a second CREATE TYPE statement in create_table below.
    # -------------------------------------------------------------------------
    op.execute(
        "CREATE TYPE channel_type_enum AS ENUM "
        "('slack', 'whatsapp', 'mattermost', 'rocketchat', 'teams', 'telegram', 'signal')"
    )

    # -------------------------------------------------------------------------
    # 3. Create tenants table (no RLS — platform admin needs full visibility)
    # -------------------------------------------------------------------------
    op.create_table(
        "tenants",
        sa.Column("id", UUID(as_uuid=True), primary_key=True, server_default=sa.text("gen_random_uuid()")),
        sa.Column("name", sa.String(255), nullable=False, unique=True),
        sa.Column("slug", sa.String(100), nullable=False, unique=True),
        sa.Column("settings", sa.JSON, nullable=False, server_default=sa.text("'{}'")),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("NOW()")),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("NOW()"),
        ),
    )
    op.create_index("ix_tenants_slug", "tenants", ["slug"])

    # -------------------------------------------------------------------------
    # 4. Create agents table with RLS
    # -------------------------------------------------------------------------
    op.create_table(
        "agents",
        sa.Column("id", UUID(as_uuid=True), primary_key=True, server_default=sa.text("gen_random_uuid()")),
        sa.Column("tenant_id", UUID(as_uuid=True), sa.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False),
        sa.Column("name", sa.String(255), nullable=False),
        sa.Column("role", sa.String(255), nullable=False),
        sa.Column("persona", sa.Text, nullable=False, server_default=sa.text("''")),
        sa.Column("system_prompt", sa.Text, nullable=False, server_default=sa.text("''")),
        sa.Column("model_preference", sa.String(50), nullable=False, server_default=sa.text("'quality'")),
        sa.Column("tool_assignments", sa.JSON, nullable=False, server_default=sa.text("'[]'")),
        sa.Column("escalation_rules", sa.JSON, nullable=False, server_default=sa.text("'[]'")),
        sa.Column("is_active", sa.Boolean, nullable=False, server_default=sa.text("TRUE")),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("NOW()")),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("NOW()"),
        ),
    )
    op.create_index("ix_agents_tenant_id", "agents", ["tenant_id"])

    # Apply RLS to agents — FORCE ensures even table owner cannot bypass
    op.execute("ALTER TABLE agents ENABLE ROW LEVEL SECURITY")
    op.execute("ALTER TABLE agents FORCE ROW LEVEL SECURITY")
    op.execute("""
        CREATE POLICY tenant_isolation ON agents
        USING (tenant_id = current_setting('app.current_tenant', TRUE)::uuid)
    """)

    # -------------------------------------------------------------------------
    # 5. Create channel_connections table with RLS
    # Use sa.Text for channel_type column — cast to enum_type in app code.
    # The channel_type_enum was created above via raw DDL.
    # We reference it here using sa.text cast to avoid SQLAlchemy auto-emit.
    # -------------------------------------------------------------------------
    op.create_table(
        "channel_connections",
        sa.Column("id", UUID(as_uuid=True), primary_key=True, server_default=sa.text("gen_random_uuid()")),
        sa.Column("tenant_id", UUID(as_uuid=True), sa.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False),
        sa.Column(
            "channel_type",
            sa.Text,  # Stored as text, constrained by CHECK to valid enum values
            nullable=False,
        ),
        sa.Column("workspace_id", sa.String(255), nullable=False),
        sa.Column("config", sa.JSON, nullable=False, server_default=sa.text("'{}'")),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("NOW()")),
        sa.UniqueConstraint("channel_type", "workspace_id", name="uq_channel_workspace"),
    )
    op.create_index("ix_channel_connections_tenant_id", "channel_connections", ["tenant_id"])

    # Add CHECK constraint to enforce valid channel types (reuses enum values)
    op.execute(
        "ALTER TABLE channel_connections ADD CONSTRAINT chk_channel_type "
        f"CHECK (channel_type IN {tuple(_CHANNEL_TYPES)})"
    )

    # Apply RLS to channel_connections
    op.execute("ALTER TABLE channel_connections ENABLE ROW LEVEL SECURITY")
    op.execute("ALTER TABLE channel_connections FORCE ROW LEVEL SECURITY")
    op.execute("""
        CREATE POLICY tenant_isolation ON channel_connections
        USING (tenant_id = current_setting('app.current_tenant', TRUE)::uuid)
    """)

    # -------------------------------------------------------------------------
    # 6. Create portal_users table (no RLS — auth happens before tenant context)
    # -------------------------------------------------------------------------
    op.create_table(
        "portal_users",
        sa.Column("id", UUID(as_uuid=True), primary_key=True, server_default=sa.text("gen_random_uuid()")),
        sa.Column("email", sa.String(255), nullable=False, unique=True),
        sa.Column("hashed_password", sa.String(255), nullable=False),
        sa.Column("name", sa.String(255), nullable=False),
        sa.Column("is_admin", sa.Boolean, nullable=False, server_default=sa.text("FALSE")),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("NOW()")),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("NOW()"),
        ),
    )
    op.create_index("ix_portal_users_email", "portal_users", ["email"])

    # -------------------------------------------------------------------------
    # 7. Grant table permissions to konstruct_app role
    # -------------------------------------------------------------------------
    op.execute("GRANT SELECT, INSERT, UPDATE, DELETE ON tenants TO konstruct_app")
    op.execute("GRANT SELECT, INSERT, UPDATE, DELETE ON agents TO konstruct_app")
    op.execute("GRANT SELECT, INSERT, UPDATE, DELETE ON channel_connections TO konstruct_app")
    op.execute("GRANT SELECT, INSERT, UPDATE, DELETE ON portal_users TO konstruct_app")


def downgrade() -> None:
    # Revoke grants
    op.execute("REVOKE ALL ON portal_users FROM konstruct_app")
    op.execute("REVOKE ALL ON channel_connections FROM konstruct_app")
    op.execute("REVOKE ALL ON agents FROM konstruct_app")
    op.execute("REVOKE ALL ON tenants FROM konstruct_app")

    # Drop tables
    op.drop_table("portal_users")
    op.drop_table("channel_connections")
    op.drop_table("agents")
    op.drop_table("tenants")

    # Drop enum type
    op.execute("DROP TYPE IF EXISTS channel_type_enum")
