/**
 * seed-dubai-activities — bulk-loads Dubai activity masters + tickets
 * into the Mellow tenant from the canonical "Dubai Activities.xlsx"
 * supplied by the client.
 *
 * Usage:
 *   npm run db:seed:dubai-activities                                 # dry run, prints plan
 *   CONFIRM_SEED=YES npm run db:seed:dubai-activities                # actually writes (upsert)
 *   CONFIRM_SEED=YES WIPE_FIRST=YES npm run db:seed:dubai-activities # wipe activity data first, then re-seed clean
 *
 *   SEED_XLSX="/path/to/Dubai Activities.xlsx"                       # override source
 *   SEED_TENANT=mellow                                               # override tenant
 *
 * WIPE_FIRST removes activity_pricing, activity_ticket_slots,
 * activity_tickets, and activities rows for the target tenant before
 * inserting. Use on a fresh server (or to force a clean reseed when
 * the source xlsx changed); it does NOT touch booking_activity_items
 * or query_quote_activities so any in-flight bookings keep their
 * snapshot pricing. Skip the flag on day-to-day re-runs — the seeder
 * is already idempotent.
 *
 * Design:
 *  - Sheet = Activity master (with split overrides for Ski Dubai /
 *    Yellow Boat / iFly).
 *  - Each row = a ticket under that master. The row's name is stripped
 *    of the master-prefix and the trailing "Tickets/Ticket" word so
 *    "Burj Khalifa Level 124 Prime + The Roof Top Meal Tickets"
 *    becomes ticket "Level 124 Prime + The Roof Top Meal" under
 *    Activity "Burj Khalifa".
 *  - Time-like parentheticals in the row name "(7 AM - 12:30 PM)"
 *    move into the ticket's time_slot — keeps the ticket name short
 *    and pushes the time data into its proper column.
 *  - Skips Service="Shared Transfer Only" rows (they're SIC transports,
 *    not activities) and the whole "Dubai - SIC" sheet.
 *  - Skips the "Visa" sheet — Dubai 30-Day visa / OTB land in Special
 *    Service flows, not Activity.
 *  - Idempotent: upsert by (tenant_id, destination_id, name) for
 *    activities and (tenant_id, activity_id, name) for tickets. Safe
 *    to re-run after edits to the source file.
 *
 * Schema gaps + defaults the seeder applies:
 *  - address: "Dubai, UAE" (Excel has none; ops fills via UI)
 *  - contact_person: "Operations" (Excel has none)
 *  - operating_days: ["Mon","Tue","Wed","Thu","Fri","Sat","Sun"] JSONB —
 *    all 7 short codes the FE checkbox group expects. "Daily" was the
 *    previous shape but the form filters by exact-code includes() so
 *    it rendered every day as unchecked.
 *  - has_transfer: false (the few bundled SIC + Ticket entries land in
 *    Special Service category as a flag)
 *  - category: derived from sheet via CATEGORY_BY_SHEET below; defaults
 *    to "Others" if the sheet isn't mapped.
 */

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-activities.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-activities.xlsx');
const LEGACY_XLSX = path.join(os.homedir(), 'Downloads', 'Dubai Activities.xlsx');
const SEED_XLSX =
  process.env.SEED_XLSX ||
  (fs.existsSync(BUNDLED_XLSX) ? BUNDLED_XLSX : LEGACY_XLSX);

// Sheets that aren't activities at all — skip wholesale.
const SKIP_SHEETS = new Set<string>(['Dubai - SIC', 'Visa']);

// Sheets where each row is its own activity (collection sheets), not
// tickets under a single master.
const MIXED_SHEETS = new Set<string>(['Dubai Tickets', 'Lunch  Dinner', 'Helicopter']);

