#!/usr/bin/env python3
"""Banc fictif SQLite : rapprochement d'une réception ShipBob, sans appel réseau.
Le wrapper account/boxes_complete est local, pas un schéma de webhook ShipBob.
Le journal et les mouvements sont locaux. Leur atomicité ne certifie aucun ERP.
Usage : python3 reception_shipbob.py
"""
import copy
import hashlib
import itertools
import json
import sqlite3

class Hold(ValueError):
    pass

CHECKS = 0

def check(ok, label):
    global CHECKS
    if not ok:
        raise AssertionError(label)
    CHECKS += 1


def whole(value, label):
    if type(value) is not int or value < 0:
        raise Hold(label)
    return value


def identifier(value, label):
    if whole(value, label) == 0:
        raise Hold(label)
    return value


def normalize(snapshot, account='merchant-A'):
    """Politique de ce pilote : unités entières, réception finale et détail complet."""
    if snapshot.get('account') != account or snapshot.get('boxes_complete') is not True:
        raise Hold('Périmètre ou collecte non qualifié')
    w = snapshot['wro']
    wid = identifier(w.get('id'), 'WRO absent')
    fc = identifier(w.get('fulfillment_center', {}).get('id'), 'Centre absent')
    if w.get('status') != 'Completed':
        raise Hold('Réception non terminée')
    aggregate = {}
    for row in w.get('inventory_quantities') or []:
        item = identifier(row.get('inventory_id'), 'Article absent')
        if item in aggregate:
            raise Hold('Identifiant agrégé répété')
        values = tuple(whole(row.get(k), k) for k in ('expected_quantity', 'received_quantity', 'stowed_quantity'))
        if values[2] > values[1]:
            raise Hold('Rangé supérieur au reçu dans ce pilote')
        aggregate[item] = values
    if not aggregate:
        raise Hold('Aucun détail quantitatif')
    totals, normalized_boxes, seen_boxes = {}, [], set()
    for box in snapshot.get('boxes') or []:
        bid = identifier(box.get('box_id'), 'Carton absent')
        if bid in seen_boxes:
            raise Hold('Carton répété')
        seen_boxes.add(bid)
        state = box.get('box_status')
        if state not in ('Completed', 'Cancelled'):
            raise Hold('Carton non terminal')
        rows, seen_items = [], set()
        for row in box.get('box_items') or []:
            # Unité entière, sans lots ni séries pour ce pilote.
            if row.get('lot_number') is not None or row.get('lot_date') is not None:
                raise Hold('Lots hors périmètre du banc')
            item = identifier(row.get('inventory_id'), 'Article du carton absent')
            if item in seen_items:
                raise Hold('Ligne répétée dans un carton')
            seen_items.add(item)
            values = tuple(whole(row.get(k), k) for k in ('quantity', 'received_quantity', 'stowed_quantity'))
            if values[2] > values[1] or (state == 'Cancelled' and (values[1] or values[2])):
                raise Hold('Quantités incompatibles avec la politique du banc')
            rows.append((item, *values))
            totals[item] = tuple(a + b for a, b in zip(totals.get(item, (0, 0, 0)), values))
        if not rows:
            raise Hold('Carton sans lignes qualifiées')
        normalized_boxes.append((bid, state, sorted(rows)))
    if not seen_boxes or totals != aggregate:
        raise Hold('Détail et agrégat différents')
    canonical = {'account':account, 'wro':wid, 'fc':fc,
                 'quantities':sorted(aggregate.items()), 'boxes':sorted(normalized_boxes)}
    fingerprint = hashlib.sha256(json.dumps(canonical, sort_keys=True).encode()).hexdigest()
    return wid, fc, aggregate, fingerprint


def database():
    db = sqlite3.connect(':memory:')
    db.executescript('''
      CREATE TABLE receipt(account TEXT, wro INTEGER, fingerprint TEXT,
                           PRIMARY KEY(account,wro));
      CREATE TABLE movement(account TEXT, wro INTEGER, fc INTEGER, inventory INTEGER, qty INTEGER,
                            PRIMARY KEY(account,wro,inventory));
      CREATE TABLE ack(account TEXT, wro INTEGER, state TEXT, PRIMARY KEY(account,wro));
    ''')
    return db


