<?php
/**
 * Analyze Agents Table Structure
 * Check current structure and generate conversion query for store_owner role
 */

require_once 'config.php';

echo "=== Agents Table Structure Analysis ===\n\n";

// Step 1: Get table structure
echo "Step 1: Current Table Structure\n";
echo str_repeat("-", 60) . "\n";

$columnsStmt = $conn->prepare("SHOW COLUMNS FROM agents");
$columnsStmt->execute();
$columnsResult = $columnsStmt->get_result();

$columns = [];
$roleColumn = null;
$isStoreOwnerColumn = null;

echo "Columns:\n";
while ($row = $columnsResult->fetch_assoc()) {
    $columns[] = $row;
    echo sprintf("  %-25s %-30s %-10s %-10s %-15s\n", 
        $row['Field'], 
        $row['Type'], 
        $row['Null'], 
        $row['Key'] ?: 'None',
        $row['Default'] ?? 'NULL'
    );
    
    if ($row['Field'] === 'role') {
        $roleColumn = $row;
    }
    if ($row['Field'] === 'is_store_owner') {
        $isStoreOwnerColumn = $row;
    }
}
$columnsStmt->close();

echo "\n";

// Step 2: Analyze role column
echo "Step 2: Role Column Analysis\n";
echo str_repeat("-", 60) . "\n";

if ($roleColumn) {
    echo "Field: " . $roleColumn['Field'] . "\n";
    echo "Type: " . $roleColumn['Type'] . "\n";
    echo "Null: " . $roleColumn['Null'] . "\n";
    echo "Default: " . ($roleColumn['Default'] ?? 'NULL') . "\n";
    
    // Check if it's ENUM
    if (stripos($roleColumn['Type'], 'enum') !== false) {
        echo "\n⚠️  Role is ENUM type\n";
        echo "Current ENUM values: " . $roleColumn['Type'] . "\n";
        
        // Extract enum values
        preg_match("/enum\((.+)\)/i", $roleColumn['Type'], $matches);
        if (isset($matches[1])) {
            $enumValues = $matches[1];
            echo "Parsed values: $enumValues\n";
            
            // Check if 'store_owner' is included
            if (stripos($roleColumn['Type'], 'store_owner') === false) {
                echo "❌ 'store_owner' is NOT in ENUM - needs to be added\n";
                echo "\nConversion needed:\n";
                echo "ALTER TABLE agents MODIFY COLUMN role ENUM($enumValues, 'store_owner');\n";
            } else {
                echo "✓ 'store_owner' is already in ENUM\n";
            }
        }
    } else {
        echo "\n✓ Role is VARCHAR/TEXT - supports any value including 'store_owner'\n";
    }
} else {
    echo "❌ ERROR: 'role' column not found!\n";
}

echo "\n";

// Step 3: Analyze is_store_owner column
echo "Step 3: is_store_owner Column Analysis\n";
echo str_repeat("-", 60) . "\n";

if ($isStoreOwnerColumn) {
    echo "Field: " . $isStoreOwnerColumn['Field'] . "\n";
    echo "Type: " . $isStoreOwnerColumn['Type'] . "\n";
    echo "Null: " . $isStoreOwnerColumn['Null'] . "\n";
    echo "Default: " . ($isStoreOwnerColumn['Default'] ?? 'NULL') . "\n";
    
    // Check if it's TINYINT(1) or similar
    if (stripos($isStoreOwnerColumn['Type'], 'tinyint') !== false || 
        stripos($isStoreOwnerColumn['Type'], 'int') !== false) {
        echo "✓ Type is appropriate for boolean flag\n";
    } else {
        echo "⚠️  Type might need adjustment\n";
    }
} else {
    echo "❌ ERROR: 'is_store_owner' column not found!\n";
    echo "\nWould need to add:\n";
    echo "ALTER TABLE agents ADD COLUMN is_store_owner TINYINT(1) DEFAULT 0 AFTER role;\n";
}

echo "\n";

// Step 4: Check current data
echo "Step 4: Current Data Analysis\n";
echo str_repeat("-", 60) . "\n";

// Check role distribution
$roleStatsStmt = $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
");
$roleStatsStmt->execute();
$roleStatsResult = $roleStatsStmt->get_result();

echo "Current role distribution:\n";
while ($row = $roleStatsResult->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";
}
$roleStatsStmt->close();

