"""
Database Migration - Add Extra Files Table
Creates the extra_files table for storing additional set files
"""

import sqlite3
import os

def find_database():
    """Find the database file."""
    script_dir = os.path.dirname(os.path.abspath(__file__))
    possible_paths = [
        os.path.join(script_dir, 'instance', 'lego_instructions.db'),
        os.path.join(script_dir, 'lego_instructions.db'),
        os.path.join(script_dir, 'app', 'lego_instructions.db'),
    ]
    
    for path in possible_paths:
        if os.path.exists(path):
            return path
    
    return None

def main():
    db_path = find_database()
    
    if not db_path:
        print("=" * 70)
        print("❌ Database not found!")
        print("=" * 70)
        return
    
    print("=" * 70)
    print("Database Migration - Extra Files Table")
    print("=" * 70)
    print()
    print(f"Database: {db_path}")
    print()
    
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # Check if table exists
        cursor.execute("""
            SELECT name FROM sqlite_master 
            WHERE type='table' AND name='extra_files'
        """)
        
        if cursor.fetchone():
            print("✅ Table 'extra_files' already exists!")
            print("   No migration needed.")
        else:
            print("Creating 'extra_files' table...")
            
            cursor.execute("""
                CREATE TABLE extra_files (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    set_id INTEGER NOT NULL,
                    file_name VARCHAR(255) NOT NULL,
                    original_filename VARCHAR(255) NOT NULL,
                    file_path VARCHAR(500) NOT NULL,
                    file_type VARCHAR(50) NOT NULL,
                    file_size INTEGER NOT NULL,
                    description TEXT,
                    category VARCHAR(50),
                    uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    uploaded_by INTEGER,
                    FOREIGN KEY (set_id) REFERENCES sets(id) ON DELETE CASCADE,
                    FOREIGN KEY (uploaded_by) REFERENCES users(id)
                )
            """)
            
            # Create indexes
            cursor.execute("""
                CREATE INDEX idx_extra_files_set_id ON extra_files(set_id)
            """)
            
            cursor.execute("""
                CREATE INDEX idx_extra_files_category ON extra_files(category)
            """)
            
            cursor.execute("""
                CREATE INDEX idx_extra_files_uploaded_at ON extra_files(uploaded_at)
            """)
            
            conn.commit()
            print("✅ Successfully created 'extra_files' table!")
            print("✅ Created indexes!")
        
        conn.close()
        
        print()
        print("=" * 70)
        print("Migration complete!")
        print("=" * 70)
        print()
        print("What you can do now:")
        print("  1. Upload extra files to your sets")
        print("  2. Store BrickLink XMLs, Stud.io files, box art, etc.")
        print("  3. Keep everything organized in one place!")
        print()
        
    except Exception as e:
        print(f"❌ Error: {e}")
        return

if __name__ == "__main__":
    main()