#!/usr/bin/env python
"""
Deploy expenses using Django migrations (recommended method)
This will automatically handle foreign key constraints correctly
"""

import os
import sys
import django

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

from django.core.management import call_command
from django.db import connection

print("=" * 70)
print("EXPENSES DEPLOYMENT - DJANGO MIGRATIONS METHOD")
print("=" * 70)
print()

# Step 1: Check if expenses app is in INSTALLED_APPS
print("Step 1: Checking INSTALLED_APPS...")
from django.conf import settings
if 'expenses' in settings.INSTALLED_APPS:
    print("✓ 'expenses' found in INSTALLED_APPS")
else:
    print("✗ 'expenses' NOT in INSTALLED_APPS")
    print("  Please add 'expenses' to INSTALLED_APPS in settings.py")
    sys.exit(1)

print()

# Step 2: Check database connection
print("Step 2: Checking database connection...")
try:
    with connection.cursor() as cursor:
        cursor.execute("SELECT 1")
    print("✓ Database connection successful")
except Exception as e:
    print(f"✗ Database connection failed: {e}")
    sys.exit(1)

print()

# Step 3: Check if table already exists
print("Step 3: Checking if expenses table exists...")
try:
    with connection.cursor() as cursor:
        cursor.execute("SHOW TABLES LIKE 'expenses_expense'")
        result = cursor.fetchone()
        if result:
            print("⚠ Table 'expenses_expense' already exists")
            response = input("  Do you want to drop and recreate it? (yes/no): ")
            if response.lower() == 'yes':
                cursor.execute("DROP TABLE expenses_expense")
                print("✓ Table dropped")
            else:
                print("  Skipping table creation")
        else:
            print("✓ Table does not exist, will create")
except Exception as e:
    print(f"⚠ Could not check table: {e}")

print()

# Step 4: Create migrations
print("Step 4: Creating migrations for expenses app...")
try:
    call_command('makemigrations', 'expenses', interactive=False)
    print("✓ Migrations created")
except Exception as e:
    print(f"⚠ Migration creation: {e}")
    print("  (This is OK if migrations already exist)")

print()

# Step 5: Run migrations
print("Step 5: Running migrations...")
try:
    call_command('migrate', 'expenses', interactive=False)
    print("✓ Migrations applied successfully")
except Exception as e:
    print(f"✗ Migration failed: {e}")
    sys.exit(1)

print()

# Step 6: Verify table was created
print("Step 6: Verifying table creation...")
try:
    with connection.cursor() as cursor:
        cursor.execute("SHOW TABLES LIKE 'expenses_expense'")
        result = cursor.fetchone()
        if result:
            print("✓ Table 'expenses_expense' exists")
            
            # Count columns
            cursor.execute("DESCRIBE expenses_expense")
            columns = cursor.fetchall()
            print(f"✓ Table has {len(columns)} columns")
            
            # Check for data
            cursor.execute("SELECT COUNT(*) FROM expenses_expense")
            count = cursor.fetchone()[0]
            print(f"✓ Table has {count} records")
        else:
            print("✗ Table was not created")
            sys.exit(1)
except Exception as e:
    print(f"✗ Verification failed: {e}")
    sys.exit(1)

print()

# Step 7: Check foreign keys
print("Step 7: Checking foreign key constraints...")
try:
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT 
                CONSTRAINT_NAME,
                COLUMN_NAME,
                REFERENCED_TABLE_NAME,
                REFERENCED_COLUMN_NAME
            FROM information_schema.KEY_COLUMN_USAGE
            WHERE TABLE_NAME = 'expenses_expense'
            AND CONSTRAINT_NAME LIKE '%fk%'
            AND TABLE_SCHEMA = DATABASE()
        """)
        fks = cursor.fetchall()
        if fks:
            print(f"✓ Found {len(fks)} foreign key constraints:")
            for fk in fks:
                print(f"  - {fk[0]}: {fk[1]} → {fk[2]}.{fk[3]}")
        else:
            print("⚠ No foreign key constraints found")
            print("  (This might be OK depending on your database setup)")
except Exception as e:
    print(f"⚠ Could not check foreign keys: {e}")

print()
print("=" * 70)
print("DEPLOYMENT COMPLETE!")
print("=" * 70)
print()
print("Next steps:")
print("1. Visit /expenses/ to test the system")
print("2. Create a test expense")
print("3. Check the analytics dashboard")
print()