#!/usr/bin/env python3
"""
Manual fix for project_versions UNIQUE constraint.
Run this if you have the error "Version 'X.X.X' already exists" across different projects.

This script fixes the database schema to allow each project to have independent version names.
"""

import sqlite3
from pathlib import Path

DB_PATH = Path(__file__).parent / "scheduler.db"

def fix_constraint():
    """Fix the UNIQUE constraint on project_versions table"""

    if not DB_PATH.exists():
        print(f"❌ Database not found at: {DB_PATH}")
        return

    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row

    print("🔍 Checking project_versions table schema...")

    cursor = conn.execute("""
        SELECT sql FROM sqlite_master
        WHERE type='table' AND name='project_versions'
    """)

    schema = cursor.fetchone()
    if schema is None:
        print("❌ Table project_versions not found!")
        print("   The database may not have been initialized yet.")
        conn.close()
        return

    schema_sql = schema[0]

    print("\nCurrent schema:")
    print(schema_sql)
    print()

    if 'UNIQUE(project_id, version_name)' in schema_sql:
        print("✅ Schema is already correct - no fix needed!")
        print("   UNIQUE constraint is properly scoped to (project_id, version_name)")
        print("   Each project can have independent version names.")
        conn.close()
        return

    print("⚠️  Found incorrect schema!")
    print("   Current: UNIQUE(version_name) - version names are globally unique")
    print("   Expected: UNIQUE(project_id, version_name) - scoped per project")
    print()

    # Count existing versions
    count_cursor = conn.execute("SELECT COUNT(*) FROM project_versions")
    version_count = count_cursor.fetchone()[0]

    if version_count > 0:
        print(f"📊 Found {version_count} existing version(s) in database")
        print("   These will be preserved during migration.")
        print()

    response = input("Do you want to fix this now? (yes/no): ").strip().lower()
    if response not in ['yes', 'y']:
        print("❌ Migration cancelled by user.")
        conn.close()
        return

    print("\n🔄 Starting migration...")

    try:
        # Create new table with correct schema
        print("   Step 1/5: Creating new table with correct schema...")
        conn.execute("""
            CREATE TABLE project_versions_new (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                project_id INTEGER NOT NULL,
                version_name TEXT NOT NULL,
                description TEXT,
                zip_filename TEXT NOT NULL,
                size_bytes INTEGER DEFAULT 0,
                files_count INTEGER DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                created_by TEXT DEFAULT 'user',
                FOREIGN KEY (project_id) REFERENCES project_configs(id) ON DELETE CASCADE,
                UNIQUE(project_id, version_name)
            )
        """)

        # Copy data
        print("   Step 2/5: Copying existing data...")
        conn.execute("""
            INSERT INTO project_versions_new
            SELECT * FROM project_versions
        """)

        # Replace table
        print("   Step 3/5: Dropping old table...")
        conn.execute("DROP TABLE project_versions")

        print("   Step 4/5: Renaming new table...")
        conn.execute("ALTER TABLE project_versions_new RENAME TO project_versions")

        # Recreate indexes
        print("   Step 5/5: Recreating indexes...")
        conn.execute("CREATE INDEX IF NOT EXISTS idx_project_versions_project ON project_versions(project_id)")
        conn.execute("CREATE INDEX IF NOT EXISTS idx_project_versions_created ON project_versions(created_at DESC)")

        conn.commit()

        print("\n✅ Migration complete!")
        print("   Each project can now have independent version names")
        print("   Example: Project A and Project B can both have version '1.0.0'")

        # Verify the fix
        verify_cursor = conn.execute("""
            SELECT sql FROM sqlite_master
            WHERE type='table' AND name='project_versions'
        """)
        new_schema = verify_cursor.fetchone()[0]

        if 'UNIQUE(project_id, version_name)' in new_schema:
            print("\n✓ Verification passed: Schema is now correct")
        else:
            print("\n⚠️ Warning: Schema may not be correct, please check manually")

    except Exception as e:
        print(f"\n❌ Migration failed: {e}")
        print("   The database has not been modified.")
        conn.rollback()
        raise
    finally:
        conn.close()

if __name__ == "__main__":
    print("=" * 70)
    print("Project Versions UNIQUE Constraint Fix")
    print("=" * 70)
    print()

    try:
        fix_constraint()
    except Exception as e:
        print(f"\n❌ Error: {e}")
        import traceback
        traceback.print_exc()
        print("\nPlease backup your database and contact support if this persists.")