import {
  BadRequestException,
  ConflictException,
  ForbiddenException,
  Injectable,
  Logger,
} from '@nestjs/common';
import { InjectDataSource } from '@nestjs/typeorm';
import { DataSource, EntityManager } from 'typeorm';

import {
  DEFAULT_SLOT_CONFIG,
  SessionBlock,
  SlotConfig,
  buildSlotGrid,
  isValidStartTime,
  nearestOpen,
  toHHMM,
  toLabel,
  toMinutes,
} from './audit-slot.rules';

// ═══════════════════════════════════════════════════════════════════════════
// SLOT SYSTEM CONFIG  (moved here from audit-requests.service.ts)
// ═══════════════════════════════════════════════════════════════════════════
export const SUBMISSION_WINDOW_DAYS = 4; // today … today+3 (inclusive)
export const CLOSED_DATES: string[] = [
  '2026-11-09',
  '2026-11-10',
  
  // '2026-12-25',
];
function isSunday(dateStr: string): boolean {
  return new Date(dateStr + 'T00:00:00Z').getUTCDay() === 0;
}
/** Timezone the office works in – used for "today" and "time already passed". */
export const SLOT_TIMEZONE = process.env.AUDIT_SLOT_TZ || 'Asia/Dubai';

/** Gap between two audits on the same seat: 30 or 60 minutes (env override). */
const envGap = Number(process.env.AUDIT_SLOT_GAP_MINUTES);
export const SLOT_CONFIG: SlotConfig = {
  ...DEFAULT_SLOT_CONFIG,
  gapMinutes: envGap === 30 || envGap === 60 ? envGap : DEFAULT_SLOT_CONFIG.gapMinutes,
};

// ═══════════════════════════════════════════════════════════════════════════
// 2026-11-10 — DYNAMIC SLOT SETTINGS (Settings → Audit Slots in the admin dashboard)
// Stored as one JSON row in `audit_slot_settings` (id = 1). The constants above
// (SUBMISSION_WINDOW_DAYS, CLOSED_DATES, SLOT_CONFIG, Sundays closed) stay as the
// FALLBACK: while the table is missing or empty, the system behaves exactly as before.
// ═══════════════════════════════════════════════════════════════════════════
export interface ClosedDate {
  date: string; // YYYY-MM-DD
  reason?: string;
}
export interface SlotSettings {
  slots_per_time: number; // audits that may start at the same time (parallel auditors)
  max_per_day: number; // hard cap of audits per date
  gap_minutes: number; // 30 or 60
  day_start: string; // "09:00"
  last_start: string; // "17:30"
  lunch_from: string; // "13:00"
  lunch_to: string; // "14:00"
  window_days: number; // today … today + N
  closed_weekdays: number[]; // 0 = Sunday … 6 = Saturday
  closed_dates: ClosedDate[];
}
interface SlotRuntime {
  cfg: SlotConfig;
  windowDays: number;
  closedWeekdays: number[];
  closedDates: ClosedDate[];
}
const WEEKDAY_NAMES = ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'];
const SETTINGS_ADMIN_IDS = [1, 8, 29];
const SETTINGS_ADMIN_ROLES = ['admin', 'superadmin', 'super-admin', 'scheme', 'coordinator'];

/** Settings equal to today's hard-coded behaviour. */
export function defaultSlotSettings(): SlotSettings {
  return {
    slots_per_time: SLOT_CONFIG.slotsPerTime,
    max_per_day: SLOT_CONFIG.maxPerDay,
    gap_minutes: SLOT_CONFIG.gapMinutes,
    day_start: toHHMM(SLOT_CONFIG.dayStartMin),
    last_start: toHHMM(SLOT_CONFIG.lastStartMin),
    lunch_from: toHHMM(SLOT_CONFIG.lunch.fromMin),
    lunch_to: toHHMM(SLOT_CONFIG.lunch.toMin),
    window_days: SUBMISSION_WINDOW_DAYS,
    closed_weekdays: [0],
    closed_dates: CLOSED_DATES.map((date) => ({ date })),
  };
}

// ─── types returned to the frontend ────────────────────────────────────────
export interface DateSummary {
  date: string;
  booked: number; // audits holding capacity that day
  available: number; // max_per_day − booked
  free_slots: number; // how many start times can still be picked
  status: 'open' | 'full' | 'closed';
  closed_reason?: string; // 2026-11-10
}

