#!/usr/bin/env python3
"""
Convert ancillary logger files to Parquet, one file per source per UTC day:

    <out>/<source>/<source>_<YYYY-MM-DD>.parquet

Every file has a 'time' column (UTC, ns) and units in the column names.

Input files are recognised by their content, not their name:

  rfpower      CSV, no header:  time, ADC1_raw_data, ADC3_raw_data,
               apex_temperature_C, rf_power_dBm                      (~10 Hz);
               only time and rf_power_dBm are kept
  temperature  JSON lines {"timestamp", "sensors": [{"rom", "celsius", ...}]}
               from the 1-wire sensors and the outside-temperature export;
               SENSORS below decides which source and column each ROM goes to
  powerclamp   JSON {"start_time", "offset_unit", "columns": ["offset", "value"],
               "data": [...]}; a file that is still being written (no closing
               brackets) is read up to its last complete row

All raw files of a source are read together, exact duplicate rows are dropped and
the day files are rewritten, so running this again after more data arrived (or on
overlapping files) is safe. Raw files are never modified.

Usage:
  python ingest_ancillary.py ancillary_raw/ [more files or dirs] -o ancillary/

Reading back:
  from ingest_ancillary import load_ancillary
  temp = load_ancillary("ancillary", "temperature", "2026-10-17 08:00", "2026-10-17 14:00")
"""

import argparse
import json
import re
import sys
from pathlib import Path

import pandas as pd

# --------------------------------------------------------------------------
# Things to adapt
# --------------------------------------------------------------------------

# 1-wire / exported sensors: ROM -> (source, column prefix)
SENSORS = {
    "28-FF-66-8A-00-16-03-A3": ("temperature", "pa_input"),
    "28-FF-2F-09-01-16-03-43": ("temperature", "pa_output"),
    "28-FF-8A-8E-A1-15-03-5E": ("temperature", "feed"),
    "weer":                    ("outside_temperature", "outside"),
}

# Temperature readings outside this range, or isolated jumps of more than SPIKE_C
# relative to both neighbouring readings, are rejected (set to NaN, flagged suspicious).
# A DS18B20 reads 85.0 °C after a power glitch, without flagging it.
PLAUSIBLE_C = (-40.0, 70.0)
SPIKE_C = 5.0

RFPOWER_COLUMNS =["time", "adc1_raw", "adc3_raw", "apex_temperature_c", "rf_power_dbm"]
# Only these are kept: the raw ADC values are superseded by rf_power_dbm, and the
# apex (indoor) temperature is too coarse to be useful.
RFPOWER_KEEP = {"time": None, "rf_power_dbm": "float32"}

# Name of the power clamp value column (the logger reports kW).
POWERCLAMP_VALUE = "power_kw"

SUFFIXES = {".csv", ".json", ".jsonl", ".txt", ".log"}

# --------------------------------------------------------------------------


def parse_times(s):
    """ISO 8601 times (any offset, any precision) -> UTC."""
    try:
        return pd.to_datetime(s, utc=True, format="ISO8601")   # pandas >= 2.0
    except (ValueError, TypeError):
        return pd.to_datetime(s, utc=True)                      # pandas 1.x


def detect(path: Path) -> str | None:
    """Which reader a file needs, from its first bytes."""
    with open(path, "r", errors="replace") as f:
        head = f.read(4096)
    first = head.lstrip()[:1]
    if first == "{":
        if '"sensors"' in head.split("\n", 1)[0]:
            return "sensors_jsonl"
        if '"start_time"' in head and '"data"' in head:
            return "powerclamp"
    if re.match(r"\s*\d{4}-\d\d-\d\dT[\d:.]+Z?\s*,", head):
        if head.split("\n", 1)[0].count(",") == len(RFPOWER_COLUMNS) - 1:
            return "rfpower"
    return None


# --------------------------------------------------------------------------
# Readers: each returns {source: long or wide DataFrame with a 'time' column}
# --------------------------------------------------------------------------

