"""Stdlib re-implementation of CS146S 2025 week4 search logic, to compare weak vs strong assertions.
Not the course code: SQL mirrors SQLAlchemy `col.contains(q)` -> `col LIKE '%' || ? || '%'`."""
import sqlite3

LIKE = "LIKE '%' || ? || '%'"
IMPLS = {
    "course": lambda c, q: c.execute(f"SELECT id FROM notes WHERE title {LIKE} OR content {LIKE}", (q, q)) if q else c.execute("SELECT id FROM notes"),
    "ignore_q": lambda c, q: c.execute("SELECT id FROM notes"),
    "case_sensitive": lambda c, q: c.execute("SELECT id FROM notes WHERE instr(title, ?) > 0 OR instr(content, ?) > 0", (q, q)) if q else c.execute("SELECT id FROM notes"),
    "title_only": lambda c, q: c.execute(f"SELECT id FROM notes WHERE title {LIKE}", (q,)) if q else c.execute("SELECT id FROM notes"),
}

def db(rows):
    c = sqlite3.connect(":memory:")
    c.execute("CREATE TABLE notes (id INTEGER PRIMARY KEY, title TEXT, content TEXT)")
    c.executemany("INSERT INTO notes (title, content) VALUES (?, ?)", rows)
    return c

def ids(impl, c, q):
    return sorted(r[0] for r in IMPLS[impl](c, q).fetchall())

def weak(impl):
    c = db([("Test", "Hello world")])          # test_notes.py L2-3
    return len(ids(impl, c, "Hello")) >= 1      # test_notes.py L16-19

def strong(impl):
    c = db([("Test", "Hello world"), ("Groceries", "milk and eggs"), ("HELLO title", "caps in title")])
    checks = {
        "hello -> {1,3}": ids(impl, c, "hello") == [1, 3],
        "zzz -> []": ids(impl, c, "zzz") == [],
    }
    return all(checks.values()), checks

for impl in IMPLS:
    ok, checks = strong(impl)
    print(f"{impl:15s} weak={'PASS' if weak(impl) else 'FAIL'}  strong={'PASS' if ok else 'FAIL'}  {checks}")

c = sqlite3.connect(":memory:")
print("ASCII  'Hello' LIKE '%hello%' ->", c.execute("SELECT 'Hello' LIKE '%' || 'hello' || '%'").fetchone()[0])
print("nonASCII 'École' LIKE '%école%' ->", c.execute("SELECT 'École' LIKE '%' || 'école' || '%'").fetchone()[0])
c2 = db([("Test", "Hello world"), ("Budget", "100 percent")])
print("q='%' course ->", ids("course", c2, "%"), " q='_' course ->", ids("course", c2, "_"), " q='' course ->", ids("course", c2, ""))
print("sqlite", sqlite3.sqlite_version)