<?php

declare(strict_types=1);

/**
 * database/build_migrations.php
 *
 * Usage: php database/build_migrations.php
 *
 * database/schema.sql is the single source of truth for the schema.
 * This script splits it into the numbered, ordered files under
 * database/migrations/ that bin/migrate.php actually applies. Run this
 * any time schema.sql changes — never hand-edit a file under
 * database/migrations/ directly, since the next run of this script
 * would silently overwrite it.
 *
 * Splitting is done on the "-- SECTION N — ..." comment headers already
 * present in schema.sql, so adding a new section there and adding its
 * name to $sectionNames below is the only step needed to add a new
 * migration file.
 */

$schemaPath = __DIR__ . '/schema.sql';
$migrationsDir = __DIR__ . '/migrations';

$sectionNames = [
    'create_migration_tracker',
    'create_rbac_and_auth',
    'create_crm_core',
    'create_shared_polymorphic',
    'create_scheduling',
    'create_billing',
    'create_email',
    'create_campaigns',
    'create_automation',
    'create_ai_conversations',
    'create_notifications_and_activity_log',
    'create_settings_and_integrations',
    'create_org_structure_and_auth_hardening',
    'create_crm_module_extensions',
    'create_marketing_system',
    'create_ai_modules_and_integrations',
    'add_performance_indexes',
    'create_saas_platform',
];

$schema = file_get_contents($schemaPath);
if ($schema === false) {
    fwrite(STDERR, "Could not read {$schemaPath}\n");
    exit(1);
}

$pattern = '/(-- =+\n-- SECTION.*?\n-- =+\n)/s';
$parts = preg_split($pattern, $schema, -1, PREG_SPLIT_DELIM_CAPTURE);

// $parts[0] is the file header (comment block + SET NAMES / FK_CHECKS=0)
// before SECTION 0; after that, headers and bodies alternate.
$sections = [];
for ($i = 1; $i < count($parts); $i += 2) {
    $sections[] = $parts[$i] . ($parts[$i + 1] ?? '');
}

if (count($sections) !== count($sectionNames)) {
    fwrite(STDERR, sprintf(
        "Section count mismatch: schema.sql has %d sections, expected %d. Update \$sectionNames in this script.\n",
        count($sections),
        count($sectionNames)
    ));
    exit(1);
}

// Clear existing generated migrations so renamed/removed sections don't
// leave stale files behind.
foreach (glob($migrationsDir . '/*.sql') ?: [] as $existing) {
    unlink($existing);
}

$setNames = "SET NAMES utf8mb4;\nSET FOREIGN_KEY_CHECKS = 0;\n\n";
$setForeignKeysBack = "\nSET FOREIGN_KEY_CHECKS = 1;\n";

foreach ($sectionNames as $index => $name) {
    $number = str_pad((string) ($index + 1), 4, '0', STR_PAD_LEFT);
    $filename = "{$migrationsDir}/{$number}_{$name}.sql";

    $content = "-- Migration {$number}: {$name}\n"
        . "-- Auto-generated from database/schema.sql by database/build_migrations.php\n"
        . "-- Do not hand-edit this file — edit schema.sql and re-run that script instead.\n\n"
        . $setNames . $sections[$index] . $setForeignKeysBack;

    file_put_contents($filename, $content);
    echo "wrote {$filename}\n";
}

echo "\nDone. Run `php bin/migrate.php` to apply.\n";
