<?php
// Configuración de servidores
$srvr_1 = "192.185.41.206";
$data_1 = "vare";

$srvr_2 = "50.6.168.20";
// $data_2 = "vare";
$data_2 = $data_1;

$rootuser = 'root';
$rootpass = 'VicoreGator76*';
//           ;E0e(Eh%5aG-EW)h

// Usuarios de base de datos
$sourceHost = $srvr_1;
$sourceDb = "vcrxol_" . $data_1;
$sourceUser = "vcrxol_" . $data_1 . "full";
$sourcePass = "vicore1976";

$destHost = $srvr_2;
$destDb = "vcrxol_" . $data_2;
$destUser = "vcrxol_" . $data_2 . "full";
// $destPass = "testing1976";
$destPass = "vicore1976";

// Tablas excluidas
// $excluir = ['rcdd0', 'vndd0', 'empl0', 'ibod0'];
$solotabla = ['mdcn0'];


// 1. Crear la base de datos destino si no existe
// $rootConn = new mysqli($destHost, $rootuser, $rootpass);
// if ($rootConn->connect_error) {
//     die("❌ Error conectando como root: " . $rootConn->connect_error);
// }
// $rootConn->query("CREATE DATABASE IF NOT EXISTS `$destDb`");
// $rootConn->close();

// Conexión origen
$sourceConn = new mysqli($sourceHost, $sourceUser, $sourcePass, $sourceDb);
if ($sourceConn->connect_error) {
    die("❌ Error al conectar con la base de datos origen: " . $sourceConn->connect_error);
}
$sourceConn->set_charset("utf8");

// Conexión destino
$destConn = new mysqli($destHost, $destUser, $destPass, $destDb);
if ($destConn->connect_error) {
    die("❌ Error al conectar con la base de datos destino: " . $destConn->connect_error);
}
$destConn->query("SET sql_mode = ''");

// Obtener todas las tablas
$sql = "SELECT TABLE_NAME FROM information_schema.tables ";
$sql.= "WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = '$sourceDb'";
// $sql.= "and TABLE_NAME LIKE 'enti%' ";
$sql.= "order by substring(TABLE_NAME,5,1), TABLE_NAME ";
$result = $sourceConn->query($sql);

$tables = [];
while ($row = $result->fetch_assoc()) {
    $tables[] = $row['TABLE_NAME'];
}

// Mostrar tablas encontradas
echo "<br>Tablas encontradas:<br>";
// echo "<br>";
foreach ($tables as $table) {
     echo "<br>";
     echo " - ".$table;
}
echo "<br>";

// Copiar cada tabla
foreach ($tables as $table) {
    if (!in_array($table, $excluir)) {
        echo "Copiando tabla '$table'...<br>";

        // Obtener CREATE TABLE
        $resCreate = $sourceConn->query("SHOW CREATE TABLE `$table`");
        $createRow = $resCreate->fetch_assoc();
        $createSQL = $createRow['Create Table'];

        // Eliminar si existe y crear tabla
        $destConn->query("DROP TABLE IF EXISTS `$table`");
        $destConn->query($createSQL);

        // Exportar datos
        $resData = $sourceConn->query("SELECT * FROM `$table`");
        if ($resData && $resData->num_rows > 0) {
            while ($row = $resData->fetch_assoc()) {
                $columns = array_keys($row);
                $values = array_map(function($value) use ($destConn) {
                    return "'" . $destConn->real_escape_string($value) . "'";
                }, array_values($row));

                $colList = "`" . implode("`, `", $columns) . "`";
                $valList = implode(", ", $values);

                $destConn->query("INSERT INTO `$table` ($colList) VALUES ($valList)");
            }
        }
         
//         ALTER TABLE nombre_tabla AUTO_INCREMENT = nuevo_valor; 

        echo "✔ Tabla '$table' copiada con éxito.<br><br>";
    }
}

echo "<strong>✅ Copia de la base de datos completada.</strong>";

// Cerrar conexiones
$sourceConn->close();
$destConn->close();
?>