export interface DaySlotsResponse {
  date: string;
  status: 'open' | 'full' | 'closed' | 'outside_window';
  closed_reason?: string; // 2026-11-10
  gap_minutes: number;
  slots_per_time: number;
  max_per_day: number;
  booked: number;
  lunch: { from: string; to: string };
  sessions: SessionBlock[];
  /** Only filled for users who may see everybody's requests. */
  bookings?: { time: string | null; label: string | null; company: string; source: string }[];
  server_time: string; // ISO, lets the UI show "updated hh:mm"
}

interface Occupant {
  date: string;
  min: number | null; // null = audit has no time yet (counts for the day cap only)
  company: string;
  source: 'request' | 'schedule' | 'hold';
}

export interface SlotCheckOptions {
  /** Ignore this request (editing it must not conflict with itself). */
  excludeRequestId?: number;
  /** Start times ("HH:MM") already chosen by sibling cards of the same batch on this date. */
  holds?: string[];
}

@Injectable()
export class AuditSlotsService {
  private readonly logger = new Logger('AuditSlotsService');

  constructor(
    @InjectDataSource('scheme_dbs')
    private readonly dataSource: DataSource,
  ) {}

  // ───────────────────────────────────────────────────────────────────────
  // 2026-11-10 — dynamic settings (cached 30 s, falls back to the constants)
  // ───────────────────────────────────────────────────────────────────────
  private runtimeCache: { at: number; rt: SlotRuntime } | null = null;
  private tableMissingLogged = false;

  private toRuntime(st: SlotSettings): SlotRuntime {
    return {
      cfg: {
        ...SLOT_CONFIG,
        slotsPerTime: st.slots_per_time,
        maxPerDay: st.max_per_day,
        gapMinutes: st.gap_minutes,
        dayStartMin: toMinutes(st.day_start)!,
        lastStartMin: toMinutes(st.last_start)!,
        lunch: { fromMin: toMinutes(st.lunch_from)!, toMin: toMinutes(st.lunch_to)! },
      },
      windowDays: st.window_days,
      closedWeekdays: st.closed_weekdays,
      closedDates: st.closed_dates,
    };
  }

  /** Saved settings merged over the defaults; null source = nothing saved yet. */
  private async readSettings(): Promise<{ settings: SlotSettings; saved: boolean; updated_at: string | null; updated_by: string | null }> {
    const defaults = defaultSlotSettings();
    try {
      const rows: any[] = await this.dataSource.query(
        `SELECT s.config, s.updated_at, TRIM(CONCAT(COALESCE(u.firstName,''),' ',COALESCE(u.lastName,''))) AS by_name
           FROM audit_slot_settings s LEFT JOIN users u ON u.id = s.updated_by
          WHERE s.id = 1 LIMIT 1`,
      );
      if (!rows.length) return { settings: defaults, saved: false, updated_at: null, updated_by: null };
      const raw = typeof rows[0].config === 'string' ? JSON.parse(rows[0].config) : rows[0].config;
      const merged = { ...defaults, ...(raw || {}) } as SlotSettings;
      this.validateSettings(merged); // a broken row must never break bookings
      return { settings: merged, saved: true, updated_at: rows[0].updated_at ?? null, updated_by: rows[0].by_name || null };
    } catch (e: any) {
      if (!this.tableMissingLogged) {
        this.logger.warn(`[SLOTS] using built-in slot settings (${e?.message})`);
        this.tableMissingLogged = true;
      }
      return { settings: defaults, saved: false, updated_at: null, updated_by: null };
    }
  }

  private async runtime(): Promise<SlotRuntime> {
    if (this.runtimeCache && Date.now() - this.runtimeCache.at < 30_000) return this.runtimeCache.rt;
    const { settings } = await this.readSettings();
    const rt = this.toRuntime(settings);
    this.runtimeCache = { at: Date.now(), rt };
    return rt;
  }

  private closedInfo(date: string, rt: SlotRuntime): { closed: boolean; reason?: string } {
    const hit = rt.closedDates.find((c) => c.date === date);
    if (hit) return { closed: true, reason: hit.reason || 'Closed' };
    const wd = new Date(date + 'T00:00:00Z').getUTCDay();
    if (rt.closedWeekdays.includes(wd)) return { closed: true, reason: `${WEEKDAY_NAMES[wd]} — closed` };
    return { closed: false };
  }

