import { db } from '@server/db/drizzle';
import { sql, type SQL } from 'drizzle-orm';
import { initDatabase } from '@server/db/init';
import { ConflictError, NotFoundError, ValidationError } from '@server/errors';
import { bindValue } from '@server/db/bind';
import { dispatchWebhook } from './webhooks.service';
import { findAvailableTable, getTable } from './tables.service';

const BOOKING_SORT_COLS: Record<string, string> = {
  booking_date: 'b.booking_date',
  date: 'b.booking_date',
  status: 'b.status',
  guest_name: 'b.guest_name',
  created_at: 'b.created_at',
  start_at: 'b.start_at',
};

export interface BookingFilters {
  restaurantId: string;
  branchId?: string;
  customerId?: string;
  status?: string;
  date?: string;
  page?: number;
  limit?: number;
  search?: string;
  sortField?: string;
  sortDir?: string;
}

const PG_EXCLUSION_VIOLATION = '23P01';
const HHMM_RE  = /^(\d{1,2}):(\d{2})(?::(\d{2}))?$/;
const HH12_RE  = /^(\d{1,2}):(\d{2})\s*([aApP])[mM]?\.?$/;

const isPgExclusion = (err: unknown): boolean =>
  typeof err === 'object' && err !== null && (err as { code?: string }).code === PG_EXCLUSION_VIOLATION;

/** Parse "HH:MM[:SS]" (24h) or "H[H]:MM AM/PM" (12h) into "HH:MM:SS". */
export function parseBookingTime(timeStr: string | null | undefined): string | null {
  if (!timeStr) return null;
  const trimmed = String(timeStr).trim();

  let m = trimmed.match(HHMM_RE);
  if (m) {
    const hh = Number(m[1]);
    const mm = Number(m[2]);
    const ss = m[3] ? Number(m[3]) : 0;
    if (hh < 0 || hh > 23 || mm < 0 || mm > 59 || ss < 0 || ss > 59) return null;
    return `${String(hh).padStart(2, '0')}:${String(mm).padStart(2, '0')}:${String(ss).padStart(2, '0')}`;
  }

  m = trimmed.match(HH12_RE);
  if (m) {
    let hh = Number(m[1]);
    const mm = Number(m[2]);
    const isPm = m[3].toLowerCase() === 'p';
    if (hh < 1 || hh > 12 || mm < 0 || mm > 59) return null;
    if (hh === 12) hh = 0;
    if (isPm) hh += 12;
    return `${String(hh).padStart(2, '0')}:${String(mm).padStart(2, '0')}:00`;
  }

  return null;
}

function combineDateTime(dateStr: string | null | undefined, timeStr: string | null | undefined): Date | null {
  if (!dateStr || !timeStr) return null;
  const canonical = parseBookingTime(timeStr);
  if (!canonical) return null;
  const d = new Date(`${dateStr}T${canonical}`);
  return Number.isNaN(d.getTime()) ? null : d;
}

interface RestaurantDefaults {
  duration: number;
  buffer: number;
}

async function loadRestaurantDefaults(restaurantId: string): Promise<RestaurantDefaults> {
  const { rows } = await db.execute(sql`
    SELECT default_booking_duration_min AS duration,
           default_booking_buffer_min   AS buffer
      FROM restaurants
     WHERE id = ${restaurantId}
  `);
  const row = rows[0] as { duration?: number | string; buffer?: number | string } | undefined;
  return {
    duration: Math.max(15, Number(row?.duration ?? 90) || 90),
    buffer: Math.max(0, Number(row?.buffer ?? 15) || 15),
  };
}

