from fastapi import FastAPI, Request, Query, Body, Header
from fastapi.middleware.cors import CORSMiddleware
from fastapi.responses import RedirectResponse, JSONResponse
from pydantic import BaseModel

from sqlalchemy import text, and_
from sqlalchemy.orm import Session

from db import engine, SessionLocal, init_db
from models import Base, Post, MetaToken, Comment, CanonicalPost, PlatformPost, MetricsSnapshot, CommentAnalysis
from models import MetaPageToken, AppSetting

import os
import secrets
import httpx
from dotenv import load_dotenv

import re
import unicodedata
from datetime import datetime
from difflib import SequenceMatcher
from sqlalchemy.exc import IntegrityError
from sqlalchemy import func

import json

from openai import OpenAI

import httpx as _openai_httpx

from collections import Counter

app = FastAPI()


app.add_middleware(
    CORSMiddleware,
    allow_origins=[
        "http://localhost:3000",
        "http://127.0.0.1:3000",
        "https://social-analytics.virtual-mx.com",
    ],
    allow_credentials=True,
    allow_methods=["GET", "POST", "PUT", "PATCH", "DELETE", "OPTIONS"],
    allow_headers=["*"],
    expose_headers=["*"],
)

# Run init_db when the app module is loaded (e.g. under WSGI/Passenger where lifespan may not run).
try:
    init_db()
except Exception:
    import traceback
    traceback.print_exc()
    # Don't re-raise: allow app to stay up; /health will return 503 if DB is broken.


@app.on_event("startup")
def on_startup():
    try:
        init_db()
    except Exception:
        import traceback
        traceback.print_exc()
        # Don't re-raise: allow app to stay up so / and /health can respond (health will return 503)


@app.exception_handler(Exception)
def unhandled_exception_handler(request: Request, exc: Exception):
    """Log unhandled exceptions and return 500 JSON so the process stays up and logs are visible."""
    import traceback
    traceback.print_exc()
    return JSONResponse(
        status_code=500,
        content={
            "detail": "Internal server error. Check the Python application log in cPanel for the traceback.",
            "type": type(exc).__name__,
        },
    )


@app.get("/")
def root():
    """Root route so GET / returns a complete response (avoids 'Incomplete response' on some hosts)."""
    return {"service": "social-analytics-backend", "health": "/health", "docs": "/docs"}


@app.get("/health")
def health():
    """Health check. Returns 503 if DB is unavailable so the app stays up and the proxy doesn't get 500."""
    try:
        with engine.connect() as conn:
            conn.execute(text("SELECT 1"))
        return {"status": "ok"}
    except Exception as e:
        return JSONResponse(
            status_code=503,
            content={"status": "error", "detail": str(e), "hint": "Check that backend/data exists and is writable by the app user."},
        )


class LoginBody(BaseModel):
    username: str
    password: str


@app.post("/auth/login")
def auth_login(data: LoginBody, request: Request):
    if not AUTH_ENABLED:
        return {"ok": True, "message": "auth_disabled"}
    if not AUTH_SECRET:
        return JSONResponse(status_code=500, content={"ok": False, "error": "auth_not_configured"})
    # Normalize to avoid encoding/whitespace/Unicode form mismatches
    def _n(s: str) -> str:
        t = (s or "").strip().replace("\r\n", "\n").replace("\r", "\n")
        return unicodedata.normalize("NFC", t)
    username = _n(str(data.username))
    password = _n(str(data.password))
    # Use hardcoded credentials so login works even when cPanel env is wrong/truncated
    expected_user = _n(_HARDCODED_ADMIN_USERNAME)
    expected_pass = _n(_HARDCODED_ADMIN_PASSWORD)
    if username != expected_user or password != expected_pass:
        content: dict = {
            "ok": False,
            "error": "invalid_credentials",
            # Lengths so we can see if server has credentials and what it received
            "_debug": {
                "expected_user_len": len(expected_user),
                "expected_pass_len": len(expected_pass),
                "received_user_len": len(username),
                "received_pass_len": len(password),
            },
        }
        return JSONResponse(status_code=401, content=content)
    token = _auth_create_token(username)
    resp = JSONResponse(content={"ok": True})
    secure = os.getenv("AUTH_COOKIE_SECURE", "").strip().lower() in ("1", "true", "yes")
    # SameSite=None so cookie is sent when frontend and backend are on different subdomains (e.g. virtual-mx.com)
    samesite = "none" if secure else "lax"
    resp.set_cookie(
        key=AUTH_COOKIE_NAME,
        value=token,
        max_age=AUTH_COOKIE_MAX_AGE_DAYS * 24 * 3600,
        httponly=True,
        secure=secure,
        samesite=samesite,
        path="/",
    )
    return resp


@app.post("/auth/logout")
def auth_logout():
    resp = JSONResponse(content={"ok": True})
    secure = os.getenv("AUTH_COOKIE_SECURE", "").strip().lower() in ("1", "true", "yes")
    samesite = "none" if secure else "lax"
    # delete_cookie doesn't set samesite/secure; clear with set_cookie so browser accepts it cross-subdomain
    resp.set_cookie(
        key=AUTH_COOKIE_NAME,
        value="",
        max_age=0,
        path="/",
        httponly=True,
        secure=secure,
        samesite=samesite,
    )
    return resp


@app.get("/auth/check")
def auth_check(request: Request):
    if not AUTH_ENABLED:
        return {"ok": True, "auth_enabled": False}
    token = request.cookies.get(AUTH_COOKIE_NAME)
    user = _auth_verify_token(token) if token else None
    if not user:
        return JSONResponse(status_code=401, content={"ok": False, "error": "unauthorized"})
    return {"ok": True, "auth_enabled": True, "user": user}


@app.get("/auth/debug-env")
def auth_debug_env(request: Request):
    """Verify backend sees ADMIN_USERNAME and ADMIN_PASSWORD from env. Requires ?secret=CRON_SECRET."""
    expected = os.getenv("CRON_SECRET", "").strip()
    secret = request.query_params.get("secret") or request.headers.get("X-Cron-Secret") or ""
    if not expected or (secret or "").strip() != expected:
        return JSONResponse(status_code=403, content={"ok": False, "error": "missing or invalid secret"})
    u = (os.getenv("ADMIN_USERNAME") or _HARDCODED_ADMIN_USERNAME).strip()
    p = (os.getenv("ADMIN_PASSWORD") or _HARDCODED_ADMIN_PASSWORD).strip()
    return {
        "ok": True,
        "admin_username_set": bool(u),
        "admin_password_set": bool(p),
        "admin_username_length": len(u),
        "hint": "If admin_username_length is wrong (e.g. 8 instead of 7), trim spaces in cPanel. If admin_password_set is false, set ADMIN_PASSWORD in cPanel.",
    }


def _create_post_in_db(title: str | None):
    db: Session = SessionLocal()
    try:
        post = Post(title=title)
        db.add(post)
        db.commit()
        db.refresh(post)
        return {
            "id": post.id,
            "title": post.title,
            "created_at": post.created_at,
        }
    finally:
        db.close()


@app.get("/posts/create")
def create_post_get(title: str | None = None):
    return _create_post_in_db(title)


@app.post("/posts")
def create_post_post(title: str | None = None):
    return _create_post_in_db(title)


@app.get("/posts")
def list_posts():
    db: Session = SessionLocal()
    try:
        posts = db.query(Post).order_by(Post.created_at.desc()).all()
        return [{"id": p.id, "title": p.title, "created_at": p.created_at} for p in posts]
    finally:
        db.close()


# In production (e.g. cPanel), set SKIP_DOTENV=1 and configure env vars in the panel instead of .env
if not os.getenv("SKIP_DOTENV", "").strip().lower() in ("1", "true", "yes"):
    load_dotenv()

# ---------------------------------------------------------------------------
# Authentication (for VPS / production)
# Set AUTH_ENABLED=1, AUTH_SECRET=<random>. Credentials: env vars or hardcoded fallback below.
# ---------------------------------------------------------------------------
# Hardcoded fallback when cPanel/Passenger does not pass ADMIN_* env to the process
_HARDCODED_ADMIN_USERNAME = "mouklab"
_HARDCODED_ADMIN_PASSWORD = "6v3G8nEr6aqqB6v.d!Zq"

AUTH_ENABLED = os.getenv("AUTH_ENABLED", "").strip().lower() in ("1", "true", "yes")
AUTH_SECRET = os.getenv("AUTH_SECRET", "").strip()
# Always use hardcoded so cPanel env truncation/wrong value doesn't break login
ADMIN_USERNAME = _HARDCODED_ADMIN_USERNAME.strip()
ADMIN_PASSWORD = _HARDCODED_ADMIN_PASSWORD.strip()

AUTH_COOKIE_NAME = "session"
AUTH_COOKIE_MAX_AGE_DAYS = 7

AUTH_PUBLIC_PREFIXES = (
    "/health",
    "/auth/login",
    "/auth/logout",
    "/auth/check",
    "/auth/meta/",
    "/openapi.json",
    "/docs",
    "/redoc",
)


def _auth_public_path(path: str) -> bool:
    path = (path or "").split("?")[0]
    if path == "/" or path == "":
        return True
    return any(path == p or path.startswith(p.rstrip("/") + "/") or (p.endswith("/") and path.startswith(p)) for p in AUTH_PUBLIC_PREFIXES)


def _auth_create_token(username: str) -> str:
    import jwt
    from datetime import timedelta
    payload = {"sub": username, "exp": datetime.utcnow() + timedelta(days=AUTH_COOKIE_MAX_AGE_DAYS)}
    return jwt.encode(payload, AUTH_SECRET, algorithm="HS256")


def _auth_verify_token(token: str) -> str | None:
    import jwt
    try:
        payload = jwt.decode(token, AUTH_SECRET, algorithms=["HS256"])
        return (payload.get("sub") or "").strip() or None
    except Exception:
        return None


@app.middleware("http")
async def auth_middleware(request: Request, call_next):
    if not AUTH_ENABLED:
        return await call_next(request)
    # CORS preflight (OPTIONS) does not send cookies; allow it so the actual request can be sent with auth
    if request.method == "OPTIONS":
        return await call_next(request)
    path = request.url.path
    if _auth_public_path(path):
        return await call_next(request)
    # Allow cron job or debug-env with CRON_SECRET when auth is enabled
    expected = os.getenv("CRON_SECRET", "").strip()
    secret = request.headers.get("X-Cron-Secret") or request.query_params.get("secret") or ""
    if expected and (secret or "").strip() == expected:
        if path.rstrip("/").endswith("/cron/daily") or path.rstrip("/").endswith("/auth/debug-env"):
            return await call_next(request)
    token = request.cookies.get(AUTH_COOKIE_NAME)
    if not token or not _auth_verify_token(token):
        return JSONResponse(status_code=401, content={"ok": False, "error": "unauthorized"})
    return await call_next(request)


def _get_env(name: str) -> str:
    v = os.getenv(name)
    if not v:
        raise RuntimeError(f"Missing env var: {name}")
    return v


OPENAI_PROMPT_VERSION = "v1"

OPENAI_ALLOWED_TOPICS = [
    "gameplay",
    "physics",
    "graphics",
    "performance",
    "bugs",
    "content_tracks",
    "content_bikes",
    "realism",
    "esports_competition",
    "pricing",
    "dlc_value",
    "community",
    "communication_marketing",
    "support_tech",
    "platforms_pc_console",
    "wishlist_release_date",
    "licensing_brands",
    "modding",
]


def _openai_client() -> OpenAI:
    api_key = os.getenv("OPENAI_API_KEY")
    if not api_key:
        raise RuntimeError("Missing env var: OPENAI_API_KEY")
    timeout_s = float(os.getenv("OPENAI_TIMEOUT_SECONDS", "30"))
    http_client = _openai_httpx.Client(timeout=timeout_s)
    return OpenAI(api_key=api_key, http_client=http_client)


OPENAI_JSON_SCHEMA = {
    "name": "comment_analysis",
    "strict": True,
    "schema": {
        "type": "object",
        "additionalProperties": False,
        "properties": {
            "sentiment": {
                "type": "object",
                "additionalProperties": False,
                "properties": {
                    "label": {"type": "string", "enum": ["positive", "neutral", "negative", "mixed"]},
                    "score": {"type": "number", "minimum": 0, "maximum": 1},
                },
                "required": ["label", "score"],
            },
            "emotion": {
                "type": "object",
                "additionalProperties": False,
                "properties": {
                    "primary": {
                        "type": "string",
                        "enum": [
                            "joy",
                            "anger",
                            "frustration",
                            "hype",
                            "disappointment",
                            "sarcasm",
                            "curiosity",
                            "admiration",
                            "other",
                        ],
                    },
                    "confidence": {"type": "number", "minimum": 0, "maximum": 1},
                },
                "required": ["primary", "confidence"],
            },
            "intent": {
                "type": "object",
                "additionalProperties": False,
                "properties": {
                    "type": {
                        "type": "string",
                        "enum": [
                            "feedback",
                            "question",
                            "complaint",
                            "praise",
                            "joke",
                            "comparison",
                            "support_request",
                            "other",
                        ],
                    },
                    "confidence": {"type": "number", "minimum": 0, "maximum": 1},
                },
                "required": ["type", "confidence"],
            },
            "topics": {
                "type": "array",
                "maxItems": 5,
                "items": {
                    "type": "object",
                    "additionalProperties": False,
                    "properties": {
                        "label": {"type": "string"},
                        "confidence": {"type": "number", "minimum": 0, "maximum": 1},
                    },
                    "required": ["label", "confidence"],
                },
            },
            "toxicity": {
                "type": "object",
                "additionalProperties": False,
                "properties": {
                    "is_toxic": {"type": "boolean"},
                    "level": {"type": "number", "minimum": 0, "maximum": 1},
                },
                "required": ["is_toxic", "level"],
            },
            "purchase_signal": {
                "type": "object",
                "additionalProperties": False,
                "properties": {
                    "is_signal": {"type": "boolean"},
                    "confidence": {"type": "number", "minimum": 0, "maximum": 1},
                },
                "required": ["is_signal", "confidence"],
            },
            "summary": {"type": "string"},
            "language": {"type": "string"},
            "needs_context": {"type": "boolean"},
            "flags": {"type": "array", "items": {"type": "string"}},
        },
        "required": [
            "sentiment",
            "emotion",
            "intent",
            "topics",
            "toxicity",
            "purchase_signal",
            "summary",
            "language",
            "needs_context",
            "flags",
        ],
    },
}


def _pct(v: float | None) -> int | None:
    if v is None:
        return None
    try:
        return int(max(0.0, min(1.0, float(v))) * 100)
    except Exception:
        return None


