#!/usr/bin/env python3
"""Import real-world fired DOPE from the real-data SQLite dataset into test cases.

Reads `real-data/dope.db` (the v_real_dope view), selects cross-validation-ready
sessions, normalizes them to the `TestCaseModel` JSON schema, and writes one
`real_*.json` per session into ../test_cases/ with `realworld: true` (merged into the
single `jvmTest` suite and scored against the wider field band per-case). Sessions whose
interior leave-one-out exceeds the field band — too sparse/rounded to cross-validate — are
excluded via EXCLUDE_SESSIONS so the merged gating suite stays clean.

Inclusion criteria (strict / cross-validation-ready):
  - provenance = 'real' (chronographed / trued / field-verified)
  - muzzle_velocity_fps present and a parseable zero_range
  - bullet has a G1 and/or G7 BC
  - >= 4 usable dope rows (beyond the zero) in a convertible unit (MIL/MOA/MRAD/in)

Key normalizations:
  - Units: MOA->mil (/3.43775), MRAD->mil (1:1), inches->mil (in / (range_yd*0.036)),
    metres->yards (*1.09361), inHg->hPa (*33.8639), twist '1:8'->8.0.
  - Sign: the dataset mixes conventions (some log come-up as +, some log drop as -).
    We re-sign every point by geometry against the zero:
        mil = sign(range_yd - zero_yd) * abs(converted_mil)
    so beyond-zero points are positive come-ups and below-zero points are hold-unders,
    matching the engine / existing curated cases.
  - A synthetic 0.0-mil anchor point is added at the zero range (fired=false).
  - Tier from environmental completeness (these all have confirmed fired impacts):
    >=85% -> "1", 40-84% -> "2", <40% -> "3".

Run:  python3 import_real_dope.py [--write]
Without --write it prints the summary only (no files written).
"""
from __future__ import annotations

import argparse
import math
import re
import sqlite3
import sys
from pathlib import Path
from typing import Optional

import json

CASES_DIR = Path(__file__).resolve().parent.parent / "test_cases"
DB_PATH = Path(__file__).resolve().parents[3] / "real-data" / "dope.db"

MOA_PER_MIL = 3.43775
YD_PER_M = 1.09361
HPA_PER_INHG = 33.8639
MIN_USABLE_ROWS = 4
DEFAULT_SIGHT_HEIGHT_IN = 1.5

# Sessions whose interior leave-one-out exceeds the field band (0.10/0.20 mil) — the data
# is too sparse / rounded to cross-validate. Excluded so the merged gating suite stays clean.
EXCLUDE_SESSIONS = {31, 40, 41, 403}

# Cartridges that should use the G1 model even when a G7 BC exists (rimfire / blunt;
# G7 form factors are poor approximations for these — matches the curated cases).
RIMFIRE_PAT = re.compile(r"\b(22\s*LR|\.22\s*LR|17\s*HMR|\.17\s*HMR|22\s*WMR|22\s*Mag)\b", re.I)


def to_mil(value: float, unit: str, range_yd: float) -> Optional[float]:
    """Convert an elevation value in MIL/MOA/MRAD/inches to mils (magnitude preserved)."""
    u = (unit or "").strip().upper()
    if u in ("MIL", "MRAD"):
        return value
    if u == "MOA":
        return value / MOA_PER_MIL
    if u == "IN":
        if range_yd <= 0:
            return None
        return value / (range_yd * 0.036)  # 1 mil subtends range_yd * 0.036 inches
    return None


def range_to_yd(value: float, unit: str) -> float:
    return value * YD_PER_M if (unit or "yd").strip().lower() == "m" else value


def parse_zero_yd(zero_range: Optional[str]) -> Optional[float]:
    """Parse '100 yd' / '100 m' / '98 yd' -> yards."""
    if not zero_range:
        return None
    m = re.search(r"(-?\d+(?:\.\d+)?)\s*(yd|yds|yard|yards|m|meter|metre|meters|metres)?", zero_range, re.I)
    if not m:
        return None
    val = float(m.group(1))
    unit = (m.group(2) or "yd").lower()
    return val * YD_PER_M if unit.startswith("m") and unit != "mm" else val


