<?php
/**
 * Analyze and Fix Roles - Comprehensive script to check database structure and fix roles
 * 
 * This script:
 * 1. Checks the current structure of the 'role' field
 * 2. Shows current role values in the database
 * 3. Updates all store owners to role = 'store_owner'
 * 4. Ensures database structure supports the new role system
 */

require_once 'config.php';
require_once 'app/classes/StoreManager.php';

echo "=== Database Role Structure Analysis and Fix ===\n\n";

// Step 1: Check the structure of the 'role' field
echo "Step 1: Checking 'role' field structure...\n";
echo str_repeat("-", 60) . "\n";

$columnInfoStmt = $conn->prepare("SHOW COLUMNS FROM agents WHERE Field = 'role'");
$columnInfoStmt->execute();
$columnResult = $columnInfoStmt->get_result();
$columnInfo = $columnResult->fetch_assoc();
$columnInfoStmt->close();

if ($columnInfo) {
    echo "Field: " . $columnInfo['Field'] . "\n";
    echo "Type: " . $columnInfo['Type'] . "\n";
    echo "Null: " . $columnInfo['Null'] . "\n";
    echo "Key: " . ($columnInfo['Key'] ?: 'None') . "\n";
    echo "Default: " . ($columnInfo['Default'] ?? 'NULL') . "\n";
    echo "Extra: " . ($columnInfo['Extra'] ?? 'None') . "\n";
    
    // Check if it's an ENUM and what values it allows
    if (stripos($columnInfo['Type'], 'enum') !== false) {
        echo "\n⚠️  WARNING: Role field is an ENUM type\n";
        echo "Current allowed values: " . $columnInfo['Type'] . "\n";
        
        // Check if 'store_owner' is in the enum
        if (stripos($columnInfo['Type'], 'store_owner') === false) {
            echo "❌ 'store_owner' is NOT in the ENUM values!\n";
            echo "Need to alter the table to add 'store_owner' to the ENUM.\n\n";
            
            // Extract current enum values
            preg_match("/enum\((.+)\)/i", $columnInfo['Type'], $matches);
            if (isset($matches[1])) {
                $enumValues = $matches[1];
                echo "Current ENUM values: $enumValues\n";
                echo "Will add 'store_owner' to the ENUM...\n";
                
                // Alter table to add 'store_owner' to enum
                $alterSql = "ALTER TABLE agents MODIFY COLUMN role ENUM($enumValues, 'store_owner')";
                if (mysqli_query($conn, $alterSql)) {
                    echo "✓ Successfully added 'store_owner' to ENUM\n\n";
                } else {
                    echo "✗ Failed to alter ENUM: " . mysqli_error($conn) . "\n";
                    echo "Trying alternative: Change to VARCHAR...\n";
                    
                    // Alternative: Change to VARCHAR if ENUM modification fails
                    $alterSql2 = "ALTER TABLE agents MODIFY COLUMN role VARCHAR(50) DEFAULT 'agent'";
                    if (mysqli_query($conn, $alterSql2)) {
                        echo "✓ Changed role field to VARCHAR(50)\n\n";
                    } else {
                        echo "✗ Failed: " . mysqli_error($conn) . "\n\n";
                    }
                }
            }
        } else {
            echo "✓ 'store_owner' is already in the ENUM values\n\n";
        }
    } else {
        echo "\n✓ Role field is VARCHAR/TEXT type - supports any value\n\n";
    }
} else {
    echo "❌ ERROR: 'role' column not found in agents table!\n\n";
    exit(1);
}

// Step 2: Check current role values in database
echo "Step 2: Analyzing current role values...\n";
echo str_repeat("-", 60) . "\n";

