from pathlib import Path
p=Path('/opt/struktur/social-media-agent/publish_ready.py'); s=p.read_text()
needle='''    con.executescript("""
    CREATE TABLE IF NOT EXISTS media_catalog ('''
insert='''    con.executescript("""
    CREATE TABLE IF NOT EXISTS package_image_variants (
        variant_id TEXT PRIMARY KEY,
        package_id TEXT NOT NULL REFERENCES publication_packages(package_id),
        generation_group_id TEXT NOT NULL,
        variant_order INTEGER NOT NULL,
        image_path TEXT NOT NULL,
        origin_path TEXT NOT NULL DEFAULT '',
        file_type TEXT NOT NULL DEFAULT 'PNG',
        file_bytes INTEGER NOT NULL DEFAULT 0,
        sha256 TEXT NOT NULL DEFAULT '',
        generator TEXT NOT NULL DEFAULT 'Codex built-in image_gen',
        prompt TEXT NOT NULL DEFAULT '',
        prompt_version TEXT NOT NULL DEFAULT 'aa014-korr10-v1',
        qa_status TEXT NOT NULL DEFAULT 'not_checked',
        qa_report_json TEXT NOT NULL DEFAULT '{}',
        qa_failure_reasons TEXT NOT NULL DEFAULT '',
        selected_by_human INTEGER NOT NULL DEFAULT 0,
        selected_at TEXT NOT NULL DEFAULT '',
        selected_by TEXT NOT NULL DEFAULT '',
        final_for_package INTEGER NOT NULL DEFAULT 0,
        human_override INTEGER NOT NULL DEFAULT 0,
        override_reason TEXT NOT NULL DEFAULT '',
        created_at TEXT NOT NULL
    );
    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(needle)==1; s=s.replace(needle,insert)
needle='''def generate_package_image(package_id: str, *, force: bool = False) -> dict:'''
helper='''def persist_image_variant(con: sqlite3.Connection, package_id: str, group_id: str, order_no: int, generated: dict, prompt: str, qa: dict) -> str:
    variant_id = f"VAR-{uuid.uuid4()}"
    con.execute("INSERT INTO package_image_variants(variant_id,package_id,generation_group_id,variant_order,image_path,origin_path,file_type,file_bytes,sha256,generator,prompt,qa_status,qa_report_json,qa_failure_reasons,created_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", (variant_id, package_id, group_id, order_no, generated.get("path", ""), generated.get("origin_path", ""), generated.get("file_type", "PNG"), int(generated.get("bytes") or 0), generated.get("sha256", ""), "Codex built-in image_gen", prompt, qa.get("overall", "FAIL"), json.dumps(qa, ensure_ascii=False), "; ".join(qa.get("failure_reasons", [])), _now()))
    return variant_id


def select_package_variant(package_id: str, variant_id: str, selected_by: str = "human", override_reason: str = "") -> dict:
    con = sqlite3.connect(CASES_DB); con.row_factory = sqlite3.Row
    row = con.execute("SELECT * FROM package_image_variants WHERE variant_id=? AND package_id=?", (variant_id, package_id)).fetchone()
    if not row:
        con.close(); raise ValueError("image variant not found")
    now = _now(); qa_status = row["qa_status"]; failed = qa_status != "passed"
    con.execute("UPDATE package_image_variants SET selected_by_human=0,final_for_package=0 WHERE package_id=?", (package_id,))
    con.execute("UPDATE package_image_variants SET selected_by_human=1,selected_at=?,selected_by=?,final_for_package=1,human_override=?,override_reason=? WHERE variant_id=?", (now, selected_by, int(failed), override_reason, variant_id))
    con.execute("UPDATE publication_packages SET image_status='present',image_path=?,image_generator=?,image_version=image_version,qa_status=?,qa_report_json=?,qa_failure_reasons=?,media_quality=?,human_visual_approval=?,updated_at=? WHERE package_id=?", (row["image_path"], row["generator"], qa_status, row["qa_report_json"], row["qa_failure_reasons"], "technisch abgelehnt — menschlich ausgewählt" if failed else "technisch geprüft", "menschlich ausgewählt", now, package_id))
    con.commit(); con.close()
    return {"package_id": package_id, "variant_id": variant_id, "qa_status": qa_status, "human_override": failed}


def generate_package_image(package_id: str, *, force: bool = False) -> dict:'''
assert s.count(needle)==1; s=s.replace(needle,helper)
# Add group and persist after QA call
old='''    feedback: list[str] = []
    attempt_reports: list[dict] = []'''
new='''    feedback: list[str] = []
    attempt_reports: list[dict] = []
    generation_group_id = f"GRP-{uuid.uuid4()}"'''
assert s.count(old)==1; s=s.replace(old,new)
old='''            attempt_reports.append(attempt_record)
            if qa_passes(qa):'''
new='''            attempt_reports.append(attempt_record)
            persist_image_variant(con, package_id, generation_group_id, attempt, generated, prompt, qa)
            con.commit()
            if qa_passes(qa):'''
assert s.count(old)==1; s=s.replace(old,new)
# Include variants in get_packages before media status
old='''        item["media_status"] = media_status_label(item)'''
new='''        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]
        item["media_status"] = media_status_label(item)'''
assert s.count(old)==1; s=s.replace(old,new)
p.write_text(s); print('variant model integrated')