#!/usr/bin/env python3
from __future__ import annotations

import argparse
import re
import sys
from pathlib import Path

ROOT = Path(__file__).resolve().parents[1]
sys.path.insert(0, str(ROOT / "src"))

from nf_nacional_web.db import MysqlCli, sql_quote


TENANT_TABLE_RE = re.compile(r"^nfse_(documentos|eventos)_\d+$|^nfse_documento_partes_\d+$")


def quote_ident(name: str) -> str:
    if not re.fullmatch(r"[A-Za-z0-9_]+", name):
        raise ValueError(f"Nome SQL inseguro: {name}")
    return "`" + name + "`"


def parse_args() -> argparse.Namespace:
    parser = argparse.ArgumentParser(
        description="Remove municipios, certificados, estado de sync e tabelas tenant para reiniciar o cadastro por SIAFI."
    )
    parser.add_argument("--yes", action="store_true", help="Confirma a limpeza destrutiva.")
    return parser.parse_args()


def column_exists(db: MysqlCli, table: str, column: str) -> bool:
    rows = db.query_rows(f"SHOW COLUMNS FROM {quote_ident(table)} LIKE {sql_quote(column)};")
    return bool(rows)


def index_exists(db: MysqlCli, table: str, index_name: str) -> bool:
    rows = db.query_rows(
        f"""
SELECT COUNT(*)
FROM information_schema.statistics
WHERE table_schema = DATABASE()
  AND table_name = {sql_quote(table)}
  AND index_name = {sql_quote(index_name)};
"""
    )
    return bool(rows and int(rows[0][0]) > 0)


def tenant_tables(db: MysqlCli) -> list[str]:
    rows = db.query_rows(
        """
SELECT table_name
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_type = 'BASE TABLE'
ORDER BY table_name;
"""
    )
    tables = [row[0] for row in rows if row and TENANT_TABLE_RE.fullmatch(row[0])]
    def order_key(table: str) -> int:
        if table.startswith("nfse_eventos_"):
            return 0
        if table.startswith("nfse_documento_partes_"):
            return 1
        return 2
    return sorted(tables, key=order_key)


def ensure_siafi_schema(db: MysqlCli) -> None:
    if not column_exists(db, "nfse_municipios", "codigo_siafi"):
        db.execute("ALTER TABLE nfse_municipios ADD COLUMN codigo_siafi VARCHAR(10) NULL AFTER codigo_ibge;")
        db.execute("ALTER TABLE nfse_municipios MODIFY COLUMN codigo_siafi VARCHAR(10) NOT NULL;")
    if not index_exists(db, "nfse_municipios", "uk_nfse_municipios_siafi_ambiente_perfil"):
        db.execute(
            """
ALTER TABLE nfse_municipios
  ADD UNIQUE KEY uk_nfse_municipios_siafi_ambiente_perfil (codigo_siafi, ambiente, perfil);
"""
        )

    if column_exists(db, "nfse_tenant_table_versions", "codigo_ibge"):
        if index_exists(db, "nfse_tenant_table_versions", "uk_nfse_tenant_table_versions_codigo_version"):
            db.execute("ALTER TABLE nfse_tenant_table_versions DROP KEY uk_nfse_tenant_table_versions_codigo_version;")
        if not column_exists(db, "nfse_tenant_table_versions", "codigo_siafi"):
            db.execute(
                """
ALTER TABLE nfse_tenant_table_versions
  CHANGE COLUMN codigo_ibge codigo_siafi VARCHAR(10) NOT NULL;
"""
            )
    elif not column_exists(db, "nfse_tenant_table_versions", "codigo_siafi"):
        db.execute("ALTER TABLE nfse_tenant_table_versions ADD COLUMN codigo_siafi VARCHAR(10) NOT NULL AFTER id;")

    if not index_exists(db, "nfse_tenant_table_versions", "uk_nfse_tenant_table_versions_siafi_version"):
        db.execute(
            """
ALTER TABLE nfse_tenant_table_versions
  ADD UNIQUE KEY uk_nfse_tenant_table_versions_siafi_version (codigo_siafi, table_version);
"""
        )


def main() -> int:
    args = parse_args()
    if not args.yes:
        print("Limpeza destrutiva bloqueada. Rode novamente com --yes para confirmar.")
        return 2

    db = MysqlCli.from_env()
    tables = tenant_tables(db)

    print(f"Banco: {db.database}")
    print(f"Tabelas tenant encontradas: {len(tables)}")
    for table in tables:
        print(f"  drop {table}")

    drop_sql = "\n".join(f"DROP TABLE IF EXISTS {quote_ident(table)};" for table in tables)
    db.execute(
        f"""
SET FOREIGN_KEY_CHECKS=0;
{drop_sql}
DELETE FROM nfse_sync_logs;
DELETE FROM nfse_certificados;
DELETE FROM nfse_sync_state;
DELETE FROM nfse_municipios;
DELETE FROM nfse_tenant_table_versions;
SET FOREIGN_KEY_CHECKS=1;
"""
    )
    ensure_siafi_schema(db)
    print("Limpeza concluida. Cadastre os municipios novamente pelo painel usando IBGE e SIAFI.")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())