#!/usr/bin/env python3
"""
Script to create an example VFX project in the database with realistic data.
"""

import sqlite3
import json
from datetime import date, datetime
from pathlib import Path

def get_database_path():
    """Get the database path."""
    possible_paths = [
        "vfx_project_management.db",
        "database.db"
    ]
    
    for path in possible_paths:
        if Path(path).exists():
            return path
    
    return "vfx_project_management.db"

def create_example_project():
    """Create an example VFX project with realistic data."""
    
    print("Creating Example VFX Project")
    print("=" * 40)
    
    db_path = get_database_path()
    print(f"Using database: {db_path}")
    
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # Check if projects table exists
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='projects'")
        if not cursor.fetchone():
            print("❌ Projects table not found. Please run the application first to create the database schema.")
            return False
        
        # Check if example project already exists
        cursor.execute("SELECT id FROM projects WHERE code_name = 'DRAGON_QUEST_2024'")
        existing_project = cursor.fetchone()
        
        if existing_project:
            print(f"✅ Example project already exists (ID: {existing_project[0]})")
            return True
        
        # Create example project data
        project_data = {
            "name": "Dragon Quest: The Awakening",
            "code_name": "DRAGON_QUEST_2024",
            "client_name": "Epic Fantasy Studios",
            "project_type": "cinema",
            "description": "A high-budget fantasy film featuring dragons, magic, and epic battles. Requires extensive VFX work including creature animation, environmental effects, and magical elements.",
            "status": "in_progress",
            "start_date": "2024-01-15",
            "end_date": "2024-12-20",
            "frame_rate": 24.0,
            "data_drive_path": "/projects/dragon_quest_2024/data",
            "publish_storage_path": "/projects/dragon_quest_2024/publish",
            "delivery_image_resolution": "4096x2160",
            "delivery_movie_specs_by_department": {
                "layout": {
                    "resolution": "1920x1080",
                    "format": "mov",
                    "codec": "h264",
                    "quality": "medium"
                },
                "animation": {
                    "resolution": "2048x1080",
                    "format": "mov",
                    "codec": "h264",
                    "quality": "high"
                },
                "lighting": {
                    "resolution": "4096x2160",
                    "format": "exr",
                    "codec": None,
                    "quality": "high"
                },
                "composite": {
                    "resolution": "4096x2160",
                    "format": "mov",
                    "codec": "prores",
                    "quality": "high"
                },
                "modeling": {
                    "resolution": "1920x1080",
                    "format": "mov",
                    "codec": "h264",
                    "quality": "medium"
                },
                "rigging": {
                    "resolution": "1920x1080",
                    "format": "mov",
                    "codec": "h264",
                    "quality": "medium"
                },
                "surfacing": {
                    "resolution": "2048x1080",
                    "format": "mov",
                    "codec": "h264",
                    "quality": "high"
                }
            }
        }
        
        # Convert delivery specs to JSON string
        delivery_specs_json = json.dumps(project_data["delivery_movie_specs_by_department"])
        
        # Insert the project
        insert_query = """
        INSERT INTO projects (
            name, code_name, client_name, project_type, description, status,
            start_date, end_date, frame_rate, data_drive_path, publish_storage_path,
            delivery_image_resolution, delivery_movie_specs_by_department,
            created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """
        
        current_time = datetime.now().isoformat()
        
        cursor.execute(insert_query, (
            project_data["name"],
            project_data["code_name"],
            project_data["client_name"],
            project_data["project_type"],
            project_data["description"],
            project_data["status"],
            project_data["start_date"],
            project_data["end_date"],
            project_data["frame_rate"],
            project_data["data_drive_path"],
            project_data["publish_storage_path"],
            project_data["delivery_image_resolution"],
            delivery_specs_json,
            current_time,
            current_time
        ))
        
        project_id = cursor.lastrowid
        
        # Commit the changes
        conn.commit()
        
        print(f"✅ Example project created successfully!")
        print(f"   Project ID: {project_id}")
        print(f"   Name: {project_data['name']}")
        print(f"   Code: {project_data['code_name']}")
        print(f"   Client: {project_data['client_name']}")
        print(f"   Type: {project_data['project_type'].upper()}")
        print(f"   Status: {project_data['status'].replace('_', ' ').title()}")
        print(f"   Frame Rate: {project_data['frame_rate']} fps")
        print(f"   Resolution: {project_data['delivery_image_resolution']}")
        print(f"   Departments: {', '.join(project_data['delivery_movie_specs_by_department'].keys())}")
        
        # Verify the project was created correctly
        cursor.execute("""
            SELECT name, code_name, frame_rate, delivery_image_resolution 
            FROM projects WHERE id = ?
        """, (project_id,))
        
        verification = cursor.fetchone()
        if verification:
            print(f"\n✅ Verification successful:")
            print(f"   Database Name: {verification[0]}")
            print(f"   Database Code: {verification[1]}")
            print(f"   Database Frame Rate: {verification[2]} fps")
            print(f"   Database Resolution: {verification[3]}")
        
        return True
        
    except sqlite3.Error as e:
        print(f"❌ Database error: {e}")
        if conn:
            conn.rollback()
        return False
    except Exception as e:
        print(f"❌ Unexpected error: {e}")
        if conn:
            conn.rollback()
        return False
    finally:
        if conn:
            conn.close()

def show_all_projects():
    """Display all projects in the database."""
    
    print("\nAll Projects in Database:")
    print("-" * 40)
    
    db_path = get_database_path()
    
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        cursor.execute("""
            SELECT id, name, code_name, client_name, project_type, status, 
                   frame_rate, delivery_image_resolution
            FROM projects
            ORDER BY created_at DESC
        """)
        
        projects = cursor.fetchall()
        
        if not projects:
            print("No projects found in database.")
            return
        
        for project in projects:
            print(f"ID: {project[0]}")
            print(f"  Name: {project[1]}")
            print(f"  Code: {project[2]}")
            print(f"  Client: {project[3]}")
            print(f"  Type: {project[4].upper()}")
            print(f"  Status: {project[5].replace('_', ' ').title()}")
            print(f"  Frame Rate: {project[6]} fps")
            print(f"  Resolution: {project[7]}")
            print()
        
    except sqlite3.Error as e:
        print(f"❌ Database error: {e}")
    except Exception as e:
        print(f"❌ Unexpected error: {e}")
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    print("VFX Project Management - Example Project Creator")
    print("=" * 50)
    
    success = create_example_project()
    
    if success:
        show_all_projects()
        print("\n🎬 Example project created successfully!")
        print("You can now test the VFX project management system with realistic data.")
    else:
        print("\n❌ Failed to create example project.")