import { logError, logInfo } from "@/lib/logger";
import { NOT_UPGRADED } from "@/internal/repository/promo-code-campaigns/promo-code-signups.repo";
import type { Kysely } from "kysely";
import { sql } from "kysely";
import {
  classifyOrigin,
  listBookingsForMemberships,
  MEMBERSHIP_CHUNK,
} from "./signup-bookings.repo";
import type { ViewpointDB } from "./types";

export const SOURCE_OF_TRUTH_DB = "db";

export interface SignupSyncOutcome {
  memberships: number;
  read: number;
  written: number;
  failed: number;
  subito: number;
  manual: number;
  pruned: number;
  errors: Array<{ bookingNo: string; message: string }>;
}

export interface SignupSyncOptions {
  prune?: boolean;
}

type PromoCodeByMembership = Map<string, string>;

async function loadSignupMemberships(
  memberDb: Kysely<any>,
): Promise<PromoCodeByMembership> {
  const result = (await sql<{ membership: string; code: string }>`
    SELECT UPPER(BTRIM(members.membership_number)) AS membership,
           UPPER(BTRIM(members.signup_promo_code)) AS code
    FROM members
    WHERE COALESCE(BTRIM(members.signup_promo_code), '') <> ''
      AND COALESCE(BTRIM(members.membership_number), '') <> ''
      AND ${sql.raw(NOT_UPGRADED)}
    GROUP BY 1, 2
  `.execute(memberDb)) as unknown as {
    rows: Array<{ membership: string; code: string }>;
  };

  const byMembership: PromoCodeByMembership = new Map();
  const ambiguous = new Set<string>();
  for (const row of result.rows) {
    const existing = byMembership.get(row.membership);
    if (existing && existing !== row.code) ambiguous.add(row.membership);
    byMembership.set(row.membership, row.code);
  }
  for (const membership of ambiguous) byMembership.set(membership, "");

  if (ambiguous.size) {
    logInfo(
      `[SignupBookingSync] ${ambiguous.size} memberships map to more than one promo code; left unattributed`,
    );
  }
  return byMembership;
}

const toDateOnly = (value: Date | null): string | null =>
  value ? value.toISOString().slice(0, 10) : null;

const UPSERT_COLUMNS = [
  "status",
  "booking_source",
  "booking_type",
  "booking_channel",
  "created_by",
  "booking_origin",
  "signup_promo_code",
  "account_id",
  "membership_number",
  "guest_name",
  "resort_name",
  "room_type",
  "arrival",
  "departure",
  "adults",
  "children",
  "booking_date",
  "total_amount",
  "currency",
  "modify_date_time",
  "source_of_truth",
  "last_synced_at",
] as const;

export async function syncSignupMemberBookings(
  db: Kysely<any>,
  memberDb: Kysely<any>,
  viewpointDb: Kysely<ViewpointDB>,
  options: SignupSyncOptions = {},
): Promise<SignupSyncOutcome> {
  const outcome: SignupSyncOutcome = {
    memberships: 0,
    read: 0,
    written: 0,
    failed: 0,
    subito: 0,
    manual: 0,
    pruned: 0,
    errors: [],
  };

  const runStartedAt = new Date();

  const promoByMembership = await loadSignupMemberships(memberDb);
  const memberships = [...promoByMembership.keys()];
  outcome.memberships = memberships.length;
  if (memberships.length === 0) return outcome;

  const seen = new Set<number>();

  for (let start = 0; start < memberships.length; start += MEMBERSHIP_CHUNK) {
    const chunk = memberships.slice(start, start + MEMBERSHIP_CHUNK);
    const rows = await listBookingsForMemberships(viewpointDb, chunk);

    for (const row of rows) {
      if (seen.has(row.booking_no)) continue;
      seen.add(row.booking_no);
      outcome.read += 1;

      const origin = classifyOrigin(row.created_by);
      if (origin === "subito") outcome.subito += 1;
      else outcome.manual += 1;

      const membership =
        [row.entitle_account_id, row.arriving_account_id, row.account_id]
          .map((v) => (v ?? "").trim().toUpperCase())
          .find((v) => v && promoByMembership.has(v)) ?? null;

      const bookingNo = String(row.booking_no);
      try {
        await db
          .insertInto("viewpoint_bookings")
          .values({
            booking_no: bookingNo,
            status: row.status,
            booking_source: row.booking_source,
            booking_type: row.booking_type,
            booking_channel: row.booking_channel,
            created_by: row.created_by,
            booking_origin: origin,
            signup_promo_code: membership
              ? promoByMembership.get(membership) || null
              : null,
            account_id: row.account_id,
            membership_number: membership,
            guest_name: row.guest_name,
            resort_name: row.resort_name,
            room_type: row.room_type,
            arrival: toDateOnly(row.arrival),
            departure: toDateOnly(row.departure),
            adults: row.adults,
            children: row.children,
            booking_date: toDateOnly(row.confirmed_date),
            total_amount: row.folio_total,
            currency: row.currency,
            modify_date_time: row.modify_date,
            source_of_truth: SOURCE_OF_TRUTH_DB,
            last_synced_at: new Date(),
            sync_error: null,
          })
          .onConflict((oc: any) =>
            oc.column("booking_no").doUpdateSet({
              ...Object.fromEntries(
                UPSERT_COLUMNS.map((column) => [
                  column,
                  sql.ref(`excluded.${column}`),
                ]),
              ),
              sync_error: null,
              updated_at: sql`now()`,
            }),
          )
          .execute();
        outcome.written += 1;
      } catch (err) {
        outcome.failed += 1;
        const message = err instanceof Error ? err.message : String(err);
        outcome.errors.push({ bookingNo, message });
        logError(`[SignupBookingSync] booking ${bookingNo} failed: ${message}`);
      }
    }
  }

  if (options.prune) {
    if (outcome.read === 0 || outcome.failed > 0) {
      logInfo(
        `[SignupBookingSync] prune skipped: read ${outcome.read}, failed ${outcome.failed}`,
      );
    } else {
      const deleted = await db
        .deleteFrom("viewpoint_bookings")
        .where("last_synced_at", "<", runStartedAt)
        .executeTakeFirst();
      outcome.pruned = Number(deleted?.numDeletedRows ?? 0);
      logInfo(`[SignupBookingSync] pruned ${outcome.pruned} stale bookings`);
    }
  }

  logInfo(
    `[SignupBookingSync] ${outcome.memberships} memberships, read ${outcome.read}, ` +
      `written ${outcome.written}, failed ${outcome.failed}, ` +
      `subito ${outcome.subito}, manual ${outcome.manual}, ` +
      `pruned ${outcome.pruned}`,
  );
  return outcome;
}