terra-view/backend/migrate_deprecate_deployment_records.py

"""
Migration: deprecate the `deployment_records` table.

Why:
  The deployment-history view on the unit detail page used to render
  from `deployment_records` — a manually-maintained table that drifted
  out of sync with `unit_assignments` (the auto-written project/location
  assignment table).  That caused the "wonky timeline" symptom: missing
  entries, duplicate / contradictory rows, and a UI that couldn't tell
  the operator what the unit was actually doing during each window.

  Phase 4 of the SFM integration replaces the deployment-history view
  with a derived timeline computed from `unit_assignments` +
  `unit_history` + SFM event overlay.  This migration is the cleanup:

  1. Adds a `deprecated_at` timestamp column to `deployment_records` so
     we can mark rows that have been migrated.
  2. For every `deployment_records` row that does NOT have a matching
     `unit_assignments` row (matched by unit_id + overlapping date
     range), synthesizes a best-effort UnitAssignment row.  The
     free-text `location_name` from the legacy table is preserved on
     the new row's `notes` field (we do NOT try to fuzzy-match it to a
     MonitoringLocation id; too error-prone — operators will need to
     reattach those manually if they want).
  3. Marks every migrated deployment_records row with `deprecated_at`.

  This migration is non-destructive: deployment_records rows stay in
  the DB.  The actual `DROP TABLE` happens in a follow-up release after
  one operator cycle confirms nothing relies on the legacy data.

Idempotent: re-running the script is a no-op if the column already
exists and all migratable rows have already been processed.

Run with:
  docker exec terra-view-terra-view-1 python3 /app/backend/migrate_deprecate_deployment_records.py
"""

import os
import sqlite3
import uuid
from datetime import datetime

DB_PATH = "./data/seismo_fleet.db"