def read_rfpower(path: Path) -> dict:
    df = pd.read_csv(path, header=None, names=RFPOWER_COLUMNS, skipinitialspace=True,
                     usecols=list(RFPOWER_KEEP))
    df["time"] = parse_times(df["time"])
    return {"rfpower": df.astype({c: t for c, t in RFPOWER_KEEP.items() if t})}


def read_sensors_jsonl(path: Path) -> dict:
    rows, unknown = [], set()
    with open(path) as f:
        for n, line in enumerate(f, 1):
            line = line.strip()
            if not line:
                continue
            try:
                rec = json.loads(line)
            except json.JSONDecodeError:
                print(f"warning: {path.name}:{n}: unreadable line skipped", file=sys.stderr)
                continue
            for s in rec.get("sensors", []):
                if s.get("rom") not in SENSORS:
                    unknown.add(s.get("rom"))
                    continue
                source, col = SENSORS[s["rom"]]
                rows.append((source, rec["timestamp"], col, s.get("celsius"),
                             s.get("suspicious")))
    if unknown:
        print(f"warning: {path.name}: unknown sensor ROM(s) {sorted(unknown)}; "
              "add them to SENSORS", file=sys.stderr)
    long = pd.DataFrame(rows, columns=["source", "time", "sensor", "celsius", "suspicious"])
    long["time"] = parse_times(long["time"])
    return {src: g.drop(columns="source") for src, g in long.groupby("source")}


def read_powerclamp(path: Path) -> dict:
    text = path.read_text()
    try:
        doc = json.loads(text)
    except json.JSONDecodeError:
        # Still being written: cut after the last complete row and close the brackets.
        cut = text.rfind("]")
        doc = json.loads(text[:cut + 1] + "]}")
        print(f"warning: {path.name}: incomplete file, read {len(doc['data'])} rows",
              file=sys.stderr)
    unit = {"s": "s", "ms": "ms", "us": "us"}[doc.get("offset_unit", "s")]
    df = pd.DataFrame(doc["data"], columns=doc.get("columns", ["offset", "value"]))
    start = pd.Timestamp(doc["start_time"]).tz_convert("UTC")
    df["time"] = start + pd.to_timedelta(df["offset"], unit=unit)
    df = df.rename(columns={"value": POWERCLAMP_VALUE})
    return {"powerclamp": df[["time", POWERCLAMP_VALUE]].astype({POWERCLAMP_VALUE: "float32"})}


READERS = {"rfpower": read_rfpower, "sensors_jsonl": read_sensors_jsonl,
           "powerclamp": read_powerclamp}


def reject_outliers(long: pd.DataFrame) -> pd.DataFrame:
    """Blank out temperature readings that can't be right and flag them suspicious:
    readings the sensor itself flagged, readings outside PLAUSIBLE_C, and isolated
    spikes (more than SPIKE_C away from both neighbouring readings, in the same
    direction). The row stays, with celsius NaN, so the dropout remains visible."""
    long = long.sort_values(["sensor", "time"]).copy()
    long["suspicious"] = long["suspicious"].fillna(False).astype(bool)
    c = long["celsius"]
    by = long.groupby("sensor")["celsius"]
    d_prev, d_next = c - by.shift(1), c - by.shift(-1)
    spike = ((d_prev > SPIKE_C) & (d_next > SPIKE_C)) | ((d_prev < -SPIKE_C) & (d_next < -SPIKE_C))
    implausible = (c < PLAUSIBLE_C[0]) | (c > PLAUSIBLE_C[1])
    bad = implausible | spike
    for _, r in long[bad].iterrows():
        why = "outside plausible range" if implausible[_] else "spike"
        print(f"  rejected {r['sensor']} {r['time']:%Y-%m-%d %H:%M:%S}: {r['celsius']} °C ({why})")
    long.loc[bad, "suspicious"] = True
    long.loc[long["suspicious"], "celsius"] = float("nan")
    return long


