<?php
// diagnose_triggers.php
require_once '../../config.php'; // Ajusta la ruta si es necesario

echo "<h2>Diagnóstico de TRIGGERS en la Base de Datos</h2>";

try {
    // 1. Mostrar todos los triggers de la BD
    $sql = "SHOW TRIGGERS";
    $res = $conn->query($sql);

    echo "<h3>📌 Triggers en la BD actual:</h3>";
    if ($res && $res->num_rows > 0) {
        echo "<table border='1' cellpadding='5' cellspacing='0'>";
        echo "<tr><th>Trigger</th><th>Tabla</th><th>Evento</th><th>Momento</th><th>Definición</th></tr>";
        while ($row = $res->fetch_assoc()) {
            echo "<tr>";
            echo "<td>" . htmlspecialchars($row['Trigger']) . "</td>";
            echo "<td>" . htmlspecialchars($row['Table']) . "</td>";
            echo "<td>" . htmlspecialchars($row['Event']) . "</td>";
            echo "<td>" . htmlspecialchars($row['Timing']) . "</td>";
            echo "<td><pre>" . htmlspecialchars($row['Statement']) . "</pre></td>";
            echo "</tr>";
        }
        echo "</table>";
    } else {
        echo "<p>⚠️ No se encontraron triggers en esta base de datos.</p>";
    }

    // 2. Mostrar definición completa desde INFORMATION_SCHEMA
    $sql2 = "
        SELECT 
            TRIGGER_NAME,
            EVENT_MANIPULATION AS accion,
            EVENT_OBJECT_TABLE AS tabla,
            ACTION_TIMING AS momento,
            ACTION_STATEMENT AS logica
        FROM INFORMATION_SCHEMA.TRIGGERS
        WHERE EVENT_OBJECT_TABLE IN ('invoices','current_tickets')
        ORDER BY EVENT_OBJECT_TABLE, ACTION_TIMING, accion
    ";
    $res2 = $conn->query($sql2);

    echo "<h3>📌 Triggers en tablas críticas (invoices / current_tickets):</h3>";
    if ($res2 && $res2->num_rows > 0) {
        echo "<table border='1' cellpadding='5' cellspacing='0'>";
        echo "<tr><th>Trigger</th><th>Tabla</th><th>Acción</th><th>Momento</th><th>Lógica</th></tr>";
        while ($row = $res2->fetch_assoc()) {
            echo "<tr>";
            echo "<td>" . htmlspecialchars($row['TRIGGER_NAME']) . "</td>";
            echo "<td>" . htmlspecialchars($row['tabla']) . "</td>";
            echo "<td>" . htmlspecialchars($row['accion']) . "</td>";
            echo "<td>" . htmlspecialchars($row['momento']) . "</td>";
            echo "<td><pre>" . htmlspecialchars($row['logica']) . "</pre></td>";
            echo "</tr>";
        }
        echo "</table>";
    } else {
        echo "<p>⚠️ No hay triggers definidos en invoices o current_tickets.</p>";
    }

} catch (Exception $e) {
    echo "<p style='color:red;'>❌ Error ejecutando diagnóstico: " . $e->getMessage() . "</p>";
}
