"""
One scheduled job, two modes — decided at runtime from social_backfill_state:

  BACKFILL (backfill_complete = 0):
    Walk history backward one BACKFILL_CHUNK_DAYS window per run, from a DB
    cursor, until the earliest post on BOTH platforms is covered. Never fetches
    all history at once. On the first run it does a one-time metadata-only scan
    to find each platform's first-post date (the stop floor).

  STEADY STATE (backfill_complete = 1):
    Lightweight count re-scan of ALL posts (metadata + comment-count summary,
    no comment bodies), diff against social_poll_counts, and download + LLM only
    the posts whose count actually went up. Catches new comments on old posts.

Both modes funnel through run_window(since, until): fetch → count-diff →
INSERT IGNORE → LLM enrich. INSERT IGNORE makes every window idempotent.

Cron (every 3h): 0 */3 * * * cd /path/to/python_social_comments && python poll_and_process.py >> poll.log 2>&1
(Optionally run more frequently while backfilling — it self-limits to one chunk
per run and auto-flips to steady state when done.)

DB tables are created automatically (ensure_table / ensure_backfill_table):
  social_poll_counts    (post_id PK, platform, comment_count, updated_at)
  social_backfill_state (id PK=1, cursor_date, fb_first_post_date,
                         ig_first_post_date, backfill_complete, updated_at)
"""

import os
import time
from datetime import datetime, timedelta
from urllib.parse import urlparse, parse_qs

import pandas as pd
from sqlalchemy import create_engine, text, bindparam
from dotenv import load_dotenv

from download_comments import (
    check_env,
    fetch_page_reels,
    fetch_ig_media,
    process_all_comments,
    fb_get,
    PAGE_ID,
    IG_BUSINESS_ID,
)
from db import insert_dataframe_ignore
from process_social_comments import process_comments
from process_intenal_comments import process_internal_comments

load_dotenv()

engine = create_engine(os.getenv("MYSQL_URI"), pool_pre_ping=True)

# How far back each backfill run reaches (one window per run — never all at once).
BACKFILL_CHUNK_DAYS = 7
# Fallback recent window for steady state only if no first-post floor is known
# (should not happen once backfill has run — steady state scans full history).
STEADY_LOOKBACK_DAYS = 7

# Internal (in-app) comment enrichment runs every invocation, independent of the
# social poll. Capped per run so a large first-time backlog (~4.5k) drains
# gradually rather than all at once. Set INTERNAL_MAX_PER_RUN = 0 to drain fully.
INTERNAL_FETCH_BATCH = 200
INTERNAL_MAX_PER_RUN = 500

FB_SYSTEM_USERNAME = os.getenv("FB_SYSTEM_USERNAME", "facebook_user")
IG_SYSTEM_USERNAME = os.getenv("IG_SYSTEM_USERNAME", "instagram_user")


# ----------------------------------------
# PLATFORM USER IDs
# ----------------------------------------

def fetch_platform_user_ids():
    with engine.connect() as conn:
        rows = conn.execute(text("""
            SELECT id, user_name FROM user
            WHERE user_name IN (:fb_user, :ig_user)
        """), {"fb_user": FB_SYSTEM_USERNAME, "ig_user": IG_SYSTEM_USERNAME}).fetchall()

    mapping = {row[1]: row[0] for row in rows}

    user_ids = {
        "facebook": mapping.get(FB_SYSTEM_USERNAME),
        "instagram": mapping.get(IG_SYSTEM_USERNAME),
    }

    if not user_ids["facebook"]:
        print(f"[WARN] No user found for FB username '{FB_SYSTEM_USERNAME}'")
    if not user_ids["instagram"]:
        print(f"[WARN] No user found for IG username '{IG_SYSTEM_USERNAME}'")

    return user_ids


# ----------------------------------------
# DB STATE
# ----------------------------------------

