from pathlib import Path

from sqlalchemy import create_engine, text
from sqlalchemy.orm import DeclarativeBase, sessionmaker

BASE_DIR = Path(__file__).resolve().parent.parent
DB_PATH = BASE_DIR / "elfirma.db"

engine = create_engine(
    f"sqlite:///{DB_PATH}",
    connect_args={"check_same_thread": False},
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)


class Base(DeclarativeBase):
    pass


def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


def init_db():
    from app import models  # noqa: F401  (enregistre les modèles avant create_all)

    Base.metadata.create_all(bind=engine)
    _migrer_colonnes_manquantes()


def _migrer_colonnes_manquantes():
    """SQLAlchemy create_all() ne modifie jamais une table déjà existante. Pour les
    nouvelles colonnes ajoutées à un modèle après la mise en service, on les ajoute
    ici manuellement (ALTER TABLE) si elles manquent — sans toucher aux données."""
    colonnes_a_ajouter = [
        ("parcelles", "ferme_id", "INTEGER REFERENCES fermes(id)"),
        ("fermes", "photo_satellite_path", "VARCHAR(500)"),
        ("achats", "numero_facture", "VARCHAR(100)"),
        ("fournisseurs", "solde_initial", "FLOAT DEFAULT 0"),
        ("fournisseurs", "date_solde_initial", "DATE"),
        ("clients", "solde_initial", "FLOAT DEFAULT 0"),
        ("clients", "date_solde_initial", "DATE"),
        ("ventes_client", "numero_facture", "VARCHAR(100)"),
        ("ventes_client", "transporteur", "VARCHAR(200)"),
        ("ventes_client", "matricule_camion", "VARCHAR(100)"),
        ("ventes_client", "incoterm", "VARCHAR(200)"),
        ("ventes_client", "type_document", "VARCHAR(20) DEFAULT 'facture'"),
        ("ventes_client", "facture_origine_id", "INTEGER REFERENCES ventes_client(id)"),
        ("ventes_client", "date_echeance", "DATE"),
        ("ventes_client", "vendeur_id", "INTEGER REFERENCES parametres_entreprise(id)"),
        ("ventes_client", "mode_reglement", "VARCHAR(100)"),
        ("achats", "unite", "VARCHAR(20)"),
        ("frais_personnel", "date_debut", "DATE"),
        ("produits", "poids_colis_kg_defaut", "FLOAT"),
        ("clients", "telephone_portable", "VARCHAR(50)"),
        ("clients", "email", "VARCHAR(200)"),
        ("clients", "numero_rue", "VARCHAR(50)"),
        ("clients", "code_postal", "VARCHAR(20)"),
        ("clients", "pays", "VARCHAR(100)"),
        ("clients", "devise", "VARCHAR(10) DEFAULT 'MAD'"),
        ("clients", "registre_commerce", "VARCHAR(100)"),
        ("clients", "autres_infos_legales", "TEXT"),
        ("clients", "afficher_telephone_bureau", "BOOLEAN DEFAULT 1"),
        ("clients", "afficher_telephone_portable", "BOOLEAN DEFAULT 1"),
        ("clients", "afficher_email", "BOOLEAN DEFAULT 1"),
        ("clients", "afficher_registre_commerce", "BOOLEAN DEFAULT 1"),
        ("clients", "afficher_autres_infos", "BOOLEAN DEFAULT 1"),
    ]
    with engine.connect() as conn:
        for table, colonne, type_sql in colonnes_a_ajouter:
            colonnes_existantes = {row[1] for row in conn.execute(text(f"PRAGMA table_info({table})"))}
            if colonne not in colonnes_existantes:
                conn.execute(text(f"ALTER TABLE {table} ADD COLUMN {colonne} {type_sql}"))
                conn.commit()