<?php

declare(strict_types=1);

/**
 * Generate chunked SQL files to update users.created_at by user id.
 *
 * Source CSV format:
 *   id,created_at
 *   10,22-08-2025 15:29
 *
 * Usage:
 *   php DB/generate_users_created_at_update_chunks.php
 */

$csvPath = __DIR__ . DIRECTORY_SEPARATOR . 'users_created_date.csv';
$outputDir = __DIR__ . DIRECTORY_SEPARATOR . 'user_created_at_update_sql_chunks';
$rowsPerFile = 5000;

if (!is_file($csvPath)) {
    fwrite(STDERR, "CSV file not found: {$csvPath}" . PHP_EOL);
    exit(1);
}

if (!is_dir($outputDir) && !mkdir($outputDir, 0777, true) && !is_dir($outputDir)) {
    fwrite(STDERR, "Unable to create output directory: {$outputDir}" . PHP_EOL);
    exit(1);
}

// Clean previous generated files.
foreach (glob($outputDir . DIRECTORY_SEPARATOR . 'users_created_at_update_part_*.sql') ?: [] as $oldSqlFile) {
    if (is_file($oldSqlFile)) {
        @unlink($oldSqlFile);
    }
}
$oldManifest = $outputDir . DIRECTORY_SEPARATOR . 'manifest.txt';
if (is_file($oldManifest)) {
    @unlink($oldManifest);
}

$handle = fopen($csvPath, 'rb');
if ($handle === false) {
    fwrite(STDERR, "Unable to open CSV file: {$csvPath}" . PHP_EOL);
    exit(1);
}

$header = fgetcsv($handle);
if ($header === false) {
    fclose($handle);
    fwrite(STDERR, "CSV appears empty: {$csvPath}" . PHP_EOL);
    exit(1);
}

$header = array_map(
    static fn($h) => strtolower(trim((string) $h)),
    $header
);

$idIdx = array_search('id', $header, true);
$createdAtIdx = array_search('created_at', $header, true);

if ($idIdx === false || $createdAtIdx === false) {
    fclose($handle);
    fwrite(STDERR, "CSV must contain headers: id, created_at" . PHP_EOL);
    exit(1);
}

/**
 * Convert CSV date like "22-08-2025 15:29" to "2025-08-22 15:29:00".
 */
function toMysqlDateTime(string $raw): ?string
{
    $raw = trim($raw);
    if ($raw === '') {
        return null;
    }

    $dt = DateTime::createFromFormat('d-m-Y H:i', $raw);
    if ($dt === false) {
        return null;
    }

    return $dt->format('Y-m-d H:i:s');
}

/**
 * Escape SQL string with single quotes.
 */
function sqlString(string $value): string
{
    $escaped = str_replace(
        ["\\", "'"],
        ["\\\\", "\\'"],
        $value
    );

    return "'" . $escaped . "'";
}

$globalRowCount = 0;
$skippedRows = 0;
$fileIndex = 0;
$rowsInCurrentFile = 0;
$fileHandle = null;
$generatedFiles = [];

$openNewFile = static function () use (&$fileHandle, &$fileIndex, $outputDir, &$generatedFiles): void {
    $fileIndex++;
    $filePath = $outputDir . DIRECTORY_SEPARATOR . sprintf('users_created_at_update_part_%03d.sql', $fileIndex);
    $fileHandle = fopen($filePath, 'wb');

    if ($fileHandle === false) {
        throw new RuntimeException("Unable to create dump file: {$filePath}");
    }

    $generatedFiles[] = $filePath;
    fwrite($fileHandle, "-- users.created_at update chunk {$fileIndex}" . PHP_EOL);
    fwrite($fileHandle, "SET NAMES utf8mb4;" . PHP_EOL);
    fwrite($fileHandle, "SET FOREIGN_KEY_CHECKS = 0;" . PHP_EOL . PHP_EOL);
};

$closeFile = static function () use (&$fileHandle): void {
    if ($fileHandle === null) {
        return;
    }

    fwrite($fileHandle, PHP_EOL . "SET FOREIGN_KEY_CHECKS = 1;" . PHP_EOL);
    fclose($fileHandle);
    $fileHandle = null;
};

try {
    $openNewFile();

    while (($row = fgetcsv($handle)) !== false) {
        if ($row === [null] || $row === []) {
            continue;
        }

        $idRaw = trim((string) ($row[(int) $idIdx] ?? ''));
        $createdAtRaw = (string) ($row[(int) $createdAtIdx] ?? '');

        if ($idRaw === '' || !preg_match('/^\d+$/', $idRaw)) {
            $skippedRows++;
            continue;
        }

        $mysqlDate = toMysqlDateTime($createdAtRaw);
        if ($mysqlDate === null) {
            $skippedRows++;
            continue;
        }

        $sql = sprintf(
            "UPDATE `users` SET `created_at` = %s WHERE `id` = %s;",
            sqlString($mysqlDate),
            $idRaw
        );

        fwrite($fileHandle, $sql . PHP_EOL);
        $rowsInCurrentFile++;
        $globalRowCount++;

        if ($rowsInCurrentFile >= $rowsPerFile) {
            $closeFile();
            $rowsInCurrentFile = 0;
            $openNewFile();
        }
    }

    $closeFile();
    fclose($handle);
} catch (Throwable $e) {
    if (is_resource($handle)) {
        fclose($handle);
    }
    if (is_resource($fileHandle)) {
        fclose($fileHandle);
    }
    fwrite(STDERR, 'Error: ' . $e->getMessage() . PHP_EOL);
    exit(1);
}

if ($globalRowCount === 0) {
    fwrite(STDOUT, "No valid rows found in CSV." . PHP_EOL);
    exit(0);
}

$manifestPath = $outputDir . DIRECTORY_SEPARATOR . 'manifest.txt';
$manifestHandle = fopen($manifestPath, 'wb');
if ($manifestHandle !== false) {
    fwrite($manifestHandle, "Generated files:" . PHP_EOL);
    foreach ($generatedFiles as $file) {
        fwrite($manifestHandle, basename($file) . PHP_EOL);
    }
    fwrite($manifestHandle, PHP_EOL . "Total rows exported: {$globalRowCount}" . PHP_EOL);
    fwrite($manifestHandle, "Skipped rows: {$skippedRows}" . PHP_EOL);
    fclose($manifestHandle);
}

fwrite(STDOUT, "Done. Exported {$globalRowCount} updates into " . count($generatedFiles) . " SQL chunk files." . PHP_EOL);
fwrite(STDOUT, "Skipped rows: {$skippedRows}" . PHP_EOL);
fwrite(STDOUT, "Output directory: {$outputDir}" . PHP_EOL);