"""Tests from Lab 06's 09_memory_store.py: real rows from Alex's memory (the stored facts, summary and
cached prompts are recorded gpt-5.4-nano outputs). Run: pytest test_store_practice.py"""
import sqlite3
import struct

from store_practice import add_value, fact_on, facts_now, forget_everywhere, scan

PHONE = "+1 415 555 0142"
PHONE_MSG = f"Oh, and my phone number is {PHONE} in case you need it for booking reminders."
SCHEMA = """
CREATE TABLE messages (id INTEGER PRIMARY KEY, user TEXT, session INTEGER, date TEXT, role TEXT, content TEXT);
CREATE TABLE search_index (message_id INTEGER, text TEXT, vector BLOB);
CREATE TABLE summaries (user TEXT PRIMARY KEY, text TEXT, updated TEXT);
CREATE TABLE facts (user TEXT, key TEXT, value TEXT, details TEXT, valid_from TEXT, valid_to TEXT);
CREATE TABLE audit_log (at TEXT, user TEXT, event TEXT);
CREATE TABLE response_cache (prompt_hash TEXT PRIMARY KEY, user TEXT, prompt TEXT, reply TEXT);
"""
TABLES = ["messages", "search_index", "summaries", "facts", "audit_log", "response_cache"]


def alex() -> sqlite3.Connection:
    db = sqlite3.connect(":memory:")
    db.executescript(SCHEMA)
    msgs = [
        (1, "2026-01-05", "user", "I work at Freightly. I have a beagle named Biscuit."),
        (2, "2026-01-19", "user", PHONE_MSG),
        (3, "2026-02-02", "user", "I left Freightly last week. I'm at Ledgerly now."),
        (5, "2026-03-02", "user", "And please forget my phone number. I shouldn't have shared it."),
    ]
    for i, (s, d, r, c) in enumerate(msgs, 1):
        db.execute("INSERT INTO messages VALUES (?,?,?,?,?,?)", (i, "alex", s, d, r, c))
        db.execute("INSERT INTO search_index VALUES (?,?,?)", (i, c, struct.pack("5f", -0.006, -0.016, 0.018, 0.014, -0.023)))
    db.execute("INSERT INTO summaries VALUES (?,?,?)", ("alex",
        "[Session 2 · 2026-01-19] User planned ~5-day May trip to Lisbon; vegetarian; ~€1,500 budget excluding flights. "
        f"Suggested stay: Alfama or Baixa; pick up from Alfama. Booking reminders via {PHONE}.", "2026-03-02"))
    facts = [
        ("employer", "Freightly", "2026-01-05", "2026-02-02"),
        ("employer", "Ledgerly", "2026-02-02", None),
        ("diet", "Vegetarian", "2026-01-19", "2026-03-02"),
        ("diet", "Pescatarian", "2026-03-02", None),
        ("contact_phone_number", PHONE, "2026-01-19", "2026-03-02"),   # closed by the forget request, not deleted
        ("dog_name", "Biscuit", "2026-01-05", None),
    ]
    for k, v, f, t in facts:
        db.execute("INSERT INTO facts VALUES (?,?,?,?,?,?)", ("alex", k, v, "", f, t))
        db.execute("INSERT INTO audit_log VALUES (?,?,?)", (f, "alex", f"fact saved: {k} = {v}"))
    line = f"[Session 2 · 2026-01-19] User said: {PHONE_MSG}"
    db.execute("INSERT INTO response_cache VALUES (?,?,?,?)", ("a1", "alex", f"{line}\n\nQuestion: What's my phone number?", "I don't know your phone number."))
    db.execute("INSERT INTO response_cache VALUES (?,?,?,?)", ("b2", "alex", f"{line}\n\nQuestion: What's my dog's name?", "Your dog's name is Biscuit."))
    return db


def test_what_was_true_on_a_date():
    db = alex()
    assert fact_on(db, "employer", "2026-01-20") == ["Freightly"]
    assert fact_on(db, "employer", "2026-02-02") == ["Ledgerly"]     # replaced that day
    assert fact_on(db, "diet", "2026-03-10") == ["Pescatarian"]
    assert fact_on(db, "employer", "2026-01-01") == []


def test_replace_closes_a_row_and_a_list_adds_one():
    db = alex()
    add_value(db, "employer", "Acme", "2026-04-01")
    assert facts_now(db, "employer") == ["Acme"]
    assert fact_on(db, "employer", "2026-03-10") == ["Ledgerly"]     # history kept, by design
    add_value(db, "dog_name", "Comet", "2026-02-12")
    assert facts_now(db, "dog_name") == ["Biscuit", "Comet"]
    add_value(db, "dog_name", "Comet", "2026-02-20")                  # already true: no new row
    assert facts_now(db, "dog_name") == ["Biscuit", "Comet"]
    assert ("2026-02-12", "alex", "fact saved: dog_name = Comet") in db.execute("SELECT * FROM audit_log").fetchall()


def test_scan_finds_every_copy_in_every_table():
    db = alex()
    assert scan(db, PHONE) == {"messages": 1, "search_index": 1, "summaries": 1, "facts": 1, "audit_log": 1, "response_cache": 2}
    db.execute("CREATE TABLE analytics_events (at TEXT, payload TEXT)")
    db.execute("INSERT INTO analytics_events VALUES ('2026-01-19', ?)", (f'{{"phone": "{PHONE}"}}',))
    assert scan(db, PHONE)["analytics_events"] == 1                    # a new table is scanned too


def test_deleting_the_messages_leaves_five_tables():
    db = alex()
    db.execute("DELETE FROM messages WHERE content LIKE ?", (f"%{PHONE}%",))
    left = [t for t, n in scan(db, PHONE).items() if n]
    assert left == ["search_index", "summaries", "facts", "audit_log", "response_cache"]


def test_forget_everywhere_reaches_every_table():
    db = alex()
    forget_everywhere(db, PHONE, "2026-03-02")
    assert scan(db, PHONE) == {t: 0 for t in TABLES}
    assert "[removed at user request]" in db.execute("SELECT text FROM summaries").fetchone()[0]
    assert facts_now(db, "employer") == ["Ledgerly"]                   # nothing else lost
    assert db.execute("SELECT COUNT(*) FROM messages").fetchone()[0] == 3
    last = db.execute("SELECT event FROM audit_log ORDER BY rowid DESC LIMIT 1").fetchone()[0]
    assert last == "deleted a value at the user's request"