/**
 * seed-dubai-transport — bulk-loads Dubai transport-service masters
 * into the Mellow tenant from the canonical "dubai transport.xlsx"
 * supplied by the client.
 *
 * Source file shape (single sheet, 3 columns):
 *   A — From location / Service descriptor
 *   B — To location (or "-" for non-route services where A already
 *       carries the full descriptor, or duplicates A for InterCity
 *       transfers)
 *   C — Service category (Arrival - PVT | Departure - PVT |
 *       PVT - One Way | PVT - Two Way | PVT - Three Way | PVT)
 *
 * Pricing is NOT in this sheet — only the route/service master list.
 * Pricing rows in transport_pricing are seeded separately when ops
 * provides per-vehicle rates.
 *
 * Usage:
 *   npm run db:seed:dubai-transport                                 # dry run, prints plan
 *   CONFIRM_SEED=YES npm run db:seed:dubai-transport                # actually writes (upsert)
 *   CONFIRM_SEED=YES WIPE_FIRST=YES npm run db:seed:dubai-transport # wipe transport data first, then re-seed clean
 *
 *   SEED_XLSX="/path/to/dubai transport.xlsx"                       # override source
 *   SEED_TENANT=mellow                                              # override tenant
 *
 * WIPE_FIRST removes booking_transport_items, query_quote_transports,
 * supplier_cost_prices (transport rows only), transport_pricing, and
 * transport_services rows for the target tenant before inserting. Use
 * on a fresh server (or to force a clean reseed when the source xlsx
 * changed). Skip on day-to-day re-runs — the seeder is already
 * idempotent. WARNING: wipes any in-flight booking transport items.
 *
 * Category mapping (Excel → entity):
 *   Arrival - PVT     → PICKUP
 *   Departure - PVT   → DROPOFF
 *   PVT - One Way     → ONE_WAY
 *   PVT - Two Way     → TWO_WAY
 *   PVT - Three Way   → TWO_WAY (3-stop round trip; no THREE_WAY enum)
 *   PVT (full/half day tour) → TWO_WAY (round trip from hotel)
 *
 * From/To derivation for B-is-"-" rows:
 *   - "X From Y"       → from=Y, to=X (parses "Burj Khalifa From Deira")
 *   - "X to Y"         → from=X, to=Y (parses "Hotel A to Hotel B InterCity")
 *   - "01 Way Transfer X to Y" / "Inter Hotel X to Y" handled similarly
 *   - fallback         → from="Dubai Hotel", to=A
 *
 * Idempotent: upsert by (tenant_id, from_city, to_city, category).
 * Safe to re-run after edits to the source file — existing rows get
 * their non-key fields updated; new rows inserted.
 *
 * Defaults the seeder applies:
 *   - destination: Dubai (looked up by name)
 *   - is_airport_service: true when category is PICKUP/DROPOFF AND a
 *     known airport keyword appears in either endpoint
 *   - distance_km, duration_mins, gps fields: NULL (source has none)
 *   - tax_slab_id: NULL (inherits destination default)
 */

import { DataSource } from 'typeorm';
import * as dotenv from 'dotenv';
import * as path from 'path';
import * as fs from 'fs';
import * as os from 'os';
import ExcelJS from 'exceljs';
import { deriveUniqueShortCode } from '../src/common/utils/short-code';

dotenv.config();

// ────────────── config ──────────────────────────────────────────────
const TENANT_SUBDOMAIN = process.env.SEED_TENANT || 'mellow';
// Resolve source xlsx. Priority: SEED_XLSX env > bundled
// scripts/seed-data/dubai-transport.xlsx (ships with the repo so
// server deploys are self-contained) > legacy ~/Downloads/ path for
// local dev.
const BUNDLED_XLSX = path.join(__dirname, 'seed-data', 'dubai-transport.xlsx');
const LEGACY_XLSX = path.join(os.homedir(), 'Downloads', 'dubai transport.xlsx');
const SEED_XLSX =
  process.env.SEED_XLSX ||
  (fs.existsSync(BUNDLED_XLSX) ? BUNDLED_XLSX : LEGACY_XLSX);

