"""Original finite SQLite pagination counterexamples; standard library only.
No live services, concurrency, timing benchmark, or production token codec.
Run: python3 pagination-lab.py
"""
import json
import platform
import sqlite3

SCHEMA = '''
CREATE TABLE tickets (
 id INTEGER PRIMARY KEY,
 tenant_id INTEGER NOT NULL,
 created_tick INTEGER NOT NULL,
 status TEXT NOT NULL
);
CREATE INDEX tickets_page ON tickets
 (tenant_id, status, created_tick DESC, id DESC);
'''
BASE = [(106,7,60,'open'),(105,7,50,'open'),(104,7,50,'open'),
        (103,7,40,'open'),(102,7,30,'open'),(101,7,20,'open'),
        (999,8,90,'open'),(998,7,90,'closed')]
SELECT = "SELECT id, created_tick FROM tickets WHERE tenant_id=7 AND status='open'"
ORDER = ' ORDER BY created_tick DESC, id DESC'

def db():
    c = sqlite3.connect(':memory:')
    c.executescript(SCHEMA)
    c.executemany('INSERT INTO tickets VALUES (?,?,?,?)', BASE)
    c.commit()
    return c

def ids(rows):
    return [r[0] for r in rows]

def page(c, boundary=None, limit=3):
    if boundary is None:
        return c.execute(SELECT+ORDER+' LIMIT ?', (limit,)).fetchall()
    return c.execute(SELECT+' AND (created_tick,id) < (?,?)'+ORDER+' LIMIT ?', (*boundary,limit)).fetchall()

def offset(c):
    return ids(c.execute(SELECT+ORDER+' LIMIT 3 OFFSET 3'))

def run():
    results = {}
    with db() as c:
        first = page(c); boundary = (first[-1][1],first[-1][0])
        assert ids(first)==[106,105,104] and boundary==(50,104)
        assert ids(page(c,boundary))==[103,102,101]
        results['baseline']={'first':ids(first),'boundary':boundary,'next':ids(page(c,boundary))}
        first_two=page(c,limit=2)
        wrong=ids(c.execute(SELECT+' AND created_tick < ?'+ORDER+' LIMIT 2',(50,)))
        correct=ids(page(c,(50,105),2))
        assert wrong==[103,102] and correct==[104,103]
        results['tie']={'first':ids(first_two),'timestamp_only':wrong,'tuple':correct}
    with db() as c:
        c.execute("INSERT INTO tickets VALUES (107,7,70,'open')");c.commit()
        assert offset(c)==[104,103,102] and ids(page(c,boundary))==[103,102,101]
        results['insert_before']={'offset':offset(c),'keyset':ids(page(c,boundary))}
    with db() as c:
        c.execute('DELETE FROM tickets WHERE id=106');c.commit()
        assert offset(c)==[102,101] and ids(page(c,boundary))==[103,102,101]
        results['delete_before']={'offset':offset(c),'keyset':ids(page(c,boundary))}
    with db() as c:
        c.execute('DELETE FROM tickets WHERE id=104');c.commit()
        assert ids(page(c,boundary))==[103,102,101]
        results['deleted_boundary']={'keyset':ids(page(c,boundary))}
    with db() as c:
        c.execute('UPDATE tickets SET created_tick=25 WHERE id=105');c.commit()
        assert ids(page(c,boundary,4))==[103,102,105,101]
        results['seen_moves_back']={'keyset':ids(page(c,boundary,4)),'repeated':105}
    with db() as c:
        c.execute('UPDATE tickets SET created_tick=55 WHERE id=103');c.commit()
        assert ids(page(c,boundary))==[102,101]
        results['unseen_moves_ahead']={'keyset':ids(page(c,boundary)),'missed':103}
    with db() as c:
        c.execute("INSERT INTO tickets VALUES (108,7,35,'open')");c.commit()
        rows=c.execute(SELECT+' AND (created_tick,id) <= (?,?) AND (created_tick,id) < (?,?)'+ORDER+' LIMIT 3',(60,106,*boundary)).fetchall()
        assert ids(rows)==[103,108,102]
        results['upper_bound_not_snapshot']={'initial_upper':[60,106],'next':ids(rows),'new_row_included':108}
    with db() as c:
        raw=page(c,limit=4); visible=raw[:3]; last=visible[-1]
        assert ids(raw)==[106,105,104,103]
        assert ids(page(c,(last[1],last[0])))==[103,102,101]
        assert ids(page(c,(raw[-1][1],raw[-1][0])))==[102,101]
        results['lookahead']={'fetched':ids(raw),'returned':ids(visible),'next_boundary':[last[1],last[0]],'wrong_lookahead_boundary_skips':103}
        reverse=c.execute(SELECT+' AND (created_tick,id) > (?,?) ORDER BY created_tick ASC,id ASC LIMIT 3',(40,103)).fetchall()
        assert ids(list(reversed(reverse)))==[106,105,104]
        results['previous_page']={'database_ascending':ids(reverse),'display_descending':ids(list(reversed(reverse)))}
    with db() as c:
        mixed=c.execute(SELECT+' ORDER BY created_tick DESC,id ASC LIMIT 2').fetchall()
        second=c.execute(SELECT+' AND (created_tick < ? OR (created_tick = ? AND id > ?)) ORDER BY created_tick DESC,id ASC LIMIT 2',(50,50,104)).fetchall()
        assert ids(mixed)==[106,104] and ids(second)==[105,103]
        assert c.execute('SELECT (NULL,100) < (50,104)').fetchone()[0] is None
        results['mixed_order']={'first':ids(mixed),'next':ids(second)}
        results['null_comparison']=None
        plan=[r[3] for r in c.execute('EXPLAIN QUERY PLAN '+SELECT+' AND (created_tick,id) < (?,?)'+ORDER+' LIMIT 3',boundary)]
        assert any('tickets_page' in p for p in plan)
        results['query_plan']=plan
    return {'environment':{'python':platform.python_version(),'sqlite':sqlite3.sqlite_version},
            'scope':'Finite sequential synthetic in-memory SQLite queries; no PostgreSQL runtime, concurrent connections, production API or latency benchmark.',
            'results':results,'assertions':'passed'}

if __name__=='__main__':
    print(json.dumps(run(),ensure_ascii=False,indent=2))