#!/usr/bin/env python3
"""Backfill events.*_zc_freq / *_zc_above_range from .sfm.json sidecars.

Idempotent. Reads each event's sidecar bw_report and writes the per-channel
ZC frequency into the DB columns via a direct UPDATE. Events with no sidecar
(or no bw_report freq) are left untouched. Mirrors scripts/backfill_record_type.py.
"""
from __future__ import annotations
import argparse
import logging
import sys
from pathlib import Path

sys.path.insert(0, str(Path(__file__).resolve().parent.parent))

from sfm.database import SeismoDb
from sfm.waveform_store import WaveformStore

log = logging.getLogger("backfill_event_zc_freq")


def _freqs_from_sidecar(sidecar: dict) -> dict | None:
    bw = (sidecar or {}).get("bw_report") or {}
    peaks = bw.get("peaks") or {}
    mic = bw.get("mic") or {}
    out = {}
    for axis in ("tran", "vert", "long"):
        chd = peaks.get(axis) or {}
        out[f"{axis}_zc_freq"] = chd.get("zc_freq_hz")
        out[f"{axis}_zc_above_range"] = 1 if chd.get("zc_freq_above_range") else 0
    out["mic_zc_freq"] = mic.get("zc_freq_hz")
    out["mic_zc_above_range"] = 1 if mic.get("zc_freq_above_range") else 0
    # None if there's literally no frequency anywhere.
    if all(out[f"{a}_zc_freq"] is None for a in ("tran", "vert", "long", "mic")):
        return None
    return out


def backfill_zc_freq(db: SeismoDb, store, *, dry_run: bool = False) -> dict:
    counts = {"updated": 0, "skipped_no_sidecar": 0, "skipped_no_freq": 0}
    # Pull every event (paginate generously; adjust if a store has >100k events).
    rows = db.query_events(limit=1_000_000)
    for row in rows:
        serial = row.get("serial")
        filename = row.get("blastware_filename")
        if not serial or not filename:
            counts["skipped_no_sidecar"] += 1
            continue
        sidecar = store.load_sidecar(serial, filename)
        if sidecar is None:
            counts["skipped_no_sidecar"] += 1
            continue
        freqs = _freqs_from_sidecar(sidecar)
        if freqs is None:
            counts["skipped_no_freq"] += 1
            continue
        if dry_run:
            counts["updated"] += 1
            continue
        with db._connect() as conn:
            conn.execute(
                """UPDATE events SET
                     tran_zc_freq=?, vert_zc_freq=?, long_zc_freq=?, mic_zc_freq=?,
                     tran_zc_above_range=?, vert_zc_above_range=?,
                     long_zc_above_range=?, mic_zc_above_range=?
                   WHERE id=?""",
                (freqs["tran_zc_freq"], freqs["vert_zc_freq"], freqs["long_zc_freq"],
                 freqs["mic_zc_freq"], freqs["tran_zc_above_range"],
                 freqs["vert_zc_above_range"], freqs["long_zc_above_range"],
                 freqs["mic_zc_above_range"], row["id"]),
            )
        counts["updated"] += 1
    return counts


def main(argv=None) -> int:
    p = argparse.ArgumentParser(description=__doc__)
    p.add_argument("--db-path", default=str(
        Path(__file__).resolve().parent.parent / "bridges" / "captures" / "seismo_relay.db"))
    p.add_argument("--store-root", default=None)
    p.add_argument("--dry-run", action="store_true")
    p.add_argument("-v", "--verbose", action="store_true")
    args = p.parse_args(argv)
    logging.basicConfig(level=logging.DEBUG if args.verbose else logging.INFO)
    db_path = Path(args.db_path).expanduser().resolve()
    store_root = (
        Path(args.store_root).expanduser().resolve()
        if args.store_root else db_path.parent / "waveforms"
    )
    db = SeismoDb(db_path)
    store = WaveformStore(store_root)
    counts = backfill_zc_freq(db, store, dry_run=args.dry_run)
    log.info("backfill_zc_freq: %s", counts)
    return 0


if __name__ == "__main__":
    raise SystemExit(main())