  async canEditSettings(userId: number): Promise<boolean> {
    if (SETTINGS_ADMIN_IDS.includes(Number(userId))) return true;
    try {
      const rows: { r: string }[] = await this.dataSource.query(
        `SELECT LOWER(r.name) AS r FROM user_roles ur JOIN roles r ON r.id = ur.role_id WHERE ur.user_id = ?`,
        [userId],
      );
      return rows.some((x) => SETTINGS_ADMIN_ROLES.includes(x.r));
    } catch {
      return false;
    }
  }

  /** GET /audit-requests/slot-settings */
  async getSettings(userId: number) {
    const r = await this.readSettings();
    return {
      ...r,
      defaults: defaultSlotSettings(),
      can_edit: await this.canEditSettings(userId),
      timezone: SLOT_TIMEZONE,
      today: this.now().date,
    };
  }

  /** PUT /audit-requests/slot-settings */
  async saveSettings(body: Partial<SlotSettings>, userId: number) {
    if (!(await this.canEditSettings(userId))) {
      throw new ForbiddenException('Only admin / coordinator can change audit slot settings.');
    }
    const current = (await this.readSettings()).settings;
    const next: SlotSettings = {
      ...current,
      ...body,
      closed_weekdays: Array.isArray(body.closed_weekdays) ? body.closed_weekdays : current.closed_weekdays,
      closed_dates: Array.isArray(body.closed_dates) ? body.closed_dates : current.closed_dates,
    };
    next.closed_weekdays = [...new Set(next.closed_weekdays.map(Number))].sort();
    const seen = new Set<string>();
    next.closed_dates = next.closed_dates
      .map((c) => ({ date: String(c.date || '').slice(0, 10), reason: String(c.reason || '').trim().slice(0, 200) || undefined }))
      .filter((c) => (seen.has(c.date) ? false : (seen.add(c.date), true)))
      .sort((a, b) => a.date.localeCompare(b.date));
    this.validateSettings(next);

    try {
      await this.dataSource.query(
        `INSERT INTO audit_slot_settings (id, config, updated_by, updated_at) VALUES (1, ?, ?, NOW())
         ON DUPLICATE KEY UPDATE config = VALUES(config), updated_by = VALUES(updated_by), updated_at = NOW()`,
        [JSON.stringify(next), userId],
      );
    } catch (e: any) {
      if (/doesn't exist|ER_NO_SUCH_TABLE/i.test(e?.message || '')) {
        throw new BadRequestException(
          'The audit_slot_settings table has not been created yet. Run backend/src/audit-requests/migrations/run-slot-settings.js once on the server.',
        );
      }
      throw e;
    }
    this.runtimeCache = null; // apply immediately
    this.logger.log(`[SLOTS] settings saved by user ${userId}: ${JSON.stringify(next)}`);
    return this.getSettings(userId);
  }

  private validateSettings(s: SlotSettings) {
    const int = (v: any, lo: number, hi: number, name: string) => {
      if (!Number.isInteger(Number(v)) || Number(v) < lo || Number(v) > hi) {
        throw new BadRequestException(`${name} must be a whole number between ${lo} and ${hi}.`);
      }
    };
    int(s.slots_per_time, 1, 20, 'Slots per time');
    int(s.max_per_day, 1, 100, 'Max audits per day');
    int(s.window_days, 0, 60, 'Booking window (days)');
    if (![30, 60].includes(Number(s.gap_minutes))) throw new BadRequestException('Gap must be 30 or 60 minutes.');
    s.slots_per_time = Number(s.slots_per_time);
    s.max_per_day = Number(s.max_per_day);
    s.window_days = Number(s.window_days);
    s.gap_minutes = Number(s.gap_minutes);
    const t = (v: string, name: string) => {
      const m = toMinutes(v);
      if (m === null || m % 30 !== 0) throw new BadRequestException(`${name} must be a time on the hour or half hour (HH:MM).`);
      return m;
    };
    const ds = t(s.day_start, 'First start time');
    const ls = t(s.last_start, 'Last start time');
    const lf = t(s.lunch_from, 'Lunch from');
    const lt = t(s.lunch_to, 'Lunch to');
    if (ls < ds) throw new BadRequestException('Last start time must be after the first start time.');
    if (lt < lf) throw new BadRequestException('Lunch end must be after lunch start.');
    s.day_start = toHHMM(ds);
    s.last_start = toHHMM(ls);
    s.lunch_from = toHHMM(lf);
    s.lunch_to = toHHMM(lt);
    if (!Array.isArray(s.closed_weekdays) || s.closed_weekdays.some((d) => !Number.isInteger(Number(d)) || Number(d) < 0 || Number(d) > 6)) {
      throw new BadRequestException('Closed weekdays must be numbers 0 (Sunday) to 6 (Saturday).');
    }
    if (s.closed_weekdays.length >= 7) throw new BadRequestException('At least one weekday must stay open.');
    if (!Array.isArray(s.closed_dates)) throw new BadRequestException('Closed dates must be a list.');
    for (const c of s.closed_dates) {
      if (!/^\d{4}-\d{2}-\d{2}$/.test(String(c?.date)) || Number.isNaN(Date.parse(c.date + 'T00:00:00Z'))) {
        throw new BadRequestException(`Invalid closed date "${c?.date}" (use YYYY-MM-DD).`);
      }
    }
    if (s.closed_dates.length > 500) throw new BadRequestException('Too many closed dates (max 500).');
  }

