#!/home/agent/venv/bin/python3
"""Create the Plappi marketing DB and seed the target list.

Idempotent: re-running creates tables IF NOT EXISTS and only inserts a target
when no row with the same (channel, name) already exists. Safe to re-run.
"""
import sqlite3
from pathlib import Path

DB = Path("/home/agent/plappi-marketing/data/marketing.sqlite")
DB.parent.mkdir(parents=True, exist_ok=True)

SCHEMA = """
CREATE TABLE IF NOT EXISTS targets (
  id INTEGER PRIMARY KEY,
  channel TEXT NOT NULL,            -- 'press' | 'social'
  platform TEXT NOT NULL,           -- email,podcast,reddit,facebook_group,instagram,tiktok,telegram,forum,linkedin,quora
  name TEXT NOT NULL,
  contact TEXT,                     -- email / URL / handle
  url TEXT,
  wave TEXT,                        -- press: A,A2,A3,A4,B,C  · social: tier1,tier2
  angle TEXT,                       -- A/B/C pitch angle or topic note
  posting_mode TEXT NOT NULL,       -- email | auto_own | manual_community | paid
  rules TEXT,                       -- compliance reminder shown before posting
  language TEXT,                    -- de | en
  priority INTEGER DEFAULT 3,
  status TEXT DEFAULT 'active',     -- active | contacted | replied | done | skip
  notes TEXT,
  created_at TEXT DEFAULT (datetime('now')),
  updated_at TEXT DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS items (
  id INTEGER PRIMARY KEY,
  target_id INTEGER,
  channel TEXT NOT NULL,
  type TEXT NOT NULL,               -- press_email | press_followup | social_post | social_comment
  platform TEXT,
  subject TEXT,
  body TEXT NOT NULL,
  disclosure TEXT,                  -- 'Werbung' / NULL
  status TEXT DEFAULT 'pending',    -- pending|approved|sent|posted|rejected|superseded|replied|needs_followup
  scheduled_for TEXT,
  thread_ref TEXT,
  fix_iterations INTEGER DEFAULT 0,
  prompt_used TEXT,
  model_used TEXT,
  created_at TEXT DEFAULT (datetime('now')),
  updated_at TEXT DEFAULT (datetime('now')),
  approved_at TEXT,
  sent_at TEXT,
  FOREIGN KEY (target_id) REFERENCES targets(id)
);
CREATE TABLE IF NOT EXISTS events (
  id INTEGER PRIMARY KEY,
  item_id INTEGER,
  event TEXT,
  detail TEXT,
  ts TEXT DEFAULT (datetime('now'))
);
"""