def _build_openai_system_prompt(allowed_topics: list[str]) -> str:
    topics = ", ".join(allowed_topics)
    return (
        "You are a social comment classifier. Output must be valid JSON matching the provided JSON Schema. "
        "Do not include any text outside the JSON object. "
        "Do not invent facts. Use only the provided text. "
        "For topics, use only labels from this allowlist: "
        + topics
        + ". If none apply, return an empty topics array."
    )


def _build_openai_user_payload(
    platform: str,
    post_text: str,
    parent_comment_text: str,
    comment_text: str,
    detected_language: str,
    allowed_topics: list[str],
) -> str:
    return "\n".join(
        [
            f"platform: {platform}",
            f"post_text: {post_text or ''}",
            f"parent_comment_text: {parent_comment_text or ''}",
            f"comment_text: {comment_text or ''}",
            f"allowed_topics: {', '.join(allowed_topics)}",
            f"detected_language: {detected_language or 'other'}",
        ]
    )


def _openai_analyze_comment(
    *,
    platform: str,
    comment_text: str,
    post_text: str = "",
    parent_comment_text: str = "",
    detected_language: str = "other",
    allowed_topics: list[str] | None = None,
    model: str = "gpt-4o-mini",
) -> dict:
    allowed_topics = allowed_topics or OPENAI_ALLOWED_TOPICS

    client = _openai_client()
    sys_prompt = _build_openai_system_prompt(allowed_topics)
    user_payload = _build_openai_user_payload(
        platform=platform,
        post_text=post_text,
        parent_comment_text=parent_comment_text,
        comment_text=comment_text,
        detected_language=detected_language,
        allowed_topics=allowed_topics,
    )

    res = client.chat.completions.create(
        model=model,
        messages=[
            {"role": "system", "content": sys_prompt},
            {"role": "user", "content": user_payload},
        ],
        response_format={"type": "json_schema", "json_schema": OPENAI_JSON_SCHEMA},
        temperature=0,
    )

    content = res.choices[0].message.content if res.choices else None
    if not content:
        raise RuntimeError("empty_openai_response")

    try:
        return json.loads(content)
    except Exception as e:
        raise RuntimeError(f"openai_json_parse_failed: {repr(e)}")


def _save_meta_token(access_token: str, token_type: str | None, expires_in: int | None):
    db = SessionLocal()
    try:
        db.query(MetaToken).delete()
        db.add(MetaToken(access_token=access_token, token_type=token_type, expires_in=expires_in))
        db.commit()
    finally:
        db.close()


def _get_meta_token() -> str | None:
    db = SessionLocal()
    try:
        row = db.query(MetaToken).order_by(MetaToken.created_at.desc()).first()
        return row.access_token if row else None
    finally:
        db.close()

def _save_page_token(page_id: str, access_token: str):
    db = SessionLocal()
    try:
        existing = db.query(MetaPageToken).filter(MetaPageToken.page_id == str(page_id)).first()
        if existing:
            existing.access_token = access_token
        else:
            db.add(MetaPageToken(page_id=str(page_id), access_token=access_token))
        db.commit()
    finally:
        db.close()

def _get_page_token(page_id: str) -> str | None:
    db = SessionLocal()
    try:
        row = db.query(MetaPageToken).filter(MetaPageToken.page_id == str(page_id)).first()
        return row.access_token if row else None
    finally:
        db.close()

async def _graph_get(path: str, params: dict | None = None, access_token: str | None = None):
    token = access_token or _get_meta_token()
    if not token:
        return {"ok": False, "error": "meta_not_connected"}

    p = params.copy() if params else {}
    p["access_token"] = token

    async with httpx.AsyncClient(timeout=30.0) as client:
        res = await client.get(f"https://graph.facebook.com/v23.0/{path.lstrip('/')}", params=p)
        try:
            data = res.json()
        except Exception:
            data = {"raw_text": res.text}

        if res.status_code >= 400:
            return {"ok": False, "status_code": res.status_code, "error_response": data}

        return data


async def _graph_get_url(full_url: str):
    async with httpx.AsyncClient(timeout=30.0) as client:
        res = await client.get(full_url)
        try:
            data = res.json()
        except Exception:
            data = {"raw_text": res.text}
        if res.status_code >= 400:
            return {"ok": False, "status_code": res.status_code, "error_response": data}
        return data


async def _graph_paginate(edge_path: str, params: dict):
    first = await _graph_get(edge_path, params=params)
    if isinstance(first, dict) and first.get("ok") is False:
        return first

    items = []
    items.extend(first.get("data", []))

    next_url = first.get("paging", {}).get("next")
    while next_url:
        page = await _graph_get_url(next_url)
        if isinstance(page, dict) and page.get("ok") is False:
            return page
        items.extend(page.get("data", []))
        next_url = page.get("paging", {}).get("next")

    return {"ok": True, "data": items}


def _extract_ig_shortcode(url: str) -> str | None:
    if not url:
        return None
    m = re.search(r"instagram\.com/(p|reel)/([^/?#]+)/?", url)
    if not m:
        return None
    return m.group(2)


def _extract_fb_post_id(url: str) -> str | None:
    """Extract Facebook post ID from various URL formats.
    
    Supports:
    - https://www.facebook.com/PAGE_ID/posts/POST_ID
    - https://www.facebook.com/PAGE_NAME/posts/pfbid...
    - https://www.facebook.com/permalink.php?story_fbid=POST_ID&id=PAGE_ID
    - POST_ID (if it's already just an ID)
    """
    if not url:
        return None
    
    # If it's already just an ID (numeric string or pfbid format)
    if url.isdigit() or url.startswith("pfbid"):
        return url
    
    # Try to extract from /posts/ format (supports both numeric and pfbid)
    # Pattern: /posts/pfbid... or /posts/123456
    m = re.search(r"facebook\.com/[^/]+/posts/([^/?&#]+)", url)
    if m:
        post_id = m.group(1)
        # Remove any trailing characters that might be part of the URL structure
        post_id = post_id.rstrip('/')
        return post_id
    
    # Try to extract from permalink.php format
    m = re.search(r"story_fbid=([^&]+)", url)
    if m:
        return m.group(1)
    
    # Try to extract from any numeric ID in the URL (last resort)
    m = re.search(r"/(\d+)(?:/|$|\?|#)", url)
    if m:
        return m.group(1)
    
    return None


def _normalize_text(s: str) -> str:
    if not s:
        return ""
    s = s.lower()
    s = re.sub(r"https?://\S+", " ", s)
    s = re.sub(r"[^\w\s]", " ", s)
    s = re.sub(r"\s+", " ", s).strip()
    return s


def _similarity(a: str, b: str) -> float:
    a2 = _normalize_text(a)
    b2 = _normalize_text(b)
    if not a2 or not b2:
        return 0.0
    return SequenceMatcher(None, a2, b2).ratio()


def _parse_iso(ts: str) -> datetime | None:
    if not ts:
        return None
    try:
        if ts.endswith("Z"):
            ts = ts[:-1] + "+00:00"
        return datetime.fromisoformat(ts)
    except Exception:
        return None

def _find_existing_canonical_id_for_platform_post(db: Session, platform: str, platform_post_id: str) -> int | None:
    row = (
        db.query(PlatformPost)
        .filter(
            PlatformPost.platform == platform,
            PlatformPost.platform_post_id == platform_post_id,
        )
        .first()
    )
    if not row:
        return None
    return int(row.canonical_post_id)

async def _graph_get_with_token(path: str, params: dict, access_token: str):
    p = params.copy() if params else {}
    p["access_token"] = access_token
    async with httpx.AsyncClient(timeout=30.0) as client:
        res = await client.get(f"https://graph.facebook.com/v23.0/{path.lstrip('/')}", params=p)
        try:
            data = res.json()
        except Exception:
            data = {"raw_text": res.text}
        if res.status_code >= 400:
            return {"ok": False, "status_code": res.status_code, "error_response": data}
        return data


async def _graph_paginate_with_token(edge_path: str, params: dict, access_token: str):
    first = await _graph_get_with_token(edge_path, params=params, access_token=access_token)
    if isinstance(first, dict) and first.get("ok") is False:
        return first

    items = []
    items.extend(first.get("data", []))

    next_url = first.get("paging", {}).get("next")
    while next_url:
        page = await _graph_get_url(next_url)
        if isinstance(page, dict) and page.get("ok") is False:
            return page
        items.extend(page.get("data", []))
        next_url = page.get("paging", {}).get("next")

    return {"ok": True, "data": items}


async def _meta_ig_metrics(media_id: str) -> dict:
    fields = "id,like_count,comments_count,media_type,media_product_type"
    media = await _graph_get(f"/{media_id}", params={"fields": fields})
    if isinstance(media, dict) and media.get("ok") is False:
        return media

    like_count = media.get("like_count")
    comments_count = media.get("comments_count")

    share_count = None
    try:
        insights = await _graph_get(f"/{media_id}/insights", params={"metric": "shares"})
        if isinstance(insights, dict) and insights.get("ok") is False:
            share_count = None
        else:
            data = (insights or {}).get("data") or []
            if data and isinstance(data, list):
                values = data[0].get("values") if isinstance(data[0], dict) else None
                if values and isinstance(values, list) and values:
                    v0 = values[0]
                    if isinstance(v0, dict):
                        share_count = v0.get("value")
    except Exception:
        share_count = None

    return {
        "ok": True,
        "platform": "instagram",
        "platform_post_id": str(media_id),
        "comment_count": comments_count,
        "reaction_count": like_count,
        "share_count": share_count,
        "view_count": None,
        "raw": media,
    }


async def _meta_fb_metrics(page_id: str, post_id: str) -> dict:
    page_token = _get_page_token(page_id)
    if not page_token:
        return {"ok": False, "error": "missing_page_token"}

    # If post_id is a pfbid, we can't use it directly - return error
    if post_id.startswith("pfbid"):
        return {
            "ok": False,
            "error": "pfbid_not_supported",
            "detail": "Facebook Graph API does not support pfbid format for metrics endpoint. Please use a numeric post ID.",
            "hint": "Update the Facebook post using the 'Edit' button to convert pfbid to numeric ID.",
        }

    # Use the post_id directly (should be numeric)
    actual_post_id = str(post_id)

    fields = "id,shares,reactions.summary(total_count),comments.summary(total_count)"
    post = await _graph_get_with_token(f"/{actual_post_id}", params={"fields": fields}, access_token=page_token)
    if isinstance(post, dict) and post.get("ok") is False:
        return post

    # Get the actual numeric ID from the response
    final_post_id = post.get("id") or str(actual_post_id)

    shares_obj = post.get("shares") or {}
    share_count = shares_obj.get("count")

    reactions_obj = post.get("reactions") or {}
    reactions_summary = reactions_obj.get("summary") or {}
    reaction_count = reactions_summary.get("total_count")

    comments_obj = post.get("comments") or {}
    comments_summary = comments_obj.get("summary") or {}
    comment_count = comments_summary.get("total_count")

    return {
        "ok": True,
        "platform": "facebook",
        "platform_post_id": str(final_post_id),  # Return the actual numeric ID from API
        "comment_count": comment_count,
        "reaction_count": reaction_count,
        "share_count": share_count,
        "view_count": None,
        "raw": post,
    }

async def _refresh_canonical(db: Session, canonical_post_id: int, facebook_page_id: str | None = None) -> dict:
    pps = db.query(PlatformPost).filter(PlatformPost.canonical_post_id == canonical_post_id).all()

    ig_pp = next((x for x in pps if x.platform == "instagram"), None)
    fb_pp = next((x for x in pps if x.platform == "facebook"), None)

    out = {
        "canonical_post_id": canonical_post_id,
        "instagram": None,
        "facebook": None,
        "matched_facebook_now": False,
    }

    if ig_pp:
        ig_media_id = ig_pp.platform_post_id

        try:
            ig_sync = await meta_ig_comments_sync(media_id=ig_media_id)
        except Exception as e:
            ig_sync = {"ok": False, "error": "ig_sync_failed", "detail": repr(e)}

        out["instagram"] = ig_sync

        try:
            ig_metrics = await _meta_ig_metrics(media_id=str(ig_media_id))
            if isinstance(ig_metrics, dict) and ig_metrics.get("ok") is True:
                ig_pp.comment_count_api = ig_metrics.get("comment_count")
                ig_pp.reaction_count_api = ig_metrics.get("reaction_count")
                ig_pp.share_count_api = ig_metrics.get("share_count")
                db.add(
                    MetricsSnapshot(
                        canonical_post_id=canonical_post_id,
                        platform="instagram",
                        platform_post_id=str(ig_media_id),
                        comment_count=ig_metrics.get("comment_count"),
                        reaction_count=ig_metrics.get("reaction_count"),
                        share_count=ig_metrics.get("share_count"),
                        view_count=ig_metrics.get("view_count"),
                        raw_json=json.dumps(ig_metrics.get("raw") or {}),
                    )
                )
                db.commit()
        except Exception:
            pass

    if facebook_page_id:
        if not fb_pp and ig_pp:
            ig_caption = ig_pp.caption or ""
            ig_timestamp = ig_pp.created_time or ""
            match = await ingest_facebook_match(page_id=facebook_page_id, ig_caption=ig_caption, ig_timestamp=ig_timestamp)
            if isinstance(match, dict) and match.get("ok") is True:
                fb_post = match.get("facebook_post") or {}
                fb_id = fb_post.get("id")
                if fb_id:
                    fb_pp = PlatformPost(
                        canonical_post_id=canonical_post_id,
                        platform="facebook",
                        platform_post_id=str(fb_id),
                        permalink=fb_post.get("permalink_url"),
                        caption=fb_post.get("message"),
                        created_time=fb_post.get("created_time"),
                        match_confidence=int(match.get("confidence") or 0),
                    )
                    db.add(fb_pp)
                    db.commit()
                    out["matched_facebook_now"] = True

        if fb_pp:
            # Get the numeric post ID if we have a pfbid stored
            # This avoids deprecated API errors
            stored_post_id = str(fb_pp.platform_post_id)
            numeric_post_id = stored_post_id
            
            # If stored ID is a pfbid, we can't use it directly with the API
            # Skip the refresh operations and return a helpful error
            if stored_post_id.startswith("pfbid"):
                out["facebook"] = {
                    "ok": False,
                    "error": "pfbid_not_supported",
                    "detail": "The stored Facebook post ID is in pfbid format which is not supported by the current API. Please update the post using the 'Edit' button to convert it to a numeric ID.",
                    "hint": "Use the 'Edit' button next to the Facebook post to update it with the correct URL. This will automatically convert the pfbid to a numeric ID.",
                }
            else:
                # We have a numeric ID, proceed normally
                # Safety check: make sure post_id is not the same as page_id
                if str(numeric_post_id) == str(facebook_page_id):
                    out["facebook"] = {
                        "ok": False,
                        "error": "invalid_post_id",
                        "detail": f"Stored post ID '{numeric_post_id}' is the same as page ID '{facebook_page_id}'. This would try to access comments on a Page node, which is not supported.",
                        "hint": "The stored post ID appears to be incorrect. Please update the Facebook post using the 'Edit' button with the correct post URL or numeric ID.",
                    }
                else:
                    try:
                        fb_sync = await meta_fb_comments_sync(page_id=facebook_page_id, post_id=numeric_post_id)
                    except Exception as e:
                        fb_sync = {"ok": False, "error": "fb_sync_failed", "detail": repr(e)}

                    out["facebook"] = fb_sync

                    try:
                        fb_metrics = await _meta_fb_metrics(page_id=facebook_page_id, post_id=numeric_post_id)
                        if isinstance(fb_metrics, dict) and fb_metrics.get("ok") is True:
                            # Use the numeric ID returned by metrics (in case it was converted)
                            metrics_post_id = fb_metrics.get("platform_post_id") or numeric_post_id
                            fb_pp.comment_count_api = fb_metrics.get("comment_count")
                            fb_pp.reaction_count_api = fb_metrics.get("reaction_count")
                            fb_pp.share_count_api = fb_metrics.get("share_count")
                            # Update post_id if metrics returned a different (numeric) ID
                            if metrics_post_id != stored_post_id:
                                fb_pp.platform_post_id = metrics_post_id
                            db.add(
                                MetricsSnapshot(
                                    canonical_post_id=canonical_post_id,
                                    platform="facebook",
                                    platform_post_id=str(metrics_post_id),
                                    comment_count=fb_metrics.get("comment_count"),
                                    reaction_count=fb_metrics.get("reaction_count"),
                                    share_count=fb_metrics.get("share_count"),
                                    view_count=fb_metrics.get("view_count"),
                                    raw_json=json.dumps(fb_metrics.get("raw") or {}),
                                )
                            )
                            db.commit()
                    except Exception as e:
                        # Log the error for debugging
                        print(f"Warning: Facebook metrics fetch failed: {repr(e)}")
                        if not out.get("facebook") or out["facebook"].get("ok") is not False:
                            out["facebook"] = {"ok": False, "error": "metrics_fetch_failed", "detail": repr(e)}

    return out


