#!/usr/bin/env python3
"""
Database migration script for IoT device fields
Adds new columns to support LILYGO T-A7670G device data
"""

import os
import sys
from sqlalchemy import create_engine, text
from sqlalchemy.exc import OperationalError

# Add the app directory to path
sys.path.insert(0, os.path.join(os.path.dirname(__file__), 'app'))

from app.core.config import settings


def run_migration():
    """Run database migration to add new IoT device fields"""
    engine = create_engine(str(settings.DATABASE_URL))
    
    # Migration queries
    migrations = [
        # Add new columns to telemetry table
        "ALTER TABLE telemetry ADD COLUMN IF NOT EXISTS gps_fixed BOOLEAN DEFAULT FALSE",
        "ALTER TABLE telemetry ADD COLUMN IF NOT EXISTS battery_voltage FLOAT",
        "ALTER TABLE telemetry ADD COLUMN IF NOT EXISTS humidity FLOAT", 
        "ALTER TABLE telemetry ADD COLUMN IF NOT EXISTS solar_voltage FLOAT",
        "ALTER TABLE telemetry ADD COLUMN IF NOT EXISTS device_timestamp TIMESTAMP WITH TIME ZONE",
        
        # Add comments for new columns
        "COMMENT ON COLUMN telemetry.gps_fixed IS 'GPS fix status from IoT device'",
        "COMMENT ON COLUMN telemetry.battery_voltage IS 'Actual battery voltage from IoT device'",
        "COMMENT ON COLUMN telemetry.humidity IS 'Humidity percentage from IoT device'",
        "COMMENT ON COLUMN telemetry.solar_voltage IS 'Solar panel voltage from IoT device'",
        "COMMENT ON COLUMN telemetry.device_timestamp IS 'Timestamp from IoT device'",
    ]
    
    print("Starting database migration for IoT device fields...")
    
    try:
        with engine.connect() as conn:
            for i, migration in enumerate(migrations, 1):
                try:
                    print(f"Running migration {i}/{len(migrations)}: {migration[:50]}...")
                    conn.execute(text(migration))
                    conn.commit()
                    print(f"✓ Migration {i} completed successfully")
                except OperationalError as e:
                    if "already exists" in str(e) or "column already exists" in str(e):
                        print(f"⚠ Migration {i} skipped - column already exists")
                    else:
                        print(f"✗ Migration {i} failed: {e}")
                        raise
                except Exception as e:
                    print(f"✗ Migration {i} failed: {e}")
                    raise
                    
        print("✓ All migrations completed successfully!")
        print("\nNew IoT device fields added:")
        print("- gps_fixed: GPS fix status boolean")
        print("- battery_voltage: Raw battery voltage")
        print("- humidity: Humidity percentage")
        print("- solar_voltage: Solar panel voltage")
        print("- device_timestamp: Device timestamp")
        
    except Exception as e:
        print(f"✗ Migration failed: {e}")
        print("\nThis is normal if you're starting with a fresh database.")
        print("The new fields will be created automatically when the app starts.")
        return False
        
    return True


def check_migration_needed():
    """Check if migration is needed by testing for new columns"""
    engine = create_engine(str(settings.DATABASE_URL))
    
    try:
        with engine.connect() as conn:
            # Check if telemetry table exists and has new columns
            result = conn.execute(text("""
                SELECT column_name 
                FROM information_schema.columns 
                WHERE table_name = 'telemetry' 
                AND column_name IN ('gps_fixed', 'battery_voltage', 'humidity', 'solar_voltage', 'device_timestamp')
            """))
            
            existing_columns = [row[0] for row in result]
            needed_columns = ['gps_fixed', 'battery_voltage', 'humidity', 'solar_voltage', 'device_timestamp']
            missing_columns = [col for col in needed_columns if col not in existing_columns]
            
            if missing_columns:
                print(f"Migration needed. Missing columns: {missing_columns}")
                return True
            else:
                print("✓ All IoT device fields already present in database")
                return False
                
    except Exception as e:
        print(f"Could not check migration status: {e}")
        print("Assuming migration is needed...")
        return True


if __name__ == "__main__":
    print("IoT Device Database Migration Tool")
    print("=" * 40)
    
    if check_migration_needed():
        run_migration()
    else:
        print("No migration needed.")