<?php
/**
 * dump_vibeeo.php
 * Full schema dump (TXT) for MySQL database `digupdog_vibeeo` without FILE privilege.
 * Outputs a single text file with: table summary, columns, indexes, FKs, and SHOW CREATE TABLE.
 */

ini_set('display_errors', 1);
error_reporting(E_ALL);
set_time_limit(0);

// ==== 1) EDIT THESE ====
$DB_HOST = 'localhost';
$DB_USER = 'digupdog_vibeeoadmin';
$DB_PASS = 'Raimundinho1';
$SCHEMA  = 'digupdog_vibeeo';   // alvo

// TXT output file (saved alongside this script)
$OUTPUT_FILE = __DIR__ . DIRECTORY_SEPARATOR . 'schema_dump_vibeeo.txt';

// ==== 2) Connect (to information_schema to query metadata) ====
$mysqli = @new mysqli($DB_HOST, $DB_USER, $DB_PASS, 'information_schema');
if ($mysqli->connect_errno) {
    http_response_code(500);
    die("MySQL connect error ({$mysqli->connect_errno}): {$mysqli->connect_error}");
}
$mysqli->set_charset('utf8mb4');

// helper: safe value for SQL
function esc($s) {
    return str_replace("`", "``", $s);
}

// open file for writing
$f = @fopen($OUTPUT_FILE, 'wb');
if (!$f) {
    http_response_code(500);
    die("Could not open output file for writing: {$OUTPUT_FILE}");
}

// header
$ts = date('Y-m-d H:i:s');
fwrite($f, "========== SCHEMA DUMP ==========\n");
fwrite($f, "Schema: {$SCHEMA}\n");
fwrite($f, "Generated at: {$ts}\n");
fwrite($f, "Host: {$DB_HOST}\n");
fwrite($f, "=================================\n\n");

// increase group concat length for index columns
$mysqli->query("SET SESSION group_concat_max_len = 1048576");

// 3) List all base tables
$sqlTables = "
    SELECT t.TABLE_NAME,
           t.ENGINE,
           t.TABLE_ROWS,
           ROUND(t.DATA_LENGTH/1024/1024, 2)  AS DATA_MB,
           ROUND(t.INDEX_LENGTH/1024/1024, 2) AS INDEX_MB,
           ROUND((t.DATA_LENGTH + t.INDEX_LENGTH)/1024/1024, 2) AS TOTAL_MB,
           t.AUTO_INCREMENT,
           t.TABLE_COLLATION,
           t.CREATE_TIME,
           t.UPDATE_TIME
    FROM information_schema.TABLES t
    WHERE t.TABLE_SCHEMA = ?
      AND t.TABLE_TYPE   = 'BASE TABLE'
    ORDER BY t.TABLE_NAME
";
$stmtTables = $mysqli->prepare($sqlTables);
$stmtTables->bind_param('s', $SCHEMA);
$stmtTables->execute();
$tablesRes = $stmtTables->get_result();

$tables = [];
while ($row = $tablesRes->fetch_assoc()) {
    $tables[] = $row;
}
$stmtTables->close();

fwrite($f, "TABLE COUNT: " . count($tables) . "\n\n");

// Pre-prepare statements for columns, indexes, FKs
$sqlCols = "
SELECT
  c.ORDINAL_POSITION AS pos,
  c.COLUMN_NAME,
  c.COLUMN_TYPE,
  c.IS_NULLABLE,
  c.COLUMN_DEFAULT,
  c.EXTRA,
  c.COLUMN_KEY,
  c.COLLATION_NAME,
  c.COLUMN_COMMENT
FROM information_schema.COLUMNS c
WHERE c.TABLE_SCHEMA = ?
  AND c.TABLE_NAME   = ?
ORDER BY c.ORDINAL_POSITION
";
$stmtCols = $mysqli->prepare($sqlCols);

$sqlIdx = "
SELECT
  s.INDEX_NAME,
  CASE WHEN s.NON_UNIQUE = 0 THEN 'UNIQUE' ELSE 'NON_UNIQUE' END AS uniqueness,
  s.INDEX_TYPE,
  GROUP_CONCAT(
    CONCAT(s.COLUMN_NAME, IF(s.SUB_PART IS NOT NULL, CONCAT('(', s.SUB_PART, ')'), ''))
    ORDER BY s.SEQ_IN_INDEX SEPARATOR ', '
  ) AS index_columns,
  MAX(s.CARDINALITY) AS cardinality_est
FROM information_schema.STATISTICS s
WHERE s.TABLE_SCHEMA = ?
  AND s.TABLE_NAME   = ?
GROUP BY s.INDEX_NAME, s.NON_UNIQUE, s.INDEX_TYPE
ORDER BY (s.INDEX_NAME = 'PRIMARY') DESC, s.INDEX_NAME
";
$stmtIdx = $mysqli->prepare($sqlIdx);

$sqlFK = "
SELECT
  rc.CONSTRAINT_NAME,
  GROUP_CONCAT(kcu.COLUMN_NAME ORDER BY kcu.ORDINAL_POSITION SEPARATOR ', ')            AS fk_columns,
  rc.REFERENCED_TABLE_NAME                                                              AS ref_table,
  GROUP_CONCAT(kcu.REFERENCED_COLUMN_NAME ORDER BY kcu.ORDINAL_POSITION SEPARATOR ', ') AS ref_columns,
  rc.UPDATE_RULE,
  rc.DELETE_RULE
FROM information_schema.REFERENTIAL_CONSTRAINTS rc
JOIN information_schema.KEY_COLUMN_USAGE kcu
  ON rc.CONSTRAINT_SCHEMA = kcu.TABLE_SCHEMA
 AND rc.CONSTRAINT_NAME   = kcu.CONSTRAINT_NAME
 AND rc.TABLE_NAME        = kcu.TABLE_NAME