def parse_twist_in(twist_rate: Optional[str]) -> float:
    """Parse '1:8' / '1:9.125' / '10' -> inches per turn (0.0 if unknown)."""
    if not twist_rate:
        return 0.0
    s = str(twist_rate).strip()
    if ":" in s:
        s = s.split(":", 1)[1]
    m = re.search(r"\d+(?:\.\d+)?", s)
    return float(m.group(0)) if m else 0.0


def slug(text: Optional[str]) -> str:
    return re.sub(r"[^a-z0-9]+", "", (text or "").lower())


def choose_bc(cartridge: str, bc_g1: Optional[float], bc_g7: Optional[float]) -> tuple[Optional[str], Optional[float]]:
    rimfire = bool(RIMFIRE_PAT.search(cartridge or ""))
    if not rimfire and bc_g7:
        return "G7", bc_g7
    if bc_g1:
        return "G1", bc_g1
    if bc_g7:
        return "G7", bc_g7
    return None, None


def build_case(session_id: int, rows: list[sqlite3.Row]) -> Optional[dict]:
    """Build a test-case dict from one session's denormalized rows, or None if unusable."""
    s = rows[0]
    if s["muzzle_velocity_fps"] is None:
        return None
    zero_yd = parse_zero_yd(s["zero_range"])
    if zero_yd is None:
        return None
    bc_model, bc = choose_bc(s["cartridge"], s["bc_g1"], s["bc_g7"])
    if bc_model is None:
        return None

    # Convert + sign-normalize each dope row; dedupe by rounded yardage.
    by_yd: dict[float, dict] = {}
    used_inches = False
    for r in rows:
        if r["elevation_value"] is None:
            continue
        yd = round(range_to_yd(r["range_value"], r["range_unit"]), 0)
        if yd <= 0:
            continue
        mag = to_mil(r["elevation_value"], r["elevation_unit"], yd)
        if mag is None:
            continue
        if (r["elevation_unit"] or "").strip().upper() == "IN":
            used_inches = True
        sign = 0.0 if yd == zero_yd else math.copysign(1.0, yd - zero_yd)
        mil = sign * abs(mag)
        by_yd[yd] = {
            "yards": yd,
            "mil": round(mil, 3),
            "moa": round(mil * MOA_PER_MIL, 2),
            "source": "real_field_dope",
            "fired": bool(r["observed"] == 1),
        }

    usable = [p for p in by_yd.values() if p["yards"] != zero_yd]
    if len(usable) < MIN_USABLE_ROWS:
        return None

    # No synthetic zero anchor: the physics pass already zeroes at zero_yards, and
    # injecting a (zero, 0.0) DOPE entry distorts the bias spline for sparse real data
    # whose points start far from the zero. Use only the observed rows.
    dope_points = sorted(by_yd.values(), key=lambda p: p["yards"])

    # Conditions + environmental completeness.
    press_hpa = round(s["pressure_inhg"] * HPA_PER_INHG, 1) if s["pressure_inhg"] is not None else 0
    field_present = {
        "altitude_ft": s["altitude_ft"] is not None,
        "temp_f": s["temp_f"] is not None,
        "pressure_hpa": s["pressure_inhg"] is not None,
        "humidity_pct": s["humidity_pct"] is not None,
    }
    confirmed = [k for k, present in field_present.items() if present]
    env_pct = len(confirmed) / 4.0
    n_fired = sum(1 for p in usable if p["fired"])
    # Tier 1/2/3 require confirmed fired impacts; with none, it is cross-calculator only.
    if n_fired == 0:
        tier = "Sub-3"
    else:
        tier = "1" if env_pct >= 0.85 else "2" if env_pct >= 0.40 else "3"

    weight = s["bullet_weight_gr"]
    # bullet_model sometimes embeds the weight (e.g. "180gr ELD-M"); strip it to avoid doubling.
    model_clean = re.sub(r"^\s*\d+(?:\.\d+)?\s*gr\s*", "", s["bullet_model"] or "", flags=re.I).strip()
    load_name = " ".join(
        x for x in [s["bullet_mfr"], f"{weight:g}gr" if weight else None, model_clean or s["bullet_model"]] if x
    ) or "Unknown load"

    model_tag = slug(model_clean.split()[0]) if model_clean else slug(s["bullet_mfr"])
    case_id = "_".join(
        x for x in ["real", str(session_id), slug(s["cartridge"]),
                    f"{weight:g}{model_tag}" if weight else model_tag] if x
    )

    notes = (
        f"Real fired/observed DOPE (provenance=real, mv_method={s['mv_method'] or 'unknown'}). "
        f"Source: {s['source_author'] or 'n/a'} @ {s['source_site'] or 'n/a'}. "
        f"Elevation re-signed by geometry vs the {zero_yd:g}yd zero (dataset mixes drop/come-up sign conventions). "
        f"Sight height assumed {DEFAULT_SIGHT_HEIGHT_IN} in (not recorded); windage/wind dropped (elevation-only). "
        f"Env completeness {env_pct*100:.0f}% (known: {', '.join(confirmed) or 'none'}). "
        f"{n_fired}/{len(usable)} points are confirmed-observed (fired)"
        f"{'; none observed -> Sub-3 (cross-calculator only)' if n_fired == 0 else ''}."
    )
    if used_inches:
        notes += " Some points converted from inches via mil = in / (range_yd * 0.036)."

    case = {
        "id": case_id,
        "caliber": s["cartridge"],
        "load_name": load_name,
        "bc_model": bc_model,
        "bc": bc,
        "bc_source": s["bc_source"] or "",
        "mv_fps": s["muzzle_velocity_fps"],
        "mv_source": f"real_{s['mv_method'] or 'unknown'}",
        "bullet_weight_gr": weight or 0.0,
        "zero_yards": zero_yd,
        "sight_height_in": DEFAULT_SIGHT_HEIGHT_IN,
        "twist_rate_in": parse_twist_in(s["twist_rate"]),
        "conditions": {
            "altitude_ft": s["altitude_ft"] or 0,
            "temp_f": s["temp_f"] if s["temp_f"] is not None else 59.0,
            "pressure_hpa": press_hpa,
            "humidity_pct": s["humidity_pct"] or 0,
            "confirmed_fields": confirmed,
        },
        "dope_points": dope_points,
        "tier": tier,
        "realworld": True,
        "data_notes": notes,
        "source_urls": [s["source_url"]] if s["source_url"] else [],
    }
    return case