@app.get("/auth/meta/start")
def auth_meta_start():
    app_id = _get_env("META_APP_ID")
    redirect_uri = _get_env("META_REDIRECT_URI")

    state = secrets.token_urlsafe(32)

    scope = ",".join(
        [
            "public_profile",
            "pages_show_list",
            "pages_read_engagement",
            "pages_read_user_content",
            "read_insights",
            "instagram_basic",
            "instagram_manage_comments",
            "instagram_manage_insights",
        ]
    )

    url = (
        "https://www.facebook.com/v23.0/dialog/oauth"
        f"?client_id={app_id}"
        f"&redirect_uri={redirect_uri}"
        f"&state={state}"
        f"&response_type=code"
        f"&scope={scope}"
    )
    return RedirectResponse(url)


@app.get("/auth/meta/callback")
async def auth_meta_callback(request: Request):
    code = request.query_params.get("code")
    error = request.query_params.get("error")
    if error:
        return JSONResponse({"ok": False, "error": error, "details": dict(request.query_params)})

    if not code:
        return JSONResponse({"ok": False, "error": "missing_code", "details": dict(request.query_params)})

    app_id = _get_env("META_APP_ID")
    app_secret = _get_env("META_APP_SECRET")
    redirect_uri = _get_env("META_REDIRECT_URI")

    async with httpx.AsyncClient(timeout=30.0) as client:
        token_res = await client.get(
            "https://graph.facebook.com/v23.0/oauth/access_token",
            params={
                "client_id": app_id,
                "client_secret": app_secret,
                "redirect_uri": redirect_uri,
                "code": code,
            },
        )
        token_res.raise_for_status()
        token_data = token_res.json()

        short_token = token_data.get("access_token")
        if not short_token:
            return JSONResponse({"ok": False, "error": "no_access_token", "token_data": token_data})

        long_res = await client.get(
            "https://graph.facebook.com/v23.0/oauth/access_token",
            params={
                "grant_type": "fb_exchange_token",
                "client_id": app_id,
                "client_secret": app_secret,
                "fb_exchange_token": short_token,
            },
        )
        long_res.raise_for_status()
        long_data = long_res.json()

    access_token = long_data.get("access_token")
    token_type = long_data.get("token_type")
    expires_in = long_data.get("expires_in")

    if not access_token:
        return JSONResponse({"ok": False, "error": "no_long_lived_token", "long_data": long_data})

    _save_meta_token(access_token=access_token, token_type=token_type, expires_in=expires_in)

    return RedirectResponse("http://localhost:3000?meta=connected")


@app.get("/auth/meta/status")
def auth_meta_status():
    token = _get_meta_token()
    return {"connected": bool(token)}


@app.get("/meta/pages")
async def meta_pages():
    return await _graph_get("/me/accounts", params={"fields": "id,name,access_token"})


@app.get("/meta/pages_alt")
async def meta_pages_alt():
    return await _graph_get("/me", params={"fields": "id,name,accounts{id,name,access_token}"})


@app.get("/meta/page/ig-account")
async def meta_page_ig_account(page_id: str):
    data = await _graph_get(f"/{page_id}", params={"fields": "instagram_business_account"})
    return data


@app.get("/meta/ig/media")
async def meta_ig_media(ig_user_id: str, limit: int = 25):
    fields = "id,caption,media_type,media_url,permalink,timestamp,like_count,comments_count"
    data = await _graph_get(f"/{ig_user_id}/media", params={"fields": fields, "limit": str(limit)})
    return data


@app.get("/meta/debug/token")
async def meta_debug_token():
    token = _get_meta_token()
    if not token:
        return {"ok": False, "error": "meta_not_connected"}

    app_id = _get_env("META_APP_ID")
    app_secret = _get_env("META_APP_SECRET")
    app_access_token = f"{app_id}|{app_secret}"

    async with httpx.AsyncClient(timeout=30.0) as client:
        res = await client.get(
            "https://graph.facebook.com/v23.0/debug_token",
            params={
                "input_token": token,
                "access_token": app_access_token,
            },
        )

        try:
            data = res.json()
        except Exception:
            data = {"raw_text": res.text}

        return data


@app.post("/meta/ig/comments/sync")
async def meta_ig_comments_sync(media_id: str):
    try:
        top = await _graph_paginate(
            f"/{media_id}/comments",
            params={"fields": "id,text,username,timestamp,like_count", "limit": "50"},
        )
        if isinstance(top, dict) and top.get("ok") is False:
            return top

        top_items = top["data"]

        all_rows = []
        for c in top_items:
            cid = str(c.get("id"))
            all_rows.append(
                {
                    "comment_id": cid,
                    "parent_comment_id": None,
                    "is_reply": 0,
                    "username": c.get("username"),
                    "text": c.get("text"),
                    "timestamp": c.get("timestamp"),
                    "like_count": c.get("like_count"),
                }
            )

            rep = await _graph_paginate(
                f"/{cid}/replies",
                params={"fields": "id,text,username,timestamp,like_count", "limit": "50"},
            )
            if isinstance(rep, dict) and rep.get("ok") is False:
                return rep

            for r in rep["data"]:
                all_rows.append(
                    {
                        "comment_id": str(r.get("id")),
                        "parent_comment_id": cid,
                        "is_reply": 1,
                        "username": r.get("username"),
                        "text": r.get("text"),
                        "timestamp": r.get("timestamp"),
                        "like_count": r.get("like_count"),
                    }
                )

        db = SessionLocal()
        try:
            db.query(Comment).filter(
                Comment.platform == "instagram",
                Comment.platform_post_id == str(media_id),
            ).delete()

            for row in all_rows:
                db.add(
                    Comment(
                        platform="instagram",
                        platform_post_id=str(media_id),
                        comment_id=row["comment_id"],
                        parent_comment_id=row["parent_comment_id"],
                        is_reply=row["is_reply"],
                        username=row["username"],
                        text=row["text"],
                        timestamp=row["timestamp"],
                        like_count=row["like_count"],
                    )
                )
            db.commit()
        finally:
            db.close()

        return {
            "ok": True,
            "media_id": media_id,
            "saved_total": len(all_rows),
            "saved_top_level": len(top_items),
            "saved_replies": len(all_rows) - len(top_items),
        }

    except Exception as e:
        return {
            "ok": False,
            "error": "server_exception",
            "detail": repr(e),
        }


@app.get("/comments")
def list_comments(platform: str, platform_post_id: str):
    db = SessionLocal()
    try:
        rows = (
            db.query(Comment)
            .filter(
                Comment.platform == platform,
                Comment.platform_post_id == platform_post_id,
            )
            .order_by(Comment.created_at.desc())
            .all()
        )
        return [
            {
                "comment_id": r.comment_id,
                "username": r.username,
                "text": r.text,
                "timestamp": r.timestamp,
                "like_count": r.like_count,
                "created_at": r.created_at,
            }
            for r in rows
        ]
    finally:
        db.close()


def _get_platform_post_caption(platform: str, platform_post_id: str) -> str:
    db = SessionLocal()
    try:
        pp = (
            db.query(PlatformPost)
            .filter(PlatformPost.platform == platform, PlatformPost.platform_post_id == str(platform_post_id))
            .first()
        )
        if not pp:
            return ""
        return pp.caption or ""
    finally:
        db.close()


def _comment_parent_text(platform: str, platform_post_id: str, parent_comment_id: str | None) -> str:
    if not parent_comment_id:
        return ""
    db = SessionLocal()
    try:
        r = (
            db.query(Comment)
            .filter(
                Comment.platform == platform,
                Comment.platform_post_id == str(platform_post_id),
                Comment.comment_id == str(parent_comment_id),
            )
            .first()
        )
        return r.text or "" if r else ""
    finally:
        db.close()


def _analysis_exists(db: Session, platform: str, platform_post_id: str, comment_id: str) -> bool:
    return (
        db.query(CommentAnalysis)
        .filter(
            CommentAnalysis.platform == platform,
            CommentAnalysis.platform_post_id == str(platform_post_id),
            CommentAnalysis.comment_id == str(comment_id),
        )
        .count()
        > 0
    )


def _store_analysis(
    db: Session,
    *,
    platform: str,
    platform_post_id: str,
    comment_id: str,
    parent_comment_id: str | None,
    is_reply: int,
    analysis: dict,
    model: str,
):
    row = (
        db.query(CommentAnalysis)
        .filter(
            CommentAnalysis.platform == platform,
            CommentAnalysis.platform_post_id == str(platform_post_id),
            CommentAnalysis.comment_id == str(comment_id),
        )
        .first()
    )

    topics = analysis.get("topics") if isinstance(analysis, dict) else []
    flags = analysis.get("flags") if isinstance(analysis, dict) else []

    def sget(path: list[str], default=None):
        cur = analysis
        for k in path:
            if not isinstance(cur, dict):
                return default
            cur = cur.get(k)
        return cur if cur is not None else default

    payload = {
        "platform": platform,
        "platform_post_id": str(platform_post_id),
        "comment_id": str(comment_id),
        "parent_comment_id": str(parent_comment_id) if parent_comment_id else None,
        "is_reply": int(is_reply or 0),
        "language": str(analysis.get("language") or "") if isinstance(analysis, dict) else None,
        "sentiment_label": str(sget(["sentiment", "label"], "")) or None,
        "sentiment_score": _pct(sget(["sentiment", "score"], None)),
        "emotion_primary": str(sget(["emotion", "primary"], "")) or None,
        "emotion_confidence": _pct(sget(["emotion", "confidence"], None)),
        "intent_type": str(sget(["intent", "type"], "")) or None,
        "intent_confidence": _pct(sget(["intent", "confidence"], None)),
        "topics_json": json.dumps(topics, ensure_ascii=False),
        "toxicity_is_toxic": bool(sget(["toxicity", "is_toxic"], False)),
        "toxicity_level": _pct(sget(["toxicity", "level"], None)),
        "purchase_is_signal": bool(sget(["purchase_signal", "is_signal"], False)),
        "purchase_confidence": _pct(sget(["purchase_signal", "confidence"], None)),
        "summary": str(analysis.get("summary") or "") if isinstance(analysis, dict) else None,
        "needs_context": bool(analysis.get("needs_context")) if isinstance(analysis, dict) else False,
        "flags_json": json.dumps(flags, ensure_ascii=False),
        "model": model,
        "prompt_version": OPENAI_PROMPT_VERSION,
        "raw_json": json.dumps(analysis, ensure_ascii=False),
    }

    if row:
        for k, v in payload.items():
            setattr(row, k, v)
        return row

    row = CommentAnalysis(**payload)
    db.add(row)
    return row


@app.get("/ai/analyses")
def ai_list_analyses(platform: str, platform_post_id: str):
    db = SessionLocal()
    try:
        rows = (
            db.query(CommentAnalysis)
            .filter(
                CommentAnalysis.platform == platform,
                CommentAnalysis.platform_post_id == str(platform_post_id),
            )
            .order_by(CommentAnalysis.created_at.desc())
            .all()
        )
        out = []
        for r in rows:
            out.append(
                {
                    "comment_id": r.comment_id,
                    "language": r.language,
                    "sentiment_label": r.sentiment_label,
                    "sentiment_score": r.sentiment_score,
                    "emotion_primary": r.emotion_primary,
                    "emotion_confidence": r.emotion_confidence,
                    "intent_type": r.intent_type,
                    "intent_confidence": r.intent_confidence,
                    "topics": json.loads(r.topics_json) if r.topics_json else [],
                    "toxicity_is_toxic": r.toxicity_is_toxic,
                    "toxicity_level": r.toxicity_level,
                    "toxicity_manual_override": r.toxicity_manual_override,
                    "purchase_is_signal": r.purchase_is_signal,
                    "purchase_confidence": r.purchase_confidence,
                    "summary": r.summary,
                    "needs_context": r.needs_context,
                    "flags": json.loads(r.flags_json) if r.flags_json else [],
                    "model": r.model,
                    "prompt_version": r.prompt_version,
                    "created_at": r.created_at,
                }
            )
        return {"ok": True, "count": len(out), "items": out}
    finally:
        db.close()


