#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""Read-only re-score audit for stored competitor match attempts."""

from __future__ import annotations

from collections import Counter
from dataclasses import asdict, dataclass
import json
from typing import Any, Iterable, Sequence

from sqlalchemy import bindparam, text

from services.competitor_price_feeder import MIN_MATCH_SCORE
from services.marketplace_product_matcher import score_marketplace_match


DEFAULT_RESCAN_STATUSES = (
    "true_low_confidence",
    "recoverable_low_score",
    "low_score",
    "refresh_low_score",
)
RESCORE_ACCEPTED_CURRENT_STATUS = "rescore_accepted_current"


@dataclass(frozen=True)
class MatchAttemptRescoreDecision:
    sku: str
    stored_status: str
    suggested_status: str
    gate_pass: bool
    stored_score: float | None
    current_score: float | None
    hard_veto: bool
    comparison_mode: str
    match_type: str
    price_basis: str
    alert_tier: str
    reasons: list[str]
    momo_product_name: str
    competitor_product_name: str

    def to_dict(self) -> dict[str, Any]:
        return asdict(self)


def _to_float(value: Any) -> float | None:
    try:
        if value in (None, ""):
            return None
        return float(value)
    except (TypeError, ValueError):
        return None


def _text_value(row: dict[str, Any], *keys: str) -> str:
    for key in keys:
        value = row.get(key)
        if value not in (None, ""):
            return str(value)
    return ""


def classify_match_attempt_row(
    row: dict[str, Any],
    *,
    min_score: float = MIN_MATCH_SCORE,
) -> MatchAttemptRescoreDecision:
    """Classify one stored match attempt using the current matcher."""
    sku = _text_value(row, "sku")
    stored_status = _text_value(row, "attempt_status", "stored_status")
    momo_name = _text_value(row, "momo_product_name", "momo_name")
    competitor_name = _text_value(
        row,
        "best_competitor_product_name",
        "competitor_product_name",
        "pchome_name",
    )
    stored_score = _to_float(row.get("best_match_score") or row.get("stored_score") or row.get("match_score"))

    if not momo_name or not competitor_name:
        return MatchAttemptRescoreDecision(
            sku=sku,
            stored_status=stored_status,
            suggested_status="skipped_missing_identity_text",
            gate_pass=False,
            stored_score=stored_score,
            current_score=None,
            hard_veto=False,
            comparison_mode="unknown",
            match_type="unknown",
            price_basis="none",
            alert_tier="suppress",
            reasons=["missing_identity_text"],
            momo_product_name=momo_name,
            competitor_product_name=competitor_name,
        )

    diagnostics = score_marketplace_match(
        momo_name,
        competitor_name,
        momo_price=_to_float(row.get("momo_price")),
        competitor_price=_to_float(
            row.get("best_competitor_price")
            or row.get("competitor_price")
            or row.get("pchome_price")
            or row.get("price")
        ),
    )
    reasons = list(diagnostics.reasons or ())

    if diagnostics.hard_veto:
        suggested_status = "identity_veto_current"
        gate_pass = False
    elif diagnostics.comparison_mode == "unit_comparable":
        suggested_status = "unit_comparable_current"
        gate_pass = False
    elif diagnostics.score >= min_score and "variant_selection_review" not in reasons:
        suggested_status = "accepted_current"
        gate_pass = True
    else:
        suggested_status = "low_score_current"
        gate_pass = False

    return MatchAttemptRescoreDecision(
        sku=sku,
        stored_status=stored_status,
        suggested_status=suggested_status,
        gate_pass=gate_pass,
        stored_score=stored_score,
        current_score=diagnostics.score,
        hard_veto=diagnostics.hard_veto,
        comparison_mode=diagnostics.comparison_mode,
        match_type=diagnostics.match_type,
        price_basis=diagnostics.price_basis,
        alert_tier=diagnostics.alert_tier,
        reasons=reasons,
        momo_product_name=momo_name,
        competitor_product_name=competitor_name,
    )


