#!/usr/bin/env python3
"""
Initialise ou met à jour la base SQLite.

Usage : python init_db.py
"""
import os
import sys
import uuid
from datetime import date, datetime

sys.path.insert(0, os.path.dirname(__file__))

from sqlalchemy import inspect, text

from app import create_app
from app.constants import DEFAULT_LOT_TYPES, DEFAULT_MILLIEME_TYPES, DEFAULT_RELEVE_TYPES
from app.models import (
    Compteur,
    Copropriete,
    Lot,
    LotMilliemes,
    LotPersonneRole,
    LotType,
    MilliemeType,
    Personne,
    Releve,
    ReleveType,
    User,
    db,
)

ADMIN_USERNAME = os.environ.get("ADMIN_USERNAME", "stessy.cauet")
ADMIN_PASSWORD = os.environ.get("ADMIN_PASSWORD", "Scauet.000")

DEFAULT_USERS: tuple[dict[str, str | bool], ...] = (
    {"username": ADMIN_USERNAME, "password": ADMIN_PASSWORD, "is_admin": True},
    {"username": "jeanmarc.cauet", "password": "JMcauet.000", "is_admin": False},
    {"username": "amaranthe.hurtado", "password": "Ahurtado.000", "is_admin": False},
)


def _parse_sql_date(value) -> date | None:
    if value is None:
        return None
    if isinstance(value, date):
        return value
    if isinstance(value, str):
        return datetime.strptime(value[:10], "%Y-%m-%d").date()
    return value


SOFT_DELETE_TABLES = (
    "coproprietes",
    "personnes",
    "lots",
    "compteurs",
    "releves",
    "lot_personne_roles",
)


def _table_columns(inspector, table: str) -> set[str]:
    if table not in inspector.get_table_names():
        return set()
    return {c["name"] for c in inspector.get_columns(table)}


def migrate_soft_delete(inspector) -> None:
    for table in SOFT_DELETE_TABLES:
        columns = _table_columns(inspector, table)
        if not columns:
            continue
        if "statut" not in columns:
            db.session.execute(
                text(
                    f"ALTER TABLE {table} "
                    "ADD COLUMN statut VARCHAR(20) NOT NULL DEFAULT 'actif'"
                )
            )
            print(f"  + {table}.statut")
        if "date_suppression" not in columns:
            db.session.execute(
                text(f"ALTER TABLE {table} ADD COLUMN date_suppression DATETIME")
            )
            print(f"  + {table}.date_suppression")


def migrate_admin(inspector) -> None:
    columns = _table_columns(inspector, "users")
    if not columns:
        return
    if "is_admin" not in columns:
        db.session.execute(
            text("ALTER TABLE users ADD COLUMN is_admin BOOLEAN NOT NULL DEFAULT 0")
        )
        print("  + users.is_admin")


def migrate_user_tel_affichage(inspector) -> None:
    columns = _table_columns(inspector, "users")
    if not columns:
        return
    if "tel_affichage" not in columns:
        db.session.execute(
            text(
                "ALTER TABLE users ADD COLUMN tel_affichage VARCHAR(20) "
                "NOT NULL DEFAULT 'international'"
            )
        )
        print("  + users.tel_affichage")


def migrate_normalize_phones() -> None:
    from app.models import Telephone
    from app.phone import normalize_phone

    updated = 0
    for tel in Telephone.query.all():
        normalized = normalize_phone(tel.numero)
        if normalized and normalized != tel.numero:
            tel.numero = normalized
            updated += 1
    if updated:
        print(f"  -> {updated} numéro(s) de téléphone normalisé(s)")