// Multi-row attraction groups on MIXED sheets. When a row name CONTAINS
// one of these patterns (prefix preferred, embedded substring as
// fallback), collapse it under the pattern as the Activity master and
// derive the ticket name from what remains. Longest pattern wins
// ("Bol Gappa Restaurant Lapita" > "Bol Gappa Restaurant" > "Yas Island").
// Examples:
//   "Ain Dubai Ticket - Non-Prime Time" → master "Ain Dubai", ticket "Non-Prime Time"
//   "1 Day 2 Yas Island Park Pass Tickets" → master "Yas Island", ticket "1 Day 2 Park Pass"
//   "Lunch - Bol Gappa Restaurant, Al Karama - Non Veg" → master "Bol Gappa Restaurant", ticket "Lunch Al Karama - Non Veg"
const MIXED_SHEET_GROUPS: Record<string, string[]> = {
  'Dubai Tickets': [
    'Ain Dubai',
    'Palm Monorail',
    'IMG',
    'Ferrari World',
    'Warner Bros',
    'Dubai Safari Park',
    'Safari Park',
    'Al Ain Zoo',
    'Glow Garden',
    'Abu Dhabi National Aquarium',
    'Yas Water Park',
    'Yas Island',
    'Dubai Ice Rink',
    'Sea World',
  ],
  'Lunch  Dinner': [
    // Longer patterns first so "Bol Gappa Restaurant Lapita" wins over
    // bare "Bol Gappa Restaurant" — sort below also enforces this.
    'Bol Gappa Restaurant Lapita',
    'Bol Gappa Restaurant',
    'Bikanervala Dubai Mall',
    'Rasoi Ghar Restaurant Yas Island',
    'Kailash Parbat Restaurant Yas Island',
    'Rasoi Ghar Restaurant',
    'Maharaja Bhog Restaurant',
    'Maharaja Express Restaurant',
    'Raj Mahal Restaurant',
    'Atithi Veg Restaurant',
    'Chhappan Bhog',
    'Rangoli',
  ],
  'Helicopter': [
    'Helicopter Ride Falcon',
    'Iconic Helicopter Tour Dubai',
    'Vision Helicopter Tour Dubai',
    'Palm Helicopter Tour Dubai',
    'Grand Helicopter tour',
  ],
};

// sheet → category. "Others" is the fallback for unmapped sheets.
const CATEGORY_BY_SHEET: Record<string, string> = {
  // Sightseeing — landmarks, viewpoints, water-park entries
  'Burj Khalifa': 'Sightseeing',
  'DAUZ': 'Sightseeing',
  'Burj Al Arab': 'Sightseeing',
  'Sky View + Glass Slide': 'Sightseeing',
  'FOM + View at Palm': 'Sightseeing',
  'Lotus Marina Dubai': 'Sightseeing',
  'Lostchamber Atlantis': 'Sightseeing',
  'Atlantis': 'Sightseeing',
  'Dubai Tickets': 'Sightseeing',
  // Adventure — high-energy / extreme
  'Sky Dive': 'Adventure',
  'Jebel Jais Zipline - Ras Al kha': 'Adventure',
  'Yas Marina Circuit Tour': 'Adventure',
  'Yas Marina Circuit': 'Adventure',
  'Gyrocopter Flight': 'Adventure',
  'Scuba Diving Tour': 'Adventure',
  'Sky Views Edge Walk': 'Adventure',
  'Deep Dive ': 'Adventure',
  'I Fly Dubai': 'Adventure',
  'I Fly': 'Adventure',
  'X line': 'Adventure',
  'Desert Safari': 'Adventure',
  // Tour — guided sightseeing trips
  'Helicopter': 'Tour',
  'Yellow Boat': 'Tour',
  'Yellow Boat 75 Min': 'Tour',
  // Park — full-day theme/water/snow parks
  'Dubai Parks': 'Park',
  'Green Planet Combo': 'Park',
  'Green Planet': 'Park',
  'Ski Dubai': 'Park',
  'Snow Park Abu Dhabi': 'Park',
  'Aquafun': 'Park',
  // Entertainment — interactive attractions, kids zones
  'TeamLab Phenomena Abu Dhabi': 'Entertainment',
  'Madame Tussauds': 'Entertainment',
  'Museum of Illusions': 'Entertainment',
  'OliOli Children Play Museum': 'Entertainment',
  'Kidzania': 'Entertainment',
  'Museum of Candy': 'Entertainment',
  'Dolphinarium Majestic Swim- Sha': 'Entertainment',
  // Show
  'La Perle': 'Show',
  // Cultural — palaces, museums
  'Qasr Al Watan': 'Cultural',
  'Louvre Museum': 'Cultural',
  'Emirates Palace Abu dhabi': 'Cultural',
  // Food
  'Alexandra Dinner Cruise': 'Food',
  'Lunch  Dinner': 'Food',
};

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

