"""Fictional two-store exercise; not WooCommerce tables or a migration tool."""
from decimal import Decimal
from pathlib import Path
from tempfile import TemporaryDirectory
import sqlite3

if sqlite3.sqlite_version_info < (3, 24, 0):
    raise RuntimeError("SQLite 3.24 or newer required")

checks = 0


def check(condition, label):
    global checks
    if not condition:
        raise AssertionError(label)
    checks += 1
    print('PASS', label)


def table_for(authority):
    if authority not in {'hpos', 'posts'}:
        raise ValueError('unknown fixture authority')
    return {'hpos': 'hpos_fixture', 'posts': 'posts_fixture'}[authority]


def snapshot(connection, authority, order_id):
    table = table_for(authority)
    row = connection.execute(
        'SELECT id,status,total,currency,erp_ref FROM ' + table + ' WHERE id=?',
        (order_id,),
    ).fetchone()
    return None if row is None else dict(zip(['id', 'status', 'total', 'currency', 'erp_ref'], row))


def export_ids(connection, authority):
    # A fixture rule for comparison, not a recommended native payment rule.
    return [r[0] for r in connection.execute(
        'SELECT id FROM ' + table_for(authority) + " WHERE status IN ('processing','completed') ORDER BY id"
    )]


def import_fixture(connection, shop, order_id):
    if not shop:
        raise ValueError('missing shop identity')
    # This is a local ERP fixture and its own unique constraint, not an ERP API guarantee.
    reference = shop + '-ERP-' + str(order_id)
    connection.execute(
        'INSERT INTO erp_fixture VALUES (?,?,?) ON CONFLICT(shop,order_id) DO NOTHING',
        (shop, order_id, reference),
    )
    connection.commit()
    return connection.execute(
        'SELECT reference FROM erp_fixture WHERE shop=? AND order_id=?', (shop, order_id)
    ).fetchone()[0]


def receipt_fixture(connection, authority, order_id, reference):
    connection.execute('UPDATE ' + table_for(authority) + ' SET erp_ref=? WHERE id=?', (reference, order_id))
    connection.commit()


def switch_plan(current, target, pending):
    table_for(current)
    table_for(target)
    if pending < 0:
        raise ValueError('invalid pending count')
    if current == target:
        return 'no change'
    if pending:
        return 'blocked: synchronization pending'
    return 'eligible for separate native checks'


def refuses(action, label):
    try:
        action()
    except ValueError:
        check(True, label)
    else:
        raise AssertionError(label)


with TemporaryDirectory(prefix='hpos-export-local-') as directory:
    database = Path(directory) / 'fixture.sqlite'
    with sqlite3.connect(database) as connection:
        for table in ['hpos_fixture', 'posts_fixture']:
            connection.execute('CREATE TABLE ' + table + ' (id INTEGER PRIMARY KEY,status TEXT,total TEXT,currency TEXT,erp_ref TEXT)')
        connection.executemany('INSERT INTO hpos_fixture VALUES (?,?,?,?,?)', [
            (501, 'processing', '129.90', 'EUR', None),
            (502, 'completed', '80.00', 'EUR', 'known-ERP-502'),
            (503, 'processing', '65.20', 'CHF', None),
        ])
        connection.executemany('INSERT INTO posts_fixture VALUES (?,?,?,?,?)', [
            (501, 'pending', '119.90', 'EUR', None),
            (502, 'completed', '80.00', 'EUR', 'known-ERP-502'),
        ])
        connection.execute('CREATE TABLE erp_fixture (shop TEXT NOT NULL,order_id INTEGER NOT NULL,reference TEXT NOT NULL,PRIMARY KEY(shop,order_id))')
        connection.commit()
        active = snapshot(connection, 'hpos', 501)
        old = snapshot(connection, 'posts', 501)
        check(active['status'] == 'processing', 'current fixture sees changed status')
        check(old['status'] == 'pending', 'legacy fixture has stale status')
        check(Decimal(active['total']) - Decimal(old['total']) == Decimal('10.00'), 'amount difference remains exact decimal')
        check(export_ids(connection, 'posts') == [502], 'legacy export misses changed and newly created orders')
        check(export_ids(connection, 'hpos') == [501, 502, 503], 'current fixture export contains three eligible orders')
        check(set(export_ids(connection, 'hpos')) - set(export_ids(connection, 'posts')) == {501, 503}, 'missing identities diagnosed separately from counts')
        check(snapshot(connection, 'hpos', 503)['currency'] == 'CHF', 'currency travels with total')
        check(snapshot(connection, 'posts', 503) is None, 'absent legacy row is not proof of absent current order')
        check(snapshot(connection, 'posts', 501) == old, 'posts-authoritative fixture can also be read explicitly')
        refuses(lambda: snapshot(connection, 'other', 501), 'reject unknown authority')
        check(snapshot(connection, 'hpos', 999) is None, 'unknown order remains missing')
        before = connection.total_changes
        snapshot(connection, 'hpos', 501)
        check(connection.total_changes == before, 'snapshot read does not modify fixture')
        reference = import_fixture(connection, 'shop-A', 501)
        receipt_fixture(connection, 'posts', 501, reference)
        check(snapshot(connection, 'posts', 501)['erp_ref'] == reference, 'wrong-store receipt is visible in legacy fixture')
        check(snapshot(connection, 'hpos', 501)['erp_ref'] is None, 'current fixture still has no receipt')
        check(connection.execute('SELECT count(*) FROM erp_fixture').fetchone()[0] == 1, 'local ERP effect already exists despite absent current receipt')
        check(import_fixture(connection, 'shop-A', 501) == reference, 'repeated local import returns same fixture reference')
        check(connection.execute('SELECT count(*) FROM erp_fixture').fetchone()[0] == 1, 'fixture unique constraint prevents second ERP row')
        receipt_fixture(connection, 'hpos', 501, reference)
        check(snapshot(connection, 'hpos', 501)['erp_ref'] == reference, 'receipt written to current fixture becomes readable')
        other = import_fixture(connection, 'shop-B', 501)
        check(other != reference, 'same order id in another shop is a separate identity')
        refuses(lambda: import_fixture(connection, '', 501), 'reject missing shop identity')
        check(switch_plan('hpos', 'posts', 1).startswith('blocked'), 'pending synchronization blocks switch plan')
        check(switch_plan('hpos', 'posts', 0).startswith('eligible'), 'zero pending is only eligibility for separate checks')
        check(switch_plan('hpos', 'hpos', 1) == 'no change', 'same authority is not a migration')
        refuses(lambda: switch_plan('hpos', 'posts', -1), 'reject invalid pending count')
    with sqlite3.connect(database) as reopened:
        check(import_fixture(reopened, 'shop-A', 501) == reference, 'fixture correspondence survives reopened connection')
print('CHECKS', checks)
print('WooCommerce calls: 0; ERP API calls: 0; WordPress options changed: 0.')