import mysql.connector
from app.db.connection import get_connection
from app.ai.analyzer import analyze_article
from app.ai.prompts import PROMPT_VERSION
from app.utils.fetcher import fetch_article_text

# Quand content < ce seuil, on considère "vide/pas utile" et on tente un scraping
MIN_CONTENT_CHARS = 400

# On tronque pour éviter d'envoyer des pavés à l'IA
MAX_CONTENT_CHARS = 12000


def fetch_rows_for_selected(cur, raw_ids: list[int]):
    if not raw_ids:
        return []

    placeholders = ", ".join(["%s"] * len(raw_ids))

    query = f"""
        SELECT
            ar.id,
            ar.title,
            ar.excerpt,
            ar.lang,
            ar.url,
            ar.content,
            tr.triage_score,
            tr.triage_label
        FROM articles_raw ar
        JOIN articles_triage tr
          ON tr.raw_id = ar.id
        LEFT JOIN articles_scored sc
          ON sc.raw_id = ar.id AND sc.prompt_version = %s
        WHERE ar.id IN ({placeholders})
          AND sc.raw_id IS NULL
        ORDER BY tr.triage_score DESC, ar.published_at DESC
    """

    params = [PROMPT_VERSION, *raw_ids]
    cur.execute(query, params)
    return cur.fetchall()


def analyze_selected(raw_ids: list[int]):
    raw_ids = [int(x) for x in raw_ids if str(x).strip().isdigit()]

    if not raw_ids:
        return {
            "requested": 0,
            "to_process": 0,
            "inserted": 0,
            "failed": 0,
            "skipped": 0,
            "scoremax": None,
        }

    conn = get_connection()
    cur = conn.cursor(dictionary=True)

    rows = fetch_rows_for_selected(cur, raw_ids)

    if not rows:
        cur.close()
        conn.close()
        return {
            "requested": len(raw_ids),
            "to_process": 0,
            "inserted": 0,
            "failed": 0,
            "skipped": len(raw_ids),
            "scoremax": None,
        }

    inserted = 0
    failed = 0
    scores = []

    processed_ids = {r["id"] for r in rows}
    skipped = len(raw_ids) - len(processed_ids)

    for r in rows:
        raw_id = r["id"]
        title = r.get("title") or ""
        excerpt = r.get("excerpt") or ""
        url = r.get("url") or ""
        content = r.get("content") or ""

        try:
            fetched = False

            if len(content.strip()) < MIN_CONTENT_CHARS and url:
                text = fetch_article_text(url)
                if text:
                    text = text.strip()[:MAX_CONTENT_CHARS]
                    cur.execute(
                        "UPDATE articles_raw SET content=%s WHERE id=%s",
                        (text, raw_id)
                    )
                    conn.commit()
                    content = text
                    fetched = True

            result = analyze_article(title=title, excerpt=excerpt, content=content)

            cur.execute(
                """
                INSERT INTO articles_scored
                  (raw_id, good_news_score, summary, wink_title, topic,
                   source_lang, output_lang, model, prompt_version, tokens_in, tokens_out)
                VALUES
                  (%s, %s, %s, %s, %s,
                   %s, %s, %s, %s, %s, %s)
                """,
                (
                    raw_id,
                    result["good_news_score"],
                    result["summary"],
                    result["wink_title"],
                    result["topic"],
                    result["source_lang"],
                    "fr",
                    result["model"],
                    PROMPT_VERSION,
                    result["tokens_in"],
                    result["tokens_out"],
                )
            )

            inserted += 1
            scores.append(result["good_news_score"])
            conn.commit()

            fetch_flag = " fetched_content=1" if fetched else ""
            print(
                f"✅ Scoré V2 {PROMPT_VERSION} score={result['good_news_score']} "
                f"triage={r.get('triage_score')} label={r.get('triage_label')} "
                f"raw_id={raw_id}{fetch_flag} reason={result['score_reason']}"
            )

        except mysql.connector.errors.IntegrityError:
            conn.commit()
            skipped += 1

        except Exception as e:
            failed += 1
            print(f"❌ Échec raw_id={raw_id}: {e}")

    cur.close()
    conn.close()

    scoremax = max(scores) if scores else None

    print(
        f"🎯 Analyse V2 terminée — requested={len(raw_ids)}, to_process={len(rows)}, "
        f"inserted={inserted}, failed={failed}, skipped={skipped}, scoremax={scoremax}"
    )

    return {
        "requested": len(raw_ids),
        "to_process": len(rows),
        "inserted": inserted,
        "failed": failed,
        "skipped": skipped,
        "scoremax": scoremax,
    }


if __name__ == "__main__":
    print("Ce script est prévu pour être appelé depuis Flask avec une liste de raw_ids.")