import type { Kysely } from "kysely";
import { createHash } from "crypto";

const isValidUUID = (str: string): boolean => {
  return /^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i.test(str);
};

const stringToUUID = (str: string): string => {
  const hash = createHash("sha256").update(str).digest("hex");
  return [
    hash.substring(0, 8),
    hash.substring(8, 12),
    "4" + hash.substring(13, 16),
    ((parseInt(hash.substring(16, 18), 16) & 0x3f) | 0x80).toString(16) + hash.substring(18, 20),
    hash.substring(20, 32),
  ].join("-");
};

const formatToLocalDate = (d: any): string => {
  if (!d) return "";
  const dateObj = d instanceof Date ? d : new Date(d);
  if (isNaN(dateObj.getTime())) return "";
  const yyyy = dateObj.getFullYear();
  const mm = String(dateObj.getMonth() + 1).padStart(2, "0");
  const dd = String(dateObj.getDate()).padStart(2, "0");
  return `${yyyy}-${mm}-${dd}`;
};

const findResortId = async (db: any, resortName: string): Promise<string | null> => {
  const dbResorts = await db.selectFrom("resorts" as any).select(["id", "name"]).execute();
  const clean = (s: string) => s.toLowerCase().replace(/[^a-z0-9]/g, "");
  const cleanJsonName = clean(resortName);

  // Exact clean match
  for (const r of dbResorts) {
    if (clean(r.name) === cleanJsonName) {
      return r.id;
    }
  }

  // Substring clean match
  for (const r of dbResorts) {
    if (cleanJsonName.includes(clean(r.name)) || clean(r.name).includes(cleanJsonName)) {
      return r.id;
    }
  }

  return null;
};

const formatDbOfferToDeal = (mo: any, unitCodes: string[]): Record<string, any> => {
  const travelStart = mo.travel_period_start ? formatToLocalDate(mo.travel_period_start) : "";
  const travelEnd = mo.travel_period_end ? formatToLocalDate(mo.travel_period_end) : "";
  const type = mo.offer_type === "EXCLUSIVE_DISCOVERY" ? "exclusive_offer" : "hot_deal";

  // Defaults derived from real columns / the joined resort. These are the
  // fallbacks used for deals imported before the console stored a full payload.
  const derived: Record<string, any> = {
    resort: mo.resort_name,
    travel_between: travelStart && travelEnd ? [[travelStart, travelEnd]] : [],
    new: "",
    starts: mo.booking_period_start ? `${formatToLocalDate(mo.booking_period_start)} 00:00:00` : "",
    expires: mo.booking_period_end ? `${formatToLocalDate(mo.booking_period_end)} 23:59:59` : "",
    roles: mo.allowed_user_types || [],
    unit_code: unitCodes,
    night: 7,
    occ_up_to: 6,
    inclusions: mo.inclusions || "",
    terms_conditions: mo.terms_and_conditions || "",
    type: type,
    thumbnail: mo.resort_cover_image || "",
    destination_profile_url: mo.resort_website_url || "",
    master_info: {
      tagline: mo.name,
      title: mo.resort_country ? `${mo.resort_country.toUpperCase()} HOT DEAL` : "HOT DEAL",
      price: mo.description || "Special Points Offer",
      details: mo.description ? [mo.description] : [],
      travel: travelStart && travelEnd ? `Travel Dates : ${travelStart} to ${travelEnd}` : "",
      booking: "",
      check_in: travelStart,
      check_out: travelEnd,
    },
  };

  // raw_config is the full deal payload the console editor saves and is the
  // authoritative source for every field the admin can edit (calculation_mode,
  // phase_3_*, night, occ_up_to, price, label_by, override, thumbnail,
  // master_info, roles, weekly, allow_breakdown, inclusions_cost_*, …). Merge
  // it over the derived defaults so the emitted JSON matches what was entered.
  const raw = mo.raw_config && typeof mo.raw_config === "object" ? mo.raw_config : {};

  // travel_rules / occp_price are not part of the booking-engine hot-deal JSON —
  // travel_rules is served by the separate travel-rules feed; occp_price is a
  // legacy field with no consumer.
  const rawDeal: Record<string, any> = { ...raw };
  delete rawDeal.travel_rules;
  delete rawDeal.occp_price;

  const merged: Record<string, any> = { ...derived, ...rawDeal };

  // master_info shallow-merges so a partial raw_config still inherits defaults.
  merged.master_info = { ...derived.master_info, ...(rawDeal.master_info || {}) };

  // Fields backed by real columns always win — they are what the editor writes
  // through the member-offers controller, so they are never stale.
  merged.resort = derived.resort;
  merged.unit_code = derived.unit_code;
  if (derived.starts) merged.starts = derived.starts;
  if (derived.expires) merged.expires = derived.expires;
  if (Array.isArray(derived.travel_between) && derived.travel_between.length) {
    merged.travel_between = derived.travel_between;
  }

  return merged;
};

