"""
Migration: Add auto_increment_index column to recurring_schedules table

This migration adds the auto_increment_index column that controls whether
the scheduler should automatically find an unused store index before starting
a new measurement.

Run this script once to update existing databases:
    python -m backend.migrate_add_auto_increment_index
"""

import sqlite3
import os

DB_PATH = "data/seismo_fleet.db"


def migrate():
    """Add auto_increment_index column to recurring_schedules table."""
    if not os.path.exists(DB_PATH):
        print(f"Database not found at {DB_PATH}")
        return False

    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    try:
        # Check if recurring_schedules table exists
        cursor.execute("""
            SELECT name FROM sqlite_master
            WHERE type='table' AND name='recurring_schedules'
        """)
        if not cursor.fetchone():
            print("recurring_schedules table does not exist yet. Will be created on app startup.")
            conn.close()
            return True

        # Check if auto_increment_index column already exists
        cursor.execute("PRAGMA table_info(recurring_schedules)")
        columns = [row[1] for row in cursor.fetchall()]

        if "auto_increment_index" in columns:
            print("auto_increment_index column already exists in recurring_schedules table.")
            conn.close()
            return True

        # Add the column
        print("Adding auto_increment_index column to recurring_schedules table...")
        cursor.execute("""
            ALTER TABLE recurring_schedules
            ADD COLUMN auto_increment_index BOOLEAN DEFAULT 1
        """)
        conn.commit()
        print("Successfully added auto_increment_index column.")

        conn.close()
        return True

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


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