"""Independent read-only SQLite oracle. Setup and lock probes live elsewhere."""
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
try:
 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
 assert [(r['id'],r['code']) for r in s['statuses']]==[(1001,'DRAFT'),(1002,'CONFIRMED'),(1003,'CANCELLED')]
 if sys.argv[2]=='bootstrap':assert len(s['roots'])==1 and not any(s[k] for k in ['orders','items','inventory','reservations'])
 else:
  f=json.loads(Path(__file__).with_name('fixture.json').read_text())
  assert len(s['roots'])==2 and len(s['orders'])==16 and len(s['items'])==16 and len(s['inventory'])==4 and not s['reservations']
  assert sorted(r['tenant_key'] for r in s['roots'])==sorted(t['key'] for t in f['tenants'])
  for root in s['roots']:
   assert root['reservation_sequence']==0 and root['version']==(2 if root['tenant_key']=='TENANT-A' else 1)
   tenant=next(t for t in f['tenants'] if t['key']==root['tenant_key'])
   for sku,qty in [(f['sku'],tenant['available']),(f['controlSku'],f['controlAvailable'])]:
    stocks=[r for r in s['inventory'] if r['commerce_platform']==root['id'] and r['sku_code']==sku];assert len(stocks)==1
    r=stocks[0];assert r['available_quantity']==qty and r['reserved_quantity']==0 and r['version']==1 and r['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
     o=orders[0];items=[i for i in s['items'] if i['customer_order']==o['id']];assert len(items)==1;i=items[0]
     assert o['order_status']==1001 and o['currency_code']=='USD' and o['total_minor']==d['quantity']*f['unitPriceMinor']
     assert i['sku_code']==f['sku'] and i['quantity']==d['quantity'] and i['unit_price_minor']==f['unitPriceMinor'] and i['line_total_minor']==o['total_minor']
     assert o['version']==i['version']
     if o['version']==1:assert o['order_note']=='Stock reservation fixture order' and i['product_name']==f['sku']
     else:assert o['order_note']==i['product_name'] and o['order_note'].startswith('STRESS-') and not o['order_note'].startswith('STRESS-PROVIDER-')
  if sys.argv[2]=='seed':assert all(o['version']==1 for o in s['orders'])
 print(json.dumps(s,sort_keys=True))
finally:db.close()