def ensure_table():
    with engine.begin() as conn:
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS social_poll_counts (
                post_id VARCHAR(100) PRIMARY KEY,
                platform VARCHAR(20),
                comment_count INT DEFAULT 0,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            )
        """))


def load_counts():
    with engine.connect() as conn:
        rows = conn.execute(text("SELECT post_id, comment_count FROM social_poll_counts")).fetchall()
    return {row[0]: row[1] for row in rows}


def save_counts(posts):
    if not posts:
        return
    with engine.begin() as conn:
        conn.execute(text("""
            INSERT INTO social_poll_counts (post_id, platform, comment_count, updated_at)
            VALUES (:post_id, :platform, :comment_count, NOW())
            ON DUPLICATE KEY UPDATE
                comment_count = VALUES(comment_count),
                updated_at = NOW()
        """), posts)


# ----------------------------------------
# BACKFILL STATE
# ----------------------------------------

def ensure_backfill_table():
    with engine.begin() as conn:
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS social_backfill_state (
                id TINYINT PRIMARY KEY DEFAULT 1,
                cursor_date DATE,
                fb_first_post_date DATE,
                ig_first_post_date DATE,
                backfill_complete TINYINT DEFAULT 0,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            )
        """))
        # Guarantee the single state row exists.
        conn.execute(text("""
            INSERT IGNORE INTO social_backfill_state (id, backfill_complete)
            VALUES (1, 0)
        """))


def load_backfill_state():
    with engine.connect() as conn:
        row = conn.execute(text("""
            SELECT cursor_date, fb_first_post_date, ig_first_post_date, backfill_complete
            FROM social_backfill_state WHERE id = 1
        """)).fetchone()
    return {
        "cursor_date": row[0],
        "fb_first_post_date": row[1],
        "ig_first_post_date": row[2],
        "backfill_complete": row[3],
    }


def save_backfill_state(cursor_date=None, fb_first=None, ig_first=None, complete=None):
    sets, params = [], {}
    if cursor_date is not None:
        sets.append("cursor_date = :cursor_date"); params["cursor_date"] = cursor_date
    if fb_first is not None:
        sets.append("fb_first_post_date = :fb_first"); params["fb_first"] = fb_first
    if ig_first is not None:
        sets.append("ig_first_post_date = :ig_first"); params["ig_first"] = ig_first
    if complete is not None:
        sets.append("backfill_complete = :complete"); params["complete"] = complete
    if not sets:
        return
    with engine.begin() as conn:
        conn.execute(text(f"UPDATE social_backfill_state SET {', '.join(sets)} WHERE id = 1"), params)


def _oldest_post_date(endpoint, time_field):
    """Paginate an endpoint (metadata only) and return the oldest post's date."""
    oldest = None
    params = {"fields": f"id,{time_field}", "limit": 100}
    while True:
        data = fb_get(endpoint, params)
        for r in data.get("data", []):
            ts = r.get(time_field)
            if not ts:
                continue
            d = datetime.strptime(ts, "%Y-%m-%dT%H:%M:%S%z").date()
            if oldest is None or d < oldest:
                oldest = d
        paging = data.get("paging", {})
        if "next" not in paging:
            break
        params["after"] = parse_qs(urlparse(paging["next"]).query)["after"][0]
    return oldest


def scan_first_post_dates():
    """One-time metadata-only scan of each platform's earliest post (the backfill floor)."""
    fb_first = _oldest_post_date(f"{PAGE_ID}/feed", "created_time")
    ig_first = _oldest_post_date(f"{IG_BUSINESS_ID}/media", "timestamp")
    return fb_first, ig_first


# ----------------------------------------
# CHECK: filter to posts with new comments
# ----------------------------------------

def filter_posts_with_new_comments(fb_reels, ig_media, stored_counts):
    new_fb, new_ig = [], []

    for reel in fb_reels:
        post_id = reel["id"]
        current = reel.get("comments", {}).get("summary", {}).get("total_count", 0)
        if current > stored_counts.get(post_id, -1):
            new_fb.append(reel)
            print(f"  [FB] post {post_id} — stored: {stored_counts.get(post_id, 0)}, current: {current}")

    for media in ig_media:
        post_id = media["id"]
        current = media.get("comments_count", 0)
        if current > stored_counts.get(post_id, -1):
            new_ig.append(media)
            print(f"  [IG] post {post_id} — stored: {stored_counts.get(post_id, 0)}, current: {current}")

    return new_fb, new_ig


def build_count_rows(fb_reels, ig_media):
    rows = []
    for reel in fb_reels:
        rows.append({
            "post_id": reel["id"],
            "platform": "facebook",
            "comment_count": reel.get("comments", {}).get("summary", {}).get("total_count", 0),
        })
    for media in ig_media:
        rows.append({
            "post_id": media["id"],
            "platform": "instagram",
            "comment_count": media.get("comments_count", 0),
        })
    return rows


# ----------------------------------------
# FORMAT DF
# ----------------------------------------

