#!/usr/bin/env python
"""
Migration script to transfer data from SQLite to MySQL
Run this script to migrate all data from db.sqlite3 to MySQL database

Usage:
    python migrate_sqlite_to_mysql.py

Prerequisites:
    - db.sqlite3 file exists in project root
    - MySQL database is running and accessible
    - Environment variables for MySQL are set (or defaults will be used)
"""

import os
import sys
import django
from django.conf import settings
from django.core.management import execute_from_command_line

# Setup Django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'worktimeapp.settings')
django.setup()

from django.db import connections
from django.core.management import call_command
import json

def get_sqlite_connection():
    """Get connection to SQLite database"""
    from django.db import connections
    return connections['sqlite']

def get_mysql_connection():
    """Get connection to MySQL database"""
    from django.db import connections
    return connections['default']

def migrate_data():
    """Migrate all data from SQLite to MySQL"""
    print("=" * 60)
    print("SQLite to MySQL Data Migration")
    print("=" * 60)
    
    # Step 1: Create MySQL database schema
    print("\n[1/4] Creating MySQL database schema...")
    try:
        call_command('migrate', '--database=default', verbosity=1, interactive=False)
        print("✓ MySQL schema created")
    except Exception as e:
        print(f"✗ Error creating schema: {e}")
        return False
    
    # Step 2: Dump SQLite data
    print("\n[2/4] Dumping SQLite data...")
    try:
        # Use Django's dumpdata to export all data
        # Exclude tables that don't have corresponding models (like customers)
        with open('sqlite_data_dump.json', 'w', encoding='utf-8') as f:
            call_command('dumpdata', 
                        database='sqlite',
                        exclude=['contenttypes', 'auth.permission', 'admin.logentry'],
                        natural_foreign=True,
                        natural_primary=True,
                        stdout=f,
                        verbosity=1)
        print("✓ SQLite data exported to sqlite_data_dump.json")
    except Exception as e:
        # If dumpdata fails due to missing models, try to dump specific apps
        print(f"⚠ Warning: Full dump failed ({e})")
        print("  Attempting to dump by app...")
        try:
            apps_to_dump = [
                'user', 'stamps', 'configurations', 'functions', 'paycode',
                'company', 'shift', 'supervisor', 'supervisorgroup',
                'employeetypes', 'questionconfigurations', 'balances',
                'balancedetail', 'userSettings', 'ml_service', 'violations',
                'ErrorLogs', 'worktimeservice'
            ]
            all_data = []
            for app in apps_to_dump:
                try:
                    from io import StringIO
                    output = StringIO()
                    call_command('dumpdata', 
                                app,
                                database='sqlite',
                                exclude=['contenttypes', 'auth.permission', 'admin.logentry'],
                                natural_foreign=True,
                                natural_primary=True,
                                stdout=output,
                                verbosity=0)
                    output.seek(0)
                    app_data = json.load(output)
                    if isinstance(app_data, list):
                        all_data.extend(app_data)
                    else:
                        all_data.append(app_data)
                    print(f"  ✓ Dumped {app}")
                except Exception as app_error:
                    print(f"  ⚠ Skipped {app}: {app_error}")
            
            with open('sqlite_data_dump.json', 'w', encoding='utf-8') as f:
                json.dump(all_data, f, indent=2, ensure_ascii=False)
            print("✓ SQLite data exported to sqlite_data_dump.json (by app)")
        except Exception as e2:
            print(f"✗ Error dumping SQLite data: {e2}")
            return False
    
    # Step 3: Load data into MySQL
    print("\n[3/4] Loading data into MySQL...")
    try:
        # Preprocess JSON to fix NULL values for timestamp fields
        print("  Preprocessing data to fix NULL timestamps...")
        with open('sqlite_data_dump.json', 'r', encoding='utf-8') as f:
            data = json.load(f)
        
        from django.utils import timezone
        default_time = timezone.now().isoformat()
        
        # Fix NULL values for common timestamp fields
        fixed_count = 0
        for item in data:
            if isinstance(item, dict) and 'fields' in item:
                fields = item['fields']
                # Fix created_at if NULL
                if 'created_at' in fields and fields['created_at'] is None:
                    fields['created_at'] = default_time
                    fixed_count += 1
                # Fix updated_at if NULL
                if 'updated_at' in fields and fields['updated_at'] is None:
                    fields['updated_at'] = default_time
                    fixed_count += 1
        
        if fixed_count > 0:
            print(f"  ✓ Fixed {fixed_count} NULL timestamp fields")
            # Write fixed data to a new file
            with open('sqlite_data_dump_fixed.json', 'w', encoding='utf-8') as f:
                json.dump(data, f, indent=2, ensure_ascii=False)
            dump_file = 'sqlite_data_dump_fixed.json'
        else:
            dump_file = 'sqlite_data_dump.json'
        
        # Load the data
        call_command('loaddata', 
                    dump_file,
                    database='default',
                    verbosity=1,
                    ignorenonexistent=True)
        print("✓ Data loaded into MySQL")
    except Exception as e:
        print(f"✗ Error loading data into MySQL: {e}")
        print("  This might be due to existing data conflicts.")
        print("  Try: python manage.py flush --database=default (WARNING: This deletes all MySQL data)")
        return False
    
    # Step 4: Verify migration
    print("\n[4/4] Verifying migration...")
    try:
        from django.db import connections
        sqlite_conn = connections['sqlite']
        mysql_conn = connections['default']
        
        # Count records in key tables
        key_tables = ['user_user', 'stamps_stamp', 'configurations_configuration']
        for table in key_tables:
            try:
                with sqlite_conn.cursor() as cursor:
                    cursor.execute(f"SELECT COUNT(*) FROM {table}")
                    sqlite_count = cursor.fetchone()[0]
                
                with mysql_conn.cursor() as cursor:
                    cursor.execute(f"SELECT COUNT(*) FROM {table}")
                    mysql_count = cursor.fetchone()[0]
                
                status = "✓" if sqlite_count == mysql_count else "⚠"
                print(f"  {status} {table}: SQLite={sqlite_count}, MySQL={mysql_count}")
            except Exception as e:
                print(f"  ⚠ {table}: Error checking - {e}")
        
        print("\n✓ Migration verification complete")
    except Exception as e:
        print(f"⚠ Verification warning: {e}")
    
    print("\n" + "=" * 60)
    print("Migration Complete!")
    print("=" * 60)
    print("\nNext steps:")
    print("1. Verify data in MySQL database")
    print("2. Update Django settings to use MySQL as default")
    print("3. Test the application with MySQL")
    print("4. Backup db.sqlite3 (keep as backup)")
    print("5. Remove sqlite_data_dump.json if migration is successful")
    
    return True

if __name__ == '__main__':
    # Check if SQLite database exists
    if not os.path.exists('db.sqlite3'):
        print("✗ Error: db.sqlite3 not found in current directory")
        sys.exit(1)
    
    # Run migration
    success = migrate_data()
    sys.exit(0 if success else 1)