#!/usr/bin/env python3
"""Import Apple Health Auto Export JSON/CSV files into health_data.db.

Recommended app setup: Google Drive + JSON.
This importer is intentionally schema-flexible because Health Auto Export can
emit slightly different field names depending on data type/app version.

It stores raw records plus normalized fields:
- record_type / metric
- start_date / end_date
- value / unit
- source/device
- raw_json for traceability

It is idempotent via a SHA256 fingerprint over the normalized record + raw JSON.
"""
from __future__ import annotations

import argparse
import csv
import hashlib
import json
import os
import shutil
import sqlite3
from datetime import datetime
from pathlib import Path
from typing import Any, Iterable

BASE = Path.home() / ".hermes" / "assets" / "Gesundheit"
DB = BASE / "health_data.db"
INBOX = BASE / "inbox" / "apple_health_exports"
PROCESSED = BASE / "processed" / "apple_health_exports"


def con() -> sqlite3.Connection:
    c = sqlite3.connect(DB)
    c.row_factory = sqlite3.Row
    return c


def ensure_schema(c: sqlite3.Connection) -> None:
    c.execute(
        """
        CREATE TABLE IF NOT EXISTS apple_health_records (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            record_type TEXT,
            metric TEXT,
            start_date TEXT,
            end_date TEXT,
            value REAL,
            value_text TEXT,
            unit TEXT,
            source_name TEXT,
            source_version TEXT,
            device TEXT,
            file_name TEXT,
            file_hash TEXT,
            record_hash TEXT UNIQUE,
            raw_json TEXT NOT NULL,
            imported_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
        """
    )
    c.execute("CREATE INDEX IF NOT EXISTS idx_apple_health_type_date ON apple_health_records(record_type,start_date)")
    c.execute("CREATE INDEX IF NOT EXISTS idx_apple_health_metric_date ON apple_health_records(metric,start_date)")
    c.execute("CREATE INDEX IF NOT EXISTS idx_apple_health_file_hash ON apple_health_records(file_hash)")
    c.execute(
        """
        CREATE TABLE IF NOT EXISTS apple_health_import_files (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            file_name TEXT,
            file_path TEXT,
            file_hash TEXT UNIQUE,
            record_count INTEGER,
            inserted_count INTEGER,
            skipped_count INTEGER,
            status TEXT,
            error TEXT,
            imported_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
        """
    )
    columns = {row[1] for row in c.execute("PRAGMA table_info(apple_health_import_files)")}
    if "retry_count" not in columns:
        c.execute("ALTER TABLE apple_health_import_files ADD COLUMN retry_count INTEGER NOT NULL DEFAULT 0")
    if "last_attempt_at" not in columns:
        c.execute("ALTER TABLE apple_health_import_files ADD COLUMN last_attempt_at TEXT")
    c.commit()


def sha(s: bytes | str) -> str:
    if isinstance(s, str):
        s = s.encode("utf-8", "replace")
    return hashlib.sha256(s).hexdigest()


def first(d: dict[str, Any], keys: list[str]) -> Any:
    # exact first
    for k in keys:
        if k in d and d[k] not in (None, ""):
            return d[k]
    # case/space insensitive fallback
    norm = {str(k).lower().replace(" ", "_").replace("-", "_"): v for k, v in d.items()}
    for k in keys:
        nk = k.lower().replace(" ", "_").replace("-", "_")
        if nk in norm and norm[nk] not in (None, ""):
            return norm[nk]
    return None


def parse_float(x: Any) -> float | None:
    if x in (None, ""):
        return None
    try:
        return float(str(x).replace(",", ".").strip())
    except Exception:
        return None


