#!/usr/bin/env python3
"""
Database migration: Add mic_unit_pref column to user_preferences.

Adds a single field controlling the mic channel's unit on the event-
report waveform chart in the SFM event detail modal.  "psi" (default —
matches the PDF report's mic axis) or "dBL".  Peaks and KPI tiles
elsewhere are always dBL regardless.

History: v0.13.0 originally shipped this with default "dBL", which
made the website chart inconsistent with the PDF.  v0.13.1 flips the
default to "psi" so they match.  This migration is idempotent and
covers three cases:

1. Fresh DB without the column — adds it with default 'psi'.
2. DB upgraded from v0.13.0 (column exists, value 'dBL') — flips to
   'psi' on the assumption no operator deliberately picked 'dBL' yet.
3. DB upgraded from later — flip step is a no-op for non-'dBL' values.
"""

import sqlite3
from pathlib import Path


def migrate():
    possible_paths = [
        Path("data/seismo_fleet.db"),
        Path("data/sfm.db"),
        Path("data/seismo.db"),
    ]
    db_path = next((p for p in possible_paths if p.exists()), None)
    if db_path is None:
        print(f"Database not found in any of: {[str(p) for p in possible_paths]}")
        print("Will be created with the new column when models.py initialises.")
        return

    print(f"Using database: {db_path}")
    conn = sqlite3.connect(db_path)
    cur = conn.cursor()

    cur.execute("PRAGMA table_info(user_preferences)")
    existing = {row[1] for row in cur.fetchall()}

    if "mic_unit_pref" not in existing:
        cur.execute(
            "ALTER TABLE user_preferences "
            "ADD COLUMN mic_unit_pref TEXT DEFAULT 'psi'"
        )
        # Backfill any rows where the column ended up NULL.
        cur.execute(
            "UPDATE user_preferences SET mic_unit_pref = 'psi' "
            "WHERE mic_unit_pref IS NULL"
        )
        print("Added mic_unit_pref column (default 'psi').")
    else:
        print("mic_unit_pref column already exists.")

    # v0.13.0 → v0.13.1 default-flip: rows still sitting at the original
    # 'dBL' default get bumped to 'psi'.  If any operator deliberately
    # chose 'dBL' through Settings before this migration runs they'd
    # get reset — acceptable trade-off given the small user base and
    # the fact the setting is one click to restore.
    cur.execute("UPDATE user_preferences SET mic_unit_pref = 'psi' "
                "WHERE mic_unit_pref = 'dBL'")
    flipped = cur.rowcount
    if flipped:
        print(f"Flipped {flipped} row(s) from 'dBL' to 'psi' (v0.13.0 default).")

    conn.commit()
    conn.close()


if __name__ == "__main__":
    migrate()