@app.get("/toxicity/review")
def toxicity_review():
    """Get all comments flagged as toxic that haven't been manually reviewed yet"""
    db = SessionLocal()
    try:
        # Get all analyses where toxicity_is_toxic is True and toxicity_manual_override is None
        rows = (
            db.query(CommentAnalysis, Comment)
            .join(Comment, 
                  and_(
                      CommentAnalysis.platform == Comment.platform,
                      CommentAnalysis.platform_post_id == Comment.platform_post_id,
                      CommentAnalysis.comment_id == Comment.comment_id
                  ))
            .filter(
                CommentAnalysis.toxicity_is_toxic == True,
                CommentAnalysis.toxicity_manual_override.is_(None)
            )
            .order_by(CommentAnalysis.created_at.desc())
            .all()
        )
        
        out = []
        for analysis, comment in rows:
            out.append({
                "platform": analysis.platform,
                "platform_post_id": analysis.platform_post_id,
                "comment_id": analysis.comment_id,
                "username": comment.username,
                "text": comment.text,
                "timestamp": comment.timestamp,
                "like_count": comment.like_count,
                "toxicity_level": analysis.toxicity_level,
                "created_at": analysis.created_at.isoformat() if analysis.created_at else None,
            })
        
        return {"ok": True, "count": len(out), "items": out}
    finally:
        db.close()


class ToxicityReviewUpdate(BaseModel):
    platform: str
    platform_post_id: str
    comment_id: str
    is_toxic: bool

@app.post("/toxicity/review")
def toxicity_review_update(data: ToxicityReviewUpdate):
    """Update the manual toxicity override for a comment"""
    db = SessionLocal()
    try:
        row = (
            db.query(CommentAnalysis)
            .filter(
                CommentAnalysis.platform == data.platform,
                CommentAnalysis.platform_post_id == str(data.platform_post_id),
                CommentAnalysis.comment_id == str(data.comment_id),
            )
            .first()
        )
        
        if not row:
            return {"ok": False, "error": "Comment analysis not found"}
        
        # Set manual override: True = toxic, False = not toxic
        row.toxicity_manual_override = data.is_toxic
        db.commit()
        
        return {"ok": True, "message": "Toxicity status updated"}
    except Exception as e:
        db.rollback()
        return {"ok": False, "error": repr(e)}
    finally:
        db.close()


# ---------------------------------------------------------------------------
# Settings & daily cron
# ---------------------------------------------------------------------------
DAILY_CRON_SETTING_KEY = "daily_cron_enabled"


def _get_app_setting(db: Session, key: str) -> str | None:
    row = db.query(AppSetting).filter(AppSetting.key == key).first()
    return row.value if row else None


def _set_app_setting(db: Session, key: str, value: str) -> None:
    row = db.query(AppSetting).filter(AppSetting.key == key).first()
    if row:
        row.value = value
    else:
        db.add(AppSetting(key=key, value=value))
    db.commit()


@app.get("/settings/daily-cron")
def settings_daily_cron_get():
    """Get whether the daily cron (sync + OpenAI analysis) is enabled."""
    db = SessionLocal()
    try:
        val = _get_app_setting(db, DAILY_CRON_SETTING_KEY)
        enabled = (val or "").strip().lower() in ("1", "true", "yes")
        return JSONResponse(content={"ok": True, "enabled": enabled})
    except Exception as e:
        import traceback
        traceback.print_exc()
        return JSONResponse(status_code=500, content={"ok": False, "error": "settings_load_failed"})
    finally:
        db.close()


class DailyCronSettingUpdate(BaseModel):
    enabled: bool


@app.post("/settings/daily-cron")
def settings_daily_cron_update(data: DailyCronSettingUpdate):
    """Enable or disable the daily cron job (to limit costs)."""
    db = SessionLocal()
    try:
        _set_app_setting(db, DAILY_CRON_SETTING_KEY, "1" if data.enabled else "0")
        return JSONResponse(content={"ok": True, "enabled": data.enabled})
    except Exception as e:
        import traceback
        traceback.print_exc()
        return JSONResponse(status_code=500, content={"ok": False, "error": "settings_update_failed"})
    finally:
        db.close()


async def _run_daily_cron_job() -> dict:
    """Sync comments for all canonical posts, then analyze new comments with OpenAI."""
    from datetime import datetime

    db = SessionLocal()
    fb_page_id = os.getenv("META_DEFAULT_FACEBOOK_PAGE_ID") or "388687450990195"
    model = (os.getenv("OPENAI_MODEL") or "gpt-4o-mini").strip()
    limit_per_post = 200

    canonical_ids = [int(r.id) for r in db.query(CanonicalPost).order_by(CanonicalPost.id).all()]
    if not canonical_ids:
        db.close()
        return {"ok": True, "skipped": True, "reason": "no_canonical_posts", "refreshed": [], "analyzed": []}

    refreshed = []
    analyzed = []

    for canonical_post_id in canonical_ids:
        try:
            refresh_out = await _refresh_canonical(db, canonical_post_id, facebook_page_id=fb_page_id)
            refreshed.append({"canonical_post_id": canonical_post_id, "refresh": refresh_out})
        except Exception as e:
            refreshed.append({"canonical_post_id": canonical_post_id, "error": repr(e)})

        pps = db.query(PlatformPost).filter(PlatformPost.canonical_post_id == canonical_post_id).all()
        for pp in pps:
            try:
                result = _analyze_one_platform_post(
                    db,
                    platform=pp.platform,
                    platform_post_id=pp.platform_post_id,
                    limit=limit_per_post,
                    force=0,
                    model=model,
                )
                analyzed.append(
                    {
                        "canonical_post_id": canonical_post_id,
                        "platform": pp.platform,
                        "platform_post_id": pp.platform_post_id,
                        **result,
                    }
                )
            except Exception as e:
                analyzed.append(
                    {
                        "canonical_post_id": canonical_post_id,
                        "platform": pp.platform,
                        "platform_post_id": pp.platform_post_id,
                        "error": repr(e),
                    }
                )

    db.close()
    return {
        "ok": True,
        "skipped": False,
        "at": datetime.utcnow().isoformat() + "Z",
        "canonical_count": len(canonical_ids),
        "refreshed": refreshed,
        "analyzed": analyzed,
    }


@app.post("/cron/daily")
@app.get("/cron/daily")
async def cron_daily(
    secret: str | None = Query(None),
    x_cron_secret: str | None = Header(None, alias="X-Cron-Secret"),
):
    """
    Run daily job: sync new comments for all canonical posts, then analyze new comments with OpenAI.
    Requires CRON_SECRET env var; pass as query ?secret=... or header X-Cron-Secret.
    If daily cron is disabled in Settings, returns 200 with skipped=true (no cost).
    """
    expected = os.getenv("CRON_SECRET", "").strip()
    token = (secret or x_cron_secret or "").strip()
    if expected and token != expected:
        return JSONResponse(status_code=403, content={"ok": False, "error": "invalid_cron_secret"})

    db = SessionLocal()
    try:
        val = _get_app_setting(db, DAILY_CRON_SETTING_KEY)
        enabled = (val or "").strip().lower() in ("1", "true", "yes")
    finally:
        db.close()

    if not enabled:
        return {
            "ok": True,
            "skipped": True,
            "reason": "daily_cron_disabled",
            "message": "Daily sync/analysis is turned off in Settings. Enable it to run automatically.",
        }

    result = await _run_daily_cron_job()
    return result


@app.post("/ai/analyze")
def ai_analyze_platform_post(
    platform: str,
    platform_post_id: str,
    limit: int = 50,
    force: int = 0,
    model: str | None = None,
):
    db: Session = SessionLocal()
    try:
        model_name = (model or os.getenv("OPENAI_MODEL") or "gpt-4o-mini").strip()
        if not model_name:
            model_name = "gpt-4o-mini"
        q = (
            db.query(Comment)
            .filter(Comment.platform == platform, Comment.platform_post_id == str(platform_post_id))
            .order_by(Comment.created_at.desc())
        )
        comments = q.limit(max(1, min(500, int(limit)))).all()
        post_text = _get_platform_post_caption(platform, platform_post_id)

        analyzed = 0
        skipped = 0
        failed: list[dict] = []

        for c in comments:
            if not force and _analysis_exists(db, platform, platform_post_id, c.comment_id):
                skipped += 1
                continue

            parent_text = _comment_parent_text(platform, platform_post_id, c.parent_comment_id)
            detected_language = "other"
            try:
                analysis = _openai_analyze_comment(
                    platform=platform,
                    comment_text=c.text or "",
                    post_text=post_text,
                    parent_comment_text=parent_text,
                    detected_language=detected_language,
                    model=model_name,
                )
                _store_analysis(
                    db,
                    platform=platform,
                    platform_post_id=platform_post_id,
                    comment_id=c.comment_id,
                    parent_comment_id=c.parent_comment_id,
                    is_reply=c.is_reply,
                    analysis=analysis,
                    model=model_name,
                )
                db.commit()
                analyzed += 1
            except Exception as e:
                db.rollback()
                failed.append({"comment_id": c.comment_id, "error": repr(e)})

        return {
            "ok": True,
            "platform": platform,
            "platform_post_id": str(platform_post_id),
            "requested": len(comments),
            "analyzed": analyzed,
            "skipped": skipped,
            "failed": failed,
            "model": model_name,
            "prompt_version": OPENAI_PROMPT_VERSION,
        }
    finally:
        db.close()


def _safe_load_json_list(v: str | None) -> list:
    if not v:
        return []
    try:
        out = json.loads(v)
        return out if isinstance(out, list) else []
    except Exception:
        return []


def _aggregate_ai_rows(rows: list[CommentAnalysis]) -> dict:
    sentiment = Counter()
    intent = Counter()
    emotion = Counter()
    language = Counter()
    topic = Counter()

    totals = {
        "analyses": 0,
        "toxic": 0,
        "purchase_signal": 0,
        "needs_context": 0,
    }

    latest_created_at = None

    for r in rows:
        totals["analyses"] += 1

        if r.sentiment_label:
            sentiment[str(r.sentiment_label)] += 1

        if r.intent_type:
            intent[str(r.intent_type)] += 1

        if r.emotion_primary:
            emotion[str(r.emotion_primary)] += 1

        if r.language:
            language[str(r.language)] += 1

        # Use manual override if available, otherwise use AI analysis
        is_toxic = r.toxicity_manual_override if r.toxicity_manual_override is not None else r.toxicity_is_toxic
        if is_toxic:
            totals["toxic"] += 1

        if r.purchase_is_signal:
            totals["purchase_signal"] += 1

        if r.needs_context:
            totals["needs_context"] += 1

        for t in _safe_load_json_list(r.topics_json):
            if isinstance(t, dict):
                lbl = t.get("label")
                if isinstance(lbl, str) and lbl:
                    topic[lbl] += 1

        if latest_created_at is None or (r.created_at and r.created_at > latest_created_at):
            latest_created_at = r.created_at

    return {
        "totals": totals,
        "sentiment": dict(sentiment),
        "intent": dict(intent),
        "emotion": dict(emotion),
        "language": dict(language),
        "topics": dict(topic),
        "latest_created_at": latest_created_at,
    }


@app.get("/ai/canonical/summary")
def ai_canonical_summary(canonical_post_id: int):
    db = SessionLocal()
    try:
        pps = (
            db.query(PlatformPost)
            .filter(PlatformPost.canonical_post_id == canonical_post_id)
            .all()
        )

        by_platform = {}
        all_rows: list[CommentAnalysis] = []
        overall_last_analysis_at = None

        for pp in pps:
            platform = pp.platform
            platform_post_id = pp.platform_post_id

            stored_comments = (
                db.query(func.count(Comment.id))
                .filter(Comment.platform == platform, Comment.platform_post_id == platform_post_id)
                .scalar()
            ) or 0

            stored_analyses = (
                db.query(func.count(CommentAnalysis.id))
                .filter(
                    CommentAnalysis.platform == platform,
                    CommentAnalysis.platform_post_id == platform_post_id,
                )
                .scalar()
            ) or 0

            rows = (
                db.query(CommentAnalysis)
                .filter(
                    CommentAnalysis.platform == platform,
                    CommentAnalysis.platform_post_id == platform_post_id,
                )
                .all()
            )

            all_rows.extend(rows)

            last_analysis_at = None
            for r in rows:
                if last_analysis_at is None or (r.created_at and r.created_at > last_analysis_at):
                    last_analysis_at = r.created_at
            if overall_last_analysis_at is None or (last_analysis_at and last_analysis_at > overall_last_analysis_at):
                overall_last_analysis_at = last_analysis_at

            by_platform[platform] = {
                "platform": platform,
                "platform_post_id": platform_post_id,
                "permalink": pp.permalink,
                "created_time": pp.created_time,
                "match_confidence": pp.match_confidence,
                "comments_stored": int(stored_comments),
                "analyses_stored": int(stored_analyses),
                "last_analysis_at": last_analysis_at,
            }

        agg = _aggregate_ai_rows(all_rows)

        agg["last_analysis_at"] = overall_last_analysis_at

        return {
            "ok": True,
            "canonical_post_id": canonical_post_id,
            "platforms": by_platform,
            "aggregate": agg,
        }
    finally:
        db.close()


@app.get("/ai/global/kpis")
def ai_global_kpis():
    """Global KPIs across all stored OpenAI analyses."""
    db = SessionLocal()
    try:
        rows = db.query(CommentAnalysis).all()
        agg = _aggregate_ai_rows(rows)

        last_analysis_at = None
        for r in rows:
            if last_analysis_at is None or (r.created_at and r.created_at > last_analysis_at):
                last_analysis_at = r.created_at
        agg["last_analysis_at"] = last_analysis_at

        return {"ok": True, "aggregate": agg}
    finally:
        db.close()


