import { createDBPool } from "@/internal/datastore/index";
import {
  countKcgmBookings,
  getViewpointDB,
  listBookingSources,
  listKcgmBookings,
  TRACKED_BOOKING_SOURCES,
} from "@/internal/viewpoint-db/kcgm-bookings.repo";
import { syncKcgmBookings } from "@/internal/viewpoint-db/kcgm-sync.service";
import dotenv from "dotenv";
import { sql } from "kysely";
import { ViewpointImportRepository } from "@/internal/viewpoint-import/viewpoint-import.repo";

dotenv.config();

const config = {
  host: process.env.DATABASE_HOST,
  port: parseInt(process.env.DATABASE_PORT || "5432"),
  database: process.env.DATABASE_NAME,
  user: process.env.DATABASE_USER,
  password: process.env.DATABASE_PWD,
  ssl:
    process.env.DATABASE_HOST !== "localhost" &&
      process.env.DATABASE_HOST !== "127.0.0.1"
      ? { rejectUnauthorized: false }
      : undefined,
};

const memberConfig = {
  host: process.env.MEMBER_DB_HOST,
  port: parseInt(process.env.MEMBER_DB_PORT || "5432"),
  database: process.env.MEMBER_DB_NAME,
  user: process.env.MEMBER_DB_USER,
  password: process.env.MEMBER_DB_PASSWORD,
  ssl:
    process.env.MEMBER_DB_HOST !== "localhost" &&
      process.env.MEMBER_DB_HOST !== "127.0.0.1"
      ? { rejectUnauthorized: false }
      : undefined,
};

async function main() {
  const write = process.argv.includes("--write");
  const viewpointDb = getViewpointDB();

  console.log(
    `Filter: bookingsource IN (${TRACKED_BOOKING_SOURCES.map((v) => `'${v}'`).join(", ")})`,
  );

  console.log("\nBooking sources in the view:");
  const sources = await listBookingSources(viewpointDb);
  for (const s of sources) {
    const tracked = TRACKED_BOOKING_SOURCES.includes(s.booking_source ?? "")
      ? "  <-- tracked"
      : "";
    console.log(`  ${String(s.bookings).padStart(7)}  ${s.booking_source}${tracked}`);
  }

  const total = await countKcgmBookings(viewpointDb);
  console.log(`\nMatched by the filter above: ${total} bookings.`);

  const sample = await listKcgmBookings(viewpointDb, { limit: 3 });
  for (const row of sample) {
    console.log({
      booking_no: row.booking_no,
      status: row.status,
      booking_source: row.booking_source,
      booking_type: row.booking_type,
      account_id: row.account_id,
      entitle_account_id: row.entitle_account_id,
      arrival: row.arrival,
      resort_name: row.resort_name,
    });
  }

  if (process.argv.includes("--check-members")) {
    const memberDb = createDBPool(memberConfig);

    const candidates = Array.from(
      new Set(
        sample.flatMap((r) =>
          [r.account_id, r.entitle_account_id].filter(Boolean),
        ),
      ),
    ) as string[];

    console.log(`\nChecking ${candidates.length} ids against the member DB:`);
    for (const id of candidates) {
      const hit = (await sql<{
        as_membership: number;
        as_member: number;
      }>`
        SELECT
          COUNT(*) FILTER (WHERE UPPER(BTRIM(membership_number)) = ${id})::int AS as_membership,
          COUNT(*) FILTER (WHERE UPPER(BTRIM(member_number)) = ${id})::int AS as_member
        FROM members
      `.execute(memberDb)) as unknown as {
        rows: Array<{ as_membership: number; as_member: number }>;
      };
      const r = hit.rows[0];
      console.log(
        `  ${id}: ${r?.as_membership ?? 0} member(s) share it as membership_number, ` +
        `${r?.as_member ?? 0} as member_number`,
      );
    }
    console.log(
      "\nThe id matching as membership_number is the one to map. " +
      "Several members sharing one is expected — a membership covers a household.",
    );
    await memberDb.destroy();
  }

  if (!write) {
    console.log("\nRead-only. Pass --write to upsert into viewpoint_bookings.");
    await viewpointDb.destroy();
    return;
  }

  const db = createDBPool(config);
  console.log(`\nWriting into ${config.database} (core2) ...`);
  const outcome = await syncKcgmBookings(db, viewpointDb);
  console.log(outcome);

  const memberDb = createDBPool(memberConfig);
  console.log("Matching against core ...");
  const matched = await new ViewpointImportRepository(
    db,
    memberDb,
  ).matchAgainstCore();
  console.log(matched);

  await memberDb.destroy();
  await db.destroy();
  await viewpointDb.destroy();
}

main().catch((err) => {
  console.error(err);
  process.exit(1);
});
