"""Observable SQL experiment using only an in-memory SQLite database.
This runs SQLite SQL, not EF Core, scaffolding, tracking, rowversion or deployment.
"""
import argparse,json,sqlite3
from contextlib import closing
from pathlib import Path

def initial():
    db=sqlite3.connect(':memory:',isolation_level=None)
    db.row_factory=sqlite3.Row
    db.executescript("CREATE TABLE Orders(Id INTEGER PRIMARY KEY, CustomerName TEXT NOT NULL); INSERT INTO Orders VALUES(1,'Neda'),(2,'Reza'); CREATE TABLE LessonMigrationHistory(Id TEXT PRIMARY KEY); INSERT INTO LessonMigrationHistory VALUES('001_Initial');")
    return db

def run():
    with closing(initial()) as db:
        db.execute('BEGIN')
        db.execute('ALTER TABLE Orders RENAME COLUMN CustomerName TO BuyerName')
        db.execute("ALTER TABLE Orders ADD COLUMN Status TEXT NOT NULL DEFAULT 'Pending'")
        db.execute('ALTER TABLE Orders ADD COLUMN Version INTEGER NOT NULL DEFAULT 1')
        db.execute("INSERT INTO LessonMigrationHistory VALUES('002_BuyerStatusVersion')")
        db.execute('COMMIT')
        migrated=[dict(r) for r in db.execute('SELECT Id,BuyerName,Status,Version FROM Orders ORDER BY Id')]
        history=[r[0] for r in db.execute('SELECT Id FROM LessonMigrationHistory ORDER BY Id')]
        first=db.execute('UPDATE Orders SET Status=?,Version=? WHERE Id=? AND Version=?',('Paid',2,1,1)).rowcount
        second=db.execute('UPDATE Orders SET Status=?,Version=? WHERE Id=? AND Version=?',('Cancelled',2,1,1)).rowcount
        final=dict(db.execute('SELECT Id,BuyerName,Status,Version FROM Orders WHERE Id=1').fetchone())
    with closing(initial()) as db:
        db.execute('BEGIN')
        try:
            db.execute('ALTER TABLE Orders RENAME COLUMN CustomerName TO BuyerName')
            db.execute('INSERT INTO ADeliberatelyMissingTable VALUES(1)')
        except sqlite3.OperationalError:
            db.execute('ROLLBACK')
        original=[dict(r) for r in db.execute('SELECT Id,CustomerName FROM Orders ORDER BY Id')]
    assert migrated==[{'Id':1,'BuyerName':'Neda','Status':'Pending','Version':1},{'Id':2,'BuyerName':'Reza','Status':'Pending','Version':1}]
    assert first==1 and second==0 and final['Status']=='Paid' and final['Version']==2
    assert original==[{'Id':1,'CustomerName':'Neda'},{'Id':2,'CustomerName':'Reza'}]
    return {'execution_kind':'ACTUAL_SQLITE_ON_SYNTHETIC_ROWS','sqlite_version':sqlite3.sqlite_version,'ef_core_executed':False,'database_lifetime':'IN_MEMORY_ONLY','migration_observed_rows':migrated,'lesson_history_not_ef_history':history,'concurrency_observation':{'first_affected_rows':first,'stale_affected_rows':second,'state':'CONCURRENCY_CONFLICT','resolution':'REJECT_STALE_WRITE','retained_row':final},'rollback_observed_original_rows':original,'assertions':'PASS','limits':['No EF model diff or migration scaffold executed','No EF tracking measurement','No shared database or SQL Server execution','No large-table locking/compatibility rehearsal']}
if __name__=='__main__':
    parser=argparse.ArgumentParser(description=__doc__);parser.add_argument('--report',type=Path);args=parser.parse_args()
    result=run();raw=json.dumps(result,ensure_ascii=False,indent=2)+'\n'
    if args.report:
        with args.report.open('x') as f:f.write(raw)
    else:print(raw,end='')