<?php
session_start();
// ======================================================================
// CONFIGURACIÓN
// ======================================================================
$host = "localhost";
$user = "root";
$pass = "33comRxXMysql";
$conn = new mysqli($host, $user, $pass);
$conn->set_charset("utf8mb4");

// Inicializar historial de consultas en sesión
if (!isset($_SESSION['query_history'])) $_SESSION['query_history'] = [];

// ======================================================================
// AJAX: Obtener tablas
// ======================================================================
if (isset($_GET['get_tables'])) {
    $db = $conn->real_escape_string($_GET['get_tables']);
    $res = $conn->query("SHOW TABLES FROM `$db`");
    $out = [];
    if ($res) {
        while ($r = $res->fetch_array()) $out[] = $r[0];
    }
    echo json_encode($out); 
    exit;
}

// ======================================================================
// AJAX: Obtener filas (orden DESC por PK si existe)
// ======================================================================
if (isset($_GET['get_rows'])) {
    $db = $conn->real_escape_string($_GET['db']);
    $table = $conn->real_escape_string($_GET['table']);

    $conn->select_db($db);
    $pk = null;
    $pkres = $conn->query("SHOW KEYS FROM `$table` WHERE Key_name='PRIMARY'");
    if ($pkres && $pkres->num_rows) {
        $rowPk = $pkres->fetch_assoc();
        $pk = $rowPk['Column_name'];
    }

    $order = $pk ? "ORDER BY `$pk` DESC" : "";
    $limit = "LIMIT 200"; // Límite de seguridad
    $res = $conn->query("SELECT * FROM `$table` $order $limit");

    $rows = [];
    if ($res) while ($r = $res->fetch_assoc()) $rows[] = $r;

    echo json_encode(["rows" => $rows, "pk" => $pk]);
    exit;
}

// ======================================================================
// AJAX: Estructura de tabla
// ======================================================================
if (isset($_GET['get_structure'])) {
    $db = $conn->real_escape_string($_GET['db']);
    $table = $conn->real_escape_string($_GET['table']);

    $conn->select_db($db);
    $res = $conn->query("SHOW COLUMNS FROM `$table`");

    $cols = [];
    if ($res) while ($r = $res->fetch_assoc()) $cols[] = $r;

    echo json_encode($cols);
    exit;
}

