from pathlib import Path
p=Path('/opt/struktur/social-media-agent/publish_ready.py'); s=p.read_text()
bad='''    CREATE INDEX IF NOT EXISTS idx_package_image_variants_package ON package_image_variants(package_id, variant_order);
    for column, definition in {"human_rating": "TEXT NOT NULL DEFAULT ''", "human_reason_verbatim": "TEXT NOT NULL DEFAULT ''", "human_reason_structured_json": "TEXT NOT NULL DEFAULT '{}'", "human_feedback_at": "TEXT NOT NULL DEFAULT ''", "human_feedback_by": "TEXT NOT NULL DEFAULT ''", "feedback_rules_json": "TEXT NOT NULL DEFAULT '[]'"}.items():
        _ensure_column(con, "package_image_variants", column, definition)
    CREATE TABLE IF NOT EXISTS media_catalog ('''
good='''    CREATE INDEX IF NOT EXISTS idx_package_image_variants_package ON package_image_variants(package_id, variant_order);
    CREATE TABLE IF NOT EXISTS media_catalog ('''
assert s.count(bad)==1; s=s.replace(bad,good)
# Ensure variant columns after schema script closes, before its next media-column loop.
needle='''    """)
    for column, definition in {
        "space_type":'''
repl='''    """)
    for column, definition in {"human_rating": "TEXT NOT NULL DEFAULT ''", "human_reason_verbatim": "TEXT NOT NULL DEFAULT ''", "human_reason_structured_json": "TEXT NOT NULL DEFAULT '{}'", "human_feedback_at": "TEXT NOT NULL DEFAULT ''", "human_feedback_by": "TEXT NOT NULL DEFAULT ''", "feedback_rules_json": "TEXT NOT NULL DEFAULT '[]'"}.items():
        _ensure_column(con, "package_image_variants", column, definition)
    for column, definition in {
        "space_type":'''
assert s.count(needle)==1; s=s.replace(needle,repl)
dup='''        variants = con.execute("SELECT * FROM package_image_variants WHERE package_id=? ORDER BY CASE WHEN final_for_package=1 THEN 0 WHEN qa_status='passed' THEN 1 ELSE 2 END, variant_order", (item["package_id"],)).fetchall()
        item["variants"] = [dict(v) for v in variants]
        variants = con.execute("SELECT * FROM package_image_variants WHERE package_id=? ORDER BY CASE WHEN final_for_package=1 THEN 0 WHEN qa_status='passed' THEN 1 ELSE 2 END, variant_order", (item["package_id"],)).fetchall()
        item["variants"] = [dict(v) for v in variants]'''
single='''        variants = con.execute("SELECT * FROM package_image_variants WHERE package_id=? ORDER BY CASE WHEN final_for_package=1 THEN 0 WHEN qa_status='passed' THEN 1 ELSE 2 END, variant_order", (item["package_id"],)).fetchall()
        item["variants"] = [dict(v) for v in variants]'''
assert s.count(dup)==1; s=s.replace(dup,single)
p.write_text(s); print('fixed partial integration')