// Excel category text → entity category enum value.
const CATEGORY_MAP: Record<string, string> = {
  'arrival - pvt': 'PICKUP',
  'departure - pvt': 'DROPOFF',
  'pvt - one way': 'ONE_WAY',
  'pvt - two way': 'TWO_WAY',
  'pvt - three way': 'TWO_WAY',
  pvt: 'TWO_WAY',
};

// Hits any of these in either endpoint → flag the service as airport.
const AIRPORT_KEYWORDS = [
  'dxb airport',
  'sharjah airport',
  'abu dhabi airport',
  'auh',
  'rkt',
  'ras al khaimah airport',
];

// ────────────── GPS coordinate lookup ──────────────────────────────
// Curated map of the recurring Dubai locations in the transport sheet.
// Coordinates are approximate landmark centres (Google Maps-style),
// rounded to 5 decimals (~1 m precision) — plenty for a Movement Chart
// pickup hint. The normalised key matches what normaliseLocation()
// produces from a from_city/to_city descriptor, so filler words like
// "Full Day", "Half Day", "Hotel", "Pickup" never reach the lookup.
// Each entry: GPS centre + optional street address. Landmarks get a
// real address (driver-friendly); hotel-zone descriptors stay address-
// less because they cover dozens of properties (ops fills the specific
// hotel address per booking).
interface LocationEntry {
  lat: number;
  lng: number;
  address?: string;
}
const LOCATION_GPS: Record<string, LocationEntry> = {
  // Airports
  'dxb airport': {
    lat: 25.25278,
    lng: 55.36444,
    address: 'Dubai International Airport, Dubai, UAE',
  },
  'sharjah airport': {
    lat: 25.32867,
    lng: 55.51716,
    address: 'Sharjah International Airport, Sharjah, UAE',
  },
  'abu dhabi airport': {
    lat: 24.43292,
    lng: 54.65111,
    address: 'Zayed International Airport, Abu Dhabi, UAE',
  },
  'ras al khaimah airport': {
    lat: 25.6135,
    lng: 55.93887,
    address: 'Ras Al Khaimah International Airport, RAK, UAE',
  },
  'xnb bus stand dubai': {
    lat: 25.25972,
    lng: 55.30806,
    address: 'Union Bus Station, Al Ittihad Rd, Deira, Dubai, UAE',
  },
  // Hotel areas — no street address, just an area centre.
  'dubai hotel': { lat: 25.21235, lng: 55.27478 },
  'deira bur dubai': { lat: 25.25855, lng: 55.31273 },
  'szr barsha marina': { lat: 25.0938, lng: 55.15683 },
  'abu dhabi hotel': { lat: 24.46667, lng: 54.36667 },
  'jebel ali': { lat: 25.00794, lng: 55.10973, address: 'Jebel Ali, Dubai, UAE' },
  'al karama': { lat: 25.24694, lng: 55.30417, address: 'Al Karama, Dubai, UAE' },
  'bur dubai': { lat: 25.2596, lng: 55.30297, address: 'Bur Dubai, Dubai, UAE' },
  // Landmarks / attractions
  'burj khalifa': {
    lat: 25.1972,
    lng: 55.27444,
    address: '1 Sheikh Mohammed bin Rashid Blvd, Downtown Dubai, UAE',
  },
  'burj al arab': {
    lat: 25.14127,
    lng: 55.18525,
    address: 'Jumeirah St, Umm Suqeim 3, Dubai, UAE',
  },
  'dubai frame': {
    lat: 25.23528,
    lng: 55.30028,
    address: 'Zabeel Park, Gate 4, Sheikh Rashid Rd, Dubai, UAE',
  },
  'atlantis': {
    lat: 25.13083,
    lng: 55.11716,
    address: 'Atlantis The Palm, Crescent Rd, Palm Jumeirah, Dubai, UAE',
  },
  'ski dubai': {
    lat: 25.11803,
    lng: 55.20039,
    address: 'Mall of the Emirates, Sheikh Zayed Rd, Al Barsha 1, Dubai, UAE',
  },
  'img worlds of adventure': {
    lat: 25.06831,
    lng: 55.30714,
    address: 'Sheikh Mohammed Bin Zayed Rd, City of Arabia, Dubai, UAE',
  },
  'global village': {
    lat: 25.06861,
    lng: 55.30694,
    address: 'Sheikh Mohammed Bin Zayed Rd, Dubailand, Dubai, UAE',
  },
  'miracle garden': {
    lat: 25.06175,
    lng: 55.24297,
    address: 'Al Barsha South 3, Dubailand, Dubai, UAE',
  },
  'glow garden dubai': {
    lat: 25.0205,
    lng: 55.21528,
    address: 'Zabeel Park, Gate 6, Dubai, UAE',
  },
  'dubai safari park': {
    lat: 25.16389,
    lng: 55.50194,
    address: 'Al Awir Rd, Dubai, UAE',
  },
  'wild wadi waterpark jumeirah': {
    lat: 25.13278,
    lng: 55.18639,
    address: 'Jumeirah St, Umm Suqeim 3, Dubai, UAE',
  },
  'aquafun dubai': {
    lat: 25.12028,
    lng: 55.11839,
    address: 'Aquaventure Beach, Palm Jumeirah, Dubai, UAE',
  },
  'dubai parks': {
    lat: 24.92806,
    lng: 54.96444,
    address: 'Sheikh Zayed Rd, Jebel Ali, Dubai, UAE',
  },
  'dubai outlet mall': {
    lat: 25.1455,
    lng: 55.41892,
    address: 'Al Ain Rd, Dubai, UAE',
  },
  'dubai outlet village': {
    lat: 24.95028,
    lng: 55.42361,
    address: 'Al Ain Rd, Dubai, UAE',
  },
  'la perle show': {
    lat: 25.24417,
    lng: 55.31694,
    address: 'Al Habtoor City, Sheikh Zayed Rd, Dubai, UAE',
  },
  'museum of the future': {
    lat: 25.22444,
    lng: 55.27944,
    address: 'Sheikh Zayed Rd, Trade Centre 2, Dubai, UAE',
  },
  'dragon mart dubai': {
    lat: 25.17097,
    lng: 55.39633,
    address: 'International City, Al Awir Rd, Dubai, UAE',
  },
  'dubai dolphin': {
    lat: 25.23861,
    lng: 55.30889,
    address: 'Creek Park, Gate 1, Riyadh St, Bur Dubai, UAE',
  },
  'view at palm': {
    lat: 25.11486,
    lng: 55.13903,
    address: 'The Palm Tower, Palm Jumeirah, Dubai, UAE',
  },
  'green planet dubai': {
    lat: 25.20208,
    lng: 55.27317,
    address: 'City Walk, Al Wasl, Dubai, UAE',
  },
  'dhow cruise marina': {
    lat: 25.07881,
    lng: 55.13836,
    address: 'Dubai Marina Promenade, Dubai, UAE',
  },
  'dhow cruise creek deira': {
    lat: 25.26944,
    lng: 55.32083,
    address: 'Dubai Creek, Deira, Dubai, UAE',
  },
  'top golf dubai': {
    lat: 25.04694,
    lng: 55.21833,
    address: 'Emirates Hills 2, Dubai, UAE',
  },
  'aya universe': {
    lat: 25.24528,
    lng: 55.31611,
    address: 'WAFI Mall, Sheikh Rashid Rd, Bur Dubai, UAE',
  },
  'sky views edge walk': {
    lat: 25.18567,
    lng: 55.26694,
    address: 'Address Sky View, Sheikh Mohammed bin Rashid Blvd, Dubai, UAE',
  },
  'aura sky pool': {
    lat: 25.11617,
    lng: 55.13886,
    address: 'The Palm Tower, Palm Jumeirah, Dubai, UAE',
  },
  'hot air baloon dubai': {
    lat: 25.0,
    lng: 55.4,
    address: 'Margham Desert, Dubai, UAE',
  },
  'sky diving': {
    lat: 25.09028,
    lng: 55.13639,
    address: 'Skydive Dubai, Palm Drop Zone, Dubai Marina, UAE',
  },
  'yacht': {
    lat: 25.08111,
    lng: 55.13889,
    address: 'Dubai Marina Yacht Club, Dubai, UAE',
  },
  'dubai ain': {
    lat: 25.0786,
    lng: 55.13083,
    address: 'Ain Dubai, Bluewaters Island, Dubai, UAE',
  },
  'abu dhabi city': { lat: 24.46667, lng: 54.36667, address: 'Abu Dhabi, UAE' },
  'dubai': { lat: 25.27654, lng: 55.29621, address: 'Dubai, UAE' },
  'sharjah': { lat: 25.34624, lng: 55.42062, address: 'Sharjah, UAE' },
  'ras al khaimah': { lat: 25.78953, lng: 55.94324, address: 'Ras Al Khaimah, UAE' },
};

