import type {
  EventRecord,
  EventTypeAnalyticsRow,
  EventSummary,
  MemberAnalyticsRow,
  RevenueByCurrency,
} from "@/lib/features/event-analytics/types";
import type { ExportColumn, ExportSheet } from "./exportXlsx";

/**
 * Worksheets for an Event Analytics export.
 *
 * Follows `analyticsSheets.ts`: the numbers behind the on-screen cards and
 * tables, not the charts. Revenue is reported per currency throughout and is
 * never summed across currencies — a combined figure would be arithmetic on
 * incomparable units.
 */

/** "USD 12,500.00; EUR 900.00" — the compact form used inside a table cell. */
export function formatRevenue(revenue: RevenueByCurrency[]): string {
  if (!revenue.length) return "—";
  return revenue
    .map(
      (r) =>
        `${r.currency} ${r.revenue.toLocaleString("en-US", {
          minimumFractionDigits: 2,
          maximumFractionDigits: 2,
        })}`,
    )
    .join("; ");
}

const DETAIL_COLUMNS: ExportColumn<Record<string, unknown>>[] = [
  { key: "#", label: "#", width: 6 },
  { key: "Event ID", label: "Event ID", width: 26 },
  { key: "Event Type", label: "Event Type", width: 24 },
  { key: "Source", label: "Source", width: 16 },
  { key: "Created At", label: "Created At", width: 24 },
  { key: "Member Number", label: "Member Number", width: 16 },
  { key: "Property", label: "Property", width: 24 },
  { key: "Experience", label: "Experience", width: 24 },
  { key: "Member Offer", label: "Member Offer", width: 24 },
  { key: "Points", label: "Points", width: 10 },
  { key: "Revenue", label: "Revenue", width: 14 },
  { key: "Currency", label: "Currency", width: 10 },
];

/**
 * Detail rows.
 *
 * Revenue is written as a number so the sheet can be totalled and sorted in
 * Excel; the currency stays in its own column precisely so those totals are only
 * ever taken within a currency. A null revenue is left as an empty cell rather
 * than 0 — the event carried no revenue, it did not earn nothing.
 */
export function buildEventRecordsSheet(records: EventRecord[]): ExportSheet {
  return {
    sheetName: "Event Records",
    columns: DETAIL_COLUMNS,
    rows: records.map((r, i) => ({
      "#": i + 1,
      "Event ID": r.id,
      // The raw collection name, not the titled label — an export is data, and
      // this is the value that matches Firestore and the API.
      "Event Type": r.eventType,
      Source: r.source ?? "",
      // The producer's own `createdAt` string, not the normalised ISO value:
      // the sheet is reconciled against Firestore, so it should show what is
      // actually stored there, and it stays populated even if parsing fails.
      "Created At": r.createdAtRaw ?? r.occurredAt ?? "",
      "Member Number": r.memberId ?? "",
      Property: r.propertyName ?? "",
      Experience: r.experienceName ?? "",
      "Member Offer": r.memberOffer ?? "",
      // Empty, not 0, when the event carried no points — same reasoning as
      // revenue below.
      Points: r.points ?? "",
      Revenue: r.revenue ?? "",
      Currency: r.currency ?? "",
    })),
  };
}

/**
 * Per-member rows, with one column per event type.
 *
 * The per-type columns make the funnel sortable and pivotable in Excel, which a
 * single "event types: 3" count cannot support.
 */
function buildMemberSheet(
  members: MemberAnalyticsRow[],
  eventTypeColumns: string[],
): ExportSheet {
  return {
    sheetName: "Analytics — By Member",
    columns: [
      { key: "Member Number", label: "Member Number", width: 16 },
      { key: "Properties", label: "Properties", width: 34 },
      { key: "Events", label: "Events", width: 10 },
      ...eventTypeColumns.map((eventType) => ({
        key: eventType,
        label: eventType,
        width: 22,
      })),
      { key: "Points", label: "Points", width: 12 },
      { key: "Revenue", label: "Revenue", width: 30 },
      { key: "First Seen", label: "First Seen", width: 22 },
      { key: "Last Seen", label: "Last Seen", width: 22 },
    ],
    rows: members.map((m) => {
      const row: Record<string, unknown> = {
        "Member Number": m.memberId,
        Properties: m.properties.join(", "),
        Events: m.events,
        Points: m.points,
        Revenue: formatRevenue(m.revenueByCurrency),
        "First Seen": m.firstSeen ?? "",
        "Last Seen": m.lastSeen ?? "",
      };
      // 0, not blank: the member genuinely produced none of this event, which
      // is the whole point of a funnel column.
      for (const eventType of eventTypeColumns) {
        row[eventType] = m.eventCounts[eventType] ?? 0;
      }
      return row;
    }),
  };
}