/** Convert an Excel time-fraction (0.375 = 9:00 AM) to HH:MM. */
function fracToHHMM(f: number | null | undefined): string {
  if (f === null || f === undefined) return '';
  const n = Number(f);
  if (!Number.isFinite(n)) return '';
  const m = Math.round(n * 1440);
  const h = Math.floor((m % 1440) / 60);
  const mm = m % 60;
  return `${String(h).padStart(2, '0')}:${String(mm).padStart(2, '0')}`;
}

/** Pull all "(7 AM - 12:30 PM)" style parentheticals out of `name` and
 *  return both the cleaned name and a comma-joined slot string. */
const TIME_PAREN =
  /\(([^)]*(?:AM|PM|am|pm|\d+\s*:\s*\d+|\d+\s*[-–]\s*\d+)[^)]*)\)/g;
function extractSlotsFromName(name: string): { name: string; slot: string } {
  const slots: string[] = [];
  const cleaned = name.replace(TIME_PAREN, (_m, inside) => {
    slots.push(String(inside).trim());
    return '';
  });
  return {
    name: cleaned.replace(/\s+/g, ' ').replace(/^[\s\-–—:,.]+|[\s\-–—:,.]+$/g, ''),
    slot: slots.join(', '),
  };
}

/** Final cleanup applied to a ticket label after master extraction:
 *  - drop the leading "Ticket(s)" word that often sits between master
 *    and variant ("Ain Dubai Ticket - Non-Prime Time" → "Non-Prime Time")
 *  - drop the trailing "Ticket(s)" word
 *  - collapse double punctuation introduced by mid-string master removal
 *    ("Lunch - , Al Karama" → "Lunch Al Karama")
 *  - trim stray leading/trailing dashes, commas, whitespace, newlines */
function cleanTicketLabel(s: string): string {
  let out = s.replace(/\s+/g, ' ').trim();
  out = out.replace(/^Tickets?\b[\s\-–—:,.]*/i, '');
  out = out.replace(/[\s\-–—:,.]*\bTickets?\b\s*$/i, '');
  // Collapse repeated separators left behind by mid-string excision.
  out = out.replace(/[\s\-–—:,]{2,}/g, ' - ');
  out = out.replace(/^[\s\-–—:,.]+|[\s\-–—:,.]+$/g, '');
  return out;
}

/** Remove the master prefix and trailing "Tickets/Ticket" word from a
 *  ticket label. */
function stripMaster(name: string, master: string): string {
  let n = name.trim();
  const candidates = [master, master.replace(/s$/, ''), master + 's', master + ' -', master + ' –', master + ':'];
  candidates.sort((a, b) => b.length - a.length);
  for (const p of candidates) {
    if (!p) continue;
    if (n.toLowerCase().startsWith(p.toLowerCase())) {
      n = n.slice(p.length);
      break;
    }
  }
  n = n.replace(/\s+Tickets?\b\s*$/i, '').trim();
  n = n.replace(/^[\s\-–—:,.]+|[\s\-–—:,.]+$/g, '');
  return n || name.trim();
}

/** Apply per-sheet master + ticket split. Mirrors the rules used to
 *  build Dubai_Activities_Master.xlsx so the seeded data lines up
 *  exactly with what ops reviewed. */
