<?php
declare(strict_types=1);

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing;

$projectRoot = dirname(__DIR__, 6);
require $projectRoot . '/vendor/autoload.php';

$input = $argv[1] ?? '';
if ($input === '' || !is_file($input)) {
    fwrite(STDERR, "Usage: php import-agent-xlsx.php /absolute/path/agents.xlsx\n");
    exit(1);
}

$moduleRoot = dirname(__DIR__, 2);
$logoDir = $moduleRoot . '/view/frontend/web/images/agents';
$dataFile = $moduleRoot . '/view/frontend/web/data/agents.json';

if (!is_dir($logoDir) && !mkdir($logoDir, 0775, true) && !is_dir($logoDir)) {
    throw new RuntimeException('Unable to create logo directory.');
}
if (!is_dir(dirname($dataFile)) && !mkdir(dirname($dataFile), 0775, true) && !is_dir(dirname($dataFile))) {
    throw new RuntimeException('Unable to create data directory.');
}

foreach (glob($logoDir . '/*.png') ?: [] as $oldLogo) {
    unlink($oldLogo);
}

$spreadsheet = IOFactory::load($input);
$sheet = $spreadsheet->getSheet(0);
$rows = $sheet->toArray(null, true, true, true);

$drawingsByRow = [];
foreach ($sheet->getDrawingCollection() as $drawing) {
    $coordinate = $drawing->getCoordinates();
    if (!preg_match('/^H(\d+)$/i', $coordinate, $matches)) {
        continue;
    }

    $drawingsByRow[(int) $matches[1]][] = $drawing;
}

// Excel may anchor two logos in one tall H cell for two adjacent records (the
// BRACK/ALLTRON rows in the current source file). Move extra drawings to the
// next logo-less record while keeping their visual order.
foreach ($drawingsByRow as $rowNumber => $rowDrawings) {
    if (count($rowDrawings) < 2) {
        continue;
    }

    $drawingsByRow[$rowNumber] = [array_shift($rowDrawings)];
    $targetRow = $rowNumber + 1;
    foreach ($rowDrawings as $drawing) {
        while (isset($drawingsByRow[$targetRow])) {
            $targetRow++;
        }
        $drawingsByRow[$targetRow] = [$drawing];
        $targetRow++;
    }
}
ksort($drawingsByRow);

$agents = [];
$usedSlugs = [];
$unmappedCountries = [];

foreach ($rows as $rowNumber => $row) {
    if ($rowNumber === 1 || trim((string) ($row['B'] ?? '')) === '') {
        continue;
    }

    $countrySource = cleanText((string) ($row['C'] ?? ''));
    $country = countryMetadata($countrySource);
    if ($country === null) {
        $unmappedCountries[$countrySource] = true;
        continue;
    }

    $id = count($agents) + 1;
    $mapPosition = mapPosition($country['code']);
    $name = cleanText((string) ($row['B'] ?? $row['A'] ?? ''));
    $baseSlug = slugify($name) ?: sprintf('agent-%03d', $id);
    $slug = $baseSlug;
    $suffix = 2;
    while (isset($usedSlugs[$slug])) {
        $slug = $baseSlug . '-' . $suffix++;
    }
    $usedSlugs[$slug] = true;

    $logo = '';
    if (isset($drawingsByRow[$rowNumber][0])) {
        $logo = $slug . '.png';
        saveDrawingAsPng($drawingsByRow[$rowNumber][0], $logoDir . '/' . $logo);
    }

    $agents[] = [
        'id' => $id,
        'slug' => $slug,
        'system_name' => cleanText((string) ($row['A'] ?? '')),
        'name' => $name,
        'continent' => $country['continent'],
        'country_code' => $country['code'],
        'country' => $country['name'],
        'website' => normalizeWebsite((string) ($row['E'] ?? '')),
        'address' => cleanText((string) ($row['F'] ?? '')),
        'logo' => $logo,
        'note' => cleanText((string) ($row['I'] ?? '')),
        'map_x' => $mapPosition['x'],
        'map_y' => $mapPosition['y'],
        'sort_order' => $id,
    ];
}

if ($unmappedCountries !== []) {
    throw new RuntimeException('Unmapped countries: ' . implode(', ', array_keys($unmappedCountries)));
}

