#!/usr/bin/env python3
import uuid
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
from dotenv import load_dotenv
from core.config import settings

load_dotenv()

DATABASE_URL = settings.DATABASE_URL

engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(bind=engine)

CITIES_CI = [
    "Abidjan", "Yamoussoukro", "Bouaké", "San-Pédro", "Daloa", "Korhogo",
    "Man", "Gagnoa", "Abengourou", "Odienné", "Divo", "Bondoukou",
    "Sassandra", "Soubré", "Ferkessédougou", "Tengrela", "Guiglo", "Agboville",
    "Duékoué", "Issia", "Bouaflé", "Aboisso"
]


def generate_unique_branch_code(session, base_code):
    """
    Génère un branch_code unique en ajoutant un compteur si nécessaire.
    ex: BOU, BOU1, BOU2…
    """
    code = base_code
    counter = 1

    # Vérifier si le code existe déjà
    while True:
        exists = session.execute(
            text("SELECT 1 FROM cities WHERE branch_code = :code"),
            {"code": code}
        ).fetchone()

        if not exists:
            return code

        code = f"{base_code}{counter}"
        counter += 1


def create_country_and_cities():
    session = SessionLocal()
    try:
        print("\n========= CRÉATION DES DONNÉES =========")

        # Vérifier si le pays existe
        row = session.execute(
            text("SELECT id FROM countries WHERE code = 'CI'")
        ).fetchone()

        if row:
            country_id = row[0]
            print(f"Pays déjà existant : {country_id}")
        else:
            country_id = str(uuid.uuid4())
            session.execute(text("""
                INSERT INTO countries (id, name, code, currency, currency_symbol, is_active)
                VALUES (:id, 'Côte d''Ivoire', 'CI', 'FCFA', 'FCFA', TRUE)
            """), {"id": country_id})
            session.commit()
            print(f"Pays créé : {country_id}")

        created = 0
        skipped = 0

        for city_name in CITIES_CI:
            # Vérifier si la ville existe déjà
            exists = session.execute(
                text("SELECT id FROM cities WHERE name = :name AND country_id = :country_id"),
                {"name": city_name, "country_id": country_id}
            ).fetchone()

            if exists:
                skipped += 1
                continue

            base_code = city_name[:3].upper()
            branch_code = generate_unique_branch_code(session, base_code)

            session.execute(text("""
                INSERT INTO cities (id, name, country_id, branch_code, is_active)
                VALUES (:id, :name, :country_id, :branch_code, TRUE)
            """), {
                "id": str(uuid.uuid4()),
                "name": city_name,
                "country_id": country_id,
                "branch_code": branch_code
            })

            created += 1

        session.commit()

        print("\n========= RÉSULTAT =========")
        print(f"🏙️ Villes créées : {created}")
        print(f"🔁 Villes ignorées : {skipped}")
        print("============================\n")

    except Exception as e:
        session.rollback()
        print(f"\n❌ ERREUR : {e}")
        raise
    finally:
        session.close()


if __name__ == "__main__":
    create_country_and_cities()
