"""Offline SQLite learning example. Creates only in-memory tables; never opens a database file."""
import json
import sys
import sqlite3


def demonstrate():
    rows = [('blog', '1', 'Sample article', 'Body A'), ('demo', '1', 'Sample tool', 'Body B')]
    db = sqlite3.connect(':memory:')
    db.execute('CREATE TABLE append_only (kind TEXT, content_id TEXT, title TEXT, body TEXT)')
    for _ in range(2):
        db.executemany('INSERT INTO append_only VALUES (?, ?, ?, ?)', rows)
    appended = db.execute('SELECT COUNT(*) FROM append_only').fetchone()[0]
    db.execute('CREATE TABLE search_sample (id INTEGER PRIMARY KEY, kind TEXT NOT NULL, content_id TEXT NOT NULL, title TEXT, body TEXT, embedding TEXT, UNIQUE(kind, content_id))')
    statement = """INSERT INTO search_sample(kind, content_id, title, body) VALUES (?, ?, ?, ?)
      ON CONFLICT(kind, content_id) DO UPDATE SET
        embedding = CASE WHEN search_sample.title IS excluded.title
          AND search_sample.body IS excluded.body THEN search_sample.embedding ELSE NULL END,
        title = excluded.title, body = excluded.body"""
    count = lambda: db.execute('SELECT COUNT(*) FROM search_sample').fetchone()[0]
    db.executemany(statement, rows)
    counts = [count()]
    first_id = db.execute("SELECT id FROM search_sample WHERE kind='blog'").fetchone()[0]
    db.execute("UPDATE search_sample SET embedding='synthetic-vector' WHERE kind='blog'")
    db.executemany(statement, rows)
    counts.append(count())
    retained = db.execute("SELECT embedding FROM search_sample WHERE kind='blog'").fetchone()[0] == 'synthetic-vector'
    db.execute(statement, ('blog', '1', 'Sample article', 'Changed sample body'))
    counts.append(count())
    final_id, vector = db.execute("SELECT id, embedding FROM search_sample WHERE kind='blog'").fetchone()
    assert appended == 4 and counts == [2, 2, 2] and first_id == final_id and retained and vector is None
    result = {'python_version': sys.version.split()[0], 'sqlite_version': sqlite3.sqlite_version, 'append_only_rows_after_two_runs': appended,
      'logical_records': 2, 'upsert_row_counts': counts, 'row_identity_preserved': first_id == final_id,
      'unchanged_content_retains_sample_vector': retained, 'changed_content_clears_sample_vector': vector is None,
      'scope': 'Synthetic in-memory example; no production database is opened.'}
    db.close()
    return result


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