$payload = [
    'version' => 1,
    'source_updated_at' => date('Y-m-d', (int) filemtime($input)),
    'continents' => [
        ['code' => 'north_america', 'label' => 'North America', 'x' => 21.5, 'y' => 34.5],
        ['code' => 'south_america', 'label' => 'South America', 'x' => 31.5, 'y' => 64.5],
        ['code' => 'europe', 'label' => 'Europe', 'x' => 49.5, 'y' => 31.5],
        ['code' => 'africa', 'label' => 'Africa', 'x' => 51.5, 'y' => 55.5],
        ['code' => 'asia', 'label' => 'Asia', 'x' => 69.0, 'y' => 37.5],
        ['code' => 'oceania', 'label' => 'Oceania', 'x' => 83.0, 'y' => 68.0],
        ['code' => 'antarctica', 'label' => 'Antarctica', 'x' => 51.0, 'y' => 91.0],
    ],
    'agents' => $agents,
];

file_put_contents(
    $dataFile,
    json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE | JSON_THROW_ON_ERROR) . "\n"
);

printf("Imported %d agents and %d logos.\n", count($agents), count(array_filter(array_column($agents, 'logo'))));

function cleanText(string $value): string
{
    $value = str_replace(["\xc2\xa0", "\r\n", "\r"], [' ', "\n", "\n"], $value);
    $value = preg_replace('/[ \t]+/u', ' ', trim($value)) ?? trim($value);
    return $value;
}

function normalizeWebsite(string $website): string
{
    $website = cleanText($website);
    if ($website === '') {
        return '';
    }

    $website = preg_replace('#^http:/([^/])#i', 'http://$1', $website) ?? $website;
    if (!preg_match('#^https?://#i', $website)) {
        $website = 'https://' . $website;
    }

    return $website;
}

function slugify(string $value): string
{
    $ascii = iconv('UTF-8', 'ASCII//TRANSLIT//IGNORE', $value) ?: $value;
    $slug = strtolower((string) preg_replace('/[^a-zA-Z0-9]+/', '-', $ascii));
    return trim($slug, '-');
}

function countryMetadata(string $source): ?array
{
    $key = mb_strtolower(trim($source));
    $key = str_replace(['（', '）'], ['(', ')'], $key);
    $key = preg_replace('/\s+/u', ' ', $key) ?? $key;

    $countries = [
        'argentina' => ['Argentina', 'AR', 'south_america'],
        'australia' => ['Australia', 'AU', 'oceania'],
        'austria' => ['Austria', 'AT', 'europe'],
        'belgium' => ['Belgium', 'BE', 'europe'],
        'brazil' => ['Brazil', 'BR', 'south_america'],
        'bulgaria' => ['Bulgaria', 'BG', 'europe'],
        'canada' => ['Canada', 'CA', 'north_america'],
        'chile' => ['Chile', 'CL', 'south_america'],
        'china(taiwan)' => ['China (Taiwan)', 'TW', 'asia'],
        'china (taiwan)' => ['China (Taiwan)', 'TW', 'asia'],
        'ecuador' => ['Ecuador', 'EC', 'south_america'],
        'egypt' => ['Egypt', 'EG', 'africa'],
        'estonia' => ['Estonia', 'EE', 'europe'],
        'finland' => ['Finland', 'FI', 'europe'],
        'france' => ['France', 'FR', 'europe'],
        'germany' => ['Germany', 'DE', 'europe'],
        'greece' => ['Greece', 'GR', 'europe'],
        'guatemala' => ['Guatemala', 'GT', 'north_america'],
        'india' => ['India', 'IN', 'asia'],
        'indonesia' => ['Indonesia', 'ID', 'asia'],
        'israel' => ['Israel', 'IL', 'asia'],
        'korea' => ['Korea', 'KR', 'asia'],
        'mexico' => ['Mexico', 'MX', 'north_america'],
        'mongolia' => ['Mongolia', 'MN', 'asia'],
        'netherlands' => ['Netherlands', 'NL', 'europe'],
        'new zealand' => ['New Zealand', 'NZ', 'oceania'],
        'norway' => ['Norway', 'NO', 'europe'],
        'peru' => ['Peru', 'PE', 'south_america'],
        'philippines' => ['Philippines', 'PH', 'asia'],
        'poland' => ['Poland', 'PL', 'europe'],
        'russia' => ['Russia', 'RU', 'europe'],
        'saudia arabia' => ['Saudi Arabia', 'SA', 'asia'],
        'singapore' => ['Singapore', 'SG', 'asia'],
        'south africa' => ['South Africa', 'ZA', 'africa'],
        'switzerland' => ['Switzerland', 'CH', 'europe'],
        'thailand' => ['Thailand', 'TH', 'asia'],
        'turkey' => ['Turkey', 'TR', 'asia'],
        'uk' => ['United Kingdom', 'GB', 'europe'],
        'united arab emirates' => ['United Arab Emirates', 'AE', 'asia'],
        'united kingdom' => ['United Kingdom', 'GB', 'europe'],
        'united states' => ['United States', 'US', 'north_america'],
        'uzbekistan' => ['Uzbekistan', 'UZ', 'asia'],
        'vietnam' => ['Vietnam', 'VN', 'asia'],
    ];

    if (!isset($countries[$key])) {
        return null;
    }

    return [
        'name' => $countries[$key][0],
        'code' => $countries[$key][1],
        'continent' => $countries[$key][2],
    ];
}