def reconcile(db, snapshot, account='merchant-A', fault=None):
    wid, fc, quantities, fingerprint = normalize(snapshot, account)
    with db:
        old = db.execute('SELECT fingerprint FROM receipt WHERE account=? AND wro=?', (account,wid)).fetchone()
        if old:
            if old[0] != fingerprint:
                raise Hold('Réception déjà appliquée, contenu différent : correction à qualifier')
            return 'already_applied'
        if snapshot.get('remote_synced') is not False:
            raise Hold('Marqueur distant inconnu ou déjà vrai sans preuve locale')
        for item, values in sorted(quantities.items()):
            db.execute('INSERT INTO movement VALUES (?,?,?,?,?)', (account,wid,fc,item,values[2]))
        if fault == 'before_commit':
            raise RuntimeError('Coupure fictive avant validation SQL')
        db.execute('INSERT INTO receipt VALUES (?,?,?)', (account,wid,fingerprint))
        db.execute('INSERT INTO ack VALUES (?,?,?)', (account,wid,'pending'))
    if fault == 'after_commit':
        raise RuntimeError('Accusé local fictif perdu après validation SQL')
    return 'applied'


def acknowledge(db, account, wid, response_received):
    """Modèle de suivi seulement : aucun POST receiving:setExternalSync n'est exécuté."""
    if not db.execute('SELECT 1 FROM receipt WHERE account=? AND wro=?',(account,wid)).fetchone():
        raise Hold('Aucun effet local confirmé')
    if response_received:
        with db:
            db.execute("UPDATE ack SET state='confirmed' WHERE account=? AND wro=?",(account,wid))
    return {'ids':[wid], 'is_external_sync':True}


def snapshot():
    return {'account':'merchant-A','boxes_complete':True,'remote_synced':False,
        'wro':{'id':777,'fulfillment_center':{'id':10},'status':'Completed',
          'inventory_quantities':[
            {'inventory_id':501,'expected_quantity':130,'received_quantity':98,'stowed_quantity':96},
            {'inventory_id':502,'expected_quantity':20,'received_quantity':19,'stowed_quantity':18}]},
        'boxes':[
          {'box_id':11,'box_status':'Completed','box_items':[{'inventory_id':501,'quantity':60,'received_quantity':58,'stowed_quantity':56}]},
          {'box_id':12,'box_status':'Completed','box_items':[{'inventory_id':501,'quantity':40,'received_quantity':40,'stowed_quantity':40},{'inventory_id':502,'quantity':20,'received_quantity':19,'stowed_quantity':18}]},
          {'box_id':13,'box_status':'Cancelled','box_items':[{'inventory_id':501,'quantity':30,'received_quantity':0,'stowed_quantity':0}]}]}


def ledger(db, account='merchant-A'):
    return dict(db.execute('SELECT inventory,SUM(qty) FROM movement WHERE account=? GROUP BY inventory',(account,)))