// Words to strip before the GPS lookup. They never carry a coordinate —
// they're durations ("full day"), context tags ("from", "to"), or
// generic suffixes ("ticket", "tour", "city tour").
const FILLER_PATTERNS = [
  /\bfull\s*day\b/g,
  /\bhalf\s*day\b/g,
  /\bcity\s*tour\b/g,
  /\bcoupon\s*only\b/g,
  /\bpickup\b/g,
  /\bdropoff\b/g,
  /\bdrop[\s-]?off\b/g,
  /\bdrop\b/g,
  /\bfrom\b/g,
  /\bto\b/g,
  /\bthe\b/g,
  /\btickets?\b/g,
  /\btours?\b/g,
  /\btransfer\b/g,
  /\bone\s*way\b/g,
  /\btwo\s*way\b/g,
  /\b\d+\s*way\b/g,
  /\b01\s*way\b/g,
  /\bdinner\b/g,
  /\blunch\b/g,
  /\bbreakfast\b/g,
];

function normaliseLocation(raw: string | null | undefined): string {
  let s = (raw || '').toLowerCase();
  s = s.replace(/[(),\/–—]/g, ' ');
  for (const re of FILLER_PATTERNS) s = s.replace(re, ' ');
  s = s.replace(/[^a-z0-9\s]/g, ' ');
  s = s.replace(/\s+/g, ' ').trim();
  return s;
}