// `message` is staff-facing (table #, booking ID, window, guest name);
// `publicMessage` is caller-safe (no PII) and used by voice/chat AI agents.
async function explainTableConflict(
  tableId: string,
  _branchId: string | null | undefined,
  startAt: Date,
  endAt: Date,
  excludeBookingId?: string
): Promise<ConflictError> {
  const conditions: SQL[] = [
    sql`b.table_id = ${tableId}`,
    sql`b.status NOT IN ('cancelled', 'noshow')`,
    sql`b.start_at IS NOT NULL`,
    sql`b.end_at IS NOT NULL`,
    sql`tstzrange(b.start_at, b.end_at + make_interval(mins => b.buffer_min), '[)')
        && tstzrange(${startAt.toISOString()}::timestamptz, ${endAt.toISOString()}::timestamptz, '[)')`,
  ];
  if (excludeBookingId) conditions.push(sql`b.id <> ${excludeBookingId}`);

  const { rows } = await db.execute(sql`
    SELECT b.id, b.guest_name, b.start_at, b.end_at, t.table_number
      FROM bookings b
      LEFT JOIN restaurant_tables t ON t.id = b.table_id
     WHERE ${sql.join(conditions, sql` AND `)}
     ORDER BY b.start_at ASC
     LIMIT 1
  `);
  const row = rows[0] as
    | { id?: string; guest_name?: string; start_at?: string; end_at?: string; table_number?: string }
    | undefined;

  const fmtIso = (d: string) => new Date(d).toISOString().slice(11, 16);
  const fmtDate = (d: Date) => d.toISOString().slice(11, 16);
  const reqWindow = `${fmtDate(startAt)}-${fmtDate(endAt)}`;

  if (row?.start_at && row?.end_at) {
    const tableLabel = row.table_number ? `Table ${row.table_number}` : 'this table';
    const idTag = row.id ? ` [#${String(row.id).slice(0, 8)}]` : '';
    const window = `${fmtIso(row.start_at)} to ${fmtIso(row.end_at)}`;
    const staff =
      `${tableLabel} is already booked${idTag} from ${window}` +
      (row.guest_name ? ` (${row.guest_name})` : '') +
      `. Pick another time or table.`;
    const publicMsg =
      `Sorry, that time isn't available — ${tableLabel} is already reserved from ${window}. ` +
      `Would you like to try a different time (for example, an hour earlier or later) or a different table?`;
    return new ConflictError(staff, publicMsg);
  }

  const generic =
    `Sorry, that time isn't available for the requested window (${reqWindow}). ` +
    `Would you like to try a different time or a different table?`;
  return new ConflictError(generic, generic);
}

export async function listBookings(filters: BookingFilters) {
  await initDatabase();
  const { restaurantId, branchId, customerId, status, date, search, page = 1, limit = 20, sortField, sortDir } = filters;
  const offset = (page - 1) * limit;
  const conditions: SQL[] = [sql`b.restaurant_id = ${restaurantId}`];

  if (branchId) conditions.push(sql`b.branch_id = ${branchId}`);
  if (customerId) conditions.push(sql`b.customer_id = ${customerId}`);
  if (status) conditions.push(sql`b.status = ${status}`);
  if (date) conditions.push(sql`b.booking_date = ${date}`);
  if (search) {
    const pattern = `%${search}%`;
    conditions.push(sql`(b.guest_name ILIKE ${pattern} OR b.guest_phone ILIKE ${pattern})`);
  }

  const where = sql.join(conditions, sql` AND `);

  /* raw: SELECT COUNT(*) FROM bookings b WHERE ... */
  const { rows: countRows } = await db.execute(sql`SELECT COUNT(*) FROM bookings b WHERE ${where}`);
  const total = parseInt((countRows[0] as { count: string })?.count ?? '0', 10);

  const orderCol = BOOKING_SORT_COLS[sortField ?? ''] ?? 'b.booking_date';
  const orderDir = sortDir === 'asc' ? 'ASC' : 'DESC';

  /* raw: SELECT b.* FROM bookings b WHERE ... ORDER BY ... LIMIT ... OFFSET ... */
  const { rows } = await db.execute(sql`
    SELECT b.* FROM bookings b
    WHERE ${where}
    ORDER BY ${sql.raw(orderCol)} ${sql.raw(orderDir)}, b.booking_time ${sql.raw(orderDir)}
    LIMIT ${limit} OFFSET ${offset}
  `);

  return { data: rows, total, page, limit, pages: Math.ceil(total / limit) };
}

export async function getBooking(id: string, restaurantId: string) {
  await initDatabase();
  /* raw: SELECT * FROM bookings WHERE id = $1 AND restaurant_id = $2 */
  const { rows } = await db.execute(sql`SELECT * FROM bookings WHERE id = ${id} AND restaurant_id = ${restaurantId}`);
  if (!rows[0]) throw new NotFoundError('Booking');
  return rows[0];
}