def migrate_lot_champs(inspector) -> None:
    columns = _table_columns(inspector, "lots")
    if not columns:
        return
    if "designation" not in columns:
        db.session.execute(
            text("ALTER TABLE lots ADD COLUMN designation VARCHAR(200) DEFAULT ''")
        )
        print("  + lots.designation")
    if "etage" not in columns:
        db.session.execute(text("ALTER TABLE lots ADD COLUMN etage VARCHAR(30) DEFAULT ''"))
        print("  + lots.etage")
    if "porte" not in columns:
        db.session.execute(text("ALTER TABLE lots ADD COLUMN porte VARCHAR(30) DEFAULT ''"))
        print("  + lots.porte")
    if "lot_type_id" not in columns:
        db.session.execute(text("ALTER TABLE lots ADD COLUMN lot_type_id INTEGER"))
        print("  + lots.lot_type_id")
    if "numero_fiscal" not in columns:
        db.session.execute(
            text("ALTER TABLE lots ADD COLUMN numero_fiscal VARCHAR(50) DEFAULT ''")
        )
        print("  + lots.numero_fiscal")

    columns = _table_columns(inspector, "lots")
    if "etage_porte" in columns:
        db.session.execute(
            text(
                "UPDATE lots SET designation = etage_porte "
                "WHERE (designation IS NULL OR designation = '') "
                "AND etage_porte IS NOT NULL AND etage_porte != ''"
            )
        )
        print("  -> etage_porte migré vers designation")


def migrate_role_date_debut_nullable(inspector) -> None:
    """SQLite : date_debut ne peut pas passer en NULL via ALTER TABLE — recréation de la table."""
    if "lot_personne_roles" not in inspector.get_table_names():
        return
    columns = {c["name"]: c for c in inspector.get_columns("lot_personne_roles")}
    if "date_debut" not in columns:
        return
    if columns["date_debut"].get("nullable", True):
        print("  · lot_personne_roles.date_debut déjà nullable")
        return

    print("  -> lot_personne_roles.date_debut rendu nullable")
    db.session.execute(text("PRAGMA foreign_keys=OFF"))
    db.session.execute(
        text(
            """
            CREATE TABLE lot_personne_roles_new (
                id INTEGER NOT NULL PRIMARY KEY,
                lot_id INTEGER NOT NULL,
                personne_id INTEGER NOT NULL,
                role VARCHAR(20) NOT NULL,
                date_debut DATE,
                date_fin DATE,
                notes TEXT,
                public_id VARCHAR(36),
                statut VARCHAR(20) NOT NULL DEFAULT 'actif',
                date_suppression DATETIME,
                created_at DATETIME,
                updated_at DATETIME,
                FOREIGN KEY(lot_id) REFERENCES lots (id),
                FOREIGN KEY(personne_id) REFERENCES personnes (id)
            )
            """
        )
    )
    db.session.execute(
        text(
            """
            INSERT INTO lot_personne_roles_new (
                id, lot_id, personne_id, role, date_debut, date_fin, notes,
                public_id, statut, date_suppression, created_at, updated_at
            )
            SELECT
                id, lot_id, personne_id, role, date_debut, date_fin, notes,
                public_id, statut, date_suppression, created_at, updated_at
            FROM lot_personne_roles
            """
        )
    )
    db.session.execute(text("DROP TABLE lot_personne_roles"))
    db.session.execute(text("ALTER TABLE lot_personne_roles_new RENAME TO lot_personne_roles"))
    db.session.execute(
        text("CREATE INDEX IF NOT EXISTS ix_lot_personne_roles_lot_id ON lot_personne_roles (lot_id)")
    )
    db.session.execute(
        text(
            "CREATE INDEX IF NOT EXISTS ix_lot_personne_roles_personne_id "
            "ON lot_personne_roles (personne_id)"
        )
    )
    db.session.execute(
        text("CREATE INDEX IF NOT EXISTS ix_lot_personne_roles_statut ON lot_personne_roles (statut)")
    )
    db.session.execute(text("PRAGMA foreign_keys=ON"))


def migrate_personne_raison_sociale(inspector) -> None:
    columns = _table_columns(inspector, "personnes")
    if not columns:
        return
    if "raison_sociale" not in columns:
        db.session.execute(
            text("ALTER TABLE personnes ADD COLUMN raison_sociale VARCHAR(200) DEFAULT ''")
        )
        print("  + personnes.raison_sociale")


def migrate_remove_est_prestataire(inspector) -> None:
    """Colonne obsolète : le rôle gestionnaire remplace est_prestataire."""
    columns = _table_columns(inspector, "personnes")
    if "est_prestataire" not in columns:
        return
    db.session.execute(text("ALTER TABLE personnes DROP COLUMN est_prestataire"))
    print("  - personnes.est_prestataire supprimé")