function resolveLocation(raw: string | null | undefined): LocationEntry | null {
  const norm = normaliseLocation(raw);
  if (!norm) return null;
  if (LOCATION_GPS[norm]) return LOCATION_GPS[norm];
  // Substring fallback — pick the longest entry whose key is contained
  // in the normalised descriptor. "1 day 2 yas island park pass" →
  // "yas island"; "ras al khaimah airport rkt" → "ras al khaimah airport".
  let best: { key: string; entry: LocationEntry } | null = null;
  for (const [key, entry] of Object.entries(LOCATION_GPS)) {
    if (norm.includes(key) && (!best || key.length > best.key.length)) {
      best = { key, entry };
    }
  }
  return best?.entry ?? null;
}

// ────────────── parsed row shape ────────────────────────────────────
interface ParsedRow {
  rowIndex: number;
  rawA: string;
  rawB: string;
  rawCategory: string;
  category: string; // mapped enum
  from_city: string;
  to_city: string;
  is_airport_service: boolean;
}

// ────────────── helpers ─────────────────────────────────────────────

function cellText(cell: ExcelJS.Cell): string {
  const v = cell.value;
  if (v == null) return '';
  if (typeof v === 'object' && 'richText' in v) {
    return (v as any).richText.map((r: any) => r.text).join('').trim();
  }
  if (typeof v === 'object' && 'result' in v) {
    return String((v as any).result).trim();
  }
  return String(v).trim();
}

/** Normalise whitespace, strip trailing punctuation. */
function norm(s: string): string {
  return s.replace(/\s+/g, ' ').replace(/[\s\-–—:,.]+$/g, '').trim();
}

function isPlaceholderTo(s: string): boolean {
  const t = s.trim();
  return t === '' || t === '-' || t === '–' || t === '—';
}

/** Pick a sensible from/to pair for non-route rows where B is "-" or
 *  duplicates A. Strategy: parse "X From Y" → from=Y, to=X; parse
 *  "X to Y" → from=X, to=Y; else default to a hotel pickup.
 *  Tries to keep both fields under 100 chars (the column limit). */
