#!/home/agent/venv/bin/python3
"""Import a curated set of top social targets into the marketing DB.
The FULL listings (200+/platform) live as docs in marketing/listings/; this
seeds a workable, high-priority subset so the engine is usable without 600 rows.
Idempotent: skips by existing name. All community targets = manual_community
(human-posted, ban-safe). Instagram creators = engagement/collab targets.
"""
import sqlite3

DB = "/home/agent/plappi-marketing/data/marketing.sqlite"

# platform, name, contact/url, wave, angle, rules, lang, priority
S = [
    # Reddit (value-first, 90/10, Founder offenlegen)
    ("reddit", "r/bilingualparenting", "reddit.com/r/bilingualparenting", "tier1", "C", "90/10, value-first, Founder offenlegen", "en", 5),
    ("reddit", "r/toddlers", "reddit.com/r/toddlers", "tier1", "C", "value-first; 2-4 J. Kernzielgruppe", "en", 4),
    ("reddit", "r/Montessori", "reddit.com/r/Montessori", "tier1", "C", "Philosophie-Alignment, kein Pitch", "en", 4),
    ("reddit", "r/nosurf", "reddit.com/r/nosurf", "tier1", "A", "screen-free Movement; ehrlich", "en", 4),
    ("reddit", "r/screenfreeparenting", "reddit.com/r/screenfreeparenting", "tier1", "A", "Bullseye; value-first", "en", 4),
    ("reddit", "r/homeschool", "reddit.com/r/homeschool", "tier1", "C", "threads-only; Self-Promo-Thread nutzen", "en", 3),
    ("reddit", "r/de", "reddit.com/r/de", "tier1", "B", "DE; kein Hardsell, organisch", "de", 4),
    ("reddit", "r/wien", "reddit.com/r/wien", "tier1", "B", "lokal AT; value-first", "de", 3),
    ("reddit", "r/Serbian", "reddit.com/r/Serbian", "tier1", "C", "Gründer-Sprach-Story; Diaspora", "en", 3),
    ("reddit", "r/languagelearning", "reddit.com/r/languagelearning", "tier1", "C", "threads-only; 'teaching my kid'-Angle", "en", 3),
    ("reddit", "r/Kickstarter", "reddit.com/r/Kickstarter", "tier1", "B", "Kampagnen-Links erlaubt", "en", 4),
    ("reddit", "r/digitalminimalism", "reddit.com/r/digitalminimalism", "tier1", "A", "value-first", "en", 3),
    ("reddit", "r/Autism_Parenting", "reddit.com/r/Autism_Parenting", "tier1", "C", "sensibel; Sprach-Tool-Angle", "en", 3),
    # Facebook groups (Gruppenregeln/Admin-OK; manuell)
    ("facebook_group", "The Screen Free Parenting Community", "facebook.com/groups/127311545931274", "tier1", "A", "Toniebox/Yoto-Talk; value-first", "en", 5),
    ("facebook_group", "Montessori at Home", "facebook.com/groups/montessoriactivities", "tier1", "C", "200k+, Admin-OK für Werbung", "en", 4),
    ("facebook_group", "Toniebox and Tonies Deals and New Releases", "facebook.com/groups/504894651275407", "tier1", "A", "warm; vorsichtig, kein Hardsell", "en", 4),
    ("facebook_group", "International Families Berlin", "facebook.com/groups/InternationalFamiliesBerlin", "tier1", "C", "Expat-Eltern DE", "en", 4),
    ("facebook_group", "München mit Kindern", "facebook.com/groups/Munichkids", "tier1", "C", "DE regional", "de", 3),
    ("facebook_group", "English Speaking Parents of Children in German Schools", "facebook.com/groups/818988174834603", "tier1", "C", "DACH bilingual ⭐", "en", 4),
    ("facebook_group", "Vienna Family Network", "viennafamilynetwork.com", "tier1", "C", "hyper-targeted Wien", "en", 4),
    ("facebook_group", "Echte Mamas", "facebook.com/groups/echtemamas", "tier1", "C", "große DE-Mama-Community", "de", 3),
    ("facebook_group", "Eltern aus Wien (multikulturell)", "facebook.com/groups/303065170040788", "tier1", "C", "AT multikulturell", "de", 4),
    ("facebook_group", "Yoto Community", "facebook.com/groups/Yotoplayergroup", "tier1", "A", "Audio-Toy-Eltern", "en", 3),
    ("facebook_group", "Freilerner in Deutschland", "facebook.com/groups/freilernerindeutschland", "tier1", "C", "screen-free-Ethos", "de", 3),
    ("facebook_group", "Expat Parents Switzerland", "facebook.com/groups/ExpatParents.ch", "tier1", "C", "CH Expat-Eltern", "en", 3),
    # Foren (meist forbidden → organisch + Offenlegung; * = promo-toleranter)
    ("forum", "elternforen.com — Mehrsprachige Erziehung", "elternforen.com/forums/mehrsprachige-erziehung.38/", "tier1", "C", "with-disclosure ⭐ dediziertes Subforum", "de", 5),
    ("forum", "mamacommunity.de — Produktempfehlungen", "mamacommunity.de/forum", "tier1", "C", "with-disclosure ⭐ Produktempfehlungs-Subforum", "de", 4),
    ("forum", "kindaktuell.at — Mehrsprachig", "forum.kindaktuell.at/categories/mehrsprachig-multi-lingual", "tier1", "C", "AT ⭐", "de", 4),
    ("forum", "swissmomforum.ch — Kids-Club", "swissmomforum.ch", "tier1", "C", "CH; with-disclosure", "de", 3),
    ("forum", "parents.at — Erziehung", "parents.at", "tier1", "B", "forbidden → nur organisch", "de", 3),
    ("forum", "babyforum.at — Entwicklung & Erziehung", "babyforum.at", "tier1", "C", "AT", "de", 3),
    # Instagram creators (Engagement/Kollab-DM; Kooperation = 'Werbung' kennzeichnen)
    ("instagram", "IG @servusmami", "instagram.com/servusmami", "tier1", "C", "Kollab-DM/Kommentar; Multi-Kulti Wien ⭐; Werbung kennzeichnen", "de", 5),
    ("instagram", "IG @einfach_mehrsprachig", "instagram.com/einfach_mehrsprachig", "tier1", "C", "Nische Mehrsprachigkeit; Kollab", "de", 4),
    ("instagram", "IG @bilingualcoco", "instagram.com/bilingualcoco", "tier1", "C", "275k Top-Stimme; Kollab-DM", "en", 4),
    ("instagram", "IG @diekindergartenpaedagogin", "instagram.com/diekindergartenpaedagogin", "tier1", "C", "Kita/Pädagogik DE; Kollab", "de", 4),
    ("instagram", "IG @no.screen.kids", "instagram.com/no.screen.kids", "tier1", "A", "screen-free; Kollab", "en", 3),
    ("instagram", "IG @themontessorinotebook", "instagram.com/themontessorinotebook", "tier1", "C", "Montessori/bilingual; Kollab", "en", 3),
    ("instagram", "IG @multilingualparenting", "instagram.com/multilingualparenting", "tier1", "C", "Rita Rosenback; Kollab", "en", 3),
    ("instagram", "IG @ninos.and.nature", "instagram.com/ninos.and.nature", "tier1", "C", "bilingual/Natur; Kollab", "en", 3),
    ("instagram", "IG @carolin_de_bruin", "instagram.com/carolin_de_bruin", "tier1", "C", "Mama/Spielideen Berlin; Kollab", "de", 3),
    ("instagram", "IG @leandra_procelli", "instagram.com/leandra_procelli", "tier1", "C", "Bad Mom Academy Wien; Kollab", "de", 3),
]


def main():
    conn = sqlite3.connect(DB)
    existing = {r[0].lower() for r in conn.execute("SELECT name FROM targets")}
    ins = 0
    for plat, name, contact, wave, angle, rules, lang, prio in S:
        if name.lower() in existing:
            continue
        conn.execute(
            """INSERT INTO targets (channel,platform,name,contact,url,wave,angle,
                                    posting_mode,rules,language,priority)
               VALUES ('social',?,?,?,?,?,?, 'manual_community', ?, ?, ?)""",
            (plat, name, contact, contact, wave, angle, rules, lang, prio))
        existing.add(name.lower())
        ins += 1
    conn.commit()
    n = conn.execute("SELECT COUNT(*) FROM targets WHERE channel='social'").fetchone()[0]
    print(f"social import: +{ins} new · social total = {n}")
    conn.close()


if __name__ == "__main__":
    main()