def migrate_compteur_releve_type(inspector) -> None:
    columns = _table_columns(inspector, "compteurs")
    if not columns:
        return
    if "releve_type_id" not in columns:
        db.session.execute(text("ALTER TABLE compteurs ADD COLUMN releve_type_id INTEGER"))
        print("  + compteurs.releve_type_id")


TIMESTAMP_TABLES = ("personnes", "lots", "lot_personne_roles")


def migrate_timestamps(inspector) -> None:
    for table in TIMESTAMP_TABLES:
        columns = _table_columns(inspector, table)
        if not columns:
            continue
        if "created_at" not in columns:
            db.session.execute(text(f"ALTER TABLE {table} ADD COLUMN created_at DATETIME"))
            print(f"  + {table}.created_at")
        if "updated_at" not in columns:
            db.session.execute(text(f"ALTER TABLE {table} ADD COLUMN updated_at DATETIME"))
            print(f"  + {table}.updated_at")
    db.session.commit()

    for table in TIMESTAMP_TABLES:
        if table == "lot_personne_roles":
            db.session.execute(
                text(
                    "UPDATE lot_personne_roles SET created_at = datetime(date_debut || ' 12:00:00') "
                    "WHERE created_at IS NULL"
                )
            )
        else:
            db.session.execute(
                text(f"UPDATE {table} SET created_at = datetime('now') WHERE created_at IS NULL")
            )
        db.session.execute(
            text(
                f"UPDATE {table} SET updated_at = COALESCE(updated_at, created_at) "
                "WHERE updated_at IS NULL"
            )
        )
    print("  -> horodatages initialisés")


PUBLIC_ID_MODELS = (
    User,
    Copropriete,
    Personne,
    Lot,
    LotPersonneRole,
    LotType,
    MilliemeType,
    ReleveType,
    Compteur,
    Releve,
)


def migrate_public_ids(inspector) -> None:
    for model in PUBLIC_ID_MODELS:
        table = model.__tablename__
        columns = _table_columns(inspector, table)
        if not columns:
            continue
        if "public_id" not in columns:
            db.session.execute(text(f"ALTER TABLE {table} ADD COLUMN public_id VARCHAR(36)"))
            print(f"  + {table}.public_id")
    db.session.commit()

    for model in PUBLIC_ID_MODELS:
        table = model.__tablename__
        rows = db.session.execute(
            text(f"SELECT id FROM {table} WHERE public_id IS NULL OR public_id = ''")
        )
        for (row_id,) in rows:
            db.session.execute(
                text(f"UPDATE {table} SET public_id = :pid WHERE id = :id"),
                {"pid": str(uuid.uuid4()), "id": row_id},
            )
    print("  -> public_id initialisés")


def seed_types_copropriete(copro: Copropriete) -> None:
    if not copro.millieme_types:
        for i, libelle in enumerate(DEFAULT_MILLIEME_TYPES):
            db.session.add(MilliemeType(copropriete_id=copro.id, libelle=libelle, ordre=i))
    if not copro.releve_types:
        for i, (libelle, unite) in enumerate(DEFAULT_RELEVE_TYPES):
            db.session.add(
                ReleveType(
                    copropriete_id=copro.id, libelle=libelle, unite=unite, ordre=i
                )
            )
    if not copro.lot_types:
        for i, libelle in enumerate(DEFAULT_LOT_TYPES):
            db.session.add(LotType(copropriete_id=copro.id, libelle=libelle, ordre=i))


def migrate_roles_from_legacy(inspector) -> None:
    if "lot_personne_roles" not in inspector.get_table_names():
        return
    if LotPersonneRole.query.first():
        return

    if "proprietaires_lot" in inspector.get_table_names():
        rows = db.session.execute(
            text(
                "SELECT lot_id, personne_id, date_debut, date_fin, notes "
                "FROM proprietaires_lot"
            )
        )
        for row in rows:
            db.session.add(
                LotPersonneRole(
                    lot_id=row[0],
                    personne_id=row[1],
                    role="proprietaire",
                    date_debut=_parse_sql_date(row[2]),
                    date_fin=_parse_sql_date(row[3]),
                    notes=row[4] or "",
                )
            )
        print("  -> proprietaires_lot migrés")

    if "locataires_lot" in inspector.get_table_names():
        rows = db.session.execute(
            text(
                "SELECT lot_id, personne_id, date_debut, date_fin, notes "
                "FROM locataires_lot"
            )
        )
        for row in rows:
            db.session.add(
                LotPersonneRole(
                    lot_id=row[0],
                    personne_id=row[1],
                    role="locataire",
                    date_debut=_parse_sql_date(row[2]),
                    date_fin=_parse_sql_date(row[3]),
                    notes=row[4] or "",
                )
            )
        print("  -> locataires_lot migrés")