def format_df(df):
    for col in ["reel_created_time", "comment_created_time"]:
        if col in df.columns:
            df[col] = pd.to_datetime(df[col], errors="coerce").dt.strftime("%Y-%m-%d %H:%M:%S")
    df = df.where(pd.notnull(df), None)
    df["article_id"] = pd.to_numeric(df["article_id"], errors="coerce")
    return df


def insert_social_into_auc(df):
    auc_df = pd.DataFrame({
        "article_id":          df["article_id"].values,
        "user_id":             df["user_id"].values,
        "source":              df["platform"].values,
        "platform":            df["platform"].values,
        "external_comment_id": df["comment_id"].values,
        "comment_author_name": df["comment_author_name"].values,
        "msg":                 df["comment_message"].values,
        "created_on":          df["comment_created_time"].values,
        "anonymous":           0,
    })
    auc_df = auc_df.where(pd.notnull(auc_df), None)
    insert_dataframe_ignore(auc_df, "article_user_comment")
    print(f"[POLL] Synced up to {len(auc_df)} comments into article_user_comment")


# ----------------------------------------
# ARTICLE VALIDATION
# ----------------------------------------
# Requirement: a social comment is only ingested if its post carried an article
# link — dynt.news/article/<id> or dyntland.com/article/<id> — AND that article
# id exists in the `article` table. Comments with no link (article_id NULL) or a
# link to a missing article are dropped here — never written to
# social_comments_raw or article_user_comment.

def existing_article_ids(article_ids):
    ids = sorted({int(a) for a in article_ids if a is not None and not pd.isna(a)})
    if not ids:
        return set()
    query = text("SELECT id FROM article WHERE id IN :ids").bindparams(
        bindparam("ids", expanding=True)
    )
    with engine.connect() as conn:
        rows = conn.execute(query, {"ids": ids}).fetchall()
    return {row[0] for row in rows}


def filter_to_existing_articles(df):
    before = len(df)

    # 1) drop comments whose post had no article link
    df = df[df["article_id"].notna()].copy()
    dropped_no_link = before - len(df)

    # 2) keep only comments whose article id is present in the article table
    valid_ids = existing_article_ids(df["article_id"].tolist())
    kept = df[df["article_id"].isin(valid_ids)].copy()
    dropped_missing = len(df) - len(kept)

    if dropped_no_link or dropped_missing:
        print(f"[FILTER] Dropped {dropped_no_link} comment(s) with no article link, "
              f"{dropped_missing} with article not in DB. Kept {len(kept)}.")

    if not kept.empty:
        kept["article_id"] = kept["article_id"].astype(int)
    return kept


# ----------------------------------------
# WINDOW RUNNER (shared by both modes)
# ----------------------------------------

def run_window(since_date, until_date, platform_user_ids):
    """Fetch posts in [since, until), download comments only for posts whose
    count increased since last run, insert, and LLM-enrich. Idempotent."""
    since_str = since_date.strftime("%m/%d/%Y")
    until_str = until_date.strftime("%m/%d/%Y")
    print(f"[POLL] Window {since_str} -> {until_str}")

    # Step 1: lightweight fetch (post metadata + comment counts only)
    fb_reels = fetch_page_reels(since_str, until_str)
    ig_media = fetch_ig_media(since_str, until_str)
    print(f"[POLL] Total posts — FB: {len(fb_reels)}, IG: {len(ig_media)}")

    # Step 2: compare against DB counts
    stored_counts = load_counts()
    new_fb, new_ig = filter_posts_with_new_comments(fb_reels, ig_media, stored_counts)
    print(f"[POLL] Posts with new comments — FB: {len(new_fb)}, IG: {len(new_ig)}")

    count_rows = build_count_rows(fb_reels, ig_media)

    if not new_fb and not new_ig:
        print("[POLL] No new comments in window.")
        save_counts(count_rows)
        return

    # Step 3: download full comments only for posts with new activity
    print("[POLL] Downloading comments for updated posts...")
    data = process_all_comments(new_fb, new_ig)

    if not data:
        print("[POLL] No comment data returned.")
        save_counts(count_rows)
        return

    # Step 4: validate — keep only comments whose post linked to an existing article
    df = format_df(pd.DataFrame(data))
    df = filter_to_existing_articles(df)
    if df.empty:
        print("[POLL] No comments matched an existing article. Nothing to store.")
        save_counts(count_rows)
        return

    # Step 5: insert into DB
    df["user_id"] = df["platform"].map(platform_user_ids)
    insert_dataframe_ignore(df, "social_comments_raw")
    print(f"[POLL] Inserted up to {len(df)} comments (INSERT IGNORE skips duplicates)")
    insert_social_into_auc(df)

    # Step 6: process with LLM (drains all pending auc rows, backfilled included)
    total_processed = 0
    while True:
        batch = process_comments()
        if not batch:
            break
        total_processed += batch
        time.sleep(1)

    save_counts(count_rows)
    print(f"[POLL] Window done. {total_processed} new comments processed.")