function deriveRoute(
  rawA: string,
  rawB: string,
): { from_city: string; to_city: string } {
  const a = norm(rawA);
  // "X From Y" / "X From  Y" — common pattern: attraction sourced FROM
  // a specific hotel area.
  const fromMatch = a.match(/^(.+?)\s+From\s+(.+)$/i);
  if (fromMatch) {
    return { from_city: clampLoc(fromMatch[2]), to_city: clampLoc(fromMatch[1]) };
  }
  // "X to Y" — usually InterCity or specific A→B route encoded in one
  // descriptor, e.g. "Dubai Hotel to Abu Dhabi Hotel InterCity Transfer".
  const toMatch = a.match(/^(.+?)\s+to\s+(.+)$/i);
  if (toMatch) {
    return { from_city: clampLoc(toMatch[1]), to_city: clampLoc(toMatch[2]) };
  }
  // B has a real value distinct from A and isn't a placeholder.
  if (!isPlaceholderTo(rawB) && norm(rawB) !== a) {
    return { from_city: clampLoc(a), to_city: clampLoc(rawB) };
  }
  // Fallback: hotel pickup, full descriptor as destination.
  return { from_city: 'Dubai Hotel', to_city: clampLoc(a) };
}

function clampLoc(s: string): string {
  const t = norm(s);
  return t.length > 100 ? t.slice(0, 97) + '...' : t;
}

function isAirportService(category: string, from: string, to: string): boolean {
  if (category !== 'PICKUP' && category !== 'DROPOFF') return false;
  const fl = from.toLowerCase();
  const tl = to.toLowerCase();
  return AIRPORT_KEYWORDS.some((k) => fl.includes(k) || tl.includes(k));
}

// ────────────── parse ────────────────────────────────────────────────

async function parseExcel(filePath: string): Promise<ParsedRow[]> {
  const wb = new ExcelJS.Workbook();
  await wb.xlsx.readFile(filePath);
  const ws = wb.worksheets[0];
  const out: ParsedRow[] = [];
  ws.eachRow((row, i) => {
    if (i === 1) return; // header
    const a = cellText(row.getCell('A'));
    const b = cellText(row.getCell('B'));
    const c = cellText(row.getCell('C'));
    if (!a || !c) return;
    const catKey = c.toLowerCase().replace(/\s+/g, ' ').trim();
    const category = CATEGORY_MAP[catKey];
    if (!category) {
      console.warn(`  Row ${i}: unknown category ${JSON.stringify(c)} — skipped.`);
      return;
    }
    let from_city: string;
    let to_city: string;
    if (!isPlaceholderTo(b) && norm(b) !== norm(a)) {
      // Direct route — A is a real location, B is a different real location.
      from_city = clampLoc(a);
      to_city = clampLoc(b);
    } else {
      // Non-route or duplicate-of-A row — derive from A alone.
      const r = deriveRoute(a, b);
      from_city = r.from_city;
      to_city = r.to_city;
    }
    out.push({
      rowIndex: i,
      rawA: a,
      rawB: b,
      rawCategory: c,
      category,
      from_city,
      to_city,
      is_airport_service: isAirportService(category, from_city, to_city),
    });
  });
  return out;
}

// ────────────── DB connect ──────────────────────────────────────────

async function buildDataSource(): Promise<DataSource> {
  const ds = new DataSource({
    type: 'postgres',
    host: process.env.DB_HOST || 'localhost',
    port: parseInt(process.env.DB_PORT || '5432', 10),
    username: process.env.DB_USERNAME || 'postgres',
    password: process.env.DB_PASSWORD || '',
    database: process.env.DB_NAME || 'dmc_ops',
    entities: [],
    logging: false,
  });
  await ds.initialize();
  return ds;
}

async function resolveTenantId(ds: DataSource): Promise<string> {
  // `tenants` is an exception in the schema — no soft-delete column.
  const rows = await ds.query(
    `SELECT id FROM tenants WHERE subdomain = $1 LIMIT 1`,
    [TENANT_SUBDOMAIN],
  );
  if (!rows.length) {
    throw new Error(`Tenant "${TENANT_SUBDOMAIN}" not found.`);
  }
  return rows[0].id;
}

async function resolveDubaiDestinationId(
  ds: DataSource,
  tenantId: string,
): Promise<string> {
  const rows = await ds.query(
    `SELECT id FROM destinations WHERE tenant_id = $1 AND LOWER(name) = 'dubai' AND is_deleted = false LIMIT 1`,
    [tenantId],
  );
  if (!rows.length) {
    throw new Error(`Dubai destination not found for tenant ${TENANT_SUBDOMAIN}.`);
  }
  return rows[0].id;
}