function buildEventTypeSheet(
  eventTypes: EventTypeAnalyticsRow[],
  totalEvents: number,
): ExportSheet {
  const rows: Record<string, unknown>[] = eventTypes.map((t) => ({
    "Event Type": t.eventType,
    Events: t.events,
    "Unique Members": t.uniqueMembers,
    "% of Total": t.sharePct,
    Points: t.points,
    Revenue: formatRevenue(t.revenueByCurrency),
  }));

  rows.push({
    "Event Type": "TOTAL",
    Events: totalEvents,
    "Unique Members": "",
    "% of Total": totalEvents ? 100 : 0,
    // Points are a single unit, so this total is meaningful — unlike revenue,
    // which spans currencies and is left blank.
    Points: eventTypes.reduce((sum, t) => sum + t.points, 0),
    Revenue: "",
  });

  return {
    sheetName: "Analytics — By Event",
    columns: [
      { key: "Event Type", label: "Event Type", width: 28 },
      { key: "Events", label: "Events", width: 12 },
      { key: "Unique Members", label: "Unique Members", width: 16 },
      { key: "% of Total", label: "% of Total", width: 12 },
      { key: "Points", label: "Points", width: 12 },
      { key: "Revenue", label: "Revenue", width: 30 },
    ],
    rows,
  };
}

/**
 * Revenue, one row per currency.
 *
 * Deliberately has no TOTAL row: the event-type sheet can total its event
 * counts because events are countable across currencies, but money is not.
 */
function buildRevenueSheet(summary: EventSummary): ExportSheet {
  return {
    sheetName: "Analytics — Revenue",
    columns: [
      { key: "Currency", label: "Currency", width: 12 },
      { key: "Revenue", label: "Revenue", width: 18 },
      { key: "Revenue Events", label: "Revenue Events", width: 16 },
      { key: "Average", label: "Average", width: 16 },
    ],
    rows: summary.revenueByCurrency.map((r) => ({
      Currency: r.currency,
      Revenue: r.revenue,
      "Revenue Events": r.events,
      // Averaged over events that actually carried revenue, not over every
      // event in the filter — otherwise a page view would dilute booking value.
      Average: r.events ? Number((r.revenue / r.events).toFixed(2)) : 0,
    })),
  };
}

/** Sheet describing the filter that produced the export, so a file is self-explaining. */
function buildFilterSheet(
  filterRows: { label: string; value: string }[],
  summary: EventSummary,
): ExportSheet {
  const rows: Record<string, unknown>[] = filterRows.map((f) => ({
    Filter: f.label,
    Value: f.value,
  }));

  rows.push({ Filter: "Total events", Value: String(summary.totalEvents) });
  rows.push({ Filter: "Unique members", Value: String(summary.uniqueMembers) });
  rows.push({
    Filter: "Points earned",
    Value: `${summary.totalPoints} across ${summary.pointsEvents} events`,
  });
  if (summary.truncated) {
    // Never let a capped scan pass as a complete export.
    rows.push({
      Filter: "⚠ Partial data",
      Value: `Only the first ${summary.scannedDocs.toLocaleString()} matching events were read. Narrow the date range for complete figures.`,
    });
  }

  return {
    sheetName: "Filters",
    columns: [
      { key: "Filter", label: "Filter", width: 24 },
      { key: "Value", label: "Value", width: 70 },
    ],
    rows,
  };
}


/**
 * Excel sheet names are limited to 31 characters and cannot contain : \ / ? * [ ].
 *
 * `Event Report - booking_hold_initiated` is 37 characters, so long names must
 * be shortened, and shortening can make two of them identical — which ExcelJS
 * accepts and Excel then refuses to open. The counter keeps every name distinct.
 */
