/**
 * The canonical fields a Viewpoint report can be mapped onto.
 *
 * The importer cannot assume a column layout — the operator uploads whatever
 * Viewpoint's report manager produced, and the console shows that sheet's own
 * header titles and asks which of these fields each one is. This module is the one
 * place that list is declared, so core's validation, the upsert's column set and
 * the console's mapping UI all agree on it.
 *
 * `aliases` only pre-selects a guess in the mapping step. It is a convenience, not
 * a rule: the operator's choice always wins, and a header nobody recognises simply
 * arrives unmapped rather than being dropped or guessed at. Matching is done on a
 * loosened form of the header (see `normaliseHeader`) so spacing, case and
 * punctuation differences between exports don't defeat it.
 */

export const VIEWPOINT_REPORT_TYPES = ["bookings", "member_contacts"] as const;
export type ViewpointReportType = (typeof VIEWPOINT_REPORT_TYPES)[number];

/** How a mapped value is coerced before it is written. */
export type FieldKind = "text" | "int" | "decimal" | "date" | "timestamp";

export interface FieldDef {
  /** Column name in `viewpoint_bookings` / `viewpoint_member_contacts`. */
  key: string;
  /** Shown in the mapping UI. */
  label: string;
  kind: FieldKind;
  /**
   * Whether a row missing this value is unusable.
   *
   * Only the natural keys are required: without a booking number or a membership
   * number a row cannot be updated by a later import or a sync, so it would
   * silently duplicate on every upload.
   */
  required?: boolean;
  aliases: string[];
}

