"""refine permission catalogue, campaign ACLs and system-role model

Revision ID: a0b1c2d3e4f5
Revises: 9d0e1f2a3b4c
Create Date: 2026-06-15 10:30:00.000000
"""
from __future__ import annotations

from datetime import datetime, timezone
import json
from uuid import uuid4

from alembic import op
import sqlalchemy as sa

revision = "a0b1c2d3e4f5"
down_revision = "9d0e1f2a3b4c"
branch_labels = None
depends_on = None

TENANT_ROLE_PERMISSIONS = {
    "owner": ["tenant:*"],
    "tenant_admin": [
        "campaign:read", "files:read", "reports:read", "audit:read",
        "admin:users:read", "admin:users:create", "admin:users:update", "admin:users:suspend",
        "admin:groups:read", "admin:groups:write", "admin:groups:manage_members",
        "admin:roles:read", "admin:roles:write", "admin:roles:assign",
        "admin:api_keys:read", "admin:api_keys:create", "admin:api_keys:revoke",
        "admin:settings:read", "admin:settings:write", "admin:policies:read", "admin:policies:write",
    ],
    "admin": ["tenant:*"],
    "access_admin": [
        "admin:users:read", "admin:users:create", "admin:users:update", "admin:users:suspend",
        "admin:groups:read", "admin:groups:manage_members", "admin:roles:read", "admin:roles:assign",
    ],
    "campaign_manager": [
        "campaign:read", "campaign:create", "campaign:update", "campaign:copy", "campaign:validate", "campaign:build",
        "recipients:read", "recipients:write", "recipients:import", "files:read", "files:download", "files:upload", "files:organize", "reports:read",
    ],
    "reviewer": ["campaign:read", "campaign:validate", "campaign:review", "recipients:read", "files:read", "reports:read"],
    "sender": [
        "campaign:read", "campaign:send_test", "campaign:queue", "campaign:control", "campaign:send", "campaign:retry", "campaign:reconcile",
        "recipients:read", "files:read", "reports:read", "reports:send", "mail_servers:use", "mail_servers:test",
    ],
    "file_manager": ["files:read", "files:download", "files:upload", "files:organize", "files:share", "files:delete"],
    "viewer": ["campaign:read", "recipients:read", "files:read", "reports:read"],
    "auditor": ["campaign:read", "recipients:read", "recipients:export", "reports:read", "reports:export", "audit:read"],
}

SYSTEM_ALL = [
    "system:tenants:read", "system:tenants:create", "system:tenants:update", "system:tenants:suspend",
    "system:accounts:read", "system:accounts:create", "system:accounts:update", "system:accounts:suspend",
    "system:roles:read", "system:roles:write", "system:roles:assign",
    "system:access:read", "system:access:assign",
    "system:audit:read", "system:settings:read", "system:settings:write",
    "system:governance:read", "system:governance:write",
]
SYSTEM_ROLE_PERMISSIONS = {
    "system_owner": ["system:*"],
    "system_admin": SYSTEM_ALL,
    "system_auditor": [
        "system:tenants:read", "system:accounts:read", "system:roles:read", "system:access:read",
        "system:audit:read", "system:settings:read", "system:governance:read",
    ],
}

# Some names were canonical in the old catalogue but represented a wider bundle.
# Expand every existing role once during upgrade; runtime authorization then treats
# the new canonical names narrowly.
LEGACY_EXPANSIONS = {
    "campaign:write": {
        "campaign:create", "campaign:update", "campaign:copy", "campaign:archive", "campaign:delete", "campaign:share",
        "recipients:read", "recipients:write", "recipients:import",
    },
    "campaign:queue": {"campaign:queue", "campaign:control", "campaign:retry"},
    "campaign:send": {"campaign:send", "campaign:reconcile"},
    "attachments:read": {"files:read", "files:download"},
    "attachments:write": {"files:upload", "files:organize", "files:share", "files:delete"},
    "reports:read": {"reports:read", "reports:export"},
    "admin:users": {
        "admin:users:read", "admin:users:create", "admin:users:update", "admin:users:suspend",
        "admin:groups:read", "admin:groups:write", "admin:groups:manage_members",
        "admin:roles:read", "admin:roles:write", "admin:roles:assign",
    },
    "admin:users:write": {"admin:users:create", "admin:users:update", "admin:users:suspend", "admin:roles:assign", "admin:groups:manage_members"},
    "admin:groups:write": {"admin:groups:write", "admin:groups:manage_members", "admin:roles:assign"},
    "admin:roles:write": {"admin:roles:write", "admin:roles:assign"},
    "admin:api_keys:write": {"admin:api_keys:create", "admin:api_keys:revoke"},
    "admin:settings": {"admin:settings:read", "admin:settings:write", "admin:api_keys:read", "admin:api_keys:create", "admin:api_keys:revoke"},
    "system:tenants:write": {"system:tenants:create", "system:tenants:update", "system:tenants:suspend"},
    "system:access:read": {"system:access:read", "system:accounts:read", "system:roles:read"},
    "system:access:write": {"system:access:assign", "system:roles:assign", "system:accounts:create", "system:accounts:update", "system:accounts:suspend"},
}


def _json(value: list[str]) -> str:
    return json.dumps(value, separators=(",", ":"))


def _decode(value) -> list[str]:
    if value is None:
        return []
    if isinstance(value, list):
        return [str(item) for item in value]
    if isinstance(value, str):
        try:
            decoded = json.loads(value)
            return [str(item) for item in decoded] if isinstance(decoded, list) else []
        except json.JSONDecodeError:
            return []
    return []


