"""
Social Media Collector for Brand Monitoring v2 (Google RSS workaround)
Uses Google News RSS to capture public posts from Facebook, Threads, TikTok.

Usage:
    python3 collect_social.py
"""

import json
import sqlite3
import datetime
import re
import feedparser
import urllib.parse

DB_PATH = "/opt/ai-os/products/ceo/brand-monitoring/data/brand-monitoring.db"
KEYWORDS = [
    "Fulbright Việt Nam",
    "Fulbright University Vietnam",
    "FUV",
    "FSPPM",
    "Fulbright TPHCM",
    "Đại học Fulbright",
    "trường fulbright", 
    "campus fulbright", 
    "fulbright việt nam"
]
VN_KEYWORDS_LOWER = ["việt nam", "vietnam", "tphcm", "hcmc", "saigon", "hồ chí minh", "hanoi", "hà nội", "fuv", "fsppm", "đại học fulbright", "fulbright university vietnam", "trường đại học fulbright", "trường fulbright", "campus fulbright", "fulbright việt nam"]

LANG = "vi"

def insert_raw(source_id, source_display, url, title, snippet, published):
    conn = sqlite3.connect(DB_PATH)
    cur = conn.cursor()
    cur.execute("SELECT id FROM raw_items WHERE url = ?", (url,))
    existing = cur.fetchone()
    if existing:
        conn.close()
        return existing[0]
    raw_json = json.dumps({
        "source": source_id,
        "title": title,
        "snippet": snippet
    }, ensure_ascii=False, default=str)
    cur.execute("""
        INSERT INTO raw_items (source_id, url, title, snippet, published, fetched_at, brand_id, lang, raw_json)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (source_display, url, title, snippet, published,
          datetime.datetime.now(datetime.timezone.utc).isoformat(),
          "fulbright", LANG, raw_json))
    conn.commit()
    conn.close()
    return cur.lastrowid

def collect_platform(platform_label, site_filter, lookback_days=7):
    for kw in KEYWORDS:
        query = urllib.parse.quote(f"site:{site_filter} {kw}")
        url = f"https://news.google.com/rss/search?q={query}&hl=vi&gl=VN&ceid=VN:vi"
        feed = feedparser.parse(url)
        for entry in feed.entries:
            try:
                title = entry.title
                link = entry.link
                published = entry.get("published", "")
                snippet = entry.get("summary", entry.get("description", ""))
                snippet = re.sub("<[^<]+?>", "", snippet) if snippet else ""
                
                # VN Content Filter
                combined = (title + " " + snippet).lower()
                if not any(kw in combined for kw in VN_KEYWORDS_LOWER):
                    continue
                    
                # Hub Filter
                if re.search(r"#(Fulbright|FUV|FSPPM)", title) and len(title) < 40:
                    continue
                    
                insert_raw(platform_label, platform_label + " News", link, title[:200], snippet[:500], published)
            except Exception:
                pass

def main():
    collect_platform("Facebook", "facebook.com")
    collect_platform("Threads", "threads.net")
    collect_platform("TikTok", "tiktok.com", lookback_days=3)

if __name__ == "__main__":
    main()