import os
from sqlalchemy import inspect, text
from werkzeug.security import generate_password_hash

from app import app
from models import db, User


DEFAULT_TIPOS_CLIENTE = [
    "Inmobiliario Usuario Final",
    "Inmobiliario Inversionistas",
    "Nobel",
    "Pepsico",
]


def ensure_app_columns_and_tables():
    inspector = inspect(db.engine)
    raw_tables = inspector.get_table_names()
    table_by_lower = {t.lower(): t for t in raw_tables}

    users_table = table_by_lower.get("users")
    inmo_table = table_by_lower.get("inmobiliarias")
    temas_table = table_by_lower.get("temas")

    cols_inmo = set()

    if users_table:
        cols_users = {c["name"].lower() for c in inspector.get_columns(users_table)}
        if "apellido" not in cols_users:
            db.session.execute(text(f"ALTER TABLE `{users_table}` ADD COLUMN apellido VARCHAR(120) NULL"))
        if "correo" not in cols_users:
            db.session.execute(text(f"ALTER TABLE `{users_table}` ADD COLUMN correo VARCHAR(150) NULL"))

        db.session.execute(text(f"ALTER TABLE `{users_table}` MODIFY COLUMN rol VARCHAR(30) NULL DEFAULT 'UsuarioEmpresa'"))
        db.session.execute(text(f"UPDATE `{users_table}` SET rol='Administrador' WHERE LOWER(rol)='admin'"))
        db.session.execute(text(f"UPDATE `{users_table}` SET rol='UsuarioEmpresa' WHERE LOWER(rol)='user'"))

    if inmo_table:
        cols_inmo = {c["name"].lower() for c in inspector.get_columns(inmo_table)}
        if "cliente" not in cols_inmo:
            db.session.execute(text(f"ALTER TABLE `{inmo_table}` ADD COLUMN cliente VARCHAR(100) NULL"))
            cols_inmo.add("cliente")

    if temas_table:
        cols_temas = {c["name"].lower() for c in inspector.get_columns(temas_table)}
        if "cliente" not in cols_temas:
            db.session.execute(text(f"ALTER TABLE `{temas_table}` ADD COLUMN cliente VARCHAR(100) NULL"))

    db.session.execute(text("""
        CREATE TABLE IF NOT EXISTS aw_tipos_cliente (
            id INT AUTO_INCREMENT PRIMARY KEY,
            nombre VARCHAR(120) NOT NULL,
            UNIQUE KEY uq_aw_tipos_cliente_nombre (nombre)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    """))

    db.session.execute(text("""
        CREATE TABLE IF NOT EXISTS user_empresas (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            inmobiliaria_id INT NOT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uq_user_empresas_user_inmobiliaria (user_id, inmobiliaria_id),
            INDEX ix_user_empresas_user_id (user_id),
            INDEX ix_user_empresas_inmobiliaria_id (inmobiliaria_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    """))

    db.session.execute(text("""
        CREATE TABLE IF NOT EXISTS aw_empresa_programacion_temas (
            id INT AUTO_INCREMENT PRIMARY KEY,
            empresa_id INT NOT NULL,
            tema VARCHAR(255) NOT NULL,
            orden INT NOT NULL DEFAULT 1,
            fecha_activacion_tema DATE NULL,
            fecha_propuesta DATE NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX ix_aw_programacion_empresa_id (empresa_id),
            INDEX ix_aw_programacion_orden (empresa_id, orden)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    """))

    for nombre in DEFAULT_TIPOS_CLIENTE:
        db.session.execute(
            text("INSERT IGNORE INTO aw_tipos_cliente (nombre) VALUES (:nombre)"),
            {"nombre": nombre},
        )

    if temas_table:
        db.session.execute(text(f"""
            INSERT IGNORE INTO aw_tipos_cliente (nombre)
            SELECT DISTINCT TRIM(cliente)
            FROM `{temas_table}`
            WHERE cliente IS NOT NULL AND TRIM(cliente) <> ''
        """))

    if inmo_table and "cliente" in cols_inmo:
        db.session.execute(text(f"""
            INSERT IGNORE INTO aw_tipos_cliente (nombre)
            SELECT DISTINCT TRIM(cliente)
            FROM `{inmo_table}`
            WHERE cliente IS NOT NULL AND TRIM(cliente) <> ''
        """))

    db.session.commit()


with app.app_context():
    db.create_all()
    ensure_app_columns_and_tables()

    if not User.query.filter_by(usuario="admin").first():
        admin_password = os.getenv("INIT_ADMIN_PASSWORD", "").strip()
        if len(admin_password) < 10 or admin_password.lower() == "admin":
            raise RuntimeError(
                "Define INIT_ADMIN_PASSWORD con al menos 10 caracteres y no uses 'admin'."
            )

        db.session.add(
            User(
                usuario="admin",
                password_hash=generate_password_hash(admin_password),
                nombre="Admin",
                apellido="",
                correo=None,
                rol="Administrador",
                activado=True,
            )
        )
        db.session.commit()
        print("Usuario admin creado correctamente.")

    print("Esquema actualizado correctamente.")