function uniqueSheetName(desired: string, taken: Set<string>): string {
  const cleaned = desired.replace(/[:\\/?*[\]]/g, "-").trim() || "Sheet";
  let name = cleaned.slice(0, 31);
  let n = 2;
  while (taken.has(name)) {
    const suffix = ` (${n})`;
    name = cleaned.slice(0, 31 - suffix.length) + suffix;
    n += 1;
  }
  taken.add(name);
  return name;
}

/** Detail rows for one subset, as its own sheet. */
function buildSubsetSheet(
  sheetName: string,
  records: EventRecord[],
): ExportSheet {
  return { ...buildEventRecordsSheet(records), sheetName };
}

/**
 * One sheet per selected member, named "Member Report - 1278640".
 *
 * Grouped from the records already fetched for the main sheet rather than
 * re-queried per member: the data is identical, and a request per member would
 * turn a ten-member export into ten round trips.
 */
function buildPerMemberSheets(
  records: EventRecord[],
  memberIds: string[],
  taken: Set<string>,
): ExportSheet[] {
  const byMember = new Map<string, EventRecord[]>();
  for (const record of records) {
    if (!record.memberId) continue;
    const list = byMember.get(record.memberId);
    if (list) list.push(record);
    else byMember.set(record.memberId, [record]);
  }

  return memberIds.map((memberId) =>
    buildSubsetSheet(
      uniqueSheetName(`Member Report - ${memberId}`, taken),
      // A selected member with no events still gets a sheet: an absent sheet
      // reads as "we forgot them", an empty one as "they did nothing".
      byMember.get(memberId) ?? [],
    ),
  );
}

/** One sheet per selected event type, named "Event Report - add_to_cart". */
function buildPerEventSheets(
  records: EventRecord[],
  eventTypes: string[],
  taken: Set<string>,
): ExportSheet[] {
  const byType = new Map<string, EventRecord[]>();
  for (const record of records) {
    const list = byType.get(record.eventType);
    if (list) list.push(record);
    else byType.set(record.eventType, [record]);
  }

  return eventTypes.map((eventType) =>
    buildSubsetSheet(
      uniqueSheetName(`Event Report - ${eventType}`, taken),
      byType.get(eventType) ?? [],
    ),
  );
}

/**
 * The full workbook payload for `ExportButton`: the detail rows as the main
 * sheet, with analytics and filter context appended.
 */
export function buildEventAnalyticsSheets(input: {
  records: EventRecord[];
  members: MemberAnalyticsRow[];
  eventTypes: EventTypeAnalyticsRow[];
  summary: EventSummary;
  filterRows: { label: string; value: string }[];
  /** Event types to give a per-member column, in funnel order. */
  eventTypeColumns: string[];
  /** Members selected in the member-wise tab; one sheet each. */
  perMemberIds?: string[];
  /** Event types selected in the event-wise tab; one sheet each. */
  perEventTypes?: string[];
}): {
  columns: ExportColumn<Record<string, unknown>>[];
  rows: Record<string, unknown>[];
  sheetName: string;
  extraSheets: ExportSheet[];
} {
  const detail = buildEventRecordsSheet(input.records);

  // Seeded with the fixed sheets so a generated name can never collide with one.
  const taken = new Set<string>([
    detail.sheetName,
    "Analytics — By Member",
    "Analytics — By Event",
    "Analytics — Revenue",
    "Filters",
  ]);

  return {
    columns: detail.columns,
    rows: detail.rows,
    sheetName: detail.sheetName,
    extraSheets: [
      buildMemberSheet(input.members, input.eventTypeColumns),
      buildEventTypeSheet(input.eventTypes, input.summary.totalEvents),
      buildRevenueSheet(input.summary),
      // Per-selection sheets sit between the roll-ups and the filter note, so
      // the workbook reads summary → detail-per-thing → provenance.
      ...buildPerMemberSheets(input.records, input.perMemberIds ?? [], taken),
      ...buildPerEventSheets(input.records, input.perEventTypes ?? [], taken),
      buildFilterSheet(input.filterRows, input.summary),
    ],
  };
}