<?php
/**
 * Convert Agents Table for Store Owner Role System
 * Run this script to ensure proper structure and data
 */

require_once 'config.php';

echo "=== Converting Agents Table for Store Owner Role ===\n\n";

// Start transaction
$conn->begin_transaction();

try {

    // Query 1: Add 'store_owner' to ENUM
    echo "Executing query 1: Add 'store_owner' to ENUM...\n";
    $query1 = "ALTER TABLE agents MODIFY COLUMN role ENUM('agent','super_agent','dealers','store_owner') DEFAULT 'agent'";
    if (mysqli_query($conn, $query1)) {
        echo "✓ Query 1 successful\n";
    } else {
        throw new Exception("Query 1 failed: " . mysqli_error($conn));
    }

    // Query 2: Update all store owners
    echo "Executing query 2: Update all store owners...\n";
    $query2 = "UPDATE agents a
INNER JOIN stores s ON s.owner_id = a.id
SET a.role = 'store_owner', a.is_store_owner = 1, a.store_id = s.id
WHERE a.role != 'store_owner' OR a.is_store_owner != 1 OR a.store_id != s.id";
    if (mysqli_query($conn, $query2)) {
        $affected = mysqli_affected_rows($conn);
        echo "✓ Query 2 successful (affected $affected rows)\n";
    } else {
        throw new Exception("Query 2 failed: " . mysqli_error($conn));
    }

    // Query 3: Fix agents who shouldn't be store owners
    echo "Executing query 3: Fix agents who shouldn't be store owners...\n";
    $query3 = "UPDATE agents a
LEFT JOIN stores s ON s.owner_id = a.id
SET a.role = 'agent', a.is_store_owner = 0
WHERE s.id IS NULL AND (a.role = 'store_owner' OR a.is_store_owner = 1)";
    if (mysqli_query($conn, $query3)) {
        $affected = mysqli_affected_rows($conn);
        echo "✓ Query 3 successful (affected $affected rows)\n";
    } else {
        throw new Exception("Query 3 failed: " . mysqli_error($conn));
    }

    // Commit transaction
    $conn->commit();
    echo "\n✓ All conversions completed successfully!\n";
    
} catch (Exception $e) {
    $conn->rollback();
    echo "\n❌ Error: " . $e->getMessage() . "\n";
    echo "All changes have been rolled back.\n";
    exit(1);
}

// Verify the conversion
echo "\n=== Verification ===\n";

// Count store owners
$verifyStoreOwnersStmt = $conn->prepare("
    SELECT 
        COUNT(DISTINCT s.owner_id) as total_store_owners,
        COUNT(DISTINCT CASE WHEN a.role = 'store_owner' AND a.is_store_owner = 1 AND a.store_id = s.id THEN a.id END) as correct_store_owners
    FROM stores s
    LEFT JOIN agents a ON s.owner_id = a.id
");
$verifyStoreOwnersStmt->execute();
$verifyStoreOwnersResult = $verifyStoreOwnersStmt->get_result();
$verifyStoreOwnersData = $verifyStoreOwnersResult->fetch_assoc();
$verifyStoreOwnersStmt->close();

// Count agents
$verifyAgentsStmt = $conn->prepare("
    SELECT COUNT(*) as correct_agents
    FROM agents a
    LEFT JOIN stores s ON s.owner_id = a.id
    WHERE s.id IS NULL AND a.role = 'agent' AND a.is_store_owner = 0
");
$verifyAgentsStmt->execute();
$verifyAgentsResult = $verifyAgentsStmt->get_result();
$verifyAgentsData = $verifyAgentsResult->fetch_assoc();
$verifyAgentsStmt->close();

echo "Store owners: {$verifyStoreOwnersData['correct_store_owners']} / {$verifyStoreOwnersData['total_store_owners']}\n";
echo "Agents: {$verifyAgentsData['correct_agents']}\n";

// Show any issues
$issuesStmt = $conn->prepare("
    SELECT 
        a.id, 
        a.username, 
        a.role, 
        a.is_store_owner, 
        a.store_id,
        s.id as should_be_store_owner
    FROM agents a
    LEFT JOIN stores s ON s.owner_id = a.id
    WHERE (s.id IS NOT NULL AND (a.role != 'store_owner' OR a.is_store_owner != 1 OR a.store_id != s.id))
       OR (s.id IS NULL AND (a.role = 'store_owner' OR a.is_store_owner = 1))
    LIMIT 10
");
$issuesStmt->execute();
$issuesResult = $issuesStmt->get_result();

if ($issuesResult->num_rows > 0) {
    echo "\n⚠️  Found issues (showing first 10):\n";
    while ($row = $issuesResult->fetch_assoc()) {
        echo "  - {$row['username']} (ID: {$row['id']}): role='{$row['role']}', is_store_owner={$row['is_store_owner']}, store_id={$row['store_id']}\n";
    }
} else {
    echo "\n✓ All data is correct!\n";
}
$issuesStmt->close();

echo "\nConversion complete!\n";
?>