def to_wide(long: pd.DataFrame) -> pd.DataFrame:
    """Sensor readings (time, sensor, celsius, suspicious) -> one row per time,
    columns <sensor>_c and <sensor>_suspicious."""
    g = long.groupby(["time", "sensor"])
    temp = g["celsius"].first().unstack("sensor").astype("float32")
    temp.columns = [f"{c}_c" for c in temp.columns]
    sus = g["suspicious"].max().unstack("sensor").fillna(False).astype("boolean")
    sus.columns = [f"{c}_suspicious" for c in sus.columns]
    out = temp.join(sus)
    out.columns.name = None
    return out.sort_index(axis=1).reset_index()


# --------------------------------------------------------------------------

def find_files(paths):
    for p in paths:
        p = Path(p)
        if p.is_dir():
            yield from sorted(q for q in p.rglob("*") if q.is_file() and q.suffix in SUFFIXES)
        elif p.is_file():
            yield p
        else:
            print(f"warning: {p} not found", file=sys.stderr)


def summarize(source: str, df: pd.DataFrame) -> str:
    dt = df["time"].diff().dt.total_seconds()
    med = dt.median()
    gaps = dt[dt > max(10 * med, 120)]   # slower sampling (e.g. 60 s) isn't a gap
    s = (f"  {source:<20} {df['time'].iloc[0]:%Y-%m-%d %H:%M:%S} – "
         f"{df['time'].iloc[-1]:%Y-%m-%d %H:%M:%S}  {len(df):>8,} rows, "
         f"median step {med:.3g} s")
    if len(gaps):
        s += f", {len(gaps)} gap(s), longest {gaps.max() / 60:.0f} min"
    return s


def ingest(paths, out: Path):
    collected: dict[str, list] = {}
    for path in find_files(paths):
        kind = detect(path)
        if kind is None:
            print(f"skipped (format not recognised): {path}", file=sys.stderr)
            continue
        for source, df in READERS[kind](path).items():
            collected.setdefault(source, []).append(df)
        print(f"read {path}  [{kind}]")

    print("\nWritten:")
    for source, parts in sorted(collected.items()):
        df = pd.concat(parts, ignore_index=True).drop_duplicates()
        if "sensor" in df.columns:
            df = to_wide(reject_outliers(df))
        df = df.sort_values("time", kind="stable").reset_index(drop=True)
        print(summarize(source, df))
        d = out / source
        d.mkdir(parents=True, exist_ok=True)
        for day, g in df.groupby(df["time"].dt.date):
            g.to_parquet(d / f"{source}_{day}.parquet", compression="zstd", index=False)
        print(f"  {'':<20} -> {d}/ ({df['time'].dt.date.nunique()} day file(s))")


def load_ancillary(root, source, start=None, end=None) -> pd.DataFrame:
    """All rows of one source between start and end (UTC; strings or Timestamps)."""
    def utc(t):
        if t is None:
            return None
        t = pd.Timestamp(t)
        return t.tz_localize("UTC") if t.tzinfo is None else t.tz_convert("UTC")
    start, end = utc(start), utc(end)
    files = []
    for f in sorted((Path(root) / source).glob(f"{source}_*.parquet")):
        day = pd.Timestamp(f.stem.rsplit("_", 1)[1], tz="UTC")
        if (start is None or day + pd.Timedelta(1, unit="D") > start) and (end is None or day <= end):
            files.append(f)
    if not files:
        raise FileNotFoundError(f"no {source} data in {root} for the requested period")
    df = pd.concat([pd.read_parquet(f) for f in files], ignore_index=True)
    df["time"] = pd.to_datetime(df["time"], utc=True)
    if start is not None:
        df = df[df["time"] >= start]
    if end is not None:
        df = df[df["time"] <= end]
    return df.reset_index(drop=True)


def main(argv=None):
    ap = argparse.ArgumentParser(description=__doc__,
                                 formatter_class=argparse.RawDescriptionHelpFormatter)
    ap.add_argument("inputs", nargs="+", help="raw files or directories (searched recursively)")
    ap.add_argument("-o", "--out", type=Path, default=Path("ancillary"),
                    help="output directory (default: ancillary/)")
    args = ap.parse_args(argv)
    ingest(args.inputs, args.out)


if __name__ == "__main__":
    main()