// ────────────── upsert ───────────────────────────────────────────────

async function upsertTransport(
  ds: DataSource,
  tenantId: string,
  destinationId: string,
  row: ParsedRow,
): Promise<'inserted' | 'updated'> {
  const existing = await ds.query(
    `SELECT id, short_code FROM transport_services
       WHERE tenant_id = $1
         AND destination_id = $2
         AND from_city = $3
         AND to_city = $4
         AND category = $5
         AND is_deleted = false
       LIMIT 1`,
    [tenantId, destinationId, row.from_city, row.to_city, row.category],
  );
  // Curated lookup — yields GPS + (optional) street address. NULL when
  // no match; ops fills via UI. Landmark entries carry an address;
  // hotel-zone descriptors (e.g. "Deira/Bur Dubai") have GPS only
  // because they cover dozens of properties — the specific hotel
  // address belongs on the booking row, not the master.
  const fromLoc = resolveLocation(row.from_city);
  const toLoc = resolveLocation(row.to_city);

  if (existing.length) {
    const { id } = existing[0];
    // Fill GPS + address only when the existing row is still missing
    // them — don't clobber ops-typed values on re-run.
    await ds.query(
      `UPDATE transport_services
          SET is_airport_service = $1,
              from_gps_latitude  = COALESCE(from_gps_latitude,  $3::text),
              from_gps_longitude = COALESCE(from_gps_longitude, $4::text),
              to_gps_latitude    = COALESCE(to_gps_latitude,    $5::text),
              to_gps_longitude   = COALESCE(to_gps_longitude,   $6::text),
              from_address       = COALESCE(from_address,       $7),
              to_address         = COALESCE(to_address,         $8),
              updated_at = CURRENT_TIMESTAMP
        WHERE id = $2`,
      [
        row.is_airport_service,
        id,
        fromLoc ? String(fromLoc.lat) : null,
        fromLoc ? String(fromLoc.lng) : null,
        toLoc ? String(toLoc.lat) : null,
        toLoc ? String(toLoc.lng) : null,
        fromLoc?.address ?? null,
        toLoc?.address ?? null,
      ],
    );
    return 'updated';
  }
  // Derive short_code from the descriptor (rawA carries the most
  // distinctive text). Falls back to "TR" + suffix if all-symbol.
  const codeSource = `${row.from_city} ${row.to_city} ${row.category}`;
  const shortCode = await deriveUniqueShortCode(codeSource, 'TR', async (cand) => {
    const r = await ds.query(
      `SELECT 1 FROM transport_services WHERE tenant_id = $1 AND short_code = $2 AND is_deleted = false LIMIT 1`,
      [tenantId, cand],
    );
    return r.length > 0;
  });
  const inserted = await ds.query(
    `INSERT INTO transport_services (
       tenant_id, destination_id, short_code, category,
       from_city, to_city,
       from_address, to_address,
       from_gps_latitude, from_gps_longitude,
       to_gps_latitude, to_gps_longitude,
       is_airport_service, is_active,
       created_by, updated_by, created_at, updated_at, is_deleted
     ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, true, NULL, NULL, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, false)
     RETURNING id`,
    [
      tenantId,
      destinationId,
      shortCode,
      row.category,
      row.from_city,
      row.to_city,
      fromLoc?.address ?? null,
      toLoc?.address ?? null,
      fromLoc ? String(fromLoc.lat) : null,
      fromLoc ? String(fromLoc.lng) : null,
      toLoc ? String(toLoc.lat) : null,
      toLoc ? String(toLoc.lng) : null,
      row.is_airport_service,
    ],
  );
  await syncPricingMatrix(
    ds,
    tenantId,
    inserted[0].id,
    destinationId,
    row.category,
  );
  return 'inserted';
}

/**
 * Mirror of TransportServiceService.syncPricingMatrix — scaffold one
 * transport_pricing row per (active vehicle type) in PVT mode + one SIC
 * row (vehicle_type_id NULL). Prices stay NULL until ops fills them
 * via the Pricing screen.
 *
 * Idempotent: skips inserts where a row already exists for the natural
 * key. Safe to call on every seeder run.
 */