const BOOKING_FIELDS: FieldDef[] = [
  {
    key: "booking_no",
    label: "Booking number",
    kind: "text",
    required: true,
    aliases: ["booking no", "booking number", "bookingno", "booking id", "res no", "reservation no", "reservation number", "confirmation no"],
  },
  {
    key: "status",
    label: "Status",
    kind: "text",
    aliases: ["status", "booking status", "reservation status"],
  },
  {
    key: "booking_source",
    label: "Booking source",
    kind: "text",
    aliases: ["booking source", "source", "source name", "channel", "booking channel"],
  },
  {
    key: "booking_source_code",
    label: "Booking source code",
    kind: "text",
    aliases: ["booking source code", "source code", "channel code"],
  },
  {
    key: "booking_type",
    label: "Booking type",
    kind: "text",
    aliases: ["booking type", "type", "reservation type"],
  },
  {
    key: "account_id",
    /*
     * Labelled "Owner account ID", not "Account ID".
     *
     * The label is matched exactly and before any alias, so calling this field
     * "Account ID" meant a header spelled "Account ID" landed here while "accountid"
     * landed on `membership_number` — the same column mapped two different ways
     * depending on a space. Renaming it removes the collision and leaves both
     * spellings going to the membership number, which is what the value is.
     */
    label: "Owner account ID",
    kind: "text",
    /*
     * "accountid" is deliberately NOT an alias here — it belongs to
     * `membership_number`, see below. Only headers that name an owner account
     * explicitly land on this field.
     */
    aliases: ["owner account", "owner account id", "owner acct"],
  },
  {
    key: "membership_number",
    label: "Membership number",
    kind: "text",
    /*
     * `accountid` maps here, not to `account_id`, on the evidence of the live report:
     * of its 155 distinct values, 145 match `members.membership_number` and only 9
     * match `members.member_number`. Viewpoint calls it an account id, but the value
     * it carries is the membership number, and this is the field the member join and
     * all promo attribution depend on.
     *
     * Mapping it to a lookalike field instead left `membership_number` empty on every
     * row, so the join found nothing and every booking read as "no number".
     */
    aliases: [
      "membership number",
      "membership no",
      "member no",
      "member number",
      "membership",
      "member id",
      "membership id",
      "accountid",
      "account id",
      "account no",
    ],
  },
  {
    key: "guest_name",
    label: "Guest name",
    kind: "text",
    aliases: ["guest name", "guest", "primary guest", "name", "lead guest"],
  },
  {
    key: "resort_code",
    label: "Resort code",
    kind: "text",
    aliases: ["resort code", "resortcode", "property code", "hotel code"],
  },
  {
    key: "resort_name",
    label: "Resort",
    kind: "text",
    aliases: ["resort", "resort name", "property", "property name", "hotel"],
  },
  {
    key: "room_type",
    label: "Room type",
    kind: "text",
    aliases: ["room type", "roomtype", "unit type", "room category"],
  },
  {
    key: "arrival",
    label: "Arrival",
    kind: "date",
    aliases: ["arrival", "arrival date", "check in", "check-in", "checkin", "from date", "start date"],
  },
  {
    key: "departure",
    label: "Departure",
    kind: "date",
    aliases: ["departure", "departure date", "check out", "check-out", "checkout", "to date", "end date"],
  },
  { key: "adults", label: "Adults", kind: "int", aliases: ["adults", "no of adults", "adult"] },
  { key: "children", label: "Children", kind: "int", aliases: ["children", "no of children", "child", "kids"] },
  {
    key: "booking_date",
    label: "Booking date",
    kind: "date",
    aliases: ["booking date", "booked on", "created date", "reservation date", "date booked"],
  },
  {
    key: "total_amount",
    label: "Total amount",
    kind: "decimal",
    aliases: ["total amount", "total", "amount", "revenue", "net amount", "total revenue"],
  },
  { key: "currency", label: "Currency", kind: "text", aliases: ["currency", "currency code", "ccy"] },
  {
    key: "modify_date_time",
    label: "Last modified",
    kind: "timestamp",
    aliases: ["modify date time", "modified", "last modified", "modify date", "updated"],
  },
  {
    key: "infants",
    label: "Infants",
    kind: "int",
    aliases: ["infants", "infant", "no of infants"],
  },
  {
    key: "confirmed_date",
    label: "Confirmed date",
    kind: "date",
    aliases: ["confirmed date", "confirmation date", "date confirmed"],
  },
  {
    key: "comments",
    label: "Comments",
    kind: "text",
    aliases: ["comments", "comment", "notes", "remarks", "booking comment"],
  },

  /*
   * The guest as Viewpoint recorded them, kept separate from the member DB's contact
   * details rather than reconciled with them. The guest on a stay is not necessarily
   * the member who owns the membership, and where the two disagree that difference is
   * itself worth being able to see.
   */
  {
    key: "guest_nationality",
    label: "Guest nationality",
    kind: "text",
    aliases: ["nationality", "guest nationality"],
  },
  {
    key: "guest_email",
    label: "Guest email",
    kind: "text",
    aliases: ["email", "guest email", "email address"],
  },
  {
    key: "guest_phone",
    label: "Guest phone",
    kind: "text",
    aliases: ["phone", "guest phone", "telephone", "landline"],
  },
  {
    key: "guest_mobile",
    label: "Guest mobile",
    kind: "text",
    // "Moblie" is a real misspelling in the live report header, not a typo here.
    aliases: ["mobile", "moblie", "guest mobile", "cell", "mobile no"],
  },
  {
    key: "guest_city",
    label: "Guest city",
    kind: "text",
    aliases: ["city", "guest city", "town"],
  },
  {
    key: "guest_state",
    label: "Guest state",
    kind: "text",
    aliases: ["state", "guest state", "province", "region"],
  },
  {
    key: "guest_country",
    label: "Guest country",
    kind: "text",
    aliases: ["country", "guest country"],
  },
  {
    key: "guest_dob",
    label: "Guest date of birth",
    kind: "date",
    aliases: ["dob", "date of birth", "birth date", "birthdate"],
  },
  {
    key: "guest_marital_status",
    label: "Marital status",
    kind: "text",
    aliases: ["marital status", "maritalstatus"],
  },
  {
    key: "flight_arrival_no",
    label: "Arrival flight",
    kind: "text",
    aliases: ["flight arrival no", "arrival flight", "flight arrival", "arrival flight no"],
  },
  {
    key: "flight_arrival_time",
    label: "Arrival flight time",
    kind: "text",
    /*
     * Text, not a timestamp. Reports carry these as a bare clock time with no date,
     * and coercing "14:30" into a timestamp would have to invent one — attaching it
     * to today, or to the epoch, either of which reads as a real value.
     */
    aliases: ["flight arrival time", "arrival flight time", "arrival time"],
  },
  {
    key: "flight_departure_no",
    label: "Departure flight",
    kind: "text",
    aliases: ["departure flight no", "flight departure no", "departure flight"],
  },
  {
    key: "flight_departure_time",
    label: "Departure flight time",
    kind: "text",
    aliases: ["departure flight time", "flight departure time", "departure time"],
  },
];

const MEMBER_CONTACT_FIELDS: FieldDef[] = [
  {
    key: "membership_number",
    label: "Membership number",
    kind: "text",
    required: true,
    aliases: ["membership number", "membership no", "member no", "member number", "membership", "member id", "membership id"],
  },
  { key: "account_id", label: "Account ID", kind: "text", aliases: ["account id", "accountid", "account no"] },
  { key: "first_name", label: "First name", kind: "text", aliases: ["first name", "firstname", "given name", "forename"] },
  { key: "last_name", label: "Last name", kind: "text", aliases: ["last name", "lastname", "surname", "family name"] },
  { key: "email", label: "Email", kind: "text", aliases: ["email", "email address", "e mail", "primary email"] },
  { key: "phone", label: "Phone", kind: "text", aliases: ["phone", "telephone", "phone number", "home phone", "landline"] },
  { key: "mobile", label: "Mobile", kind: "text", aliases: ["mobile", "mobile number", "cell", "cell phone", "mobile phone"] },
  { key: "address_line1", label: "Address line 1", kind: "text", aliases: ["address line 1", "address1", "address", "street"] },
  { key: "address_line2", label: "Address line 2", kind: "text", aliases: ["address line 2", "address2"] },
  { key: "city", label: "City", kind: "text", aliases: ["city", "town", "address city"] },
  { key: "state", label: "State", kind: "text", aliases: ["state", "province", "region", "county"] },
  { key: "country", label: "Country", kind: "text", aliases: ["country", "addr country", "country name"] },
  { key: "postcode", label: "Postcode", kind: "text", aliases: ["postcode", "post code", "zip", "zip code", "postal code"] },
  { key: "membership_type", label: "Membership type", kind: "text", aliases: ["membership type", "account type", "member type", "product"] },
  { key: "membership_status", label: "Membership status", kind: "text", aliases: ["membership status", "account status", "status"] },
  { key: "commence_date", label: "Commence date", kind: "date", aliases: ["commence date", "start date", "join date", "date of acquisition", "acquisition date"] },
  { key: "expiry_date", label: "Expiry date", kind: "date", aliases: ["expiry date", "expiration date", "end date", "expires"] },
];

