import os
import sys
import pkgutil
import importlib
from dotenv import load_dotenv
from sqlalchemy import create_engine, MetaData

load_dotenv(override=True)

db_url = os.getenv("DATABASE_URL")

if not db_url:
    print("ERRO: DATABASE_URL não encontrada no .env")
    sys.exit(1)

engine = create_engine(db_url)

sys.path.append(os.path.abspath(os.path.dirname(__file__)))

from app.core.database import Base

models_package = "app.models"
models_path = os.path.join(os.path.dirname(__file__), "app", "models")

import_errors = []

for module_info in pkgutil.iter_modules([models_path]):
    if module_info.name.startswith("__"):
        continue

    module_name = f"{models_package}.{module_info.name}"

    try:
        importlib.import_module(module_name)
    except Exception as e:
        import_errors.append((module_name, str(e)))

print("=== AUDITORIA HYPERAI — MYSQL x SQLALCHEMY MODELS ===")
print("Modo: READ-ONLY")
print("Banco: conexão carregada do .env")
print()

real_meta = MetaData()
real_meta.reflect(bind=engine)

real_tables = {name: table for name, table in real_meta.tables.items()}
model_tables = {name: table for name, table in Base.metadata.tables.items()}

print(f"Tabelas reais no MySQL: {len(real_tables)}")
print(f"Models/tabelas esperadas no código: {len(model_tables)}")
print()

if import_errors:
    print("--- ERROS AO IMPORTAR MODELS ---")
    for module_name, error in import_errors:
        print(f"[ATENÇÃO] {module_name}: {error}")
    print()

print("--- TABELAS QUE O CÓDIGO ESPERA, MAS NÃO EXISTEM NO MYSQL ---")
missing_tables = sorted(set(model_tables.keys()) - set(real_tables.keys()))

if missing_tables:
    for table_name in missing_tables:
        print(f"[CRÍTICO] Tabela ausente no MySQL: {table_name}")
else:
    print("Nenhuma.")
print()

print("--- TABELAS QUE EXISTEM NO MYSQL, MAS NÃO TÊM MODEL SQLALCHEMY ---")
extra_tables = sorted(set(real_tables.keys()) - set(model_tables.keys()))

if extra_tables:
    for table_name in extra_tables:
        print(f"[INFO] Tabela extra no MySQL: {table_name}")
else:
    print("Nenhuma.")
print()

print("--- COLUNAS FALTANTES NO MYSQL ---")
has_missing_columns = False

for table_name in sorted(model_tables.keys()):
    if table_name not in real_tables:
        continue

    model_cols = {column.name: column for column in model_tables[table_name].columns}
    real_cols = {column.name: column for column in real_tables[table_name].columns}

    missing_cols = sorted(set(model_cols.keys()) - set(real_cols.keys()))

    if missing_cols:
        has_missing_columns = True
        print(f"[CRÍTICO] Tabela '{table_name}' está sem colunas:")
        for col in missing_cols:
            print(f"  - {col}")

if not has_missing_columns:
    print("Nenhuma.")
print()

print("--- COLUNAS EXTRAS NO MYSQL, NÃO MAPEADAS NO MODEL ---")
has_extra_columns = False

for table_name in sorted(real_tables.keys()):
    if table_name not in model_tables:
        continue

    model_cols = {column.name: column for column in model_tables[table_name].columns}
    real_cols = {column.name: column for column in real_tables[table_name].columns}

    extra_cols = sorted(set(real_cols.keys()) - set(model_cols.keys()))

    if extra_cols:
        has_extra_columns = True
        print(f"[INFO] Tabela '{table_name}' tem colunas extras no banco:")
        for col in extra_cols:
            print(f"  - {col}")

if not has_extra_columns:
    print("Nenhuma.")
print()

print("--- POSSÍVEIS DIVERGÊNCIAS DE TIPO ---")
has_type_diff = False

for table_name in sorted(model_tables.keys()):
    if table_name not in real_tables:
        continue

    model_cols = {column.name: column for column in model_tables[table_name].columns}
    real_cols = {column.name: column for column in real_tables[table_name].columns}

    common_cols = sorted(set(model_cols.keys()) & set(real_cols.keys()))

    for col in common_cols:
        model_type = str(model_cols[col].type).lower()
        real_type = str(real_cols[col].type).lower()

        if model_type != real_type:
            has_type_diff = True
            print(f"[ATENÇÃO] {table_name}.{col}")
            print(f"  Model: {model_type}")
            print(f"  MySQL: {real_type}")

if not has_type_diff:
    print("Nenhuma divergência aparente.")
print()

print("=== FIM DA AUDITORIA ===")