"""
Migration script to add report_templates table.

This creates a new table for storing report generation configurations:
- Template name and project association
- Time filtering settings (start/end time)
- Date range filtering (optional)
- Report title defaults

Run this script once to migrate an existing database.
"""

import sqlite3
import os

# Database path
DB_PATH = "./data/seismo_fleet.db"

def migrate_database():
    """Create report_templates table"""

    if not os.path.exists(DB_PATH):
        print(f"Database not found at {DB_PATH}")
        print("The database will be created automatically when you run the application.")
        return

    print(f"Migrating database: {DB_PATH}")

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

    # Check if report_templates table already exists
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='report_templates'")
    table_exists = cursor.fetchone()

    if table_exists:
        print("Migration already applied - report_templates table exists")
        conn.close()
        return

    print("Creating report_templates table...")

    try:
        cursor.execute("""
            CREATE TABLE report_templates (
                id TEXT PRIMARY KEY,
                name TEXT NOT NULL,
                project_id TEXT,
                report_title TEXT DEFAULT 'Background Noise Study',
                start_time TEXT,
                end_time TEXT,
                start_date TEXT,
                end_date TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        print("  ✓ Created report_templates table")

        # Insert default templates
        import uuid

        default_templates = [
            (str(uuid.uuid4()), "Nighttime (7PM-7AM)", None, "Background Noise Study", "19:00", "07:00", None, None),
            (str(uuid.uuid4()), "Daytime (7AM-7PM)", None, "Background Noise Study", "07:00", "19:00", None, None),
            (str(uuid.uuid4()), "Full Day (All Data)", None, "Background Noise Study", None, None, None, None),
        ]

        cursor.executemany("""
            INSERT INTO report_templates (id, name, project_id, report_title, start_time, end_time, start_date, end_date)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        """, default_templates)
        print("  ✓ Inserted default templates (Nighttime, Daytime, Full Day)")

        conn.commit()
        print("\nMigration completed successfully!")

    except sqlite3.Error as e:
        print(f"\nError during migration: {e}")
        conn.rollback()
        raise

    finally:
        conn.close()


if __name__ == "__main__":
    migrate_database()