/**
 * Script to generate ports seed migration from sea-ports JSON data
 *
 * Data source: https://github.com/marchah/sea-ports
 * License: MIT
 *
 * This script:
 * 1. Reads sea-ports.json from /tmp
 * 2. Parses and validates port data
 * 3. Generates SQL INSERT statements
 * 4. Creates a TypeORM migration file
 */

import * as fs from 'fs';
import * as path from 'path';

interface SeaPort {
  name: string;
  city: string;
  country: string;
  coordinates: [number, number]; // [longitude, latitude]
  province?: string;
  timezone?: string;
  unlocs: string[];
  code?: string;
  alias?: string[];
  regions?: string[];
}

interface SeaPortsData {
  [locode: string]: SeaPort;
}

interface ParsedPort {
  code: string;
  name: string;
  city: string;
  country: string;
  countryName: string;
  countryCode: string;
  latitude: number;
  longitude: number;
  timezone: string | null;
  isActive: boolean;
}

// Country code to name mapping (ISO 3166-1 alpha-2)
const countryNames: { [key: string]: string } = {
  AE: 'United Arab Emirates',
  AG: 'Antigua and Barbuda',
  AL: 'Albania',
  AM: 'Armenia',
  AO: 'Angola',
  AR: 'Argentina',
  AT: 'Austria',
  AU: 'Australia',
  AZ: 'Azerbaijan',
  BA: 'Bosnia and Herzegovina',
  BB: 'Barbados',
  BD: 'Bangladesh',
  BE: 'Belgium',
  BG: 'Bulgaria',
  BH: 'Bahrain',
  BN: 'Brunei',
  BR: 'Brazil',
  BS: 'Bahamas',
  BZ: 'Belize',
  CA: 'Canada',
  CH: 'Switzerland',
  CI: 'Ivory Coast',
  CL: 'Chile',
  CM: 'Cameroon',
  CN: 'China',
  CO: 'Colombia',
  CR: 'Costa Rica',
  CU: 'Cuba',
  CY: 'Cyprus',
  CZ: 'Czech Republic',
  DE: 'Germany',
  DJ: 'Djibouti',
  DK: 'Denmark',
  DO: 'Dominican Republic',
  DZ: 'Algeria',
  EC: 'Ecuador',
  EE: 'Estonia',
  EG: 'Egypt',
  ES: 'Spain',
  FI: 'Finland',
  FJ: 'Fiji',
  FR: 'France',
  GA: 'Gabon',
  GB: 'United Kingdom',
  GE: 'Georgia',
  GH: 'Ghana',
  GI: 'Gibraltar',
  GR: 'Greece',
  GT: 'Guatemala',
  GY: 'Guyana',
  HK: 'Hong Kong',
  HN: 'Honduras',
  HR: 'Croatia',
  HT: 'Haiti',
  HU: 'Hungary',
  ID: 'Indonesia',
  IE: 'Ireland',
  IL: 'Israel',
  IN: 'India',
  IQ: 'Iraq',
  IR: 'Iran',
  IS: 'Iceland',
  IT: 'Italy',
  JM: 'Jamaica',
  JO: 'Jordan',
  JP: 'Japan',
  KE: 'Kenya',
  KH: 'Cambodia',
  KR: 'South Korea',
  KW: 'Kuwait',
  KZ: 'Kazakhstan',
  LB: 'Lebanon',
  LK: 'Sri Lanka',
  LR: 'Liberia',
  LT: 'Lithuania',
  LV: 'Latvia',
  LY: 'Libya',
  MA: 'Morocco',
  MC: 'Monaco',
  MD: 'Moldova',
  ME: 'Montenegro',
  MG: 'Madagascar',
  MK: 'North Macedonia',
  MM: 'Myanmar',
  MN: 'Mongolia',
  MO: 'Macau',
  MR: 'Mauritania',
  MT: 'Malta',
  MU: 'Mauritius',
  MV: 'Maldives',
  MX: 'Mexico',
  MY: 'Malaysia',
  MZ: 'Mozambique',
  NA: 'Namibia',
  NG: 'Nigeria',
  NI: 'Nicaragua',
  NL: 'Netherlands',
  NO: 'Norway',
  NZ: 'New Zealand',
  OM: 'Oman',
  PA: 'Panama',
  PE: 'Peru',
  PG: 'Papua New Guinea',
  PH: 'Philippines',
  PK: 'Pakistan',
  PL: 'Poland',
  PR: 'Puerto Rico',
  PT: 'Portugal',
  PY: 'Paraguay',
  QA: 'Qatar',
  RO: 'Romania',
  RS: 'Serbia',
  RU: 'Russia',
  SA: 'Saudi Arabia',
  SD: 'Sudan',
  SE: 'Sweden',
  SG: 'Singapore',
  SI: 'Slovenia',
  SK: 'Slovakia',
  SN: 'Senegal',
  SO: 'Somalia',
  SR: 'Suriname',
  SY: 'Syria',
  TH: 'Thailand',
  TN: 'Tunisia',
  TR: 'Turkey',
  TT: 'Trinidad and Tobago',
  TW: 'Taiwan',
  TZ: 'Tanzania',
  UA: 'Ukraine',
  UG: 'Uganda',
  US: 'United States',
  UY: 'Uruguay',
  VE: 'Venezuela',
  VN: 'Vietnam',
  YE: 'Yemen',
  ZA: 'South Africa',
};

