from pathlib import Path
p=Path('/opt/struktur/content-extraction/db.py')
s=p.read_text()
old='''CREATE TABLE IF NOT EXISTS ce_notes (\n    id          INTEGER PRIMARY KEY AUTOINCREMENT,\n    item_id     INTEGER NOT NULL REFERENCES ce_items(id) ON DELETE CASCADE,\n    note_text   TEXT NOT NULL,\n    created_at  TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP\n);\n'''
new=old+'''\nCREATE TABLE IF NOT EXISTS ce_analysis_marks (\n    id              INTEGER PRIMARY KEY AUTOINCREMENT,\n    source_id       INTEGER NOT NULL REFERENCES ce_sources(id) ON DELETE CASCADE,\n    analysis_type   TEXT NOT NULL,\n    status          TEXT NOT NULL CHECK(status IN ('processed','transcript_missing','not_usable')),\n    batch_id        TEXT,\n    analyzed_at     TEXT,\n    output_refs     TEXT,\n    note            TEXT,\n    created_at      TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,\n    updated_at      TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,\n    UNIQUE(source_id, analysis_type)\n);\n'''
if old not in s: raise SystemExit('schema anchor not found')
s=s.replace(old,new,1)
# enrich search rows with customer-project mark
anchor='''        row["visible_tags"] = visible_tags\n        row["tag_keys"] = tag_keys\n        base_scored.append(row)\n'''
repl='''        row["visible_tags"] = visible_tags\n        row["tag_keys"] = tag_keys\n        mark = analysis_marks.get(row["id"])\n        row["customer_project_analysis"] = mark\n        base_scored.append(row)\n'''
if anchor not in s: raise SystemExit('row anchor not found')
s=s.replace(anchor,repl,1)
# change function signature and load marks
sig="def search_videos(query, tag=None, limit=50, sort='score'):"
s=s.replace(sig,"def search_videos(query, tag=None, limit=50, sort='score', exclude_customer_projects=False):",1)
load='''    conn = get_db()\n    try:\n        rows = conn.execute("SELECT * FROM ce_sources").fetchall()\n    finally:\n        conn.close()\n\n    base_scored = []\n'''
load2='''    conn = get_db()\n    try:\n        rows = conn.execute("SELECT * FROM ce_sources").fetchall()\n        analysis_marks = {r["source_id"]: dict(r) for r in conn.execute("SELECT * FROM ce_analysis_marks WHERE analysis_type = 'customer_projects'").fetchall()}\n    finally:\n        conn.close()\n\n    base_scored = []\n'''
if load not in s: raise SystemExit('load anchor not found')
s=s.replace(load,load2,1)
# exclude only successfully processed; missing transcript remains visible/retryable
flt='''    if active_rule:\n        filtered = [row for row in base_scored if active_rule["key"] in row.get("tag_keys", [])]\n'''
flt2='''    if exclude_customer_projects:\n        base_scored = [row for row in base_scored if not (row.get("customer_project_analysis") and row["customer_project_analysis"].get("status") == "processed")]\n\n    if active_rule:\n        filtered = [row for row in base_scored if active_rule["key"] in row.get("tag_keys", [])]\n'''
if flt not in s: raise SystemExit('filter anchor not found')
s=s.replace(flt,flt2,1)
p.write_text(s)
print('DB_PY_PATCHED')