"""Independent read-only SQLite snapshot oracle. No mutation or fault installation."""
import json,sqlite3,sys
from pathlib import Path
p=Path(sys.argv[1]).resolve();db=sqlite3.connect(p.as_uri()+'?mode=ro',uri=True);db.row_factory=sqlite3.Row
assert db.execute('pragma integrity_check').fetchone()[0]=='ok'
assert not db.execute('pragma foreign_key_check').fetchall()
tables={'roots':'commerce_platform','statuses':'order_status','orders':'customer_order','items':'order_item','inventory':'inventory','reservations':'stock_reservation'}
s={k:[dict(r) for r in db.execute('select * from '+v+'_data order by id')] for k,v in tables.items()}
for rows in s.values():
 for row in rows:assert row['id']>0 and row['version']>0
if sys.argv[2]=='bootstrap':assert len(s['roots'])==1 and len(s['statuses'])==3 and not any(s[k] for k in ['orders','items','inventory','reservations'])
if sys.argv[2]=='seed':
 assert len(s['roots'])==2 and len(s['statuses'])==3 and len(s['orders'])==16 and len(s['items'])==16 and len(s['inventory'])==4 and not s['reservations']
 for r in s['roots']:assert r['reservation_sequence']==0
 for r in s['orders']:assert r['order_status']==1001 and r['currency_code']=='USD' and r['order_note']=='Stock reservation fixture order'
 for r in s['items']:assert r['sku_code']=='SKU-WIDGET' and r['unit_price_minor']==1250 and r['line_total_minor']==r['quantity']*1250
 for r in s['inventory']:assert r['reserved_quantity']==0 and r['warehouse_note']=='Initial warehouse stock'
if sys.argv[2]!='bootstrap':
 f=json.loads(Path(__file__).with_name('fixture.json').read_text())
 assert sorted(r['tenant_key'] for r in s['roots'])==sorted(t['key'] for t in f['tenants'])
 assert [(r['id'],r['code']) for r in s['statuses']]==[(1001,'DRAFT'),(1002,'CONFIRMED'),(1003,'CANCELLED')]
 assert len(s['orders'])==16 and len(s['items'])==16 and len(s['inventory'])==4
 assert len({(r['commerce_platform'],r['buyer_reference'],r['request_key']) for r in s['reservations']})==len(s['reservations'])
 for root in s['roots']:
  receipts=[r for r in s['reservations'] if r['commerce_platform']==root['id']]
  assert root['reservation_sequence']==len(receipts)
  assert root['version']==(2 if root['tenant_key']=='TENANT-A' else 1)+len(receipts)
  tenant=next(t for t in f['tenants'] if t['key']==root['tenant_key'])
  for sku,available in [(f['sku'],tenant['available']),(f['controlSku'],f['controlAvailable'])]:
   stocks=[i for i in s['inventory'] if i['commerce_platform']==root['id'] and i['sku_code']==sku];assert len(stocks)==1
   stock=stocks[0];reserved=[r for r in receipts if r['inventory']==stock['id']]
   assert stock['reserved_quantity']==sum(r['quantity'] for r in reserved)
   assert stock['available_quantity']==available-stock['reserved_quantity']>=0
   assert stock['version']==1+len(reserved) and stock['warehouse_note']=='Initial warehouse stock'
  for buyer in f['buyers']:
   for d in f['orders']:
    orders=[o for o in s['orders'] if o['commerce_platform']==root['id'] and o['buyer_reference']==buyer and o['order_number']==d['number']];assert len(orders)==1
    order=orders[0];reserved=[r for r in receipts if r['customer_order']==order['id']];assert len(reserved)<=1
    assert order['version']==1+len(reserved) and order['order_status']==(1002 if reserved else 1001)
    assert order['total_minor']==d['quantity']*f['unitPriceMinor'] and order['currency_code']=='USD'
    assert order['order_note']=='Stock reservation fixture order'
    items=[i for i in s['items'] if i['customer_order']==order['id']];assert len(items)==1
    i=items[0];assert i['version']==1 and i['sku_code']==f['sku'] and i['quantity']==d['quantity'] and i['unit_price_minor']==f['unitPriceMinor'] and i['line_total_minor']==order['total_minor']
    for r in reserved:
     assert r['version']==1 and r['order_number']==d['number'] and r['buyer_reference']==buyer and r['quantity']==d['quantity'] and r['sku_code']==f['sku']
     stock=next(i for i in s['inventory'] if i['id']==r['inventory']);assert stock['commerce_platform']==root['id'] and stock['sku_code']==r['sku_code']
print(json.dumps(s,sort_keys=True));db.close()