export async function createBooking(restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();

  const branchId = (data.branch_id as string | null | undefined) ?? null;
  const partySize = Math.max(1, Number(data.party_size ?? 1) || 1);

  const defaults = await loadRestaurantDefaults(restaurantId);
  const durationMin = Math.max(
    15,
    Number(data.duration_min ?? defaults.duration) || defaults.duration
  );
  const bufferMin = Math.max(
    0,
    Number(data.buffer_min ?? defaults.buffer) || defaults.buffer
  );

  // Explicit start_at (ISO) wins; otherwise combine date+time.
  let startAt: Date | null = null;
  if (data.start_at) {
    const d = new Date(String(data.start_at));
    startAt = Number.isNaN(d.getTime()) ? null : d;
  }
  if (!startAt) {
    startAt = combineDateTime(data.booking_date as string, data.booking_time as string);
  }
  const endAt = startAt ? new Date(startAt.getTime() + durationMin * 60_000) : null;

  // Mirror booking_date / booking_time from start_at so legacy list/sort/filter
  // paths (which still read those columns) stay coherent with timeline writes.
  let bookingDate = (data.booking_date as string | null | undefined) ?? null;
  let bookingTime = (data.booking_time as string | null | undefined) ?? null;
  if (startAt) {
    const iso = startAt.toISOString();
    if (!bookingDate) bookingDate = iso.slice(0, 10);
    if (!bookingTime) bookingTime = iso.slice(11, 16);
  }

  // Resolve table: explicit table_id, table_no (legacy), or auto-assign.
  let tableId: string | null = (data.table_id as string | null | undefined) ?? null;
  let tableNo: string | null = (data.table_no as string | null | undefined) ?? null;

  if (tableId) {
    try {
      const t = await getTable(tableId, restaurantId);
      tableNo = String((t as { table_number?: string }).table_number ?? tableNo ?? '');
    } catch {
      throw new ValidationError('Selected table not found for this restaurant');
    }
  } else if (branchId && startAt && endAt) {
    const auto = await findAvailableTable({
      restaurantId,
      branchId,
      partySize,
      startAt,
      endAt,
      bufferMin,
    });
    if (auto) {
      tableId = (auto as { id: string }).id;
      tableNo = String((auto as { table_number?: string }).table_number ?? tableNo ?? '');
    }
  }

  try {
    /* raw: INSERT INTO bookings (...) VALUES (...) RETURNING * */
    const { rows } = await db.execute(sql`
      INSERT INTO bookings (
        restaurant_id, branch_id, customer_id,
        guest_name, guest_phone, guest_email,
        table_no, table_id, party_size,
        booking_date, booking_time,
        start_at, end_at, duration_min, buffer_min,
        zone, status, channel, occasion,
        dietary_needs, special_requests, ai_notes, is_vip, notes
      )
      VALUES (
        ${restaurantId}, ${branchId}, ${(data.customer_id as string) ?? null},
        ${data.guest_name as string}, ${(data.guest_phone as string) ?? null}, ${(data.guest_email as string) ?? null},
        ${tableNo}, ${tableId}, ${partySize},
        ${bookingDate}, ${bookingTime},
        ${startAt ? startAt.toISOString() : null}, ${endAt ? endAt.toISOString() : null},
        ${durationMin}, ${bufferMin},
        ${(data.zone as string) ?? null},
        COALESCE(${(data.status as string) ?? null},'pending')::booking_status,
        ${(data.channel as string) ?? 'chat'}::channel_type,
        ${(data.occasion as string) ?? null},
        ${JSON.stringify(data.dietary_needs ?? [])}::jsonb,
        ${(data.special_requests as string) ?? null},
        ${(data.ai_notes as string) ?? null}, ${data.is_vip ?? false}, ${(data.notes as string) ?? null}
      )
      RETURNING *
    `);
    const row = rows[0];
    if (row) dispatchWebhook(restaurantId, 'booking.created', row as Record<string, unknown>);
    return row;
  } catch (err: unknown) {
    if (isPgExclusion(err) && tableId && startAt && endAt) {
      throw await explainTableConflict(tableId, branchId, startAt, endAt);
    }
    throw err;
  }
}

export async function updateBookingStatus(id: string, restaurantId: string, status: string, notes?: string) {
  await initDatabase();
  /* raw: UPDATE bookings SET status = $1, notes = COALESCE($4, notes), updated_at = NOW() WHERE id = $2 AND restaurant_id = $3 RETURNING * */
  const { rows } = await db.execute(sql`
    UPDATE bookings SET status = ${status}, notes = COALESCE(${notes ?? null}, notes), updated_at = NOW()
    WHERE id = ${id} AND restaurant_id = ${restaurantId} RETURNING *
  `);
  if (!rows[0]) throw new NotFoundError('Booking');
  const event = status === 'cancelled' ? 'booking.cancelled' : 'booking.updated';
  dispatchWebhook(restaurantId, event, rows[0] as Record<string, unknown>);
  return rows[0];
}

