#!/home/agent/venv/bin/python3
"""Import expanded press outlets from a pipe-separated file into the marketing DB.
Idempotent: skips outlets whose name or contact email already exists.
Usage: import_press.py [press-expansion.psv]
"""
import sqlite3, sys

DB = "/home/agent/plappi-marketing/data/marketing.sqlite"
PSV = sys.argv[1] if len(sys.argv) > 1 else "/home/agent/plappi-marketing/data/press-expansion.psv"


def wave_for(cat: str) -> str:
    c = cat.lower()
    if "podcast" in c:
        return "A4"
    if any(k in c for k in ("startup", "tech", "founder", "business", "wire", "daily", "innovation")):
        return "A"
    return "A3"  # parenting/family/toy/kita/montessori/waldorf/blog/edtech/lifestyle


def main():
    conn = sqlite3.connect(DB)
    existing_names = {r[0].lower() for r in conn.execute("SELECT name FROM targets")}
    existing_mail = {(r[0] or "").lower() for r in conn.execute(
        "SELECT contact FROM targets WHERE channel='press'") if r[0] and "@" in r[0]}
    ins = skip = 0
    for line in open(PSV, encoding="utf-8"):
        line = line.strip()
        if not line or line.lower().startswith("name |"):
            continue
        parts = [p.strip() for p in line.split("|")]
        if len(parts) < 6:
            continue
        name, contact, cat, geo, angle, lang = parts[:6]
        is_mail = "@" in contact
        if name.lower() in existing_names or (is_mail and contact.lower() in existing_mail):
            skip += 1
            continue
        wave = wave_for(cat)
        plat = "podcast" if wave == "A4" else "email"
        rules = "" if is_mail else f"Kontakt via Formular: {contact}"
        conn.execute(
            """INSERT INTO targets (channel,platform,name,contact,url,wave,angle,
                                    posting_mode,rules,language,priority)
               VALUES ('press',?,?,?,?,?,?, 'email', ?, ?, 3)""",
            (plat, name, contact, (contact if not is_mail else ""), wave, angle, rules, lang))
        existing_names.add(name.lower())
        if is_mail:
            existing_mail.add(contact.lower())
        ins += 1
    conn.commit()
    n = conn.execute("SELECT COUNT(*) FROM targets WHERE channel='press'").fetchone()[0]
    print(f"press import: +{ins} new, {skip} skipped · press total = {n}")
    conn.close()


if __name__ == "__main__":
    main()