<?php

declare(strict_types=1);

/**
 * Generate chunked SQL dump files from DB/users.csv.
 *
 * Usage:
 *   php DB/generate_users_sql_chunks.php
 */

$csvPath = __DIR__ . DIRECTORY_SEPARATOR . 'users.csv';
$outputDir = __DIR__ . DIRECTORY_SEPARATOR . 'user_sql_chunks';
$rowsPerFile = 5000;
$valuesPerInsert = 500;

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);
}

$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) => trim((string) $h),
    $header
);

$requiredColumns = ['id', 'user_type', 'customer_type', 'name', 'email'];
$indexes = [];

foreach ($requiredColumns as $column) {
    $idx = array_search($column, $header, true);
    if ($idx === false) {
        fclose($handle);
        fwrite(STDERR, "Missing required column in CSV: {$column}" . PHP_EOL);
        exit(1);
    }
    $indexes[$column] = (int) $idx;
}

// Support renamed CSV column: prefer "mobile", fallback to legacy "phone".
$mobileIdx = array_search('mobile', $header, true);
if ($mobileIdx === false) {
    $mobileIdx = array_search('phone', $header, true);
}
if ($mobileIdx === false) {
    fclose($handle);
    fwrite(STDERR, "Missing required column in CSV: mobile (or phone)" . PHP_EOL);
    exit(1);
}
$indexes['mobile'] = (int) $mobileIdx;

// Remove previous generated SQL chunks/manifest so output is always fresh.
foreach (glob($outputDir . DIRECTORY_SEPARATOR . 'users_import_part_*.sql') ?: [] as $oldSqlFile) {
    if (is_file($oldSqlFile)) {
        @unlink($oldSqlFile);
    }
}
$oldManifest = $outputDir . DIRECTORY_SEPARATOR . 'manifest.txt';
if (is_file($oldManifest)) {
    @unlink($oldManifest);
}

/**
 * Escape string value for SQL single-quoted literal.
 */
function sqlString(string $value): string
{
    $escaped = str_replace(
        ["\\", "'"],
        ["\\\\", "\\'"],
        $value
    );

    return "'" . $escaped . "'";
}

/**
 * Convert nullable string to SQL literal.
 */
function sqlNullableString(?string $value): string
{
    if ($value === null || trim($value) === '') {
        return 'NULL';
    }

    return sqlString(trim($value));
}

/**
 * Convert nullable numeric-like id to SQL literal.
 */
function sqlNullableInt(?string $value): string
{
    if ($value === null) {
        return 'NULL';
    }

    $trimmed = trim($value);
    if ($trimmed === '' || !preg_match('/^-?\d+$/', $trimmed)) {
        return 'NULL';
    }

    return $trimmed;
}

$globalRowCount = 0;
$fileIndex = 0;
$rowsInCurrentFile = 0;
$currentInsertValues = [];
$fileHandle = null;
$currentFilePath = '';
$generatedFiles = [];

/**
 * Open a new chunk file and write SQL prologue.
 */
$openNewFile = static function () use (&$fileHandle, &$fileIndex, &$currentFilePath, $outputDir, &$generatedFiles): void {
    $fileIndex++;
    $currentFilePath = $outputDir . DIRECTORY_SEPARATOR . sprintf('users_import_part_%03d.sql', $fileIndex);
    $fileHandle = fopen($currentFilePath, 'wb');

    if ($fileHandle === false) {
        throw new RuntimeException("Unable to create dump file: {$currentFilePath}");
    }

    $generatedFiles[] = $currentFilePath;

    fwrite($fileHandle, "-- Users import chunk {$fileIndex}" . PHP_EOL);
    fwrite($fileHandle, "SET NAMES utf8mb4;" . PHP_EOL);
    fwrite($fileHandle, "SET FOREIGN_KEY_CHECKS = 0;" . PHP_EOL . PHP_EOL);
};

/**
 * Flush current multi-row INSERT statement.
 */
$flushInsert = static function () use (&$currentInsertValues, &$fileHandle): void {
    if ($fileHandle === null || count($currentInsertValues) === 0) {
        return;
    }

    $sql = "INSERT INTO `users` (`id`, `user_type`, `customer_type`, `name`, `email`, `mobile`) VALUES" . PHP_EOL;
    $sql .= implode(',' . PHP_EOL, $currentInsertValues) . ';' . PHP_EOL . PHP_EOL;

    fwrite($fileHandle, $sql);
    $currentInsertValues = [];
};

/**
 * Close current chunk file with SQL epilogue.
 */
$closeFile = static function () use (&$fileHandle): void {
    if ($fileHandle === null) {
        return;
    }

    fwrite($fileHandle, "SET FOREIGN_KEY_CHECKS = 1;" . PHP_EOL);
    fclose($fileHandle);
    $fileHandle = null;
};

try {
    $openNewFile();

    while (($row = fgetcsv($handle)) !== false) {
        if ($row === [null] || $row === []) {
            continue;
        }

        $id = $row[$indexes['id']] ?? null;
        $userType = $row[$indexes['user_type']] ?? null;
        $customerType = $row[$indexes['customer_type']] ?? null;
        $name = $row[$indexes['name']] ?? null;
        $email = $row[$indexes['email']] ?? null;
        $mobile = $row[$indexes['mobile']] ?? null;

        $tuple = sprintf(
            "(%s, %s, %s, %s, %s, %s)",
            sqlNullableInt($id),
            sqlNullableString($userType),
            sqlNullableString($customerType),
            sqlNullableString($name),
            sqlNullableString($email),
            sqlNullableString($mobile)
        );

        $currentInsertValues[] = $tuple;
        $rowsInCurrentFile++;
        $globalRowCount++;

        if (count($currentInsertValues) >= $valuesPerInsert) {
            $flushInsert();
        }

        if ($rowsInCurrentFile >= $rowsPerFile) {
            $flushInsert();
            $closeFile();
            $rowsInCurrentFile = 0;
            $openNewFile();
        }
    }

    $flushInsert();
    $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 data rows found in CSV (header only)." . 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);
    fclose($manifestHandle);
}

fwrite(STDOUT, "Done. Exported {$globalRowCount} users into " . count($generatedFiles) . " SQL chunk files." . PHP_EOL);
fwrite(STDOUT, "Output directory: {$outputDir}" . PHP_EOL);