def normalize_record(r: dict[str, Any], file_name: str, file_hash: str) -> dict[str, Any]:
    record_type = first(r, ["type", "record_type", "RecordType", "identifier", "name", "metric", "quantityType"])
    metric = first(r, ["metric", "name", "type", "identifier", "quantityType"])
    start = first(r, ["startDate", "start_date", "start", "date", "timestamp", "Date", "startTime"])
    end = first(r, ["endDate", "end_date", "end", "End", "endTime"])
    value_raw = first(r, ["value", "Value", "qty", "quantity", "count", "duration", "totalSleep", "asleep", "result", "systolic", "diastolic"])
    unit = first(r, ["unit", "Unit", "units"])
    source = first(r, ["sourceName", "source_name", "source", "Source", "app"])
    source_version = first(r, ["sourceVersion", "source_version"])
    device = first(r, ["device", "Device", "deviceName"])
    raw = json.dumps(r, ensure_ascii=False, sort_keys=True)
    fp_basis = json.dumps({
        "record_type": record_type,
        "metric": metric,
        "start_date": start,
        "end_date": end,
        "value": value_raw,
        "unit": unit,
        "source": source,
        "raw": r,
    }, ensure_ascii=False, sort_keys=True)
    return {
        "record_type": str(record_type or "unknown"),
        "metric": str(metric or record_type or "unknown"),
        "start_date": str(start or ""),
        "end_date": str(end or start or ""),
        "value": parse_float(value_raw),
        "value_text": None if value_raw is None else str(value_raw),
        "unit": None if unit is None else str(unit),
        "source_name": None if source is None else str(source),
        "source_version": None if source_version is None else str(source_version),
        "device": None if device is None else str(device),
        "file_name": file_name,
        "file_hash": file_hash,
        "record_hash": sha(fp_basis),
        "raw_json": raw,
    }


def iter_json_records(obj: Any) -> Iterable[dict[str, Any]]:
    if isinstance(obj, list):
        for x in obj:
            if isinstance(x, dict):
                yield x
        return
    if isinstance(obj, dict):
        # Health Auto Export common shape:
        # {"data":{"metrics":[{"name":"steps","units":"count","data":[{"qty":...,"date":...}]}]}}
        metrics = obj.get("data", {}).get("metrics") if isinstance(obj.get("data"), dict) else obj.get("metrics")
        if isinstance(metrics, list):
            for m in metrics:
                if not isinstance(m, dict):
                    continue
                name = m.get("name") or m.get("type") or m.get("identifier")
                units = m.get("units") or m.get("unit")
                rows = m.get("data")
                if isinstance(rows, list):
                    for x in rows:
                        if isinstance(x, dict):
                            y = dict(x)
                            y.setdefault("type", name)
                            y.setdefault("metric", name)
                            y.setdefault("unit", units)
                            yield y
            return
        # Common wrappers: records, samples, workouts, items, results.
        for key in ["records", "samples", "workouts", "body", "items", "results"]:
            val = obj.get(key)
            if isinstance(val, list):
                for x in val:
                    if isinstance(x, dict):
                        yield x
                return
        # `data` may itself be a list in some app versions.
        val = obj.get("data")
        if isinstance(val, list):
            for x in val:
                if isinstance(x, dict):
                    yield x
            return
        # Dict of arrays by metric.
        emitted = False
        for k, val in obj.items():
            if isinstance(val, list):
                for x in val:
                    if isinstance(x, dict):
                        y = dict(x)
                        y.setdefault("type", k)
                        yield y
                        emitted = True
        if emitted:
            return
        # Single record fallback.
        yield obj


def load_records(path: Path) -> list[dict[str, Any]]:
    suffix = path.suffix.lower()
    if suffix == ".json":
        obj = json.loads(path.read_text(encoding="utf-8-sig"))
        return list(iter_json_records(obj))
    if suffix == ".csv":
        with path.open("r", encoding="utf-8-sig", newline="") as f:
            return list(csv.DictReader(f))
    raise ValueError(f"Unsupported file type: {path.suffix}")


def archive_file(path: Path) -> Path:
    PROCESSED.mkdir(parents=True, exist_ok=True)
    target = PROCESSED / f"{datetime.now():%Y%m%d_%H%M%S_%f}_{path.name}"
    shutil.move(str(path), str(target))
    return target