# (channel, platform, name, contact, url, wave, angle, posting_mode, rules, language, priority)
TARGETS = [
    # ── PRESS · WAVE A — Startup-Medien DACH/EU ─────────────────────────────
    ("press","email","Der Brutkasten","redaktion@derbrutkasten.com","brutkasten.com","A","B","email","",  "de",5),
    ("press","email","deutsche-startups.de","ahuesing@deutsche-startups.de","deutsche-startups.de","A","B","email","Hüsing antwortet persönlich — kurz halten","de",5),
    ("press","email","Gründerszene / BI DE","redaktion@gruenderszene.de","businessinsider.de/gruenderszene","A","B","email","","de",5),
    ("press","email","t3n","tipp@t3n.de","t3n.de","A","A","email","coverte Tonies","de",4),
    ("press","email","EU-Startups","antonio@eu-startups.com","eu-startups.com","A","A","email","","en",4),
    ("press","email","Startupticker.ch","news@startupticker.ch","startupticker.ch","A","B","email","","de",4),
    ("press","email","Startnext Presse","presse@startnext.com","startnext.com","A","B","email","","de",4),
    ("press","email","Trending Topics","",  "trendingtopics.eu","A","B","email","Kontakt via Site-Formular (kein Mail bestätigt)","de",4),
    ("press","email","Futurezone.at","",  "futurezone.at/start-ups","A","B","email","Start-ups-Desk; Kontakt via Site","de",4),
    ("press","email","tech.eu","",  "tech.eu/startup-form","A","A","email","Pitch via Startup-Formular","en",4),
    # ── PRESS · WAVE A3 — Eltern / Bilingual / Spielzeug ────────────────────
    ("press","email","Eltern.de","redaktion@eltern.de","eltern.de","A3","C","email","","de",5),
    ("press","email","bilingual-erziehen.de","info@bilingual-erziehen.de","bilingual-erziehen.de","A3","C","email","Review/Gastbeitrag, Testgerät anbieten","de",5),
    ("press","email","Linguamulti (Z. Ortega)","z.ortega@linguamulti.at","linguamulti.at","A3","C","email","Testimonial + Tür zu Der Standard","de",5),
    ("press","email","german-toys.com","info@german-toys.com","german-toys.com","A3","C","email","Angle: Plappi vs. Toniebox","de",5),
    ("press","email","fratz&co","redaktion@timepub.at","fratz.at","A3","C","email","","de",4),
    ("press","email","babelli.de","info@babelli.de","babelli.de","A3","C","email","","de",4),
    ("press","email","Sprachheld.de","",  "sprachheld.de","A3","C","email","Gastbeitrag-Formular, 2-4 Themen vorschlagen","de",4),
    ("press","email","Spielzeugwelten.de","info@spielzeugwelten.de","spielzeugwelten.de","A3","C","email","Testmuster anbieten","de",4),
    # ── PRESS · WAVE A4 — Podcasts ──────────────────────────────────────────
    ("press","podcast","Die Linguistin (B. Gruber)","info@dielinguistin.at","dielinguistin.at","A4","C","email","Gründer als Gast","de",4),
    ("press","podcast","BOLD & BILINGUAL (S. Edelhoff)","sabine@belingualathome.com","belingualathome.com","A4","C","email","Gründer als Gast","de",4),
    # ── PRESS · WAVE B — Internationale Reviewer (TESTGERÄT NÖTIG) ───────────
    ("press","email","Engadget (K. Naudus)","",  "engadget.com","B","C","email","Yoto-Hands-on; Testgerät nötig","en",3),
    ("press","email","Wirecutter/NYT (A. Rochford)","",  "nytimes.com/wirecutter","B","C","email","Yoto-Test; Testgerät nötig","en",3),
    ("press","email","The Toy Book — Talkin' Toys (J. Zahn)","",  "toybook.com","B","C","email","coverte TIMIO+Storypod; @TheRockFather","en",4),
    ("press","email","The Gadgeteer","",  "the-gadgeteer.com","B","C","email","Kids-Audio-Vergleiche; Testgerät","en",3),

    # ── SOCIAL · TIER-1 communities (MANUELL posten) ────────────────────────
    ("social","reddit","r/multilingualparenting","reddit.com/r/multilingualparenting","reddit.com/r/multilingualparenting","tier1","C","manual_community","90/10-Regel, value-first, 'I'm the founder' offenlegen, nur in Promo-Threads werben","en",5),
    ("social","reddit","r/germany","reddit.com/r/germany","reddit.com/r/germany","tier1","A","manual_community","Toniebox-Threads laufen dort; value-first, kein Linkdrop","en",4),
    ("social","reddit","r/austria","reddit.com/r/austria","reddit.com/r/austria","tier1","B","manual_community","value-first, AT-Startup-Angle, kein Spam","de",4),
    ("social","reddit","r/Eltern","reddit.com/r/Eltern","reddit.com/r/Eltern","tier1","C","manual_community","klein aber exakt; value-first","de",3),
    ("social","facebook_group","Raising Bilingual/Multilingual Children","facebook.com/groups/RaisingBilingualMultilingualChildren","","tier1","C","manual_community","Gruppenregeln lesen, Admin-OK/Promo-Tag für Werbung","en",5),
    ("social","facebook_group","Bilingual Kidspot (Little Global Citizens)","facebook.com/groups/bilingualkidspot","","tier1","C","manual_community","Gruppenregeln, value-first","en",5),
    ("social","facebook_group","Non-native Speakers Raising Bilingual Children","facebook.com/groups/277780785899414","","tier1","C","manual_community","exakter Fit; Admin-Erlaubnis für Werbung","en",4),
    ("social","facebook_group","Toniebox Community","facebook.com/groups/2424166074431755","","tier1","A","manual_community","warm für screen-free Audio; vorsichtig, kein harter Pitch","en",4),
    ("social","forum","urbia.de — Mehrsprachig aufwachsen","urbia.de/forum/21-erziehung","","tier1","C","manual_community","DE-Forum; org. Teilnahme + Offenlegung, Werbung sonst nur bezahlt","de",4),
    ("social","forum","rund-ums-baby.de — /mehrsprachig/","rund-ums-baby.de/eltern-forum/mehrsprachig","","tier1","C","manual_community","dediziertes Subforum; value-first + Offenlegung","de",4),

    # ── SOCIAL · own channels (auto_own) ────────────────────────────────────
    ("social","instagram","Plappi Instagram","@helloplappi","instagram.com/helloplappi","tier1","C","auto_own","eigener Kanal; 'Werbung' kennzeichnen","de",4),
    ("social","tiktok","Plappi TikTok","@helloplappi","tiktok.com/@helloplappi","tier1","C","auto_own","eigener Kanal; Disclosure-Toggle + 'Werbung' Caption","de",3),
    ("social","linkedin","Founder LinkedIn (Plappi-Story)","","linkedin.com","tier1","B","auto_own","kann über bestehende LinkedIn-Pipeline laufen","de",3),
]


def main():
    conn = sqlite3.connect(DB)
    conn.executescript(SCHEMA)
    ins = 0
    for t in TARGETS:
        channel, name = t[0], t[2]
        exists = conn.execute("SELECT 1 FROM targets WHERE channel=? AND name=?",
                              (channel, name)).fetchone()
        if exists:
            continue
        conn.execute(
            """INSERT INTO targets (channel,platform,name,contact,url,wave,angle,
                                    posting_mode,rules,language,priority)
               VALUES (?,?,?,?,?,?,?,?,?,?,?)""", t)
        ins += 1
    conn.commit()
    n_press = conn.execute("SELECT COUNT(*) FROM targets WHERE channel='press'").fetchone()[0]
    n_soc = conn.execute("SELECT COUNT(*) FROM targets WHERE channel='social'").fetchone()[0]
    conn.close()
    print(f"DB ok: {DB}")
    print(f"  inserted {ins} new targets · press={n_press} social={n_soc}")


if __name__ == "__main__":
    main()