#!/usr/bin/env python
"""
Fix Customer Requests Table Issue
=================================

This script fixes the missing customer_requests table in production by:
1. Checking if the table exists
2. Creating the table if it doesn't exist
3. Running the necessary migrations
4. Verifying the fix

Run this script from the Django project root directory.
"""

import os
import sys
import subprocess
import django
from django.conf import settings
from django.db import connection
from django.core.management import execute_from_command_line

def setup_django():
    """Setup Django environment"""
    os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings')
    django.setup()

def check_table_exists(table_name):
    """Check if a table exists in the database"""
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT COUNT(*)
            FROM information_schema.tables 
            WHERE table_schema = DATABASE() 
            AND table_name = %s
        """, [table_name])
        return cursor.fetchone()[0] > 0

def run_command(command, description=""):
    """Run a shell command and handle errors"""
    print(f"\n{'='*60}")
    print(f"EXECUTING: {description or command}")
    print(f"{'='*60}")
    
    try:
        result = subprocess.run(command, shell=True, check=True, capture_output=True, text=True)
        if result.stdout:
            print(result.stdout)
        return True
    except subprocess.CalledProcessError as e:
        print(f"ERROR: {e}")
        if e.stdout:
            print(f"STDOUT: {e.stdout}")
        if e.stderr:
            print(f"STDERR: {e.stderr}")
        return False

def main():
    print("""
    ╔══════════════════════════════════════════════════════════════╗
    ║              FIX CUSTOMER REQUESTS TABLE ISSUE               ║
    ║                    Production Database Fix                   ║
    ╚══════════════════════════════════════════════════════════════╝
    
    This script will:
    1. Check if customer_requests table exists
    2. Run migrations to create missing tables
    3. Verify the fix works
    4. Test the customer requests endpoint
    
    """)
    
    # Setup Django
    setup_django()
    
    # Check if we're in the right directory
    if not os.path.exists('manage.py'):
        print("ERROR: This script must be run from the Django project root directory")
        sys.exit(1)
    
    print("✓ Django project detected")
    
    # Step 1: Check table existence
    print("\n" + "="*60)
    print("STEP 1: CHECKING TABLE EXISTENCE")
    print("="*60)
    
    tables_to_check = [
        'customer_requests',
        'registration_fees', 
        'registration_fee_payments',
        'report_schedules',
        'report_executions'
    ]
    
    missing_tables = []
    for table in tables_to_check:
        exists = check_table_exists(table)
        if exists:
            print(f"✓ Table '{table}' exists")
        else:
            print(f"✗ Table '{table}' is missing")
            missing_tables.append(table)
    
    if not missing_tables:
        print("\n✓ All required tables exist!")
        print("The issue might be with model imports or Django configuration.")
        return
    
    # Step 2: Run migrations
    print("\n" + "="*60)
    print("STEP 2: RUNNING MIGRATIONS")
    print("="*60)
    
    print("Running Django migrations to create missing tables...")
    
    # First, check migration status
    if not run_command("python manage.py showmigrations reports", "Checking migration status"):
        print("Warning: Could not check migration status")
    
    # Run migrations
    if not run_command("python manage.py migrate reports", "Applying reports migrations"):
        print("ERROR: Failed to apply migrations!")
        
        # Try to run all migrations
        print("Trying to run all migrations...")
        if not run_command("python manage.py migrate", "Applying all migrations"):
            print("ERROR: All migrations failed!")
            sys.exit(1)
    
    # Step 3: Verify tables were created
    print("\n" + "="*60)
    print("STEP 3: VERIFYING TABLE CREATION")
    print("="*60)
    
    all_created = True
    for table in missing_tables:
        exists = check_table_exists(table)
        if exists:
            print(f"✓ Table '{table}' now exists")
        else:
            print(f"✗ Table '{table}' still missing")
            all_created = False
    
    if not all_created:
        print("\nERROR: Some tables are still missing!")
        print("You may need to manually create the tables or check your database configuration.")
        sys.exit(1)
    
    # Step 4: Test Django models
    print("\n" + "="*60)
    print("STEP 4: TESTING DJANGO MODELS")
    print("="*60)
    
    try:
        from reports.enhanced_models import CustomerRequest, RegistrationFee
        
        # Test model access
        count = CustomerRequest.objects.count()
        print(f"✓ CustomerRequest model accessible - {count} records found")
        
        count = RegistrationFee.objects.count()
        print(f"✓ RegistrationFee model accessible - {count} records found")
        
    except Exception as e:
        print(f"✗ Error accessing models: {e}")
        sys.exit(1)
    
    # Step 5: Test the problematic endpoint
    print("\n" + "="*60)
    print("STEP 5: TESTING CUSTOMER REQUESTS ENDPOINT")
    print("="*60)
    
    try:
        from django.test import Client
        from django.contrib.auth import get_user_model
        
        # Create a test client
        client = Client()
        
        # Try to access the customer requests page (this will fail if no user is logged in, but should not give table error)
        response = client.get('/reports/customer-requests/')
        
        if response.status_code == 302:  # Redirect to login
            print("✓ Customer requests endpoint accessible (redirects to login as expected)")
        elif response.status_code == 200:
            print("✓ Customer requests endpoint accessible")
        else:
            print(f"⚠ Customer requests endpoint returned status {response.status_code}")
            
    except Exception as e:
        if "doesn't exist" in str(e):
            print(f"✗ Table still missing: {e}")
            sys.exit(1)
        else:
            print(f"⚠ Other error (may be normal): {e}")
    
    # Step 6: Create sample data (optional)
    print("\n" + "="*60)
    print("STEP 6: SAMPLE DATA SETUP")
    print("="*60)
    
    create_sample = input("Do you want to create sample registration fees data? (y/n): ").lower().strip()
    if create_sample == 'y':
        try:
            from reports.enhanced_models import RegistrationFee
            from django.contrib.auth import get_user_model
            
            User = get_user_model()
            admin_user = User.objects.filter(is_superuser=True).first()
            
            # Create sample registration fees
            sample_fees = [
                {
                    'product_type': 'boost',
                    'fee_name': 'Boost Registration Fee',
                    'amount': 500.00,
                    'description': 'Registration fee for Boost loan product'
                },
                {
                    'product_type': 'boost_plus',
                    'fee_name': 'Boost Plus Registration Fee', 
                    'amount': 750.00,
                    'description': 'Registration fee for Boost Plus loan product'
                },
                {
                    'product_type': 'account_opening',
                    'fee_name': 'Account Opening Fee',
                    'amount': 200.00,
                    'description': 'Fee for opening new customer account'
                }
            ]
            
            created_count = 0
            for fee_data in sample_fees:
                fee, created = RegistrationFee.objects.get_or_create(
                    product_type=fee_data['product_type'],
                    fee_name=fee_data['fee_name'],
                    defaults={
                        'amount': fee_data['amount'],
                        'description': fee_data['description'],
                        'created_by': admin_user
                    }
                )
                if created:
                    created_count += 1
                    print(f"✓ Created: {fee.fee_name}")
                else:
                    print(f"- Already exists: {fee.fee_name}")
            
            print(f"\n✓ Created {created_count} new registration fees")
            
        except Exception as e:
            print(f"✗ Error creating sample data: {e}")
    
    # Final summary
    print("\n" + "="*60)
    print("FIX SUMMARY")
    print("="*60)
    
    print("""
    ✓ Customer requests table issue has been resolved!
    
    WHAT WAS FIXED:
    1. Missing database tables have been created
    2. Django models are now accessible
    3. Customer requests endpoint should work
    4. All enhanced models are properly registered
    
    TABLES CREATED:
    - customer_requests
    - registration_fees
    - registration_fee_payments
    - report_schedules
    - report_executions
    
    NEXT STEPS:
    1. Test the customer requests page: /reports/customer-requests/
    2. Verify all report endpoints work properly
    3. Consider creating sample data for testing
    4. Deploy this fix to production
    
    PRODUCTION DEPLOYMENT:
    To apply this fix to production, run:
    python manage.py migrate reports
    
    This will create the missing tables in your production database.
    """)
    
    print("\n🎉 CUSTOMER REQUESTS TABLE FIX COMPLETED! 🎉")

if __name__ == "__main__":
    main()