"""ai_tool_applications — Application for Introduction of AI Function/Products.

The Feishu template (flow_key=ai_tool_application_request,
external_code configured per-integration) collects:
    AI (name), How to use (multi-select), Data Level (multi-select),
    Purpose, Data type, Privacy Policy, Attachment.

Routes through the standard approval pipeline. On approval an entry
is published on the Approved AI Tools page that is visible to all
authenticated employees.

Legacy backfill columns (legacy_request_no, source_id, legacy
applicant fields) let us import the pre-Helix Feishu records via the
108_seed_approved_ai_tools migration without rewriting actor linkage.

Revision ID: 107
"""
from __future__ import annotations

import sqlalchemy as sa
from alembic import op


revision = "107"
down_revision = "106"
branch_labels = None
depends_on = None


def _fk_source_col_type(bind, target_table: str, target_column: str, *, length: int = 36):
    """See migrations 098 / 101–104 — mirror the FK target's
    DATA_TYPE + COLLATION so TiDB / MySQL 8 don't reject the
    constraint with error 3780."""
    row = bind.execute(
        sa.text(
            "SELECT DATA_TYPE, COLLATION_NAME FROM "
            "information_schema.COLUMNS "
            "WHERE TABLE_SCHEMA = DATABASE() "
            "  AND TABLE_NAME = :t AND COLUMN_NAME = :c"
        ),
        {"t": target_table, "c": target_column},
    ).fetchone()
    data_type = (row[0] or "char").lower() if row else "char"
    collation = row[1] if row else None
    type_cls = sa.CHAR if data_type == "char" else sa.String
    return type_cls(length, collation=collation) if collation else type_cls(length)


def upgrade() -> None:
    bind = op.get_bind()
    persons_id = _fk_source_col_type(bind, "identity_persons", "id")
    approval_requests_id = _fk_source_col_type(bind, "approval_requests", "id")
    op.create_table(
        "ai_tool_applications",
        sa.Column("id", sa.String(36), primary_key=True),
        # Nullable because legacy backfill rows from the Feishu xlsx
        # may reference applicants who haven't yet HRIS-synced into
        # identity_persons (esp. on local dev seeds).
        sa.Column("person_id", persons_id, nullable=True),
        sa.Column("ai_name", sa.String(255), nullable=False),
        sa.Column("how_to_use", sa.String(255), nullable=False),
        sa.Column("data_level", sa.String(64), nullable=False),
        sa.Column("purpose", sa.Text, nullable=False),
        sa.Column("data_type", sa.Text, nullable=False),
        sa.Column("privacy_policy", sa.Text, nullable=False),
        sa.Column("attachment_note", sa.String(255), nullable=True),
        # State machine: pending → approved / rejected / cancelled.
        # The Feishu "已撤回" (withdrawn) folds into ``cancelled``.
        sa.Column("status", sa.String(32), nullable=False, server_default="pending"),
        sa.Column("feishu_instance_code", sa.String(64), nullable=True),
        sa.Column("approval_request_id", approval_requests_id, nullable=True),
        sa.Column("granted_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("legacy_request_no", sa.String(32), nullable=True),
        sa.Column("legacy_applicant_name", sa.String(128), nullable=True),
        sa.Column("legacy_applicant_department", sa.String(128), nullable=True),
        sa.Column("source_id", sa.String(255), nullable=True),
        sa.Column(
            "created_at", sa.DateTime(timezone=True),
            server_default=sa.func.now(), nullable=False,
        ),
        sa.Column(
            "updated_at", sa.DateTime(timezone=True),
            server_default=sa.func.now(), nullable=False,
        ),
        sa.ForeignKeyConstraint(
            ["person_id"], ["identity_persons.id"],
            name="fk_ai_tool_applications_person",
        ),
        sa.ForeignKeyConstraint(
            ["approval_request_id"], ["approval_requests.id"],
            name="fk_ai_tool_applications_approval_request",
            ondelete="SET NULL",
        ),
        sa.Index(
            "ix_ai_tool_applications_status_granted",
            "status", "granted_at",
        ),
        sa.Index("ix_ai_tool_applications_person", "person_id", "status"),
        sa.Index(
            "ix_ai_tool_applications_feishu_instance",
            "feishu_instance_code",
            unique=True,
            mysql_length={"feishu_instance_code": 64},
        ),
        # source_id from the Feishu xlsx is the dedup key for the
        # backfill seed — we want to be able to re-run 106 idempotently.
        sa.Index(
            "ix_ai_tool_applications_source",
            "source_id",
            unique=True,
            mysql_length={"source_id": 191},
        ),
    )


def downgrade() -> None:
    op.drop_table("ai_tool_applications")