#!/usr/bin/env python3
"""One-shot v1 SQLite -> v2 Postgres migration.

Runs INSIDE the garden2-app container (needs psycopg + the /data mount):

    docker exec garden2-app python scripts/migrate_v1.py \
        --sqlite /data/v1-snapshot/garden-frozen-YYYYMMDD.db [--wipe]

Design (per the approved plan):
- Idempotent: refuses a non-empty target unless --wipe (truncate + reload).
- ONE transaction: any failure rolls back everything.
- Seeds identity rows first: users(rian) / gardens(1) / garden_members /
  assistants (placeholder persona — named later in the UI).
- Original v1 ids preserved (identity columns are BY DEFAULT); sequences
  setval'd afterwards.
- Value transforms are ONLY the mechanical type maps (ts/bool/date) from
  scripts/canonical.py + garden_id=1 on scoped tables. quantity, positions,
  photo_paths, tool_calls are copied byte-for-byte.
- Derives the v2 dual-voice layer: one `notes` row per bodied v1 field_note
  (author='user', body VERBATIM — no AI rewriting), targets mirrored to
  note_targets. field_notes itself is carried as the immutable raw log.
- field_notes.parent_id is a real FK in v2 -> two-pass (insert NULL, then
  UPDATE). areas.parent_id has no FK (ported faithfully) -> direct insert.
"""
from __future__ import annotations

import argparse
import os
import sqlite3
import sys

sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
from canonical import parse_date, parse_ts  # noqa: E402

import psycopg  # noqa: E402
from psycopg.rows import dict_row  # noqa: E402

GARDEN_ID = 1
V1_USER = "rian"

# (table, scoped_by_garden, columns_carried_verbatim)
# Column lists come from the pragma dump; ts/date/bool transforms applied by
# COLUMN_KINDS in canonical.py. Order respects FKs.
TABLES = [
    ("species", True),
    ("varieties", False),
    ("years", True),
    ("areas", True),
    ("watering_stations", True),
    ("area_stations", False),
    ("plantings", True),
    ("plants", True),
    ("supplies", True),
    ("field_notes", True),          # two-pass parent_id
    ("field_note_targets", False),
    ("overview_versions", False),
    ("artifacts", True),
    ("chat_sessions", True),
    ("chat_messages", False),
    ("tool_suggestions", False),
    ("activity_log", True),
]

TS_COLS = {
    "created_at", "captured_at", "processed_at", "ended_at",
}
DATE_COLS = {"comment_date"}
BOOL_COLS = {"is_archived", "is_primary", "needed"}
# artifacts.generated_at is date-only in v1 but the v2 column is DATE
ARTIFACT_DATE_COLS = {"generated_at"}


def convert_value(table: str, col: str, value):
    if col in TS_COLS:
        return parse_ts(value)
    if col in DATE_COLS or (table == "artifacts" and col in ARTIFACT_DATE_COLS):
        return parse_date(value)
    if col in BOOL_COLS:
        return None if value is None else bool(int(value))
    return value