function splitMaster(sheet: string, rowName: string): { master: string; ticket: string } {
  // Lunch  Dinner — special meal-prefix shape:
  //   "Dinner - Veg World Restaurant, Bur Dubai - Pure Veg"
  //     → master "Veg World Restaurant", ticket "Dinner"
  // The restaurant name lives between the meal word and the first
  // comma; everything after the comma is location + cuisine, which is
  // metadata on the restaurant, not the ticket. Runs BEFORE the group-
  // prefix/embedded passes because those would otherwise dump the
  // location text into the ticket label.
  if (sheet === 'Lunch  Dinner') {
    const meal = rowName.match(/^(Lunch|Dinner|Breakfast)\s*[-–—]\s*(.+)$/i);
    if (meal) {
      const mealType = meal[1].charAt(0).toUpperCase() + meal[1].slice(1).toLowerCase();
      const rest = meal[2].trim();
      const commaIdx = rest.indexOf(',');
      const restaurant = (commaIdx === -1 ? rest : rest.slice(0, commaIdx)).trim();
      if (restaurant) return { master: restaurant, ticket: mealType };
    }
  }
  if (MIXED_SHEETS.has(sheet)) {
    // Check group patterns longest-first so "Bol Gappa Restaurant
    // Lapita" wins over the shorter "Bol Gappa Restaurant", and
    // "Yas Water Park" wins over "Yas Island".
    const groups = (MIXED_SHEET_GROUPS[sheet] || []).slice().sort((a, b) => b.length - a.length);
    const lower = rowName.toLowerCase();
    // Pass 1 — prefix match. Preferred because it preserves left-to-
    // right reading order in the ticket name ("Ain Dubai Ticket - Non-
    // Prime Time" → ticket "Non-Prime Time").
    for (const prefix of groups) {
      if (lower.startsWith(prefix.toLowerCase())) {
        // Slice the prefix off directly — don't reuse stripMaster here
        // because its empty-fallback would return the whole row name
        // when the remainder is just "Ticket" (e.g. "Dubai Ice Rink
        // Ticket"). We want the empty remainder so cleanTicketLabel
        // can fall through to "Standard".
        const cleaned = cleanTicketLabel(rowName.slice(prefix.length));
        return { master: prefix, ticket: cleaned || 'Standard' };
      }
    }
    // Pass 2 — embedded substring match. Catches rows where the
    // master sits in the middle, e.g. "1 Day 2 Yas Island Park Pass
    // Tickets" or "Lunch - Bol Gappa Restaurant, Al Karama - Non Veg".
    for (const pattern of groups) {
      const idx = lower.indexOf(pattern.toLowerCase());
      if (idx === -1) continue;
      const before = rowName.slice(0, idx);
      const after = rowName.slice(idx + pattern.length);
      // Join the surrounding fragments with a space — the cleanup
      // pass below squeezes double punctuation/whitespace.
      const ticket = cleanTicketLabel(`${before} ${after}`);
      return { master: pattern, ticket: ticket || 'Standard' };
    }
    return { master: rowName, ticket: rowName };
  }
  if (sheet === 'Ski Dubai') {
    const master = rowName.includes('Snow Abu Dhabi') ? 'Snow Abu Dhabi' : 'Ski Dubai';
    return { master, ticket: stripMaster(rowName, master) };
  }
  if (sheet === 'Yellow Boat' || sheet === 'Yellow Boat 75 Min') {
    return { master: 'Yellow Boat', ticket: stripMaster(rowName, 'Yellow Boats') };
  }
  if (sheet === 'I Fly' || sheet === 'I Fly Dubai') {
    return { master: 'iFly Dubai', ticket: stripMaster(rowName, 'I Fly') };
  }
  return { master: sheet, ticket: stripMaster(rowName, sheet) };
}

/** Parse a single slot label "10:00 AM - 4 PM" → { start, end }.
 *  Returns nulls when the label is prose or ambiguous. */
const SLOT_RANGE = /^(.+?)\s*[-–to]+\s*(.+)$/i;
function parseTimeFragment(s: string): string | null {
  const m = s.trim().match(/^(\d{1,2})(?::(\d{1,2}))?\s*(AM|PM)?$/i);
  if (!m) return null;
  let h = Number(m[1]);
  const mm = m[2] ? Number(m[2]) : 0;
  const ap = m[3]?.toUpperCase();
  if (ap === 'PM' && h !== 12) h += 12;
  if (ap === 'AM' && h === 12) h = 0;
  if (h < 0 || h > 23 || mm < 0 || mm > 59) return null;
  return `${String(h).padStart(2, '0')}:${String(mm).padStart(2, '0')}:00`;
}
function parseSlotRange(label: string): { start: string | null; end: string | null } {
  const m = label.trim().match(SLOT_RANGE);
  if (!m) {
    // Maybe a single departure time
    return { start: parseTimeFragment(label), end: null };
  }
  let left = m[1].trim();
  const right = m[2].trim();
  // Propagate AM/PM from right → left when left lacks it ("7 - 11 AM").
  const rightAP = right.match(/(AM|PM)$/i)?.[1];
  if (rightAP && !/(AM|PM)$/i.test(left)) left = `${left} ${rightAP}`;
  return { start: parseTimeFragment(left), end: parseTimeFragment(right) };
}

