"""
Solution: Alex's memory as tables (Lab 06, 09_memory_store.py).
"""
import sqlite3

USER = "alex"
LIST_KEYS = {"dog_name"}


def facts_now(db: sqlite3.Connection, key: str) -> list[str]:
    rows = db.execute("SELECT value FROM facts WHERE user = ? AND key = ? AND valid_to IS NULL ORDER BY valid_from",
                      (USER, key))
    return [r[0] for r in rows]


def fact_on(db: sqlite3.Connection, key: str, date: str) -> list[str]:
    rows = db.execute("SELECT value FROM facts WHERE user = ? AND key = ? AND valid_from <= ? "
                      "AND (valid_to IS NULL OR ? < valid_to) ORDER BY valid_from", (USER, key, date, date))
    return [r[0] for r in rows]


def add_value(db: sqlite3.Connection, key: str, value: str, date: str) -> None:
    if value in facts_now(db, key):
        return
    if key not in LIST_KEYS:
        db.execute("UPDATE facts SET valid_to = ? WHERE user = ? AND key = ? AND valid_to IS NULL", (date, USER, key))
    db.execute("INSERT INTO facts VALUES (?,?,?,?,?,NULL)", (USER, key, value, "", date))
    db.execute("INSERT INTO audit_log VALUES (?,?,?)", (date, USER, f"fact saved: {key} = {value}"))


def scan(db: sqlite3.Connection, value: str) -> dict[str, int]:
    hits = {}
    for (t,) in db.execute("SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY rowid").fetchall():
        cols = [c[1] for c in db.execute(f"PRAGMA table_info({t})") if c[2] == "TEXT"]
        if not cols:
            hits[t] = 0
            continue
        where = " OR ".join(f"{c} LIKE ?" for c in cols)
        hits[t] = db.execute(f"SELECT COUNT(*) FROM {t} WHERE {where}", [f"%{value}%"] * len(cols)).fetchone()[0]
    return hits


def forget_everywhere(db: sqlite3.Connection, value: str, today: str) -> None:
    like = f"%{value}%"
    db.execute("DELETE FROM messages WHERE user = ? AND content LIKE ?", (USER, like))
    db.execute("DELETE FROM search_index WHERE text LIKE ?", (like,))
    db.execute("DELETE FROM facts WHERE user = ? AND (value LIKE ? OR details LIKE ?)", (USER, like, like))
    db.execute("UPDATE summaries SET text = REPLACE(text, ?, '[removed at user request]') WHERE user = ?", (value, USER))
    db.execute("DELETE FROM response_cache WHERE user = ? AND (prompt LIKE ? OR reply LIKE ?)", (USER, like, like))
    db.execute("UPDATE audit_log SET event = REPLACE(event, ?, '[removed]') WHERE user = ?", (value, USER))
    db.execute("INSERT INTO audit_log VALUES (?, ?, 'deleted a value at the user''s request')", (today, USER))