"""Shared helpers for the Plappi Social-Marketing engine.

Single source of truth for: paths, DB access, WA dispatch, Claude drafting
(with OpenAI-Codex fallback, mirrored from the LinkedIn pipeline), e-mail send
(via lena-mail.py), disclosure labels and the WhatsApp approval format.

Two channels, three posting modes (set per target — see init_db.py):
  channel 'press'  → posting_mode 'email'             (Lena drafts → approve → SENDS mail)
  channel 'social' → posting_mode 'auto_own'          (eigene Plappi-Kanäle: draft → approve → post)
                   → posting_mode 'manual_community'   (Reddit/FB-Gruppen/Foren: draft → approve → DU postest)
                   → posting_mode 'paid'               (Ad-Creative-Draft → approve → schalten)

Design rule (see PLATTFORM-RICHTLINIEN.md): NEVER auto-post into third-party
communities — that gets accounts banned. Communities are always human-posted.
"""
from __future__ import annotations

import json
import sqlite3
import subprocess
from datetime import datetime
from pathlib import Path
from typing import Optional

ROOT = Path("/home/agent/plappi-marketing")
DB_PATH = ROOT / "data" / "marketing.sqlite"
LOG_DIR = ROOT / "logs"

# Nemanja's WhatsApp (master phone) — same constant the LinkedIn pipeline uses.
MASTER_PHONE = "4367761591536"
SEND_WA = "/home/agent/bin/send-wa.sh"
LENA_MAIL = "/home/agent/bin/lena-mail.py"
CLAUDE = "/home/nk/.local/bin/claude"
TRACK = "/home/agent/tracking/log_call.py"

APPROVAL_PREFIX = "🔔 Plappi"


# ──────────────────────────────────────────────────────────────────────────
# DB
# ──────────────────────────────────────────────────────────────────────────
def db() -> sqlite3.Connection:
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn


def log(msg: str, stream: str = "engine") -> None:
    LOG_DIR.mkdir(parents=True, exist_ok=True)
    line = f"[{datetime.now().isoformat(timespec='seconds')}] {msg}\n"
    (LOG_DIR / f"{stream}.log").open("a").write(line)


def wa_send(body: str, to: str = MASTER_PHONE) -> None:
    """Queue a WhatsApp message to Nemanja via Lena's send-wa.sh."""
    subprocess.run([SEND_WA, to, body], check=False)


# ── targets ────────────────────────────────────────────────────────────────
def list_targets(channel: Optional[str] = None, platform: Optional[str] = None,
                 wave: Optional[str] = None, status: Optional[str] = None) -> list[sqlite3.Row]:
    q = "SELECT * FROM targets WHERE 1=1"
    p: list = []
    for col, val in (("channel", channel), ("platform", platform),
                     ("wave", wave), ("status", status)):
        if val:
            q += f" AND {col}=?"; p.append(val)
    q += " ORDER BY priority DESC, id ASC"
    conn = db(); rows = conn.execute(q, p).fetchall(); conn.close()
    return rows


def get_target(tid: int) -> Optional[sqlite3.Row]:
    conn = db(); r = conn.execute("SELECT * FROM targets WHERE id=?", (tid,)).fetchone(); conn.close()
    return r


def set_target_status(tid: int, status: str) -> None:
    conn = db()
    conn.execute("UPDATE targets SET status=?, updated_at=datetime('now') WHERE id=?", (status, tid))
    conn.commit(); conn.close()


# ── items (drafts) ──────────────────────────────────────────────────────────
def insert_item(target_id: Optional[int], channel: str, type_: str, platform: str,
                body: str, subject: Optional[str] = None, disclosure: Optional[str] = None,
                scheduled_for: Optional[str] = None, thread_ref: Optional[str] = None,
                prompt_used: Optional[str] = None, model_used: Optional[str] = None) -> int:
    conn = db()
    cur = conn.execute(
        """INSERT INTO items (target_id, channel, type, platform, subject, body,
                              disclosure, scheduled_for, thread_ref, prompt_used,
                              model_used, status)
           VALUES (?,?,?,?,?,?,?,?,?,?,?, 'pending')""",
        (target_id, channel, type_, platform, subject, body, disclosure,
         scheduled_for, thread_ref, prompt_used, model_used))
    conn.commit(); iid = cur.lastrowid; conn.close()
    return iid