// ────────────── parsed row shape ────────────────────────────────────
interface ParsedSlot {
  label: string;
  start_time: string | null;
  end_time: string | null;
}
interface ParsedTicket {
  master: string;
  category: string;
  ticket: string;
  service: string;
  timeSlot: string; // comma-joined label kept for legacy time_slot column
  slots: ParsedSlot[];
  open: string;
  close: string;
  durationMins: number | null;
  childAgeRange: string; // e.g. "3-11"
  description: string;
  sourceSheet: string;
}

/** Split a slot text "7 AM- 12:30 PM, 7:30 PM - 11 PM" into structured
 *  rows. Comma is the canonical delimiter the parser uses to enumerate
 *  windows or departure times within the same ticket. */
function buildSlots(timeSlotText: string): ParsedSlot[] {
  const chunks = timeSlotText
    .split(/[,;]/)
    .map((s) => s.trim())
    .filter(Boolean);
  return chunks.map((label) => {
    const { start, end } = parseSlotRange(label);
    return { label, start_time: start, end_time: end };
  });
}

// ────────────── Excel parser ────────────────────────────────────────
async function parseExcel(xlsxPath: string): Promise<ParsedTicket[]> {
  const wb = new ExcelJS.Workbook();
  await wb.xlsx.readFile(xlsxPath);
  const out: ParsedTicket[] = [];
  for (const ws of wb.worksheets) {
    const sheet = ws.name;
    if (SKIP_SHEETS.has(sheet)) continue;
    const category = CATEGORY_BY_SHEET[sheet] || 'Others';
    // Detect child age range from the first ~8 rows ("Child (3-11)").
    let childAge = '';
    for (let r = 1; r <= Math.min(8, ws.rowCount); r++) {
      for (let c = 1; c <= ws.columnCount; c++) {
        const v = String(ws.getRow(r).getCell(c).value ?? '');
        const m = v.match(/Child\s*\(([^)]+)\)/);
        if (m) {
          childAge = m[1];
          break;
        }
      }
      if (childAge) break;
    }
    ws.eachRow((row) => {
      const a = String(row.getCell('A').value ?? '').trim();
      if (!a || ['Name', 'Adult', 'Child'].includes(a)) return;
      const service = String(row.getCell('B').value ?? '').trim();
      if (service === 'Shared Transfer Only') return; // SIC transport
      const { master, ticket: rawTicket } = splitMaster(sheet, a);
      const slots = extractSlotsFromName(rawTicket);
      const dRaw = row.getCell('D').value;
      const eRaw = row.getCell('E').value;
      const fRaw = row.getCell('F').value;
      const gRaw = row.getCell('G').value;
      const open = fracToHHMM(typeof dRaw === 'number' ? dRaw : null);
      const close = fracToHHMM(typeof eRaw === 'number' ? eRaw : null);
      let durationMins: number | null = null;
      if (typeof fRaw === 'number' && fRaw > 1) durationMins = Math.round(fRaw);
      let slotG = '';
      if (gRaw !== null && gRaw !== undefined) {
        const sg = String(gRaw).trim();
        if (sg) {
          const asNum = Number(sg);
          slotG = Number.isFinite(asNum) && sg.includes('.') ? fracToHHMM(asNum) : sg;
        }
      }
      const timeSlot =
        slots.slot ||
        slotG ||
        (open && close ? `${open} - ${close}` : open || close || '');
      const descRaw = String(row.getCell('C').value ?? '').trim();
      const description = descRaw.split(/\r?\n/).join(' ').slice(0, 1000);
      const slotRecords = buildSlots(timeSlot);
      out.push({
        master,
        category,
        ticket: slots.name,
        service: service || 'Ticket',
        timeSlot,
        slots: slotRecords,
        open,
        close,
        durationMins,
        childAgeRange: childAge,
        description,
        sourceSheet: sheet,
      });
    });
  }
  return out;
}

