<?php
/**
 * Script to Clean Up Duplicate Roles
 * This script removes duplicate roles (same name within the same tenant)
 * Access this file in your browser: http://localhost/TellerPro/run_clean_duplicate_roles.php
 */

require_once __DIR__ . '/config/config.php';
require_once __DIR__ . '/config/database.php';

echo '<!DOCTYPE html>
<html>
<head>
    <title>Cleaning Duplicate Roles</title>
    <style>
        body { font-family: Arial, sans-serif; padding: 20px; max-width: 800px; margin: 0 auto; }
        .success { color: green; }
        .error { color: red; }
        .info { color: blue; }
    </style>
</head>
<body>';

echo '<h1>Cleaning Duplicate Roles</h1>';

try {
    db()->beginTransaction();

    echo '<p class="info">Finding duplicate roles...</p>';

    // Find duplicate roles (same name within the same tenant)
    $checkSql = "SELECT tenant_id, name, COUNT(*) as count, GROUP_CONCAT(id ORDER BY id) as ids
                 FROM roles
                 GROUP BY tenant_id, name
                 HAVING COUNT(*) > 1";
    $checkStmt = db()->query($checkSql);
    $duplicates = $checkStmt->fetchAll();

    if (empty($duplicates)) {
        echo '<p class="success">No duplicate roles found.</p>';
    } else {
        echo '<p class="info">Found ' . count($duplicates) . ' sets of duplicate roles:</p>';
        echo '<ul>';
        foreach ($duplicates as $dup) {
            echo "<li>Tenant ID: {$dup['tenant_id']}, Role: {$dup['name']}, Count: {$dup['count']}</li>";
        }
        echo '</ul>';

        $totalDeleted = 0;

        foreach ($duplicates as $dup) {
            $ids = explode(',', $dup['ids']);
            // Keep the first ID, delete the rest
            $keepId = array_shift($ids);

            // Delete role_permissions for duplicate roles first
            if (!empty($ids)) {
                $placeholders = implode(',', array_fill(0, count($ids), '?'));
                $deletePermsSql = "DELETE FROM role_permissions WHERE role_id IN ($placeholders)";
                $deletePermsStmt = db()->prepare($deletePermsSql);
                $deletePermsStmt->execute($ids);

                // Update users with duplicate role_id to point to the kept role
                $updateUsersSql = "UPDATE users SET role_id = ? WHERE role_id IN ($placeholders)";
                $updateUsersStmt = db()->prepare($updateUsersSql);
                $params = array_merge([$keepId], $ids);
                $updateUsersStmt->execute($params);

                // Delete the duplicate roles
                $deleteRolesSql = "DELETE FROM roles WHERE id IN ($placeholders)";
                $deleteRolesStmt = db()->prepare($deleteRolesSql);
                $deleteRolesStmt->execute($ids);

                $totalDeleted += count($ids);
                echo "<p class='success'>Deleted " . count($ids) . " duplicate(s) of role '{$dup['name']}' (kept ID: {$keepId})</p>";
            }
        }

        echo "<p class='success'>Total duplicates deleted: {$totalDeleted}</p>";
    }

    // Add unique constraint to prevent future duplicates
    echo '<p class="info">Adding unique constraint to prevent future duplicates...</p>';

    // Check if constraint already exists
    $checkConstraint = db()->query("SELECT COUNT(*) FROM information_schema.table_constraints
                                    WHERE table_schema = DATABASE()
                                    AND table_name = 'roles'
                                    AND constraint_name = 'unique_tenant_role'")->fetchColumn();

    if ($checkConstraint == 0) {
        $alterSql = "ALTER TABLE roles ADD CONSTRAINT unique_tenant_role UNIQUE (tenant_id, name)";
        db()->query($alterSql);
        echo '<p class="success">Unique constraint added successfully.</p>';
    } else {
        echo '<p class="info">Unique constraint already exists.</p>';
    }

    db()->commit();

    echo '<h2 class="success">Cleanup completed successfully!</h2>';
    echo '<p><a href="' . APP_URL . '/roles">Go to Roles Page</a></p>';

} catch (Exception $e) {
    db()->rollBack();
    echo '<h2 class="error">Cleanup failed</h2>';
    echo '<pre class="error">' . htmlspecialchars($e->getMessage()) . '</pre>';
    echo '<p>Please check your database configuration and try again.</p>';
}

echo '</body>
</html>';
