<?php
/**
 * ============================================================================
 * Migration Script: Auto-Update Role Permissions System
 * Run this once to install the auto-update system
 * Access: /admin/migrate-permissions.php (Super Admin only)
 * ============================================================================
 */

define('AIMS_ACCESS', true);
require_once __DIR__ . '/../config/config.php';

// Super strict access control
if (!isLoggedIn() || getUserType() !== 'admin') {
    die('Access Denied');
}

// Check if user is super admin (level 10)
$database = new Database();
$db = $database->getConnection();

$sql = "SELECT ar.role_level 
        FROM users u 
        JOIN admin_roles ar ON u.admin_role_id = ar.id 
        WHERE u.id = ?";
$stmt = $db->prepare($sql);
$stmt->execute([getUserId()]);
$roleLevel = $stmt->fetch(PDO::FETCH_ASSOC)['role_level'] ?? 0;

if ($roleLevel < 10) {
    die('Super Admin access required');
}

$migrationLog = [];
$errors = [];

// Function to log migration steps
function logMigration($message, $type = 'info') {
    global $migrationLog;
    $migrationLog[] = [
        'time' => date('H:i:s'),
        'type' => $type,
        'message' => $message
    ];
}

// Function to execute SQL with error handling
function executeSql($db, $sql, $description) {
    global $errors;
    try {
        $db->exec($sql);
        logMigration("✓ {$description}", 'success');
        return true;
    } catch (PDOException $e) {
        $error = "{$description}: {$e->getMessage()}";
        $errors[] = $error;
        logMigration("✗ {$error}", 'error');
        return false;
    }
}

