<?php

// Configuration
$sqlFile = __DIR__ . '/schema.sql';
$migrationPath = __DIR__ . '/app/Database/Migrations/';

if (!file_exists($sqlFile)) {
    die("❌ SQL file not found at $sqlFile\n");
}

$sql = file_get_contents($sqlFile);

// Match CREATE TABLE statements
preg_match_all('/CREATE TABLE\s+(?:IF NOT EXISTS\s+)?`?(\w+)`?\s*\((.*?)\)\s*(?:ENGINE|CHARSET|;)/is', $sql, $matches, PREG_SET_ORDER);

echo "Found " . count($matches) . " table(s).\n";

foreach ($matches as $match) {
    $tableName = $match[1];
    $columnsSql = trim($match[2]);

    $className = 'Create' . ucfirst(camelCase($tableName)) . 'Table';
    $timestamp = date('YmdHis');
    $filename = $timestamp . '_create_' . strtolower($tableName) . '_table.php';
    sleep(1); // Avoid filename collisions

    $columnsArray = parseColumns($columnsSql);
    $fieldDefs = array_map(
        fn($name, $def) => is_numeric($name)
            ? "            $def,"
            : "            '$name' => $def,",
        array_keys($columnsArray),
        $columnsArray
    );
    $fieldDefsText = implode("\n", $fieldDefs);

    $migrationContent = <<<PHP
<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class $className extends Migration
{
    public function up()
    {
        \$this->forge->addField([
$fieldDefsText
        ]);
        \$this->forge->addKey('id', true); // Adjust if needed
        \$this->forge->createTable('$tableName');
    }

    public function down()
    {
        \$this->forge->dropTable('$tableName');
    }
}
PHP;

    file_put_contents($migrationPath . $filename, $migrationContent);
    echo "✅ Created migration for `$tableName`: $filename\n";
}

function parseColumns(string $columnsSql): array
{
    $lines = preg_split('/,\n|\n/', $columnsSql);
    $result = [];

    foreach ($lines as $line) {
        $line = trim($line);

        // Skip keys, constraints
        if (preg_match('/^(PRIMARY|UNIQUE|KEY|CONSTRAINT|FOREIGN)/i', $line)) {
            continue;
        }

        // Handle ENUM as raw SQL
        if (preg_match("/^`(\w+)`\s+enum\((.*?)\)(.*)/i", $line, $enumMatch)) {
            $name = $enumMatch[1];
            $values = $enumMatch[2];
            $rest = $enumMatch[3];

            // Wrap the whole ENUM in double quotes, and escape single quotes
            $enumSQL = '"' . "$name ENUM($values)$rest" . '"';
            $enumSQL = str_replace("`", "", $enumSQL);
            $result[] = $enumSQL;
            continue;
        }


        if (preg_match('/^`(\w+)`\s+([a-zA-Z]+)(\(([^)]+)\))?/i', $line, $col)) {
            $field = $col[1];
            $type = strtoupper($col[2]);
            $length = $col[4] ?? null;

            $definition = [];
            $definition[] = "'type' => '$type'";

            if ($length && !in_array($type, ['TEXT', 'DATE', 'DATETIME', 'TIMESTAMP'])) {
                $definition[] = "'constraint' => $length";
            }

            $definition[] = stripos($line, 'NOT NULL') !== false ? "'null' => false" : "'null' => true";

            if (stripos($line, 'AUTO_INCREMENT') !== false) {
                $definition[] = "'auto_increment' => true";
            }

            if (preg_match('/DEFAULT\s+([^\s,]+)/i', $line, $defMatch)) {
                $default = trim($defMatch[1], "'\"");
                if (strtoupper($default) !== 'NULL') {
                    $definition[] = "'default' => '$default'";
                }
            }

            $result[$field] = '[' . implode(', ', $definition) . ']';
        }
    }

    return $result;
}

function camelCase(string $str): string
{
    return str_replace(' ', '', ucwords(str_replace(['-', '_'], ' ', $str)));
}