// ======================================================================
// AJAX: Ejecutar consulta libre
// ======================================================================
if (isset($_POST['run_query'])) {
    $db = $conn->real_escape_string($_POST['db']);
    $query = $_POST['query'];

    if($db) $conn->select_db($db);
    
    try {
        $ok = $conn->query($query);
        $error = $conn->error;
        $affected = $conn->affected_rows;
    } catch (Exception $e) {
        $ok = false;
        $error = $e->getMessage();
        $affected = 0;
    }

    // Guardar en historial
    $_SESSION['query_history'][] = ['query'=>$query, 'ok'=>$ok, 'error'=>$error, 'affected'=>$affected];
    if (count($_SESSION['query_history'])>10) array_shift($_SESSION['query_history']);

    echo json_encode(["ok" => (bool)$ok, "error" => $error, "affected" => $affected, "history"=>$_SESSION['query_history']]);
    exit;
}
?>
<!DOCTYPE html>
<html lang="es">
<head>
    <meta charset="utf-8">
    <title>Editor Dinámico de Tablas</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <style>
        body { background: #f7f8fa; }
        #tableContainer { max-height: 55vh; overflow: auto; }
        #dataTable tbody tr:hover { background: #e9f5ff; cursor: pointer; }
        /* Estilos para los offcanvas */
        .offcanvas-end { width: 40% !important; max-width: 600px; }
        @media (max-width: 900px) { .offcanvas-end { width: 100% !important; } }
        
        pre.sql-box { white-space: pre-wrap; word-break: break-word; background: #2d2d2d; color: #76ff03; padding: 12px; border-radius: 6px; font-family: monospace; }
        .query-history { max-height: 200px; overflow-y: auto; font-size: 0.9em; background:#fff; padding:6px; border:1px solid #ddd; border-radius:4px;}
        
        /* Diferenciar visualmente Agregar vs Editar */
        #addOffcanvas .offcanvas-header { background-color: #d1e7dd; color: #0f5132; }
        #editOffcanvas .offcanvas-header { background-color: #cfe2ff; color: #084298; }
    </style>
</head>
<body class="p-4">

<div class="container-fluid">

    <h3 class="mb-3">Editor Dinámico de Tablas</h3>

    <!-- SELECTORES Y CONTROLES -->
    <div class="row g-2 align-items-center mb-3">
        <div class="col-auto">
            <select id="dbSelect" class="form-select">
                <option value="">Selecciona base de datos</option>
                <?php
                $res = $conn->query("SHOW DATABASES");
                while ($d = $res->fetch_array()) {
                    if ($d[0] == 'information_schema' || $d[0] == 'performance_schema' || $d[0] == 'mysql') continue; 
                    echo "<option value=\"" . htmlspecialchars($d[0]) . "\">" . htmlspecialchars($d[0]) . "</option>";
                }
                ?>
            </select>
        </div>
        <div class="col-auto">
            <select id="tableSelect" class="form-select" style="min-width: 200px;">
                <option value="">Selecciona tabla</option>
            </select>
        </div>
        <div class="col-auto flex-grow-1">
            <input id="filterInput" class="form-control" placeholder="Buscar en la tabla...">
        </div>
        <div class="col-auto">
            <a id="btnNewPage" class="btn btn-warning" href="EditorDB.php">DB</a>
        </div>
        <div class="col-auto">
            <!-- Botón para abrir el Modal de AGREGAR -->
            <button id="btnOpenAdd" class="btn btn-success">
                + Nuevo Registro
            </button>
        </div>
    </div>

    <!-- AREA: tabla -->
    <div id="tableContainer" class="border bg-white rounded p-2 shadow-sm">
        <table id="dataTable" class="table table-sm table-bordered table-hover mb-0">
            <thead class="table-light sticky-top"></thead>
            <tbody></tbody>
        </table>
        <div id="loadingMsg" class="text-center p-3 text-muted" style="display:none;">Cargando datos...</div>
    </div>

    <!-- AREA: consulta manual y resultado -->
    <div class="row mt-4">
        <div class="col-md-8">
            <div class="card">
                <div class="card-header bg-light"><strong>Consola SQL</strong></div>
                <div class="card-body">
                    <textarea id="manualQuery" class="form-control font-monospace" rows="3" placeholder="SELECT * FROM..."></textarea>
                    <div class="d-flex gap-2 mt-2">
                        <button id="runQueryBtn" class="btn btn-primary btn-sm">Ejecutar SQL</button>
                        <button id="clearQueryBtn" class="btn btn-outline-secondary btn-sm">Limpiar</button>
                        <div id="queryResult" class="ms-2 align-self-center"></div>
                    </div>
                </div>
            </div>
        </div>
        <div class="col-md-4">
            <div class="card h-100">
                <div class="card-header bg-light"><strong>Historial</strong></div>
                <div class="card-body p-0">
                    <div id="queryHistory" class="query-history h-100 border-0"></div>
                </div>
            </div>
        </div>
    </div>

</div>

<!-- ========================================== -->
<!-- OFFCANVAS 1: AGREGAR REGISTRO -->
<!-- ========================================== -->
<div class="offcanvas offcanvas-end" tabindex="-1" id="addOffcanvas">
    <div class="offcanvas-header">
        <h5 class="offcanvas-title">✨ Agregar Nuevo Registro</h5>
        <button type="button" class="btn-close" data-bs-dismiss="offcanvas"></button>
    </div>
    <div class="offcanvas-body">
        <form id="addForm" class="row g-3"></form>
        <hr>
        <div class="alert alert-secondary mt-3">
            <small>Previsualización SQL:</small>
            <pre id="addSqlPreview" class="sql-box mt-1 mb-0"></pre>
        </div>
        <div id="addResultMsg"></div>
    </div>
    <div class="offcanvas-footer p-3 bg-light border-top">
        <button id="btnAddSave" class="btn btn-success w-100">Guardar Nuevo Registro</button>
    </div>
</div>

<!-- ========================================== -->
<!-- OFFCANVAS 2: EDITAR REGISTRO -->
<!-- ========================================== -->
<div class="offcanvas offcanvas-end" tabindex="-1" id="editOffcanvas">
    <div class="offcanvas-header">
        <h5 class="offcanvas-title">✏️ Editar Registro</h5>
        <button type="button" class="btn-close" data-bs-dismiss="offcanvas"></button>
    </div>
    <div class="offcanvas-body">
        <form id="editForm" class="row g-3"></form>
        <hr>
        <div class="alert alert-secondary mt-3">
            <small>Previsualización SQL:</small>
            <pre id="editSqlPreview" class="sql-box mt-1 mb-0"></pre>
        </div>
        <div id="editResultMsg"></div>
    </div>
    <div class="offcanvas-footer p-3 bg-light border-top d-flex gap-2">
        <button id="btnEditSave" class="btn btn-primary flex-grow-1">Guardar Cambios</button>
        <button id="btnEditDelete" class="btn btn-danger">Eliminar</button>
    </div>
</div>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>

<script>
let currentDB="", currentTable="", tableStructure=[], pkName=null, editingRowData=null;

// Instancias de Bootstrap Offcanvas
const addOffcanvas = new bootstrap.Offcanvas(document.getElementById('addOffcanvas'));
const editOffcanvas = new bootstrap.Offcanvas(document.getElementById('editOffcanvas'));

// ---------------------------
// 1. Cargar Tablas
// ---------------------------
$("#dbSelect").on("change", function(){
    currentDB = $(this).val();
    $("#tableSelect").html("<option value=''>Cargando...</option>");
    if(!currentDB) return;

    $.get("?get_tables="+encodeURIComponent(currentDB), function(resp){
        let tabs = JSON.parse(resp);
        let opts = "<option value=''>Seleccione tabla</option>";
        tabs.forEach(t => opts += `<option value="${t}">${t}</option>`);
        $("#tableSelect").html(opts);
    });
});

// ---------------------------
// 2. Cargar Datos
// ---------------------------
$("#tableSelect").on("change", function(){
    currentTable = $(this).val();
    loadTable();
});

function loadTable(){
    if(!currentDB || !currentTable) return;
    $("#loadingMsg").show();
    $("#dataTable tbody").empty();

    // Obtener estructura primero
    $.get("?get_structure&db="+encodeURIComponent(currentDB)+"&table="+encodeURIComponent(currentTable), function(structResp){
        tableStructure = JSON.parse(structResp);
        
        // Renderizar cabecera
        let header="<tr>";
        tableStructure.forEach(c => header += `<th>${c.Field} <small class='text-muted' style='font-size:0.7em'>${c.Key=='PRI'?'🔑':''}</small></th>`);
        header+="</tr>";
        $("#dataTable thead").html(header);

        // Obtener datos
        $.get("?get_rows&db="+encodeURIComponent(currentDB)+"&table="+encodeURIComponent(currentTable), function(resp){
            let data = JSON.parse(resp);
            pkName = data.pk;
            
            let html="";
            data.rows.forEach(r => {
                let encoded = encodeURIComponent(JSON.stringify(r));
                html += `<tr data-row='${encoded}'>`;
                tableStructure.forEach(c => {
                    let val = r[c.Field];
                    if(val === null) val = "<i>NULL</i>";
                    else if(val.length > 50) val = val.substring(0,50)+"...";
                    html += `<td>${escapeHtml(String(val))}</td>`;
                });
                html += "</tr>";
            });
            $("#dataTable tbody").html(html);
            $("#loadingMsg").hide();
        });
    });
}

// ---------------------------
// 3. Abrir MODAL AGREGAR
// ---------------------------
$("#btnOpenAdd").click(function(){
    if(!currentTable) return alert("Selecciona una tabla primero");
    
    buildForm("#addForm", {}); // Formulario vacío
    updateAddPreview(); // Generar SQL inicial
    addOffcanvas.show();
});

// Detectar cambios en formulario agregar para actualizar preview
$("#addForm").on("input change", "input, textarea, select", function(){
    updateAddPreview();
});

function updateAddPreview(){
    $("#addSqlPreview").text(getInsertSQL());
}

// ---------------------------
// 4. Abrir MODAL EDITAR (Clic en fila)
// ---------------------------
$(document).on("click", "#dataTable tbody tr", function(){
    let rowData = JSON.parse(decodeURIComponent($(this).attr("data-row")));
    editingRowData = rowData;
    
    buildForm("#editForm", rowData);
    updateEditPreview();
    editOffcanvas.show();
});

// Detectar cambios en formulario editar
$("#editForm").on("input change", "input, textarea, select", function(){
    updateEditPreview();
});

function updateEditPreview(){
    $("#editSqlPreview").text(getUpdateSQL());
}

// ---------------------------
// FUNCIONES DE UTILIDAD
// ---------------------------

// Construir inputs dinámicamente
function buildForm(targetSelector, values){
    const $form = $(targetSelector);
    $form.empty();

    tableStructure.forEach(col => {
        let val = values[col.Field] ?? "";
        let isPK = (col.Key === "PRI");
        let label = `<label class="form-label fw-bold" style="font-size:0.85rem">${col.Field} <span class="text-muted fw-normal">(${col.Type})</span></label>`;
        let inputHtml = "";

        // Si es Agregar y es AutoIncrement (usualmente PK int), sugerimos dejar vacío
        let placeholder = "";
        if(targetSelector === "#addForm" && isPK) placeholder = "placeholder='(Auto)'";

        if ((col.Type||"").includes("text") || (col.Type||"").includes("json") || (col.Type||"").includes("blob")) {
            inputHtml = `<textarea name="${col.Field}" class="form-control" rows="2" ${placeholder}>${escapeHtml(String(val))}</textarea>`;
        } else {
            // Si es editar y es PK, hacerlo readonly
            let readonly = (targetSelector === "#editForm" && isPK) ? "readonly style='background-color:#e9ecef'" : "";
            inputHtml = `<input type="text" name="${col.Field}" class="form-control" value="${escapeHtml(String(val))}" ${readonly} ${placeholder}>`;
        }

        $form.append(`<div class="col-12 mb-2">${label}${inputHtml}</div>`);
    });
}

// Generar SQL INSERT (CORREGIDO PARA EL BUG)
function getInsertSQL(){
    let fields = [];
    let values = [];
    
    $("#addForm").find("input, textarea, select").each(function(){
        let name = $(this).attr("name");
        let val = $(this).val();
        
        // BUG FIX: Si el campo es PK y está vacío, NO lo incluimos en el INSERT
        // para que MySQL use el AUTO_INCREMENT.
        if(name === pkName && val === "") return; // Skip
        
        fields.push("`"+name+"`");
        // Manejar NULL si se escribe 'NULL' explícitamente o dejarlo como string vacío
        values.push("'" + escapeSql(val) + "'"); 
    });
    
    if(fields.length === 0) return "-- No hay campos para insertar";
    
    return `INSERT INTO \`${currentTable}\` (${fields.join(", ")}) VALUES (${values.join(", ")})`;
}

// Generar SQL UPDATE
function getUpdateSQL(){
    if(!editingRowData || !pkName) return "-- Error: No hay clave primaria detectada";
    
    let sets = [];
    $("#editForm").find("input, textarea, select").each(function(){
        let name = $(this).attr("name");
        let val = $(this).val();
        
        // No actualizamos la PK en el SET
        if(name !== pkName) {
            sets.push("`" + name + "` = '" + escapeSql(val) + "'");
        }
    });
    
    // Condición WHERE basada en el valor ORIGINAL de la PK (por si acaso cambió, aunque está readonly)
    let pkVal = editingRowData[pkName]; 
    return `UPDATE \`${currentTable}\` SET ${sets.join(", ")} WHERE \`${pkName}\` = '${escapeSql(pkVal)}'`;
}

// Escapar caracteres para JS/HTML
function escapeHtml(text){ 
    if(text===null || text===undefined) return "";
    return String(text).replace(/&/g,"&amp;").replace(/</g,"&lt;").replace(/>/g,"&gt;").replace(/"/g,"&quot;"); 
}
// Escapar comillas simples para SQL visual (básico)
function escapeSql(s){ return String(s).replace(/'/g,"\\'"); }


// ---------------------------
// EJECUCIÓN (AJAX)
// ---------------------------

// Botón GUARDAR en modal AGREGAR
$("#btnAddSave").click(function(){
    let sql = getInsertSQL();
    executeQuery(sql, function(resp){
        if(resp.ok){
            addOffcanvas.hide();
            loadTable();
            alert("✅ Registro agregado correctamente");
        } else {
            alert("❌ Error: " + resp.error);
        }
    });
});

// Botón GUARDAR en modal EDITAR
$("#btnEditSave").click(function(){
    let sql = getUpdateSQL();
    executeQuery(sql, function(resp){
        if(resp.ok){
            editOffcanvas.hide();
            loadTable();
        } else {
            alert("❌ Error: " + resp.error);
        }
    });
});

// Botón ELIMINAR en modal EDITAR
$("#btnEditDelete").click(function(){
    if(!confirm("¿Estás seguro de ELIMINAR este registro permanentemente?")) return;
    
    let pkVal = editingRowData[pkName];
    let sql = `DELETE FROM \`${currentTable}\` WHERE \`${pkName}\` = '${escapeSql(pkVal)}'`;
    
    executeQuery(sql, function(resp){
        if(resp.ok){
            editOffcanvas.hide();
            loadTable();
        } else {
            alert("❌ Error al eliminar: " + resp.error);
        }
    });
});

// Ejecución Genérica
function executeQuery(sql, callback){
    $.post("", {run_query:1, db:currentDB, query:sql}, function(resp){
        let r; 
        try { r=JSON.parse(resp); } catch(e){ r={ok:false, error:"Error parsing JSON"}; }
        
        updateHistory(r.history);
        
        if(typeof callback === "function") callback(r);
        
        // Actualizar resultado visual en consola
        let msg = r.ok 
            ? `<span class="text-success fw-bold">✔ Éxito (${r.affected} filas)</span>` 
            : `<span class="text-danger fw-bold">✖ Error: ${r.error}</span>`;
        $("#queryResult").html(msg);
    });
}

// Consola Manual
$("#runQueryBtn").click(function(){
    executeQuery($("#manualQuery").val(), function(r){
        if(r.ok) loadTable(); // Si fue exitoso, recargar tabla por si acaso afectó a la actual
    });
});
$("#clearQueryBtn").click(function(){ $("#manualQuery").val(""); $("#queryResult").empty(); });

// Historial
function updateHistory(h){
    if(!h) return;
    let html="";
    h.slice().reverse().forEach(q=>{
        let icon = q.ok ? "🟢" : "🔴";
        html += `<div class="border-bottom p-1 mb-1" style="font-size:0.85em; cursor:pointer;" onclick="$('#manualQuery').val(this.innerText.substring(2))">
            ${icon} <span class="text-break">${q.query}</span>
        </div>`;
    });
    $("#queryHistory").html(html);
}

// Filtrar en tabla
$("#filterInput").on("keyup", function() {
    var value = $(this).val().toLowerCase();
    $("#dataTable tbody tr").filter(function() {
      $(this).toggle($(this).text().toLowerCase().indexOf(value) > -1)
    });
});

<?php
if(isset($_SESSION['query_history']))
    echo "updateHistory(".json_encode($_SESSION['query_history']).");";
?>
</script>
</body>
</html>