export async function updateBooking(id: string, restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();

  // Snapshot current row so we can recompute start_at / end_at when only one
  // of (booking_date, booking_time, duration_min) is being changed.
  const current = (await getBooking(id, restaurantId)) as Record<string, unknown>;

  const allowed = [
    'guest_name', 'guest_phone', 'guest_email',
    'table_no', 'table_id',
    'party_size',
    'booking_date', 'booking_time', 'start_at', 'end_at',
    'duration_min', 'buffer_min',
    'zone', 'status',
    'occasion', 'special_requests', 'notes', 'is_vip', 'reminder_sent',
  ];

  const sets: SQL[] = [];
  for (const [k, v] of Object.entries(data)) {
    if (!allowed.includes(k)) continue;
    // start_at / end_at handled below as a coordinated recompute.
    if (k === 'start_at' || k === 'end_at') continue;
    sets.push(sql`${sql.raw(k)} = ${bindValue(v)}`);
  }

  // Resolve final table_id and mirror table_no when the caller switched tables
  // by id (calendar drag) but didn't supply a new table_no.
  let finalTableId = (data.table_id as string | null | undefined);
  if (finalTableId === undefined) finalTableId = (current.table_id as string | null | undefined) ?? null;
  if ('table_id' in data && data.table_id) {
    try {
      const t = await getTable(String(data.table_id), restaurantId);
      const tn = String((t as { table_number?: string }).table_number ?? '');
      if (tn && !('table_no' in data)) sets.push(sql`table_no = ${tn}`);
    } catch {
      throw new ValidationError('Selected table not found for this restaurant');
    }
  } else if ('table_id' in data && data.table_id === null) {
    if (!('table_no' in data)) sets.push(sql`table_no = NULL`);
  }

  // Recompute start_at / end_at if any time-related field changed.
  const dateChanged = 'booking_date' in data;
  const timeChanged = 'booking_time' in data;
  const durChanged = 'duration_min' in data;
  const startChanged = 'start_at' in data;

  const finalDate = dateChanged ? (data.booking_date as string | null) : (current.booking_date as string | null);
  const finalTime = timeChanged ? (data.booking_time as string | null) : (current.booking_time as string | null);
  const finalDuration = Math.max(
    15,
    Number(durChanged ? data.duration_min : (current.duration_min ?? 90)) || 90
  );
  const finalBuffer = Math.max(
    0,
    Number('buffer_min' in data ? data.buffer_min : (current.buffer_min ?? 15)) || 15
  );

  let newStartAt: Date | null = null;
  if (startChanged && data.start_at) {
    const d = new Date(String(data.start_at));
    newStartAt = Number.isNaN(d.getTime()) ? null : d;
  } else if (dateChanged || timeChanged || durChanged) {
    newStartAt = combineDateTime(finalDate, finalTime);
  }

  let newEndAt: Date | null = null;
  if (newStartAt) {
    newEndAt = new Date(newStartAt.getTime() + finalDuration * 60_000);
    sets.push(sql`start_at = ${newStartAt.toISOString()}`);
    sets.push(sql`end_at = ${newEndAt.toISOString()}`);
    if (!durChanged) sets.push(sql`duration_min = ${finalDuration}`);
    // Mirror legacy columns so list/filter/sort paths stay coherent.
    if (!dateChanged) sets.push(sql`booking_date = ${newStartAt.toISOString().slice(0, 10)}`);
    if (!timeChanged) sets.push(sql`booking_time = ${newStartAt.toISOString().slice(11, 16)}`);
  }

  // For conflict explanation we need an effective window even when the caller
  // only changed table_id. Fall back to the row's existing start_at/end_at.
  const currStartRaw = current.start_at as string | Date | null;
  const currEndRaw   = current.end_at as string | Date | null;
  const effStartAt = newStartAt ?? (currStartRaw ? new Date(currStartRaw as string) : null);
  const effEndAt   = newEndAt   ?? (currEndRaw   ? new Date(currEndRaw   as string) : null);

  if (sets.length === 0) throw new ValidationError('No valid fields to update');
  sets.push(sql`updated_at = NOW()`);

  try {
    /* raw: UPDATE bookings SET ... WHERE id = $N AND restaurant_id = $N RETURNING * */
    const { rows } = await db.execute(sql`
      UPDATE bookings SET ${sql.join(sets, sql`, `)}
      WHERE id = ${id} AND restaurant_id = ${restaurantId} RETURNING *
    `);
    if (!rows[0]) throw new NotFoundError('Booking');
    dispatchWebhook(restaurantId, 'booking.updated', rows[0] as Record<string, unknown>);
    return rows[0];
  } catch (err: unknown) {
    if (isPgExclusion(err) && finalTableId && effStartAt && effEndAt) {
      throw await explainTableConflict(
        finalTableId,
        (current.branch_id as string | null) ?? null,
        effStartAt,
        effEndAt,
        id
      );
    }
    throw err;
  }
}

export async function cancelBooking(id: string, restaurantId: string) {
  return updateBookingStatus(id, restaurantId, 'cancelled');
}

export async function deleteBooking(id: string, restaurantId: string) {
  await initDatabase();
  /* raw: DELETE FROM bookings WHERE id = $1 AND restaurant_id = $2 */
  const result = await db.execute(sql`DELETE FROM bookings WHERE id = ${id} AND restaurant_id = ${restaurantId}`);
  if ((result.rowCount ?? 0) === 0) throw new NotFoundError('Booking');
}