/**
 * Mirror of ActivityService's pricing auto-create on the create path.
 * Inserts one activity_pricing row per (ticket × enabled age group) for
 * the given activity. Prices stay NULL — ops fills them via the Pricing
 * screen.
 *
 * Idempotent via UNIQUE(tenant, activity, ticket, age_group, currency).
 * age_group is lowercased to match the service-layer convention
 * (Adult→'adult', Child→'child', Complimentary→'complimentary'). Age
 * groups with min_age=max_age=0 are skipped — same filter as the
 * service, which treats those as "disabled".
 */
async function syncActivityPricing(
  ds: DataSource,
  tenantId: string,
  activityId: string,
  destinationId: string,
  ageConfigJson: string | null,
): Promise<number> {
  if (!ageConfigJson) return 0;
  const destRows: { currency_id: string | null }[] = await ds.query(
    `SELECT currency_id FROM destinations WHERE id = $1 LIMIT 1`,
    [destinationId],
  );
  const currencyId = destRows[0]?.currency_id;
  if (!currencyId) return 0;
  let ageConfig: { label: string; min_age: number; max_age: number }[];
  try {
    ageConfig = JSON.parse(ageConfigJson);
  } catch {
    return 0;
  }
  const groups = ageConfig
    .filter((ag) => (ag.min_age ?? 0) > 0 || (ag.max_age ?? 0) > 0)
    .map((ag) => ag.label.toLowerCase());
  if (!groups.length) return 0;

  // Tickets for this activity. If none, emit rows with ticket_id NULL
  // (the service uses [null] as a singleton fallback).
  const tickets: { id: string }[] = await ds.query(
    `SELECT id FROM activity_tickets
       WHERE activity_id = $1 AND is_deleted = false`,
    [activityId],
  );
  const ticketIds: (string | null)[] = tickets.length
    ? tickets.map((t) => t.id)
    : [null];

  let inserted = 0;
  for (const ticketId of ticketIds) {
    for (const ageGroup of groups) {
      // NULL ticket_id needs an `IS NULL` predicate; non-NULL uses `=`.
      // Express both with COALESCE-on-text so a single NOT EXISTS works
      // either way.
      const r = await ds.query(
        `INSERT INTO activity_pricing (
           id, tenant_id, destination_id, activity_id, ticket_id,
           age_group, currency_id,
           is_deleted, created_at, updated_at
         )
         SELECT
           uuid_generate_v4(), $1, $2, $3, $4::uuid,
           $5::varchar, $6,
           false, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
         WHERE NOT EXISTS (
           SELECT 1 FROM activity_pricing ap
            WHERE ap.tenant_id = $1
              AND ap.activity_id = $3
              AND COALESCE(ap.ticket_id::text, '') = COALESCE($4::uuid::text, '')
              AND ap.age_group = $5::varchar
              AND ap.currency_id = $6
              AND ap.is_deleted = false
         )
         RETURNING id`,
        [tenantId, destinationId, activityId, ticketId, ageGroup, currencyId],
      );
      inserted += r.length;
    }
  }
  return inserted;
}