def summarize_match_attempt_rescore(
    rows: Iterable[dict[str, Any]],
    *,
    min_score: float = MIN_MATCH_SCORE,
    sample_limit: int = 20,
) -> dict[str, Any]:
    decisions: list[MatchAttemptRescoreDecision] = []
    status_counts: Counter[str] = Counter()
    reason_counts: Counter[str] = Counter()

    for row in rows:
        decision = classify_match_attempt_row(row, min_score=min_score)
        decisions.append(decision)
        status_counts[decision.suggested_status] += 1
        reason_counts.update(decision.reasons)

    return {
        "scanned": len(decisions),
        "gate_pass": sum(1 for decision in decisions if decision.gate_pass),
        "unit_comparable": status_counts.get("unit_comparable_current", 0),
        "identity_veto": status_counts.get("identity_veto_current", 0),
        "still_low": status_counts.get("low_score_current", 0),
        "skipped": status_counts.get("skipped_missing_identity_text", 0),
        "status_counts": dict(status_counts),
        "top_reasons": [
            {"reason": reason, "count": count}
            for reason, count in reason_counts.most_common(30)
        ],
        "samples": [
            decision.to_dict()
            for decision in decisions[: max(0, sample_limit)]
        ],
    }


def _json_expr(conn, bind_name: str) -> str:
    return f"CAST(:{bind_name} AS jsonb)" if conn.dialect.name == "postgresql" else f":{bind_name}"


def _diagnostic_payload(decision: MatchAttemptRescoreDecision) -> dict[str, Any]:
    return {
        "rescore_version": "current_matcher_v1",
        "stored_status": decision.stored_status,
        "stored_score": decision.stored_score,
        "score": decision.current_score,
        "hard_veto": decision.hard_veto,
        "comparison_mode": decision.comparison_mode,
        "match_type": decision.match_type,
        "price_basis": decision.price_basis,
        "alert_tier": decision.alert_tier,
        "reasons": decision.reasons,
    }


def _diagnostic_text(decision: MatchAttemptRescoreDecision) -> str:
    reasons = ",".join(decision.reasons or [])
    return (
        "matcher_rescore=accepted_current; "
        f"stored_status={decision.stored_status}; "
        f"stored_score={decision.stored_score}; "
        f"current_score={decision.current_score}; "
        f"mode={decision.comparison_mode}; "
        f"price_basis={decision.price_basis}; "
        f"reasons={reasons}"
    )


def _ensure_attempt_table(conn) -> None:
    from services.competitor_price_feeder import CompetitorPriceFeeder

    CompetitorPriceFeeder(engine=None)._ensure_competitor_match_attempts_table(conn)


def _already_materialized(conn, *, source: str, sku: str, candidate_id: str) -> bool:
    row = conn.execute(text("""
        SELECT 1
        FROM competitor_match_attempts
        WHERE sku = :sku
          AND source = :source
          AND attempt_status = :attempt_status
          AND COALESCE(best_competitor_product_id, '') = :candidate_id
        LIMIT 1
    """), {
        "sku": sku,
        "source": source,
        "attempt_status": RESCORE_ACCEPTED_CURRENT_STATUS,
        "candidate_id": candidate_id,
    }).first()
    return row is not None


