<?php
/**
 * Script para reparar/verificar la tabla password_reset_tokens
 * Ejecutar una vez si hay problemas con la creación de la tabla
 */

session_start();

// Verificar si el usuario está autenticado (opcional, puede comentarse para ejecución directa)
// if (!isset($_SESSION['user'])) {
//     die("Acceso denegado");
// }

require_once 'config/email-config.php';

try {
    $pdo = getEmailDatabaseConnection();
    
    echo "<h2>Reparación de Tabla password_reset_tokens</h2>";
    echo "<pre>";
    
    // 1. Verificar si la tabla existe
    echo "1. Verificando si la tabla existe...\n";
    $stmt = $pdo->query("SHOW TABLES LIKE 'password_reset_tokens'");
    $tableExists = $stmt->rowCount() > 0;
    
    if ($tableExists) {
        echo "   ✓ La tabla existe\n";
    } else {
        echo "   ✗ La tabla NO existe, creándola...\n";
        $createTable = "
            CREATE TABLE password_reset_tokens (
                id INT AUTO_INCREMENT PRIMARY KEY,
                codempleado INT NOT NULL,
                token VARCHAR(64) NOT NULL,
                expires_at DATETIME NOT NULL,
                used TINYINT(1) DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_codempleado (codempleado),
                INDEX idx_token (token),
                INDEX idx_expires_at (expires_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ";
        $pdo->exec($createTable);
        echo "   ✓ Tabla creada exitosamente\n";
    }
    
    // 2. Verificar foreign keys existentes
    echo "\n2. Verificando foreign keys...\n";
    $stmt = $pdo->query("
        SELECT CONSTRAINT_NAME 
        FROM information_schema.TABLE_CONSTRAINTS 
        WHERE CONSTRAINT_SCHEMA = DATABASE()
        AND TABLE_NAME = 'password_reset_tokens'
        AND CONSTRAINT_TYPE = 'FOREIGN KEY'
    ");
    $existingFKs = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    if (in_array('fk_password_reset_empleado', $existingFKs)) {
        echo "   ✓ Foreign key 'fk_password_reset_empleado' ya existe\n";
    } else {
        echo "   ✗ Foreign key no existe, intentando crearla...\n";
        try {
            $pdo->exec("ALTER TABLE password_reset_tokens 
                       ADD CONSTRAINT fk_password_reset_empleado 
                       FOREIGN KEY (codempleado) REFERENCES templeado(codempleado) ON DELETE CASCADE");
            echo "   ✓ Foreign key creada exitosamente\n";
        } catch (PDOException $e) {
            $errorMsg = $e->getMessage();
            if (stripos($errorMsg, 'errno: 121') !== false || 
                stripos($errorMsg, 'Duplicate key') !== false) {
                echo "   ⚠ Advertencia: " . $errorMsg . "\n";
                echo "   → Intentando eliminar foreign keys duplicadas...\n";
                
                // Intentar eliminar todas las foreign keys y recrear
                try {
                    // Obtener todas las foreign keys
                    $stmt = $pdo->query("
                        SELECT CONSTRAINT_NAME 
                        FROM information_schema.TABLE_CONSTRAINTS 
                        WHERE CONSTRAINT_SCHEMA = DATABASE()
                        AND TABLE_NAME = 'password_reset_tokens'
                        AND CONSTRAINT_TYPE = 'FOREIGN KEY'
                    ");
                    $fks = $stmt->fetchAll(PDO::FETCH_COLUMN);
                    
                    foreach ($fks as $fk) {
                        try {
                            $pdo->exec("ALTER TABLE password_reset_tokens DROP FOREIGN KEY " . $fk);
                            echo "   → Eliminada foreign key: $fk\n";
                        } catch (PDOException $dropError) {
                            echo "   → No se pudo eliminar $fk: " . $dropError->getMessage() . "\n";
                        }
                    }
                    
                    // Recrear la foreign key
                    $pdo->exec("ALTER TABLE password_reset_tokens 
                               ADD CONSTRAINT fk_password_reset_empleado 
                               FOREIGN KEY (codempleado) REFERENCES templeado(codempleado) ON DELETE CASCADE");
                    echo "   ✓ Foreign key recreada exitosamente\n";
                } catch (PDOException $recreateError) {
                    echo "   ⚠ No se pudo recrear la foreign key: " . $recreateError->getMessage() . "\n";
                    echo "   → La tabla funcionará sin foreign key (no crítico)\n";
                }
            } else {
                echo "   ✗ Error: " . $errorMsg . "\n";
            }
        }
    }
    
    // 3. Verificar estructura de la tabla
    echo "\n3. Verificando estructura de la tabla...\n";
    $stmt = $pdo->query("DESCRIBE password_reset_tokens");
    $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    $requiredColumns = ['id', 'codempleado', 'token', 'expires_at', 'used', 'created_at'];
    $existingColumns = array_column($columns, 'Field');
    
    foreach ($requiredColumns as $col) {
        if (in_array($col, $existingColumns)) {
            echo "   ✓ Columna '$col' existe\n";
        } else {
            echo "   ✗ Columna '$col' NO existe\n";
        }
    }
    
    // 4. Verificar índices
    echo "\n4. Verificando índices...\n";
    $stmt = $pdo->query("SHOW INDEX FROM password_reset_tokens");
    $indexes = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $indexNames = array_unique(array_column($indexes, 'Key_name'));
    
    $requiredIndexes = ['PRIMARY', 'idx_codempleado', 'idx_token', 'idx_expires_at'];
    foreach ($requiredIndexes as $idx) {
        if (in_array($idx, $indexNames)) {
            echo "   ✓ Índice '$idx' existe\n";
        } else {
            echo "   ✗ Índice '$idx' NO existe\n";
        }
    }
    
    // 5. Estadísticas
    echo "\n5. Estadísticas de la tabla...\n";
    $stmt = $pdo->query("SELECT COUNT(*) FROM password_reset_tokens");
    $total = $stmt->fetchColumn();
    echo "   Total de tokens: $total\n";
    
    $stmt = $pdo->query("SELECT COUNT(*) FROM password_reset_tokens WHERE used = 0 AND expires_at > NOW()");
    $activos = $stmt->fetchColumn();
    echo "   Tokens activos: $activos\n";
    
    $stmt = $pdo->query("SELECT COUNT(*) FROM password_reset_tokens WHERE used = 1 OR expires_at < NOW()");
    $expirados = $stmt->fetchColumn();
    echo "   Tokens expirados/usados: $expirados\n";
    
    echo "\n✓ Verificación completada\n";
    echo "</pre>";
    echo "<p><a href='dashboard.php'>Volver al Dashboard</a></p>";
    
} catch (Exception $e) {
    echo "<pre>";
    echo "✗ Error: " . $e->getMessage() . "\n";
    echo "</pre>";
}
?>

