"""DB上の過去実績から、期待値予想（ev_forecast）の参考的中率を取得する。"""
from __future__ import annotations

EV_PROFILE_KEYS = ("ev_forecast", "p6_latest")


def _pair_key(a: int, b: int) -> str:
    return f"{a}-{b}" if a <= b else f"{b}-{a}"


def _aggregate_umaren_rows(rows: list[dict]) -> dict:
    """PHP の aggregate_umaren_box_from_rows（◎〜△4頭・6通り）と同ロジック。"""
    stake = 100
    pairs = [
        ("hon_tai", "h1", "h2"),
        ("hon_tan", "h1", "h3"),
        ("hon_ren", "h1", "h4"),
        ("tai_tan", "h2", "h3"),
        ("tai_ren", "h2", "h4"),
        ("tan_ren", "h3", "h4"),
    ]
    hits = {pid: 0 for pid, _, _ in pairs}
    returned = {pid: 0 for pid, _, _ in pairs}
    box_hits = 0
    box_returned = 0
    n = 0

    for row in rows:
        h1 = int(row["h1"])
        h2 = int(row["h2"])
        h3 = int(row["h3"])
        h4 = int(row["h4"])
        u1 = int(row["u1"])
        u2 = int(row["u2"])
        nums = [h1, h2, h3, h4]
        if len(set(nums)) != 4:
            continue
        n += 1
        actual = _pair_key(u1, u2)
        pay = int(row.get("actual_payout_yen") or 0)
        hit_box = False
        hmap = {"h1": h1, "h2": h2, "h3": h3, "h4": h4}
        for pid, ka, kb in pairs:
            ha = hmap[ka]
            hb = hmap[kb]
            if _pair_key(ha, hb) == actual:
                hits[pid] += 1
                returned[pid] += pay
                hit_box = True
        if hit_box:
            box_hits += 1
            box_returned += pay

    def rate(hit: int) -> float | None:
        return round(hit / n, 4) if n > 0 else None

    tickets = 6
    box_inv = n * tickets * stake

    return {
        "denominator_races": n,
        "pattern_hit_rates": {
            "hon_tai_◎_〇": rate(hits["hon_tai"]),
            "hon_tan_◎_▲": rate(hits["hon_tan"]),
            "hon_ren_◎_△": rate(hits["hon_ren"]),
            "tai_tan_〇_▲": rate(hits["tai_tan"]),
            "tai_ren_〇_△": rate(hits["tai_ren"]),
            "tan_ren_▲_△": rate(hits["tan_ren"]),
        },
        "box_six_tickets": {
            "hit_rate": rate(box_hits),
            "recovery_rate": round(box_returned / box_inv, 4) if box_inv > 0 else None,
        },
    }


def fetch_ev_forecast_empirical_calibration_for_track(cur, track_code: str | None) -> dict | None:
    """
    同一競馬場コードに限定した期待値予想の過去実績（単勝印・馬連BOX）の参考的中率。
    テーブルが無い・データが無い場合は None。
    """
    cur.execute("SHOW TABLES LIKE %s", ("all_race_ai_profile_compare",))
    if not cur.fetchone():
        return None

    tc = (track_code or "").strip()
    if not tc:
        return None

    keys_sql = ", ".join(["%s"] * len(EV_PROFILE_KEYS))

    cur.execute(
        f"""
        SELECT
            COUNT(*) AS total_rows,
            SUM(CASE WHEN winner_umaban IS NOT NULL THEN 1 ELSE 0 END) AS with_winner,
            SUM(CASE WHEN match_honmei_winner = 1 THEN 1 ELSE 0 END) AS honmei_hits,
            SUM(CASE WHEN match_taikou_winner = 1 THEN 1 ELSE 0 END) AS taikou_hits,
            SUM(CASE WHEN match_honmei_or_taikou_winner = 1 THEN 1 ELSE 0 END) AS top2_hits
        FROM all_race_ai_profile_compare
        WHERE profile_key IN ({keys_sql})
          AND status = 'ok'
          AND track_code = %s
        """,
        (*EV_PROFILE_KEYS, tc),
    )
    tr = cur.fetchone() or {}
    w = int(tr.get("with_winner") or 0)

    def wr(num: int) -> float | None:
        return round(int(num) / w, 4) if w > 0 else None

    tansho = {
        "sample_races_with_winner": w,
        "honmei_hit_rate": wr(int(tr.get("honmei_hits") or 0)),
        "taikou_hit_rate": wr(int(tr.get("taikou_hits") or 0)),
        "top2_hit_rate": wr(int(tr.get("top2_hits") or 0)),
    }

    cur.execute(
        f"""
        SELECT
            a.ai_honmei_umaban AS h1,
            a.ai_taikou_umaban AS h2,
            a.ai_tanana_umaban AS h3,
            a.ai_renka_umaban AS h4,
            re1.horse_number AS u1,
            re2.horse_number AS u2,
            COALESCE(pum.payout_yen, 0) AS actual_payout_yen
        FROM all_race_ai_profile_compare a
        INNER JOIN race_results rr1 ON rr1.race_id = a.race_id AND rr1.finish_position = 1
        INNER JOIN race_entries re1 ON re1.race_id = rr1.race_id AND re1.horse_id = rr1.horse_id
        INNER JOIN race_results rr2 ON rr2.race_id = a.race_id AND rr2.finish_position = 2
        INNER JOIN race_entries re2 ON re2.race_id = rr2.race_id AND re2.horse_id = rr2.horse_id
        LEFT JOIN payouts pum ON pum.race_id = a.race_id AND pum.bet_type = 'umaren'
          AND pum.combination = CONCAT(
            LEAST(CAST(re1.horse_number AS UNSIGNED), CAST(re2.horse_number AS UNSIGNED)),
            '-',
            GREATEST(CAST(re1.horse_number AS UNSIGNED), CAST(re2.horse_number AS UNSIGNED))
          )
        WHERE a.profile_key IN ({keys_sql})
          AND a.status = 'ok'
          AND a.track_code = %s
          AND a.ai_honmei_umaban IS NOT NULL
          AND a.ai_taikou_umaban IS NOT NULL
          AND a.ai_tanana_umaban IS NOT NULL
          AND a.ai_renka_umaban IS NOT NULL
        """,
        (*EV_PROFILE_KEYS, tc),
    )
    urows = list(cur.fetchall() or [])
    umaren = _aggregate_umaren_rows(urows)

    return {
        "profile_key": "ev_forecast",
        "track_code": tc,
        "disclaimer": (
            "過去の同一競馬場における期待値予想の集計から算出した参考的中率です。"
            "本レースの真の当たり確率を保証するものではありません。"
        ),
        "tansho_marks": tansho,
        "umaren_box_top4": umaren,
    }


def fetch_p6_empirical_calibration_for_track(cur, track_code: str | None) -> dict | None:
    """後方互換エイリアス。"""
    return fetch_ev_forecast_empirical_calibration_for_track(cur, track_code)