92 lines
2.6 KiB
PHP
92 lines
2.6 KiB
PHP
<?php
|
|
|
|
use Illuminate\Database\Migrations\Migration;
|
|
use Illuminate\Database\Schema\Blueprint;
|
|
use Illuminate\Support\Facades\DB;
|
|
use Illuminate\Support\Facades\Schema;
|
|
|
|
return new class extends Migration
|
|
{
|
|
public function up(): void
|
|
{
|
|
Schema::create('regions', function (Blueprint $table) {
|
|
$table->id();
|
|
$table->string('name');
|
|
$table->foreignId('country_id')->constrained('countries');
|
|
$table->string('code');
|
|
$table->string('local_code');
|
|
$table->string('continent', 2);
|
|
$table->timestamps();
|
|
});
|
|
|
|
$this->importCsv();
|
|
}
|
|
|
|
public function down(): void
|
|
{
|
|
Schema::dropIfExists('regions');
|
|
}
|
|
|
|
private function importCsv(): void
|
|
{
|
|
$path = storage_path('app/private/seed_data/regions.csv');
|
|
|
|
if (! file_exists($path)) {
|
|
throw new \RuntimeException("Regions CSV not found at: {$path}");
|
|
}
|
|
|
|
$handle = fopen($path, 'rb');
|
|
|
|
if ($handle === false) {
|
|
throw new \RuntimeException("Failed to open regions CSV at: {$path}");
|
|
}
|
|
|
|
// Skip header row
|
|
fgetcsv($handle);
|
|
|
|
// Build a lookup map of country code -> id to resolve the foreign key
|
|
$countryMap = DB::table('countries')->pluck('id', 'code')->all();
|
|
|
|
$batch = [];
|
|
$batchSize = 500;
|
|
$now = now()->toDateTimeString();
|
|
|
|
while (($row = fgetcsv($handle)) !== false) {
|
|
if (count($row) < 6) {
|
|
continue;
|
|
}
|
|
|
|
// id, code, local_code, name, continent, iso_country, wikipedia_link, keywords
|
|
[, $code, $localCode, $name, $continent, $isoCountry] = $row;
|
|
|
|
$isoCountry = trim($isoCountry);
|
|
|
|
if (! isset($countryMap[$isoCountry])) {
|
|
// Skip regions whose country wasn't imported (shouldn't happen with a complete dataset)
|
|
continue;
|
|
}
|
|
|
|
$batch[] = [
|
|
'code' => trim($code),
|
|
'local_code' => trim($localCode),
|
|
'name' => trim($name),
|
|
'continent' => trim($continent),
|
|
'country_id' => $countryMap[$isoCountry],
|
|
'created_at' => $now,
|
|
'updated_at' => $now,
|
|
];
|
|
|
|
if (count($batch) >= $batchSize) {
|
|
DB::table('regions')->insert($batch);
|
|
$batch = [];
|
|
}
|
|
}
|
|
|
|
fclose($handle);
|
|
|
|
if (! empty($batch)) {
|
|
DB::table('regions')->insert($batch);
|
|
}
|
|
}
|
|
};
|