from pathlib import Path
p=Path('/opt/struktur/social-media-agent/publish_ready.py'); s=p.read_text()
# Add media_briefs table before package variants table in init_schema.
needle='''    CREATE TABLE IF NOT EXISTS package_image_variants ('''
brief_sql='''    CREATE TABLE IF NOT EXISTS media_briefs (
        media_brief_id TEXT PRIMARY KEY,
        package_id TEXT NOT NULL REFERENCES publication_packages(package_id),
        generation_group_id TEXT NOT NULL,
        content_pillar TEXT NOT NULL,
        topic TEXT NOT NULL,
        core_claim TEXT NOT NULL,
        main_motif TEXT NOT NULL,
        technical_action TEXT NOT NULL,
        required_objects_json TEXT NOT NULL DEFAULT '[]',
        technical_requirements_json TEXT NOT NULL DEFAULT '[]',
        building_context TEXT NOT NULL DEFAULT '',
        mallorca_context TEXT NOT NULL DEFAULT 'none',
        visual_style TEXT NOT NULL DEFAULT '',
        visual_effect TEXT NOT NULL DEFAULT '',
        avoid_motifs_json TEXT NOT NULL DEFAULT '[]',
        avoid_technical_errors_json TEXT NOT NULL DEFAULT '[]',
        reference_assets_json TEXT NOT NULL DEFAULT '[]',
        feedback_rules_json TEXT NOT NULL DEFAULT '[]',
        generator TEXT NOT NULL DEFAULT 'Codex built-in image_gen',
        created_at TEXT NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_media_briefs_package ON media_briefs(package_id, created_at);
    CREATE TABLE IF NOT EXISTS package_image_variants ('''