def migrate_database():
    if not os.path.exists(DB_PATH):
        print(f"Database not found at {DB_PATH}")
        return

    print(f"Migrating database: {DB_PATH}")
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    cur = conn.cursor()

    # 1. Add deprecated_at column if not present.
    cur.execute("PRAGMA table_info(deployment_records)")
    cols = {row["name"] for row in cur.fetchall()}
    if "deprecated_at" not in cols:
        print("Adding deployment_records.deprecated_at column ...")
        cur.execute("ALTER TABLE deployment_records ADD COLUMN deprecated_at TEXT")
        conn.commit()
    else:
        print("deployment_records.deprecated_at column already exists — skipping ADD COLUMN")

    # 2. Find candidate rows: not-yet-deprecated deployment_records that
    #    have no matching unit_assignments row.
    cur.execute("""
        SELECT id, unit_id, deployed_date, estimated_removal_date,
               actual_removal_date, project_id, project_ref, location_name, notes
          FROM deployment_records
         WHERE deprecated_at IS NULL
    """)
    rows = cur.fetchall()
    print(f"\nFound {len(rows)} deployment_records rows not yet deprecated.")

    backfilled = 0
    skipped_no_match_attempted = 0
    skipped_already_in_assignments = 0
    skipped_missing_unit = 0

    for row in rows:
        unit_id = row["unit_id"]
        if not unit_id:
            print(f"  ⚠ row {row['id']!r}: no unit_id, marking deprecated without backfill")
            cur.execute(
                "UPDATE deployment_records SET deprecated_at=? WHERE id=?",
                (datetime.utcnow().isoformat(), row["id"]),
            )
            skipped_missing_unit += 1
            continue

        # Does the unit still exist?  If not, skip — we don't synthesize
        # assignments for ghost units.
        cur.execute("SELECT id, device_type FROM roster WHERE id=?", (unit_id,))
        roster = cur.fetchone()
        if not roster:
            print(f"  ⚠ row {row['id']!r}: unit_id {unit_id!r} not in roster, marking deprecated without backfill")
            cur.execute(
                "UPDATE deployment_records SET deprecated_at=? WHERE id=?",
                (datetime.utcnow().isoformat(), row["id"]),
            )
            skipped_missing_unit += 1
            continue

        # Check if a UnitAssignment already covers this window (any overlap).
        # We don't try to be clever — just see if a row exists for this unit
        # whose [assigned_at, assigned_until] overlaps the deployment window.
        cur.execute("""
            SELECT id FROM unit_assignments
             WHERE unit_id=?
               AND (assigned_at <= COALESCE(?, '9999')
                    AND COALESCE(assigned_until, '9999') >= COALESCE(?, '0000'))
             LIMIT 1
        """, (
            unit_id,
            row["actual_removal_date"] or row["estimated_removal_date"] or row["deployed_date"],
            row["deployed_date"],
        ))
        if cur.fetchone():
            cur.execute(
                "UPDATE deployment_records SET deprecated_at=? WHERE id=?",
                (datetime.utcnow().isoformat(), row["id"]),
            )
            skipped_already_in_assignments += 1
            continue

        # No matching UnitAssignment — synthesize one.  We can't FK to a
        # MonitoringLocation because the legacy `location_name` is free
        # text.  Backfilled rows go in with location_id = "" (empty) and
        # the original location_name dropped into notes for operator
        # context.
        if not row["project_id"]:
            print(f"  ⚠ row {row['id']!r}: no project_id, can't synthesize unit_assignment, marking deprecated")
            cur.execute(
                "UPDATE deployment_records SET deprecated_at=? WHERE id=?",
                (datetime.utcnow().isoformat(), row["id"]),
            )
            skipped_no_match_attempted += 1
            continue

        synthesized_id = str(uuid.uuid4())
        synth_notes_parts = []
        if row["location_name"]:
            synth_notes_parts.append(f"Legacy location: {row['location_name']}")
        if row["project_ref"]:
            synth_notes_parts.append(f"Legacy project_ref: {row['project_ref']}")
        if row["notes"]:
            synth_notes_parts.append(f"Original notes: {row['notes']}")
        synth_notes_parts.append(f"(Synthesized from deployment_records row {row['id']})")
        synth_notes = " | ".join(synth_notes_parts)

        assigned_until = row["actual_removal_date"]
        # Don't auto-close active deployments based on estimated_removal_date.
        status = "completed" if assigned_until else "active"

        # Need a location_id to satisfy NOT NULL constraint.  Use a
        # placeholder UUID so the FK can be cleaned up later if the
        # operator decides to retarget the assignment to a real location.
        # We tag this with the synthesized notes so it's discoverable.
        placeholder_loc_id = ""

        try:
            cur.execute("""
                INSERT INTO unit_assignments (
                    id, unit_id, location_id, project_id, device_type,
                    assigned_at, assigned_until, status, notes, created_at
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                synthesized_id,
                unit_id,
                placeholder_loc_id,
                row["project_id"],
                roster["device_type"] or "seismograph",
                row["deployed_date"] or datetime.utcnow().isoformat(),
                assigned_until,
                status,
                synth_notes,
                datetime.utcnow().isoformat(),
            ))
            cur.execute(
                "UPDATE deployment_records SET deprecated_at=? WHERE id=?",
                (datetime.utcnow().isoformat(), row["id"]),
            )
            backfilled += 1
            print(
                f"  ✓ row {row['id']!r}: synthesized unit_assignment {synthesized_id} "
                f"for unit={unit_id} project={row['project_id'][:8]}… "
                f"({row['deployed_date']} → {assigned_until or 'present'})"
            )
        except Exception as e:
            print(f"  ✗ row {row['id']!r}: failed to synthesize — {e}")

    conn.commit()
    conn.close()

    print("\n────────────────────────────────────────────────────────")
    print(f"Backfilled new unit_assignments:      {backfilled}")
    print(f"Already covered (deprecated only):    {skipped_already_in_assignments}")
    print(f"No project_id (deprecated only):      {skipped_no_match_attempted}")
    print(f"Missing/orphaned unit (deprecated):   {skipped_missing_unit}")
    print(f"\nNOTE: synthesized rows have an empty location_id and the legacy")
    print(f"      free-text location is preserved in notes.  An operator should")
    print(f"      retarget them to real MonitoringLocation rows if they want")
    print(f"      events to show up on a location detail page.")


if __name__ == "__main__":
    migrate_database()