function mapPosition(string $countryCode): array
{
    // This SVG is an intentionally stylized projection. These positions are
    // calibrated against its actual land shapes rather than raw longitude and latitude.
    $svgOverrides = [
        'ID' => ['x' => 81.5, 'y' => 66.0],
        'SG' => ['x' => 77.0, 'y' => 60.5],
        'TH' => ['x' => 76.2, 'y' => 52.0],
        'TW' => ['x' => 81.6, 'y' => 45.0],
        'VN' => ['x' => 77.6, 'y' => 52.5],
    ];

    if (isset($svgOverrides[$countryCode])) {
        return $svgOverrides[$countryCode];
    }

    $centroids = [
        'AE' => [54.0, 24.0], 'AR' => [-64.0, -34.0], 'AT' => [14.0, 47.5],
        'AU' => [134.0, -25.0], 'BE' => [4.5, 50.5], 'BG' => [25.0, 43.0],
        'BR' => [-52.0, -10.0], 'CA' => [-106.0, 56.0], 'CH' => [8.0, 47.0],
        'CL' => [-71.0, -33.0], 'DE' => [10.0, 51.0], 'EC' => [-78.0, -1.5],
        'EE' => [25.5, 58.5], 'EG' => [30.0, 27.0], 'FI' => [26.0, 64.0],
        'FR' => [2.0, 46.0], 'GB' => [-2.0, 54.0], 'GR' => [22.0, 39.0],
        'GT' => [-90.2, 15.7], 'ID' => [118.0, -2.0], 'IL' => [35.0, 31.5],
        'IN' => [79.0, 22.0], 'KR' => [128.0, 36.0], 'MN' => [104.0, 46.0],
        'MX' => [-102.0, 23.0], 'NL' => [5.5, 52.0], 'NO' => [9.0, 61.0],
        'NZ' => [172.0, -41.0], 'PE' => [-75.0, -9.0], 'PH' => [122.0, 13.0],
        'PL' => [19.0, 52.0], 'RU' => [80.0, 55.0], 'SA' => [45.0, 24.0],
        'SG' => [103.8, 1.3], 'TH' => [101.0, 15.0], 'TR' => [35.0, 39.0],
        'TW' => [121.0, 24.0], 'US' => [-98.0, 39.0], 'UZ' => [64.0, 41.0],
        'VN' => [108.0, 16.0], 'ZA' => [24.0, -30.0],
    ];

    if (!isset($centroids[$countryCode])) {
        return ['x' => null, 'y' => null];
    }

    [$longitude, $latitude] = $centroids[$countryCode];
    $x = ($longitude + 180) / 360 * 100;
    $y = (90 - $latitude) / 180 * 100 + max(0, $latitude) / 12;

    return [
        'x' => round(max(2, min(98, $x)), 3),
        'y' => round(max(4, min(92, $y)), 3),
    ];
}

function saveDrawingAsPng(Drawing|MemoryDrawing $drawing, string $destination): void
{
    if ($drawing instanceof MemoryDrawing) {
        ob_start();
        $renderingFunction = $drawing->getRenderingFunction();
        $renderingFunction($drawing->getImageResource());
        $bytes = (string) ob_get_clean();
    } else {
        $bytes = file_get_contents($drawing->getPath());
        if ($bytes === false) {
            throw new RuntimeException('Unable to read drawing: ' . $drawing->getName());
        }
    }

    $source = imagecreatefromstring($bytes);
    if ($source === false) {
        throw new RuntimeException('Unsupported logo image: ' . $drawing->getName());
    }

    $width = imagesx($source);
    $height = imagesy($source);
    $scale = min(1, 560 / max(1, $width), 220 / max(1, $height));
    $targetWidth = max(1, (int) round($width * $scale));
    $targetHeight = max(1, (int) round($height * $scale));
    $target = imagecreatetruecolor($targetWidth, $targetHeight);
    imagealphablending($target, false);
    imagesavealpha($target, true);
    $transparent = imagecolorallocatealpha($target, 255, 255, 255, 127);
    imagefill($target, 0, 0, $transparent);
    imagecopyresampled($target, $source, 0, 0, 0, 0, $targetWidth, $targetHeight, $width, $height);
    imagepng($target, $destination, 8);
    imagedestroy($source);
    imagedestroy($target);
}