def get_item(iid: int) -> Optional[sqlite3.Row]:
    conn = db(); r = conn.execute("SELECT * FROM items WHERE id=?", (iid,)).fetchone(); conn.close()
    return r


def list_items(status: Optional[str] = None, channel: Optional[str] = None) -> list[sqlite3.Row]:
    q = "SELECT * FROM items WHERE 1=1"; p: list = []
    if status:
        q += " AND status=?"; p.append(status)
    if channel:
        q += " AND channel=?"; p.append(channel)
    q += " ORDER BY id DESC LIMIT 40"
    conn = db(); rows = conn.execute(q, p).fetchall(); conn.close()
    return rows


def set_status(iid: int, status: str, **extra) -> None:
    fields = ["status=?", "updated_at=datetime('now')"]; params: list = [status]
    for k, v in extra.items():
        fields.append(f"{k}=?"); params.append(v)
    params.append(iid)
    conn = db(); conn.execute(f"UPDATE items SET {', '.join(fields)} WHERE id=?", params)
    conn.commit(); conn.close()


def log_event(item_id: Optional[int], event: str, detail: str = "") -> None:
    conn = db()
    conn.execute("INSERT INTO events (item_id, event, detail) VALUES (?,?,?)",
                 (item_id, event, detail))
    conn.commit(); conn.close()


# ──────────────────────────────────────────────────────────────────────────
# Disclosure (AT/DE Kennzeichnungspflicht — see PLATTFORM-RICHTLINIEN.md)
# ──────────────────────────────────────────────────────────────────────────
def disclosure_for(posting_mode: str) -> Optional[str]:
    if posting_mode in ("auto_own", "paid", "manual_community"):
        return "Werbung"   # eigenes Produkt bewerben = kennzeichnungspflichtig
    return None            # press pitch (email) is not advertising


# ──────────────────────────────────────────────────────────────────────────
# Claude drafting (mirrors linkedin/draft_post.py: haiku + token-track + codex fallback)
# ──────────────────────────────────────────────────────────────────────────
def run_claude(user_prompt: str, system_prompt: str, model: str = "haiku",
               label: str = "plappi_marketing") -> str:
    cmd = ["sudo", "-n", "-u", "nk", CLAUDE, "-p", user_prompt,
           "--append-system-prompt", system_prompt, "--model", model,
           "--output-format", "json"]
    res = subprocess.run(cmd, capture_output=True, text=True, timeout=180)
    try:
        subprocess.run([TRACK, label, "-p", "plappi", model],
                       input=res.stdout, text=True, timeout=10, capture_output=True)
    except Exception:
        pass
    # happy path: parse JSON .result
    try:
        d = json.loads(res.stdout)
        if isinstance(d, dict) and d.get("result"):
            return d["result"].strip()
    except Exception:
        pass
    # OpenAI-Codex fallback on Claude limit (same pattern as LinkedIn/GAs)
    blob = (res.stderr or "") + (res.stdout or "")
    import re
    limited = re.search(r"usage limit|rate.?limit|5-hour limit|reached your.*limit|"
                        r"quota|exceeded your|limit reached|Overloaded|Not logged in",
                        blob, re.I)
    if res.returncode != 0 or limited:
        try:
            import tempfile, os
            codex = "/home/agent/.nvm/versions/node/v24.15.0/bin/codex"
            combined = system_prompt + "\n\n## AUFGABE:\n" + user_prompt
            outf = tempfile.mktemp(suffix=".txt")
            subprocess.run([codex, "exec", "--skip-git-repo-check", "--cd", "/home/agent",
                            "--dangerously-bypass-approvals-and-sandbox",
                            "--output-last-message", outf, combined],
                           stdin=subprocess.DEVNULL, capture_output=True, text=True, timeout=300)
            out = ""
            try:
                out = open(outf).read().strip(); os.unlink(outf)
            except Exception:
                pass
            if out:
                log("Claude-Limit → OpenAI-Codex-Fallback genutzt")
                return out
        except Exception as e:
            log(f"codex-fallback err: {e}")
        raise RuntimeError(f"claude failed + codex fallback failed: {res.stderr[:300]}")
    raise RuntimeError(f"claude returned no result: {res.stdout[:200]}")


