import { env } from "@/lib/env";
import type { Kysely } from "kysely";
import { createViewpointDBPool } from "./index";
import type { ViewpointDB } from "./types";

export const KCGM_BOOKING_SOURCE = "KCGM";

export const TRACKED_BOOKING_SOURCES = [KCGM_BOOKING_SOURCE];

export interface KcgmBookingRow {
  booking_no: number;
  status: string | null;
  booking_source: string | null;
  booking_type: string | null;
  account_id: string | null;
  entitle_account_id: string | null;
  guest_name: string | null;
  resort_name: string | null;
  resort_country: string | null;
  room_type: string | null;
  arrival: Date | null;
  departure: Date | null;
  nights: number | null;
  adults: number | null;
  children: number | null;
  currency: string | null;
  folio_total: number | null;
  confirmed_date: Date | null;
  cancelled_date: Date | null;
  modify_date: Date | null;
}

let pool: Kysely<ViewpointDB> | null = null;

export const getViewpointDB = (): Kysely<ViewpointDB> => {
  if (pool) return pool;
  pool = createViewpointDBPool({
    host: env.GetString("VIEWPOINT_DATABASE_HOST"),
    port: env.GetInt("VIEWPOINT_DATABASE_PORT") || 1433,
    database: env.GetString("VIEWPOINT_DATABASE_NAME"),
    user: env.GetString("VIEWPOINT_DATABASE_USER"),
    password: env.GetString("VIEWPOINT_DATABASE_PWD"),
    maxConnections: env.GetInt("VIEWPOINT_DATABASE_MAX_CONNECTIONS") || 5,
    idleTimeout: env.GetInt("VIEWPOINT_DATABASE_IDLE_TIMEOUT") || 30000,
    connectionTimeout:
      env.GetInt("VIEWPOINT_DATABASE_CONNECTION_TIMEOUT") || 10000,
    requestTimeout: env.GetInt("VIEWPOINT_DATABASE_QUERY_TIMEOUT") || 30000,
    trustServerCertificate: readBoolEnv(
      "VIEWPOINT_DATABASE_TRUST_SERVER_CERT",
    ),
  });
  return pool;
};

const readBoolEnv = (key: string): boolean => {
  const value = process.env[key]?.trim().toLowerCase();
  return value === "true" || value === "1" || value === "yes";
};

export interface ListKcgmBookingsParams {
  arrivalFrom?: string;
  arrivalTo?: string;
  limit?: number;
  offset?: number;
}


export async function listKcgmBookings(
  db: Kysely<ViewpointDB>,
  params: ListKcgmBookingsParams = {},
): Promise<KcgmBookingRow[]> {
  const { arrivalFrom, arrivalTo, limit = 500, offset = 0 } = params;

  let query = db
    .selectFrom("vw_Reporting_BookingGuest_Build")
    .select([
      "bookingno as booking_no",
      "status",
      "bookingsource as booking_source",
      "bookingtype as booking_type",
      "accountid as account_id",
      "entitleaccountid as entitle_account_id",
      "GuestName as guest_name",
      "resortname as resort_name",
      "Resort Country as resort_country",
      "unittype as room_type",
      "arrival",
      "departure",
      "number_of_nights as nights",
      "adults",
      "children",
      "Charge Currency as currency",
      "Folio Total as folio_total",
      "Confirmed Date as confirmed_date",
      "Cancelled Date as cancelled_date",
      "Modify_Date as modify_date",
    ])
    .where("IsFirstRowOfBooking", "=", 1)
    .where("bookingsource", "in", TRACKED_BOOKING_SOURCES);

  if (arrivalFrom) query = query.where("arrival", ">=", new Date(arrivalFrom));
  if (arrivalTo) query = query.where("arrival", "<=", new Date(arrivalTo));

  return (await query
    .orderBy("arrival", "desc")
    .orderBy("bookingno", "desc")
    .offset(offset)
    .fetch(limit)
    .execute()) as KcgmBookingRow[];
}

export async function countKcgmBookings(
  db: Kysely<ViewpointDB>,
): Promise<number> {
  const row = await db
    .selectFrom("vw_Reporting_BookingGuest_Build")
    .select((eb) => eb.fn.countAll().as("total"))
    .where("IsFirstRowOfBooking", "=", 1)
    .where("bookingsource", "in", TRACKED_BOOKING_SOURCES)
    .executeTakeFirst();
  return Number(row?.total ?? 0);
}

export async function listBookingSources(
  db: Kysely<ViewpointDB>,
  limit = 60,
): Promise<Array<{ booking_source: string | null; bookings: number }>> {
  const rows = await db
    .selectFrom("vw_Reporting_BookingGuest_Build")
    .select((eb) => [
      "bookingsource as booking_source",
      eb.fn.countAll().as("bookings"),
    ])
    .where("IsFirstRowOfBooking", "=", 1)
    .groupBy("bookingsource")
    .top(limit)
    .orderBy("bookings", "desc")
    .execute();

  return rows.map((r) => ({
    booking_source: r.booking_source,
    bookings: Number(r.bookings),
  }));
}