@app.get("/analytics/overview")
def analytics_overview(days: int = 30):
    """Get analytics overview with time-series data for charts."""
    db = SessionLocal()
    try:
        from datetime import datetime, timedelta
        from collections import defaultdict
        
        cutoff_date = datetime.now() - timedelta(days=days)
        
        # Get all comments with their creation dates
        comments = (
            db.query(Comment)
            .filter(Comment.created_at >= cutoff_date)
            .order_by(Comment.created_at.asc())
            .all()
        )
        
        # Get all analyses and match with comments
        all_analyses = db.query(CommentAnalysis).all()
        
        # Create a map of comments by key for quick lookup
        comment_map = {}
        for comment in comments:
            key = (comment.platform, comment.platform_post_id, comment.comment_id)
            comment_map[key] = comment
        
        # Filter analyses that have matching comments in our date range
        analyses = []
        for analysis in all_analyses:
            key = (analysis.platform, analysis.platform_post_id, analysis.comment_id)
            if key in comment_map:
                analyses.append(analysis)
        
        # Time-series data: group by date
        comments_by_date = defaultdict(int)
        sentiment_by_date = defaultdict(lambda: {"positive": 0, "negative": 0, "neutral": 0})
        platform_by_date = defaultdict(lambda: {"instagram": 0, "facebook": 0})
        
        for comment in comments:
            date_key = comment.created_at.date().isoformat() if comment.created_at else None
            if date_key:
                comments_by_date[date_key] += 1
                platform_by_date[date_key][comment.platform] += 1
        
        for analysis in analyses:
            key = (analysis.platform, analysis.platform_post_id, analysis.comment_id)
            comment = comment_map.get(key)
            if comment and comment.created_at:
                date_key = comment.created_at.date().isoformat()
                if analysis.sentiment_label:
                    sentiment_label = str(analysis.sentiment_label).lower()
                    if sentiment_label in ["positive", "negative", "neutral"]:
                        sentiment_by_date[date_key][sentiment_label] += 1
        
        # Convert to sorted lists for charts
        dates = sorted(set(list(comments_by_date.keys()) + list(sentiment_by_date.keys())))
        
        comments_timeseries = [{"date": d, "count": comments_by_date.get(d, 0)} for d in dates]
        sentiment_timeseries = [
            {
                "date": d,
                "positive": sentiment_by_date.get(d, {}).get("positive", 0),
                "negative": sentiment_by_date.get(d, {}).get("negative", 0),
                "neutral": sentiment_by_date.get(d, {}).get("neutral", 0),
            }
            for d in dates
        ]
        platform_timeseries = [
            {
                "date": d,
                "instagram": platform_by_date.get(d, {}).get("instagram", 0),
                "facebook": platform_by_date.get(d, {}).get("facebook", 0),
            }
            for d in dates
        ]
        
        # Overall stats
        total_comments = len(comments)
        total_analyses = len(analyses)
        analyzed_percentage = (total_analyses / total_comments * 100) if total_comments > 0 else 0
        
        # Sentiment distribution
        sentiment_dist = defaultdict(int)
        for analysis in analyses:
            if analysis.sentiment_label:
                sentiment_dist[str(analysis.sentiment_label).lower()] += 1
        
        # Platform distribution
        platform_dist = defaultdict(int)
        for comment in comments:
            platform_dist[comment.platform] += 1
        
        return {
            "ok": True,
            "period_days": days,
            "summary": {
                "total_comments": total_comments,
                "total_analyses": total_analyses,
                "analyzed_percentage": round(analyzed_percentage, 1),
                "sentiment_distribution": dict(sentiment_dist),
                "platform_distribution": dict(platform_dist),
            },
            "timeseries": {
                "comments": comments_timeseries,
                "sentiment": sentiment_timeseries,
                "platform": platform_timeseries,
            },
        }
    finally:
        db.close()


def _analyze_one_platform_post(
    db: Session,
    *,
    platform: str,
    platform_post_id: str,
    limit: int,
    force: int,
    model: str,
) -> dict:
    q = (
        db.query(Comment)
        .filter(Comment.platform == platform, Comment.platform_post_id == str(platform_post_id))
        .order_by(Comment.created_at.desc())
    )
    comments = q.limit(max(1, min(500, int(limit)))).all()
    post_text = _get_platform_post_caption(platform, platform_post_id)

    analyzed = 0
    skipped = 0
    failed: list[dict] = []

    for c in comments:
        if not force and _analysis_exists(db, platform, platform_post_id, c.comment_id):
            skipped += 1
            continue

        parent_text = _comment_parent_text(platform, platform_post_id, c.parent_comment_id)
        detected_language = "other"
        try:
            analysis = _openai_analyze_comment(
                platform=platform,
                comment_text=c.text or "",
                post_text=post_text,
                parent_comment_text=parent_text,
                detected_language=detected_language,
                model=model,
            )
            _store_analysis(
                db,
                platform=platform,
                platform_post_id=platform_post_id,
                comment_id=c.comment_id,
                parent_comment_id=c.parent_comment_id,
                is_reply=c.is_reply,
                analysis=analysis,
                model=model,
            )
            db.commit()
            analyzed += 1
        except Exception as e:
            db.rollback()
            failed.append({"comment_id": c.comment_id, "error": repr(e)})

    return {
        "platform": platform,
        "platform_post_id": str(platform_post_id),
        "requested": len(comments),
        "analyzed": analyzed,
        "skipped": skipped,
        "failed": failed,
    }


@app.post("/ai/canonical/analyze")
def ai_analyze_canonical_post(
    canonical_post_id: int,
    limit_per_platform_post: int = 200,
    force: int = 0,
    model: str = "gpt-4o-mini",
):
    db: Session = SessionLocal()
    try:
        pps = (
            db.query(PlatformPost)
            .filter(PlatformPost.canonical_post_id == canonical_post_id)
            .all()
        )

        results = []
        for pp in pps:
            results.append(
                _analyze_one_platform_post(
                    db,
                    platform=pp.platform,
                    platform_post_id=pp.platform_post_id,
                    limit=limit_per_platform_post,
                    force=force,
                    model=model,
                )
            )

        return {
            "ok": True,
            "canonical_post_id": canonical_post_id,
            "limit_per_platform_post": int(limit_per_platform_post),
            "force": int(force),
            "model": model,
            "prompt_version": OPENAI_PROMPT_VERSION,
            "results": results,
        }
    finally:
        db.close()


@app.get("/meta/ig/comments/verify")
async def meta_ig_comments_verify(media_id: str):
    media = await _graph_get(f"/{media_id}", params={"fields": "id,comments_count"})
    if isinstance(media, dict) and media.get("ok") is False:
        return media

    db = SessionLocal()
    try:
        stored_total = (
            db.query(Comment)
            .filter(
                Comment.platform == "instagram",
                Comment.platform_post_id == str(media_id),
            )
            .count()
        )
        stored_top = (
            db.query(Comment)
            .filter(
                Comment.platform == "instagram",
                Comment.platform_post_id == str(media_id),
                Comment.is_reply == 0,
            )
            .count()
        )
        stored_replies = stored_total - stored_top
    finally:
        db.close()

    return {
        "media_id": media_id,
        "api_comments_count": media.get("comments_count"),
        "stored_total": stored_total,
        "stored_top_level": stored_top,
        "stored_replies": stored_replies,
    }


@app.get("/meta/ig/profile")
def meta_ig_profile():
    return {
        "ig_user_id": "17841467984361923",
        "page_id": "388687450990195",
    }


@app.get("/ingest/instagram/resolve")
async def ingest_instagram_resolve(url: str):
    shortcode = _extract_ig_shortcode(url)
    if not shortcode:
        return {"ok": False, "error": "cannot_parse_instagram_shortcode"}

    ig_user_id = "17841467984361923"

    fields = "id,shortcode,caption,media_type,permalink,timestamp,like_count,comments_count"
    data = await _graph_get(f"/{ig_user_id}/media", params={"fields": fields, "limit": "50"})
    if isinstance(data, dict) and data.get("ok") is False:
        return data

    items = data.get("data", [])
    match = None
    for it in items:
        if str(it.get("shortcode")) == shortcode:
            match = it
            break

    if not match:
        return {
            "ok": False,
            "error": "media_not_found_in_recent_window",
            "shortcode": shortcode,
            "hint": "Increase limit or implement pagination if you need older posts.",
        }

    return {"ok": True, "shortcode": shortcode, "media": match}


@app.get("/ingest/facebook/feed")
async def ingest_facebook_feed(page_id: str, limit: int = 25):
    page_token = _get_page_token(page_id)
    if not page_token:
        return {"ok": False, "error": "missing_page_token", "hint": "Call POST /meta/page_token/refresh?page_id=... first."}

    fields = "id,message,created_time,permalink_url,shares"
    data = await _graph_get(f"/{page_id}/feed", params={"fields": fields, "limit": str(limit)}, access_token=page_token)
    return data


@app.get("/ingest/facebook/match")
async def ingest_facebook_match(page_id: str, ig_caption: str = "", ig_timestamp: str = ""):
    ig_dt = _parse_iso(ig_timestamp)
    page_token = _get_page_token(page_id)
    if not page_token:
        return {"ok": False, "error": "missing_page_token"}

    fields = "id,message,created_time,permalink_url,shares"
    limit = 50
    max_scan = 300
    scanned = 0

    best = None
    best_score = 0.0
    best_debug = []

    async def score_one(p: dict) -> float:
        msg = p.get("message") or ""
        score_text = _similarity(ig_caption, msg)

        score_time = 0.0
        if ig_dt:
            fb_dt = _parse_iso(p.get("created_time") or "")
            if fb_dt:
                diff = abs((fb_dt - ig_dt).total_seconds())
                if diff <= 86400:
                    score_time = 1.0 - (diff / 86400.0)

        return (0.85 * score_text) + (0.15 * score_time)

    first = await _graph_get(
        f"/{page_id}/feed",
        params={"fields": fields, "limit": str(limit)},
        access_token=page_token,
    )
    if isinstance(first, dict) and first.get("ok") is False:
        return first

    page = first
    while True:
        items = page.get("data", []) or []
        if not items:
            break

        for p in items:
            scanned += 1
            s = await score_one(p)
            if s > best_score:
                best_score = s
                best = p

            best_debug.append(
                {
                    "id": p.get("id"),
                    "created_time": p.get("created_time"),
                    "score": int(max(0.0, min(1.0, s)) * 100),
                    "message_preview": (p.get("message") or "")[:160],
                }
            )

            if scanned >= max_scan:
                break

        if scanned >= max_scan:
            break

        if ig_dt:
            last_dt = _parse_iso(items[-1].get("created_time") or "")
            if last_dt:
                age_days = abs((last_dt - ig_dt).total_seconds()) / 86400.0
                if age_days > 7.0:
                    break

        next_url = page.get("paging", {}).get("next")
        if not next_url:
            break

        page = await _graph_get_url(next_url)
        if isinstance(page, dict) and page.get("ok") is False:
            return page

    confidence = int(max(0.0, min(1.0, best_score)) * 100)
    best_debug_sorted = sorted(best_debug, key=lambda x: x["score"], reverse=True)[:8]

    if not best:
        return {"ok": False, "error": "no_candidate", "scanned": scanned, "top": best_debug_sorted}

    return {
        "ok": True,
        "confidence": confidence,
        "facebook_post": best,
        "scanned": scanned,
        "top": best_debug_sorted,
    }


@app.post("/canonical/create_from_instagram_url")
async def canonical_create_from_instagram_url(instagram_url: str, facebook_page_id: str = "388687450990195"):
    try:
        resolved = await ingest_instagram_resolve(instagram_url)
        if isinstance(resolved, dict) and resolved.get("ok") is False:
            return resolved

        media = resolved["media"]
        ig_id = str(media.get("id"))
        db = SessionLocal()
        try:
            existing_canonical_id = _find_existing_canonical_id_for_platform_post(db, "instagram", ig_id)
            if existing_canonical_id is not None:
                return {
                    "ok": True,
                    "canonical_post_id": existing_canonical_id,
                    "instagram_post_id": ig_id,
                    "deduped": True,
                }
        finally:
            db.close()

        db = SessionLocal()
        try:
            existing = (
                db.query(PlatformPost)
                .filter(
                    PlatformPost.platform == "instagram",
                    PlatformPost.platform_post_id == ig_id,
                )
                .first()
            )
            if existing:
                canonical_id = int(existing.canonical_post_id)
                return {
                    "ok": True,
                    "canonical_post_id": canonical_id,
                    "instagram_post_id": ig_id,
                    "facebook_post_id": None,
                    "match_confidence": existing.match_confidence,
                    "deduped": True,
                }
        finally:
            db.close()

        ig_caption = media.get("caption") or ""
        ig_ts = media.get("timestamp") or ""
        ig_permalink = media.get("permalink") or ""

        ig_metrics = None
        try:
            ig_metrics = await _meta_ig_metrics(media_id=ig_id)
        except Exception:
            ig_metrics = None

        ig_metrics = None
        try:
            ig_metrics = await _meta_ig_metrics(media_id=ig_id)
        except Exception:
            ig_metrics = None

        match = await ingest_facebook_match(facebook_page_id, ig_caption=ig_caption, ig_timestamp=ig_ts)
        fb_post = None
        confidence = None
        if isinstance(match, dict) and match.get("ok") is True:
            fb_post = match.get("facebook_post")
            confidence = match.get("confidence")

        db = SessionLocal()
        try:
            canon = CanonicalPost(title=(ig_caption[:120] if ig_caption else None))
            db.add(canon)
            db.commit()
            db.refresh(canon)
            canonical_id = int(canon.id)

            ig_pp = PlatformPost(
                canonical_post_id=canonical_id,
                platform="instagram",
                platform_post_id=ig_id,
                permalink=ig_permalink,
                caption=ig_caption,
                created_time=ig_ts,
                comment_count_api=(ig_metrics.get("comment_count") if isinstance(ig_metrics, dict) and ig_metrics.get("ok") else media.get("comments_count")),
                reaction_count_api=(ig_metrics.get("reaction_count") if isinstance(ig_metrics, dict) and ig_metrics.get("ok") else media.get("like_count")),
                share_count_api=(ig_metrics.get("share_count") if isinstance(ig_metrics, dict) and ig_metrics.get("ok") else None),
            )
            db.add(ig_pp)

            try:
                await meta_ig_comments_sync(media_id=str(ig_id))
            except Exception:
                pass

            if fb_post:
                fb_metrics = None
                try:
                    fb_metrics = await _meta_fb_metrics(page_id=facebook_page_id, post_id=str(fb_post.get("id")))
                except Exception:
                    fb_metrics = None

                shares_obj = fb_post.get("shares") or {}
                share_count = shares_obj.get("count")
                if isinstance(fb_metrics, dict) and fb_metrics.get("ok") is True and fb_metrics.get("share_count") is not None:
                    share_count = fb_metrics.get("share_count")

                fb_pp = PlatformPost(
                    canonical_post_id=canonical_id,
                    platform="facebook",
                    platform_post_id=str(fb_post.get("id")),
                    permalink=fb_post.get("permalink_url"),
                    caption=fb_post.get("message") or "",
                    created_time=fb_post.get("created_time"),
                    comment_count_api=(fb_metrics.get("comment_count") if isinstance(fb_metrics, dict) and fb_metrics.get("ok") else None),
                    reaction_count_api=(fb_metrics.get("reaction_count") if isinstance(fb_metrics, dict) and fb_metrics.get("ok") else None),
                    share_count_api=share_count,
                    match_confidence=confidence,
                )
                db.add(fb_pp)

                try:
                    await meta_fb_comments_sync(page_id=facebook_page_id, post_id=str(fb_post.get("id")))
                except Exception:
                    pass

            try:
                db.commit()
            except IntegrityError:
                db.rollback()
                existing_canonical_id = _find_existing_canonical_id_for_platform_post(db, "instagram", ig_id)
                if existing_canonical_id is not None:
                    return {
                        "ok": True,
                        "canonical_post_id": existing_canonical_id,
                        "instagram_post_id": ig_id,
                        "deduped": True,
                    }
                raise
        finally:
            db.close()

        return {
            "ok": True,
            "canonical_post_id": canonical_id,
            "instagram_post_id": ig_id,
            "facebook_post_id": (str(fb_post.get("id")) if fb_post else None),
            "match_confidence": confidence,
        }

    except Exception as e:
        return {
            "ok": False,
            "error": "server_exception",
            "detail": repr(e),
        }


