#!/usr/bin/env python3
"""Jeu synthétique de ventes et contrôles SQL pour le guide Nymphar.

Python 3.10+, bibliothèque standard uniquement. Aucun accès réseau.
Crée un nouveau dossier de CSV à importer dans Power BI ; n'écrase rien.
Les calculs SQL sont exécutés ici, les mesures DAX restent à vérifier dans Power BI.
"""
import argparse
import csv
import json
from pathlib import Path
import sqlite3


def prepare():
    db = sqlite3.connect(":memory:")
    db.row_factory = sqlite3.Row
    db.execute("PRAGMA foreign_keys = ON")
    db.executescript("""
        CREATE TABLE canaux (canal_id TEXT PRIMARY KEY, canal TEXT NOT NULL);
        INSERT INTO canaux VALUES ('web', 'Web'), ('store', 'Magasin');
        CREATE TABLE commandes (
            commande_id TEXT PRIMARY KEY, date_commande TEXT NOT NULL,
            canal_id TEXT NOT NULL REFERENCES canaux(canal_id), statut TEXT NOT NULL,
            montant_ht_centimes INTEGER NOT NULL, remise_ht_centimes INTEGER NOT NULL
        );
        INSERT INTO commandes VALUES
          ('C1', '2026-09-01', 'web',   'paid',     12000, 2000),
          ('C2', '2026-09-01', 'store', 'paid',      8000,    0),
          ('C3', '2026-09-02', 'web',   'canceled',  5000,    0),
          ('C4', '2026-09-02', 'web',   'paid',      6000, 1000),
          ('C5', '2026-09-02', 'store', 'paid',     20000, 2000),
          ('C6', '2026-09-02', 'store', 'paid',     10000,    0);
        CREATE TABLE remboursements (
            remboursement_id TEXT PRIMARY KEY,
            commande_id TEXT NOT NULL REFERENCES commandes(commande_id),
            remboursement_ht_centimes INTEGER NOT NULL
        );
        INSERT INTO remboursements VALUES ('R1', 'C1', 500), ('R2', 'C1', 500), ('R3', 'C4', 5000);
        CREATE VIEW fact_commandes AS
        WITH retours AS (
            SELECT commande_id, SUM(remboursement_ht_centimes) AS remboursement_ht_centimes
            FROM remboursements GROUP BY commande_id
        )
        SELECT c.*, COALESCE(r.remboursement_ht_centimes, 0) AS remboursement_ht_centimes
        FROM commandes c LEFT JOIN retours r USING (commande_id);
    """)
    return db


def metrics(db, where="1=1", params=()):
    # where est uniquement une expression constante du script, jamais une entrée utilisateur.
    row = db.execute(f"""
        SELECT COALESCE(SUM(montant_ht_centimes-remise_ht_centimes-remboursement_ht_centimes),0) AS net,
               COUNT(*) AS n
        FROM fact_commandes WHERE statut='paid' AND ({where})
    """, params).fetchone()
    return {"ca_net_ht_centimes": row["net"], "commandes_payees": row["n"],
            "panier_net_moyen_euros": row["net"] / 100 / row["n"] if row["n"] else None}


def verify(db):
    total = metrics(db)
    web = metrics(db, "canal_id=?", ("web",))
    store = metrics(db, "canal_id=?", ("store",))
    assert total == {"ca_net_ht_centimes": 45000, "commandes_payees": 5, "panier_net_moyen_euros": 90.0}
    assert web == {"ca_net_ht_centimes": 9000, "commandes_payees": 2, "panier_net_moyen_euros": 45.0}
    assert store == {"ca_net_ht_centimes": 36000, "commandes_payees": 3, "panier_net_moyen_euros": 120.0}
    assert metrics(db, "date_commande=?", ("2026-09-01",))["ca_net_ht_centimes"] == 17000
    assert metrics(db, "commande_id=?", ("C4",))["ca_net_ht_centimes"] == 0
    assert metrics(db, "commande_id=?", ("C4",))["commandes_payees"] == 1
    assert metrics(db, "statut=?", ("canceled",))["commandes_payees"] == 0
    assert metrics(db, "date_commande=?", ("2030-01-01",))["panier_net_moyen_euros"] is None
    assert db.execute("SELECT COUNT(*)=COUNT(DISTINCT commande_id) FROM fact_commandes").fetchone()[0]
    assert not db.execute("PRAGMA foreign_key_check").fetchall()
    naive = db.execute("""
        SELECT SUM(c.montant_ht_centimes-c.remise_ht_centimes-COALESCE(r.remboursement_ht_centimes,0))
        FROM commandes c LEFT JOIN remboursements r USING(commande_id) WHERE c.statut='paid'
    """).fetchone()[0]
    assert naive == 55000
    wrong_average = (web["panier_net_moyen_euros"] + store["panier_net_moyen_euros"]) / 2
    assert wrong_average == 82.5 and wrong_average != total["panier_net_moyen_euros"]
    # Vérifier qu'un doublon de clé de commande ne peut pas être introduit.
    try:
        db.execute("INSERT INTO commandes SELECT * FROM commandes WHERE commande_id='C1'")
    except sqlite3.IntegrityError:
        duplicate_rejected = True
    else:
        raise AssertionError("La clé de commande accepte un doublon")
    return {"synthetic_data": True, "all_checks_passed": True, "total": total,
            "web": web, "magasin": store, "naive_join_centimes": naive,
            "wrong_unweighted_average_euros": wrong_average, "duplicate_rejected": duplicate_rejected,
            "dax_executed": False, "sqlite_version": sqlite3.sqlite_version}


def export_csv(db, path, query):
    cursor = db.execute(query)
    with path.open("x", encoding="utf-8", newline="") as file:
        writer = csv.writer(file)
        writer.writerow([col[0] for col in cursor.description])
        writer.writerows(cursor.fetchall())


def main():
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument("directory", type=Path, help="Dossier neuf à créer")
    args = parser.parse_args()
    db = prepare()
    report = verify(db)
    args.directory.mkdir(parents=True, exist_ok=False)
    export_csv(db, args.directory / "FactCommandes.csv", "SELECT * FROM fact_commandes ORDER BY commande_id")
    export_csv(db, args.directory / "DimCanal.csv", "SELECT * FROM canaux ORDER BY canal_id")
    export_csv(db, args.directory / "DimDate.csv", "SELECT DISTINCT date_commande AS date FROM commandes ORDER BY date_commande")
    with (args.directory / "attendus.json").open("x", encoding="utf-8") as file:
        json.dump(report, file, ensure_ascii=False, indent=2)
        file.write("\n")
    print(json.dumps(report, ensure_ascii=False, indent=2))
    db.close()


if __name__ == "__main__":
    main()