# ──────────────────────────────────────────────────────────────────────────
# E-mail send (press funnel) — wraps lena-mail.py, BCCs Nemanja for transparency
# ──────────────────────────────────────────────────────────────────────────
def send_email(to: str, subject: str, body: str, attachments: Optional[list[str]] = None,
               dry: bool = False, no_bcc: bool = False) -> bool:
    cmd = [LENA_MAIL, "--to", to, "--subject", subject, "--body", body]
    for a in (attachments or []):
        cmd += ["--attach", a]
    if no_bcc:
        cmd.append("--no-bcc")
    if dry:
        cmd.append("--dry")
    r = subprocess.run(cmd, capture_output=True, text=True, timeout=90)
    log(f"send_email to={to} dry={dry} rc={r.returncode} :: {r.stdout.strip()[:120]}")
    return r.returncode == 0


# ──────────────────────────────────────────────────────────────────────────
# WhatsApp approval cards
# ──────────────────────────────────────────────────────────────────────────
def format_for_wa(item: sqlite3.Row, target: Optional[sqlite3.Row]) -> str:
    iid = item["id"]
    name = target["name"] if target else "—"
    sched = item["scheduled_for"] or "sofort"
    mode = (target["posting_mode"] if target else "email")
    bar = "━━━━━━━━━━━━━━━━━━━━"

    if item["channel"] == "press":
        return (f"{APPROVAL_PREFIX}-Presse #{iid} → {name} ({item['platform']}, {sched})\n"
                f"An: {target['contact'] if target else '—'}\n"
                f"Betreff: {item['subject'] or '—'}\n{bar}\n{item['body']}\n{bar}\n\n"
                f"ok #{iid} → Mail senden  ·  nein #{iid}  ·  fix #{iid}: <änderung>")

    # social
    if mode == "manual_community":
        return (f"{APPROVAL_PREFIX}-Community #{iid} → {name} ({item['platform']})\n"
                f"⚠️ MANUELL posten (Account-Schutz).\n"
                f"Regel: {target['rules'] if target else '—'}\n"
                f"Kennzeichnung: {item['disclosure'] or '—'}\n{bar}\n{item['body']}\n{bar}\n\n"
                f"ok #{iid} → freigeben (ich geb dir den fertigen Text)  ·  nein #{iid}  ·  "
                f"fix #{iid}: <änderung>\nWenn gepostet: posted #{iid}")

    # auto_own / paid
    tag = f"[{item['disclosure']}] " if item["disclosure"] else ""
    return (f"{APPROVAL_PREFIX}-Post #{iid} → {name} ({item['platform']}, eigener Kanal)\n"
            f"{tag}{sched}\n{bar}\n{item['body']}\n{bar}\n\n"
            f"ok #{iid} → posten  ·  nein #{iid}  ·  fix #{iid}: <änderung>")


def manual_post_card(item: sqlite3.Row, target: sqlite3.Row) -> str:
    """Ready-to-paste block Lena returns after a community draft is approved."""
    bar = "━━━━━━━━━━━━━━━━━━━━"
    disc = f"\n\n— {item['disclosure']} —" if item["disclosure"] else ""
    return (f"✅ Freigegeben #{item['id']} — bitte HIER posten:\n"
            f"Ziel: {target['name']}\n"
            f"Link/Handle: {target['contact'] or target['url'] or '—'}\n"
            f"Regel beachten: {target['rules'] or '—'}\n{bar}\n{item['body']}{disc}\n{bar}\n"
            f"Danach: posted #{item['id']}")