"""
Migration: Add deployment_records table.

Tracks each time a unit is sent to the field and returned.
The active deployment is the row with actual_removal_date IS NULL.

Run once per database:
    python backend/migrate_add_deployment_records.py
"""

import sqlite3
import os

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

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

    try:
        # Check if table already exists
        cursor.execute("""
            SELECT name FROM sqlite_master
            WHERE type='table' AND name='deployment_records'
        """)
        if cursor.fetchone():
            print("✓ deployment_records table already exists, skipping")
            return

        print("Creating deployment_records table...")
        cursor.execute("""
            CREATE TABLE deployment_records (
                id TEXT PRIMARY KEY,
                unit_id TEXT NOT NULL,
                deployed_date DATE,
                estimated_removal_date DATE,
                actual_removal_date DATE,
                project_ref TEXT,
                project_id TEXT,
                location_name TEXT,
                notes TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)

        cursor.execute("""
            CREATE INDEX idx_deployment_records_unit_id
            ON deployment_records(unit_id)
        """)
        cursor.execute("""
            CREATE INDEX idx_deployment_records_project_id
            ON deployment_records(project_id)
        """)
        # Index for finding active deployments quickly
        cursor.execute("""
            CREATE INDEX idx_deployment_records_active
            ON deployment_records(unit_id, actual_removal_date)
        """)

        conn.commit()
        print("✓ deployment_records table created successfully")
        print("✓ Indexes created")

    except Exception as e:
        conn.rollback()
        print(f"✗ Migration failed: {e}")
        raise
    finally:
        conn.close()


if __name__ == "__main__":
    migrate_database()