/**
 * seed-dubai-dmc — populates the Mellow tenant with a realistic
 * Dubai DMC dataset: masters + 6 bookings spread across 8 days so the
 * Movement Dashboard shows rich grouped data.
 *
 * Usage:
 *   npm run db:seed:dubai                  # preview (safe; no writes)
 *   CONFIRM_SEED=YES npm run db:seed:dubai # actually seed
 *
 * Design:
 *  - Today = 2026-04-24 (adjust DAY_TODAY below to shift the window)
 *  - 6 bookings overlapping around today so the Dashboard's today
 *    view has arrivals, departures, excursions, and activities.
 *  - Transport rows share routes → groups form naturally:
 *      • SIC DXB Airport → Dubai Hotels  (multiple arrivals today)
 *      • PVT Hotels → Desert Safari (3 bookings doing the same run)
 *      • PVT Hotels → DXB Airport (drop-offs)
 *  - Activities repeat across bookings → Activity tab groups also
 *    form naturally.
 *
 * Idempotent: each master is upserted by natural key (short_code /
 * name); bookings are re-created fresh every run (removes existing
 * ones matching our seed booking numbers first).
 */

import { DataSource } from 'typeorm';
import * as dotenv from 'dotenv';

dotenv.config();

// ------------- config ----------------------------------------------
const DAY_TODAY = '2026-04-24';    // today inside the seeded world
const TENANT_SUBDOMAIN = 'mellow'; // pick the Mellow tenant

// ------------- helpers --------------------------------------------
function shiftDate(isoDate: string, days: number): string {
  const d = new Date(isoDate);
  d.setDate(d.getDate() + days);
  return d.toISOString().slice(0, 10);
}

