#!/usr/bin/env python3
"""
Simple migration script to add shared course fields to the courses table.
"""

import sqlite3
import os
from datetime import datetime

def check_column_exists(cursor, table_name, column_name):
    """Check if a column exists in a table."""
    cursor.execute(f"PRAGMA table_info({table_name})")
    columns = [column[1] for column in cursor.fetchall()]
    return column_name in columns

def run_migration(db_path):
    """Run the shared course fields migration."""
    
    if not os.path.exists(db_path):
        print(f"Database file not found: {db_path}")
        return False
    
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        print(f"Processing database: {db_path}")
        
        # Check if courses table exists
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='courses'")
        if not cursor.fetchone():
            print("Courses table not found, skipping...")
            return True
        
        # Add shared course fields
        fields_to_add = [
            ("is_shared_course", "BOOLEAN DEFAULT 0"),
            ("shared_course_type", "VARCHAR(50) DEFAULT 'department_specific'"),
            ("sharing_level", "VARCHAR(50) DEFAULT 'single'")
        ]
        
        for field_name, field_type in fields_to_add:
            if not check_column_exists(cursor, 'courses', field_name):
                try:
                    cursor.execute(f"ALTER TABLE courses ADD COLUMN {field_name} {field_type}")
                    print(f"✓ Added column: {field_name}")
                except sqlite3.OperationalError as e:
                    print(f"✗ Error adding column {field_name}: {e}")
            else:
                print(f"ℹ️  Column {field_name} already exists")
        
        # Create course_departments table if it doesn't exist
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='course_departments'")
        if not cursor.fetchone():
            try:
                cursor.execute("""
                    CREATE TABLE course_departments (
                        id VARCHAR(150) PRIMARY KEY,
                        course_id VARCHAR(150) NOT NULL,
                        department_name VARCHAR(100) NOT NULL,
                        is_primary_department BOOLEAN DEFAULT 0,
                        assigned_date DATE DEFAULT CURRENT_DATE,
                        assigned_by VARCHAR(150),
                        notes TEXT,
                        is_active BOOLEAN DEFAULT 1,
                        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                        FOREIGN KEY (course_id) REFERENCES courses(id),
                        FOREIGN KEY (assigned_by) REFERENCES supervisors(id)
                    )
                """)
                print("✓ Created course_departments table")
            except sqlite3.OperationalError as e:
                print(f"✗ Error creating course_departments table: {e}")
        else:
            print("ℹ️  course_departments table already exists")
        
        # Create indexes
        indexes = [
            "CREATE INDEX IF NOT EXISTS idx_courses_is_shared_course ON courses(is_shared_course)",
            "CREATE INDEX IF NOT EXISTS idx_courses_shared_course_type ON courses(shared_course_type)",
            "CREATE INDEX IF NOT EXISTS idx_courses_sharing_level ON courses(sharing_level)",
            "CREATE INDEX IF NOT EXISTS idx_course_departments_course_id ON course_departments(course_id)",
            "CREATE INDEX IF NOT EXISTS idx_course_departments_department_name ON course_departments(department_name)"
        ]
        
        for index_sql in indexes:
            try:
                cursor.execute(index_sql)
                print(f"✓ Created index")
            except sqlite3.OperationalError as e:
                if "already exists" in str(e):
                    print(f"ℹ️  Index already exists")
                else:
                    print(f"✗ Error creating index: {e}")
        
        conn.commit()
        print(f"✅ Successfully migrated {db_path}")
        return True
        
    except Exception as e:
        print(f"❌ Error processing {db_path}: {str(e)}")
        return False
    finally:
        if 'conn' in locals():
            conn.close()

def main():
    """Main migration function."""
    db_files = [
        'test_9.db',
        'test_8.db', 
        'test_7.db',
        'test_6.db',
        'test_5.db',
        'test_4.db',
        'test_3.db',
        'test_2.db',
        'test_1.db',
        'test.db'
    ]
    
    print("🚀 Starting shared course fields migration...")
    print(f"Timestamp: {datetime.now()}")
    print("-" * 60)
    
    for db_file in db_files:
        if os.path.exists(db_file):
            success = run_migration(db_file)
            if success:
                print(f"✅ {db_file} - Migration completed")
            else:
                print(f"❌ {db_file} - Migration failed")
            print()
        else:
            print(f"ℹ️  {db_file} not found, skipping...")
    
    print("🎉 Migration process completed!")

if __name__ == "__main__":
    main()