#!/usr/bin/env python3
from pathlib import Path
import hashlib
import shutil
import sqlite3
import tempfile

ROOT = Path(__file__).resolve().parents[2]
MIGRATIONS = ROOT / 'app/server/migrations'
FILES = sorted(MIGRATIONS.glob('*.sql'))
assert len(FILES) == 14, f'Expected 14 migrations, found {len(FILES)}'


def apply(conn: sqlite3.Connection, files: list[Path]) -> None:
    conn.execute('PRAGMA foreign_keys=ON')
    for file in files:
        conn.executescript(file.read_text(encoding='utf-8'))


def integrity(conn: sqlite3.Connection) -> None:
    conn.execute('PRAGMA foreign_keys=ON')
    assert conn.execute('PRAGMA integrity_check').fetchone()[0] == 'ok'
    assert conn.execute('PRAGMA foreign_keys').fetchone()[0] == 1
    assert conn.execute('PRAGMA foreign_key_check').fetchall() == []


def setting(conn: sqlite3.Connection, key: str) -> str:
    row = conn.execute('SELECT value FROM settings WHERE key=?', (key,)).fetchone()
    assert row is not None, key
    return row[0]


with tempfile.TemporaryDirectory(prefix='glmchat-migrations-') as temporary:
    tmp = Path(temporary)
    db = tmp / 'fresh.db'
    conn = sqlite3.connect(db)
    apply(conn, FILES)
    integrity(conn)
    tables = {row[0] for row in conn.execute("SELECT name FROM sqlite_master WHERE type='table'")}
    required = {
        'users', 'settings', 'conversations', 'messages', 'projects', 'workflows',
        'automation_definitions', 'automation_templates', 'sync_mutations',
        'workflow_workers', 'tool_approvals',
    }
    assert required <= tables, sorted(required - tables)
    assert setting(conn, 'app_version') == '2.2.1'
    assert setting(conn, 'model_chat') == 'zai-org/GLM-5.1'
    assert setting(conn, 'model_context_tokens') == '202752'
    assert setting(conn, 'price_input_per_million') == '1.40'
    assert setting(conn, 'price_cached_input_per_million') == '1.40'
    assert setting(conn, 'price_output_per_million') == '4.40'
    assert setting(conn, 'price_vision_input_per_million') == '0.10'
    assert setting(conn, 'price_vision_cached_input_per_million') == '0.10'
    assert setting(conn, 'price_vision_output_per_million') == '0.15'
    assert setting(conn, 'price_tts_kokoro_per_million_characters') == '4.00'
    assert setting(conn, 'price_verified_date') == '2026-07-29'
    conn.commit()
    conn.close()

    # Exact clone and restore evidence.
    original_hash = hashlib.sha256(db.read_bytes()).hexdigest()
    clone = tmp / 'clone.db'
    shutil.copy2(db, clone)
    cloned = sqlite3.connect(clone)
    integrity(cloned)
    cloned.close()
    assert hashlib.sha256(clone.read_bytes()).hexdigest() == original_hash

    # Controlled failed upgrade must roll back all statements in the explicit transaction.
    failure = tmp / 'failure.db'
    shutil.copy2(db, failure)
    failed = sqlite3.connect(failure)
    before = failed.execute('PRAGMA schema_version').fetchone()[0]
    try:
        failed.execute('BEGIN IMMEDIATE')
        failed.execute('CREATE TABLE should_rollback(id INTEGER PRIMARY KEY)')
        failed.execute('INSERT INTO missing_table VALUES(1)')
        failed.commit()
    except sqlite3.Error:
        failed.rollback()
    assert failed.execute("SELECT COUNT(*) FROM sqlite_master WHERE name='should_rollback'").fetchone()[0] == 0
    assert failed.execute('PRAGMA schema_version').fetchone()[0] == before
    integrity(failed)
    failed.close()

    # Synthetic 13 -> 14 upgrade retains user data and migrates only known legacy defaults.
    old = tmp / 'v13.db'
    upgraded = sqlite3.connect(old)
    apply(upgraded, FILES[:13])
    upgraded.execute("UPDATE settings SET value='zai-org/GLM-5.2' WHERE key='model_chat'")
    upgraded.execute("UPDATE settings SET value='262144' WHERE key='model_context_tokens'")
    upgraded.execute("UPDATE settings SET value='0.26' WHERE key='price_cached_input_per_million'")
    upgraded.execute("UPDATE settings SET value='10.00' WHERE key='price_tts_kokoro_per_million_characters'")
    upgraded.execute("UPDATE settings SET value='2026-07-17' WHERE key='price_verified_date'")
    upgraded.execute("INSERT INTO users(id,password_hash) VALUES(1,'test-hash')")
    upgraded.execute(
        "INSERT INTO projects(id,name,root_path,source_type) VALUES(?,?,?,?)",
        ('00000000-0000-4000-8000-000000000001', 'Retained', '/private/project', 'pasted'),
    )
    upgraded.commit()
    apply(upgraded, FILES[13:])
    integrity(upgraded)
    assert upgraded.execute(
        'SELECT name FROM projects WHERE id=?',
        ('00000000-0000-4000-8000-000000000001',),
    ).fetchone()[0] == 'Retained'
    assert setting(upgraded, 'model_chat') == 'zai-org/GLM-5.1'
    assert setting(upgraded, 'model_context_tokens') == '202752'
    assert setting(upgraded, 'price_cached_input_per_million') == '1.40'
    assert setting(upgraded, 'price_tts_kokoro_per_million_characters') == '4.00'
    assert setting(upgraded, 'price_verified_date') == '2026-07-29'
    assert setting(upgraded, 'price_vision_input_per_million') == '0.10'
    upgraded.close()

    # Administrator-customised values must not be overwritten by the catalogue migration.
    custom = tmp / 'custom-v13.db'
    preserved = sqlite3.connect(custom)
    apply(preserved, FILES[:13])
    custom_values = {
        'model_chat': 'custom/provider-model',
        'model_context_tokens': '131072',
        'price_cached_input_per_million': '0.26',
        'price_tts_kokoro_per_million_characters': '7.50',
        'price_verified_date': '2026-07-01',
    }
    for key, value in custom_values.items():
        preserved.execute('UPDATE settings SET value=? WHERE key=?', (value, key))
    preserved.commit()
    apply(preserved, FILES[13:])
    integrity(preserved)
    for key, value in custom_values.items():
        assert setting(preserved, key) == value, (key, setting(preserved, key), value)
    assert setting(preserved, 'price_vision_output_per_million') == '0.15'
    preserved.close()

print('Python migration contracts passed: fresh, clone, rollback, 13-to-14 catalogue upgrade, and custom-value preservation.')