# ----------------------------------------
# MODES
# ----------------------------------------

def run_backfill(state, platform_user_ids):
    # One-time bootstrap: find each platform's first-post date (the stop floor)
    # and start the cursor at tomorrow so the first window includes today's posts.
    if state["cursor_date"] is None:
        print("[BACKFILL] One-time first-post scan (metadata only)...")
        fb_first, ig_first = scan_first_post_dates()
        cursor = datetime.now().date() + timedelta(days=1)
        save_backfill_state(cursor_date=cursor, fb_first=fb_first, ig_first=ig_first)
        state["cursor_date"] = cursor
        state["fb_first_post_date"] = fb_first
        state["ig_first_post_date"] = ig_first
        print(f"[BACKFILL] FB first post: {fb_first}, IG first post: {ig_first}")

    floors = [d for d in (state["fb_first_post_date"], state["ig_first_post_date"]) if d is not None]
    if not floors:
        print("[BACKFILL] No posts on either platform. Marking backfill complete.")
        save_backfill_state(complete=1)
        return

    # Cover history until the EARLIER of the two first posts -> both fully covered.
    floor = min(floors)
    until_date = state["cursor_date"]

    if until_date <= floor:
        print("[BACKFILL] Cursor already at/below floor. Marking complete.")
        save_backfill_state(complete=1)
        return

    since_date = until_date - timedelta(days=BACKFILL_CHUNK_DAYS)
    if since_date < floor:
        since_date = floor

    print(f"[BACKFILL] Chunk {since_date} -> {until_date} (floor {floor})")
    run_window(since_date, until_date, platform_user_ids)

    if since_date <= floor:
        save_backfill_state(cursor_date=since_date, complete=1)
        print("[BACKFILL] Reached first post on both platforms. Backfill complete.")
    else:
        save_backfill_state(cursor_date=since_date)
        print(f"[BACKFILL] Cursor advanced to {since_date}.")


def run_steady(state, platform_user_ids):
    # Full lightweight count re-scan of ALL posts; count-diff downloads only the
    # posts whose comment count actually rose (catches late comments on old posts).
    floors = [d for d in (state["fb_first_post_date"], state["ig_first_post_date"]) if d is not None]
    since_date = min(floors) if floors else (datetime.now().date() - timedelta(days=STEADY_LOOKBACK_DAYS))
    until_date = datetime.now().date() + timedelta(days=1)  # +1 so today's posts are included
    print(f"[STEADY] Full count re-scan {since_date} -> {until_date}")
    run_window(since_date, until_date, platform_user_ids)


def run_internal():
    """Enrich unprocessed internal (in-app) comments. Runs every invocation,
    independent of the social poll mode. Capped by INTERNAL_MAX_PER_RUN so a
    large backlog drains over several runs instead of all at once."""
    total = 0
    while True:
        limit = INTERNAL_FETCH_BATCH
        if INTERNAL_MAX_PER_RUN:
            remaining = INTERNAL_MAX_PER_RUN - total
            if remaining <= 0:
                print(f"[INTERNAL] Per-run cap ({INTERNAL_MAX_PER_RUN}) reached; rest next run.")
                break
            limit = min(limit, remaining)
        n = process_internal_comments(limit=limit)
        if not n:
            break
        total += n
        time.sleep(1)
    print(f"[INTERNAL] Processed {total} internal comment(s) this run.")


# ----------------------------------------
# MAIN
# ----------------------------------------

def main():
    check_env()
    ensure_table()
    ensure_backfill_table()

    platform_user_ids = fetch_platform_user_ids()
    state = load_backfill_state()

    now = datetime.now()
    print(f"\n[POLL] {now.strftime('%Y-%m-%d %H:%M:%S')}")

    if not state["backfill_complete"]:
        print("[MODE] BACKFILL")
        run_backfill(state, platform_user_ids)
    else:
        print("[MODE] STEADY STATE")
        run_steady(state, platform_user_ids)

    # Internal (in-app) comment enrichment — every run, regardless of social mode.
    print("[MODE] INTERNAL COMMENTS")
    run_internal()

    print("[POLL] Done.\n")


if __name__ == "__main__":
    main()