$roleStatsStmt = $conn->prepare("
    SELECT role, COUNT(*) as count 
    FROM agents 
    GROUP BY role 
    ORDER BY count DESC
");
$roleStatsStmt->execute();
$roleStatsResult = $roleStatsStmt->get_result();

echo "Current role distribution:\n";
while ($row = $roleStatsResult->fetch_assoc()) {
    echo "  - '{$row['role']}': {$row['count']} users\n";
}
$roleStatsStmt->close();

// Step 3: Find store owners and their current roles
echo "\nStep 3: Finding store owners...\n";
echo str_repeat("-", 60) . "\n";

$storeOwnersStmt = $conn->prepare("
    SELECT DISTINCT 
        a.id, 
        a.username, 
        a.email, 
        a.role, 
        a.is_store_owner, 
        a.store_id, 
        s.id as store_id_owned,
        s.store_name
    FROM agents a
    INNER JOIN stores s ON s.owner_id = a.id
    ORDER BY a.id
");
$storeOwnersStmt->execute();
$storeOwnersResult = $storeOwnersStmt->get_result();

$storeOwners = [];
$needsFix = [];

while ($row = $storeOwnersResult->fetch_assoc()) {
    $storeOwners[] = $row;
    if ($row['role'] !== 'store_owner' || $row['is_store_owner'] != 1 || $row['store_id'] != $row['store_id_owned']) {
        $needsFix[] = $row;
    }
}

echo "Found " . count($storeOwners) . " store owner(s):\n";
foreach ($storeOwners as $owner) {
    echo "  - {$owner['username']} (ID: {$owner['id']}, Store: {$owner['store_name']})\n";
    echo "    Current: role='{$owner['role']}', is_store_owner={$owner['is_store_owner']}, store_id={$owner['store_id']}\n";
    if ($owner['role'] !== 'store_owner') {
        echo "    ⚠️  NEEDS FIX: Role should be 'store_owner'\n";
    }
    echo "\n";
}

$storeOwnersStmt->close();

// Step 4: Fix store owners
if (!empty($needsFix)) {
    echo "Step 4: Fixing store owners...\n";
    echo str_repeat("-", 60) . "\n";
    
    $fixedCount = 0;
    foreach ($needsFix as $owner) {
        $updateStmt = $conn->prepare("
            UPDATE agents 
            SET role = 'store_owner', is_store_owner = 1, store_id = ? 
            WHERE id = ?
        ");
        $updateStmt->bind_param("ii", $owner['store_id_owned'], $owner['id']);
        
        if ($updateStmt->execute()) {
            echo "✓ Fixed: {$owner['username']} (ID: {$owner['id']})\n";
            echo "  Updated: role='store_owner', is_store_owner=1, store_id={$owner['store_id_owned']}\n\n";
            $fixedCount++;
        } else {
            echo "✗ Failed to fix: {$owner['username']} - " . $updateStmt->error . "\n\n";
        }
        $updateStmt->close();
    }
    
    echo "Fixed: $fixedCount store owner(s)\n\n";
} else {
    echo "Step 4: All store owners already have correct roles!\n\n";
}

// Step 5: Verify the fix
echo "Step 5: Verifying fixes...\n";
echo str_repeat("-", 60) . "\n";

$verifyStmt = $conn->prepare("
    SELECT 
        a.id, 
        a.username, 
        a.role, 
        a.is_store_owner, 
        a.store_id,
        s.id as store_exists
    FROM agents a
    LEFT JOIN stores s ON s.owner_id = a.id
    WHERE s.id IS NOT NULL
    ORDER BY a.id
");
$verifyStmt->execute();
$verifyResult = $verifyStmt->get_result();

$allCorrect = true;
while ($row = $verifyResult->fetch_assoc()) {
    if ($row['role'] !== 'store_owner' || $row['is_store_owner'] != 1) {
        echo "❌ Still incorrect: {$row['username']} - role='{$row['role']}', is_store_owner={$row['is_store_owner']}\n";
        $allCorrect = false;
    }
}

if ($allCorrect) {
    echo "✓ All store owners have correct roles!\n\n";
} else {
    echo "⚠️  Some store owners still need fixing\n\n";
}

$verifyStmt->close();

// Step 6: Final statistics
echo "Step 6: Final Statistics...\n";
echo str_repeat("-", 60) . "\n";

$finalStatsStmt = $conn->prepare("
    SELECT 
        role, 
        COUNT(*) as count,
        SUM(CASE WHEN is_store_owner = 1 THEN 1 ELSE 0 END) as store_owners_count
    FROM agents 
    GROUP BY role 
    ORDER BY count DESC
");
$finalStatsStmt->execute();
$finalStatsResult = $finalStatsStmt->get_result();

echo "Final role distribution:\n";
while ($row = $finalStatsResult->fetch_assoc()) {
    $storeOwnerNote = ($row['store_owners_count'] > 0) ? " ({$row['store_owners_count']} with is_store_owner=1)" : "";
    echo "  - '{$row['role']}': {$row['count']} users$storeOwnerNote\n";
}
$finalStatsStmt->close();

echo "\n=== Analysis Complete ===\n";
echo "Store owners should now be able to login correctly!\n";
?>