// Check store owners
$storeOwnersStmt = $conn->prepare("
    SELECT 
        COUNT(DISTINCT s.owner_id) as total_store_owners,
        COUNT(DISTINCT CASE WHEN a.role = 'store_owner' THEN a.id END) as with_store_owner_role,
        COUNT(DISTINCT CASE WHEN a.is_store_owner = 1 THEN a.id END) as with_store_owner_flag
    FROM stores s
    LEFT JOIN agents a ON s.owner_id = a.id
");
$storeOwnersStmt->execute();
$storeOwnersResult = $storeOwnersStmt->get_result();
$storeOwnersData = $storeOwnersResult->fetch_assoc();
$storeOwnersStmt->close();

echo "\nStore owners:\n";
echo "  - Total store owners (from stores table): {$storeOwnersData['total_store_owners']}\n";
echo "  - With role='store_owner': {$storeOwnersData['with_store_owner_role']}\n";
echo "  - With is_store_owner=1: {$storeOwnersData['with_store_owner_flag']}\n";

echo "\n";

// Step 5: Generate conversion queries
echo "Step 5: Conversion Queries\n";
echo str_repeat("-", 60) . "\n";

$conversionQueries = [];

// Check if role needs to be updated
if ($roleColumn && stripos($roleColumn['Type'], 'enum') !== false) {
    preg_match("/enum\((.+)\)/i", $roleColumn['Type'], $matches);
    if (isset($matches[1]) && stripos($roleColumn['Type'], 'store_owner') === false) {
        $enumValues = $matches[1];
        $conversionQueries[] = "ALTER TABLE agents MODIFY COLUMN role ENUM($enumValues, 'store_owner') DEFAULT 'agent';";
        echo "Query 1: Add 'store_owner' to ENUM\n";
        echo $conversionQueries[count($conversionQueries) - 1] . "\n\n";
    }
}

// Check if is_store_owner column exists and is correct type
if (!$isStoreOwnerColumn) {
    $conversionQueries[] = "ALTER TABLE agents ADD COLUMN is_store_owner TINYINT(1) NOT NULL DEFAULT 0 AFTER role;";
    echo "Query 2: Add is_store_owner column\n";
    echo $conversionQueries[count($conversionQueries) - 1] . "\n\n";
} elseif (stripos($isStoreOwnerColumn['Type'], 'tinyint') === false && stripos($isStoreOwnerColumn['Type'], 'int') === false) {
    $conversionQueries[] = "ALTER TABLE agents MODIFY COLUMN is_store_owner TINYINT(1) NOT NULL DEFAULT 0;";
    echo "Query 3: Fix is_store_owner column type\n";
    echo $conversionQueries[count($conversionQueries) - 1] . "\n\n";
}

// Update all store owners to have correct role
$conversionQueries[] = "
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;
";
echo "Query 4: Update all store owners\n";
echo $conversionQueries[count($conversionQueries) - 1] . "\n\n";

// Ensure all non-store-owners have correct role
$conversionQueries[] = "
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);
";
echo "Query 5: Fix agents who shouldn't be store owners\n";
echo $conversionQueries[count($conversionQueries) - 1] . "\n\n";

// Step 6: Generate complete conversion script
echo "Step 6: Complete Conversion Script\n";
echo str_repeat("-", 60) . "\n";

$completeScript = "<?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 {
";

foreach ($conversionQueries as $index => $query) {
    $queryNum = $index + 1;
    $completeScript .= "
    // Query $queryNum
    echo \"Executing query $queryNum...\\n\";
    if (mysqli_query(\$conn, \"" . addslashes(trim($query)) . "\")) {
        echo \"✓ Query $queryNum successful\\n\";
    } else {
        throw new Exception(\"Query $queryNum failed: \" . mysqli_error(\$conn));
    }
";
}

$completeScript .= "
    // 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\";
\$verifyStmt = \$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 THEN a.id END) as correct_store_owners,
        COUNT(DISTINCT CASE WHEN a.role = 'agent' AND a.is_store_owner = 0 THEN a.id END) as correct_agents
    FROM stores s
    LEFT JOIN agents a ON s.owner_id = a.id
    CROSS JOIN agents a2
\");
\$verifyStmt->execute();
\$verifyResult = \$verifyStmt->get_result();
\$verifyData = \$verifyResult->fetch_assoc();
\$verifyStmt->close();

echo \"Store owners: {\$verifyData['correct_store_owners']} / {\$verifyData['total_store_owners']}\\n\";
echo \"Agents: {\$verifyData['correct_agents']}\\n\";
echo \"\\nConversion complete!\\n\";
?>";

// Save the conversion script
file_put_contents('convert_agents_table.php', $completeScript);
echo "✓ Complete conversion script saved to: convert_agents_table.php\n";

echo "\n=== Summary ===\n";
echo "Total conversion queries: " . count($conversionQueries) . "\n";
echo "Run 'php convert_agents_table.php' to execute the conversion\n";
?>

