"""Independent read-only SQLite oracle: no application models or snapshot expectations."""
import json,sqlite3,sys
from pathlib import Path
if not __debug__:raise RuntimeError('Assertions required')
database,spec_file,report_file=map(Path,sys.argv[1:]);spec=json.loads(spec_file.read_text());report=json.loads(report_file.read_text())
with sqlite3.connect(database.resolve().as_uri()+'?mode=ro',uri=True) as db:
 db.row_factory=sqlite3.Row
 where='version>0 AND buyer_reference=? AND currency_code=? AND total_minor>=?'
 def rows(buyer,currency,minimum,offset,sort,preview):
  result=[]
  for o in db.execute('SELECT * FROM customer_order_data WHERE '+where+' ORDER BY '+sort+' LIMIT 3 OFFSET ?',(buyer,currency,minimum,offset)):
   value=dict(id=o['id'],orderNumber=o['order_number'],buyer=o['buyer_reference'],totalMinor=o['total_minor'],version=o['version'])
   if preview:value['items']=[dict(id=i['id'],sku=i['sku_code'],lineTotalMinor=i['line_total_minor'],version=i['version']) for i in db.execute('SELECT * FROM order_item_data WHERE version>0 AND customer_order=? ORDER BY sku_code,id LIMIT 1',(o['id'],))]
   result.append(value)
  return result
 if spec['kind']=='retained':
  assert len(report['pages'])==len(spec['offsets'])
  for n,offset in enumerate(spec['offsets']):
   buyer='BUYER-MISSING' if spec['id']=='empty' else 'BUYER-CONTROL' if spec['id']=='isolation' and n==1 else 'BUYER-EXPORT'
   total=db.execute('SELECT count(*) FROM customer_order_data WHERE '+where,(buyer,'USD',0)).fetchone()[0]
   assert {k:v for k,v in report['pages'][n].items() if k not in ['plan','observedCount']}==dict(offset=offset,buyer=buyer,currency='USD',rows=rows(buyer,'USD',0,offset,'total_minor,id',False),total=total)
 else:
  buyer,currency,minimum=spec['buyer'],spec['currency'],spec['minimum'];args=(buyer,currency,minimum)
  groups=[dict(statusId=r['order_status'],count=r['n'],totalMinor=r['amount']) for r in db.execute('SELECT order_status,count(*) n,sum(total_minor) amount FROM customer_order_data WHERE '+where+' GROUP BY order_status ORDER BY order_status',args)]
  total,amount=db.execute('SELECT count(*),sum(total_minor) FROM customer_order_data WHERE '+where,args).fetchone()
  all_buckets={r['code']:sum(g['count'] for g in groups if g['statusId']==r['id']) for r in db.execute('SELECT id,code FROM order_status_data ORDER BY id')}
  expected=dict(buyer=buyer,currency=currency,minimum=minimum,groups=groups,summary=[dict(count=total,totalMinor=amount)],allBuckets=all_buckets,matchedBuckets={k:v for k,v in all_buckets.items() if v},rows=rows(buyer,currency,minimum,0,'id',True),total=total)
  report['groups'].sort(key=lambda r:r['statusId']);assert report==expected,(report,expected)
print('ORDER_INSIGHTS_ORACLE_OK')
