"""
Social Inbox API Route
Nhận webhook từ Facebook/Instagram/TikTok → phân tích sentiment → feed vào Canifa learning loop.
Cũng expose REST API để FE dashboard đọc messages.
"""

import json
import logging
import os
import sqlite3
from datetime import datetime, timezone
from pathlib import Path
from typing import Optional

from fastapi import APIRouter, BackgroundTasks, Header, HTTPException, Query, Request
from fastapi.responses import JSONResponse, PlainTextResponse

from common.feedback_tracker import save_feedback
from common.social.inbox_webhook import (handle_facebook_verify, parse_facebook_webhook, parse_tiktok_webhook,
                                         verify_facebook_signature)
from common.social.sentiment_vi import analyze_sentiment_detail

logger = logging.getLogger(__name__)

router = APIRouter(prefix="/api/social", tags=["Social Inbox"])

DB_PATH = Path(__file__).resolve().parents[3] / "database" / "social_inbox.db"
AUTOTRAIN_DB = Path(__file__).resolve().parents[3] / "database" / "autotrain.db"

# ─────────────────────────────────────────────
# Storage helpers
# ─────────────────────────────────────────────

def init_social_inbox_db():
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
    with sqlite3.connect(DB_PATH) as conn:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS social_messages (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                platform TEXT NOT NULL,
                sender_id TEXT,
                sender_name TEXT,
                body TEXT NOT NULL,
                message_type TEXT DEFAULT 'comment',
                sentiment TEXT DEFAULT 'neutral',
                pos_score INTEGER DEFAULT 0,
                neg_score INTEGER DEFAULT 0,
                matched_keywords TEXT,
                post_id TEXT,
                received_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                fed_to_autotrain BOOLEAN DEFAULT 0,
                UNIQUE(platform, body)
            )
        """)
        conn.commit()

init_social_inbox_db()

def _append_messages(new_messages: list[dict]) -> int:
    added = 0
    with sqlite3.connect(DB_PATH) as conn:
        cur = conn.cursor()
        for msg in new_messages:
            try:
                cur.execute("""
                    INSERT INTO social_messages (
                        platform, sender_id, sender_name, body, message_type, sentiment, post_id
                    ) VALUES (?, ?, ?, ?, ?, ?, ?)
                """, (
                    msg.get("platform", ""), msg.get("sender_id", ""), msg.get("sender_name", ""),
                    msg.get("body", ""), msg.get("message_type", ""), msg.get("sentiment", ""), msg.get("post_id", "")
                ))
                added += 1
            except sqlite3.IntegrityError:
                pass
        conn.commit()
    return added


# ─────────────────────────────────────────────
# Background: Feed message vào learning loop
# ─────────────────────────────────────────────

def _feed_to_feedback_tracker(messages: list[dict]):
    """
    Feed social messages vào Canifa feedback_tracker và autotrain.db.
    """
    for msg in messages:
        if msg.get("sentiment") == "negative":
            try:
                save_feedback(
                    trace_id=f"social_{msg.get('platform')}_{datetime.now(timezone.utc).timestamp()}",
                    rating=0,  # 0 = dislike (negative feedback)
                    comment=msg.get("body", ""),
                    user_category="social_feedback",
                    ai_category="social_negative_comment",
                    ai_severity="low",
                    ai_summary=f"[{msg.get('platform', '').upper()}] Negative comment từ @{msg.get('sender_name', 'Unknown')}: {msg.get('body', '')[:100]}",
                )
                logger.info(f"📥 Social negative feedback fed to learning loop: {msg.get('platform')} / {msg.get('body', '')[:50]}")
                
                # Insert into autotrain.db
                with sqlite3.connect(AUTOTRAIN_DB) as conn:
                    cur = conn.cursor()
                    cur.execute("""
                        INSERT INTO feedbacks (
                            feedback_id, query, ai_response, feedback_text, feedback_type, channel, status
                        ) VALUES (?, ?, ?, ?, ?, ?, ?)
                    """, (
                        f"FB-SOC-{int(datetime.now(timezone.utc).timestamp())}",
                        msg.get("body", ""),
                        "",
                        msg.get("body", ""),
                        "bug",
                        "social_inbox",
                        "pending"
                    ))
                    conn.commit()
                    
                # Update fed_to_autotrain flag
                with sqlite3.connect(DB_PATH) as conn:
                    conn.execute("UPDATE social_messages SET fed_to_autotrain = 1 WHERE platform = ? AND body = ?", (msg.get("platform"), msg.get("body")))
                    conn.commit()
            except Exception as e:
                logger.error(f"Failed to feed to feedback tracker: {e}")


# ─────────────────────────────────────────────
# Facebook/Instagram Webhook
# ─────────────────────────────────────────────

@router.get("/webhook/facebook")
async def facebook_webhook_verify(
    hub_mode: str = Query(None, alias="hub.mode"),
    hub_verify_token: str = Query(None, alias="hub.verify_token"),
    hub_challenge: str = Query("", alias="hub.challenge"),
):
    """Facebook webhook verification (GET handshake)."""
    challenge = handle_facebook_verify(hub_mode or "", hub_verify_token or "", hub_challenge)
    if challenge is None:
        raise HTTPException(status_code=403, detail="Webhook verification failed")
    return PlainTextResponse(challenge)


@router.post("/webhook/facebook")
async def facebook_webhook_receive(
    request: Request,
    background_tasks: BackgroundTasks,
    x_hub_signature_256: Optional[str] = Header(None, alias="X-Hub-Signature-256"),
):
    """Nhận Facebook/Instagram comment/DM webhook events."""
    body = await request.body()

    # Verify signature
    if not verify_facebook_signature(body, x_hub_signature_256 or ""):
        raise HTTPException(status_code=403, detail="Invalid webhook signature")

    try:
        payload = await request.json()
    except Exception:
        raise HTTPException(status_code=400, detail="Invalid JSON")

    messages = await parse_facebook_webhook(payload)
    if messages:
        added = _append_messages(messages)
        background_tasks.add_task(_feed_to_feedback_tracker, messages)
        logger.info(f"📬 Facebook/IG webhook: {len(messages)} events, {added} mới")

    return JSONResponse({"ok": True, "received": len(messages)})


# ─────────────────────────────────────────────
# TikTok Webhook
# ─────────────────────────────────────────────

@router.post("/webhook/tiktok")
async def tiktok_webhook_receive(
    request: Request,
    background_tasks: BackgroundTasks,
):
    """Nhận TikTok comment webhook events."""
    try:
        payload = await request.json()
    except Exception:
        raise HTTPException(status_code=400, detail="Invalid JSON")

    messages = await parse_tiktok_webhook(payload)
    if messages:
        added = _append_messages(messages)
        background_tasks.add_task(_feed_to_feedback_tracker, messages)
        logger.info(f"📬 TikTok webhook: {len(messages)} comments, {added} mới")

    return JSONResponse({"ok": True, "received": len(messages)})


# ─────────────────────────────────────────────
# REST API cho FE Dashboard
# ─────────────────────────────────────────────

@router.get("/messages")
async def get_social_messages(
    platform: Optional[str] = Query(None, description="facebook | instagram | tiktok"),
    sentiment: Optional[str] = Query(None, description="positive | negative | neutral"),
    limit: int = Query(50, ge=1, le=200),
    offset: int = Query(0, ge=0),
):
    """
    Lấy danh sách social messages cho dashboard.
    Filter theo platform và/hoặc sentiment.
    """
    with sqlite3.connect(DB_PATH) as conn:
        conn.row_factory = sqlite3.Row
        cur = conn.cursor()
        
        query = "SELECT * FROM social_messages WHERE 1=1"
        params = []
        if platform:
            query += " AND platform = ?"
            params.append(platform)
        if sentiment:
            query += " AND sentiment = ?"
            params.append(sentiment)
            
        # Get total
        cur.execute(f"SELECT COUNT(*) FROM ({query})", params)
        total = cur.fetchone()[0]
        
        # Get paginated
        query += " ORDER BY received_at DESC LIMIT ? OFFSET ?"
        params.extend([limit, offset])
        cur.execute(query, params)
        rows = [dict(row) for row in cur.fetchall()]

    return {
        "total": total,
        "limit": limit,
        "offset": offset,
        "messages": rows,
    }


@router.get("/messages/stats")
async def get_social_stats():
    """Thống kê tổng hợp social messages theo platform + sentiment."""
    with sqlite3.connect(DB_PATH) as conn:
        cur = conn.cursor()
        cur.execute("SELECT COUNT(*) FROM social_messages")
        total = cur.fetchone()[0]
        
        cur.execute("SELECT platform, sentiment, COUNT(*) FROM social_messages GROUP BY platform, sentiment")
        rows = cur.fetchall()

    stats: dict = {
        "total": total,
        "by_platform": {},
        "by_sentiment": {"positive": 0, "negative": 0, "neutral": 0},
        "negative_rate": 0.0,
    }

    for platform, sentiment, count in rows:
        if platform not in stats["by_platform"]:
            stats["by_platform"][platform] = {"total": 0, "positive": 0, "negative": 0, "neutral": 0}
        stats["by_platform"][platform]["total"] += count
        stats["by_platform"][platform][sentiment] = count
        stats["by_sentiment"][sentiment] = stats["by_sentiment"].get(sentiment, 0) + count

    if total > 0:
        stats["negative_rate"] = round(stats["by_sentiment"]["negative"] / total * 100, 1)

    return stats


@router.post("/messages/analyze")
async def analyze_text_sentiment(request: Request):
    """
    Test sentiment analysis — nhập text, trả về phân tích.
    Dùng cho debug / demo trên dashboard.
    """
    body = await request.json()
    text = body.get("text", "")
    if not text:
        raise HTTPException(status_code=400, detail="text is required")

    result = await analyze_sentiment_detail(text)
    return result


@router.delete("/messages/clear")
async def clear_social_messages():
    """Xóa toàn bộ messages (dùng khi debug)."""
    with sqlite3.connect(DB_PATH) as conn:
        conn.execute("DELETE FROM social_messages")
        conn.commit()
    return {"ok": True, "message": "Đã xóa tất cả social messages"}