def materialize_rescore_accept_reviews(
    conn,
    rows: Iterable[dict[str, Any]],
    *,
    source: str = "pchome",
    min_score: float = MIN_MATCH_SCORE,
) -> dict[str, Any]:
    """Append accepted-current rescore rows to the manual review queue.

    This never writes ``competitor_prices``. It only creates a latest
    ``competitor_match_attempts`` row that Dashboard can route through the
    existing human review buttons.
    """
    _ensure_attempt_table(conn)
    stats = {
        "scanned": 0,
        "materialized": 0,
        "skipped_not_gate_pass": 0,
        "skipped_missing_candidate": 0,
        "skipped_duplicate": 0,
        "samples": [],
    }
    search_terms_expr = _json_expr(conn, "search_terms")
    diagnostic_json_expr = _json_expr(conn, "match_diagnostic_json")
    diagnostic_codes_expr = _json_expr(conn, "diagnostic_codes")

    for row in rows:
        stats["scanned"] += 1
        decision = classify_match_attempt_row(row, min_score=min_score)
        if not decision.gate_pass:
            stats["skipped_not_gate_pass"] += 1
            continue

        candidate_id = str(row.get("best_competitor_product_id") or "").strip()
        candidate_name = str(row.get("best_competitor_product_name") or "").strip()
        candidate_price = _to_float(row.get("best_competitor_price"))
        if not candidate_id or not candidate_name or not candidate_price or candidate_price <= 0:
            stats["skipped_missing_candidate"] += 1
            continue

        if _already_materialized(conn, source=source, sku=decision.sku, candidate_id=candidate_id):
            stats["skipped_duplicate"] += 1
            continue

        diagnostic_payload = _diagnostic_payload(decision)
        conn.execute(text(f"""
            INSERT INTO competitor_match_attempts
                (sku, source, momo_product_id, momo_product_name, momo_price,
                 search_terms, candidate_count, attempt_status,
                 best_competitor_product_id, best_competitor_product_name,
                 best_competitor_price, best_match_score, error_message,
                 attempted_at, competitor_product_url, competitor_image_url,
                 competitor_stock, match_diagnostic_json, comparison_mode,
                 hard_veto, diagnostic_codes)
            VALUES
                (:sku, :source, :momo_product_id, :momo_product_name, :momo_price,
                 {search_terms_expr}, :candidate_count, :attempt_status,
                 :best_id, :best_name,
                 :best_price, :best_score, :error_message,
                 CURRENT_TIMESTAMP, :competitor_product_url, :competitor_image_url,
                 :competitor_stock, {diagnostic_json_expr}, :comparison_mode,
                 :hard_veto, {diagnostic_codes_expr})
        """), {
            "sku": decision.sku,
            "source": source,
            "momo_product_id": row.get("momo_product_id"),
            "momo_product_name": decision.momo_product_name,
            "momo_price": _to_float(row.get("momo_price")),
            "search_terms": json.dumps([
                "matcher_rescore:accepted_current",
                f"stored_status:{decision.stored_status}",
            ], ensure_ascii=False),
            "candidate_count": int(row.get("candidate_count") or 1),
            "attempt_status": RESCORE_ACCEPTED_CURRENT_STATUS,
            "best_id": candidate_id,
            "best_name": candidate_name[:300],
            "best_price": candidate_price,
            "best_score": decision.current_score,
            "error_message": _diagnostic_text(decision)[:1000],
            "competitor_product_url": row.get("competitor_product_url"),
            "competitor_image_url": row.get("competitor_image_url"),
            "competitor_stock": row.get("competitor_stock"),
            "match_diagnostic_json": json.dumps(diagnostic_payload, ensure_ascii=False),
            "comparison_mode": decision.comparison_mode,
            "hard_veto": False,
            "diagnostic_codes": json.dumps(decision.reasons, ensure_ascii=False),
        })
        stats["materialized"] += 1
        if len(stats["samples"]) < 10:
            stats["samples"].append(decision.to_dict())

    return stats