  // ───────────────────────────────────────────────────────────────────────
  // date helpers (timezone-safe, string based)
  // ───────────────────────────────────────────────────────────────────────
  /** Current date + minutes-from-midnight in the office timezone. */
  now(): { date: string; min: number } {
    const parts = new Intl.DateTimeFormat('en-CA', {
      timeZone: SLOT_TIMEZONE,
      year: 'numeric',
      month: '2-digit',
      day: '2-digit',
      hour: '2-digit',
      minute: '2-digit',
      hour12: false,
    }).formatToParts(new Date());
    const get = (t: string) => parts.find((p) => p.type === t)!.value;
    const hour = Number(get('hour')) % 24; // some engines return "24" at midnight
    return {
      date: `${get('year')}-${get('month')}-${get('day')}`,
      min: hour * 60 + Number(get('minute')),
    };
  }

  addDays(date: string, n: number): string {
    const d = new Date(date + 'T00:00:00Z');
    d.setUTCDate(d.getUTCDate() + n);
    return d.toISOString().slice(0, 10);
  }

  /** 2026-11-10: window from the dynamic settings (closed weekdays left out, like Sundays were). */
  private windowDatesRt(rt: SlotRuntime): string[] {
    const today = this.now().date;
    return Array.from({ length: rt.windowDays + 1 }, (_, i) => this.addDays(today, i)).filter(
      (d) => !rt.closedWeekdays.includes(new Date(d + 'T00:00:00Z').getUTCDay()),
    );
  }

  /** 2026-11-10: last bookable date, pushed past closed weekdays (as the old code did for Sundays). */
  private windowEndRt(rt: SlotRuntime): string {
    let maxDate = this.addDays(this.now().date, rt.windowDays);
    let guard = 0;
    while (rt.closedWeekdays.includes(new Date(maxDate + 'T00:00:00Z').getUTCDay()) && guard++ < 7) {
      maxDate = this.addDays(maxDate, 1);
    }
    return maxDate;
  }

  private inWindowRt(date: string, rt: SlotRuntime): boolean {
    if (rt.closedWeekdays.includes(new Date(date + 'T00:00:00Z').getUTCDay())) return false;
    return date >= this.now().date && date <= this.windowEndRt(rt);
  }

  // (original, static versions — kept)
    private windowDates(): string[] {
    const today = this.now().date;
    return Array.from({ length: SUBMISSION_WINDOW_DAYS + 1 }, (_, i) => this.addDays(today, i))
      // 🆕 Filter out Sundays from the date window
      .filter((d) => !isSunday(d));
  }

  private inWindow(date: string): boolean {
    const today = this.now().date;
    // 🆕 If it's Sunday, it's never in the window
    if (isSunday(date)) return false;
    
    // Find the actual last day of the window (skipping Sundays if they fall on the last day)
    let maxDate = this.addDays(today, SUBMISSION_WINDOW_DAYS);
    while (isSunday(maxDate)) {
      maxDate = this.addDays(maxDate, 1); // extend by 1 day if the window ends on a Sunday
    }
    return date >= today && date <= maxDate;
  }