def run():
    s = snapshot(); expected = {501:96,502:18}
    db = database()
    check(reconcile(db,s)=='applied','Première application')
    check(ledger(db)==expected,'Oracle indépendant : 96 et18')
    check(reconcile(db,s)=='already_applied','Renvoi sans nouvel effet')
    check(db.execute('SELECT COUNT(*) FROM movement').fetchone()[0]==2,'Deux lignes, pas quatre')
    for field, total in [('expected_quantity',150),('received_quantity',117),('stowed_quantity',114)]:
        check(sum(r[field] for r in s['wro']['inventory_quantities'])==total,'Total '+field)
    check(150-114==36 and 117-114==3,'Deux écarts quantitatifs distincts')
    check({501:10+96,502:5+18}=={501:106,502:23},'Mouvements ajoutés à des soldes fictifs')
    # Notifications : chaque événement déclenche la lecture du MÊME état final,
    # jamais l'application de son payload historique. Six ordres de livraison.
    for order in itertools.permutations(['wro.completed','wro.box.stowed','renvoi.completed']):
        d = database()
        for _ in order:
            reconcile(d,copy.deepcopy(s))
        check(ledger(d)==expected,'Ordre des notifications '+str(order))
        check(d.execute('SELECT COUNT(*) FROM receipt').fetchone()[0]==1,'Une réception logique')
    for order in itertools.permutations(range(3)):
        altered = copy.deepcopy(s); altered['boxes']=[altered['boxes'][i] for i in order]
        altered['wro']['inventory_quantities'].reverse()
        for box in altered['boxes']:
            box['box_items'].reverse()
        check(normalize(altered)[3]==normalize(s)[3],'Empreinte indépendante de l’ordre des lignes')
    for point in ['before_commit','after_commit']:
        d = database()
        try:
            reconcile(d,s,fault=point)
        except RuntimeError:
            pass
        check(ledger(d)==({} if point=='before_commit' else expected),'Effet à la coupure '+point)
        check(d.execute('SELECT COUNT(*) FROM receipt').fetchone()[0]==(0 if point=='before_commit' else 1),'Journal à la coupure')
        reconcile(d,s)
        check(ledger(d)==expected,'Reprise après coupure')
        check(d.execute('SELECT COUNT(*) FROM ack').fetchone()[0]==1,'Une intention de marquage')
    body = acknowledge(db,'merchant-A',777,False)
    check(body=={'ids':[777],'is_external_sync':True},'Corps du marquage à effectuer')
    check(db.execute('SELECT state FROM ack').fetchone()[0]=='pending','Réponse perdue : reste en attente')
    check(reconcile(db,s)=='already_applied' and ledger(db)==expected,'Polling répété pendant marqueur faux')
    acknowledge(db,'merchant-A',777,True)
    check(db.execute('SELECT state FROM ack').fetchone()[0]=='confirmed','Réponse finalement reçue')
    variants = {}
    a=copy.deepcopy(s);a['wro']['status']='Processing';variants['non_completed']=a
    a=copy.deepcopy(s);a['account']='merchant-B';variants['wrong_account']=a
    a=copy.deepcopy(s);a['boxes_complete']=False;variants['incomplete_collection']=a
    a=copy.deepcopy(s);a['boxes'].pop();variants['missing_box']=a
    a=copy.deepcopy(s);a['boxes'].append(copy.deepcopy(a['boxes'][0]));variants['duplicate_box']=a
    a=copy.deepcopy(s);a['wro']['inventory_quantities'][0]['stowed_quantity']=None;variants['missing_quantity']=a
    a=copy.deepcopy(s);a['wro']['inventory_quantities'][0]['stowed_quantity']=-1;variants['negative_quantity']=a
    a=copy.deepcopy(s);a['wro']['inventory_quantities'][0]['stowed_quantity']=True;variants['boolean_quantity']=a
    a=copy.deepcopy(s);a['boxes'][0]['box_items'][0]['quantity']='60';variants['string_quantity']=a
    a=copy.deepcopy(s);a['boxes'][0]['box_items'][0]['stowed_quantity']=55;variants['different_totals']=a
    a=copy.deepcopy(s);a['boxes'][0]['box_items'][0]['lot_number']='LOT-FICTIF';variants['lot_out_of_scope']=a
    a=copy.deepcopy(s);a['boxes'][2]['box_items'][0]['stowed_quantity']=1;variants['cancelled_with_quantity']=a
    a=copy.deepcopy(s);a['boxes'][0]['box_status']='Stowing';variants['non_terminal_box']=a
    a=copy.deepcopy(s);a['remote_synced']=True;variants['remote_true_no_local_proof']=a
    a=copy.deepcopy(s);a['remote_synced']=None;variants['remote_unknown']=a
    for name, bad in variants.items():
        d=database()
        try:
            reconcile(d,bad)
        except Hold:
            check(True,'Refus '+name)
        else:
            raise AssertionError('Acceptation indue : '+name)
        check(ledger(d)=={} and d.execute('SELECT COUNT(*) FROM ack').fetchone()[0]==0,'Aucun effet ni marquage '+name)
    changed=copy.deepcopy(s);changed['wro']['inventory_quantities'][0]['stowed_quantity']=95;changed['boxes'][0]['box_items'][0]['stowed_quantity']=55
    try:
        reconcile(db,changed)
    except Hold:
        check(True,'Correction distincte du renvoi')
    else:
        raise AssertionError('Correction silencieuse')
    check(ledger(db)==expected,'Ancien effet conservé sur conflit')
    other=copy.deepcopy(s);other['account']='merchant-B'
    reconcile(db,other,account='merchant-B')
    check(ledger(db,'merchant-A')==expected and ledger(db,'merchant-B')==expected,'Même ID, comptes isolés')
    # Quatre méthodes incorrectes, comparées à l'oracle fixé à114.
    wrong_expected = sum(r['expected_quantity'] for r in s['wro']['inventory_quantities'])
    wrong_received = sum(r['received_quantity'] for r in s['wro']['inventory_quantities'])
    aggregate_total = sum(r['stowed_quantity'] for r in s['wro']['inventory_quantities'])
    boxes_total = sum(r['stowed_quantity'] for box in s['boxes'] for r in box['box_items'])
    bad_results={'expected_as_received':wrong_expected,'received_as_stowed':wrong_received,
                 'aggregate_plus_boxes':aggregate_total+boxes_total,
                 'repeat_after_lost_flag':sum(aggregate_total for _ in range(2))}
    for name,result in bad_results.items():
        check(result!=114,'Témoin incorrect '+name)
    check(12*15==180,'Hypothèse 12 écarts ×15 minutes =3 heures')
    return {'status':'PASS','checks':CHECKS,'receipt':{'501':96,'502':18},'totals':{'expected':150,'received':117,'stowed':114},
            'repeatedReceipt':ledger(db),'eventOrders':6,'boxOrders':6,'refusalCases':list(variants),
            'wrongResults':bad_results,'noExternalCalls':True,'noRealErp':True,'remoteFlagSimulated':True}

if __name__ == '__main__':
    print(json.dumps(run(),ensure_ascii=False,indent=2))