async function main() {
  if (process.env.CONFIRM_SEED !== 'YES') {
    console.log('DMC seeder — dry run. Re-run with CONFIRM_SEED=YES to write.');
    console.log(`Target: tenant "${TENANT_SUBDOMAIN}", today = ${DAY_TODAY}`);
    console.log('Will create ~5 destinations? No — Dubai only. Masters:');
    console.log('  • 1 destination (Dubai)');
    console.log('  • 6 trip sources, 2 seasons');
    console.log('  • 5 suppliers, 7 drivers, 6 vehicles');
    console.log('  • 5 hotels, 10 transport services, 6 activities (with tickets)');
    console.log('  • 6 queries → 6 quotes → 6 bookings');
    console.log('  • transport & activity items spread across 6-8 days per booking,');
    console.log('    overlapping TODAY so the Movement Dashboard is live on first load.');
    process.exit(0);
  }

  const ds = new DataSource({
    type: 'postgres',
    host: process.env.DB_HOST || 'localhost',
    port: Number(process.env.DB_PORT || 5432),
    username: process.env.DB_USERNAME || 'postgres',
    password: process.env.DB_PASSWORD || '',
    database: process.env.DB_NAME || 'dmc_ops',
  });
  await ds.initialize();
  console.log(`→ connected. Seeding tenant "${TENANT_SUBDOMAIN}"...`);

  // ---- Resolve tenant + admin user + system types ---------------
  const tenantRow = await ds.query(
    `SELECT id FROM tenants WHERE subdomain = $1 AND is_active = true LIMIT 1`,
    [TENANT_SUBDOMAIN],
  );
  if (!tenantRow.length) throw new Error(`Tenant ${TENANT_SUBDOMAIN} not found`);
  const tenantId: string = tenantRow[0].id;

  const adminRow = await ds.query(
    `SELECT id FROM users WHERE tenant_id = $1 AND email = 'admin@mellow.com' LIMIT 1`,
    [tenantId],
  );
  if (!adminRow.length) throw new Error('admin@mellow.com not found');
  const adminId: string = adminRow[0].id;

  const aed = (await ds.query(
    `SELECT id FROM currencies WHERE tenant_id = $1 AND code = 'AED' LIMIT 1`,
    [tenantId],
  ))[0]?.id;
  const usd = (await ds.query(
    `SELECT id FROM currencies WHERE tenant_id = $1 AND code = 'USD' LIMIT 1`,
    [tenantId],
  ))[0]?.id;
  if (!aed || !usd) throw new Error('Seed currencies AED/USD missing');

  const getServiceType = async (name: string) => {
    const r = await ds.query(
      `SELECT id FROM service_types WHERE tenant_id = $1 AND LOWER(name) LIKE $2 LIMIT 1`,
      [tenantId, `%${name.toLowerCase()}%`],
    );
    return r[0]?.id as string | undefined;
  };
  const stHotel = await getServiceType('hotel');
  const stTransport = await getServiceType('transport');
  const stActivity = await getServiceType('activit');

  const baseMeta: Record<string, any> = {
    tenant_id: tenantId,
    created_by: adminId,
    updated_by: adminId,
  };

  // ---- Common upsert helper -------------------------------------
  // Tables use *partial* unique indexes (WHERE is_deleted=false), so
  // ON CONFLICT won't match without a filtered WHERE clause. Do a
  // separate SELECT + INSERT/UPDATE to stay index-agnostic.
  async function upsert(
    table: string,
    data: Record<string, any>,
    uniqueCols: string[],
  ): Promise<string> {
    const payload: Record<string, any> = { ...baseMeta, ...data };
    const whereCols = uniqueCols.filter((c) => payload[c] !== undefined);
    const whereSql = whereCols.map((c, i) => `"${c}" = $${i + 1}`).join(' AND ');
    const whereVals = whereCols.map((c) => payload[c]);
    const existing = await ds.query(
      `SELECT id FROM ${table} WHERE ${whereSql} AND is_deleted = false LIMIT 1`,
      whereVals,
    );
    if (existing.length) {
      // Update everything except the identity bits.
      const updCols = Object.keys(payload).filter((c) => !['tenant_id', 'created_by'].includes(c));
      const setSql = updCols.map((c, i) => `"${c}" = $${i + 1}`).join(', ');
      const setVals = updCols.map((c) => payload[c]);
      await ds.query(
        `UPDATE ${table} SET ${setSql} WHERE id = $${updCols.length + 1}`,
        [...setVals, existing[0].id],
      );
      return existing[0].id as string;
    }
    const cols = Object.keys(payload);
    const placeholders = cols.map((_, i) => `$${i + 1}`).join(', ');
    const values = cols.map((c) => payload[c]);
    const r = await ds.query(
      `INSERT INTO ${table} (${cols.map((c) => `"${c}"`).join(', ')})
       VALUES (${placeholders})
       RETURNING id`,
      values,
    );
    return r[0].id as string;
  }

  // ---- Destination ---------------------------------------------
  const destDubai = await upsert(
    'destinations',
    {
      name: 'Dubai',
      short_code: 'DXB',
      country: 'UAE',
      city: 'Dubai',
      currency_id: aed,
      timezone: 'Asia/Dubai',
      visa_required: false,
      // simple-json column stored as TEXT; pass a JSON string so the
      // pg driver doesn't serialise to a Postgres array literal.
      services: JSON.stringify([stHotel, stTransport, stActivity].filter(Boolean)),
      usd_to_base_rate: '3.672000',
      rate_updated_at: new Date(),
    },
    ['tenant_id', 'short_code'],
  );
  console.log('  ✓ Destination Dubai');

  // ---- Trip sources --------------------------------------------
  const tripSources: Record<string, string> = {};
  for (const name of [
    'Direct Walk-in', 'Website', 'Referral', 'Travel Agent', 'Corporate',
    'Instagram', 'Facebook', 'Repeat Client', 'WhatsApp', 'Trade Show',
  ]) {
    const shortCode = name.replace(/[^A-Z]/g, '').slice(0, 10) || name.slice(0, 6).toUpperCase();
    tripSources[name] = await upsert(
      'trip_sources',
      {
        name,
        short_name: name.split(' ')[0].slice(0, 20),
        short_code: shortCode,
        source_type: name === 'Corporate' ? 'corporate' : 'direct',
        contact_name: 'N/A',
        is_active: true,
      },
      ['tenant_id', 'name'],
    );
  }
  console.log('  ✓ Trip sources (10)');

  // ---- Seasons --------------------------------------------------
  // seasons.start_date / end_date are MM-DD strings (recurring yearly)
  const seasons = [
    { name: 'High Season',     start: '11-01', end: '03-31' },
    { name: 'Low Season',      start: '05-01', end: '09-30' },
    { name: 'Shoulder Season', start: '04-01', end: '04-30' },
    { name: 'October Shoulder', start: '10-01', end: '10-31' },
    { name: 'Ramadan Period',  start: '03-10', end: '04-09' },
  ];
  for (const s of seasons) {
    await upsert(
      'seasons',
      { destination_id: destDubai, name: s.name, start_date: s.start, end_date: s.end, is_active: true },
      ['tenant_id', 'destination_id', 'name'],
    );
  }
  console.log(`  ✓ Seasons (${seasons.length})`);

  // ---- Suppliers -----------------------------------------------
  const supplierOps = [
    { name: 'Arabian Adventures',     types: ['transport', 'activity'], contact: 'Omar Saeed', phone: '+971501001001' },
    { name: 'Lama Tours',             types: ['activity'],              contact: 'Layla Noor', phone: '+971502002002' },
    { name: 'Platinum Heritage',      types: ['activity'],              contact: 'Dr Jamal', phone: '+971503003003' },
    { name: 'Dubai Star Transport',   types: ['transport'],             contact: 'Firas Khan', phone: '+971504004004' },
    { name: 'KL City Tours',          types: ['transport', 'activity'], contact: 'Ahmed Al Rashid', phone: '+971505005005' },
    { name: 'Al Bastakiya Cabs',      types: ['transport'],             contact: 'Yusuf Ali', phone: '+971506006006' },
    { name: 'Desert Rose Tours',      types: ['transport', 'activity'], contact: 'Mariam Khalifa', phone: '+971507007007' },
    { name: 'Emirates Luxury Fleet',  types: ['transport'],             contact: 'Rashid Al Maktoum', phone: '+971508008008' },
    { name: 'Oasis Excursions',       types: ['activity'],              contact: 'Faisal Rahman', phone: '+971509009009' },
    { name: 'Falcon City Transfers',  types: ['transport'],             contact: 'Saeed Hammadi', phone: '+971501010101' },
  ];
  const suppliers: Record<string, string> = {};
  for (const s of supplierOps) {
    suppliers[s.name] = await upsert(
      'suppliers',
      {
        name: s.name,
        contact_person: s.contact,
        phone: s.phone,
        email: s.name.toLowerCase().replace(/\s+/g, '.') + '@example.com',
        address: 'Dubai',
        destination_id: destDubai,
        // simple-json column — JSON-stringify so the pg driver
        // doesn't write it as a Postgres array literal (which then
        // fails TypeORM's JSON.parse on read).
        supplier_types: JSON.stringify(s.types),
        is_active: true,
      },
      ['tenant_id', 'name'],
    );
  }
  console.log(`  ✓ Suppliers (${supplierOps.length})`);

  // ---- Vehicles ------------------------------------------------
  const vehicles = [
    { plate: 'DXB-A-12345', type: 'Sedan',   capacity: 4,  supplier: 'KL City Tours' },
    { plate: 'DXB-B-55678', type: 'SUV',     capacity: 6,  supplier: 'Arabian Adventures' },
    { plate: 'DXB-C-22345', type: 'Van',     capacity: 13, supplier: 'Dubai Star Transport' },
    { plate: 'DXB-D-33456', type: 'Minibus', capacity: 25, supplier: 'KL City Tours' },
    { plate: 'DXB-E-44567', type: '4x4',     capacity: 6,  supplier: 'Platinum Heritage' },
    { plate: 'DXB-F-55890', type: 'SUV',     capacity: 6,  supplier: 'Dubai Star Transport' },
    { plate: 'DXB-G-10111', type: 'Sedan',   capacity: 4,  supplier: 'Al Bastakiya Cabs' },
    { plate: 'DXB-H-10112', type: 'Van',     capacity: 13, supplier: 'Emirates Luxury Fleet' },
    { plate: 'DXB-I-10113', type: 'Bus',     capacity: 45, supplier: 'Falcon City Transfers' },
    { plate: 'DXB-J-10114', type: 'SUV',     capacity: 6,  supplier: 'Desert Rose Tours' },
    { plate: 'DXB-K-10115', type: '4x4',     capacity: 6,  supplier: 'Desert Rose Tours' },
    { plate: 'DXB-L-10116', type: 'Luxury Sedan', capacity: 4, supplier: 'Emirates Luxury Fleet' },
  ];
  const vehicleIds: Record<string, string> = {};
  for (const v of vehicles) {
    vehicleIds[v.plate] = await upsert(
      'vehicles',
      {
        vehicle_no: v.plate,
        type: v.type,
        capacity: v.capacity,
        supplier_id: suppliers[v.supplier],
        destination_id: destDubai,
        status: 'available',
      },
      ['tenant_id', 'vehicle_no'],
    );
  }
  console.log('  ✓ Vehicles (6)');

  // ---- Drivers -------------------------------------------------
  const drivers = [
    { name: 'Ahmed Al Rashid', phone: '+971501001001', supplier: 'KL City Tours' },
    { name: 'Khalid Noor', phone: '+971502001001', supplier: 'Arabian Adventures' },
    { name: 'Rakesh Kumar', phone: '+971503001001', supplier: 'Dubai Star Transport' },
    { name: 'Omar Saeed', phone: '+971504001001', supplier: 'Arabian Adventures' },
    { name: 'Vikram Singh', phone: '+971505001001', supplier: 'KL City Tours' },
    { name: 'Hassan Ibrahim', phone: '+971506001001', supplier: 'Dubai Star Transport' },
    { name: 'Saleem Ali', phone: '+971507001001', supplier: 'Platinum Heritage' },
  ];
  const driverIds: Record<string, string> = {};
  for (const d of drivers) {
    driverIds[d.name] = await upsert(
      'drivers',
      {
        name: d.name,
        phone: d.phone,
        supplier_id: suppliers[d.supplier],
        destination_id: destDubai,
        status: 'available',
        is_active: true,
      },
      ['tenant_id', 'phone'],
    );
  }
  console.log('  ✓ Drivers (7)');

  // ---- Hotels --------------------------------------------------
  const hotelDefs = [
    { code: 'JW-MARQUIS', name: 'JW Marriott Marquis Dubai', address: 'Sheikh Zayed Road, Business Bay, Dubai' },
    { code: 'ATLANTIS',   name: 'Atlantis The Palm', address: 'Crescent Rd, The Palm Jumeirah, Dubai' },
    { code: 'ROVE-DT',    name: 'Rove Downtown', address: '312 Al Sayah St, Downtown, Dubai' },
    { code: 'HILTON-HC',  name: 'Hilton Al Habtoor City', address: 'Sheikh Zayed Road, Al Habtoor City, Dubai' },
    { code: 'PI-BARSHA',  name: 'Premier Inn Dubai Barsha', address: 'Al Barsha 1, Dubai' },
  ];
  const hotelIds: Record<string, string> = {};
  for (const h of hotelDefs) {
    hotelIds[h.name] = await upsert(
      'hotels',
      {
        name: h.name,
        short_code: h.code,
        address: h.address,
        destination_id: destDubai,
        contact_person: 'Reservations',
        contact_phone: '+971400000000',
        is_active: true,
      },
      ['tenant_id', 'short_code'],
    );
  }
  console.log('  ✓ Hotels (5)');

  // ---- Transport services --------------------------------------
  const tsDefs = [
    { code: 'DXB-HTL-PVT', from: 'DXB Airport', to: 'Dubai Hotels', cat: 'PICKUP', dur: 45, km: 15 },
    { code: 'DXB-HTL-SIC', from: 'DXB Airport', to: 'Dubai Hotels', cat: 'PICKUP', dur: 60, km: 15 },
    { code: 'HTL-DXB-PVT', from: 'Dubai Hotels', to: 'DXB Airport', cat: 'DROPOFF', dur: 45, km: 15 },
    { code: 'HTL-DXB-SIC', from: 'Dubai Hotels', to: 'DXB Airport', cat: 'DROPOFF', dur: 60, km: 15 },
    { code: 'DXB-AUH-2W', from: 'Dubai', to: 'Abu Dhabi', cat: 'TWO_WAY', dur: 90, km: 140 },
    { code: 'HTL-DESERT-2W', from: 'Dubai Hotels', to: 'Desert Safari Location', cat: 'TWO_WAY', dur: 30, km: 60 },
    { code: 'HTL-CRUISE-2W', from: 'Dubai Hotels', to: 'Dubai Marina Cruise', cat: 'TWO_WAY', dur: 25, km: 20 },
    { code: 'HTL-BURJ-2W', from: 'Dubai Hotels', to: 'Burj Khalifa', cat: 'TWO_WAY', dur: 20, km: 8 },
    { code: 'HTL-AQUA-2W', from: 'Dubai Hotels', to: 'Aquaventure Waterpark', cat: 'TWO_WAY', dur: 30, km: 25 },
    { code: 'CITY-8H', from: 'Dubai Hotels', to: 'City Tour', cat: 'ONE_WAY', dur: 480, km: 120 },
  ];
  // Resolve a default vehicle_type_id once for the booking + quote
  // transport rows below (schema is now (vehicle_type_id) FK, not the
  // legacy denormalised `vehicle_type` text column).
  const sedanRow = (await ds.query(
    `SELECT id FROM vehicle_types
      WHERE tenant_id = $1 AND is_deleted = false
        AND LOWER(name) IN ('sedan', 'luxury sedan', 'private sedan')
      ORDER BY name ASC LIMIT 1`,
    [tenantId],
  )) as { id: string }[];
  const sedanVtFallback = (await ds.query(
    `SELECT id FROM vehicle_types WHERE tenant_id = $1 AND is_deleted = false
      ORDER BY name ASC LIMIT 1`,
    [tenantId],
  )) as { id: string }[];
  const sedanVtId: string | null = sedanRow[0]?.id ?? sedanVtFallback[0]?.id ?? null;
  if (!sedanVtId) {
    console.warn('No vehicle_types found for tenant — transport rows will have vehicle_type_id=NULL.');
  }

  const tsIds: Record<string, string> = {};
  for (const t of tsDefs) {
    tsIds[t.code] = await upsert(
      'transport_services',
      {
        short_code: t.code,
        from_city: t.from,
        from_address: t.from,
        to_city: t.to,
        to_address: t.to,
        destination_id: destDubai,
        category: t.cat,
        duration_mins: t.dur,
        distance_km: t.km.toString(),
        description: `${t.from} → ${t.to}`,
        is_active: true,
      },
      ['tenant_id', 'short_code'],
    );
  }
  console.log('  ✓ Transport services (10)');

  // ---- Activities + tickets ------------------------------------
  const actDefs = [
    { code: 'DSRT-PRM', name: 'Desert Safari Premium', category: 'Adventure', supplier: 'Arabian Adventures', tickets: ['Morning Safari', 'Evening Safari'] },
    { code: 'BRJ-KHL', name: 'Burj Khalifa At The Top', category: 'Sightseeing', supplier: 'KL City Tours', tickets: ['Level 125', 'SKY 148'] },
    { code: 'DHW-CRS', name: 'Dhow Cruise Marina', category: 'Dinner Cruise', supplier: 'Lama Tours', tickets: ['Standard Cruise', 'Premium Cruise'] },
    { code: 'IMG-WRLD', name: 'IMG Worlds of Adventure', category: 'Theme Park', supplier: 'Lama Tours', tickets: ['General Entry'] },
    { code: 'AQUA-WP', name: 'Aquaventure Waterpark', category: 'Theme Park', supplier: 'Lama Tours', tickets: ['Adult Pass'] },
    { code: 'FRR-WRLD', name: 'Ferrari World Abu Dhabi', category: 'Theme Park', supplier: 'KL City Tours', tickets: ['Bronze Pass', 'Silver Pass'] },
  ];
  const activityIds: Record<string, string> = {};
  const ticketIds: Record<string, string> = {}; // `${activity} :: ${ticket}` → id
  for (const a of actDefs) {
    const actId = await upsert(
      'activities',
      {
        name: a.name,
        short_code: a.code,
        category: a.category,
        destination_id: destDubai,
        description: a.name,
        address: 'Dubai',
        contact_person: 'Reservations',
        contact_phone: '+971400000000',
        is_active: true,
        has_transfer: false,
      },
      ['tenant_id', 'short_code'],
    );
    activityIds[a.name] = actId;
    for (const t of a.tickets) {
      const ticketId = await upsert(
        'activity_tickets',
        {
          activity_id: actId,
          name: t,
          duration_mins: 60,
        },
        ['activity_id', 'name'],
      );
      ticketIds[`${a.name} :: ${t}`] = ticketId;
    }
  }
  console.log('  ✓ Activities + tickets (6 + 13)');

  // ==================== BOOKINGS ==================================
  // Per-booking plan. Dates are relative to DAY_TODAY.
  //
  // Day offsets (negative = before today, 0 = today):
  //   BK-001 Sharma     : -2 .. +4 (Apr 22 - Apr 28)
  //   BK-002 Mehta      : -1 .. +3 (Apr 23 - Apr 27)
  //   BK-003 Johnson cpl: -3 .. +1 (Apr 21 - Apr 25)  leaves in 1 day
  //   BK-004 Patel      :  0 .. +5 (Apr 24 - Apr 29)  arrives today
  //   BK-005 Khan       :  0 .. +4 (Apr 24 - Apr 28)  arrives today
  //   BK-006 Corp(6A)   : -2 .. +4 (Apr 22 - Apr 28)

  type OpDef =
    | { kind: 'transport'; tsCode: string; pickup: string; is_shared?: boolean; adults: number; children: number }
    | { kind: 'activity'; activity: string; ticket: string; slot: string; adults: number; children: number }
    | { kind: 'special'; name: string; date_offset: number; price: number };

  type Itinerary = {
    // `items[dayOffset]` → ops for that day (relative to start_date)
    items: Record<number, OpDef[]>;
  };

  const bookings: Array<{
    num: string;
    guest: string;
    email: string;
    phone: string;
    source: string;
    startOffset: number; // relative to DAY_TODAY
    endOffset: number;
    adults: number;
    children_ages: number[];
    infants: number;
    itinerary: Itinerary;
  }> = [
    {
      num: 'BK-001',
      guest: 'Anita Sharma',
      email: 'anita.sharma@example.com',
      phone: '+919912345678',
      source: 'Website',
      startOffset: -2,
      endOffset: 4,
      adults: 4,
      children_ages: [8, 11],
      infants: 0,
      itinerary: {
        items: {
          0: [{ kind: 'transport', tsCode: 'DXB-HTL-SIC', pickup: '14:30', is_shared: true, adults: 4, children: 2 }],
          1: [
            { kind: 'transport', tsCode: 'DXB-AUH-2W', pickup: '09:00', adults: 4, children: 2 },
            { kind: 'activity', activity: 'Ferrari World Abu Dhabi', ticket: 'Silver Pass', slot: '11:30', adults: 4, children: 2 },
          ],
          2: [
            { kind: 'transport', tsCode: 'HTL-DESERT-2W', pickup: '15:00', adults: 4, children: 2 },
            { kind: 'activity', activity: 'Desert Safari Premium', ticket: 'Evening Safari', slot: '15:30', adults: 4, children: 2 },
          ],
          3: [{ kind: 'activity', activity: 'Burj Khalifa At The Top', ticket: 'Level 125', slot: '18:00', adults: 4, children: 2 }],
          4: [
            { kind: 'transport', tsCode: 'HTL-CRUISE-2W', pickup: '19:00', adults: 4, children: 2 },
            { kind: 'activity', activity: 'Dhow Cruise Marina', ticket: 'Standard Cruise', slot: '19:30', adults: 4, children: 2 },
          ],
          5: [{ kind: 'activity', activity: 'Aquaventure Waterpark', ticket: 'Adult Pass', slot: '10:00', adults: 4, children: 2 }],
          6: [{ kind: 'transport', tsCode: 'HTL-DXB-PVT', pickup: '06:00', adults: 4, children: 2 }],
        },
      },
    },
    {
      num: 'BK-002',
      guest: 'Rahul Mehta',
      email: 'rahul.mehta@example.com',
      phone: '+919823456789',
      source: 'Travel Agent',
      startOffset: -1,
      endOffset: 3,
      adults: 2,
      children_ages: [],
      infants: 0,
      itinerary: {
        items: {
          0: [{ kind: 'transport', tsCode: 'DXB-HTL-PVT', pickup: '22:00', adults: 2, children: 0 }],
          1: [
            { kind: 'transport', tsCode: 'HTL-DESERT-2W', pickup: '15:00', adults: 2, children: 0 },
            { kind: 'activity', activity: 'Desert Safari Premium', ticket: 'Evening Safari', slot: '15:30', adults: 2, children: 0 },
          ],
          2: [
            { kind: 'transport', tsCode: 'HTL-CRUISE-2W', pickup: '19:00', adults: 2, children: 0 },
            { kind: 'activity', activity: 'Dhow Cruise Marina', ticket: 'Premium Cruise', slot: '19:30', adults: 2, children: 0 },
          ],
          3: [{ kind: 'activity', activity: 'Burj Khalifa At The Top', ticket: 'SKY 148', slot: '18:30', adults: 2, children: 0 }],
          4: [{ kind: 'transport', tsCode: 'HTL-DXB-PVT', pickup: '05:30', adults: 2, children: 0 }],
        },
      },
    },
    {
      num: 'BK-003',
      guest: 'Sarah Johnson',
      email: 'sarah.johnson@example.com',
      phone: '+447900123456',
      source: 'Direct Walk-in',
      startOffset: -3,
      endOffset: 1,
      adults: 2,
      children_ages: [],
      infants: 0,
      itinerary: {
        items: {
          0: [{ kind: 'transport', tsCode: 'DXB-HTL-SIC', pickup: '10:00', is_shared: true, adults: 2, children: 0 }],
          1: [{ kind: 'activity', activity: 'Burj Khalifa At The Top', ticket: 'Level 125', slot: '17:00', adults: 2, children: 0 }],
          2: [
            { kind: 'transport', tsCode: 'DXB-AUH-2W', pickup: '09:00', adults: 2, children: 0 },
            { kind: 'activity', activity: 'Ferrari World Abu Dhabi', ticket: 'Bronze Pass', slot: '11:30', adults: 2, children: 0 },
          ],
          3: [{ kind: 'activity', activity: 'Aquaventure Waterpark', ticket: 'Adult Pass', slot: '10:00', adults: 2, children: 0 }],
          4: [{ kind: 'transport', tsCode: 'HTL-DXB-PVT', pickup: '14:00', adults: 2, children: 0 }],
        },
      },
    },
    {
      num: 'BK-004',
      guest: 'Priya Patel',
      email: 'priya.patel@example.com',
      phone: '+919934567890',
      source: 'Referral',
      startOffset: 0,
      endOffset: 5,
      adults: 3,
      children_ages: [9],
      infants: 0,
      itinerary: {
        items: {
          0: [{ kind: 'transport', tsCode: 'DXB-HTL-SIC', pickup: '15:00', is_shared: true, adults: 3, children: 1 }],
          1: [
            { kind: 'transport', tsCode: 'HTL-DESERT-2W', pickup: '15:00', adults: 3, children: 1 },
            { kind: 'activity', activity: 'Desert Safari Premium', ticket: 'Evening Safari', slot: '15:30', adults: 3, children: 1 },
          ],
          2: [{ kind: 'activity', activity: 'Burj Khalifa At The Top', ticket: 'Level 125', slot: '18:00', adults: 3, children: 1 }],
          3: [
            { kind: 'transport', tsCode: 'DXB-AUH-2W', pickup: '09:00', adults: 3, children: 1 },
            { kind: 'activity', activity: 'Ferrari World Abu Dhabi', ticket: 'Silver Pass', slot: '11:30', adults: 3, children: 1 },
          ],
          4: [{ kind: 'activity', activity: 'Dhow Cruise Marina', ticket: 'Standard Cruise', slot: '19:30', adults: 3, children: 1 }],
          5: [{ kind: 'transport', tsCode: 'HTL-DXB-PVT', pickup: '12:00', adults: 3, children: 1 }],
        },
      },
    },
    {
      num: 'BK-005',
      guest: 'Imran Khan',
      email: 'imran.khan@example.com',
      phone: '+919945678901',
      source: 'Instagram',
      startOffset: 0,
      endOffset: 4,
      adults: 5,
      children_ages: [10, 14],
      infants: 0,
      itinerary: {
        items: {
          0: [{ kind: 'transport', tsCode: 'DXB-HTL-SIC', pickup: '16:30', is_shared: true, adults: 5, children: 2 }],
          1: [{ kind: 'activity', activity: 'Desert Safari Premium', ticket: 'Evening Safari', slot: '15:30', adults: 5, children: 2 }],
          2: [
            { kind: 'transport', tsCode: 'DXB-AUH-2W', pickup: '08:30', adults: 5, children: 2 },
            { kind: 'activity', activity: 'Ferrari World Abu Dhabi', ticket: 'Silver Pass', slot: '11:00', adults: 5, children: 2 },
          ],
          3: [{ kind: 'activity', activity: 'Dhow Cruise Marina', ticket: 'Premium Cruise', slot: '19:30', adults: 5, children: 2 }],
          4: [{ kind: 'transport', tsCode: 'HTL-DXB-PVT', pickup: '20:00', adults: 5, children: 2 }],
        },
      },
    },
    {
      num: 'BK-006',
      guest: 'TechCorp Retreat',
      email: 'ops@techcorp.example.com',
      phone: '+919956789012',
      source: 'Corporate',
      startOffset: -2,
      endOffset: 4,
      adults: 6,
      children_ages: [],
      infants: 0,
      itinerary: {
        items: {
          0: [{ kind: 'transport', tsCode: 'DXB-HTL-PVT', pickup: '11:00', adults: 6, children: 0 }],
          1: [{ kind: 'transport', tsCode: 'CITY-8H', pickup: '09:00', adults: 6, children: 0 }],
          2: [
            { kind: 'transport', tsCode: 'HTL-DESERT-2W', pickup: '15:00', adults: 6, children: 0 },
            { kind: 'activity', activity: 'Desert Safari Premium', ticket: 'Evening Safari', slot: '15:30', adults: 6, children: 0 },
          ],
          3: [{ kind: 'activity', activity: 'Ferrari World Abu Dhabi', ticket: 'Silver Pass', slot: '10:00', adults: 6, children: 0 }],
          4: [{ kind: 'activity', activity: 'Aquaventure Waterpark', ticket: 'Adult Pass', slot: '10:00', adults: 6, children: 0 }],
          5: [{ kind: 'activity', activity: 'Burj Khalifa At The Top', ticket: 'SKY 148', slot: '19:00', adults: 6, children: 0 }],
          6: [{ kind: 'transport', tsCode: 'HTL-DXB-PVT', pickup: '07:00', adults: 6, children: 0 }],
        },
      },
    },
  ];

  // Wipe any prior copies of our seed bookings so this script is re-runnable.
  // Also drop any orphan queries with our QRY- pattern left over from
  // failed prior runs (before we got to the booking insert).
  const seedQueryNumbers = bookings.map((b) => `QRY-${b.num.replace('BK-', '')}`);
  const orphanQueries = await ds.query(
    `SELECT id FROM queries WHERE tenant_id = $1 AND query_number = ANY($2::text[])`,
    [tenantId, seedQueryNumbers],
  );
  for (const q of orphanQueries) {
    await ds.query(`DELETE FROM query_quote_transports WHERE quote_id IN (SELECT id FROM query_quotes WHERE query_id = $1)`, [q.id]);
    await ds.query(`DELETE FROM query_quote_activities WHERE quote_id IN (SELECT id FROM query_quotes WHERE query_id = $1)`, [q.id]);
    await ds.query(`DELETE FROM query_quote_hotels WHERE quote_id IN (SELECT id FROM query_quotes WHERE query_id = $1)`, [q.id]);
    await ds.query(`DELETE FROM query_quote_specials WHERE quote_id IN (SELECT id FROM query_quotes WHERE query_id = $1)`, [q.id]);
    await ds.query(`DELETE FROM query_quotes WHERE query_id = $1`, [q.id]);
    await ds.query(`DELETE FROM queries WHERE id = $1`, [q.id]);
  }
  for (const b of bookings) {
    const existing = await ds.query(
      `SELECT id, query_id FROM bookings WHERE tenant_id = $1 AND booking_number = $2`,
      [tenantId, b.num],
    );
    for (const e of existing) {
      await ds.query(`DELETE FROM booking_transport_items WHERE booking_id = $1`, [e.id]);
      await ds.query(`DELETE FROM booking_activity_items WHERE booking_id = $1`, [e.id]);
      await ds.query(`DELETE FROM booking_hotel_items WHERE booking_id = $1`, [e.id]);
      await ds.query(`DELETE FROM booking_special_items WHERE booking_id = $1`, [e.id]);
      await ds.query(`DELETE FROM bookings WHERE id = $1`, [e.id]);
      if (e.query_id) {
        await ds.query(`DELETE FROM query_quote_transports WHERE quote_id IN (SELECT id FROM query_quotes WHERE query_id = $1)`, [e.query_id]);
        await ds.query(`DELETE FROM query_quote_activities WHERE quote_id IN (SELECT id FROM query_quotes WHERE query_id = $1)`, [e.query_id]);
        await ds.query(`DELETE FROM query_quote_hotels WHERE quote_id IN (SELECT id FROM query_quotes WHERE query_id = $1)`, [e.query_id]);
        await ds.query(`DELETE FROM query_quote_specials WHERE quote_id IN (SELECT id FROM query_quotes WHERE query_id = $1)`, [e.query_id]);
        await ds.query(`DELETE FROM query_quotes WHERE query_id = $1`, [e.query_id]);
        await ds.query(`DELETE FROM queries WHERE id = $1`, [e.query_id]);
      }
    }
  }
  console.log('  ✓ Prior seed bookings cleared (if any)');

  // Create each booking — query → quote → booking → items
  for (const b of bookings) {
    const startDate = shiftDate(DAY_TODAY, b.startOffset);
    const endDate = shiftDate(DAY_TODAY, b.endOffset);

    // 1) Query
    const queryR = await ds.query(
      `INSERT INTO queries (
        tenant_id, created_by, updated_by,
        query_number, source_id, destination_id, start_date, end_date,
        adults, children_ages, infants, guest_name, guest_email, guest_mobile,
        currency_id, stage, sales_team
      ) VALUES ($1,$2,$2, $3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16)
      RETURNING id`,
      [
        tenantId, adminId,
        `QRY-${b.num.replace('BK-', '')}`,
        tripSources[b.source],
        destDubai,
        startDate,
        endDate,
        b.adults,
        JSON.stringify(b.children_ages),
        b.infants,
        b.guest,
        b.email,
        b.phone,
        aed,
        'converted',
        JSON.stringify([adminId]),
      ],
    );
    const queryId: string = queryR[0].id;

    // 2) Quote (selected)
    const quoteR = await ds.query(
      `INSERT INTO query_quotes (
        tenant_id, created_by, updated_by,
        query_id, quote_number, label, is_selected,
        pricing_strategy, selling_currency_id, exchange_rate, rounding, markup_type, markup_amount
      ) VALUES ($1,$2,$2,$3,1,'Quote 1', true, 'overall', $4, 1, 1, 'amount', 0)
      RETURNING id`,
      [tenantId, adminId, queryId, aed],
    );
    const quoteId: string = quoteR[0].id;

    // 3) Booking shell
    const bookingR = await ds.query(
      `INSERT INTO bookings (
        tenant_id, created_by, updated_by,
        booking_number, query_id, quote_id,
        hotel_total, transport_total, activity_total, special_total, grand_total,
        selling_currency_id, exchange_rate,
        selling_hotel_total, selling_transport_total, selling_activity_total, selling_special_total, selling_total,
        status
      ) VALUES ($1,$2,$2,$3,$4,$5, 0,0,0,0,0, $6, 1, 0,0,0,0,0, $7)
      RETURNING id`,
      [tenantId, adminId, b.num, queryId, quoteId, aed, b.startOffset <= 0 ? 'ongoing' : 'upcoming'],
    );
    const bookingId: string = bookingR[0].id;

    // 4) Items
    let transportTotal = 0;
    let activityTotal = 0;
    for (const [offsetStr, ops] of Object.entries(b.itinerary.items)) {
      const offset = Number(offsetStr);
      const date = shiftDate(startDate, offset);
      for (const op of ops) {
        if (op.kind === 'transport') {
          const baseCost = op.is_shared ? 150 : 350;
          const subtotal = baseCost;
          transportTotal += subtotal;
          // Booking row — vehicle_type_id FK (schema modernised).
          await ds.query(
            `INSERT INTO booking_transport_items (
              tenant_id, created_by, updated_by,
              booking_id, transport_service_id, vehicle_type_id,
              quantity, unit_price, subtotal, date, sort_order,
              is_shared, pickup_time, pickup_address, dropoff_address, op_status, op_status_at
            ) VALUES ($1,$2,$2,$3,$4,$5,1,$6,$6,$7,0,$8,$9,$10,$11,'PLANNED',NOW())`,
            [tenantId, adminId, bookingId, tsIds[op.tsCode], sedanVtId, baseCost, date, !!op.is_shared, op.pickup, 'Dubai Hotels', 'Dubai'],
          );
          // Matching quote row
          await ds.query(
            `INSERT INTO query_quote_transports (
              tenant_id, created_by, updated_by,
              quote_id, transport_service_id, vehicle_type_id,
              quantity, unit_price, subtotal, date, sort_order, is_shared
            ) VALUES ($1,$2,$2,$3,$4,$5,1,$6,$6,$7,0,$8)`,
            [tenantId, adminId, quoteId, tsIds[op.tsCode], sedanVtId, baseCost, date, !!op.is_shared],
          );
        } else if (op.kind === 'activity') {
          const perPax = 200;
          const qty = op.adults + op.children;
          const subtotal = perPax * qty;
          activityTotal += subtotal;
          const actId = activityIds[op.activity];
          const ticketId = ticketIds[`${op.activity} :: ${op.ticket}`] || null;
          // Booking row (one per age_group; use 'adult' for everyone in seed)
          await ds.query(
            `INSERT INTO booking_activity_items (
              tenant_id, created_by, updated_by,
              booking_id, activity_id, ticket_id, age_group,
              quantity, unit_price, subtotal, slot, date, sort_order, op_status, op_status_at
            ) VALUES ($1,$2,$2,$3,$4,$5,'adult',$6,$7,$8,$9,$10,0,'PLANNED',NOW())`,
            [tenantId, adminId, bookingId, actId, ticketId, qty, perPax, subtotal, op.slot, date],
          );
          // Matching quote row
          await ds.query(
            `INSERT INTO query_quote_activities (
              tenant_id, created_by, updated_by,
              quote_id, activity_id, ticket_id, age_group,
              quantity, unit_price, subtotal, slot, date, sort_order
            ) VALUES ($1,$2,$2,$3,$4,$5,'adult',$6,$7,$8,$9,$10,0)`,
            [tenantId, adminId, quoteId, actId, ticketId, qty, perPax, subtotal, op.slot, date],
          );
        }
      }
    }

    // Update totals on booking + quote
    const grand = transportTotal + activityTotal;
    const selling = Math.round(grand * 1.2); // 20% markup for demo
    await ds.query(
      `UPDATE bookings
         SET transport_total = $1, activity_total = $2, grand_total = $3,
             selling_transport_total = $4, selling_activity_total = $5, selling_total = $6
       WHERE id = $7`,
      [transportTotal, activityTotal, grand, Math.round(transportTotal * 1.2), Math.round(activityTotal * 1.2), selling, bookingId],
    );
  }
  console.log(`  ✓ Bookings (${bookings.length}) + queries + quotes + items`);

  await ds.destroy();
  console.log('\n✓ seed complete. Login and check Movement Dashboard.');
}

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