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

The tables (SQLite, standing in for a production database plus a vector store):
    messages(id, user, session, date, role, content)
    search_index(message_id, text, vector)          -- keeps the text next to the vector
    summaries(user, text, updated)
    facts(user, key, value, details, valid_from, valid_to)   -- valid_to IS NULL: true now
    audit_log(at, user, event)
    response_cache(prompt_hash, user, prompt, reply)

Fill in the TODOs, then run the tests:
    pytest test_store_practice.py        (locally)
or press "Run tests" on the chapter page.
"""
import sqlite3

USER = "alex"
LIST_KEYS = {"dog_name"}  # keys that can hold several values at once


def facts_now(db: sqlite3.Connection, key: str) -> list[str]:
    """Alex's values for this key that are true now (open rows), oldest first."""
    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]:
    """The values that were true on `date` (an ISO date string): valid_from <= date, and either
    still open or valid_to after date. Oldest first.

    TODO 1
    """
    return []


def add_value(db: sqlite3.Connection, key: str, value: str, date: str) -> None:
    """Save a new value learned on `date`.

    - Already true now: do nothing.
    - Not a LIST_KEY: close the open row(s) first (set valid_to = date). Don't delete them.
    - Then insert an open row (details "", valid_to NULL), and log
      "fact saved: <key> = <value>" in audit_log with at = date.

    TODO 2
    """


def scan(db: sqlite3.Connection, value: str) -> dict[str, int]:
    """For EVERY table in the database, how many rows contain `value` in any TEXT column.

    Read the table names from the database itself (sqlite_master, type = 'table'), so a table
    added later is scanned too, and the column types from PRAGMA table_info(<table>): each row
    is (cid, name, type, ...). Use LIKE '%value%'. A table with no TEXT columns scores 0.

    TODO 3
    """
    return {}


def forget_everywhere(db: sqlite3.Connection, value: str, today: str) -> None:
    """Delete the exact stored value from every store the app owns.

    - messages, search_index, response_cache: delete the rows that contain it
    - facts: delete every row with it in value or details, open OR closed
    - summaries: a summary can't lose one fact cleanly: replace the value with
      '[removed at user request]' (a real app would rebuild it)
    - audit_log: replace the value with '[removed]', then add a row
      (today, USER, "deleted a value at the user's request"), without the value

    TODO 4
    """
    db.execute("DELETE FROM messages WHERE user = ? AND content LIKE ?", (USER, f"%{value}%"))