"""Two local API implementations backed by real temporary SQLite databases.

The candidate deliberately moves a write before authorization. This simulates
an API implementation cutover; it does not execute a production schema migration.
"""
import sqlite3


def create_database(path):
    connection = sqlite3.connect(path)
    try:
        connection.execute('CREATE TABLE documents (id INTEGER PRIMARY KEY, tenant TEXT, label TEXT)')
        connection.execute('INSERT INTO documents VALUES (?, ?, ?)', (1, 'alpha', 'quarterly'))
        connection.commit()
    finally:
        connection.close()


def old_api(connection, tenant, label, request_id):
    owner = connection.execute('SELECT tenant FROM documents WHERE id = 1').fetchone()[0]
    if owner != tenant:
        return {'status': 403, 'error': 'forbidden', 'request_id': request_id}
    connection.execute('UPDATE documents SET label = ? WHERE id = 1', (label,))
    connection.commit()
    return {'status': 200, 'label': label, 'request_id': request_id}


def candidate_api(connection, tenant, label, request_id):
    # Deliberately faulty ordering: the rejected request has a committed effect.
    connection.execute('UPDATE documents SET label = ? WHERE id = 1', (label,))
    connection.commit()
    owner = connection.execute('SELECT tenant FROM documents WHERE id = 1').fetchone()[0]
    if owner != tenant:
        return {'status': 403, 'error': 'forbidden', 'request_id': request_id}
    return {'status': 200, 'label': label, 'request_id': request_id}


def observe(path, implementation, case, request_id):
    create_database(path)
    connection = sqlite3.connect(path)
    try:
        response = implementation(connection, case['tenant'], case['label'], request_id)
    finally:
        connection.close()
    # A fresh connection observes committed state after the API call has ended.
    connection = sqlite3.connect(path)
    try:
        rows = connection.execute('SELECT id, tenant, label FROM documents ORDER BY id').fetchall()
    finally:
        connection.close()
    return {'response': response, 'durable_rows': [list(row) for row in rows]}


def permitted_normalization(observation):
    return {
        'response': {key: value for key, value in observation['response'].items() if key != 'request_id'},
        'durable_rows': observation['durable_rows'],
    }


def dangerous_normalization(observation):
    # Deliberately invalid for this contract: drops all durable effects.
    return permitted_normalization(observation)['response']