<?php

declare(strict_types=1);

/**
 * Generate chunked SQL dump files for `addresses` table from DB/addresses.csv.
 *
 * Usage:
 *   php DB/generate_addresses_sql_chunks.php
 */

$csvPath = __DIR__ . DIRECTORY_SEPARATOR . 'addresses.csv';
$outputDir = __DIR__ . DIRECTORY_SEPARATOR . 'address_sql_chunks';
$rowsPerFile = 4000;
$valuesPerInsert = 400;

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 . 'addresses_import_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
);

$requiredColumns = [
    'id',
    'user_id',
    'name',
    'address',
    'country_id',
    'state_id',
    'city_id',
    'postal_code',
    'mobile',
    'set_default',
    'created_at',
    'updated_at',
];

$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;
}

function sqlString(string $value): string
{
    $escaped = str_replace(
        ["\\", "'"],
        ["\\\\", "\\'"],
        $value
    );

    return "'" . $escaped . "'";
}

function sqlNullableString(?string $value): string
{
    if ($value === null) {
        return 'NULL';
    }

    $value = trim($value);
    if ($value === '') {
        return 'NULL';
    }

    return sqlString($value);
}

function sqlNullableInt(?string $value): string
{
    if ($value === null) {
        return 'NULL';
    }

    $value = trim($value);
    if ($value === '' || strcasecmp($value, 'null') === 0) {
        return 'NULL';
    }

    return preg_match('/^-?\d+$/', $value) ? $value : 'NULL';
}

function toMysqlDateTime(?string $raw): ?string
{
    if ($raw === null) {
        return null;
    }

    $raw = trim($raw);
    if ($raw === '' || strcasecmp($raw, 'null') === 0) {
        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');
}

$globalRowCount = 0;
$skippedRows = 0;
$fileIndex = 0;
$rowsInCurrentFile = 0;
$currentInsertValues = [];
$fileHandle = null;
$generatedFiles = [];

$openNewFile = static function () use (&$fileHandle, &$fileIndex, $outputDir, &$generatedFiles): void {
    $fileIndex++;
    $filePath = $outputDir . DIRECTORY_SEPARATOR . sprintf('addresses_import_part_%03d.sql', $fileIndex);
    $fileHandle = fopen($filePath, 'wb');

    if ($fileHandle === false) {
        throw new RuntimeException("Unable to create dump file: {$filePath}");
    }

    $generatedFiles[] = $filePath;
    fwrite($fileHandle, "-- addresses import chunk {$fileIndex}" . PHP_EOL);
    fwrite($fileHandle, "SET NAMES utf8mb4;" . PHP_EOL);
    fwrite($fileHandle, "SET FOREIGN_KEY_CHECKS = 0;" . PHP_EOL . PHP_EOL);
};

$flushInsert = static function () use (&$fileHandle, &$currentInsertValues): void {
    if ($fileHandle === null || count($currentInsertValues) === 0) {
        return;
    }

    $sql = "INSERT INTO `addresses` (`id`, `user_id`, `name`, `mobile`, `line1`, `line2`, `country_id`, `state_id`, `city_id`, `city`, `state`, `pin`, `is_default`, `created_at`, `updated_at`) VALUES" . PHP_EOL;
    $sql .= implode(',' . PHP_EOL, $currentInsertValues) . ';' . PHP_EOL . PHP_EOL;
    fwrite($fileHandle, $sql);
    $currentInsertValues = [];
};

$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 = sqlNullableInt($row[$indexes['id']] ?? null);
        $userId = sqlNullableInt($row[$indexes['user_id']] ?? null);
        $name = sqlNullableString($row[$indexes['name']] ?? null);
        $mobile = sqlNullableString($row[$indexes['mobile']] ?? null);
        $line1 = sqlNullableString($row[$indexes['address']] ?? null);
        $line2 = 'NULL';
        $countryId = sqlNullableInt($row[$indexes['country_id']] ?? null);
        $stateId = sqlNullableInt($row[$indexes['state_id']] ?? null);
        $cityId = sqlNullableInt($row[$indexes['city_id']] ?? null);
        $city = "''";
        $state = "''";
        $pin = sqlNullableString($row[$indexes['postal_code']] ?? null);
        $isDefault = sqlNullableInt($row[$indexes['set_default']] ?? null);

        $createdAtRaw = $row[$indexes['created_at']] ?? null;
        $updatedAtRaw = $row[$indexes['updated_at']] ?? null;
        $createdAt = toMysqlDateTime(is_string($createdAtRaw) ? $createdAtRaw : null);
        $updatedAt = toMysqlDateTime(is_string($updatedAtRaw) ? $updatedAtRaw : null);

        $createdAtSql = $createdAt === null ? 'NULL' : sqlString($createdAt);
        $updatedAtSql = $updatedAt === null ? 'NULL' : sqlString($updatedAt);

        if ($userId === 'NULL' || $line1 === 'NULL') {
            $skippedRows++;
            continue;
        }

        $tuple = sprintf(
            "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
            $id,
            $userId,
            $name,
            $mobile,
            $line1,
            $line2,
            $countryId,
            $stateId,
            $cityId,
            $city,
            $state,
            $pin,
            $isDefault,
            $createdAtSql,
            $updatedAtSql
        );

        $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 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} rows into " . count($generatedFiles) . " SQL chunk files." . PHP_EOL);
fwrite(STDOUT, "Skipped rows: {$skippedRows}" . PHP_EOL);
fwrite(STDOUT, "Output directory: {$outputDir}" . PHP_EOL);