def main() -> int:
    ap = argparse.ArgumentParser(description=__doc__)
    ap.add_argument("--write", action="store_true", help="write real_*.json files (default: dry run)")
    ap.add_argument("--db", type=Path, default=DB_PATH, help=f"path to dope.db (default: {DB_PATH})")
    args = ap.parse_args()

    if not args.db.exists():
        print(f"[error] dataset not found: {args.db}", file=sys.stderr)
        return 1

    con = sqlite3.connect(str(args.db))
    con.row_factory = sqlite3.Row
    rows = con.execute(
        "SELECT * FROM v_real_dope ORDER BY session_id, range_value"
    ).fetchall()
    con.close()

    sessions: dict[int, list[sqlite3.Row]] = {}
    for r in rows:
        sessions.setdefault(r["session_id"], []).append(r)

    cases = []
    for sid, srows in sessions.items():
        if sid in EXCLUDE_SESSIONS:
            continue
        case = build_case(sid, srows)
        if case is not None:
            cases.append(case)
    cases.sort(key=lambda c: c["id"])

    print(f"Real sessions scanned: {len(sessions)}  ->  usable cases: {len(cases)}\n")
    print(f"  {'id':<34} {'caliber':<18} {'pts':>4} {'tier':>4} {'env%':>5}")
    print("  " + "-" * 70)
    for c in cases:
        fired = sum(1 for p in c["dope_points"] if p["fired"])
        env = len(c["conditions"]["confirmed_fields"]) * 25
        print(f"  {c['id']:<34} {c['caliber']:<18} {len(c['dope_points']):>4} "
              f"{c['tier']:>4} {env:>4}%   ({fired} fired)")

    if args.write:
        CASES_DIR.mkdir(parents=True, exist_ok=True)
        for c in cases:
            path = CASES_DIR / f"{c['id']}.json"
            path.write_text(json.dumps(c, indent=2) + "\n")
        print(f"\nWrote {len(cases)} files to {CASES_DIR}")
    else:
        print("\n(dry run — re-run with --write to emit files)")
    return 0


if __name__ == "__main__":
    sys.exit(main())
