<?php
/**
 * Script para comparar dos archivos SQL y generar los comandos de actualización (MIGRACIÓN)
 */

$vitamedFile = "c:/Users/Pc/Desktop/AAA REPARACIONES/BASE DE DATOS/histkjpq_vitamed.sql";
$medicoFile = "c:/Users/Pc/Desktop/AAA REPARACIONES/BASE DE DATOS/histkjpq_medico (2).sql";

function getTables($content) {
    $tables = [];
    // Buscar CREATE TABLE `name` ( ... );
    preg_match_all('/CREATE TABLE `([^`]+)` \((.*?)\) ENGINE=/s', $content, $matches);
    for ($i = 0; $i < count($matches[1]); $i++) {
        $tableName = $matches[1][$i];
        $tableContent = $matches[2][$i];
        
        $columns = [];
        // Buscar columnas: `colName` type ...
        preg_match_all('/^\s*`([^`]+)`\s+(.*?)(?:,|$)/m', $tableContent, $colMatches);
        for ($j = 0; $j < count($colMatches[1]); $j++) {
            $columns[$colMatches[1][$j]] = trim($colMatches[2][$j]);
        }
        $tables[$tableName] = $columns;
    }
    return $tables;
}

echo "Analizando archivos...\n";
$vitamedTables = getTables(file_get_contents($vitamedFile));
$medicoTables = getTables(file_get_contents($medicoFile));

echo "--- TABLAS FALTANTES EN VITAMED ---\n";
foreach ($medicoTables as $tableName => $cols) {
    if (!isset($vitamedTables[$tableName])) {
        echo "CREATE TABLE `$tableName` (...\n";
    }
}

echo "\n--- COLUMNAS FALTANTES O DIFERENTES EN VITAMED ---\n";
foreach ($medicoTables as $tableName => $medicoCols) {
    if (isset($vitamedTables[$tableName])) {
        $vitamedCols = $vitamedTables[$tableName];
        foreach ($medicoCols as $colName => $def) {
            if (!isset($vitamedCols[$colName])) {
                echo "ALTER TABLE `$tableName` ADD COLUMN `$colName` $def;\n";
            }
        }
    }
}
?>
