"""Read-only SQL oracle for native page output, independent of application predicates."""
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
    def page(buyer,offset,limit,sort,preview):
        ordering='total_minor ASC,id ASC' if sort=='composite' else 'id DESC' if sort=='desc' else 'id ASC'
        total=db.execute('SELECT count(*) FROM customer_order_data WHERE version>0 AND buyer_reference=?',(buyer,)).fetchone()[0]
        rows=[]
        for o in db.execute('SELECT * FROM customer_order_data WHERE version>0 AND buyer_reference=? ORDER BY '+ordering+' LIMIT ? OFFSET ?',(buyer,limit,offset)):
            row=dict(id=o['id'],orderNumber=o['order_number'],buyer=o['buyer_reference'],totalMinor=o['total_minor'],version=o['version'])
            if preview:
                row['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'],))]
            rows.append(row)
        return dict(rows=rows,total=total)
    if spec['kind']=='bounded':
        if spec.get('invalid'):
            assert report==dict(rejected=True,reason='Page bounds must be offset >= 0 and limit in [1, 3]')
        else:
            assert report==dict(offset=spec['offset'],limit=spec['limit'],buyer=spec['buyer'],**page(spec['buyer'],spec['offset'],spec['limit'],'composite',True))
    else:
        assert report['mode']==spec['mode'] and len(report['pages'])==len(spec['offsets'])
        for n,offset in enumerate(spec['offsets']):
            buyer='BUYER-CONTROL' if spec['mode']=='predicate' and n==1 else 'BUYER-EXPORT'
            actual={k:v for k,v in report['pages'][n].items() if k!='plan'}
            assert actual==dict(offset=offset,buyer=buyer,**page(buyer,offset,3,spec['mode'],False))
        if spec['mode'] in ('asc','desc'):
            ids=[o['id'] for p in report['pages'] for o in p['rows']]
            expected=[o['id'] for o in page('BUYER-EXPORT',0,100,spec['mode'],False)['rows']]
            assert ids==expected and len(set(ids))==len(ids)
print('ORDER_PAGE_ORACLE_OK')