function parseSeaPorts(filePath: string): ParsedPort[] {
  const jsonData = fs.readFileSync(filePath, 'utf-8');
  const seaPorts: SeaPortsData = JSON.parse(jsonData);

  const parsedPorts: ParsedPort[] = [];
  let skipped = 0;

  for (const [locode, port] of Object.entries(seaPorts)) {
    // Validate required fields
    if (!port.name || !port.coordinates || port.coordinates.length !== 2) {
      skipped++;
      continue;
    }

    // Extract country code from UN/LOCODE (first 2 characters)
    const countryCode = locode.substring(0, 2).toUpperCase();

    // Skip if invalid country code
    if (!countryNames[countryCode]) {
      skipped++;
      continue;
    }

    // Validate coordinates
    const [longitude, latitude] = port.coordinates;
    if (
      latitude < -90 || latitude > 90 ||
      longitude < -180 || longitude > 180
    ) {
      skipped++;
      continue;
    }

    parsedPorts.push({
      code: locode.toUpperCase(),
      name: port.name.trim(),
      city: port.city?.trim() || port.name.trim(),
      country: countryCode,
      countryName: countryNames[countryCode] || port.country,
      countryCode: countryCode,
      latitude: Number(latitude.toFixed(6)),
      longitude: Number(longitude.toFixed(6)),
      timezone: port.timezone || null,
      isActive: true,
    });
  }

  console.log(`✅ Parsed ${parsedPorts.length} ports`);
  console.log(`⚠️  Skipped ${skipped} invalid entries`);

  return parsedPorts;
}

function generateSQLInserts(ports: ParsedPort[]): string {
  const batchSize = 100;
  const batches: string[] = [];

  for (let i = 0; i < ports.length; i += batchSize) {
    const batch = ports.slice(i, i + batchSize);
    const values = batch.map(port => {
      const name = port.name.replace(/'/g, "''");
      const city = port.city.replace(/'/g, "''");
      const countryName = port.countryName.replace(/'/g, "''");
      const timezone = port.timezone ? `'${port.timezone}'` : 'NULL';

      return `(
        '${port.code}',
        '${name}',
        '${city}',
        '${port.country}',
        '${countryName}',
        ${port.latitude},
        ${port.longitude},
        ${timezone},
        ${port.isActive}
      )`;
    }).join(',\n      ');

    batches.push(`
    // Batch ${Math.floor(i / batchSize) + 1}/${Math.ceil(ports.length / batchSize)} (${batch.length} ports)
    await queryRunner.query(\`
      INSERT INTO ports (code, name, city, country, country_name, latitude, longitude, timezone, is_active)
      VALUES ${values}
    \`);
`);
  }

  return batches.join('\n');
}

function generateMigration(ports: ParsedPort[]): string {
  const timestamp = Date.now();
  const className = `SeedPorts${timestamp}`;
  const sqlInserts = generateSQLInserts(ports);

  const migrationContent = `/**
 * Migration: Seed Ports Table
 *
 * Source: sea-ports (https://github.com/marchah/sea-ports)
 * License: MIT
 * Generated: ${new Date().toISOString()}
 * Total ports: ${ports.length}
 */

import { MigrationInterface, QueryRunner } from 'typeorm';

export class ${className} implements MigrationInterface {
  name = '${className}';

  public async up(queryRunner: QueryRunner): Promise<void> {
    console.log('Seeding ${ports.length} maritime ports...');

${sqlInserts}

    console.log('✅ Successfully seeded ${ports.length} ports');
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(\`TRUNCATE TABLE ports RESTART IDENTITY CASCADE\`);
    console.log('🗑️  Cleared all ports');
  }
}
`;

  return migrationContent;
}

async function main() {
  const seaPortsPath = '/tmp/sea-ports.json';

  console.log('🚢 Generating Ports Seed Migration\n');

  // Check if sea-ports.json exists
  if (!fs.existsSync(seaPortsPath)) {
    console.error('❌ Error: /tmp/sea-ports.json not found!');
    console.log('Please download it first:');
    console.log('curl -o /tmp/sea-ports.json https://raw.githubusercontent.com/marchah/sea-ports/master/lib/ports.json');
    process.exit(1);
  }

  // Parse ports
  console.log('📖 Parsing sea-ports.json...');
  const ports = parseSeaPorts(seaPortsPath);

  // Sort by country, then by name
  ports.sort((a, b) => {
    if (a.country !== b.country) {
      return a.country.localeCompare(b.country);
    }
    return a.name.localeCompare(b.name);
  });

  // Generate migration
  console.log('\n📝 Generating migration file...');
  const migrationContent = generateMigration(ports);

  // Write migration file
  const migrationsDir = path.join(__dirname, '../src/infrastructure/persistence/typeorm/migrations');
  const timestamp = Date.now();
  const fileName = `${timestamp}-SeedPorts.ts`;
  const filePath = path.join(migrationsDir, fileName);

  fs.writeFileSync(filePath, migrationContent, 'utf-8');

  console.log(`\n✅ Migration created: ${fileName}`);
  console.log(`📍 Location: ${filePath}`);
  console.log(`\n📊 Summary:`);
  console.log(`   - Total ports: ${ports.length}`);
  console.log(`   - Countries: ${new Set(ports.map(p => p.country)).size}`);
  console.log(`   - Ports with timezone: ${ports.filter(p => p.timezone).length}`);
  console.log(`\n🚀 Run the migration:`);
  console.log(`   cd apps/backend`);
  console.log(`   npm run migration:run`);
}

main().catch(console.error);