"""
Migrations leves do schema MySQL (sem Alembic).

O código versionado cria/atualiza a estrutura do banco. Os dados operacionais
(kanban, jobs, sessões) precisam ser exportados da máquina de origem — veja
`scripts/transferir_banco.py`.
"""

from __future__ import annotations

from typing import Callable

from sqlalchemy import inspect, text
from sqlalchemy.engine import Engine

from app.time_utils import agora_local

MigrationFn = Callable[[Engine], None]

MIGRATIONS: list[tuple[str, str, MigrationFn]] = [
    (
        "001",
        "Tabelas de autenticação (sessões e lembre-me)",
        "_migration_001_auth_tables",
    ),
    (
        "002",
        "Tabelas da aplicação (jobs, kanban)",
        "_migration_002_app_tables",
    ),
    (
        "003",
        "Colunas extras do kanban_cards",
        "_migration_003_kanban_cards_columns",
    ),
    (
        "004",
        "Schema kanban_colunas com slug",
        "_migration_004_kanban_colunas_schema",
    ),
    (
        "005",
        "Etapas padrão do Kanban",
        "_migration_005_kanban_etapas_seed",
    ),
]


def _migration_000_schema_migrations(engine: Engine) -> None:
    with engine.begin() as conn:
        conn.execute(
            text(
                """
                CREATE TABLE IF NOT EXISTS schema_migrations (
                    id VARCHAR(16) NOT NULL PRIMARY KEY,
                    descricao VARCHAR(255) NOT NULL DEFAULT '',
                    aplicada_em DATETIME NOT NULL
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
                """
            )
        )


def _migration_001_auth_tables(engine: Engine) -> None:
    with engine.begin() as conn:
        conn.execute(
            text(
                """
                CREATE TABLE IF NOT EXISTS sessoes_ativas (
                    usuario_id INT NOT NULL PRIMARY KEY,
                    session_id VARCHAR(128) NOT NULL,
                    atualizado_em DATETIME NULL DEFAULT CURRENT_TIMESTAMP
                        ON UPDATE CURRENT_TIMESTAMP
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
                """
            )
        )
        conn.execute(
            text(
                """
                CREATE TABLE IF NOT EXISTS tokens_lembre_me (
                    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                    usuario_id INT NOT NULL,
                    token VARCHAR(64) NOT NULL,
                    expira_em DATETIME NOT NULL,
                    UNIQUE KEY uq_tokens_lembre_me_token (token),
                    KEY ix_tokens_lembre_me_usuario (usuario_id)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
                """
            )
        )


def _migration_002_app_tables(engine: Engine) -> None:
    from app.db import Base
    from app.db_models import JobModel, KanbanCardModel, KanbanColunaModel  # noqa: F401

    Base.metadata.create_all(bind=engine)


def _migration_003_kanban_cards_columns(engine: Engine) -> None:
    insp = inspect(engine)
    if "kanban_cards" not in insp.get_table_names():
        return

    colunas = {coluna["name"] for coluna in insp.get_columns("kanban_cards")}
    novas_colunas = {
        "responsavel_correcao": "VARCHAR(255) NOT NULL DEFAULT ''",
        "tipo_prova": "VARCHAR(255) NOT NULL DEFAULT ''",
        "data_conclusao": "DATETIME NULL",
        "data_previsao_entrega": "DATETIME NULL",
        "turma": "VARCHAR(255) NOT NULL DEFAULT ''",
        "historico_processamentos": "JSON NULL",
        "etapa_manual": "TINYINT(1) NOT NULL DEFAULT 0",
    }
    with engine.begin() as conn:
        for nome, definicao in novas_colunas.items():
            if nome not in colunas:
                conn.execute(text(f"ALTER TABLE kanban_cards ADD COLUMN {nome} {definicao}"))
        if "serie" in colunas:
            tipo_serie = next(
                (coluna.get("type") for coluna in insp.get_columns("kanban_cards") if coluna["name"] == "serie"),
                None,
            )
            tamanho_atual = getattr(tipo_serie, "length", None) if tipo_serie is not None else None
            if tamanho_atual is not None and tamanho_atual < 512:
                conn.execute(text("ALTER TABLE kanban_cards MODIFY COLUMN serie VARCHAR(512) NOT NULL DEFAULT ''"))


