import type { Kysely } from "kysely";
import { sql } from "kysely";
import type { ViewpointDB } from "./types";

const SUBITO_CREATE_USERS = new Set(["karma app", "karma website"]);

export type BookingOrigin = "subito" | "manual";

export const classifyOrigin = (createUser: string | null): BookingOrigin =>
  SUBITO_CREATE_USERS.has(
    (createUser ?? "").replace(/\s+/g, " ").trim().toLowerCase(),
  )
    ? "subito"
    : "manual";

export interface SignupBookingRow {
  booking_no: number;
  status: string | null;
  booking_source: string | null;
  booking_type: string | null;
  booking_channel: string | null;
  created_by: string | null;
  account_id: string | null;
  entitle_account_id: string | null;
  arriving_account_id: string | null;
  guest_name: string | null;
  resort_name: string | null;
  room_type: string | null;
  arrival: Date | null;
  departure: Date | null;
  adults: number | null;
  children: number | null;
  currency: string | null;
  folio_total: number | null;
  confirmed_date: Date | null;
  modify_date: Date | null;
}

export const MEMBERSHIP_CHUNK = 1500;

const ACCOUNT_COLUMNS = [
  "entitleaccountid",
  "arrivingaccountid",
  "accountid",
] as const;

export async function listBookingsForMemberships(
  db: Kysely<ViewpointDB>,
  memberships: string[],
): Promise<SignupBookingRow[]> {
  if (memberships.length === 0) return [];
  const list = sql.join(memberships.map((m) => sql`${m}`));
  const byBookingNo = new Map<number, SignupBookingRow>();

  for (const column of ACCOUNT_COLUMNS) {
    const rows = await sql<SignupBookingRow>`
      SELECT
        bookingno              AS booking_no,
        status,
        bookingsource          AS booking_source,
        bookingtype            AS booking_type,
        Booking_Channel        AS booking_channel,
        createuser             AS created_by,
        accountid              AS account_id,
        entitleaccountid       AS entitle_account_id,
        arrivingaccountid      AS arriving_account_id,
        GuestName              AS guest_name,
        resortname             AS resort_name,
        unittype               AS room_type,
        arrival,
        departure,
        adults,
        children,
        [Charge Currency]      AS currency,
        [Folio Total]          AS folio_total,
        [Confirmed Date]       AS confirmed_date,
        Modify_Date            AS modify_date
      FROM vw_Reporting_BookingGuest_Build
      WHERE IsFirstRowOfBooking = 1 AND ${sql.ref(column)} IN (${list})
    `.execute(db);

    for (const row of rows.rows) {
      if (!byBookingNo.has(row.booking_no)) byBookingNo.set(row.booking_no, row);
    }
  }

  return [...byBookingNo.values()];
}