<?php
// Database Collation Fix Script
// This script fixes collation mismatches between tblareas and tblcustomers tables

include_once('includes/config.php');

echo "<h2>Database Collation Fix</h2>";
echo "<p>Checking and fixing collation issues...</p>";

try {
    // Check current collation of tblcustomers.Area field
    $checkCustomersQuery = "SHOW FULL COLUMNS FROM tblcustomers LIKE 'Area'";
    $result = mysqli_query($con, $checkCustomersQuery);
    
    if ($result && mysqli_num_rows($result) > 0) {
        $row = mysqli_fetch_assoc($result);
        $customersAreaCollation = $row['Collation'];
        echo "<p>✓ tblcustomers.Area collation: <strong>$customersAreaCollation</strong></p>";
    }
    
    // Check if tblareas table exists
    $checkAreasQuery = "SHOW TABLES LIKE 'tblareas'";
    $result = mysqli_query($con, $checkAreasQuery);
    
    if ($result && mysqli_num_rows($result) > 0) {
        // Check current collation of tblareas.AreaName field
        $checkAreasColQuery = "SHOW FULL COLUMNS FROM tblareas LIKE 'AreaName'";
        $result2 = mysqli_query($con, $checkAreasColQuery);
        
        if ($result2 && mysqli_num_rows($result2) > 0) {
            $row2 = mysqli_fetch_assoc($result2);
            $areasNameCollation = $row2['Collation'];
            echo "<p>✓ tblareas.AreaName collation: <strong>$areasNameCollation</strong></p>";
            
            // If collations don't match, fix them
            if ($customersAreaCollation !== $areasNameCollation) {
                echo "<p>⚠️ <strong>Collation mismatch detected!</strong></p>";
                echo "<p>Fixing collation by updating tblareas.AreaName to match tblcustomers.Area...</p>";
                
                // Update tblareas.AreaName collation to match tblcustomers.Area
                $fixQuery = "ALTER TABLE tblareas MODIFY COLUMN AreaName VARCHAR(100) CHARACTER SET utf8mb4 COLLATE $customersAreaCollation NOT NULL";
                
                if (mysqli_query($con, $fixQuery)) {
                    echo "<p>✅ <strong>Successfully fixed collation!</strong></p>";
                    echo "<p>tblareas.AreaName now uses collation: <strong>$customersAreaCollation</strong></p>";
                } else {
                    echo "<p>❌ <strong>Error fixing collation:</strong> " . mysqli_error($con) . "</p>";
                }
            } else {
                echo "<p>✅ <strong>Collations already match - no fix needed!</strong></p>";
            }
        } else {
            echo "<p>❌ Could not check tblareas.AreaName collation</p>";
        }
    } else {
        echo "<p>ℹ️ tblareas table doesn't exist yet - it will be created with correct collation when needed.</p>";
    }
    
} catch (Exception $e) {
    echo "<p>❌ <strong>Error:</strong> " . $e->getMessage() . "</p>";
}

echo "<hr>";
echo "<p><a href='area-management.php'>← Go to Area Management</a></p>";
echo "<p><a href='dashboard.php'>← Go to Dashboard</a></p>";
?>

<style>
body { 
    font-family: Arial, sans-serif; 
    max-width: 800px; 
    margin: 20px auto; 
    padding: 20px; 
    background: #f8f9fa; 
}
h2 { 
    color: #343a40; 
    border-bottom: 2px solid #dee2e6; 
    padding-bottom: 10px; 
}
p { 
    background: white; 
    padding: 10px; 
    border-radius: 5px; 
    box-shadow: 0 1px 3px rgba(0,0,0,0.1); 
}
</style>