export const VIEWPOINT_FIELDS: Record<ViewpointReportType, FieldDef[]> = {
  bookings: BOOKING_FIELDS,
  member_contacts: MEMBER_CONTACT_FIELDS,
};

export const VIEWPOINT_TABLE: Record<ViewpointReportType, string> = {
  bookings: "viewpoint_bookings",
  member_contacts: "viewpoint_member_contacts",
};

/** The column a re-import upserts on, per report. */
export const VIEWPOINT_CONFLICT_KEY: Record<ViewpointReportType, string> = {
  bookings: "booking_no",
  member_contacts: "membership_number",
};

/**
 * Loosens a header for alias matching.
 *
 * Lowercased, punctuation and underscores turned into spaces, runs of whitespace
 * collapsed — so "Booking No.", "BOOKING_NO" and "booking  no" all reduce to the
 * same thing. Only used for the mapping *guess*; nothing is stored in this form.
 */
export function normaliseHeader(header: string): string {
  return String(header ?? "")
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, " ")
    .trim()
    .replace(/\s+/g, " ");
}

/**
 * A header reduced to letters and digits only.
 *
 * Real Viewpoint exports write headers joined rather than spaced — `GuestName`,
 * `resortname`, `BookingSourceDesc`. `normaliseHeader` turns those into "guestname"
 * and "resortname", while the aliases are written as "guest name" and "resort name",
 * so nothing matched: 20 of 26 columns on a real report arrived unmapped. Comparing
 * both sides with spaces removed makes the join irrelevant, which fixes the whole
 * class rather than requiring a second alias per field.
 */
function despace(value: string): string {
  return normaliseHeader(value).replace(/ /g, "");
}

/**
 * Trailing words that describe the column rather than name it.
 *
 * Viewpoint suffixes several headers with the kind of value they hold —
 * `BookingSourceDesc` is the booking source, `BookingTypeDesc` the booking type.
 * Stripping the suffix lets one alias cover both spellings instead of listing each.
 */
const DESCRIPTIVE_SUFFIXES = ["desc", "description", "name", "no", "number"];

function stripSuffix(value: string): string {
  for (const suffix of DESCRIPTIVE_SUFFIXES) {
    if (value.length > suffix.length + 2 && value.endsWith(suffix)) {
      return value.slice(0, -suffix.length);
    }
  }
  return value;
}

/**
 * The field a header most likely means, or null when nothing matches.
 *
 * Tried in decreasing confidence: exact, then join-insensitive, then with a
 * descriptive suffix removed. The order matters — a loose match must never win over
 * an exact one, or `BookingSourceDesc` could beat `BookingSource` to the same field
 * and leave the more precise header unmapped.
 *
 * A miss returns null and the column arrives on "Don't import" rather than being
 * attached to a plausible-looking field. The operator's choice is always the final
 * word; this only decides what is pre-selected.
 */
export function guessField(
  header: string,
  reportType: ViewpointReportType,
): string | null {
  const norm = normaliseHeader(header);
  if (!norm) return null;
  const fields = VIEWPOINT_FIELDS[reportType];

  const candidates = (field: FieldDef) => [field.label, ...field.aliases];

  // 1. Exact, on the spaced form.
  for (const field of fields) {
    if (candidates(field).some((c) => normaliseHeader(c) === norm)) {
      return field.key;
    }
  }

  // 2. Join-insensitive: "GuestName" === "guest name".
  const flat = despace(header);
  for (const field of fields) {
    if (candidates(field).some((c) => despace(c) === flat)) return field.key;
  }

  // 3. With a descriptive suffix removed: "BookingSourceDesc" === "booking source".
  const trimmed = stripSuffix(flat);
  if (trimmed !== flat) {
    for (const field of fields) {
      if (candidates(field).some((c) => despace(c) === trimmed)) return field.key;
    }
  }

  return null;
}