"""
Practice: the safety and grading code around a text-to-SQL model (Lab 04, 06_text_to_sql.py).

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

FORBIDDEN = re.compile(r"\b(insert|update|delete|drop|alter|create|replace|attach|pragma|vacuum)\b", re.I)
CHECKED = ("status", "category", "name", "country")


def is_safe(sql: str) -> tuple[bool, str]:
    """Gate 1: allow only a single SELECT (or WITH … SELECT). Return (ok, reason), checking in order:
      - a ";" anywhere except at the very end   → (False, "more than one statement")
      - doesn't start with SELECT or WITH        → (False, "not a SELECT query")
      - contains a FORBIDDEN keyword             → (False, "forbidden keyword: <the word as written>")
      - otherwise                                → (True, "ok")

    TODO 1
    """
    return True, "ok"


def normalize(value) -> str:
    return f"{value:.2f}" if isinstance(value, float) else str(value)


def same_result(gold: list[tuple], predicted: list[tuple]) -> bool:
    """Execution accuracy, like the lab: the same number of rows, and every gold row's values
    (normalized) appear in a different predicted row. Extra columns in a predicted row are fine.

    TODO 2
    """
    return gold == predicted


def unknown_values(db: sqlite3.Connection, sql: str) -> list[str]:
    """A cheap check before trusting a written query: string values it compares against a
    status, category, name or country column must exist in the data.

    Find comparisons like  status = 'paid'  or  o.status IN ('shipped', 'paid')  for those four
    column names (ignore case and a table prefix), and return the values that don't appear in any
    table's column of that name, in the order they appear in the query.

    TODO 3
    """
    return []


def run_read_only(path: str, sql: str) -> list[tuple]:
    """Gate 2: run the query on a connection SQLite itself opens read-only
    (sqlite3.connect(f"file:{path}?mode=ro", uri=True)), and return all rows.

    TODO 4
    """
    db = sqlite3.connect(path)
    try:
        return db.execute(sql).fetchall()
    finally:
        db.commit()
        db.close()