<?php
/**
 * Generate an idempotent, data-free schema file for EduTrack.
 *
 *   php database/generate_schema_sql.php  [> database/schema.sql]
 *
 * Output can be run repeatedly against any EduTrack database:
 *   - missing tables are created
 *   - missing columns are added to tables that already exist
 *   - missing indexes are added
 * Nothing is ever dropped, and no existing column is ever modified.
 *
 * WordPress `oric_*` tables that share the database are deliberately skipped.
 */
declare(strict_types=1);

require_once __DIR__ . '/../config.php';

$conn = getDBConnection();
if (!$conn instanceof mysqli) {
    fwrite(STDERR, "Database connection failed.\n");
    exit(1);
}

$db = $conn->query('SELECT DATABASE() d')->fetch_assoc()['d'];

/** Tables to include: EduTrack base tables only. */
$tables = [];
$res = $conn->query(
    "SELECT TABLE_NAME
     FROM information_schema.TABLES
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_TYPE = 'BASE TABLE'
       AND TABLE_NAME NOT LIKE 'oric\\_%'
     ORDER BY TABLE_NAME"
);
while ($row = $res->fetch_assoc()) {
    $tables[] = $row['TABLE_NAME'];
}

/**
 * Split a SHOW CREATE TABLE body into column definitions and key definitions.
 *
 * Parsing the DDL is more reliable than reassembling definitions from
 * information_schema: it preserves generated columns, expression defaults,
 * per-column charsets and MariaDB CHECK constraints verbatim.
 */
function splitDefinition(string $ddl): array
{
    $open  = strpos($ddl, "(\n");
    $close = strrpos($ddl, "\n)");
    if ($open === false || $close === false) {
        return [[], [], ''];
    }

    $body    = substr($ddl, $open + 2, $close - $open - 2);
    $options = trim(substr($ddl, $close + 2));

    // Strip AUTO_INCREMENT=n so re-running never rewinds a live counter.
    $options = (string) preg_replace('/\s*AUTO_INCREMENT=\d+/', '', $options);

    $columns = [];
    $keys    = [];
    $depth   = 0;
    $buf     = '';
    $inStr   = false;
    $len     = strlen($body);

    for ($i = 0; $i < $len; $i++) {
        $ch = $body[$i];

        if ($inStr) {
            $buf .= $ch;
            if ($ch === '\\' && $i + 1 < $len) { $buf .= $body[++$i]; continue; }
            if ($ch === "'") $inStr = false;
            continue;
        }
        if ($ch === "'")  { $inStr = true; $buf .= $ch; continue; }
        if ($ch === '(')  { $depth++; }
        if ($ch === ')')  { $depth--; }

        // Top-level comma ends one definition.
        if ($ch === ',' && $depth === 0) {
            $line = trim($buf);
            if ($line !== '') { $line[0] === '`' ? $columns[] = $line : $keys[] = $line; }
            $buf = '';
            continue;
        }
        $buf .= $ch;
    }
    $line = trim($buf);
    if ($line !== '') { $line[0] === '`' ? $columns[] = $line : $keys[] = $line; }

    return [$columns, $keys, $options];
}

/** Column name from a definition line: `foo` int(11) NOT NULL -> foo */
function columnName(string $def): ?string
{
    return preg_match('/^`([^`]+)`/', $def, $m) ? $m[1] : null;
}

/** Convert a KEY clause into the ALTER TABLE ... ADD form, or null to skip. */
function keyToAdd(string $key): ?string
{
    // PRIMARY KEY can only be added to a table that has none; if the table
    // exists it already has one. Foreign keys are skipped on purpose — adding
    // one to a live table fails if any existing row violates it.
    if (stripos($key, 'PRIMARY KEY') === 0)  return null;
    if (stripos($key, 'CONSTRAINT') === 0)   return null;
    if (stripos($key, 'FOREIGN KEY') === 0)  return null;

    if (preg_match('/^UNIQUE KEY\s+(`[^`]+`\s*\(.+)$/is', $key, $m)) {
        return 'ADD UNIQUE INDEX IF NOT EXISTS ' . $m[1];
    }
    if (preg_match('/^FULLTEXT KEY\s+(`[^`]+`\s*\(.+)$/is', $key, $m)) {
        return 'ADD FULLTEXT INDEX IF NOT EXISTS ' . $m[1];
    }
    if (preg_match('/^SPATIAL KEY\s+(`[^`]+`\s*\(.+)$/is', $key, $m)) {
        return 'ADD SPATIAL INDEX IF NOT EXISTS ' . $m[1];
    }
    if (preg_match('/^KEY\s+(`[^`]+`\s*\(.+)$/is', $key, $m)) {
        return 'ADD INDEX IF NOT EXISTS ' . $m[1];
    }
    return null;
}