// ────────────── 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);
  const masterMap = new Map<string, ParsedTicket[]>();
  for (const p of parsed) {
    const k = p.master;
    if (!masterMap.has(k)) masterMap.set(k, []);
    const bucket = masterMap.get(k)!;
    // Dedupe by case-insensitive ticket name within the same master:
    // "1 Day 2 Yas Island Park Pass Ticket" and "...Tickets" both
    // resolve to ticket "1 Day 2 Park Pass" under "Yas Island" — keep
    // the first, later ones are noise from singular/plural row dupes.
    const dup = bucket.find((t) => t.ticket.toLowerCase() === p.ticket.toLowerCase());
    if (!dup) bucket.push(p);
  }
  console.log(`Parsed: ${masterMap.size} activity masters, ${parsed.length} tickets.`);
  // Category breakdown
  const catCount: Record<string, number> = {};
  for (const tickets of masterMap.values()) {
    const cat = tickets[0].category;
    catCount[cat] = (catCount[cat] || 0) + 1;
  }
  console.log('\nBy category:');
  for (const [c, n] of Object.entries(catCount).sort((a, b) => b[1] - a[1])) {
    console.log(`  ${String(n).padStart(3)}  ${c}`);
  }

  if (dryRun) {
    const focus = process.env.SEED_FOCUS;
    const entries = focus
      ? [...masterMap].filter(([m]) => m.toLowerCase().includes(focus.toLowerCase()))
      : [...masterMap].slice(0, 6);
    console.log(
      focus
        ? `\nMatching masters (filter: "${focus}"):`
        : '\nSample rows (first 6 masters):',
    );
    for (const [master, tickets] of entries) {
      console.log(`  • [${tickets[0].category}] ${master} (${tickets.length} ticket${tickets.length === 1 ? '' : 's'})`);
      for (const t of tickets.slice(0, focus ? tickets.length : 3)) {
        console.log(`      – ${t.ticket}  slot=${t.timeSlot || '-'}`);
      }
      if (!focus && tickets.length > 3) console.log(`      … +${tickets.length - 3} more`);
    }
    console.log('\nRe-run with CONFIRM_SEED=YES to write.');
    process.exit(0);
  }

  // ──── DB connect ────────────────────────────────────────────────
  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('\n→ connected.');

  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 ORDER BY created_at ASC LIMIT 1`,
    [tenantId],
  );
  if (!adminRow.length) throw new Error('No users for this tenant');
  const adminId: string = adminRow[0].id;
  const destRow = await ds.query(
    `SELECT id FROM destinations WHERE tenant_id = $1 AND LOWER(name) = 'dubai' AND is_deleted = false LIMIT 1`,
    [tenantId],
  );
  if (!destRow.length) throw new Error('Dubai destination not found for this tenant');
  const destinationId: string = destRow[0].id;

  if (process.env.WIPE_FIRST === 'YES') {
    // Hard-delete in FK order. Activity FK graph is wider than the
    // first naive wipe assumed — booking/quote tables hold raw FKs
    // (not snapshot copies) so they must be cleared first. Transport
    // tables carry a soft FK (linked_activity_id) to the activity-side
    // booking/quote rows; NULL those before the activity-side delete.
    //
    //   journeys → items → batches
    //                 ↓
    //   booking_activity_items + query_quote_activities
    //                 ↓
    //   activity_pricing + slots + tickets + activities
    console.log('WIPE_FIRST=YES — clearing activity data for tenant…');
    await ds.query(`DELETE FROM activity_ticket_journeys WHERE tenant_id = $1`, [tenantId]);
    await ds.query(`DELETE FROM activity_ticket_items WHERE tenant_id = $1`, [tenantId]);
    await ds.query(`DELETE FROM activity_ticket_batches WHERE tenant_id = $1`, [tenantId]);
    await ds.query(
      `UPDATE query_quote_transports SET linked_activity_id = NULL WHERE tenant_id = $1 AND linked_activity_id IS NOT NULL`,
      [tenantId],
    );
    await ds.query(
      `UPDATE booking_transport_items SET linked_activity_id = NULL WHERE tenant_id = $1 AND linked_activity_id IS NOT NULL`,
      [tenantId],
    );
    await ds.query(`DELETE FROM query_quote_activities WHERE tenant_id = $1`, [tenantId]);
    await ds.query(`DELETE FROM booking_activity_items WHERE tenant_id = $1`, [tenantId]);
    const r1 = await ds.query(`DELETE FROM activity_pricing WHERE tenant_id = $1`, [tenantId]);
    const r2 = await ds.query(`DELETE FROM activity_ticket_slots WHERE tenant_id = $1`, [tenantId]);
    const r3 = await ds.query(`DELETE FROM activity_tickets WHERE tenant_id = $1`, [tenantId]);
    const r4 = await ds.query(`DELETE FROM activities WHERE tenant_id = $1`, [tenantId]);
    console.log(
      `  wiped: ${r4[1] ?? 0} activities, ${r3[1] ?? 0} tickets, ${r2[1] ?? 0} slots, ${r1[1] ?? 0} pricing rows (plus child quote/booking/batch/journey rows)`,
    );
  }

  // ──── upsert helper (mirrors seed-dubai-dmc; partial unique index aware) ────
  async function upsert(table: string, data: Record<string, any>, uniqueCols: string[]): Promise<string> {
    const payload: Record<string, any> = {
      tenant_id: tenantId,
      created_by: adminId,
      updated_by: adminId,
      ...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 mutable fields; never overwrite tenant_id / created_by.
      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.join('","')}") VALUES (${placeholders}) RETURNING id`,
      values,
    );
    return r[0].id as string;
  }

  let mastersUpserted = 0;
  let ticketsUpserted = 0;
  let pricingUpserted = 0;
  for (const [master, tickets] of masterMap) {
    const first = tickets[0];
    // Merge descriptions / operating windows from the first non-empty
    // ticket. Ops sees one master-level entry; ticket-level fields
    // carry the per-tier detail.
    const sampleDesc = tickets.find((t) => t.description)?.description || '';
    const ageMatch = first.childAgeRange.match(/(\d+)\s*-\s*(\d+)/);
    const childMin = ageMatch ? Number(ageMatch[1]) : null;
    const childMax = ageMatch ? Number(ageMatch[2]) : null;
    const ageConfig =
      childMin !== null && childMax !== null
        ? JSON.stringify([
            { label: 'Adult', min_age: childMax + 1, max_age: 99 },
            { label: 'Child', min_age: childMin, max_age: childMax },
            { label: 'Complimentary', min_age: 0, max_age: Math.max(0, childMin - 1) },
          ])
        : null;
    // Derive a tenant-unique short_code on first insert; preserve on
    // re-run (the upsert path updates the row but never regenerates the
    // code, matching the FE rule that short_code is immutable
    // post-create).
    const existingShortCode = await ds.query(
      `SELECT short_code FROM activities WHERE tenant_id = $1 AND destination_id = $2 AND name = $3 AND is_deleted = false LIMIT 1`,
      [tenantId, destinationId, master],
    );
    const shortCode = existingShortCode.length
      ? existingShortCode[0].short_code
      : await deriveUniqueShortCode(master, 'ACT', async (cand) => {
          const r = await ds.query(
            `SELECT 1 FROM activities WHERE tenant_id = $1 AND short_code = $2 AND is_deleted = false LIMIT 1`,
            [tenantId, cand],
          );
          return r.length > 0;
        });
    const activityId = await upsert(
      'activities',
      {
        destination_id: destinationId,
        name: master,
        short_code: shortCode,
        category: first.category,
        description: sampleDesc || `Imported from "${first.sourceSheet}" sheet`,
        address: 'Dubai, UAE',
        contact_person: 'Operations',
        contact_phone: null,
        is_active: true,
        operating_days: JSON.stringify(['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun']),
        age_config: ageConfig,
        has_transfer: tickets.some((t) => t.service.includes('Transfer')),
      },
      ['tenant_id', 'destination_id', 'name'],
    );
    mastersUpserted++;
    for (const t of tickets) {
      const ticketId = await upsert(
        'activity_tickets',
        {
          activity_id: activityId,
          name: t.ticket,
          duration_mins: t.durationMins,
          description: t.description || null,
          is_active: true,
        },
        ['tenant_id', 'activity_id', 'name'],
      );
      ticketsUpserted++;
      // Slot rows — one per window/departure. Idempotent via the
      // (ticket_id, label) unique key. Existing slot rows that don't
      // appear in the current run are kept (manual edits ops made via
      // UI won't get clobbered on re-seed).
      for (let i = 0; i < t.slots.length; i++) {
        const s = t.slots[i];
        await upsert(
          'activity_ticket_slots',
          {
            ticket_id: ticketId,
            label: s.label,
            start_time: s.start_time,
            end_time: s.end_time,
            sort_order: i,
          },
          ['tenant_id', 'ticket_id', 'label'],
        );
      }
    }
    // Pricing scaffold — mirror ActivityService's auto-create on the
    // create path. One activity_pricing row per (ticket × enabled age
    // group), prices NULL. Idempotent via the UNIQUE(tenant, activity,
    // ticket, age_group, currency) constraint.
    pricingUpserted += await syncActivityPricing(
      ds,
      tenantId,
      activityId,
      destinationId,
      ageConfig,
    );
  }
  console.log(`\n✓ ${mastersUpserted} activity master(s) upserted`);
  console.log(`✓ ${ticketsUpserted} ticket(s) upserted`);
  console.log(`✓ ${pricingUpserted} activity_pricing scaffold row(s) ensured`);
  await ds.destroy();
}

main().catch((err) => {
  console.error(err);
  process.exit(1);
});