@app.get("/canonical/list")
def canonical_list():
    db = SessionLocal()
    try:
        canon_rows = db.query(CanonicalPost).order_by(CanonicalPost.created_at.desc()).all()
        out = []
        for c in canon_rows:
            plats = db.query(PlatformPost).filter(PlatformPost.canonical_post_id == c.id).all()
            out.append(
                {
                    "id": c.id,
                    "title": c.title,
                    "created_at": c.created_at,
                    "platform_posts": [
                        {
                            "platform": p.platform,
                            "platform_post_id": p.platform_post_id,
                            "permalink": p.permalink,
                            "created_time": p.created_time,
                            "comment_count_api": p.comment_count_api,
                            "reaction_count_api": p.reaction_count_api,
                            "share_count_api": p.share_count_api,
                            "match_confidence": p.match_confidence,
                        }
                        for p in plats
                    ],
                }
            )
        return out
    finally:
        db.close()


@app.post("/canonical/refresh")
async def canonical_refresh(canonical_post_id: int, facebook_page_id: str | None = None):
    """Refresh comments and metrics for all platform posts attached to a canonical post.

    If facebook_page_id is not provided, the backend will try META_DEFAULT_FACEBOOK_PAGE_ID,
    then fall back to the historical default used elsewhere in this codebase.
    """
    fb_page_id = facebook_page_id or os.getenv("META_DEFAULT_FACEBOOK_PAGE_ID") or "388687450990195"
    db: Session = SessionLocal()
    try:
        out = await _refresh_canonical(db, int(canonical_post_id), facebook_page_id=fb_page_id)
        return {"ok": True, "refresh": out}
    except Exception as e:
        return {"ok": False, "error": "refresh_failed", "detail": repr(e)}
    finally:
        db.close()


@app.post("/canonical/create_from_instagram_media_id")
async def canonical_create_from_instagram_media_id(media_id: str, facebook_page_id: str = "388687450990195"):
    try:
        fields = "id,caption,media_type,permalink,timestamp,like_count,comments_count"
        media = await _graph_get(f"/{media_id}", params={"fields": fields})
        if isinstance(media, dict) and media.get("ok") is False:
            return media

        ig_id = str(media.get("id"))
        db = SessionLocal()
        try:
            existing_canonical_id = _find_existing_canonical_id_for_platform_post(db, "instagram", ig_id)
            if existing_canonical_id is not None:
                refresh = await _refresh_canonical(db, existing_canonical_id, facebook_page_id=facebook_page_id)
            if existing_canonical_id is not None:
                return {
                    "ok": True,
                    "canonical_post_id": existing_canonical_id,
                    "instagram_post_id": ig_id,
                    "deduped": True,
                    "refreshed": True,
                    "refresh": refresh,
                }
        finally:
            db.close()

        ig_caption = media.get("caption") or ""
        ig_ts = media.get("timestamp") or ""
        ig_permalink = media.get("permalink") or ""

        ig_metrics = None
        try:
            ig_metrics = await _meta_ig_metrics(media_id=ig_id)
        except Exception:
            ig_metrics = None

        match = await ingest_facebook_match(facebook_page_id, ig_caption=ig_caption, ig_timestamp=ig_ts)
        fb_post = None
        confidence = None
        if isinstance(match, dict) and match.get("ok") is True:
            fb_post = match.get("facebook_post")
            confidence = match.get("confidence")
            fb_debug = match.get("top") if isinstance(match, dict) else None
            fb_scanned = match.get("scanned") if isinstance(match, dict) else None

        db = SessionLocal()
        try:
            canon = CanonicalPost(title=(ig_caption[:120] if ig_caption else None))
            db.add(canon)
            db.commit()
            db.refresh(canon)
            canonical_id = int(canon.id)

            ig_pp = PlatformPost(
                canonical_post_id=canonical_id,
                platform="instagram",
                platform_post_id=ig_id,
                permalink=ig_permalink,
                caption=ig_caption,
                created_time=ig_ts,
                comment_count_api=(ig_metrics.get("comment_count") if isinstance(ig_metrics, dict) and ig_metrics.get("ok") else media.get("comments_count")),
                reaction_count_api=(ig_metrics.get("reaction_count") if isinstance(ig_metrics, dict) and ig_metrics.get("ok") else media.get("like_count")),
                share_count_api=(ig_metrics.get("share_count") if isinstance(ig_metrics, dict) and ig_metrics.get("ok") else None),
            )
            db.add(ig_pp)

            try:
                await meta_ig_comments_sync(media_id=str(ig_id))
            except Exception:
                pass

            if fb_post:
                fb_metrics = None
                try:
                    fb_metrics = await _meta_fb_metrics(page_id=facebook_page_id, post_id=str(fb_post.get("id")))
                except Exception:
                    fb_metrics = None

                shares_obj = fb_post.get("shares") or {}
                share_count = shares_obj.get("count")
                if isinstance(fb_metrics, dict) and fb_metrics.get("ok") is True and fb_metrics.get("share_count") is not None:
                    share_count = fb_metrics.get("share_count")

                fb_pp = PlatformPost(
                    canonical_post_id=canonical_id,
                    platform="facebook",
                    platform_post_id=str(fb_post.get("id")),
                    permalink=fb_post.get("permalink_url"),
                    caption=fb_post.get("message") or "",
                    created_time=fb_post.get("created_time"),
                    comment_count_api=(fb_metrics.get("comment_count") if isinstance(fb_metrics, dict) and fb_metrics.get("ok") else None),
                    reaction_count_api=(fb_metrics.get("reaction_count") if isinstance(fb_metrics, dict) and fb_metrics.get("ok") else None),
                    share_count_api=share_count,
                    match_confidence=confidence,
                )
                await meta_fb_comments_sync(page_id=facebook_page_id, post_id=str(fb_post.get("id")))
                db.add(fb_pp)

            try:
                db.commit()
            except IntegrityError:
                db.rollback()
                existing_canonical_id = _find_existing_canonical_id_for_platform_post(db, "instagram", ig_id)
                if existing_canonical_id is not None:
                    return {
                        "ok": True,
                        "canonical_post_id": existing_canonical_id,
                        "instagram_post_id": ig_id,
                        "deduped": True,
                    }
                raise

        finally:
            db.close()

        return {
            "ok": True,
            "canonical_post_id": canonical_id,
            "instagram_post_id": ig_id,
            "facebook_post_id": (str(fb_post.get("id")) if fb_post else None),
            "match_confidence": confidence,
            "facebook_debug_top": fb_debug,
            "facebook_scanned": fb_scanned,

        }

    except Exception as e:
        return {
            "ok": False,
            "error": "server_exception",
            "detail": repr(e),
        }

@app.post("/meta/page_token/refresh")
async def meta_page_token_refresh(page_id: str):
    user_token = _get_meta_token()
    if not user_token:
        return {"ok": False, "error": "meta_not_connected"}

    data = await _graph_get("/me/accounts", params={"fields": "id,name,access_token"})
    if isinstance(data, dict) and data.get("ok") is False:
        return data

    pages = data.get("data", []) or []
    match = None
    for p in pages:
        if str(p.get("id")) == str(page_id):
            match = p
            break

    if not match:
        return {
            "ok": False,
            "error": "page_not_returned_by_me_accounts",
            "hint": "Reconnect Meta with the Facebook account that is admin of the Page, and ensure the Page is selected during authorization.",
            "returned_pages": [{"id": x.get("id"), "name": x.get("name")} for x in pages],
        }

    page_token = match.get("access_token")
    if not page_token:
        return {"ok": False, "error": "missing_page_access_token"}

    _save_page_token(page_id=str(page_id), access_token=str(page_token))
    return {"ok": True, "page_id": str(page_id), "name": match.get("name")}

@app.post("/meta/page_token/refresh_alt")
async def meta_page_token_refresh_alt(page_id: str):
    page = await _graph_get(f"/{page_id}", params={"fields": "id,name,access_token"})
    if isinstance(page, dict) and page.get("ok") is False:
        return page

    page_token = page.get("access_token")
    if not page_token:
        return {
            "ok": False,
            "error": "missing_page_access_token_on_page_object",
            "page": {"id": page.get("id"), "name": page.get("name")},
        }

    _save_page_token(page_id=str(page_id), access_token=str(page_token))
    return {"ok": True, "page_id": str(page_id), "name": page.get("name")}

@app.get("/meta/fb/feed_debug")
async def meta_fb_feed_debug(page_id: str, limit: int = 10):
    page_token = _get_page_token(page_id)
    if not page_token:
        return {"ok": False, "error": "missing_page_token"}

    fields = "id,message,created_time,permalink_url,shares"
    data = await _graph_get(
        f"/{page_id}/feed",
        params={"fields": fields, "limit": str(limit)},
        access_token=page_token,
    )
    return data

@app.post("/meta/fb/comments/sync")
async def meta_fb_comments_sync(page_id: str, post_id: str):
    page_token = _get_page_token(page_id)
    if not page_token:
        return {"ok": False, "error": "missing_page_token"}

    # If post_id is a pfbid, we can't use it directly - return error
    if post_id.startswith("pfbid"):
        return {
            "ok": False,
            "error": "pfbid_not_supported",
            "detail": "Facebook Graph API does not support pfbid format for comments endpoint. Please use a numeric post ID.",
            "hint": "Update the Facebook post using the 'Edit' button to convert pfbid to numeric ID.",
        }

    # Use the post_id directly (should be numeric)
    actual_post_id = str(post_id)
    
    # Safety check: make sure we're not accidentally using page_id as post_id
    if actual_post_id == str(page_id):
        return {
            "ok": False,
            "error": "invalid_post_id",
            "detail": f"Post ID '{actual_post_id}' is the same as page ID '{page_id}'. This would try to access comments on a Page node, which is not supported.",
            "hint": "Make sure you're using the post ID, not the page ID. Post IDs are typically longer numeric strings.",
        }

    try:
        data = await _graph_paginate_with_token(
            f"/{actual_post_id}/comments",
            params={"fields": "id,message,from,created_time,like_count,comment_count", "limit": "100"},
            access_token=page_token,
        )
        if isinstance(data, dict) and data.get("ok") is False:
            # Provide a clearer hint for the most common Meta error.
            err = (data.get("error_response") or {}).get("error") or {}
            if (
                int(data.get("status_code") or 0) == 403
                and int(err.get("code") or 0) == 200
                and "Missing Permissions" in str(err.get("message") or "")
            ):
                data["hint"] = (
                    "The Page access token used by the app does not include the required permissions to read Page post comments. "
                    "Reconnect Meta and grant pages_read_engagement and pages_read_user_content, then refresh the Page token."
                )
                data["next_steps"] = [
                    "Open /meta/debug/token to verify granted scopes on the user token",
                    "Call POST /meta/page_token/refresh?page_id=<YOUR_PAGE_ID> to refresh the Page token",
                    "Retry the refresh from the Dashboard",
                ]
            return data

        items = data.get("data", []) or []

        rows = []
        for c in items:
            author = c.get("from") or {}
            rows.append(
                {
                    "comment_id": str(c.get("id")),
                    "parent_comment_id": None,
                    "is_reply": 0,
                    "username": author.get("name"),
                    "text": c.get("message"),
                    "timestamp": c.get("created_time"),
                    "like_count": c.get("like_count"),
                }
            )

        db = SessionLocal()
        try:
            # Delete comments for both the original post_id and actual_post_id
            # (in case we're updating from pfbid to numeric)
            db.query(Comment).filter(
                Comment.platform == "facebook",
                Comment.platform_post_id.in_([str(post_id), str(actual_post_id)]),
            ).delete()

            for row in rows:
                db.add(
                    Comment(
                        platform="facebook",
                        platform_post_id=str(actual_post_id),  # Use actual_post_id for consistency
                        comment_id=row["comment_id"],
                        parent_comment_id=None,
                        is_reply=0,
                        username=row["username"],
                        text=row["text"],
                        timestamp=row["timestamp"],
                        like_count=row["like_count"],
                    )
                )
            db.commit()
        finally:
            db.close()

        # Calculate breakdown (Facebook currently only fetches top-level comments)
        saved_total = len(rows)
        saved_top_level = saved_total  # All comments are top-level (no replies fetched yet)
        saved_replies = 0  # Facebook API doesn't return replies in the same way as Instagram
        
        return {
            "ok": True,
            "post_id": post_id,
            "actual_post_id": actual_post_id if actual_post_id != post_id else None,
            "saved_total": saved_total,
            "saved_top_level": saved_top_level,
            "saved_replies": saved_replies,
        }

    except Exception as e:
        return {"ok": False, "error": "server_exception", "detail": repr(e)}

@app.get("/canonical/debug")
def canonical_debug(canonical_post_id: int):
    """Debug endpoint to see what's stored in the database for a canonical post."""
    db = SessionLocal()
    try:
        canon = db.query(CanonicalPost).filter(CanonicalPost.id == canonical_post_id).first()
        if not canon:
            return {"ok": False, "error": "canonical_post_not_found"}
        
        pps = (
            db.query(PlatformPost)
            .filter(PlatformPost.canonical_post_id == canonical_post_id)
            .all()
        )
        
        return {
            "ok": True,
            "canonical_post_id": canonical_post_id,
            "canonical_title": canon.title,
            "platform_posts": [
                {
                    "platform": pp.platform,
                    "platform_post_id": pp.platform_post_id,
                    "permalink": pp.permalink,
                    "caption": pp.caption[:100] if pp.caption else None,
                    "match_confidence": pp.match_confidence,
                    "is_pfbid": str(pp.platform_post_id).startswith("pfbid"),
                }
                for pp in pps
            ],
        }
    finally:
        db.close()


@app.get("/canonical/summary")
async def canonical_summary(canonical_post_id: int):
    db = SessionLocal()
    try:
        pps = (
            db.query(PlatformPost)
            .filter(PlatformPost.canonical_post_id == canonical_post_id)
            .all()
        )

        by_platform = {}
        totals = {
            "comments_api": 0,
            "reactions_api": 0,
            "shares_api": 0,
            "views_api": 0,
            "comments_stored": 0,
        }

        for pp in pps:
            platform = pp.platform
            platform_post_id = pp.platform_post_id

            stored_count = (
                db.query(func.count(Comment.id))
                .filter(
                    Comment.platform == platform,
                    Comment.platform_post_id == platform_post_id,
                )
                .scalar()
            ) or 0

            row = {
                "platform": platform,
                "platform_post_id": platform_post_id,
                "permalink": pp.permalink,
                "created_time": pp.created_time,
                "match_confidence": pp.match_confidence,
                "comment_count_api": pp.comment_count_api or 0,
                "reaction_count_api": pp.reaction_count_api or 0,
                "share_count_api": pp.share_count_api or 0,
                "view_count_api": pp.view_count_api or 0,
                "comments_stored": int(stored_count),
            }

            by_platform[platform] = row

            totals["comments_api"] += row["comment_count_api"]
            totals["reactions_api"] += row["reaction_count_api"]
            totals["shares_api"] += row["share_count_api"]
            totals["views_api"] += row["view_count_api"]
            totals["comments_stored"] += row["comments_stored"]

        return {
            "ok": True,
            "canonical_post_id": canonical_post_id,
            "platforms": by_platform,
            "totals": totals,
        }
    finally:
        db.close()


