"""Recette locale sur données fictives, sans réseau ni appel à Claude.
Python 3.10+, bibliothèque standard. Écrit uniquement sur stdout.
Usage : python3 verifier_demo.py commandes-demo.csv
"""
import csv
import json
import sqlite3
import sys
from datetime import date
from pathlib import Path

QUERY = """SELECT pays, COUNT(*),
SUM(montant_ht_centimes-remboursement_ht_centimes)
FROM commandes
WHERE statut='paid' AND devise='EUR'
AND date_metier >= '2026-10-01' AND date_metier < '2026-10-11'
GROUP BY pays ORDER BY pays"""

def read_rows(path):
    with path.open(newline='', encoding='utf-8') as stream:
        records = list(csv.DictReader(stream))
    ids = set()
    rows = []
    for r in records:
        key = r['commande_id']
        if not key or key in ids:
            raise ValueError('Identifiant absent ou dupliqué : ' + key)
        ids.add(key)
        date.fromisoformat(r['date_metier'])
        amount = int(r['montant_ht_centimes'])
        refund = int(r['remboursement_ht_centimes'])
        if amount < 0 or not 0 <= refund <= amount:
            raise ValueError('Montants incohérents : ' + key)
        rows.append((key, r['date_metier'], r['pays'], r['statut'],
                     r['devise'], amount, refund))
    return rows

def main(path):
    rows = read_rows(path)
    with sqlite3.connect(':memory:') as db:
        db.execute('''CREATE TABLE commandes (
            commande_id TEXT PRIMARY KEY, date_metier TEXT, pays TEXT,
            statut TEXT, devise TEXT, montant_ht_centimes INTEGER,
            remboursement_ht_centimes INTEGER)''')
        db.executemany('INSERT INTO commandes VALUES (?,?,?,?,?,?,?)', rows)
        actual = db.execute(QUERY).fetchall()
        expected = [('BE', 1, 4000), ('FR', 3, 21000)]
        if actual != expected:
            raise ValueError(f'Jeu de référence différent : {actual!r}')
        controls = {}
        controls['annulation_incluse'] = db.execute(
            QUERY.replace("statut='paid' AND ", '')).fetchall() != expected
        controls['devise_melangee'] = db.execute(
            QUERY.replace("AND devise='EUR'", '')).fetchall() != expected
        controls['remboursement_oublie'] = db.execute(
            QUERY.replace('montant_ht_centimes-remboursement_ht_centimes',
                          'montant_ht_centimes')).fetchall() != expected
        controls['borne_fin_inclusive'] = db.execute(
            QUERY.replace("date_metier < '2026-10-11'",
                          "date_metier <= '2026-10-11'")).fetchall() != expected
        if not all(controls.values()):
            raise RuntimeError('La fixture ne distingue pas toutes les erreurs prévues')
        db.execute("INSERT INTO commandes VALUES ('A009','2026-10-10','BE','paid','EUR',4000,0)")
        refreshed = db.execute(QUERY).fetchall()
        if refreshed != [('BE', 2, 8000), ('FR', 3, 21000)]:
            raise RuntimeError('Résultat après ajout inattendu')
    print(json.dumps({'mode':'local_synthetique_sans_claude',
        'reference':actual,'commandes':4,'net_ht_centimes':25000,
        'erreurs_detectables':controls,'apres_ajout':refreshed,
        'nouveau_total_centimes':29000}, ensure_ascii=False, indent=2))

if __name__ == '__main__':
    main(Path(sys.argv[1]) if len(sys.argv)>1 else Path(__file__).with_name('commandes-demo.csv'))