WHERE rc.CONSTRAINT_SCHEMA = ?
  AND rc.TABLE_NAME        = ?
GROUP BY rc.CONSTRAINT_NAME, rc.REFERENCED_TABLE_NAME, rc.UPDATE_RULE, rc.DELETE_RULE
";
$stmtFK = $mysqli->prepare($sqlFK);

// loop each table
foreach ($tables as $t) {
    $tbl = $t['TABLE_NAME'];

    fwrite($f, "============================================================\n");
    fwrite($f, "TABLE: {$tbl}\n");
    fwrite($f, "============================================================\n");

    // (1) Summary
    fwrite($f, "--- SUMMARY ---\n");
    fwrite($f, sprintf(
        "ENGINE: %s | ROWS(est): %s | DATA_MB: %s | INDEX_MB: %s | TOTAL_MB: %s | AUTO_INCREMENT: %s | COLLATION: %s | CREATE: %s | UPDATE: %s\n\n",
        $t['ENGINE'] ?? 'NULL',
        $t['TABLE_ROWS'] ?? 'NULL',
        $t['DATA_MB'] ?? 'NULL',
        $t['INDEX_MB'] ?? 'NULL',
        $t['TOTAL_MB'] ?? 'NULL',
        $t['AUTO_INCREMENT'] ?? 'NULL',
        $t['TABLE_COLLATION'] ?? 'NULL',
        $t['CREATE_TIME'] ?? 'NULL',
        $t['UPDATE_TIME'] ?? 'NULL'
    ));

    // (2) Columns
    fwrite($f, "--- COLUMNS ---\n");
    $stmtCols->bind_param('ss', $SCHEMA, $tbl);
    $stmtCols->execute();
    $resCols = $stmtCols->get_result();
    if ($resCols->num_rows === 0) {
        fwrite($f, "(no columns?)\n");
    } else {
        fwrite($f, "pos\tname\ttype\tis_nullable\tdefault\textra\tkey\tcollation\tcomment\n");
        while ($c = $resCols->fetch_assoc()) {
            fwrite($f, implode("\t", [
                $c['pos'],
                $c['COLUMN_NAME'],
                $c['COLUMN_TYPE'],
                $c['IS_NULLABLE'],
                is_null($c['COLUMN_DEFAULT']) ? 'NULL' : (string)$c['COLUMN_DEFAULT'],
                $c['EXTRA'],
                $c['COLUMN_KEY'],
                $c['COLLATION_NAME'],
                str_replace(["\r","\n","\t"], [' ',' ',' '], (string)$c['COLUMN_COMMENT']),
            ]) . "\n");
        }
    }
    fwrite($f, "\n");

    // (3) Indexes
    fwrite($f, "--- INDEXES ---\n");
    $stmtIdx->bind_param('ss', $SCHEMA, $tbl);
    $stmtIdx->execute();
    $resIdx = $stmtIdx->get_result();
    if ($resIdx->num_rows === 0) {
        fwrite($f, "(no indexes)\n");
    } else {
        fwrite($f, "index_name\tuniqueness\tindex_type\tcolumns\tcardinality_est\n");
        while ($ix = $resIdx->fetch_assoc()) {
            fwrite($f, implode("\t", [
                $ix['INDEX_NAME'],
                $ix['uniqueness'],
                $ix['INDEX_TYPE'],
                $ix['index_columns'],
                $ix['cardinality_est'],
            ]) . "\n");
        }
    }
    fwrite($f, "\n");

    // (4) Foreign Keys
    fwrite($f, "--- FOREIGN KEYS ---\n");
    $stmtFK->bind_param('ss', $SCHEMA, $tbl);
    $stmtFK->execute();
    $resFK = $stmtFK->get_result();
    if ($resFK->num_rows === 0) {
        fwrite($f, "(no foreign keys)\n");
    } else {
        fwrite($f, "constraint\tfk_columns\tref_table\tref_columns\tupdate_rule\tdelete_rule\n");
        while ($fk = $resFK->fetch_assoc()) {
            fwrite($f, implode("\t", [
                $fk['CONSTRAINT_NAME'],
                $fk['fk_columns'],
                $fk['ref_table'],
                $fk['ref_columns'],
                $fk['UPDATE_RULE'],
                $fk['DELETE_RULE'],
            ]) . "\n");
        }
    }
    fwrite($f, "\n");

    // (5) SHOW CREATE TABLE
    fwrite($f, "--- SHOW CREATE TABLE ---\n");
    // switch database temporarily to run SHOW CREATE
    if (!$mysqli->select_db($SCHEMA)) {
        fwrite($f, "(error selecting db: {$SCHEMA})\n\n");
        // switch back to information_schema in case of error
        $mysqli->select_db('information_schema');
        continue;
    }
    $tblEsc = '`' . esc($tbl) . '`';
    $resCreate = $mysqli->query("SHOW CREATE TABLE {$tblEsc}");
    if ($resCreate && $rowCreate = $resCreate->fetch_array(MYSQLI_NUM)) {
        // usually [0]=Table, [1]=Create Table
        fwrite($f, $rowCreate[1] . ";\n\n");
        $resCreate->free();
    } else {
        fwrite($f, "(SHOW CREATE TABLE failed)\n\n");
    }
    // go back to information_schema for next loops
    $mysqli->select_db('information_schema');

    fwrite($f, "\n"); // space between tables
}

// close statements and file
$stmtCols->close();
$stmtIdx->close();
$stmtFK->close();
fclose($f);

echo "Done. File generated: " . basename($OUTPUT_FILE) . "\n";
echo "Path: " . $OUTPUT_FILE . "\n";