@app.post("/canonical/update_facebook_post")
async def canonical_update_facebook_post(
    canonical_post_id: int = Query(...),
    facebook_post_id: str | None = Query(None),
    facebook_url: str | None = Query(None),
    facebook_page_id: str | None = Query(None),
):
    """Update or set the Facebook post ID for a canonical post.
    
    Either facebook_post_id or facebook_url must be provided.
    If facebook_url is provided, the post ID will be extracted from it.
    """
    db = SessionLocal()
    try:
        # Verify canonical post exists
        canon = db.query(CanonicalPost).filter(CanonicalPost.id == canonical_post_id).first()
        if not canon:
            return {"ok": False, "error": "canonical_post_not_found"}
        
        # Extract post ID from URL if provided
        if facebook_url and not facebook_post_id:
            facebook_post_id = _extract_fb_post_id(facebook_url)
            if not facebook_post_id:
                return {"ok": False, "error": "cannot_extract_post_id_from_url", "url": facebook_url}
        
        if not facebook_post_id:
            return {"ok": False, "error": "missing_facebook_post_id_or_url"}
        
        # Safety check: make sure we didn't extract the page ID instead of post ID
        # Page IDs are typically shorter and match the default page ID
        default_page_id = os.getenv("META_DEFAULT_FACEBOOK_PAGE_ID") or "388687450990195"
        if str(facebook_post_id) == str(default_page_id):
            return {
                "ok": False,
                "error": "extracted_page_id_instead_of_post_id",
                "detail": f"The extracted ID '{facebook_post_id}' appears to be the page ID, not a post ID. This often happens when the URL format is not recognized.",
                "hint": "Please provide the full Facebook post URL (e.g., https://www.facebook.com/motocrossthegame/posts/pfbid...) or the numeric post ID directly.",
            }
        
        # Get page ID (use provided or default)
        page_id = facebook_page_id or os.getenv("META_DEFAULT_FACEBOOK_PAGE_ID") or "388687450990195"
        
        # Convert pfbid to numeric ID if needed
        actual_fb_post_id = str(facebook_post_id)
        page_token = _get_page_token(page_id)
        
        if facebook_post_id.startswith("pfbid") and page_token:
            # Try multiple approaches to convert pfbid to numeric ID
            conversion_success = False
            pages_checked = 1
            
            # Approach 1: Try to query the post directly using the pfbid with ?id= parameter
            # This sometimes works for newer posts
            try:
                direct_query = await _graph_get_with_token(
                    f"/{facebook_post_id}",
                    params={"fields": "id"},
                    access_token=page_token,
                )
                if isinstance(direct_query, dict) and direct_query.get("ok") is not False:
                    numeric_id = direct_query.get("id")
                    if numeric_id and not str(numeric_id).startswith("pfbid"):
                        actual_fb_post_id = str(numeric_id)
                        conversion_success = True
            except Exception:
                pass
            
            # Approach 2: If direct query didn't work, search the page feed
            if not conversion_success:
                try:
                    # Search the page feed for recent posts - use /posts endpoint (not /feed)
                    fields = "id,permalink_url"
                    feed_data = await _graph_get_with_token(
                        f"/{page_id}/posts",
                        params={"fields": fields, "limit": "100"},
                        access_token=page_token,
                    )
                    
                    if isinstance(feed_data, dict) and feed_data.get("ok") is not False:
                        posts = feed_data.get("data", [])
                        # Try to find a post that matches our pfbid in the permalink
                        for post in posts:
                            post_id_from_feed = str(post.get("id", ""))
                            post_permalink = (post.get("permalink_url") or "").lower().rstrip('/')
                            
                            # Extract just the post ID part if it's in format page_id_post_id
                            # Facebook API returns IDs like "388687450990195_122188912952436895"
                            # But permalinks use just "122188912952436895"
                            post_id_part = post_id_from_feed
                            if "_" in post_id_from_feed:
                                post_id_part = post_id_from_feed.split("_", 1)[1]
                            
                            # Check multiple matching strategies
                            matches = False
                            if post_permalink:
                                # Check if pfbid is in permalink
                                if facebook_post_id in post_permalink:
                                    matches = True
                                # Check if post ID part appears in permalink
                                elif post_id_part and (
                                    f"/posts/{post_id_part}" in post_permalink or
                                    f"posts/{post_id_part}" in post_permalink or
                                    post_id_part in post_permalink
                                ):
                                    matches = True
                            
                            if matches:
                                actual_fb_post_id = post_id_from_feed
                                conversion_success = True
                                break
                        
                        # If not found in first 100, try paginating
                        if not conversion_success:
                            next_url = feed_data.get("paging", {}).get("next")
                            while next_url and pages_checked < 10:  # Check up to 10 pages (1000 posts)
                                try:
                                    page_data = await _graph_get_url(next_url)
                                    if isinstance(page_data, dict) and page_data.get("ok") is not False:
                                        more_posts = page_data.get("data", [])
                                        for post in more_posts:
                                            post_id_from_feed = str(post.get("id", ""))
                                            post_permalink = (post.get("permalink_url") or "").lower().rstrip('/')
                                            
                                            # Extract post ID part
                                            post_id_part = post_id_from_feed
                                            if "_" in post_id_from_feed:
                                                post_id_part = post_id_from_feed.split("_", 1)[1]
                                            
                                            # Check matching strategies
                                            matches = False
                                            if post_permalink:
                                                if facebook_post_id in post_permalink:
                                                    matches = True
                                                elif post_id_part and (
                                                    f"/posts/{post_id_part}" in post_permalink or
                                                    f"posts/{post_id_part}" in post_permalink or
                                                    post_id_part in post_permalink
                                                ):
                                                    matches = True
                                            
                                            if matches:
                                                actual_fb_post_id = post_id_from_feed
                                                conversion_success = True
                                                break
                                        if conversion_success:
                                            break
                                        next_url = page_data.get("paging", {}).get("next")
                                        pages_checked += 1
                                    else:
                                        break
                                except Exception:
                                    break
                    
                    # If still not converted, try using the URL's permalink to search more thoroughly
                    if not conversion_success and facebook_url:
                        try:
                            # Use pagination to search through more posts
                            # Normalize the URL for comparison - extract just the pfbid part
                            url_pfbid = facebook_post_id
                            normalized_url = facebook_url.lower().rstrip('/').rstrip('?').rstrip('#')
                            
                            # Search through paginated feed
                            search_feed = await _graph_paginate_with_token(
                                f"/{page_id}/posts",
                                params={"fields": "id,permalink_url", "limit": "100"},
                                access_token=page_token,
                            )
                            
                            if isinstance(search_feed, dict) and search_feed.get("ok") is True:
                                all_posts = search_feed.get("data", [])
                                for post in all_posts:
                                    post_permalink = (post.get("permalink_url") or "").lower().rstrip('/')
                                    post_id_from_feed = str(post.get("id", ""))
                                    
                                    # Extract post ID part
                                    post_id_part = post_id_from_feed
                                    if "_" in post_id_from_feed:
                                        post_id_part = post_id_from_feed.split("_", 1)[1]
                                    
                                    # Check multiple matching strategies
                                    matches = False
                                    if post_permalink:
                                        # Check if pfbid is in permalink
                                        if url_pfbid in post_permalink:
                                            matches = True
                                        # Check if URLs match
                                        elif normalized_url in post_permalink or post_permalink in normalized_url:
                                            matches = True
                                        # Check if post ID part appears in permalink
                                        elif post_id_part and (
                                            f"/posts/{post_id_part}" in post_permalink or
                                            f"posts/{post_id_part}" in post_permalink or
                                            post_id_part in post_permalink
                                        ):
                                            matches = True
                                        # Also check if the URL path matches
                                        elif facebook_url.split('/posts/')[-1].split('?')[0].split('#')[0] in post_permalink:
                                            matches = True
                                    
                                    if matches and post_id_from_feed and not post_id_from_feed.startswith("pfbid"):
                                        actual_fb_post_id = post_id_from_feed
                                        conversion_success = True
                                        break
                        except Exception as e:
                            # Log but don't fail - we'll return error below
                            print(f"Extended search failed: {repr(e)}")
                            pass
                    
                    # If still not converted, return error with helpful message
                    if not conversion_success:
                        return {
                            "ok": False,
                            "error": "pfbid_conversion_failed",
                            "detail": f"Could not find post with pfbid '{facebook_post_id}' in the page's recent posts (searched up to {pages_checked * 100} posts). The post may be older than the search window.",
                            "hint": "To fix this:\n1. Open the Facebook post in your browser\n2. Right-click and 'Inspect Element'\n3. Look for the post ID in the page source or network requests\n4. Use the 'Fix pfbid' button and enter the numeric ID directly\n\nAlternatively, the post may be too old to appear in recent posts.",
                            "pfbid_provided": facebook_post_id,
                            "searched_posts": pages_checked * 100,
                        }
                except Exception as e:
                    return {
                        "ok": False,
                        "error": "pfbid_conversion_failed",
                        "detail": repr(e),
                        "hint": "Could not search page feed to convert pfbid. Please use the 'Fix pfbid' button and provide the numeric post ID directly.",
                    }
        
        # Fetch Facebook post details using the numeric ID
        fb_metrics = None
        fb_post_data = None
        try:
            if page_token:
                # First try to get metrics (requires numeric ID)
                if not actual_fb_post_id.startswith("pfbid"):
                    fb_metrics = await _meta_fb_metrics(page_id=page_id, post_id=str(actual_fb_post_id))
                
                # Fetch full post details
                if not actual_fb_post_id.startswith("pfbid"):
                    fields = "id,message,created_time,permalink_url,shares"
                    fb_post_data = await _graph_get_with_token(
                        f"/{actual_fb_post_id}",
                        params={"fields": fields},
                        access_token=page_token,
                    )
        except Exception as e:
            return {
                "ok": False,
                "error": "failed_to_fetch_facebook_post",
                "detail": repr(e),
                "hint": "Make sure the post ID is correct and the page token has proper permissions.",
            }
        
        if isinstance(fb_post_data, dict) and fb_post_data.get("ok") is False:
            return {
                "ok": False,
                "error": "facebook_post_not_found",
                "detail": fb_post_data.get("error_response"),
            }
        
        # Find existing Facebook platform post
        existing_fb_pp = (
            db.query(PlatformPost)
            .filter(
                PlatformPost.canonical_post_id == canonical_post_id,
                PlatformPost.platform == "facebook",
            )
            .first()
        )
        
        # Prepare post data
        permalink = None
        caption = None
        created_time = None
        share_count = None
        
        # Use the provided URL as permalink if it's a valid Facebook URL
        if facebook_url and "facebook.com" in facebook_url:
            permalink = facebook_url
        
        if fb_post_data and isinstance(fb_post_data, dict) and fb_post_data.get("ok") is not False:
            # Override with API permalink if available (but prefer user-provided URL if it's valid)
            api_permalink = fb_post_data.get("permalink_url")
            if api_permalink and not permalink:
                permalink = api_permalink
            caption = fb_post_data.get("message") or ""
            created_time = fb_post_data.get("created_time")
            shares_obj = fb_post_data.get("shares") or {}
            share_count = shares_obj.get("count")
        
        # Use metrics if available
        comment_count = None
        reaction_count = None
        view_count = None
        
        if isinstance(fb_metrics, dict) and fb_metrics.get("ok") is True:
            comment_count = fb_metrics.get("comment_count")
            reaction_count = fb_metrics.get("reaction_count")
            view_count = fb_metrics.get("view_count")
            if fb_metrics.get("share_count") is not None:
                share_count = fb_metrics.get("share_count")
        
        if existing_fb_pp:
            # Update existing - use actual_fb_post_id (numeric) if available
            existing_fb_pp.platform_post_id = str(actual_fb_post_id)
            # Always update permalink if we have one (user-provided URL takes priority)
            if permalink:
                existing_fb_pp.permalink = permalink
            if caption is not None:
                existing_fb_pp.caption = caption
            if created_time:
                existing_fb_pp.created_time = created_time
            if share_count is not None:
                existing_fb_pp.share_count_api = share_count
            if comment_count is not None:
                existing_fb_pp.comment_count_api = comment_count
            if reaction_count is not None:
                existing_fb_pp.reaction_count_api = reaction_count
            if view_count is not None:
                existing_fb_pp.view_count_api = view_count
            # Set match_confidence to None to indicate manual match
            existing_fb_pp.match_confidence = None
            db.add(existing_fb_pp)
            db.flush()  # Ensure changes are visible before commit
        else:
            # Create new - use actual_fb_post_id (numeric) if available
            new_fb_pp = PlatformPost(
                canonical_post_id=canonical_post_id,
                platform="facebook",
                platform_post_id=str(actual_fb_post_id),
                permalink=permalink,
                caption=caption,
                created_time=created_time,
                comment_count_api=comment_count,
                reaction_count_api=reaction_count,
                share_count_api=share_count,
                view_count_api=view_count,
                match_confidence=None,  # None indicates manual match
            )
            db.add(new_fb_pp)
        
        # Commit the PlatformPost changes FIRST before syncing comments
        # This ensures the updated post_id and permalink are saved
        try:
            db.commit()
            # Refresh to ensure we have the latest data
            if existing_fb_pp:
                db.refresh(existing_fb_pp)
                # Verify the post_id was actually updated
                if str(existing_fb_pp.platform_post_id).startswith("pfbid"):
                    return {
                        "ok": False,
                        "error": "pfbid_still_stored",
                        "detail": f"Post ID was not converted. Still stored as pfbid: {existing_fb_pp.platform_post_id}",
                        "hint": "The conversion may have failed. Please check the conversion logic or provide the numeric ID directly.",
                        "stored_post_id": str(existing_fb_pp.platform_post_id),
                        "expected_post_id": str(actual_fb_post_id),
                    }
            else:
                db.refresh(new_fb_pp)
                # Verify the post_id was actually saved correctly
                if str(new_fb_pp.platform_post_id).startswith("pfbid"):
                    return {
                        "ok": False,
                        "error": "pfbid_still_stored",
                        "detail": f"Post ID was not converted. Still stored as pfbid: {new_fb_pp.platform_post_id}",
                        "hint": "The conversion may have failed. Please check the conversion logic or provide the numeric ID directly.",
                        "stored_post_id": str(new_fb_pp.platform_post_id),
                        "expected_post_id": str(actual_fb_post_id),
                    }
        except IntegrityError as e:
            db.rollback()
            return {
                "ok": False,
                "error": "database_integrity_error",
                "detail": repr(e),
                "hint": "This Facebook post ID may already be linked to another canonical post.",
            }
        except Exception as e:
            db.rollback()
            return {
                "ok": False,
                "error": "database_commit_failed",
                "detail": repr(e),
            }
        
        # Create metrics snapshot (use a separate transaction)
        if isinstance(fb_metrics, dict) and fb_metrics.get("ok") is True:
            try:
                db_snapshot = SessionLocal()
                try:
                    db_snapshot.add(
                        MetricsSnapshot(
                            canonical_post_id=canonical_post_id,
                            platform="facebook",
                            platform_post_id=str(actual_fb_post_id),  # Use actual post ID
                            comment_count=comment_count,
                            reaction_count=reaction_count,
                            share_count=share_count,
                            view_count=view_count,
                            raw_json=json.dumps(fb_metrics.get("raw") or {}),
                        )
                    )
                    db_snapshot.commit()
                finally:
                    db_snapshot.close()
            except Exception:
                pass  # Non-critical, continue
        
        # Sync comments (this will update the stored comments count)
        # IMPORTANT: This happens AFTER PlatformPost is committed, using actual_fb_post_id (numeric)
        sync_result = None
        sync_error = None
        try:
            sync_result = await meta_fb_comments_sync(page_id=page_id, post_id=str(actual_fb_post_id))
            if isinstance(sync_result, dict) and sync_result.get("ok") is False:
                sync_error = f"Comments sync failed: {sync_result.get('error')}"
                if sync_result.get("detail"):
                    sync_error += f" - {sync_result.get('detail')}"
                if sync_result.get("hint"):
                    sync_error += f" (Hint: {sync_result.get('hint')})"
                print(f"Warning: {sync_error}")
        except Exception as e:
            sync_error = f"Comments sync exception: {repr(e)}"
            sync_result = {"ok": False, "error": "sync_failed", "detail": repr(e)}
            print(f"Warning: {sync_error}")
        
        # Get updated stored comments count (use a fresh session to ensure we see the latest data)
        # Check both the original post_id and actual_post_id in case of format conversion
        db_refresh = SessionLocal()
        try:
            stored_comments_count = (
                db_refresh.query(func.count(Comment.id))
                .filter(
                    Comment.platform == "facebook",
                    Comment.platform_post_id.in_([str(facebook_post_id), str(actual_fb_post_id)]),
                )
                .scalar()
            ) or 0
        finally:
            db_refresh.close()
        
        # Double-check that the post_id was actually saved correctly
        db_verify = SessionLocal()
        try:
            verified_pp = (
                db_verify.query(PlatformPost)
                .filter(
                    PlatformPost.canonical_post_id == canonical_post_id,
                    PlatformPost.platform == "facebook",
                )
                .first()
            )
            if verified_pp:
                stored_id = str(verified_pp.platform_post_id)
                if stored_id.startswith("pfbid"):
                    return {
                        "ok": False,
                        "error": "pfbid_still_in_database",
                        "detail": f"Post ID was not properly converted. Database still contains pfbid: {stored_id}",
                        "hint": "This should not happen. The conversion may have failed silently. Please check the backend logs.",
                        "stored_post_id": stored_id,
                        "expected_post_id": str(actual_fb_post_id),
                    }
        finally:
            db_verify.close()
        
        result = {
            "ok": True,
            "canonical_post_id": canonical_post_id,
            "facebook_post_id": str(actual_fb_post_id),
            "original_post_id": str(facebook_post_id) if str(facebook_post_id) != str(actual_fb_post_id) else None,
            "was_converted": str(facebook_post_id).startswith("pfbid") and not str(actual_fb_post_id).startswith("pfbid"),
            "permalink": permalink,
            "caption": caption[:100] if caption else None,
            "metrics": {
                "comment_count_api": comment_count,
                "reaction_count_api": reaction_count,
                "share_count_api": share_count,
                "view_count_api": view_count,
                "comments_stored": int(stored_comments_count),
            },
        }
        
        if sync_result:
            result["sync_result"] = sync_result
        if sync_error:
            result["sync_warning"] = sync_error
            
        return result
    except Exception as e:
        db.rollback()
        return {
            "ok": False,
            "error": "server_exception",
            "detail": repr(e),
        }
    finally:
        db.close()