def record_import_error(c: sqlite3.Connection, path: Path, file_hash: str, error: Exception) -> None:
    c.execute(
        """INSERT INTO apple_health_import_files
           (file_name,file_path,file_hash,record_count,inserted_count,skipped_count,status,error,retry_count,last_attempt_at)
           VALUES(?,?,?,?,?,?,?,?,1,CURRENT_TIMESTAMP)
           ON CONFLICT(file_hash) DO UPDATE SET
             file_name=excluded.file_name,
             file_path=excluded.file_path,
             status='error',
             error=excluded.error,
             retry_count=COALESCE(apple_health_import_files.retry_count,0)+1,
             last_attempt_at=CURRENT_TIMESTAMP""",
        (path.name, str(path), file_hash, 0, 0, 0, "error", str(error)),
    )
    c.commit()


def import_file(path: Path, move: bool = False) -> dict[str, Any]:
    b = path.read_bytes()
    fh = sha(b)
    c = con()
    ensure_schema(c)
    existing = c.execute("SELECT id,status,retry_count FROM apple_health_import_files WHERE file_hash=?", (fh,)).fetchone()
    if existing and existing["status"] == "imported":
        try:
            known_archive = str(archive_file(path)) if move and path.exists() else None
            return {"file": str(path), "status": "already_imported", "inserted": 0, "skipped": 0, "archived_to": known_archive}
        finally:
            c.close()
    archived_to: Path | None = None
    try:
        records = load_records(path)
        inserted = skipped = 0
        c.execute("BEGIN IMMEDIATE")
        if existing:
            # Remove partial data left by older non-transactional importer runs.
            c.execute("DELETE FROM apple_health_records WHERE file_hash=?", (fh,))
            c.execute("DELETE FROM apple_health_import_files WHERE file_hash=?", (fh,))
        for r in records:
            n = normalize_record(r, path.name, fh)
            try:
                c.execute(
                    """
                    INSERT INTO apple_health_records
                    (record_type,metric,start_date,end_date,value,value_text,unit,source_name,source_version,device,file_name,file_hash,record_hash,raw_json)
                    VALUES (:record_type,:metric,:start_date,:end_date,:value,:value_text,:unit,:source_name,:source_version,:device,:file_name,:file_hash,:record_hash,:raw_json)
                    """,
                    n,
                )
                inserted += 1
            except sqlite3.IntegrityError as exc:
                # Only the expected record-hash collision is a duplicate.
                # Any other constraint failure invalidates the complete file.
                if "UNIQUE constraint failed: apple_health_records.record_hash" in str(exc):
                    skipped += 1
                else:
                    raise
        c.execute(
            """INSERT INTO apple_health_import_files
               (file_name,file_path,file_hash,record_count,inserted_count,skipped_count,status,error,retry_count,last_attempt_at)
               VALUES(?,?,?,?,?,?,?,'',?,CURRENT_TIMESTAMP)""",
            (path.name, str(path), fh, len(records), inserted, skipped, "imported", int(existing["retry_count"] or 0) if existing else 0),
        )
        if move:
            archived_to = archive_file(path)
        c.commit()
        return {"file": str(path), "status": "imported", "records": len(records), "inserted": inserted, "skipped": skipped}
    except Exception as e:
        c.rollback()
        if archived_to is not None and archived_to.exists() and not path.exists():
            path.parent.mkdir(parents=True, exist_ok=True)
            shutil.move(str(archived_to), str(path))
        record_import_error(c, path, fh, e)
        raise
    finally:
        c.close()


def main() -> None:
    ap = argparse.ArgumentParser()
    ap.add_argument("paths", nargs="*", help="JSON/CSV files or directories. Default: apple health inbox")
    ap.add_argument("--move", action="store_true", help="Move imported files to processed/apple_health_exports")
    args = ap.parse_args()
    paths = [Path(p).expanduser() for p in args.paths] or [INBOX]
    files: list[Path] = []
    for p in paths:
        if p.is_dir():
            files.extend(sorted([*p.glob("*.json"), *p.glob("*.csv")]))
        elif p.exists():
            files.append(p)
    results = [import_file(f, move=args.move) for f in files]
    print(json.dumps({"files_seen": len(files), "results": results}, ensure_ascii=False, indent=2))


if __name__ == "__main__":
    main()