def migrate_milliemes_legacy(inspector) -> None:
    columns = _table_columns(inspector, "lots")
    if not columns or "milliemes" not in columns:
        return
    lots = Lot.query.filter(Lot.milliemes != "", Lot.milliemes.isnot(None)).all()
    for lot in lots:
        if lot.milliemes_valeurs:
            continue
        copro = lot.copropriete
        seed_types_copropriete(copro)
        db.session.flush()
        type_def = (
            MilliemeType.query.filter_by(
                copropriete_id=copro.id, libelle="Charges communes"
            ).first()
        )
        if type_def and lot.milliemes.strip():
            db.session.add(
                LotMilliemes(lot_id=lot.id, type_id=type_def.id, valeur=lot.milliemes.strip())
            )
    if lots:
        print("  -> millièmes legacy migrés")


def migrate_compteur_types_legacy(inspector) -> None:
    if "compteurs" not in inspector.get_table_names():
        return
    mapping = {"eau_froide": "Eau froide", "eau_chaude": "Eau chaude"}
    compteurs = db.session.execute(
        text("SELECT id, lot_id, type_compteur, releve_type_id FROM compteurs")
    )
    for cid, lot_id, type_compteur, releve_type_id in compteurs:
        if releve_type_id:
            continue
        lot = db.session.get(Lot, lot_id)
        if not lot:
            continue
        seed_types_copropriete(lot.copropriete)
        db.session.flush()
        libelle = mapping.get(type_compteur or "", "Autre")
        rt = ReleveType.query.filter_by(
            copropriete_id=lot.copropriete_id, libelle=libelle
        ).first()
        if rt:
            db.session.execute(
                text("UPDATE compteurs SET releve_type_id = :tid WHERE id = :cid"),
                {"tid": rt.id, "cid": cid},
            )
    print("  -> types relevés compteurs migrés")


def ensure_default_users() -> None:
    for spec in DEFAULT_USERS:
        username = str(spec["username"])
        user = User.query.filter_by(username=username).first()
        if not user:
            user = User(username=username, is_admin=bool(spec["is_admin"]))
            user.set_password(str(spec["password"]))
            db.session.add(user)
            print(f"Utilisateur créé : {username}")
        else:
            if spec["is_admin"] and not user.is_admin:
                user.is_admin = True
            print(f"Utilisateur déjà présent : {username}")


def ensure_admin_user() -> None:
    ensure_default_users()


def main() -> None:
    app = create_app()
    with app.app_context():
        print("Création des tables si nécessaire…")
        db.create_all()

        print("Migrations de schéma…")
        inspector = inspect(db.engine)
        migrate_soft_delete(inspector)
        migrate_admin(inspector)
        migrate_user_tel_affichage(inspector)
        migrate_personne_raison_sociale(inspector)
        migrate_remove_est_prestataire(inspector)
        migrate_compteur_releve_type(inspector)
        migrate_lot_champs(inspector)
        migrate_role_date_debut_nullable(inspector)
        migrate_timestamps(inspector)
        migrate_public_ids(inspector)
        db.session.commit()

        inspector = inspect(db.engine)
        print("Données et types par défaut…")
        for copro in Copropriete.query_actifs().all():
            seed_types_copropriete(copro)
        db.session.commit()

        migrate_roles_from_legacy(inspector)
        db.session.commit()
        migrate_milliemes_legacy(inspector)
        migrate_compteur_types_legacy(inspector)
        db.session.commit()

        print("Normalisation des téléphones…")
        migrate_normalize_phones()
        db.session.commit()

        print("Comptes utilisateurs par défaut…")
        ensure_default_users()
        db.session.commit()

        print(f"Terminé. Base : {app.config['SQLALCHEMY_DATABASE_URI']}")


if __name__ == "__main__":
    main()