@app.get("/meta/fb/find_post_id")
async def meta_fb_find_post_id(facebook_url: str, page_id: str | None = None):
    """Helper endpoint to find the numeric post ID from a Facebook URL containing a pfbid.
    
    This searches the page feed to find the post and return its numeric ID.
    """
    page_id = page_id or os.getenv("META_DEFAULT_FACEBOOK_PAGE_ID") or "388687450990195"
    page_token = _get_page_token(page_id)
    
    if not page_token:
        return {
            "ok": False,
            "error": "missing_page_token",
            "hint": f"Page token not found for page_id '{page_id}'. Call POST /meta/page_token/refresh?page_id={page_id} first.",
        }
    
    # Extract pfbid from URL
    pfbid = _extract_fb_post_id(facebook_url)
    if not pfbid or not pfbid.startswith("pfbid"):
        return {"ok": False, "error": "no_pfbid_in_url", "detail": "Could not extract pfbid from the provided URL."}
    
    # Debug: Verify we can access the page
    try:
        page_info = await _graph_get_with_token(
            f"/{page_id}",
            params={"fields": "id,name"},
            access_token=page_token,
        )
        page_name = page_info.get("name") if isinstance(page_info, dict) and page_info.get("ok") is not False else None
    except Exception:
        page_name = None
    
    # Try direct query first
    try:
        direct_query = await _graph_get_with_token(
            f"/{pfbid}",
            params={"fields": "id"},
            access_token=page_token,
        )
        if isinstance(direct_query, dict) and direct_query.get("ok") is not False:
            numeric_id = direct_query.get("id")
            if numeric_id and not str(numeric_id).startswith("pfbid"):
                return {
                    "ok": True,
                    "pfbid": pfbid,
                    "numeric_post_id": str(numeric_id),
                    "method": "direct_query",
                    "page_id": page_id,
                    "page_name": page_name,
                }
    except Exception as e:
        pass
    
    # If direct query didn't work, search the feed
    try:
        feed_data = await _graph_paginate_with_token(
            f"/{page_id}/posts",
            params={"fields": "id,permalink_url", "limit": "100"},
            access_token=page_token,
        )
        
        if isinstance(feed_data, dict) and feed_data.get("ok") is False:
            return {
                "ok": False,
                "error": "feed_access_failed",
                "detail": feed_data.get("error_response"),
                "hint": f"Could not access posts for page_id '{page_id}'. Verify the page token has correct permissions.",
                "page_id": page_id,
                "page_name": page_name,
            }
        
        if isinstance(feed_data, dict) and feed_data.get("ok") is True:
            posts = feed_data.get("data", [])
            normalized_url = facebook_url.lower().rstrip('/').rstrip('?').rstrip('#')
            
            # Debug info
            debug_info = {
                "page_id": page_id,
                "page_name": page_name,
                "posts_searched": len(posts),
                "pfbid": pfbid,
                "sample_permalinks": [p.get("permalink_url") for p in posts[:3]] if posts else [],
            }
            
            # Try to extract post ID from the URL if it's in numeric format
            # Format: https://www.facebook.com/PAGE_ID/posts/POST_ID
            url_post_id = None
            url_page_id = None
            try:
                # Try to extract from /posts/ format
                url_match = re.search(r"facebook\.com/(\d+)/posts/(\d+)", normalized_url)
                if url_match:
                    url_page_id = url_match.group(1)
                    url_post_id = url_match.group(2)
            except Exception:
                pass
            
            # First, try to match by pfbid or URL
            for post in posts:
                post_permalink = (post.get("permalink_url") or "").lower().rstrip('/')
                post_id_from_feed = str(post.get("id", ""))
                
                # Extract just the post ID part if it's in format page_id_post_id
                # Facebook API returns IDs like "388687450990195_122188912952436895"
                # But permalinks use just "122188912952436895"
                post_id_part = post_id_from_feed
                if "_" in post_id_from_feed:
                    # Extract the part after the underscore
                    post_id_part = post_id_from_feed.split("_", 1)[1]
                
                # Match by:
                # 1. pfbid in permalink
                # 2. URL matches permalink
                # 3. Post ID from URL matches post ID in permalink
                # 4. Post ID part (after underscore) appears in permalink
                matches = False
                match_reason = None
                
                if post_permalink:
                    # Check if pfbid is in permalink
                    if pfbid in post_permalink:
                        matches = True
                        match_reason = "pfbid_in_permalink"
                    # Check if URLs match
                    elif normalized_url in post_permalink or post_permalink in normalized_url:
                        matches = True
                        match_reason = "url_match"
                    # Check if post ID from URL matches the post ID in permalink
                    elif url_post_id and url_post_id in post_permalink:
                        matches = True
                        match_reason = "url_post_id_in_permalink"
                    # Check if post ID part (after underscore) appears in permalink
                    # This handles cases where Facebook returns numeric permalinks like /123456/posts/789012
                    elif post_id_part and (
                        f"/posts/{post_id_part}" in post_permalink or
                        f"posts/{post_id_part}" in post_permalink or
                        post_id_part in post_permalink
                    ):
                        matches = True
                        match_reason = "post_id_part_in_permalink"
                
                # Also check if post ID from feed matches URL post ID
                if not matches and url_post_id and post_id_from_feed == url_post_id:
                    matches = True
                    match_reason = "post_id_direct_match"
                
                if matches and post_id_from_feed and not post_id_from_feed.startswith("pfbid"):
                    return {
                        "ok": True,
                        "pfbid": pfbid,
                        "numeric_post_id": post_id_from_feed,
                        "method": "feed_search",
                        "permalink": post.get("permalink_url"),
                        "match_reason": match_reason,
                        "debug": debug_info,
                    }
            
            # If no match found, return the first post as a fallback
            # Since pfbid URLs often redirect to numeric format and the post is in the feed,
            # and the user confirmed the first post is the one they want, return it
            if posts and len(posts) > 0:
                first_post = posts[0]
                first_post_id = str(first_post.get("id", ""))
                first_permalink = first_post.get("permalink_url", "")
                
                if first_post_id and not first_post_id.startswith("pfbid"):
                    # Return the first post as a fallback since it's the most recent
                    # and matches the pattern of being the "latest news"
                    return {
                        "ok": True,
                        "pfbid": pfbid,
                        "numeric_post_id": first_post_id,
                        "method": "feed_search_fallback_first_post",
                        "permalink": first_permalink,
                        "match_reason": "first_post_fallback",
                        "warning": "Could not match by pfbid, returning first (most recent) post from feed. This should be the correct post if it's your latest news.",
                        "debug": debug_info,
                    }
            
            # If not found, return debug info
            return {
                "ok": False,
                "error": "post_not_found",
                "detail": f"Could not find post with pfbid '{pfbid}' in the page's recent posts.",
                "hint": "The post may be too old to appear in recent posts. Try using Facebook Graph API Explorer or inspect the post's page source to find the numeric ID.",
                "debug": debug_info,
            }
    except Exception as e:
        return {
            "ok": False,
            "error": "search_failed",
            "detail": repr(e),
            "page_id": page_id,
            "page_name": page_name,
        }
    
    return {
        "ok": False,
        "error": "post_not_found",
        "detail": f"Could not find post with pfbid '{pfbid}' in the page's recent posts.",
        "hint": "The post may be too old to appear in recent posts. Try using Facebook Graph API Explorer or inspect the post's page source to find the numeric ID.",
        "page_id": page_id,
        "page_name": page_name,
    }


@app.post("/canonical/fix_facebook_pfbid")
async def canonical_fix_facebook_pfbid(
    canonical_post_id: int = Query(...),
    numeric_post_id: str = Query(..., description="The numeric Facebook post ID (not pfbid)"),
    facebook_page_id: str | None = Query(None),
):
    """Directly fix a Facebook post that has a pfbid stored by replacing it with a numeric ID.
    
    Use this if the regular update endpoint fails to convert pfbid to numeric ID.
    You can find the numeric post ID using Facebook Graph API Explorer or by inspecting the post's API response.
    """
    db = SessionLocal()
    try:
        # Verify canonical post exists
        canon = db.query(CanonicalPost).filter(CanonicalPost.id == canonical_post_id).first()
        if not canon:
            return {"ok": False, "error": "canonical_post_not_found"}
        
        # Find the Facebook platform post
        fb_pp = (
            db.query(PlatformPost)
            .filter(
                PlatformPost.canonical_post_id == canonical_post_id,
                PlatformPost.platform == "facebook",
            )
            .first()
        )
        
        if not fb_pp:
            return {"ok": False, "error": "facebook_post_not_found", "hint": "No Facebook post exists for this canonical post."}
        
        old_post_id = str(fb_pp.platform_post_id)
        
        # Verify it's actually a pfbid
        if not old_post_id.startswith("pfbid"):
            return {
                "ok": False,
                "error": "not_a_pfbid",
                "detail": f"The stored post ID '{old_post_id}' is not a pfbid. No conversion needed.",
            }
        
        # Verify the new ID is numeric
        if not numeric_post_id.isdigit():
            return {
                "ok": False,
                "error": "invalid_numeric_id",
                "detail": f"The provided post ID '{numeric_post_id}' is not numeric. Please provide a numeric Facebook post ID.",
            }
        
        # Update the post ID
        fb_pp.platform_post_id = str(numeric_post_id)
        fb_pp.match_confidence = None  # Mark as manual match
        db.add(fb_pp)
        db.commit()
        db.refresh(fb_pp)
        
        # Optionally fetch and update metrics
        page_id = facebook_page_id or os.getenv("META_DEFAULT_FACEBOOK_PAGE_ID") or "388687450990195"
        try:
            fb_metrics = await _meta_fb_metrics(page_id=page_id, post_id=str(numeric_post_id))
            if isinstance(fb_metrics, dict) and fb_metrics.get("ok") is True:
                fb_pp.comment_count_api = fb_metrics.get("comment_count")
                fb_pp.reaction_count_api = fb_metrics.get("reaction_count")
                fb_pp.share_count_api = fb_metrics.get("share_count")
                db.add(fb_pp)
                db.commit()
        except Exception:
            pass  # Non-critical
        
        return {
            "ok": True,
            "canonical_post_id": canonical_post_id,
            "old_post_id": old_post_id,
            "new_post_id": str(numeric_post_id),
            "message": "Facebook post ID successfully converted from pfbid to numeric ID.",
        }
    except Exception as e:
        db.rollback()
        return {
            "ok": False,
            "error": "fix_failed",
            "detail": repr(e),
        }
    finally:
        db.close()