def _migration_004_kanban_colunas_schema(engine: Engine) -> None:
    insp = inspect(engine)
    if "kanban_colunas" not in insp.get_table_names():
        return

    nomes_colunas = {coluna["name"] for coluna in insp.get_columns("kanban_colunas")}
    if "slug" in nomes_colunas:
        return

    with engine.begin() as conn:
        conn.execute(
            text(
                """
                CREATE TABLE kanban_colunas_nova (
                    etapa_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                    slug VARCHAR(64) NOT NULL,
                    titulo VARCHAR(255) NOT NULL DEFAULT '',
                    cor VARCHAR(16) NOT NULL DEFAULT '#788394',
                    ordem INT NOT NULL DEFAULT 0,
                    eh_inicial TINYINT(1) NOT NULL DEFAULT 0,
                    eh_final TINYINT(1) NOT NULL DEFAULT 0,
                    ativo TINYINT(1) NOT NULL DEFAULT 1,
                    criado_em DATETIME NULL,
                    atualizado_em DATETIME NULL,
                    UNIQUE KEY uq_kanban_colunas_slug (slug),
                    KEY ix_kanban_colunas_ordem (ordem)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
                """
            )
        )
        conn.execute(
            text(
                """
                INSERT INTO kanban_colunas_nova
                    (slug, titulo, cor, ordem, eh_inicial, eh_final, ativo, criado_em, atualizado_em)
                SELECT
                    etapa_id, titulo, cor, ordem, eh_inicial, eh_final, ativo, criado_em, atualizado_em
                FROM kanban_colunas
                ORDER BY ordem, etapa_id
                """
            )
        )
        conn.execute(text("DROP TABLE kanban_colunas"))
        conn.execute(text("RENAME TABLE kanban_colunas_nova TO kanban_colunas"))


def _migration_005_kanban_etapas_seed(engine: Engine) -> None:
    from app.kanban import garantir_etapas_padrao

    garantir_etapas_padrao()


_MIGRATION_FUNCS: dict[str, MigrationFn] = {
    "001": _migration_001_auth_tables,
    "002": _migration_002_app_tables,
    "003": _migration_003_kanban_cards_columns,
    "004": _migration_004_kanban_colunas_schema,
    "005": _migration_005_kanban_etapas_seed,
}


def _migracoes_aplicadas(engine: Engine) -> set[str]:
    insp = inspect(engine)
    if "schema_migrations" not in insp.get_table_names():
        return set()

    with engine.connect() as conn:
        linhas = conn.execute(text("SELECT id FROM schema_migrations")).fetchall()
    return {str(linha[0]) for linha in linhas}


def run_migrations(engine: Engine | None = None, *, verbose: bool = False) -> list[str]:
    if engine is None:
        from app.db import ENGINE as engine_local

        engine = engine_local

    if engine is None:
        return []

    _migration_000_schema_migrations(engine)
    aplicadas = _migracoes_aplicadas(engine)
    executadas: list[str] = []

    for migracao_id, descricao, _ in MIGRATIONS:
        if migracao_id in aplicadas:
            continue

        func = _MIGRATION_FUNCS[migracao_id]
        func(engine)

        with engine.begin() as conn:
            conn.execute(
                text(
                    """
                    INSERT INTO schema_migrations (id, descricao, aplicada_em)
                    VALUES (:id, :descricao, :aplicada_em)
                    """
                ),
                {
                    "id": migracao_id,
                    "descricao": descricao,
                    "aplicada_em": agora_local(),
                },
            )

        executadas.append(migracao_id)
        if verbose:
            print(f"[migration] {migracao_id} — {descricao}")

    return executadas


def status_migrations(engine: Engine | None = None) -> dict:
    if engine is None:
        from app.db import ENGINE as engine_local

        engine = engine_local

    if engine is None:
        return {"backend": "file", "aplicadas": [], "pendentes": [mid for mid, _, _ in MIGRATIONS]}

    _migration_000_schema_migrations(engine)
    aplicadas = sorted(_migracoes_aplicadas(engine))
    pendentes = [mid for mid, _, _ in MIGRATIONS if mid not in aplicadas]
    return {
        "backend": "mysql",
        "aplicadas": aplicadas,
        "pendentes": pendentes,
        "total": len(MIGRATIONS),
    }