  // ───────────────────────────────────────────────────────────────────────
  // OCCUPANCY — the single source of truth for "who holds capacity".
  //
  //  • A request that is NOT yet linked to a schedule row holds capacity at
  //    its proposed date/time (unless CANCELLED / REJECTED).
  //  • Once linked, the SCHEDULE ROW is the truth – so a coordinator who
  //    reschedules moves the slot, and one who CANCELS the row frees it,
  //    even though audit_requests.status stays SCHEDULED.
  //  • Rows created directly in the schedule module (no request) also
  //    consume auditor capacity, so they count too.
  // ───────────────────────────────────────────────────────────────────────
  private async loadOccupancy(
    dates: string[],
    excludeRequestId?: number,
    manager?: EntityManager,
  ): Promise<Occupant[]> {
    if (!dates.length) return [];
    const run = (sql: string, params: any[]) =>
      (manager ?? this.dataSource).query(sql, params) as Promise<any[]>;
    const ph = dates.map(() => '?').join(',');

    const reqParams: any[] = [...dates];
    let reqExtra = '';
    if (excludeRequestId) {
      reqExtra = ' AND ar.id <> ?';
      reqParams.push(excludeRequestId);
    }
    const requests = await run(
      `SELECT DATE_FORMAT(ar.proposed_date, '%Y-%m-%d') AS d,
              TIME_FORMAT(ar.proposed_time, '%H:%i')    AS t,
              ar.company_name                           AS company
         FROM audit_requests ar
        WHERE ar.proposed_date IN (${ph})
          AND ar.status NOT IN ('CANCELLED', 'REJECTED')
          AND ar.audit_schedule_row_id IS NULL${reqExtra}`,
      reqParams,
    );

    const rowParams: any[] = [...dates];
    let rowExtra = '';
    if (excludeRequestId) {
      rowExtra = ' AND (asr.audit_request_id IS NULL OR asr.audit_request_id <> ?)';
      rowParams.push(excludeRequestId);
    }
    const rows = await run(
      `SELECT DATE_FORMAT(sch.schedule_date, '%Y-%m-%d') AS d,
              TIME_FORMAT(asr.audit_time, '%H:%i')       AS t,
              ar.company_name                            AS company
         FROM audit_schedule_rows asr
         JOIN audit_schedules sch    ON sch.id = asr.schedule_id
         LEFT JOIN audit_requests ar ON ar.id  = asr.audit_request_id
        WHERE sch.schedule_date IN (${ph})
          AND sch.status <> 'CANCELLED'
          AND asr.status <> 'CANCELLED'${rowExtra}`,
      rowParams,
    );

    return [
      ...requests.map((r) => ({
        date: r.d as string,
        min: toMinutes(r.t),
        company: (r.company as string) || 'Audit request',
        source: 'request' as const,
      })),
      ...rows.map((r) => ({
        date: r.d as string,
        min: toMinutes(r.t),
        company: (r.company as string) || 'Scheduled audit',
        source: 'schedule' as const,
      })),
    ];
  }

  private holdsToOccupants(date: string, holds?: string[]): Occupant[] {
    return (holds ?? [])
      .map((h) => toMinutes(h))
      .filter((m): m is number => m !== null)
      .map((min) => ({ date, min, company: 'Another client in this batch', source: 'hold' as const }));
  }

  // ───────────────────────────────────────────────────────────────────────
  // READ: the tiles above the time field  (GET /audit-requests/available-slots)
  // ───────────────────────────────────────────────────────────────────────
  async getDateSummaries(): Promise<{
    window_start: string;
    window_end: string;
    max_per_day: number;
    slots_per_time: number;
    gap_minutes: number;
    dates: DateSummary[];
  }> {
    const rt = await this.runtime(); // 2026-11-10: dynamic settings
    const cfg = rt.cfg;
    const dates = this.windowDatesRt(rt);
    const occupancy = await this.loadOccupancy(dates);
    const now = this.now();

    const summaries: DateSummary[] = dates.map((date) => {
      const day = occupancy.filter((o) => o.date === date);
      const booked = day.length;
      const ci = this.closedInfo(date, rt);
      if (ci.closed) {
        return { date, booked, available: 0, free_slots: 0, status: 'closed' as const, closed_reason: ci.reason };
      }
      const grid = buildSlotGrid({
        booked: day.map((o) => o.min).filter((m): m is number => m !== null),
        dayCount: booked,
        dateClosed: false,
        outsideWindow: false,
        notBeforeMin: date === now.date ? now.min : null,
        cfg,
      });
      const freeSlots = grid.flatMap((s) => s.slots).filter((c) => c.free > 0).length;
      return {
        date,
        booked,
        available: Math.max(0, cfg.maxPerDay - booked),
        free_slots: freeSlots,
        status: freeSlots > 0 ? ('open' as const) : ('full' as const),
      };
    });

    return {
      window_start: dates[0],
      window_end: dates[dates.length - 1],
      max_per_day: cfg.maxPerDay,
      slots_per_time: cfg.slotsPerTime,
      gap_minutes: cfg.gapMinutes,
      dates: summaries,
    };
  }