export class HotDealsService {
  private static instance: HotDealsService;

  static getInstance(): HotDealsService {
    if (!HotDealsService.instance) {
      HotDealsService.instance = new HotDealsService();
    }
    return HotDealsService.instance;
  }

  async listDeals(db: any) {
    const dbOffers = await db
      .selectFrom("member_offers as mo" as any)
      .innerJoin("resorts as r" as any, "r.id" as any, "mo.resort_id" as any)
      .select([
        "mo.id" as any,
        "mo.name" as any,
        "mo.offer_type" as any,
        "mo.description" as any,
        "mo.booking_period_start" as any,
        "mo.booking_period_end" as any,
        "mo.travel_period_start" as any,
        "mo.travel_period_end" as any,
        "mo.inclusions" as any,
        "mo.terms_and_conditions" as any,
        "mo.allowed_user_types" as any,
        "mo.raw_config" as any,
        "r.name as resort_name" as any,
        "r.country as resort_country" as any,
        "r.cover_image as resort_cover_image" as any,
        "r.website_url as resort_website_url" as any,
      ])
      .where("mo.offer_type" as any, "in", ["HOT_DEAL", "EXCLUSIVE_DISCOVERY"])
      .execute();

    const deals = [];
    for (const mo of dbOffers) {
      const dbUnits = await db
        .selectFrom("member_offer_units as mou" as any)
        .innerJoin("resort_units as ru" as any, "ru.id" as any, "mou.resort_unit_id" as any)
        .select("ru.unit_vp_code as unit_vp_code" as any)
        .where("mou.member_offer_id" as any, "=", mo.id)
        .execute();

      const unitCodes = dbUnits.map((u: any) => u.unit_vp_code);
      deals.push({
        id: mo.id,
        ...formatDbOfferToDeal(mo, unitCodes),
      });
    }

    return { success: true, data: deals, message: "OK" };
  }

  async getDeal(db: any, id: string) {
    const parsedId = isValidUUID(id) ? id : stringToUUID(id);

    const mo = await db
      .selectFrom("member_offers as mo" as any)
      .innerJoin("resorts as r" as any, "r.id" as any, "mo.resort_id" as any)
      .select([
        "mo.id" as any,
        "mo.name" as any,
        "mo.offer_type" as any,
        "mo.description" as any,
        "mo.booking_period_start" as any,
        "mo.booking_period_end" as any,
        "mo.travel_period_start" as any,
        "mo.travel_period_end" as any,
        "mo.inclusions" as any,
        "mo.terms_and_conditions" as any,
        "mo.allowed_user_types" as any,
        "mo.raw_config" as any,
        "r.name as resort_name" as any,
        "r.country as resort_country" as any,
        "r.cover_image as resort_cover_image" as any,
        "r.website_url as resort_website_url" as any,
      ])
      .where("mo.id" as any, "=", parsedId)
      .executeTakeFirst();

    if (!mo) {
      return { success: false, data: null, message: "Deal not found" };
    }

    const dbUnits = await db
      .selectFrom("member_offer_units as mou" as any)
      .innerJoin("resort_units as ru" as any, "ru.id" as any, "mou.resort_unit_id" as any)
      .select("ru.unit_vp_code as unit_vp_code" as any)
      .where("mou.member_offer_id" as any, "=", parsedId)
      .execute();

    const unitCodes = dbUnits.map((u: any) => u.unit_vp_code);
    return { success: true, data: { id, ...formatDbOfferToDeal(mo, unitCodes) }, message: "OK" };
  }