assert s.count(needle)==1; s=s.replace(needle,brief_sql)
# Add media_brief_id to variants through ensure columns after variant feedback ensure line.
old='''{"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 '[]'"}'''
new='''{"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 '[]'", "media_brief_id": "TEXT NOT NULL DEFAULT ''"}'''
assert s.count(old)==1; s=s.replace(old,new)
# Insert brief helpers before persist variant.
needle='def persist_image_variant(con: sqlite3.Connection, package_id: str, group_id: str, order_no: int, generated: dict, prompt: str, qa: dict, feedback_rules: list[dict] | None = None) -> str:'
helper='''def build_media_brief(package: dict, feedback_rules: list[dict], reference_asset: dict | None, generation_group_id: str) -> dict:
    pillar = _text(package.get("content_pillar") or package.get("topic_category"))
    topic = _text(package.get("topic"))
    if "feuchte" in pillar or "schimmel" in topic.casefold():
        motif = "professionelles elektronisches Feuchtemessgerät mit klar erkennbarer flacher Sensorfläche an einer Innenwand"
        action = "Fachperson hält das kompakte Messgerät unmittelbar und plausibel flach an die Wandoberfläche"
        objects = ["Fachperson", "kompaktes professionelles Feuchtemessgerät", "Innenwand", "sichtbare Feuchtespur nur falls zum Thema passend"]
        requirements = ["Sensorfläche muss zum Gerät gehören", "keine externe Sonde", "keine Kabel", "keine Bohrmaschine oder Schleifgerät"]
    elif "lüft" in pillar or "lüft" in topic.casefold():
        motif = "reales dezentrales Lüftungsgerät mit klarer Innenblende und nachvollziehbarer Wanddurchführung"
        action = "Fachperson prüft die sichtbare technische Montage und den Luftweg ohne erfundene Messanordnung"
        objects = ["Fachperson", "dezentrales Lüftungsgerät", "Innenblende", "Wanddurchführung"]
        requirements = ["Bauteile geometrisch verbunden", "keine unklaren Sonden", "keine Leitungen ohne Anfang und Ende"]
    else:
        motif = "professionelles Raumluftmessgerät beziehungsweise Sensor im realistischen Innenraum"
        action = "Fachperson kontrolliert Raumluftdaten mit einem eindeutig passenden Messgerät"
        objects = ["Fachperson", "professionelles Raumluftmessgerät", "Innenraum"]
        requirements = ["Gerät und Messaufgabe funktional zusammengehörig", "keine Fantasiewerte", "keine unklaren Kabel"]
    avoid_motifs = ["Strand", "Meer", "Küste", "Sehenswürdigkeiten", "touristische Straße", "Postkartenästhetik", "Luxusvilla ohne Fachbezug", "primitive Grafik", "Comic", "Text im Bild", "Logo", "Wasserzeichen"]
    avoid_errors = ["Fantasie-Messgerät", "Kabel aus Wand oder Boden", "unklare Sonde", "widersprüchliches Messsystem", "zusätzliche funktionslose Instrumente", "unplausible Anatomie"]
    ref = [reference_asset] if reference_asset else []
    return {"media_brief_id": f"MB-{uuid.uuid4()}", "package_id": package.get("package_id", ""), "generation_group_id": generation_group_id, "content_pillar": pillar, "topic": topic, "core_claim": _text(package.get("final_text") or package.get("facebook_text") or package.get("relevance")), "main_motif": motif, "technical_action": action, "required_objects": objects, "technical_requirements": requirements, "building_context": "realistischer Innenraum einer Wohnimmobilie; technische Gebäudesubstanz widerspruchsfrei", "mallorca_context": "subtle" if "mallorca" in topic.casefold() else "none", "visual_style": "fotorealistische, sachliche dokumentarische Fachfotografie", "visual_effect": "ruhig, glaubwürdig, hochwertig, technische Handlung klar lesbar", "avoid_motifs": avoid_motifs, "avoid_technical_errors": avoid_errors, "reference_assets": ref, "feedback_rules": feedback_rules, "generator": "Codex built-in image_gen", "created_at": _now()}


def persist_media_brief(con: sqlite3.Connection, brief: dict) -> None:
    con.execute("INSERT INTO media_briefs(media_brief_id,package_id,generation_group_id,content_pillar,topic,core_claim,main_motif,technical_action,required_objects_json,technical_requirements_json,building_context,mallorca_context,visual_style,visual_effect,avoid_motifs_json,avoid_technical_errors_json,reference_assets_json,feedback_rules_json,generator,created_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", (brief["media_brief_id"],brief["package_id"],brief["generation_group_id"],brief["content_pillar"],brief["topic"],brief["core_claim"],brief["main_motif"],brief["technical_action"],json.dumps(brief["required_objects"],ensure_ascii=False),json.dumps(brief["technical_requirements"],ensure_ascii=False),brief["building_context"],brief["mallorca_context"],brief["visual_style"],brief["visual_effect"],json.dumps(brief["avoid_motifs"],ensure_ascii=False),json.dumps(brief["avoid_technical_errors"],ensure_ascii=False),json.dumps(brief["reference_assets"],ensure_ascii=False),json.dumps(brief["feedback_rules"],ensure_ascii=False),brief["generator"],brief["created_at"]))


def media_brief_prompt(brief: dict, variant_no: int) -> str:
    variants = {1: "klare technische Hauptaufnahme und unmittelbare Tätigkeit", 2: "etwas mehr räumlicher Innenkontext bei unverändert klarer technischer Handlung", 3: "alternative glaubwürdige Perspektive oder Komposition ohne anderes Messverfahren"}
    return (f"Media Brief {brief['media_brief_id']}. Thema: {brief['topic']}. Fachliche Kernaussage: {brief['core_claim']}. "
            f"Hauptmotiv: {brief['main_motif']}. Technische Handlung: {brief['technical_action']}. "
            f"Erforderliche Objekte: {', '.join(brief['required_objects'])}. Technisch zwingend: {', '.join(brief['technical_requirements'])}. "
            f"Gebäudekontext: {brief['building_context']}. Mallorca-Kontext: {brief['mallorca_context']}. "
            f"Stil/Wirkung: {brief['visual_style']}; {brief['visual_effect']}. "
            f"Diese Variante ist bewusst: {variants[variant_no]}. "
            f"Vermeide: {', '.join(brief['avoid_motifs'] + brief['avoid_technical_errors'])}. "
            "Keine erfundenen Marken, Messwerte, Texte oder Logos; keine zusätzlichen funktionslosen Instrumente; technische Geometrie, Sensoren und Leitungen müssen physikalisch plausibel sein.")


def persist_image_variant(con: sqlite3.Connection, package_id: str, group_id: str, order_no: int, generated: dict, prompt: str, qa: dict, feedback_rules: list[dict] | None = None, media_brief_id: str = "") -> str:'''
assert s.count(needle)==1; s=s.replace(needle,helper)
# Replace persist insertion block
start=s.index('    columns="variant_id,package_id,generation_group_id', s.index('def persist_image_variant'))
end=s.index('\n    return variant_id',start)
newbody='''    columns="variant_id,package_id,generation_group_id,variant_order,image_path,origin_path,file_type,file_bytes,sha256,generator,prompt,feedback_rules_json,media_brief_id,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, json.dumps(feedback_rules or [], ensure_ascii=False), media_brief_id, "passed" if qa.get("overall") == "PASS" else "failed", json.dumps(qa, ensure_ascii=False), "; ".join(qa.get("failure_reasons", [])), _now())
    con.execute(f"INSERT INTO package_image_variants({columns}) VALUES ({','.join('?' for _ in values)})", values)'''
