#!/usr/bin/env python3
"""
Comprehensive Database Schema Analysis and Fix
Analyzes and fixes all database issues:
- Schema correctness
- Missing columns
- Foreign key constraints
- Indexes for performance
- Backend/database alignment
"""

from postgresql_models import engine, Base, Profile, Song, Plan, PlanSong, ProfileSong, ProfileSongKey
from sqlalchemy import text, inspect, Column, String, Text
import sys

def analyze_schema():
    """Analyze current database schema and identify issues"""
    print("=" * 70)
    print("DATABASE SCHEMA ANALYSIS")
    print("=" * 70)
    
    inspector = inspect(engine)
    issues = []
    fixes = []
    
    # Check Profile table
    print("\n📋 CHECKING PROFILES TABLE")
    print("-" * 70)
    profile_cols = {col['name']: col for col in inspector.get_columns('profiles')}
    
    # Check for missing columns referenced in app.py
    required_profile_cols = {
        'email': 'VARCHAR(255)',
        'contact_number': 'VARCHAR(50)', 
        'notes': 'TEXT'
    }
    
    for col_name, col_type in required_profile_cols.items():
        if col_name not in profile_cols:
            issues.append(f"❌ Profile.{col_name} column is MISSING (referenced in app.py)")
            fixes.append(f"ALTER TABLE profiles ADD COLUMN {col_name} {col_type} DEFAULT '';")
        else:
            print(f"  ✅ Profile.{col_name} exists")
    
    # Check Songs table
    print("\n📋 CHECKING SONGS TABLE")
    print("-" * 70)
    songs_cols = {col['name']: col for col in inspector.get_columns('songs')}
    
    if not songs_cols['title']['nullable']:
        print("  ✅ songs.title is NOT NULL")
    else:
        issues.append("❌ songs.title should be NOT NULL")
        fixes.append("UPDATE songs SET title = 'Untitled' WHERE title IS NULL;")
        fixes.append("ALTER TABLE songs ALTER COLUMN title SET NOT NULL;")
    
    # Check created_at/updated_at should use INTEGER (timestamps)
    if str(songs_cols['created_at']['type']) == 'BIGINT':
        print("  ✅ songs.created_at is BIGINT (timestamp)")
    else:
        issues.append(f"❌ songs.created_at type is {songs_cols['created_at']['type']}, should be BIGINT")
    
    # Check Plans table
    print("\n📋 CHECKING PLANS TABLE")
    print("-" * 70)
    plans_cols = {col['name']: col for col in inspector.get_columns('plans')}
    
    if not plans_cols['date']['nullable']:
        print("  ✅ plans.date is NOT NULL")
    else:
        issues.append("❌ plans.date should be NOT NULL")
        fixes.append("UPDATE plans SET date = TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') WHERE date IS NULL;")
        fixes.append("ALTER TABLE plans ALTER COLUMN date SET NOT NULL;")
    
    # Check plan_songs table - id can be VARCHAR(255) for UUIDs or INTEGER for autoincrement
    print("\n📋 CHECKING PLAN_SONGS TABLE")
    print("-" * 70)
    plan_songs_cols = {col['name']: col for col in inspector.get_columns('plan_songs')}
    
    plan_songs_id_type = str(plan_songs_cols['id']['type'])
    if 'VARCHAR' in plan_songs_id_type:
        print(f"  ✅ plan_songs.id is VARCHAR (using UUIDs)")
    elif 'INTEGER' in plan_songs_id_type or 'SERIAL' in plan_songs_id_type:
        print(f"  ✅ plan_songs.id is INTEGER (autoincrement)")
    else:
        issues.append(f"❌ plan_songs.id has unexpected type: {plan_songs_id_type}")
        print(f"  ❌ plan_songs.id type: {plan_songs_id_type} (unexpected)")
    
    # Check Indexes
    print("\n📊 CHECKING INDEXES")
    print("-" * 70)
    
    required_indexes = {
        'songs': ['idx_song_title', 'idx_song_artist', 'idx_song_band', 'idx_song_singer'],
        'profiles': ['idx_profile_name'],
        'plans': ['idx_plan_date', 'idx_plan_profile'],
        'plan_songs': ['idx_plan_songs_order'],
        'profile_songs': ['idx_profile_songs_profile'],
        'profile_song_keys': ['idx_profile_song_keys']
    }
    
    for table, expected_indexes in required_indexes.items():
        existing = [idx['name'] for idx in inspector.get_indexes(table)]
        for idx_name in expected_indexes:
            if idx_name in existing:
                print(f"  ✅ {table}.{idx_name}")
            else:
                issues.append(f"❌ Missing index: {table}.{idx_name}")
                if idx_name == 'idx_song_title':
                    fixes.append(f"CREATE INDEX IF NOT EXISTS {idx_name} ON {table}(title);")
                elif idx_name == 'idx_song_artist':
                    fixes.append(f"CREATE INDEX IF NOT EXISTS {idx_name} ON {table}(artist);")
                elif idx_name == 'idx_song_band':
                    fixes.append(f"CREATE INDEX IF NOT EXISTS {idx_name} ON {table}(band);")
                elif idx_name == 'idx_song_singer':
                    fixes.append(f"CREATE INDEX IF NOT EXISTS {idx_name} ON {table}(singer);")
                elif idx_name == 'idx_profile_name':
                    fixes.append(f"CREATE INDEX IF NOT EXISTS {idx_name} ON {table}(name);")
                elif idx_name == 'idx_plan_date':
                    fixes.append(f"CREATE INDEX IF NOT EXISTS {idx_name} ON {table}(date);")
                elif idx_name == 'idx_plan_profile':
                    fixes.append(f"CREATE INDEX IF NOT EXISTS {idx_name} ON {table}(profile_id);")
                elif idx_name == 'idx_plan_songs_order':
                    fixes.append(f"CREATE INDEX IF NOT EXISTS {idx_name} ON {table}(plan_id, order_index);")
                elif idx_name == 'idx_profile_songs_profile':
                    fixes.append(f"CREATE INDEX IF NOT EXISTS {idx_name} ON {table}(profile_id);")
                elif idx_name == 'idx_profile_song_keys':
                    fixes.append(f"CREATE INDEX IF NOT EXISTS {idx_name} ON {table}(profile_id, song_id);")
    
    # Check Foreign Keys CASCADE
    print("\n🔗 CHECKING FOREIGN KEY CONSTRAINTS")
    print("-" * 70)
    
    fk_tables = ['plan_songs', 'profile_songs', 'profile_song_keys']
    for table in fk_tables:
        fks = inspector.get_foreign_keys(table)
        for fk in fks:
            cascade = fk['options'].get('ondelete', 'NO ACTION')
            if cascade == 'CASCADE':
                print(f"  ✅ {table}.{fk['name']} → CASCADE")
            else:
                issues.append(f"❌ {table}.{fk['name']} should CASCADE on delete (currently: {cascade})")
    
    # Check plans.profile_id should SET NULL
    plans_fks = inspector.get_foreign_keys('plans')
    for fk in plans_fks:
        if 'profile_id' in fk['constrained_columns']:
            cascade = fk['options'].get('ondelete', 'NO ACTION')
            if cascade == 'SET NULL':
                print(f"  ✅ plans.profile_id → SET NULL")
            else:
                issues.append(f"❌ plans.profile_id should SET NULL on delete (currently: {cascade})")
    
    # Summary
    print("\n" + "=" * 70)
    print("ANALYSIS SUMMARY")
    print("=" * 70)
    
    if issues:
        print(f"\n⚠️  Found {len(issues)} issues:\n")
        for issue in issues:
            print(f"  {issue}")
    else:
        print("\n✅ No issues found! Database schema is correct.")
    
    if fixes:
        print(f"\n🔧 Suggested fixes ({len(fixes)} SQL statements):\n")
        for i, fix in enumerate(fixes, 1):
            print(f"{i}. {fix}")
    
    return issues, fixes