// Start migration if form submitted
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['confirm_migration'])) {
    
    logMigration('=== Starting Auto-Update Role Permissions Migration ===', 'info');
    
    // Step 1: Drop existing triggers
    logMigration('Step 1: Cleaning up old triggers...', 'info');
    executeSql($db, "DROP TRIGGER IF EXISTS `sync_super_admin_permissions_on_new_permission`", "Drop old trigger 1");
    executeSql($db, "DROP TRIGGER IF EXISTS `sync_admin_permissions_on_new_permission`", "Drop old trigger 2");
    executeSql($db, "DROP TRIGGER IF EXISTS `assign_default_permissions_on_new_role`", "Drop old trigger 3");
    executeSql($db, "DROP TRIGGER IF EXISTS `log_permission_changes`", "Drop old trigger 4");
    
    // Step 2: Create triggers
    logMigration('Step 2: Creating new triggers...', 'info');
    
    $trigger1 = "
    CREATE TRIGGER `sync_super_admin_permissions_on_new_permission`
    AFTER INSERT ON `admin_permissions`
    FOR EACH ROW
    BEGIN
        DECLARE super_admin_role_id INT;
        SELECT id INTO super_admin_role_id FROM admin_roles WHERE role_level >= 10 AND is_active = 1 LIMIT 1;
        IF super_admin_role_id IS NOT NULL THEN
            INSERT INTO role_permissions (role_id, permission_id, granted_at, granted_by)
            VALUES (super_admin_role_id, NEW.id, NOW(), @current_admin_id)
            ON DUPLICATE KEY UPDATE granted_at = NOW();
        END IF;
    END";
    executeSql($db, $trigger1, "Create auto-grant Super Admin trigger");
    
    $trigger2 = "
    CREATE TRIGGER `sync_admin_permissions_on_new_permission`
    AFTER INSERT ON `admin_permissions`
    FOR EACH ROW
    BEGIN
        DECLARE admin_role_id INT;
        SELECT id INTO admin_role_id FROM admin_roles WHERE role_level = 8 AND is_active = 1 LIMIT 1;
        IF admin_role_id IS NOT NULL AND NEW.permission_key NOT IN ('full_access', 'manage_roles', 'delete_users', 'manage_server') THEN
            INSERT INTO role_permissions (role_id, permission_id, granted_at, granted_by)
            VALUES (admin_role_id, NEW.id, NOW(), @current_admin_id)
            ON DUPLICATE KEY UPDATE granted_at = NOW();
        END IF;
    END";
    executeSql($db, $trigger2, "Create auto-grant Administrator trigger");
    
    $trigger3 = "
    CREATE TRIGGER `assign_default_permissions_on_new_role`
    AFTER INSERT ON `admin_roles`
    FOR EACH ROW
    BEGIN
        INSERT INTO role_permissions (role_id, permission_id, granted_at, granted_by)
        SELECT NEW.id, id, NOW(), @current_admin_id
        FROM admin_permissions
        WHERE permission_key = 'view_dashboard'
        ON DUPLICATE KEY UPDATE granted_at = NOW();
        
        IF NEW.role_level >= 10 THEN
            INSERT INTO role_permissions (role_id, permission_id, granted_at, granted_by)
            SELECT NEW.id, id, NOW(), @current_admin_id
            FROM admin_permissions
            ON DUPLICATE KEY UPDATE granted_at = NOW();
        END IF;
    END";
    executeSql($db, $trigger3, "Create new role defaults trigger");
    
    $trigger4 = "
    CREATE TRIGGER `log_permission_changes`
    AFTER INSERT ON `role_permissions`
    FOR EACH ROW
    BEGIN
        DECLARE role_name_val VARCHAR(50);
        DECLARE perm_name_val VARCHAR(100);
        SELECT role_name INTO role_name_val FROM admin_roles WHERE id = NEW.role_id;
        SELECT permission_name INTO perm_name_val FROM admin_permissions WHERE id = NEW.permission_id;
        INSERT INTO admin_activity_log (admin_id, action_type, action_description, affected_table, affected_record_id)
        VALUES (COALESCE(NEW.granted_by, @current_admin_id, 1), 'grant_permission',
                CONCAT('Granted \"', perm_name_val, '\" to \"', role_name_val, '\"'), 'role_permissions', NEW.id);
    END";
    executeSql($db, $trigger4, "Create permission logging trigger");
    
    // Step 3: Create stored procedures
    logMigration('Step 3: Creating stored procedures...', 'info');
    
    executeSql($db, "DROP PROCEDURE IF EXISTS `sync_role_permissions`", "Drop old sync_role procedure");
    $proc1 = "
    CREATE PROCEDURE `sync_role_permissions`(IN role_id_param INT)
    BEGIN
        DECLARE role_level_val INT;
        SELECT role_level INTO role_level_val FROM admin_roles WHERE id = role_id_param;
        IF role_level_val >= 10 THEN
            INSERT INTO role_permissions (role_id, permission_id, granted_at, granted_by)
            SELECT role_id_param, id, NOW(), @current_admin_id FROM admin_permissions
            ON DUPLICATE KEY UPDATE granted_at = NOW();
        ELSEIF role_level_val >= 8 THEN
            INSERT INTO role_permissions (role_id, permission_id, granted_at, granted_by)
            SELECT role_id_param, id, NOW(), @current_admin_id FROM admin_permissions
            WHERE permission_key NOT IN ('full_access', 'manage_roles', 'delete_users')
            ON DUPLICATE KEY UPDATE granted_at = NOW();
        END IF;
    END";
    executeSql($db, $proc1, "Create sync_role_permissions procedure");
    
    executeSql($db, "DROP PROCEDURE IF EXISTS `sync_all_role_permissions`", "Drop old sync_all procedure");
    $proc2 = "
    CREATE PROCEDURE `sync_all_role_permissions`()
    BEGIN
        DECLARE done INT DEFAULT FALSE;
        DECLARE current_role_id INT;
        DECLARE current_role_level INT;
        DECLARE role_cursor CURSOR FOR SELECT id, role_level FROM admin_roles WHERE is_active = 1;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        OPEN role_cursor;
        read_loop: LOOP
            FETCH role_cursor INTO current_role_id, current_role_level;
            IF done THEN LEAVE read_loop; END IF;
            CALL sync_role_permissions(current_role_id);
        END LOOP;
        CLOSE role_cursor;
    END";
    executeSql($db, $proc2, "Create sync_all_role_permissions procedure");
    
    // Step 4: Create views
    logMigration('Step 4: Creating views...', 'info');
    
    $view1 = "
    CREATE OR REPLACE VIEW v_user_permissions AS
    SELECT u.id AS user_id, u.full_name, u.user_type, ar.role_name, ar.role_level,
           ap.permission_key, ap.permission_name, ap.permission_category, rp.granted_at
    FROM users u
    LEFT JOIN admin_roles ar ON u.admin_role_id = ar.id
    LEFT JOIN role_permissions rp ON ar.id = rp.role_id
    LEFT JOIN admin_permissions ap ON rp.permission_id = ap.id
    WHERE u.user_type = 'admin' AND u.is_active = 1";
    executeSql($db, $view1, "Create v_user_permissions view");
    
    $view2 = "
    CREATE OR REPLACE VIEW v_role_permission_summary AS
    SELECT ar.id AS role_id, ar.role_name, ar.role_level, ar.is_active,
           COUNT(rp.id) AS permissions_count,
           GROUP_CONCAT(DISTINCT ap.permission_category) AS categories,
           MAX(rp.granted_at) AS last_permission_granted
    FROM admin_roles ar
    LEFT JOIN role_permissions rp ON ar.id = rp.role_id
    LEFT JOIN admin_permissions ap ON rp.permission_id = ap.id
    GROUP BY ar.id, ar.role_name, ar.role_level, ar.is_active";
    executeSql($db, $view2, "Create v_role_permission_summary view");
    
    // Step 5: Sync existing roles
    logMigration('Step 5: Synchronizing existing roles...', 'info');
    
    try {
        $db->exec("SET @current_admin_id = " . getUserId());
        $stmt = $db->query("CALL sync_all_role_permissions()");
        logMigration("✓ All roles synchronized", 'success');
    } catch (PDOException $e) {
        logMigration("✗ Sync failed: " . $e->getMessage(), 'error');
    }
    
    // Step 6: Grant new permissions to Super Admin
    logMigration('Step 6: Granting new permissions to Super Admin...', 'info');
    
    try {
        $sql = "INSERT INTO role_permissions (role_id, permission_id, granted_at, granted_by)
                SELECT ar.id, ap.id, NOW(), ?
                FROM admin_roles ar
                CROSS JOIN admin_permissions ap
                WHERE ar.role_level >= 10 AND ar.is_active = 1
                AND NOT EXISTS (SELECT 1 FROM role_permissions rp WHERE rp.role_id = ar.id AND rp.permission_id = ap.id)
                ON DUPLICATE KEY UPDATE granted_at = NOW()";
        $stmt = $db->prepare($sql);
        $stmt->execute([getUserId()]);
        $count = $stmt->rowCount();
        logMigration("✓ Granted {$count} new permissions to Super Admin", 'success');
    } catch (PDOException $e) {
        logMigration("✗ Grant failed: " . $e->getMessage(), 'error');
    }
    
    logMigration('=== Migration Completed ===', 'success');
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Role Permissions Migration - AIMS Admin</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.11.1/font/bootstrap-icons.css" rel="stylesheet">
    <style>
        .log-entry { padding: 8px; border-left: 4px solid #6b7280; margin-bottom: 4px; }
        .log-entry.success { border-left-color: #22c55e; background: #f0fdf4; }
        .log-entry.error { border-left-color: #ef4444; background: #fef2f2; }
        .log-entry.info { border-left-color: #3b82f6; background: #eff6ff; }
    </style>
</head>
<body class="bg-light">
    <div class="container py-5">
        <div class="row justify-content-center">
            <div class="col-lg-8">
                <div class="card border-0 shadow-sm">
                    <div class="card-header bg-primary text-white py-3">
                        <h4 class="mb-0">
                            <i class="bi bi-gear-fill me-2"></i>
                            Auto-Update Role Permissions Migration
                        </h4>
                    </div>
                    <div class="card-body">
                        <?php if (empty($migrationLog)): ?>
                        
                        <div class="alert alert-info">
                            <i class="bi bi-info-circle me-2"></i>
                            <strong>About This Migration</strong><br>
                            This will install the auto-update role permissions system which:
                            <ul class="mb-0 mt-2">
                                <li>Automatically grants new permissions to Super Admin role</li>
                                <li>Automatically grants appropriate permissions to Administrator role</li>
                                <li>Assigns default permissions to new roles</li>
                                <li>Logs all permission changes</li>
                                <li>Provides stored procedures for bulk operations</li>
                            </ul>
                        </div>
                        
                        <div class="alert alert-warning">
                            <i class="bi bi-exclamation-triangle me-2"></i>
                            <strong>Important:</strong> This migration will create database triggers and stored procedures.
                            Make sure you have a database backup before proceeding.
                        </div>
                        
                        <form method="POST">
                            <div class="form-check mb-3">
                                <input class="form-check-input" type="checkbox" id="backup_confirm" required>
                                <label class="form-check-label" for="backup_confirm">
                                    I confirm that I have a database backup
                                </label>
                            </div>
                            
                            <div class="form-check mb-4">
                                <input class="form-check-input" type="checkbox" id="understand_confirm" required>
                                <label class="form-check-label" for="understand_confirm">
                                    I understand this will modify the database structure
                                </label>
                            </div>
                            
                            <button type="submit" name="confirm_migration" class="btn btn-primary btn-lg w-100">
                                <i class="bi bi-play-circle me-2"></i>
                                Start Migration
                            </button>
                        </form>
                        
                        <?php else: ?>
                        
                        <div class="mb-4">
                            <h5>Migration Log</h5>
                            <div class="border rounded p-3" style="max-height: 500px; overflow-y: auto; background: #f8f9fa;">
                                <?php foreach ($migrationLog as $log): ?>
                                <div class="log-entry <?php echo $log['type']; ?>">
                                    <small class="text-muted me-2">[<?php echo $log['time']; ?>]</small>
                                    <?php echo htmlspecialchars($log['message']); ?>
                                </div>
                                <?php endforeach; ?>
                            </div>
                        </div>
                        
                        <?php if (empty($errors)): ?>
                        <div class="alert alert-success">
                            <i class="bi bi-check-circle-fill me-2"></i>
                            <strong>Migration Completed Successfully!</strong><br>
                            The auto-update role permissions system is now active.
                        </div>
                        <?php else: ?>
                        <div class="alert alert-danger">
                            <i class="bi bi-x-circle-fill me-2"></i>
                            <strong>Migration Completed with Errors</strong><br>
                            Please review the log above and contact support if needed.
                        </div>
                        <?php endif; ?>
                        
                        <div class="d-grid gap-2">
                            <a href="<?php echo APP_URL; ?>/admin/role-permissions.php" class="btn btn-primary">
                                <i class="bi bi-diagram-3 me-2"></i>
                                Go to Role Permissions Management
                            </a>
                            <a href="<?php echo APP_URL; ?>/admin/index.php" class="btn btn-outline-secondary">
                                <i class="bi bi-house me-2"></i>
                                Return to Dashboard
                            </a>
                        </div>
                        
                        <?php endif; ?>
                    </div>
                </div>
                
                <div class="text-center mt-3">
                    <small class="text-muted">
                        Migration Script v1.0 | Super Admin Access Only
                    </small>
                </div>
            </div>
        </div>
    </div>
</body>
</html>