/** * Transport seeder — reset + seed transport_services and blank-price * transport_pricing rows for one tenant (or all) and one destination * (or all destinations with vehicle-type mappings). * * Pricing rows are fanned out per the destination's mapped vehicle types * (destination_vehicle_types). Each route gets: * • one PVT row per mapped vehicle type * • one SIC row (vehicle type = NULL, by data-model convention) * All price columns are left NULL — ops will fill them via the UI or * the Transport Pricing Excel import. * * USAGE — local dev * npx ts-node src/cli/seed-transport.ts --tenant=mellow --reset * npx ts-node src/cli/seed-transport.ts --all-tenants --reset * npx ts-node src/cli/seed-transport.ts --tenant=mellow --destination=Dubai * npx ts-node src/cli/seed-transport.ts --tenant=mellow --reset --dry-run * * USAGE — server (after `npm run build`) * node dist/cli/seed-transport.js --tenant= --reset * * Or via npm script (added in package.json): * npm run seed:transport -- --tenant=mellow --reset * * Flags * --tenant= run for a single tenant * --all-tenants run for every active tenant * --destination= limit to a single destination by name * --reset HARD-DELETE transport_services + transport_pricing * + supplier_cost_prices (transport rows only) * + booking_transport_items + query_quote_transports * BEFORE seeding. Without this flag, the script only * seeds NEW routes that don't already exist by * short_code. * --dry-run report what would happen, write nothing * * Safety * • Single transaction per tenant — failures roll back cleanly. * • --reset cascades through child references — bookings/quotes that * used transport rows will lose those line items. The booking / * quote header rows themselves survive. * • Skips destinations that have zero cab-type mappings in * destination_vehicle_types (logged + skipped, not errored). * • Skips destinations with NULL currency_id (transport_pricing * requires a currency). */ import { DataSource } from 'typeorm'; import * as dotenv from 'dotenv'; dotenv.config(); // ───────────────────────────────────────────────────────────────────────── // Configuration — the generic starter pack of routes to seed per dest. // Edit / extend this list if your tenants need a different default catalog. // short_code is prefixed with the destination's slug at seed time so the // same template fans out cleanly across destinations without collisions. // ───────────────────────────────────────────────────────────────────────── interface RouteTemplate { short_code_suffix: string; category: 'PICKUP' | 'DROPOFF' | 'ONE_WAY' | 'TWO_WAY' | 'HALF_DAY' | 'FULL_DAY'; from_city: string; to_city: string; from_address: string; to_address: string; distance_km: number; duration_mins: number; description: string; } const STARTER_ROUTES: RouteTemplate[] = [ { short_code_suffix: 'AIRPORT-CITY-PU', category: 'PICKUP', from_city: 'Airport', to_city: 'City Center', from_address: 'Arrival Terminal', to_address: 'Downtown hotels', distance_km: 20, duration_mins: 35, description: 'Airport arrival transfer to city center', }, { short_code_suffix: 'CITY-AIRPORT-DO', category: 'DROPOFF', from_city: 'City Center', to_city: 'Airport', from_address: 'Downtown hotels', to_address: 'Departure Terminal', distance_km: 20, duration_mins: 35, description: 'City to airport departure transfer', }, { short_code_suffix: 'CITY-TOUR-HD', category: 'HALF_DAY', from_city: 'Hotel pickup', to_city: 'City Tour', from_address: 'Guest hotel', to_address: 'City highlights', distance_km: 30, duration_mins: 240, description: 'Half-day city sightseeing tour', }, { short_code_suffix: 'DAY-TRIP-FD', category: 'FULL_DAY', from_city: 'Hotel pickup', to_city: 'Day Trip Destination', from_address: 'Guest hotel', to_address: 'Day trip area', distance_km: 80, duration_mins: 480, description: 'Full-day excursion', }, { short_code_suffix: 'HOTEL-HOTEL-1W', category: 'ONE_WAY', from_city: 'Hotel A', to_city: 'Hotel B', from_address: 'Origin hotel', to_address: 'Destination hotel', distance_km: 10, duration_mins: 20, description: 'Inter-hotel transfer', }, { short_code_suffix: 'SHOPPING-MALL-2W', category: 'TWO_WAY', from_city: 'Hotel pickup', to_city: 'Shopping Mall', from_address: 'Guest hotel', to_address: 'Mall entrance', distance_km: 15, duration_mins: 60, description: 'Shopping trip', }, ]; // ───────────────────────────────────────────────────────────────────────── // CLI parsing // ───────────────────────────────────────────────────────────────────────── interface CliOpts { tenant?: string; allTenants: boolean; destination?: string; reset: boolean; dryRun: boolean; } function parseArgs(argv: string[]): CliOpts { const opts: CliOpts = { allTenants: false, reset: false, dryRun: false }; for (const arg of argv) { if (arg.startsWith('--tenant=')) opts.tenant = arg.slice('--tenant='.length); else if (arg === '--all-tenants') opts.allTenants = true; else if (arg.startsWith('--destination=')) opts.destination = arg.slice('--destination='.length); else if (arg === '--reset') opts.reset = true; else if (arg === '--dry-run') opts.dryRun = true; else if (arg === '--help' || arg === '-h') { console.log(usage()); process.exit(0); } else if (arg.startsWith('--')) { console.error(`Unknown flag: ${arg}`); console.error(usage()); process.exit(1); } } if (!opts.tenant && !opts.allTenants) { console.error('Error: pass --tenant= or --all-tenants'); console.error(usage()); process.exit(1); } if (opts.tenant && opts.allTenants) { console.error('Error: --tenant and --all-tenants are mutually exclusive'); process.exit(1); } return opts; } function usage(): string { return ` Transport seeder Required (one of): --tenant= seed for a single tenant --all-tenants seed for every active tenant Optional: --destination= limit to a single destination --reset HARD-DELETE existing transport routes + pricing first --dry-run show what would happen, write nothing --help, -h this message Examples: npx ts-node src/cli/seed-transport.ts --tenant=mellow --reset npx ts-node src/cli/seed-transport.ts --all-tenants --reset npx ts-node src/cli/seed-transport.ts --tenant=mellow --destination=Dubai --reset npm run seed:transport -- --tenant=mellow --reset `; } // ───────────────────────────────────────────────────────────────────────── // Helpers // ───────────────────────────────────────────────────────────────────────── function slug(name: string): string { // Strip diacritics via NFD decomposition + unicode property class for // combining marks (\p{M}); fall back to a plain ASCII filter. Then // collapse non-alphanumerics to dashes and trim leading/trailing dashes. return name .toUpperCase() .normalize('NFD') .replace(/\p{M}+/gu, '') .replace(/[^A-Z0-9]+/g, '-') .replace(/^-+|-+$/g, '') .slice(0, 20); } interface DestRow { destination_id: string; destination_name: string; currency_id: string | null; // Postgres' pg driver returns COUNT(*) as a string (bigint-safe). Accept // either shape and normalise at the use site. cab_count: number | string; } // ───────────────────────────────────────────────────────────────────────── // Main // ───────────────────────────────────────────────────────────────────────── async function main(): Promise { const opts = parseArgs(process.argv.slice(2)); console.log( `\n▶ Transport seeder — ${opts.dryRun ? 'DRY RUN' : 'LIVE'}` + `${opts.reset ? ' (will RESET existing transport data)' : ' (skip-on-duplicate mode)'}\n`, ); 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', }); await ds.initialize(); console.log('✓ Connected to database'); try { // Resolve target tenants type TenantRow = { id: string; subdomain: string }; const tenantsQ = opts.allTenants ? `SELECT id, subdomain FROM tenants ORDER BY subdomain` : `SELECT id, subdomain FROM tenants WHERE subdomain = $1`; const tenants: TenantRow[] = opts.allTenants ? await ds.query(tenantsQ) : await ds.query(tenantsQ, [opts.tenant!]); if (tenants.length === 0) { console.error(`✗ No tenant found (filter: ${opts.tenant || 'all'})`); process.exit(1); } console.log( `✓ Found ${tenants.length} tenant(s): ${tenants.map((t) => t.subdomain).join(', ')}\n`, ); let totalServices = 0; let totalPricingRows = 0; let totalResetRows = 0; const skippedDestinations: string[] = []; for (const tenant of tenants) { console.log(`\n━━━ Tenant: ${tenant.subdomain} ━━━`); // Find destinations with cab-type mappings. The destination-name // filter is parameterised with a "match-all when null" predicate // so the query string is fully static (Sonar S3649: avoid // concatenating SQL fragments even when the fragment is hard-coded). const destinationFilter = opts.destination ?? null; const destinations: DestRow[] = await ds.query( ` SELECT d.id AS destination_id, d.name AS destination_name, d.currency_id, COUNT(dvt.id) FILTER (WHERE dvt.is_deleted = FALSE) AS cab_count FROM destinations d LEFT JOIN destination_vehicle_types dvt ON dvt.destination_id = d.id WHERE d.tenant_id = $1 AND d.is_deleted = FALSE AND ($2::text IS NULL OR d.name = $2) GROUP BY d.id, d.name, d.currency_id ORDER BY d.name `, [tenant.id, destinationFilter], ); if (destinations.length === 0) { console.log( ` (no destinations${opts.destination ? ` matching "${opts.destination}"` : ''})`, ); continue; } // RESET pass — applied tenant-wide once, before seeding starts. // Done in a single transaction so a failure aborts cleanly. if (opts.reset) { const runner = ds.createQueryRunner(); await runner.connect(); await runner.startTransaction(); try { // Order: children → pricing → services. Scoped to the whole tenant // (not narrowed to the destination list) — re-running with a // destination filter still wipes the entire tenant's transport // data because partial scoping risks orphaning child rows. const r1 = await runner.query( `DELETE FROM booking_transport_items WHERE tenant_id = $1 AND transport_service_id IN (SELECT id FROM transport_services WHERE tenant_id = $1)`, [tenant.id], ); const r2 = await runner.query( `DELETE FROM query_quote_transports WHERE tenant_id = $1 AND transport_service_id IN (SELECT id FROM transport_services WHERE tenant_id = $1)`, [tenant.id], ); const r3 = await runner.query( `DELETE FROM supplier_cost_prices WHERE tenant_id = $1 AND transport_service_id IS NOT NULL`, [tenant.id], ); const r4 = await runner.query(`DELETE FROM transport_pricing WHERE tenant_id = $1`, [ tenant.id, ]); const r5 = await runner.query(`DELETE FROM transport_services WHERE tenant_id = $1`, [ tenant.id, ]); const deletedCount = ((r1[1] as number) || 0) + ((r2[1] as number) || 0) + ((r3[1] as number) || 0) + ((r4[1] as number) || 0) + ((r5[1] as number) || 0); totalResetRows += deletedCount; // Pull rowCount from each query result tuple (TypeORM raw query // returns [rows, count] for DELETE/UPDATE on Postgres). console.log( ` Reset: deleted booking_transport_items=${r1[1] ?? 0}` + ` query_quote_transports=${r2[1] ?? 0}` + ` supplier_cost_prices=${r3[1] ?? 0}` + ` transport_pricing=${r4[1] ?? 0}` + ` transport_services=${r5[1] ?? 0}`, ); if (opts.dryRun) { await runner.rollbackTransaction(); console.log(' (dry-run — RESET rolled back)'); } else { await runner.commitTransaction(); } } catch (err) { await runner.rollbackTransaction(); throw err; } finally { await runner.release(); } } // SEED pass — one transaction per destination so partial successes are committed. for (const dest of destinations) { if (Number(dest.cab_count) === 0) { skippedDestinations.push( `${tenant.subdomain}/${dest.destination_name} (no cab mappings)`, ); console.log( ` ⊘ ${dest.destination_name}: no destination_vehicle_types mapping — skipped`, ); continue; } if (!dest.currency_id) { skippedDestinations.push(`${tenant.subdomain}/${dest.destination_name} (no currency)`); console.log(` ⊘ ${dest.destination_name}: NULL currency_id — skipped`); continue; } const slugPrefix = slug(dest.destination_name); const runner = ds.createQueryRunner(); await runner.connect(); await runner.startTransaction(); try { let seededHere = 0; let pricingHere = 0; for (const route of STARTER_ROUTES) { const shortCode = `${slugPrefix}-${route.short_code_suffix}`; // Skip if a service with this short_code already exists (when not --reset) if (!opts.reset) { const exists = await runner.query( `SELECT 1 FROM transport_services WHERE tenant_id = $1 AND destination_id = $2 AND short_code = $3 AND is_deleted = FALSE`, [tenant.id, dest.destination_id, shortCode], ); if (exists.length > 0) continue; // skip — already there } // Insert transport_service const [{ id: tsId }] = await runner.query( `INSERT INTO transport_services (tenant_id, destination_id, short_code, category, from_city, to_city, from_address, to_address, distance_km, duration_mins, description, is_active) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, TRUE) RETURNING id`, [ tenant.id, dest.destination_id, shortCode, route.category, route.from_city, route.to_city, route.from_address, route.to_address, route.distance_km, route.duration_mins, route.description, ], ); seededHere++; // Fan out PVT pricing per mapped vehicle type. `RETURNING id` so // we can count the inserted rows reliably across pg driver // versions (TypeORM's raw query() doesn't expose rowCount // uniformly for bulk INSERT...SELECT). const pvtRows: Array<{ id: string }> = await runner.query( `INSERT INTO transport_pricing (tenant_id, destination_id, transport_service_id, category, currency_id, mode, vehicle_type_id) SELECT $1, $2, $3, $4, $5, 'PVT', dvt.vehicle_type_id FROM destination_vehicle_types dvt WHERE dvt.tenant_id = $1 AND dvt.destination_id = $2 AND dvt.is_deleted = FALSE RETURNING id`, [tenant.id, dest.destination_id, tsId, route.category, dest.currency_id], ); pricingHere += pvtRows.length; // SIC row (one per route, NULL vehicle type) await runner.query( `INSERT INTO transport_pricing (tenant_id, destination_id, transport_service_id, category, currency_id, mode, vehicle_type_id) VALUES ($1, $2, $3, $4, $5, 'SIC', NULL)`, [tenant.id, dest.destination_id, tsId, route.category, dest.currency_id], ); pricingHere++; } if (opts.dryRun) { await runner.rollbackTransaction(); console.log( ` · ${dest.destination_name}: would seed ${seededHere} services + ${pricingHere} pricing rows (rolled back)`, ); } else { await runner.commitTransaction(); console.log( ` ✓ ${dest.destination_name}: ${seededHere} services + ${pricingHere} pricing rows`, ); } totalServices += seededHere; totalPricingRows += pricingHere; } catch (err) { await runner.rollbackTransaction(); console.error(` ✗ ${dest.destination_name}: FAILED —`, err); } finally { await runner.release(); } } } console.log('\n━━━ Summary ━━━'); if (opts.reset) console.log(` Deleted (across tenants): ~${totalResetRows} rows`); console.log(` Seeded transport_services: ${totalServices}`); console.log(` Seeded transport_pricing: ${totalPricingRows}`); if (skippedDestinations.length) { console.log(` Skipped destinations: ${skippedDestinations.length}`); for (const s of skippedDestinations) console.log(` - ${s}`); } console.log(opts.dryRun ? ' (DRY RUN — nothing committed)' : ' ✓ Done.\n'); } finally { await ds.destroy(); } } main().catch((err) => { console.error('\n✗ Seeder failed:', err); process.exit(1); });