def _expand_legacy(scopes: list[str]) -> list[str]:
    expanded: set[str] = set()
    for scope in scopes:
        replacement = LEGACY_EXPANSIONS.get(scope)
        if replacement:
            expanded.update(replacement)
        else:
            expanded.add(scope)
    return sorted(expanded)


def upgrade() -> None:
    bind = op.get_bind()
    inspector = sa.inspect(bind)
    tables = set(inspector.get_table_names())
    if "campaigns" in tables:
        columns = {col["name"] for col in inspector.get_columns("campaigns")}
        if "owner_user_id" not in columns:
            op.add_column("campaigns", sa.Column("owner_user_id", sa.String(length=36), nullable=True))
            op.create_index("ix_campaigns_owner_user_id", "campaigns", ["owner_user_id"])
        if "owner_group_id" not in columns:
            op.add_column("campaigns", sa.Column("owner_group_id", sa.String(length=36), nullable=True))
            op.create_index("ix_campaigns_owner_group_id", "campaigns", ["owner_group_id"])
        bind.execute(sa.text("UPDATE campaigns SET owner_user_id = created_by_user_id WHERE owner_user_id IS NULL"))
    if "campaign_shares" not in tables:
        op.create_table(
            "campaign_shares",
            sa.Column("id", sa.String(length=36), nullable=False),
            sa.Column("tenant_id", sa.String(length=36), nullable=False),
            sa.Column("campaign_id", sa.String(length=36), nullable=False),
            sa.Column("target_type", sa.String(length=20), nullable=False),
            sa.Column("target_id", sa.String(length=36), nullable=False),
            sa.Column("permission", sa.String(length=20), nullable=False, server_default="read"),
            sa.Column("created_by_user_id", sa.String(length=36), nullable=True),
            sa.Column("revoked_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
            sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
            sa.ForeignKeyConstraint(["tenant_id"], ["tenancy_tenants.id"], ondelete="CASCADE"),
            sa.ForeignKeyConstraint(["campaign_id"], ["campaigns.id"], ondelete="CASCADE"),
            sa.ForeignKeyConstraint(["created_by_user_id"], ["access_users.id"], ondelete="SET NULL"),
            sa.PrimaryKeyConstraint("id"),
            sa.UniqueConstraint("campaign_id", "target_type", "target_id", name="uq_campaign_share_target"),
        )
        op.create_index("ix_campaign_shares_tenant_id", "campaign_shares", ["tenant_id"])
        op.create_index("ix_campaign_shares_campaign_id", "campaign_shares", ["campaign_id"])
        op.create_index("ix_campaign_shares_target_type", "campaign_shares", ["target_type"])
        op.create_index("ix_campaign_shares_target_id", "campaign_shares", ["target_id"])
        op.create_index("ix_campaign_shares_created_by_user_id", "campaign_shares", ["created_by_user_id"])
        op.create_index("ix_campaign_shares_revoked_at", "campaign_shares", ["revoked_at"])
    if "access_roles" not in tables:
        return

    rows = bind.execute(sa.text("SELECT id, permissions FROM access_roles")).mappings().all()
    for row in rows:
        bind.execute(
            sa.text("UPDATE access_roles SET permissions = :permissions WHERE id = :id"),
            {"id": row["id"], "permissions": _json(_expand_legacy(_decode(row["permissions"])))},
        )

    for slug, permissions in TENANT_ROLE_PERMISSIONS.items():
        bind.execute(
            sa.text("UPDATE access_roles SET permissions = :permissions WHERE tenant_id IS NOT NULL AND slug = :slug AND is_builtin = TRUE"),
            {"slug": slug, "permissions": _json(permissions)},
        )

    now = datetime.now(timezone.utc)
    for slug, permissions in SYSTEM_ROLE_PERMISSIONS.items():
        existing = bind.execute(
            sa.text("SELECT id FROM access_roles WHERE tenant_id IS NULL AND slug = :slug"), {"slug": slug}
        ).first()
        is_protected = slug == "system_owner"
        if existing:
            bind.execute(
                sa.text(
                    "UPDATE access_roles SET permissions = :permissions, is_builtin = :is_builtin, is_assignable = TRUE "
                    "WHERE tenant_id IS NULL AND slug = :slug"
                ),
                {"slug": slug, "permissions": _json(permissions), "is_builtin": is_protected},
            )
        else:
            names = {
                "system_owner": ("System owner", "Protected full instance-wide administration."),
                "system_admin": ("System administrator", "Manage the instance without granting the protected System owner role."),
                "system_auditor": ("System auditor", "Read-only access to system administration and audit."),
            }
            name, description = names[slug]
            bind.execute(
                sa.text(
                    "INSERT INTO access_roles (id, tenant_id, slug, name, description, permissions, is_builtin, is_assignable, "
                    "system_template_id, system_required, created_at, updated_at) "
                    "VALUES (:id, NULL, :slug, :name, :description, :permissions, :is_builtin, TRUE, NULL, FALSE, :created_at, :updated_at)"
                ),
                {
                    "id": str(uuid4()), "slug": slug, "name": name, "description": description,
                    "permissions": _json(permissions), "is_builtin": is_protected,
                    "created_at": now, "updated_at": now,
                },
            )


def downgrade() -> None:
    # ACL/evidence-bearing columns are intentionally retained on downgrade.
    pass