def main() -> None:
    ap = argparse.ArgumentParser(description=__doc__)
    ap.add_argument("--sqlite", required=True, help="frozen v1 garden.db path")
    ap.add_argument("--wipe", action="store_true",
                    help="truncate all v2 tables before loading")
    args = ap.parse_args()

    dsn = os.environ.get("DATABASE_URL", "")
    if not dsn:
        raise SystemExit("DATABASE_URL not set")
    # SQLAlchemy-style URL -> psycopg DSN
    dsn = dsn.replace("postgresql+psycopg://", "postgresql://")

    src = sqlite3.connect(f"file:{args.sqlite}?mode=ro", uri=True)
    src.row_factory = sqlite3.Row

    with psycopg.connect(dsn, row_factory=dict_row) as pg:
        with pg.cursor() as cur:
            # --- preconditions ---
            cur.execute("SELECT COUNT(*) AS n FROM areas")
            existing = cur.fetchone()["n"]
            if existing and not args.wipe:
                raise SystemExit(
                    f"target has data (areas={existing}); rerun with --wipe"
                )
            if args.wipe:
                all_tables = [t for t, _ in TABLES] + [
                    "note_targets", "notes", "note_media", "note_revisions",
                    "garden_members", "assistants", "gardens", "users",
                    "embeddings", "pipeline_runs", "resolver_feedback",
                    "ai_usage", "scheduled_actions", "care_rules",
                    "reminder_events", "notification_channels",
                    "notification_dispatches", "weather_daily",
                    "onboarding_sessions", "assistant_templates",
                ]
                cur.execute(
                    "TRUNCATE " + ", ".join(all_tables) + " RESTART IDENTITY CASCADE"
                )
                print(f"wiped {len(all_tables)} tables")

            # --- seed identity ---
            cur.execute(
                """INSERT INTO users (external_username, display_name, created_at)
                   VALUES (%s, %s, now()) RETURNING id""",
                (V1_USER, "Rian"),
            )
            user_id = cur.fetchone()["id"]
            cur.execute(
                """INSERT INTO gardens (id, name, timezone, created_at)
                   VALUES (%s, %s, %s, now())""",
                (GARDEN_ID, "Home Garden", "America/Vancouver"),
            )
            cur.execute("SELECT setval(pg_get_serial_sequence('gardens','id'), %s)",
                        (GARDEN_ID,))
            cur.execute(
                """INSERT INTO garden_members (garden_id, user_id, role, created_at)
                   VALUES (%s, %s, 'owner', now())""",
                (GARDEN_ID, user_id),
            )
            cur.execute(
                """INSERT INTO assistants
                   (garden_id, name, role, persona_prompt, is_active, created_at)
                   VALUES (%s, %s, 'primary', '', true, now())""",
                (GARDEN_ID, "Assistant"),
            )
            print(f"seeded identity: user #{user_id}, garden #{GARDEN_ID}, assistant")

            # v1 ran SQLite with FK enforcement OFF, so orphaned child rows
            # exist (e.g. field_note_targets pointing at deleted notes).
            # These are garbage, not data: skip them, count them, and the
            # diff harness applies the SAME rule to the v1 side (documented
            # deviation — see diff_harness.py I6).
            v1_note_ids = {
                r[0] for r in src.execute("SELECT id FROM field_notes")
            }
            v1_session_ids = {
                r[0] for r in src.execute("SELECT id FROM chat_sessions")
            }

            def orphan_filter(table: str, rows: list) -> tuple[list, int]:
                if table == "field_note_targets":
                    kept = [r for r in rows if r["field_note_id"] in v1_note_ids]
                elif table == "chat_messages":
                    kept = [r for r in rows if r["session_id"] in v1_session_ids]
                else:
                    return rows, 0
                return kept, len(rows) - len(kept)

            # --- carried tables ---
            for table, scoped in TABLES:
                rows = src.execute(f"SELECT * FROM {table}").fetchall()
                rows, skipped = orphan_filter(table, rows)
                if skipped:
                    print(f"{table}: SKIPPING {skipped} orphaned rows (v1 FK-off wart)")
                if not rows:
                    print(f"{table}: 0 rows")
                    continue
                cols = rows[0].keys()
                insert_cols = list(cols) + (["garden_id"] if scoped else [])

                two_pass_parent = table == "field_notes"
                if two_pass_parent:
                    insert_cols_pass1 = [c for c in insert_cols if c != "parent_id"]
                else:
                    insert_cols_pass1 = insert_cols

                placeholders = ", ".join(["%s"] * len(insert_cols_pass1))
                col_sql = ", ".join(insert_cols_pass1)
                stmt = f"INSERT INTO {table} ({col_sql}) VALUES ({placeholders})"

                batch = []
                for r in rows:
                    vals = [
                        convert_value(table, c, r[c])
                        for c in cols
                        if not (two_pass_parent and c == "parent_id")
                    ]
                    if scoped:
                        vals.append(GARDEN_ID)
                    batch.append(vals)
                cur.executemany(stmt, batch)

                if two_pass_parent:
                    for r in rows:
                        if r["parent_id"] is not None:
                            cur.execute(
                                "UPDATE field_notes SET parent_id=%s WHERE id=%s",
                                (r["parent_id"], r["id"]),
                            )

                if "id" in cols:
                    cur.execute(
                        f"SELECT setval(pg_get_serial_sequence('{table}','id'),"
                        f" (SELECT MAX(id) FROM {table}))"
                    )
                print(f"{table}: {len(rows)} rows")

            # --- derive dual-voice layer (deviation D2) ---
            # One notes row per v1 field_note with non-empty body; body copied
            # VERBATIM; targets mirrored. Keyed 1:1 by using the same id so
            # provenance is trivially auditable (note.id == field_note.id for
            # all migrated rows; new v2 notes continue above MAX(id)).
            cur.execute(
                """INSERT INTO notes
                     (id, garden_id, source_capture_id, author, body_md,
                      body_source, kind, note_date, is_pinned, created_at,
                      is_archived)
                   SELECT fn.id, fn.garden_id, fn.id, 'user', fn.body,
                          'user_raw', COALESCE(fn.kind, ''), fn.comment_date,
                          false, fn.created_at, COALESCE(fn.is_archived, false)
                   FROM field_notes fn
                   WHERE fn.body IS NOT NULL AND fn.body != ''"""
            )
            cur.execute("SELECT COUNT(*) AS n FROM notes")
            n_notes = cur.fetchone()["n"]
            cur.execute(
                """INSERT INTO note_targets
                     (note_id, target_type, target_id, is_primary,
                      link_source)
                   SELECT fnt.field_note_id, fnt.target_type, fnt.target_id,
                          COALESCE(fnt.is_primary, false), 'confirmed'
                   FROM field_note_targets fnt
                   JOIN notes n ON n.id = fnt.field_note_id"""
            )
            cur.execute("SELECT COUNT(*) AS n FROM note_targets")
            n_nt = cur.fetchone()["n"]
            cur.execute(
                "SELECT setval(pg_get_serial_sequence('notes','id'),"
                " (SELECT MAX(id) FROM notes))"
            )
            print(f"derived: {n_notes} notes, {n_nt} note_targets")

        pg.commit()
    print("MIGRATION COMMITTED")


if __name__ == "__main__":
    main()