"""Finite SQLite compatibility and write-order examples, not a production migrator.

Run with Python 3. Uses independent in-memory fixtures, no network or dependencies.
The expanded fixture starts with both columns populated to isolate write behavior.
"""
import json
import sqlite3
from pathlib import Path


def database(stage):
    db = sqlite3.connect(':memory:')
    if stage == 'Legacy schema':
        db.execute('CREATE TABLE profiles (id INTEGER PRIMARY KEY, display_name TEXT)')
        db.execute("INSERT INTO profiles VALUES (1, 'Ana')")
    elif stage == 'Expanded schema':
        db.execute('CREATE TABLE profiles (id INTEGER PRIMARY KEY, display_name TEXT, public_name TEXT)')
        db.execute("INSERT INTO profiles VALUES (1, 'Ana', 'Ana')")
    else:
        assert stage == 'Contracted schema'
        db.execute('CREATE TABLE profiles (id INTEGER PRIMARY KEY, public_name TEXT)')
        db.execute("INSERT INTO profiles VALUES (1, 'Ana')")
    db.commit()
    return db


def read(db, version):
    column = 'public_name' if version == 'New client' else 'display_name'
    return db.execute(f'SELECT {column} FROM profiles WHERE id = 1').fetchone()[0]


def write(db, version, value):
    if version == 'Bridge client':
        db.execute('UPDATE profiles SET display_name = ?, public_name = ? WHERE id = 1', (value, value))
    else:
        column = 'public_name' if version == 'New client' else 'display_name'
        db.execute(f'UPDATE profiles SET {column} = ? WHERE id = 1', (value,))


def snapshot(db):
    row = db.execute('SELECT display_name, public_name FROM profiles WHERE id = 1').fetchone()
    return {'display_name': row[0], 'public_name': row[1], 'equal': row[0] == row[1]}


def compatibility():
    result = []
    expected = [[True, False, False], [True, True, True], [False, False, True]]
    for si, stage in enumerate(['Legacy schema', 'Expanded schema', 'Contracted schema']):
        for ci, client in enumerate(['Legacy client', 'Bridge client', 'New client']):
            db = database(stage)
            try:
                before = read(db, client)
                with db:
                    write(db, client, 'Bea')
                after = read(db, client)
                ok = before == 'Ana' and after == 'Bea'
                detail = 'Read, update and read succeeded'
            except sqlite3.OperationalError as error:
                ok = False
                detail = str(error)
            assert ok == expected[si][ci]
            result.append({'schema': stage, 'client': client, 'sql_compatible': ok, 'detail': detail})
            db.close()
    return result


def write_cases():
    results = []
    db = database('Expanded schema')
    with db:
        db.execute('UPDATE profiles SET public_name = display_name')
        write(db, 'Legacy client', 'Bea')
    state = snapshot(db)
    assert state == {'display_name': 'Bea', 'public_name': 'Ana', 'equal': False}
    results.append({'case': 'Backfill then legacy write', **state, 'visible_value': read(db, 'New client')})
    db.close()

    db = database('Expanded schema')
    with db:
        write(db, 'Legacy client', 'Bea')
        db.execute('UPDATE profiles SET public_name = display_name')
    state = snapshot(db)
    assert state == {'display_name': 'Bea', 'public_name': 'Bea', 'equal': True}
    results.append({'case': 'Legacy write then final backfill', **state, 'visible_value': read(db, 'New client')})
    db.close()

    db = database('Expanded schema')
    with db:
        write(db, 'Bridge client', 'Bea')
    state = snapshot(db)
    assert state == {'display_name': 'Bea', 'public_name': 'Bea', 'equal': True}
    results.append({'case': 'Bridge writes both columns', **state, 'visible_value': read(db, 'Legacy client')})
    db.close()

    db = database('Expanded schema')
    with db:
        write(db, 'New client', 'Bea')
    state = snapshot(db)
    assert state == {'display_name': 'Ana', 'public_name': 'Bea', 'equal': False}
    results.append({'case': 'New-only write then legacy read', **state, 'visible_value': read(db, 'Legacy client')})
    db.close()

    db = database('Expanded schema')
    try:
        with db:
            db.execute("UPDATE profiles SET display_name = 'Bea' WHERE id = 1")
            raise RuntimeError('Injected failure before updating the second column')
    except RuntimeError:
        pass
    state = snapshot(db)
    assert state == {'display_name': 'Ana', 'public_name': 'Ana', 'equal': True}
    results.append({'case': 'Failure inside explicit transaction', **state, 'visible_value': read(db, 'Legacy client')})
    db.close()
    return results


def run():
    return {'scope': 'Independent single-connection SQLite fixtures; finite sequential operations, not concurrent DDL or production measurements', 'compatibility': compatibility(), 'write_cases': write_cases()}


if __name__ == '__main__':
    result = run()
    assert result == run(), 'Examples must repeat identically'
    destination = Path(__file__).with_name('results.json')
    destination.write_text(json.dumps(result, indent=2) + '\n')
    print(f'Passed 9 compatibility cells and 5 write-order cases; wrote {destination.name}')