  // ───────────────────────────────────────────────────────────────────────
  // READ: the time grid  (GET /audit-requests/available-slots/:date)
  // ───────────────────────────────────────────────────────────────────────
  async getDaySlots(
    date: string,
    opts: SlotCheckOptions & { includeBookings?: boolean } = {},
  ): Promise<DaySlotsResponse> {
    this.assertDateFormat(date);
    const occupancy = [
      ...(await this.loadOccupancy([date], opts.excludeRequestId)),
      ...this.holdsToOccupants(date, opts.holds),
    ];
    const now = this.now();
    const rt = await this.runtime(); // 2026-11-10: dynamic settings
    const cfg = rt.cfg;
    const ci = this.closedInfo(date, rt);
    const closed = ci.closed;
    const outside = !closed && !this.inWindowRt(date, rt);
    const booked = occupancy.length;

    const sessions = buildSlotGrid({
      booked: occupancy.map((o) => o.min).filter((m): m is number => m !== null),
      dayCount: booked,
      dateClosed: closed,
      outsideWindow: outside,
      notBeforeMin: date === now.date ? now.min : null,
      cfg,
    });
    const anyOpen = sessions.some((s) => s.slots.some((c) => c.free > 0));

    const res: DaySlotsResponse = {
      date,
      status: closed ? 'closed' : outside ? 'outside_window' : anyOpen ? 'open' : 'full',
      closed_reason: ci.reason,
      gap_minutes: cfg.gapMinutes,
      slots_per_time: cfg.slotsPerTime,
      max_per_day: cfg.maxPerDay,
      booked,
      lunch: {
        from: toLabel(cfg.lunch.fromMin),
        to: toLabel(cfg.lunch.toMin),
      },
      sessions,
      server_time: new Date().toISOString(),
    };

    if (opts.includeBookings) {
      res.bookings = occupancy
        .filter((o) => o.source !== 'hold')
        .sort((a, b) => (a.min ?? 9999) - (b.min ?? 9999))
        .map((o) => ({
          time: o.min === null ? null : toHHMM(o.min),
          label: o.min === null ? null : toLabel(o.min),
          company: o.company,
          source: o.source,
        }));
    }
    return res;
  }

  // ───────────────────────────────────────────────────────────────────────
  // WRITE-SIDE VALIDATION — call inside withDateLock() so it cannot race.
  // Replaces the old day-only validateSlotAvailability().
  // ───────────────────────────────────────────────────────────────────────
  async assertSlotAvailable(
    date: string,
    time: string,
    manager?: EntityManager,
    opts: SlotCheckOptions = {},
  ): Promise<string> {
    this.assertDateFormat(date);
    const now = this.now();
    const rt = await this.runtime(); // 2026-11-10: dynamic settings
    const cfg = rt.cfg;

    if (date < now.date) {
      throw new BadRequestException(`Cannot submit audit request for a past date (${date}).`);
    }
    const ci = this.closedInfo(date, rt);
    if (ci.closed) {
      throw new BadRequestException(
        `Slot closed for ${date}${ci.reason && ci.reason !== 'Closed' ? ` (${ci.reason})` : ''}. Please choose another date.`,
      );
    }
    if (!this.inWindowRt(date, rt)) {
      throw new BadRequestException(
        `Proposed date ${date} is outside the submission window. You can only submit for today up to ${this.windowEndRt(rt)}.`,
      );
    }

    const t = toMinutes(time);
    if (!isValidStartTime(t, cfg)) {
      throw new BadRequestException(
        `Please pick a time slot between ${toLabel(cfg.dayStartMin)} and ${toLabel(cfg.lastStartMin)} (every ${cfg.stepMin} minutes, not during the ${toLabel(cfg.lunch.fromMin)}–${toLabel(cfg.lunch.toMin)} lunch break).`,
      );
    }
    if (date === now.date && t! <= now.min) {
      throw new BadRequestException(`${toLabel(t!)} has already passed today. Please pick a later time.`);
    }

    const occupancy = [
      ...(await this.loadOccupancy([date], opts.excludeRequestId, manager)),
      ...this.holdsToOccupants(date, opts.holds),
    ];
    const sessions = buildSlotGrid({
      booked: occupancy.map((o) => o.min).filter((m): m is number => m !== null),
      dayCount: occupancy.length,
      dateClosed: false,
      outsideWindow: false,
      notBeforeMin: date === now.date ? now.min : null,
      cfg,
    });
    const cell = sessions.flatMap((s) => s.slots).find((c) => c.time === toHHMM(t!))!;

    if (cell.free === 0) {
      const alternatives = nearestOpen(sessions, cell.time, 3);
      const why =
        cell.reason === 'Day full'
          ? `${date} has reached the daily limit of ${cfg.maxPerDay} audits.`
          : cell.reason === 'Fully booked'
            ? `${cell.label} is fully booked – all ${cfg.slotsPerTime} audit slot${cfg.slotsPerTime === 1 ? ' is' : 's are'} taken.`
            : `${cell.label} is too close to another audit (minimum gap ${cfg.gapMinutes} minutes).`;
      throw new ConflictException({
        statusCode: 409,
        code: 'SLOT_TAKEN',
        message: `${why} Please choose another time.${
          alternatives.length
            ? ` Nearest open: ${alternatives.map((a) => toLabel(toMinutes(a)!)).join(', ')}.`
            : ''
        }`,
        alternatives,
      });
    }
    return `${cell.time}:00`; // normalised value to store in the TIME column
  }