async function syncPricingMatrix(
  ds: DataSource,
  tenantId: string,
  transportServiceId: string,
  destinationId: string,
  category: string,
): Promise<void> {
  const destRows = await ds.query(
    `SELECT currency_id FROM destinations WHERE id = $1 LIMIT 1`,
    [destinationId],
  );
  const currencyId = destRows[0]?.currency_id;
  if (!currencyId) return;

  // PVT rows — one per active vehicle type.
  await ds.query(
    `INSERT INTO transport_pricing (
       id, tenant_id, destination_id, transport_service_id, category,
       vehicle_type_id, mode, currency_id,
       is_deleted, created_at, updated_at
     )
     SELECT
       uuid_generate_v4(), $1, $2, $3, $4,
       vt.id, 'PVT', $5,
       false, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
     FROM vehicle_types vt
     WHERE vt.tenant_id = $1
       AND vt.is_deleted = false
       AND vt.is_active = true
       AND NOT EXISTS (
         SELECT 1 FROM transport_pricing tp
          WHERE tp.tenant_id = $1
            AND tp.transport_service_id = $3
            AND tp.vehicle_type_id = vt.id
            AND tp.mode = 'PVT'
            AND tp.currency_id = $5
            AND tp.is_deleted = false
       )`,
    [tenantId, destinationId, transportServiceId, category, currencyId],
  );

  // SIC row — exactly one per service (vehicle_type_id NULL).
  await ds.query(
    `INSERT INTO transport_pricing (
       id, tenant_id, destination_id, transport_service_id, category,
       vehicle_type_id, mode, currency_id,
       is_deleted, created_at, updated_at
     )
     SELECT
       uuid_generate_v4(), $1, $2, $3, $4,
       NULL, 'SIC', $5,
       false, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
     WHERE NOT EXISTS (
       SELECT 1 FROM transport_pricing tp
        WHERE tp.tenant_id = $1
          AND tp.transport_service_id = $3
          AND tp.vehicle_type_id IS NULL
          AND tp.mode = 'SIC'
          AND tp.currency_id = $5
          AND tp.is_deleted = false
     )`,
    [tenantId, destinationId, transportServiceId, category, currencyId],
  );
}

// ────────────── main ────────────────────────────────────────────────