s=s[:start]+newbody+s[end:]
# Replace generation prompt/function block
start=s.index('def generate_package_image('); end=s.index('\n\ndef _direct_url',start)
replacement='''def generate_package_image(package_id: str, *, force: bool = False) -> dict:
    """Generate three brief-driven Codex variants, each independently QA-checked."""
    con = sqlite3.connect(CASES_DB); con.row_factory = sqlite3.Row
    row = con.execute("SELECT * FROM publication_packages WHERE package_id=?", (package_id,)).fetchone()
    if not row:
        con.close(); raise ValueError("publication package not found")
    package = dict(row); generation_group_id = f"GRP-{uuid.uuid4()}"
    feedback_text, feedback_rules = feedback_prompt_text(package)
    reference_asset = None
    try:
        ref_path, ref_label = internal_asset_for_package(package)
        if ref_path and ref_path.is_file(): reference_asset = {"path": str(ref_path), "label": ref_label, "source": "media_catalog"}
    except Exception:
        reference_asset = None
    brief = build_media_brief(package, feedback_rules, reference_asset, generation_group_id)
    persist_media_brief(con, brief); con.commit()
    reports=[]
    for variant_no in range(1,4):
        prompt = media_brief_prompt(brief, variant_no)
        target = (BASE_DIR / "generated_visuals" / f"{package['signal_id']}_codex-image-gen-group-{generation_group_id[-8:]}-v{variant_no}.png").resolve()
        try:
            generated = generate_with_codex(prompt, target)
            qa = run_image_qa(target, _text(package.get("topic")), _text(package.get("final_text") or package.get("facebook_text")))
            persist_image_variant(con, package_id, generation_group_id, variant_no, generated, prompt, qa, feedback_rules, brief["media_brief_id"]); con.commit()
            reports.append({"variant":variant_no,"path":str(target),"origin_path":generated.get("origin_path"),"bytes":generated.get("bytes"),"sha256":generated.get("sha256"),"qa":qa})
        except (CodexImageError, CodexImageQAError, OSError, ValueError) as exc:
            reports.append({"variant":variant_no,"qa":{"overall":"FAIL","failure_reasons":[str(exc)]},"error":str(exc)})
    con.close()
    passed=sum(1 for r in reports if r.get("qa",{}).get("overall")=="PASS")
    return {"ok":len(reports)==3,"status":f"{len(reports)} Varianten erzeugt; {passed} technisch bestanden","generation_group_id":generation_group_id,"media_brief_id":brief["media_brief_id"],"media_brief":brief,"variants":reports,"attempts":reports}
'''
s=s[:start]+replacement+s[end:]
p.write_text(s); print('media brief integrated')