#!/usr/bin/env python3
"""Export the v1 garden SQLite database as canonical JSON.

This is the LEFT side of the migration diff harness (the regression oracle).
Per /srv/projects/standards/react.md: "build a data-export diff harness first
— it's the before/after regression oracle that proves the port didn't
silently lose data."

Usage:
    python3 export_v1.py --db /srv/apps/garden/data/garden.db \
                         --out snapshot_v1.json

The output shape is::

    {
      "meta": {"exported_at": "...", "db_path": "...", "table_counts": {...}},
      "tables": {"areas": [ {row}, ... sorted by id ], ...}
    }

Rows are dicts keyed by column name, values exactly as SQLite returns them
(no normalization here — canonicalization for comparison happens in
diff_harness.py so both sides are normalized by the SAME code).

The DB is opened read-only (mode=ro) — this script can never mutate v1.
"""
from __future__ import annotations

import argparse
import json
import sqlite3
import sys
from datetime import datetime, timezone

# All 17 v1 tables, verified against sqlite_master 2026-07-02.
# If v1 grows a table before cutover, the count check below catches it.
V1_TABLES = [
    "activity_log",
    "area_stations",
    "areas",
    "artifacts",
    "chat_messages",
    "chat_sessions",
    "field_note_targets",
    "field_notes",
    "overview_versions",
    "plantings",
    "plants",
    "species",
    "supplies",
    "tool_suggestions",
    "varieties",
    "watering_stations",
    "years",
]

# Tables without a single `id` PK; sorted by their natural key instead.
COMPOSITE_KEY_TABLES = {
    "area_stations": ("area_id", "station_id"),
}


def export(db_path: str) -> dict:
    con = sqlite3.connect(f"file:{db_path}?mode=ro", uri=True)
    con.row_factory = sqlite3.Row

    actual_tables = {
        r[0]
        for r in con.execute(
            "SELECT name FROM sqlite_master WHERE type='table' "
            "AND name NOT LIKE 'sqlite_%'"
        )
    }
    expected = set(V1_TABLES)
    if actual_tables != expected:
        missing = expected - actual_tables
        extra = actual_tables - expected
        raise SystemExit(
            f"v1 table set drifted — missing={sorted(missing)} "
            f"extra={sorted(extra)}. Update V1_TABLES before exporting."
        )

    tables: dict = {}
    counts: dict = {}
    for table in V1_TABLES:
        order = COMPOSITE_KEY_TABLES.get(table, ("id",))
        order_sql = ", ".join(order)
        rows = con.execute(f"SELECT * FROM {table} ORDER BY {order_sql}").fetchall()
        tables[table] = [dict(r) for r in rows]
        counts[table] = len(rows)

    con.close()
    return {
        "meta": {
            "exported_at": datetime.now(timezone.utc).isoformat(timespec="seconds"),
            "db_path": db_path,
            "table_counts": counts,
        },
        "tables": tables,
    }


def main() -> None:
    ap = argparse.ArgumentParser(description=__doc__)
    ap.add_argument("--db", required=True, help="path to v1 garden.db")
    ap.add_argument("--out", required=True, help="output JSON path")
    args = ap.parse_args()

    snapshot = export(args.db)
    with open(args.out, "w", encoding="utf-8") as f:
        json.dump(snapshot, f, ensure_ascii=False, sort_keys=True, indent=1)

    total = sum(snapshot["meta"]["table_counts"].values())
    print(f"exported {total} rows across {len(V1_TABLES)} tables -> {args.out}")
    for t, n in sorted(snapshot["meta"]["table_counts"].items()):
        print(f"  {t}: {n}")


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