  /**
   * Pre-flight check for a whole batch BEFORE anything is written, so a
   * clash on client #3 doesn't leave clients #1-2 half-created.
   * Clients are checked in order; each one "holds" its slot for the next.
   */
  async assertBatchAvailable(items: { company_name: string; proposed_date: string; proposed_time: string }[]) {
    const held = new Map<string, string[]>();
    for (const [i, c] of items.entries()) {
      try {
        const normalised = await this.assertSlotAvailable(c.proposed_date, c.proposed_time, undefined, {
          holds: held.get(c.proposed_date),
        });
        held.set(c.proposed_date, [...(held.get(c.proposed_date) ?? []), normalised.slice(0, 5)]);
      } catch (e: any) {
        const prefix = `Client ${i + 1} (${c.company_name}): `;
        if (e instanceof ConflictException) {
          const body = e.getResponse() as Record<string, any>;
          throw new ConflictException({ ...body, message: prefix + body.message });
        }
        if (e instanceof BadRequestException) {
          const body = e.getResponse() as any;
          throw new BadRequestException(prefix + (body?.message ?? e.message));
        }
        throw e;
      }
    }
  }

  // ───────────────────────────────────────────────────────────────────────
  // CONCURRENCY — serialise all writers for the same calendar date.
  // Two marketing users clicking the last open slot at the same second must
  // not both succeed. Uses a MySQL/MariaDB named lock (GET_LOCK) taken on the
  // SAME connection that runs the transaction, *before* the transaction's
  // first read, so the occupancy query always sees the latest commits.
  // ───────────────────────────────────────────────────────────────────────
  async withDateLock<T>(date: string, fn: (manager: EntityManager) => Promise<T>): Promise<T> {
    this.assertDateFormat(date);
    const qr = this.dataSource.createQueryRunner();
    await qr.connect();
    const lockName = `audit_slot:${date}`;
    let locked = false;
    try {
      const r = await qr.query('SELECT GET_LOCK(?, 10) AS ok', [lockName]);
      if (Number(r?.[0]?.ok) !== 1) {
        throw new ConflictException(
          'Another booking for this date is being saved. Please try again in a moment.',
        );
      }
      locked = true;
      await qr.startTransaction();
      try {
        const out = await fn(qr.manager);
        await qr.commitTransaction();
        return out;
      } catch (err) {
        await qr.rollbackTransaction();
        throw err;
      }
    } finally {
      if (locked) {
        try {
          await qr.query('SELECT RELEASE_LOCK(?)', [lockName]);
        } catch (e: any) {
          this.logger.warn(`RELEASE_LOCK failed for ${lockName}: ${e?.message}`);
        }
      }
      await qr.release();
    }
  }

  private assertDateFormat(date: string) {
    if (!/^\d{4}-\d{2}-\d{2}$/.test(date) || Number.isNaN(Date.parse(date + 'T00:00:00Z'))) {
      throw new BadRequestException('date must be in YYYY-MM-DD format');
    }
  }
}