#!/usr/bin/env python3
"""
Migration script to add wedge-recovery fields to nl43_config and nl43_status tables.

Adds to nl43_config:
- auto_recovery_enabled (BOOLEAN, default 0/False)
- reset_backend (TEXT, default 'manual')
- reset_webhook_url (TEXT, nullable)
- reset_webhook_method (TEXT, default 'POST')
- recovery_boot_wait_seconds (INTEGER, default 90)
- recovery_reconnect_timeout (INTEGER, default 180)
- recovery_auto_resume (BOOLEAN, default 1/True)
- recovery_max_attempts (INTEGER, default 2)
- recovery_window_minutes (INTEGER, default 360)

Adds to nl43_status:
- connection_state (TEXT, default 'ok')
- last_wedge_at (DATETIME, nullable)
- wedge_count (INTEGER, default 0)
- recovery_state (TEXT, default 'idle')
- last_recovery_at (DATETIME, nullable)
- last_recovery_result (TEXT, nullable)

Usage:
    python migrate_add_recovery_fields.py
"""

import sqlite3
import sys
from pathlib import Path

CONFIG_COLUMNS = [
    ("auto_recovery_enabled", "BOOLEAN DEFAULT 0"),
    ("reset_backend", "TEXT DEFAULT 'manual'"),
    ("reset_webhook_url", "TEXT"),
    ("reset_webhook_method", "TEXT DEFAULT 'POST'"),
    ("recovery_boot_wait_seconds", "INTEGER DEFAULT 90"),
    ("recovery_reconnect_timeout", "INTEGER DEFAULT 180"),
    ("recovery_auto_resume", "BOOLEAN DEFAULT 1"),
    ("recovery_max_attempts", "INTEGER DEFAULT 2"),
    ("recovery_window_minutes", "INTEGER DEFAULT 360"),
]

STATUS_COLUMNS = [
    ("connection_state", "TEXT DEFAULT 'ok'"),
    ("last_wedge_at", "DATETIME"),
    ("wedge_count", "INTEGER DEFAULT 0"),
    ("recovery_state", "TEXT DEFAULT 'idle'"),
    ("last_recovery_at", "DATETIME"),
    ("last_recovery_result", "TEXT"),
]


def migrate():
    db_path = Path("data/slmm.db")

    if not db_path.exists():
        print(f"❌ Database not found at {db_path}")
        print("   Run this script from the slmm directory")
        return False

    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()

        cursor.execute("PRAGMA table_info(nl43_config)")
        config_columns = [row[1] for row in cursor.fetchall()]

        cursor.execute("PRAGMA table_info(nl43_status)")
        status_columns = [row[1] for row in cursor.fetchall()]

        changes_made = False

        for name, ddl in CONFIG_COLUMNS:
            if name not in config_columns:
                print(f"Adding {name} to nl43_config...")
                cursor.execute(f"ALTER TABLE nl43_config ADD COLUMN {name} {ddl}")
                changes_made = True
            else:
                print(f"✓ {name} already exists in nl43_config")

        for name, ddl in STATUS_COLUMNS:
            if name not in status_columns:
                print(f"Adding {name} to nl43_status...")
                cursor.execute(f"ALTER TABLE nl43_status ADD COLUMN {name} {ddl}")
                changes_made = True
            else:
                print(f"✓ {name} already exists in nl43_status")

        if changes_made:
            conn.commit()
            print("\n✓ Migration completed successfully")
            print("  Added wedge-recovery fields to nl43_config and nl43_status")
        else:
            print("\n✓ All recovery fields already exist - no changes needed")

        conn.close()
        return True

    except Exception as e:
        print(f"❌ Migration failed: {e}")
        return False


if __name__ == "__main__":
    success = migrate()
    sys.exit(0 if success else 1)