  async createDeal(db: any, id: string, deal: Record<string, any>) {
    const parsedId = isValidUUID(id) ? id : stringToUUID(id);

    const existing = await db
      .selectFrom("member_offers" as any)
      .select("id")
      .where("id", "=", parsedId)
      .executeTakeFirst();

    if (existing) {
      return { success: false, data: null, message: "Deal ID already exists" };
    }

    const resortId = await findResortId(db, deal.resort || "");
    if (!resortId) {
      return { success: false, data: null, message: `No matching resort found for "${deal.resort}"` };
    }

    const offerType = deal.type === "exclusive_offer" ? "EXCLUSIVE_DISCOVERY" : "HOT_DEAL";
    const bookingStart = deal.starts ? deal.starts.split(" ")[0] : null;
    const bookingEnd = deal.expires ? deal.expires.split(" ")[0] : null;
    const travelStart = deal.travel_between?.[0]?.[0] || null;
    const travelEnd = deal.travel_between?.[0]?.[1] || null;

    const offerData = {
      id: parsedId,
      name: deal.master_info?.tagline || id,
      offer_type: offerType,
      description: deal.master_info?.price || null,
      resort_id: resortId,
      is_active: true,
      is_cancellable: false,
      booking_period_start: bookingStart,
      booking_period_end: bookingEnd,
      travel_period_start: travelStart,
      travel_period_end: travelEnd,
      blocked_account_types: [],
      blocked_account_statuses: [],
      blocked_clubs: [],
      allowed_user_types: Array.isArray(deal.roles) ? deal.roles : ["LEGAL_OWNER", "FAMILY_MEMBER"],
      inclusions: deal.inclusions || null,
      terms_and_conditions: deal.terms_conditions || null,
      confirmation_edm_template_ids: [],
      cancellation_edm_template_ids: [],
      publish_mode: "PUBLISHED",
      published_at: new Date(),
      updated_at: new Date(),
      // Preserve the full imported payload so the deal round-trips exactly,
      // including fields with no dedicated column (override, master_info, etc.).
      raw_config: deal,
    };

    await db.insertInto("member_offers" as any).values(offerData).execute();

    if (Array.isArray(deal.unit_code) && deal.unit_code.length > 0) {
      const dbUnits = await db
        .selectFrom("resort_units" as any)
        .select(["id", "unit_vp_code"])
        .where("resort_id", "=", resortId)
        .execute();

      let offerValueType = "PERCENTAGE";
      let offerValue = 75;
      const priceStr = deal.master_info?.price || "";
      const pctMatch = priceStr.match(/(\d+)%\s*Off/i);
      if (pctMatch) {
        offerValue = parseInt(pctMatch[1]);
        offerValueType = "PERCENTAGE";
      } else if (typeof deal.price === "number") {
        offerValue = deal.price;
        offerValueType = "POINTS";
      }

      const minNights = deal.phase_3_min_night || deal.night || null;
      const maxNights = deal.phase_3_max_night || deal.night || null;

      const unitOffersToInsert = [];
      for (const code of deal.unit_code) {
        const matchedUnit = dbUnits.find(
          (u: any) => u.unit_vp_code.toLowerCase().trim() === code.toLowerCase().trim(),
        );
        if (matchedUnit) {
          unitOffersToInsert.push({
            member_offer_id: parsedId,
            resort_unit_id: matchedUnit.id,
            is_admin_override: true,
            offer_value_type: offerValueType,
            offer_value: offerValue,
            minimum_nights: minNights,
            maximum_nights_per_booking: maxNights,
            maximum_units_per_membership: 2,
            created_at: new Date(),
            updated_at: new Date(),
          });
        }
      }

      if (unitOffersToInsert.length > 0) {
        await db.insertInto("member_offer_units" as any).values(unitOffersToInsert).execute();
      }
    }

    return { success: true, data: { id, ...deal }, message: "Deal created" };
  }

