#!/usr/bin/env python3
"""
Standalone MySQL Collation Fix
Works without Django - uses direct MySQL connection
"""

import mysql.connector
import os
from dotenv import load_dotenv

# Load environment variables
load_dotenv()

def get_db_connection():
    """Get MySQL connection from environment variables"""
    try:
        connection = mysql.connector.connect(
            host=os.getenv('DB_HOST', 'localhost'),
            database=os.getenv('DB_NAME', 'xygbfpsg_graz'),
            user=os.getenv('DB_USER', 'acbptxvs'),
            password=os.getenv('DB_PASSWORD', ''),
            charset='utf8mb4',
            collation='utf8mb4_unicode_ci'
        )
        return connection
    except Exception as e:
        print(f"Connection failed: {e}")
        # Try with manual input
        print("Please enter database credentials:")
        host = input("Host (localhost): ") or "localhost"
        database = input("Database (xygbfpsg_graz): ") or "xygbfpsg_graz"
        user = input("Username (acbptxvs): ") or "acbptxvs"
        password = input("Password: ")
        
        connection = mysql.connector.connect(
            host=host,
            database=database,
            user=user,
            password=password,
            charset='utf8mb4',
            collation='utf8mb4_unicode_ci'
        )
        return connection

def fix_collations():
    """Fix all collation issues"""
    print("Starting standalone collation fix...")
    
    connection = get_db_connection()
    cursor = connection.cursor()
    
    fixes = [
        # Set database default
        "ALTER DATABASE xygbfpsg_graz CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",
        
        # Create missing table
        """CREATE TABLE IF NOT EXISTS `portfolio_performance` (
            `id` bigint NOT NULL AUTO_INCREMENT,
            `date` date NOT NULL,
            `total_loans` int DEFAULT 0,
            `active_loans` int DEFAULT 0,
            `overdue_loans` int DEFAULT 0,
            `total_amount` decimal(15,2) DEFAULT 0.00,
            `collected_amount` decimal(15,2) DEFAULT 0.00,
            `outstanding_amount` decimal(15,2) DEFAULT 0.00,
            `created_at` datetime(6) NOT NULL,
            `updated_at` datetime(6) NOT NULL,
            PRIMARY KEY (`id`),
            UNIQUE KEY `date` (`date`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",
    ]
    
    # Get all tables and add them to fixes
    cursor.execute("SHOW TABLES")
    tables = [table[0] for table in cursor.fetchall()]
    
    for table in tables:
        fixes.append(f"ALTER TABLE `{table}` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
    
    success_count = 0
    error_count = 0
    
    for i, sql in enumerate(fixes, 1):
        try:
            print(f"Executing fix {i}/{len(fixes)}...")
            cursor.execute(sql)
            connection.commit()
            print(f"✓ Fix {i} completed")
            success_count += 1
        except Exception as e:
            print(f"✗ Fix {i} failed: {str(e)}")
            error_count += 1
            # Continue with other fixes
    
    cursor.close()
    connection.close()
    
    print(f"\nStandalone fix completed!")
    print(f"Successful: {success_count}")
    print(f"Errors: {error_count}")
    
    if error_count == 0:
        print("✓ All fixes applied successfully!")
    else:
        print("⚠ Some fixes had errors, but the main issues should be resolved")

if __name__ == "__main__":
    fix_collations()