"""SQLite pagination walks with one controlled mutation between page one and two."""
import csv,json,sqlite3
from pathlib import Path
NAMES=['Static keys','Insert before cursor','Delete before cursor','Four tied timestamps','Move seen row','Insert after cursor']
def walk(case,mode):
    db=sqlite3.connect(':memory:');db.execute('CREATE TABLE items(id INTEGER PRIMARY KEY, sort_key INTEGER NOT NULL)')
    fixture=[(i,i*10) for i in range(1,7)]
    if case=='Four tied timestamps':fixture=[(1,10),(2,20),(3,30),(4,30),(5,30),(6,30)]
    db.executemany('INSERT INTO items VALUES (?,?)',fixture);db.commit()
    pages=[];offset=0;cursor=None
    def page():
        if mode=='offset':return db.execute('SELECT id,sort_key FROM items ORDER BY sort_key DESC,id DESC LIMIT 3 OFFSET ?',(offset,)).fetchall()
        if cursor is None:return db.execute('SELECT id,sort_key FROM items ORDER BY sort_key DESC,id DESC LIMIT 3').fetchall()
        key,id_=cursor
        if mode=='timestamp':return db.execute('SELECT id,sort_key FROM items WHERE sort_key < ? ORDER BY sort_key DESC,id DESC LIMIT 3',(key,)).fetchall()
        return db.execute('SELECT id,sort_key FROM items WHERE sort_key < ? OR (sort_key = ? AND id < ?) ORDER BY sort_key DESC,id DESC LIMIT 3',(key,key,id_)).fetchall()
    for number in range(10):
        rows=page()
        if not rows:break
        pages.append([r[0] for r in rows]);offset+=len(rows);cursor=(rows[-1][1],rows[-1][0])
        if number==0:
            if case=='Insert before cursor':db.execute('INSERT INTO items VALUES (7,70)')
            if case=='Delete before cursor':db.execute('DELETE FROM items WHERE id=6')
            if case=='Move seen row':db.execute('UPDATE items SET sort_key=5 WHERE id=6')
            if case=='Insert after cursor':db.execute('INSERT INTO items VALUES (7,35)')
            db.commit()
    else:raise AssertionError('Walk did not terminate')
    seen=[i for p in pages for i in p];surviving={r[0] for r in db.execute('SELECT id FROM items')} & set(range(1,7));db.close()
    return dict(pages=pages,ids=seen,duplicates=len(seen)-len(set(seen)),missingOriginal=sorted(surviving-set(seen)))
def run():
    rows=[dict(case=name,**{mode:walk(name,mode) for mode in ['offset','cursor','timestamp']}) for name in NAMES]
    assert [len(r['offset']['missingOriginal']) for r in rows]==[0,0,1,0,1,0]
    assert [len(r['cursor']['missingOriginal']) for r in rows]==[0]*6
    assert [r['offset']['duplicates'] for r in rows]==[0,1,0,0,1,0]
    assert [r['cursor']['duplicates'] for r in rows]==[0,0,0,0,1,0]
    assert rows[3]['timestamp']['missingOriginal']==[3]
    assert 7 not in rows[1]['cursor']['ids'] and 7 in rows[5]['cursor']['ids']
    return dict(scope='Six finite SQLite walks; page size 3; one scheduled mutation after first page; no snapshot, concurrent threads or performance benchmark.',rows=rows)
if __name__=='__main__':
    result=run();assert result==run();p=Path(__file__).resolve().parent;(p/'results.json').write_text(json.dumps(result,indent=2)+'\n')
    with (p/'results.csv').open('w',newline='') as f:
        w=csv.writer(f);w.writerow(['case','method','pages','duplicate_count','missing_surviving_original_ids'])
        for row in result['rows']:
            for mode in ['offset','cursor','timestamp']:
                v=row[mode];w.writerow([row['case'],mode,json.dumps(v['pages']),v['duplicates'],json.dumps(v['missingOriginal'])])
    print(json.dumps(result,indent=2))