// ── Emit ────────────────────────────────────────────────────────────────────
$now = date('Y-m-d H:i');
$out = [];

$out[] = "-- =========================================================================";
$out[] = "-- EduTrack — schema only, idempotent. NO DATA.";
$out[] = "--";
$out[] = "-- Generated {$now} by database/generate_schema_sql.php from `{$db}`.";
$out[] = "-- " . count($tables) . " tables. WordPress `oric_*` tables are excluded.";
$out[] = "--";
$out[] = "-- Safe to run repeatedly on a live database:";
$out[] = "--   * missing TABLES  are created (CREATE TABLE IF NOT EXISTS)";
$out[] = "--   * missing COLUMNS are added   (ADD COLUMN IF NOT EXISTS)";
$out[] = "--   * missing INDEXES are added   (ADD INDEX  IF NOT EXISTS)";
$out[] = "--";
$out[] = "-- It never DROPs anything and never MODIFYs an existing column, so a";
$out[] = "-- column whose type has drifted is left alone rather than rewritten —";
$out[] = "-- altering a populated column can truncate data, so that stays manual.";
$out[] = "--";
$out[] = "-- Foreign keys are intentionally omitted: adding one to a live table";
$out[] = "-- fails outright if any existing row violates it.";
$out[] = "--";
$out[] = "-- Requires MariaDB 10.0.2+ (uses ADD COLUMN/INDEX IF NOT EXISTS).";
$out[] = "-- BACK UP FIRST:  mysqldump -u USER -p DBNAME > backup.sql";
$out[] = "-- =========================================================================";
$out[] = "";
$out[] = "SET NAMES utf8mb4;";
$out[] = "SET FOREIGN_KEY_CHECKS = 0;";
$out[] = "SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';";
$out[] = "";

$stats = ['tables' => 0, 'columns' => 0, 'indexes' => 0];

foreach ($tables as $table) {
    $row = $conn->query("SHOW CREATE TABLE `{$table}`")->fetch_assoc();
    $ddl = $row['Create Table'] ?? '';
    if ($ddl === '') continue;

    [$columns, $keys, $options] = splitDefinition($ddl);
    if (!$columns) continue;

    // Columns belonging to the PRIMARY KEY are created with the table and are
    // never added afterwards — an AUTO_INCREMENT column cannot be bolted onto a
    // table that already has a primary key.
    $pkCols = [];
    foreach ($keys as $k) {
        if (stripos($k, 'PRIMARY KEY') === 0 && preg_match_all('/`([^`]+)`/', $k, $m)) {
            $pkCols = $m[1];
        }
    }

    $out[] = "-- -------------------------------------------------------------------------";
    $out[] = "-- {$table}";
    $out[] = "-- -------------------------------------------------------------------------";

    // 1. Create the table if it is absent.
    $create = (string) preg_replace('/^CREATE TABLE /', 'CREATE TABLE IF NOT EXISTS ', $ddl, 1);
    $create = (string) preg_replace('/\s*AUTO_INCREMENT=\d+/', '', $create);
    $out[]  = $create . ';';
    $stats['tables']++;

    // 2. Add any column the table is missing, in its original position.
    $prev = null;
    foreach ($columns as $def) {
        $name = columnName($def);
        if ($name === null) continue;

        if (!in_array($name, $pkCols, true)) {
            $where  = $prev === null ? 'FIRST' : "AFTER `{$prev}`";
            $out[]  = "ALTER TABLE `{$table}` ADD COLUMN IF NOT EXISTS {$def} {$where};";
            $stats['columns']++;
        }
        $prev = $name;
    }

    // 3. Add any missing secondary index.
    foreach ($keys as $k) {
        $add = keyToAdd($k);
        if ($add !== null) {
            $out[] = "ALTER TABLE `{$table}` {$add};";
            $stats['indexes']++;
        }
    }

    $out[] = "";
}

$out[] = "SET FOREIGN_KEY_CHECKS = 1;";
$out[] = "";
$out[] = "-- Summary: {$stats['tables']} tables, {$stats['columns']} column guards, {$stats['indexes']} index guards.";
$out[] = "";

echo implode("\n", $out);

fwrite(STDERR, sprintf(
    "Generated schema for %d tables (%d column guards, %d index guards).\n",
    $stats['tables'], $stats['columns'], $stats['indexes']
));