def apply_fixes(fixes):
    """Apply database fixes"""
    if not fixes:
        print("\n✅ No fixes needed!")
        return True
    
    print("\n" + "=" * 70)
    print("APPLYING FIXES")
    print("=" * 70)
    
    try:
        with engine.begin() as conn:
            for i, fix in enumerate(fixes, 1):
                if fix.startswith('--'):
                    print(f"\n{fix}")
                    continue
                    
                print(f"\n{i}. Executing: {fix[:80]}...")
                try:
                    conn.execute(text(fix))
                    print("   ✅ Success")
                except Exception as e:
                    print(f"   ⚠️  Warning: {str(e)}")
                    # Continue with other fixes
        
        print("\n✅ All fixes applied successfully!")
        return True
        
    except Exception as e:
        print(f"\n❌ Error applying fixes: {str(e)}")
        return False

def verify_backend_alignment():
    """Verify backend code aligns with database schema"""
    print("\n" + "=" * 70)
    print("BACKEND ALIGNMENT VERIFICATION")
    print("=" * 70)
    
    inspector = inspect(engine)
    
    # Check Profile model
    print("\n📋 Profile Model vs Database")
    print("-" * 70)
    profile_db_cols = set(col['name'] for col in inspector.get_columns('profiles'))
    profile_model_attrs = {'id', 'first_name', 'last_name', 'name', 'default_key', 'email', 'contact_number', 'notes'}
    
    missing_in_db = profile_model_attrs - profile_db_cols
    extra_in_db = profile_db_cols - profile_model_attrs - {'metadata', 'registry'}
    
    if missing_in_db:
        print(f"  ⚠️  Model attributes not in DB: {missing_in_db}")
    if extra_in_db:
        print(f"  ℹ️  DB columns not in model: {extra_in_db}")
    if not missing_in_db and not extra_in_db:
        print("  ✅ Profile model aligned with database")
    
    # Check Song model
    print("\n📋 Song Model vs Database")
    print("-" * 70)
    song_db_cols = set(col['name'] for col in inspector.get_columns('songs'))
    song_model_attrs = {'id', 'title', 'artist', 'band', 'singer', 'lyrics', 'chords', 'memo', 'created_at', 'updated_at'}
    
    missing_in_db = song_model_attrs - song_db_cols
    extra_in_db = song_db_cols - song_model_attrs - {'metadata', 'registry'}
    
    if missing_in_db:
        print(f"  ⚠️  Model attributes not in DB: {missing_in_db}")
    if extra_in_db:
        print(f"  ℹ️  DB columns not in model: {extra_in_db}")
    if not missing_in_db and not extra_in_db:
        print("  ✅ Song model aligned with database")
    
    print("\n" + "=" * 70)

if __name__ == "__main__":
    print("\n🔍 Starting comprehensive database analysis...\n")
    
    # Step 1: Analyze
    issues, fixes = analyze_schema()
    
    # Step 2: Apply fixes
    if fixes and '--apply' in sys.argv:
        print("\n⚠️  --apply flag detected, applying fixes...")
        if apply_fixes(fixes):
            print("\n✅ Fixes applied successfully!")
            # Re-analyze to verify
            print("\n🔍 Re-analyzing database...")
            issues, _ = analyze_schema()
        else:
            print("\n❌ Some fixes failed. Please review manually.")
            sys.exit(1)
    elif fixes:
        print("\n💡 To apply these fixes, run:")
        print(f"   python3 {sys.argv[0]} --apply")
    
    # Step 3: Verify alignment
    verify_backend_alignment()
    
    # Final status
    print("\n" + "=" * 70)
    if issues:
        print(f"⚠️  {len(issues)} issues found. Run with --apply to fix.")
        sys.exit(1)
    else:
        print("✅ Database schema is correct and aligned!")
        sys.exit(0)