#!/usr/bin/env python3
"""
Migration script specifically for test_9.db to add shared course functionality.
"""

import sys
import os
from datetime import datetime

# Add the current directory to the Python path
sys.path.append(os.path.dirname(__file__))

from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
from sqlalchemy.exc import SQLAlchemyError

def migrate_test_9_db():
    """Run the migration specifically on test_9.db"""
    print("=" * 60)
    print("SHARED COURSE MIGRATION FOR TEST_9.DB")
    print("=" * 60)
    print(f"Migration started at: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
    print()
    
    try:
        # Connect directly to test_9.db
        engine = create_engine('sqlite:///test_9.db')
        Session = sessionmaker(bind=engine)
        session = Session()
        
        # Step 1: Add new columns to courses table
        print("Adding shared course columns to courses table...")
        
        columns_to_add = [
            "is_shared_course BOOLEAN DEFAULT FALSE",
            "shared_course_type VARCHAR(50) DEFAULT 'department_specific'",
            "sharing_level VARCHAR(50) DEFAULT 'single'"
        ]
        
        for column_def in columns_to_add:
            try:
                alter_sql = text(f"ALTER TABLE courses ADD COLUMN {column_def}")
                session.execute(alter_sql)
                print(f"✓ Added column: {column_def.split()[0]}")
            except SQLAlchemyError as e:
                if "duplicate column name" in str(e).lower():
                    print(f"⚠ Column {column_def.split()[0]} already exists, skipping...")
                else:
                    print(f"✗ Error adding column {column_def.split()[0]}: {e}")
                    raise
        
        session.commit()
        print("✓ All shared course columns added successfully")
        print()
        
        # Step 2: Create course_departments table
        print("Creating course_departments table...")
        
        try:
            create_table_sql = text("""
                CREATE TABLE IF NOT EXISTS course_departments (
                    id VARCHAR(150) PRIMARY KEY,
                    course_id VARCHAR(150) NOT NULL,
                    department_name VARCHAR(100) NOT NULL,
                    is_primary_department BOOLEAN DEFAULT FALSE,
                    assigned_date DATE DEFAULT CURRENT_DATE,
                    assigned_by VARCHAR(150),
                    notes TEXT,
                    is_active BOOLEAN DEFAULT TRUE,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
                    FOREIGN KEY (assigned_by) REFERENCES supervisors(id) ON DELETE SET NULL
                )
            """)
            
            session.execute(create_table_sql)
            session.commit()
            print("✓ course_departments table created successfully")
            print()
            
        except SQLAlchemyError as e:
            if "already exists" in str(e).lower():
                print("⚠ course_departments table already exists, skipping...")
            else:
                print(f"✗ Error creating course_departments table: {e}")
                raise
        
        # Step 3: Create indexes
        print("Creating 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:
                session.execute(text(index_sql))
                print(f"✓ Created index: {index_sql.split()[-1]}")
            except SQLAlchemyError as e:
                if "duplicate key name" in str(e).lower():
                    print(f"⚠ Index already exists, skipping...")
                else:
                    print(f"✗ Error creating index: {e}")
        
        session.commit()
        print("✓ All indexes created successfully")
        print()
        
        # Step 4: Verify migration
        print("Verifying migration...")
        
        # Check if columns exist in courses table
        columns_check = text("""
            SELECT COUNT(*) as count 
            FROM pragma_table_info('courses') 
            WHERE name IN ('is_shared_course', 'shared_course_type', 'sharing_level')
        """)
        
        result = session.execute(columns_check).fetchone()
        if result[0] == 3:
            print("✓ All columns verified successfully")
        else:
            print(f"✗ Column verification failed. Expected 3 columns, found {result[0]}")
            return False
        
        # Check if table exists
        table_check = text("""
            SELECT COUNT(*) as count 
            FROM sqlite_master 
            WHERE type='table' AND name='course_departments'
        """)
        
        result = session.execute(table_check).fetchone()
        if result[0] == 1:
            print("✓ course_departments table verified successfully")
        else:
            print("✗ course_departments table verification failed")
            return False
        
        # Check database integrity
        integrity_check = text("PRAGMA integrity_check")
        result = session.execute(integrity_check).fetchone()
        if result[0] == 'ok':
            print("✓ Database integrity check passed")
        else:
            print(f"✗ Database integrity check failed: {result[0]}")
            return False
        
        session.close()
        
        print()
        print("=" * 60)
        print("MIGRATION COMPLETED SUCCESSFULLY!")
        print("=" * 60)
        print("The shared course functionality has been added to test_9.db.")
        print("You can now:")
        print("- Create courses that are shared across multiple departments")
        print("- Assign courses to multiple departments")
        print("- Track which department is the primary owner of a shared course")
        print("- Use common course functionality in timetable generation")
        print()
        return True
        
    except Exception as e:
        print()
        print("=" * 60)
        print("MIGRATION FAILED!")
        print("=" * 60)
        print(f"Error: {e}")
        print("Please check the error messages above and resolve any issues.")
        return False

if __name__ == "__main__":
    success = migrate_test_9_db()
    sys.exit(0 if success else 1)