  async updateDeal(db: any, id: string, deal: Record<string, any>) {
    const parsedId = isValidUUID(id) ? id : stringToUUID(id);

    const existing = await db
      .selectFrom("member_offers" as any)
      .select("id")
      .where("id", "=", parsedId)
      .executeTakeFirst();

    if (!existing) {
      return { success: false, data: null, message: "Deal not found" };
    }

    const resortId = await findResortId(db, deal.resort || "");
    if (!resortId) {
      return { success: false, data: null, message: `No matching resort found for "${deal.resort}"` };
    }

    const offerType = deal.type === "exclusive_offer" ? "EXCLUSIVE_DISCOVERY" : "HOT_DEAL";
    const bookingStart = deal.starts ? deal.starts.split(" ")[0] : null;
    const bookingEnd = deal.expires ? deal.expires.split(" ")[0] : null;
    const travelStart = deal.travel_between?.[0]?.[0] || null;
    const travelEnd = deal.travel_between?.[0]?.[1] || null;

    const offerData = {
      name: deal.master_info?.tagline || id,
      offer_type: offerType,
      description: deal.master_info?.price || null,
      resort_id: resortId,
      booking_period_start: bookingStart,
      booking_period_end: bookingEnd,
      travel_period_start: travelStart,
      travel_period_end: travelEnd,
      allowed_user_types: Array.isArray(deal.roles) ? deal.roles : ["LEGAL_OWNER", "FAMILY_MEMBER"],
      inclusions: deal.inclusions || null,
      terms_and_conditions: deal.terms_conditions || null,
      updated_at: new Date(),
      // Preserve the full payload so the deal round-trips exactly.
      raw_config: deal,
    };

    await db
      .updateTable("member_offers" as any)
      .set(offerData)
      .where("id", "=", parsedId)
      .execute();

    // Clear unit mapping and recreate
    await db.deleteFrom("member_offer_units" as any).where("member_offer_id", "=", parsedId).execute();

    if (Array.isArray(deal.unit_code) && deal.unit_code.length > 0) {
      const dbUnits = await db
        .selectFrom("resort_units" as any)
        .select(["id", "unit_vp_code"])
        .where("resort_id", "=", resortId)
        .execute();

      let offerValueType = "PERCENTAGE";
      let offerValue = 75;
      const priceStr = deal.master_info?.price || "";
      const pctMatch = priceStr.match(/(\d+)%\s*Off/i);
      if (pctMatch) {
        offerValue = parseInt(pctMatch[1]);
        offerValueType = "PERCENTAGE";
      } else if (typeof deal.price === "number") {
        offerValue = deal.price;
        offerValueType = "POINTS";
      }

      const minNights = deal.phase_3_min_night || deal.night || null;
      const maxNights = deal.phase_3_max_night || deal.night || null;

      const unitOffersToInsert = [];
      for (const code of deal.unit_code) {
        const matchedUnit = dbUnits.find(
          (u: any) => u.unit_vp_code.toLowerCase().trim() === code.toLowerCase().trim(),
        );
        if (matchedUnit) {
          unitOffersToInsert.push({
            member_offer_id: parsedId,
            resort_unit_id: matchedUnit.id,
            is_admin_override: true,
            offer_value_type: offerValueType,
            offer_value: offerValue,
            minimum_nights: minNights,
            maximum_nights_per_booking: maxNights,
            maximum_units_per_membership: 2,
            created_at: new Date(),
            updated_at: new Date(),
          });
        }
      }

      if (unitOffersToInsert.length > 0) {
        await db.insertInto("member_offer_units" as any).values(unitOffersToInsert).execute();
      }
    }

    return { success: true, data: { id, ...deal }, message: "Deal updated" };
  }

  async deleteDeal(db: any, id: string) {
    const parsedId = isValidUUID(id) ? id : stringToUUID(id);

    const existing = await db
      .selectFrom("member_offers" as any)
      .select("id")
      .where("id", "=", parsedId)
      .executeTakeFirst();

    if (!existing) {
      return { success: false, data: null, message: "Deal not found" };
    }

    await db.deleteFrom("member_offers" as any).where("id", "=", parsedId).execute();
    return { success: true, data: null, message: "Deal deleted" };
  }

  async getRawDeals(db: any) {
    const res = await this.listDeals(db);
    const dict: Record<string, any> = {};
    for (const item of res.data) {
      const { id, ...dealData } = item;
      dict[id] = dealData;
    }
    return dict;
  }
}