async function main() {
  const dryRun = process.env.CONFIRM_SEED !== 'YES';
  console.log(`Source: ${SEED_XLSX}`);
  console.log(`Target tenant: ${TENANT_SUBDOMAIN}`);
  console.log(`Mode: ${dryRun ? 'DRY RUN (no writes)' : 'WRITE'}\n`);

  const parsed = await parseExcel(SEED_XLSX);
  console.log(`Parsed: ${parsed.length} transport service row(s).`);

  const byCat: Record<string, number> = {};
  for (const r of parsed) byCat[r.category] = (byCat[r.category] || 0) + 1;
  console.log('\nBy category:');
  for (const [c, n] of Object.entries(byCat).sort((a, b) => b[1] - a[1])) {
    console.log(`  ${String(n).padStart(3)}  ${c}`);
  }

  const airportN = parsed.filter((r) => r.is_airport_service).length;
  console.log(`\nAirport-flagged: ${airportN} row(s).`);

  if (dryRun) {
    const focus = process.env.SEED_FOCUS;
    const entries = focus
      ? parsed.filter(
          (r) =>
            r.rawA.toLowerCase().includes(focus.toLowerCase()) ||
            r.from_city.toLowerCase().includes(focus.toLowerCase()) ||
            r.to_city.toLowerCase().includes(focus.toLowerCase()),
        )
      : parsed.slice(0, 12);
    console.log(
      focus
        ? `\nMatching rows (filter: "${focus}"):`
        : '\nSample rows (first 12):',
    );
    for (const r of entries) {
      console.log(
        `  [${r.category}${r.is_airport_service ? ' AIR' : ''}] ${r.from_city} → ${r.to_city}`,
      );
    }
    console.log('\nRe-run with CONFIRM_SEED=YES to write.');
    process.exit(0);
  }

  const ds = await buildDataSource();
  console.log('→ connected.');
  const tenantId = await resolveTenantId(ds);
  const destinationId = await resolveDubaiDestinationId(ds, tenantId);

  if (process.env.WIPE_FIRST === 'YES') {
    // Hard-delete in FK order. booking_flight_details → booking_transport_items
    // + query_quote_transports → supplier_cost_prices (transport rows)
    // → transport_pricing → transport_services. All tenant-scoped.
    console.log('WIPE_FIRST=YES — clearing transport data for tenant…');
    await ds.query(
      `DELETE FROM booking_flight_details WHERE tenant_id = $1`,
      [tenantId],
    );
    const r1 = await ds.query(
      `DELETE FROM booking_transport_items WHERE tenant_id = $1`,
      [tenantId],
    );
    const r2 = await ds.query(
      `DELETE FROM query_quote_transports WHERE tenant_id = $1`,
      [tenantId],
    );
    const r3 = await ds.query(
      `DELETE FROM supplier_cost_prices WHERE tenant_id = $1 AND transport_service_id IS NOT NULL`,
      [tenantId],
    );
    const r4 = await ds.query(
      `DELETE FROM transport_pricing WHERE tenant_id = $1`,
      [tenantId],
    );
    const r5 = await ds.query(
      `DELETE FROM transport_services WHERE tenant_id = $1`,
      [tenantId],
    );
    console.log(
      `  wiped: ${r5[1] ?? 0} transport_services, ${r4[1] ?? 0} pricing, ${r3[1] ?? 0} supplier_cost, ${r2[1] ?? 0} quote_transports, ${r1[1] ?? 0} booking_transport_items`,
    );
  }

  let inserted = 0;
  let updated = 0;
  for (const row of parsed) {
    try {
      const r = await upsertTransport(ds, tenantId, destinationId, row);
      if (r === 'inserted') inserted++;
      else updated++;
    } catch (err) {
      console.error(`  Row ${row.rowIndex} failed:`, (err as Error).message);
    }
  }

  // Final pass — scan EVERY transport_services row in this destination
  // (not just the Excel ones) and try resolveLocation on the from_city
  // and to_city. Catches hand-created routes from the FE — disposal
  // services, ad-hoc routes ops typed — that the Excel upsert never
  // touches. COALESCE keeps any address / GPS ops already typed.
  let backfilled = 0;
  const allRows: { id: string; from_city: string; to_city: string }[] =
    await ds.query(
      `SELECT id, from_city, to_city FROM transport_services
        WHERE tenant_id = $1 AND destination_id = $2 AND is_deleted = false
          AND (from_address IS NULL OR to_address IS NULL
               OR from_gps_latitude IS NULL OR to_gps_latitude IS NULL)`,
      [tenantId, destinationId],
    );
  for (const r of allRows) {
    const fromLoc = resolveLocation(r.from_city);
    const toLoc = resolveLocation(r.to_city);
    if (!fromLoc && !toLoc) continue;
    const result = await ds.query(
      `UPDATE transport_services
          SET from_gps_latitude  = COALESCE(from_gps_latitude,  $2::text),
              from_gps_longitude = COALESCE(from_gps_longitude, $3::text),
              to_gps_latitude    = COALESCE(to_gps_latitude,    $4::text),
              to_gps_longitude   = COALESCE(to_gps_longitude,   $5::text),
              from_address       = COALESCE(from_address,       $6),
              to_address         = COALESCE(to_address,         $7),
              updated_at = CURRENT_TIMESTAMP
        WHERE id = $1`,
      [
        r.id,
        fromLoc ? String(fromLoc.lat) : null,
        fromLoc ? String(fromLoc.lng) : null,
        toLoc ? String(toLoc.lat) : null,
        toLoc ? String(toLoc.lng) : null,
        fromLoc?.address ?? null,
        toLoc?.address ?? null,
      ],
    );
    backfilled += result[1] ?? 0;
  }
  await ds.destroy();
  console.log(`\n✓ ${inserted} inserted, ${updated} updated, ${backfilled} address/GPS backfilled.`);
}

main().catch((err) => {
  console.error('Seeder failed:', err);
  process.exit(1);
});