def fetch_match_attempt_rescore_rows(
    conn,
    *,
    source: str = "pchome",
    statuses: Sequence[str] = DEFAULT_RESCAN_STATUSES,
    reason_filter: str | None = None,
    limit: int = 100,
    latest_sku_only: bool = True,
) -> list[dict[str, Any]]:
    """Fetch stored attempts for read-only re-score auditing.

    The default mirrors the Dashboard review queue: pick the latest attempt per
    SKU first, then apply the requested status/reason filters. This keeps stale
    historical low-score rows from re-entering operator reports after a SKU has
    already moved to a newer review state.
    """
    status_values = tuple(status for status in statuses if status) or DEFAULT_RESCAN_STATUSES

    if latest_sku_only:
        reason_predicate = "AND diagnostic_codes::text LIKE :reason_filter" if (
            conn.dialect.name == "postgresql" and reason_filter
        ) else "AND CAST(diagnostic_codes AS TEXT) LIKE :reason_filter" if reason_filter else ""
        nulls_last = " NULLS LAST" if conn.dialect.name == "postgresql" else ""
        sql = text(f"""
            WITH ranked AS (
                SELECT
                    sku,
                    attempt_status,
                    momo_product_id,
                    momo_product_name,
                    momo_price,
                    candidate_count,
                    best_competitor_product_id,
                    best_competitor_product_name,
                    best_competitor_price,
                    best_match_score,
                    competitor_product_url,
                    competitor_image_url,
                    competitor_stock,
                    diagnostic_codes,
                    attempted_at,
                    ROW_NUMBER() OVER (
                        PARTITION BY sku
                        ORDER BY attempted_at DESC{nulls_last}, id DESC
                    ) AS rn
                FROM competitor_match_attempts
                WHERE source = :source
            )
            SELECT
                sku,
                attempt_status,
                momo_product_id,
                momo_product_name,
                momo_price,
                candidate_count,
                best_competitor_product_id,
                best_competitor_product_name,
                best_competitor_price,
                best_match_score,
                competitor_product_url,
                competitor_image_url,
                competitor_stock,
                diagnostic_codes,
                attempted_at
            FROM ranked
            WHERE rn = 1
              AND attempt_status IN :statuses
              {reason_predicate}
            ORDER BY attempted_at DESC{nulls_last}
            LIMIT :limit
        """).bindparams(bindparam("statuses", expanding=True))
    elif conn.dialect.name == "postgresql":
        reason_predicate = "AND diagnostic_codes::text LIKE :reason_filter" if reason_filter else ""
        sql = text(f"""
            SELECT DISTINCT ON (sku, best_competitor_product_id)
                sku,
                attempt_status,
                momo_product_id,
                momo_product_name,
                momo_price,
                candidate_count,
                best_competitor_product_id,
                best_competitor_product_name,
                best_competitor_price,
                best_match_score,
                competitor_product_url,
                competitor_image_url,
                competitor_stock,
                diagnostic_codes,
                attempted_at
            FROM competitor_match_attempts
            WHERE source = :source
              AND attempt_status IN :statuses
              {reason_predicate}
            ORDER BY sku, best_competitor_product_id, attempted_at DESC NULLS LAST, id DESC
            LIMIT :limit
        """).bindparams(bindparam("statuses", expanding=True))
    else:
        reason_predicate = "AND CAST(diagnostic_codes AS TEXT) LIKE :reason_filter" if reason_filter else ""
        sql = text(f"""
            WITH ranked AS (
                SELECT
                    sku,
                    attempt_status,
                    momo_product_id,
                    momo_product_name,
                    momo_price,
                    candidate_count,
                    best_competitor_product_id,
                    best_competitor_product_name,
                    best_competitor_price,
                    best_match_score,
                    competitor_product_url,
                    competitor_image_url,
                    competitor_stock,
                    diagnostic_codes,
                    attempted_at,
                    ROW_NUMBER() OVER (
                        PARTITION BY sku, best_competitor_product_id
                        ORDER BY attempted_at DESC, id DESC
                    ) AS rn
                FROM competitor_match_attempts
                WHERE source = :source
                  AND attempt_status IN :statuses
                  {reason_predicate}
            )
            SELECT *
            FROM ranked
            WHERE rn = 1
            ORDER BY attempted_at DESC
            LIMIT :limit
        """).bindparams(bindparam("statuses", expanding=True))

    params = {
        "source": source,
        "statuses": status_values,
        "limit": max(1, int(limit)),
    }
    if reason_filter:
        params["reason_filter"] = f"%{reason_filter}%"

    return [dict(row) for row in conn.execute(sql, params).mappings().all()]


def build_match_attempt_rescore_audit(
    engine,
    *,
    source: str = "pchome",
    statuses: Sequence[str] = DEFAULT_RESCAN_STATUSES,
    reason_filter: str | None = None,
    limit: int = 100,
    min_score: float = MIN_MATCH_SCORE,
    sample_limit: int = 20,
    latest_sku_only: bool = True,
) -> dict[str, Any]:
    with engine.connect() as conn:
        rows = fetch_match_attempt_rescore_rows(
            conn,
            source=source,
            statuses=statuses,
            reason_filter=reason_filter,
            limit=limit,
            latest_sku_only=latest_sku_only,
        )
    summary = summarize_match_attempt_rescore(
        rows,
        min_score=min_score,
        sample_limit=sample_limit,
    )
    summary["selection_mode"] = "latest_sku_only" if latest_sku_only else "historical_candidate_latest"
    return summary
