"""Synthetic runbook decision/query fixture, not an operator usability study.
The SQLite authorizer denies database mutations during the observation phase.
All times and thresholds are illustrative integers, not production settings.
"""
import sqlite3
import json


def fixture(recovered=False):
    db=sqlite3.connect(':memory:')
    db.execute('CREATE TABLE import_jobs(batch TEXT, id TEXT PRIMARY KEY, state TEXT)')
    rows=[('B17',f'J{n:02}','confirmed') for n in range(1,41)]
    rows += [('B17','J41','confirmed' if recovered else 'not_started'),('B17','J42','confirmed' if recovered else 'issued_unknown'),('B17','J43','confirmed' if recovered else 'validation_rejected')]
    db.executemany('INSERT INTO import_jobs VALUES (?,?,?)',rows);db.commit()
    allowed={sqlite3.SQLITE_SELECT,sqlite3.SQLITE_READ,sqlite3.SQLITE_FUNCTION}
    db.set_authorizer(lambda action,*args:sqlite3.SQLITE_OK if action in allowed else sqlite3.SQLITE_DENY)
    return db


def observe(db,access=True,observed_at=100,now=130):
    if not access:return {'decision':'escalate_access','query_run':False}
    if now-observed_at>90:return {'decision':'refresh_observation','query_run':False}
    rows=db.execute('SELECT id,state FROM import_jobs WHERE batch=? ORDER BY id',('B17',)).fetchall()
    confirmed=sum(state=='confirmed' for _,state in rows)
    if len(rows)!=43:return {'decision':'escalate_membership','query_run':True,'accepted':len(rows)}
    if confirmed==43:return {'decision':'verify_downstream_and_close','query_run':True,'accepted':43,'confirmed':43,'unresolved':[]}
    actions={'not_started':'eligible_for_authorised_start','issued_unknown':'reconcile_original_request_before_retry','validation_rejected':'owner_corrects_input_then_new_validation'}
    return {'decision':'triage_per_record','query_run':True,'accepted':43,'confirmed':confirmed,'unresolved':[{'id':i,'state':s,'next':actions.get(s,'escalate_unknown_state')} for i,s in rows if s!='confirmed']}


def run():
    db=fixture();ongoing=observe(db)
    assert ongoing['confirmed']==40 and len(ongoing['unresolved'])==3
    assert ongoing['unresolved'][1]['next']=='reconcile_original_request_before_retry'
    try:db.execute('DELETE FROM import_jobs')
    except sqlite3.DatabaseError:write_denied=True
    else:raise AssertionError('observation permitted deletion')
    assert observe(db,access=False)=={'decision':'escalate_access','query_run':False}
    assert observe(db,now=191)=={'decision':'refresh_observation','query_run':False}
    recovered=observe(fixture(True));assert recovered['decision']=='verify_downstream_and_close'
    return {'initial':ongoing,'recovered':recovered,'missingAccess':observe(db,access=False),'stale':observe(db,now=191),'readOnlyMutationDenied':write_denied,'nextHumanChecks':['Verify receiving import result, not only worker health.','Record current batch snapshot, attempted actions and unresolved request IDs.','Page designated import owner if authority or effect is uncertain.']}

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