import { BadRequestException } from '@nestjs/common';
import * as ExcelJS from 'exceljs';

const SEASON_LABELS = ['Peak', 'High Shoulder', 'Low Shoulder', 'Off-Peak'] as const;
const SEASON_COUNT = 4;

/**
 * Sentinel returned by `cellNum` when the user typed the literal
 * "CLEAR" (case-insensitive) into a price cell — distinguishes
 * "intentionally clear this price" from "cell is blank" (no change).
 *
 * Why a Symbol: type-narrowable, can't collide with any user value,
 * never leaks past `importFromExcel` (the service maps it to `null`
 * before persisting).
 */
export const CLEAR_SENTINEL = Symbol.for('pricing-excel:CLEAR');
export type CellPriceValue = number | null | typeof CLEAR_SENTINEL;

// ─── Transport PVT sheet (per-vehicle) ───────────────────────────────────────
// A–F = identity (6 cols), G = Default Sell, H–K = Season Sell,
// L = Default Floor, M–P = Season Floor.
const T_PVT_SHEET = 'PVT — Per Vehicle';
const T_PVT_HEADERS = [
  'Row ID (do not edit)',
  'Route',
  'Short Code',
  'Category',
  'Vehicle Type',
  'Currency',
];
const T_PVT_WIDTHS = [38, 28, 14, 12, 18, 10];
const T_PVT_SELL_DEFAULT = 7; // col G
const T_PVT_SELL = 8; // col H — season_1_price
const T_PVT_FLOOR_DEFAULT = 12; // col L
const T_PVT_FLOOR = 13; // col M — floor_season_1_price

// ─── Transport SIC sheet (per-person) ────────────────────────────────────────
// A–E = identity (5 cols, no Vehicle Type), F = Default Sell,
// G–J = Season Sell, K = Default Floor, L–O = Season Floor.
const T_SIC_SHEET = 'SIC — Per Person';
const T_SIC_HEADERS = ['Row ID (do not edit)', 'Route', 'Short Code', 'Category', 'Currency'];
const T_SIC_WIDTHS = [38, 28, 14, 12, 10];
const T_SIC_SELL_DEFAULT = 6; // col F
const T_SIC_SELL = 7; // col G — season_1_price
const T_SIC_FLOOR_DEFAULT = 11; // col K
const T_SIC_FLOOR = 12; // col L — floor_season_1_price

// ─── Activity sheet ───────────────────────────────────────────────────────────
// A–F = identity (6 cols), G = Default Sell, H–K = Season Sell,
// L = Default Floor, M–P = Season Floor.
const A_SHEET = 'Activity Pricing';
const A_HEADERS = [
  'Row ID (do not edit)',
  'Activity',
  'Destination',
  'Ticket Type',
  'Age Group',
  'Currency',
];
const A_WIDTHS = [38, 28, 20, 20, 14, 10];
const A_SELL_DEFAULT = 7; // col G
const A_SELL = 8; // col H — season_1_price
const A_FLOOR_DEFAULT = 12; // col L
const A_FLOOR = 13; // col M — floor_season_1_price

// ─── Transport ────────────────────────────────────────────────────────────────

/**
 * `withFloor` (default true) controls whether the Floor columns appear
 * in the generated sheet. Callers without `transport_floor_pricing:view`
 * permission pass `false` so the workbook never exposes minimum-margin
 * data to operations / sales users.
 */
type TransportPricingRow = {
  id: string;
  mode?: string;
  category?: string | null;
  transport_service?: { from_city?: string; to_city?: string; short_code?: string } | null;
  vehicle_type?: { name?: string } | null;
  currency?: { code?: string } | null;
  default_price?: number | null;
  season_1_price?: number | null;
  season_2_price?: number | null;
  season_3_price?: number | null;
  season_4_price?: number | null;
  floor_default_price?: number | null;
  floor_season_1_price?: number | null;
  floor_season_2_price?: number | null;
  floor_season_3_price?: number | null;
  floor_season_4_price?: number | null;
};

type ActivityPricingRow = {
  id: string;
  age_group?: string | null;
  activity?: { name?: string; destination?: { name?: string } | null } | null;
  ticket?: { name?: string } | null;
  currency?: { code?: string } | null;
  default_price?: number | null;
  season_1_price?: number | null;
  season_2_price?: number | null;
  season_3_price?: number | null;
  season_4_price?: number | null;
  floor_default_price?: number | null;
  floor_season_1_price?: number | null;
  floor_season_2_price?: number | null;
  floor_season_3_price?: number | null;
  floor_season_4_price?: number | null;
};

/** Headers used for both sell + floor blocks. Defaults sit before the
 *  seasonal columns so importers can fill a single "fallback" price
 *  that applies whenever no season matches the travel date. */
function buildPriceBlockHeaders(suffix: 'Sell' | 'Floor'): string[] {
  return [`Default — ${suffix}`, ...SEASON_LABELS.map((s) => `${s} — ${suffix}`)];
}
const PRICE_BLOCK_COUNT = 1 + SEASON_COUNT; // default + N seasons

export async function buildTransportSample(
  rows: TransportPricingRow[],
  withFloor = true,
): Promise<Buffer> {
  const wb = new ExcelJS.Workbook();
  wb.creator = 'DMC Ops';
  wb.created = new Date();

  const pvtRows = rows.filter((r) => r.mode !== 'SIC');
  const sicRows = rows.filter((r) => r.mode === 'SIC');

  const sellHeaders = buildPriceBlockHeaders('Sell');
  const floorHeaders = buildPriceBlockHeaders('Floor');
  const extraHeaders = withFloor ? [...sellHeaders, ...floorHeaders] : sellHeaders;
  const extraWidthsCount = withFloor ? PRICE_BLOCK_COUNT * 2 : PRICE_BLOCK_COUNT;

  // PVT sheet — always included even if empty so user sees the format
  const pvtWs = wb.addWorksheet(T_PVT_SHEET);
  setupSheet(
    pvtWs,
    [...T_PVT_HEADERS, ...extraHeaders],
    [...T_PVT_WIDTHS, ...Array(extraWidthsCount).fill(16)],
    T_PVT_HEADERS.length,
  );
  for (const r of pvtRows) {
    const base = [
      r.id,
      r.transport_service
        ? `${r.transport_service.from_city} → ${r.transport_service.to_city}`
        : '—',
      r.transport_service?.short_code ?? '',
      r.category ?? '',
      r.vehicle_type?.name ?? '—',
      r.currency?.code ?? '',
      r.default_price ?? null,
      r.season_1_price ?? null,
      r.season_2_price ?? null,
      r.season_3_price ?? null,
      r.season_4_price ?? null,
    ];
    const floor = withFloor
      ? [
          r.floor_default_price ?? null,
          r.floor_season_1_price ?? null,
          r.floor_season_2_price ?? null,
          r.floor_season_3_price ?? null,
          r.floor_season_4_price ?? null,
        ]
      : [];
    const row = pvtWs.addRow([...base, ...floor]);
    stylePriceRow(row, T_PVT_SELL_DEFAULT, withFloor ? T_PVT_FLOOR_DEFAULT : -1);
  }
  pvtWs.getCell('A1').note = withFloor
    ? 'PVT (per-vehicle) rows. Edit Sell and Floor columns only — Default applies when no season matches the travel date. Type "CLEAR" in a price cell to reset it to no value. Do not modify Row ID or identity columns (A–F).'
    : 'PVT (per-vehicle) rows. Edit Sell columns only. Floor (minimum) pricing is hidden — contact admin for changes.';

  // SIC sheet — always included
  const sicWs = wb.addWorksheet(T_SIC_SHEET);
  setupSheet(
    sicWs,
    [...T_SIC_HEADERS, ...extraHeaders],
    [...T_SIC_WIDTHS, ...Array(extraWidthsCount).fill(16)],
    T_SIC_HEADERS.length,
  );
  for (const r of sicRows) {
    const base = [
      r.id,
      r.transport_service
        ? `${r.transport_service.from_city} → ${r.transport_service.to_city}`
        : '—',
      r.transport_service?.short_code ?? '',
      r.category ?? '',
      r.currency?.code ?? '',
      r.default_price ?? null,
      r.season_1_price ?? null,
      r.season_2_price ?? null,
      r.season_3_price ?? null,
      r.season_4_price ?? null,
    ];
    const floor = withFloor
      ? [
          r.floor_default_price ?? null,
          r.floor_season_1_price ?? null,
          r.floor_season_2_price ?? null,
          r.floor_season_3_price ?? null,
          r.floor_season_4_price ?? null,
        ]
      : [];
    const row = sicWs.addRow([...base, ...floor]);
    stylePriceRow(row, T_SIC_SELL_DEFAULT, withFloor ? T_SIC_FLOOR_DEFAULT : -1);
  }
  sicWs.getCell('A1').note = withFloor
    ? 'SIC (per-person / shared coach) rows. Edit Sell and Floor columns only — Default applies when no season matches the travel date. Type "CLEAR" in a price cell to reset it to no value. Do not modify Row ID or identity columns (A–E).'
    : 'SIC (per-person / shared coach) rows. Edit Sell columns only. Floor (minimum) pricing is hidden — contact admin for changes.';

  const ab = await wb.xlsx.writeBuffer();
  return Buffer.from(ab);
}

/**
 * `acceptFloor` defaults to true. When false (caller lacks floor:edit
 * permission), the parsed result omits all floor_* fields so the
 * downstream save will leave existing floor prices untouched. The
 * helper additionally surfaces `floorEditCount` so the service can
 * reject the upload (403) when the user submitted floor edits they
 * weren't allowed to make — silent strip used to hide the rejection
 * behind a misleading success toast.
 */
export async function parseTransportImport(
  buffer: Buffer,
  acceptFloor = true,
): Promise<{
  rows: TransportParsedRow[];
  floorEditCount: number;
}> {
  // PVT
  const pvt = await _parseSheet(
    buffer,
    T_PVT_SHEET,
    (row) => readPriceBlock(row, T_PVT_SELL_DEFAULT, T_PVT_FLOOR_DEFAULT, acceptFloor),
    false,
  );
  // SIC
  const sic = await _parseSheet(
    buffer,
    T_SIC_SHEET,
    (row) => readPriceBlock(row, T_SIC_SELL_DEFAULT, T_SIC_FLOOR_DEFAULT, acceptFloor),
    false,
  );

  const combined = [...pvt, ...sic];
  if (combined.length === 0) {
    throw new BadRequestException(
      'No data rows found. Ensure the file has a "PVT — Per Vehicle" or "SIC — Per Person" sheet with data.',
    );
  }

  // Floor-edit detection runs even when acceptFloor=false — that's how
  // we know to 403 instead of silently stripping. The PVT/SIC scans
  // above set acceptFloor as told; here we re-scan with acceptFloor=true
  // ONLY to count floor cells that DID have values, so the service can
  // tell the user "you submitted N floor edits but lack permission".
  let floorEditCount = 0;
  if (!acceptFloor) {
    const pvtFloors = await _parseSheet(
      buffer,
      T_PVT_SHEET,
      (row) => readPriceBlock(row, T_PVT_SELL_DEFAULT, T_PVT_FLOOR_DEFAULT, true),
      false,
    );
    const sicFloors = await _parseSheet(
      buffer,
      T_SIC_SHEET,
      (row) => readPriceBlock(row, T_SIC_SELL_DEFAULT, T_SIC_FLOOR_DEFAULT, true),
      false,
    );
    floorEditCount = countFloorEdits([...pvtFloors, ...sicFloors]);
  }

  return { rows: combined, floorEditCount };
}

// ─── Activity ─────────────────────────────────────────────────────────────────

export async function buildActivitySample(
  rows: ActivityPricingRow[],
  withFloor = true,
): Promise<Buffer> {
  const wb = new ExcelJS.Workbook();
  wb.creator = 'DMC Ops';
  wb.created = new Date();

  const sellHeaders = buildPriceBlockHeaders('Sell');
  const floorHeaders = buildPriceBlockHeaders('Floor');
  const extraHeaders = withFloor ? [...sellHeaders, ...floorHeaders] : sellHeaders;
  const extraWidthsCount = withFloor ? PRICE_BLOCK_COUNT * 2 : PRICE_BLOCK_COUNT;

  const ws = wb.addWorksheet(A_SHEET);
  setupSheet(
    ws,
    [...A_HEADERS, ...extraHeaders],
    [...A_WIDTHS, ...Array(extraWidthsCount).fill(16)],
    A_HEADERS.length,
  );

  for (const r of rows) {
    const base = [
      r.id,
      r.activity?.name ?? '—',
      r.activity?.destination?.name ?? '—',
      r.ticket?.name ?? 'General',
      r.age_group ?? '',
      r.currency?.code ?? '',
      r.default_price ?? null,
      r.season_1_price ?? null,
      r.season_2_price ?? null,
      r.season_3_price ?? null,
      r.season_4_price ?? null,
    ];
    const floor = withFloor
      ? [
          r.floor_default_price ?? null,
          r.floor_season_1_price ?? null,
          r.floor_season_2_price ?? null,
          r.floor_season_3_price ?? null,
          r.floor_season_4_price ?? null,
        ]
      : [];
    const row = ws.addRow([...base, ...floor]);
    stylePriceRow(row, A_SELL_DEFAULT, withFloor ? A_FLOOR_DEFAULT : -1);
  }

  ws.getCell('A1').note = withFloor
    ? 'Edit the Sell and Floor columns only — Default applies when no season matches the travel date. Type "CLEAR" in a price cell to reset it to no value. Do not modify Row ID or the identity columns (A–F).'
    : 'Edit the Sell columns only. Floor (minimum) pricing is hidden — contact admin for changes.';

  const ab = await wb.xlsx.writeBuffer();
  return Buffer.from(ab);
}

/**
 * Same shape as `parseTransportImport` — returns `{ rows, floorEditCount }`
 * so the activity-pricing service can 403 with a real count when the
 * caller lacks `activity_floor_pricing:edit` but tried to edit floor.
 */
export async function parseActivityImport(
  buffer: Buffer,
  acceptFloor = true,
): Promise<{ rows: ActivityParsedRow[]; floorEditCount: number }> {
  const rows = await _parseSheet(
    buffer,
    A_SHEET,
    (row) => readPriceBlock(row, A_SELL_DEFAULT, A_FLOOR_DEFAULT, acceptFloor),
  );

  let floorEditCount = 0;
  if (!acceptFloor) {
    const withFloors = await _parseSheet(
      buffer,
      A_SHEET,
      (row) => readPriceBlock(row, A_SELL_DEFAULT, A_FLOOR_DEFAULT, true),
    );
    floorEditCount = countFloorEdits(withFloors);
  }

  return { rows, floorEditCount };
}

// ─── Shared helpers ───────────────────────────────────────────────────────────

export type TransportParsedRow = { id: string } & ReturnType<typeof readPriceBlock>;
export type ActivityParsedRow = { id: string } & ReturnType<typeof readPriceBlock>;

/** Reads the Default + N seasonal cells for both sell and (optionally)
 *  floor into the canonical entity field names. Each value is `number`,
 *  `null` (blank cell = no change), or `CLEAR_SENTINEL` (user typed
 *  "CLEAR" = explicit null write). */
function readPriceBlock(
  row: ExcelJS.Row,
  sellDefaultCol: number,
  floorDefaultCol: number,
  includeFloor: boolean,
) {
  const sell: Record<string, CellPriceValue> = {
    default_price: cellNum(row.getCell(sellDefaultCol)),
  };
  for (let i = 0; i < SEASON_COUNT; i++) {
    sell[`season_${i + 1}_price`] = cellNum(row.getCell(sellDefaultCol + 1 + i));
  }
  if (!includeFloor) return sell;
  const floor: Record<string, CellPriceValue> = {
    floor_default_price: cellNum(row.getCell(floorDefaultCol)),
  };
  for (let i = 0; i < SEASON_COUNT; i++) {
    floor[`floor_season_${i + 1}_price`] = cellNum(row.getCell(floorDefaultCol + 1 + i));
  }
  return { ...sell, ...floor };
}

/** True when the caller submitted a real edit on any floor_* field
 *  (numeric value OR an explicit CLEAR). Blanks don't count — those
 *  are "no change" in this import model. */
function countFloorEdits(rows: ({ id: string } & Record<string, CellPriceValue>)[]): number {
  let count = 0;
  for (const r of rows) {
    for (const [k, v] of Object.entries(r)) {
      if (!k.startsWith('floor_')) continue;
      if (v !== null && v !== undefined) count++;
    }
  }
  return count;
}

async function _parseSheet<T extends object>(
  buffer: Buffer,
  sheetName: string,
  priceMapper: (row: ExcelJS.Row) => T,
  required = true,
): Promise<({ id: string } & T)[]> {
  const wb = new ExcelJS.Workbook();
  // ExcelJS types accept ArrayBuffer; Buffer is structurally compatible at runtime
  await wb.xlsx.load(buffer as unknown as ArrayBuffer);
  const ws = wb.getWorksheet(sheetName);
  if (!ws) {
    if (!required) return [];
    throw new BadRequestException(`Sheet "${sheetName}" not found in the uploaded file`);
  }

  const results: ({ id: string } & T)[] = [];
  ws.eachRow((row, rowNum) => {
    if (rowNum <= 2) return; // row 1 = warning banner, row 2 = header
    const id = cellStr(row.getCell(1));
    if (!id) return;
    results.push({ id, ...priceMapper(row) });
  });

  if (required && results.length === 0) {
    throw new BadRequestException('No data rows found in the uploaded file');
  }
  return results;
}

function setupSheet(
  ws: ExcelJS.Worksheet,
  headers: string[],
  widths: number[],
  identityCols: number,
) {
  headers.forEach((_, i) => {
    ws.getColumn(i + 1).width = widths[i] ?? 14;
  });

  // Row 1 — warning banner merged across all columns
  const totalCols = headers.length;
  const bannerRow = ws.addRow([
    '⚠  DO NOT EDIT the grey columns — they are for reference only.   Edit only the  🔵 Sell  and  🟠 Floor  price columns.  Type "CLEAR" to reset a price.',
  ]);
  bannerRow.height = 28;
  ws.mergeCells(1, 1, 1, totalCols);
  const bannerCell = bannerRow.getCell(1);
  bannerCell.font = { bold: true, size: 11, color: { argb: 'FF92400E' } };
  bannerCell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFFEF3C7' } };
  bannerCell.alignment = { vertical: 'middle', horizontal: 'center', wrapText: false };

  // Row 2 — column headers
  const hdrRow = ws.addRow(headers);
  hdrRow.height = 30;
  hdrRow.eachCell((cell, colNum) => {
    const isIdentity = colNum <= identityCols;
    // sell block: Default + N seasons, then floor block (also Default + N seasons)
    const isSell = colNum > identityCols && colNum <= identityCols + PRICE_BLOCK_COUNT;
    cell.font = { bold: true, color: { argb: 'FFFFFFFF' } };
    cell.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: isIdentity ? 'FF374151' : isSell ? 'FF1D4ED8' : 'FFB45309' },
    };
    cell.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true };
  });
}

/** Styles a single data row. `sellDefaultStart` points at the Default
 *  Sell column; the function paints `PRICE_BLOCK_COUNT` cells starting
 *  there for sell, and another `PRICE_BLOCK_COUNT` from `floorDefaultStart`
 *  for floor when present. */
function stylePriceRow(row: ExcelJS.Row, sellDefaultStart: number, floorDefaultStart: number) {
  // Identity cells — grey fill signals read-only; data validation blocks edits in Excel
  for (let i = 1; i < sellDefaultStart; i++) {
    const cell = row.getCell(i);
    cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFE5E7EB' } };
    cell.dataValidation = {
      type: 'custom',
      formulae: ['FALSE'],
      showErrorMessage: true,
      errorStyle: 'stop',
      errorTitle: 'Read Only',
      error: 'This cell cannot be edited. Only the Sell and Floor price columns are editable.',
    };
  }

  for (let i = 0; i < PRICE_BLOCK_COUNT; i++) {
    const sellCell = row.getCell(sellDefaultStart + i);
    sellCell.numFmt = '#,##0.00';
    sellCell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFDBEAFE' } };

    // floorDefaultStart < 0 → floor columns omitted from this sheet (no perm).
    if (floorDefaultStart > 0) {
      const floorCell = row.getCell(floorDefaultStart + i);
      floorCell.numFmt = '#,##0.00';
      floorCell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFFEF3C7' } };
    }
  }
}

function cellStr(cell: ExcelJS.Cell): string {
  const v = cell.value;
  if (v === null || v === undefined) return '';
  return String(v).trim();
}

/** Returns the numeric value of a price cell, OR `null` when blank, OR
 *  `CLEAR_SENTINEL` when the user typed the literal "CLEAR" (case-
 *  insensitive) — the service maps that to an explicit `null` write so
 *  ops can reset a price they previously set. */
function cellNum(cell: ExcelJS.Cell): CellPriceValue {
  const v = cell.value;
  if (v === null || v === undefined || v === '') return null;
  if (typeof v === 'string') {
    const trimmed = v.trim();
    if (trimmed === '') return null;
    if (trimmed.toUpperCase() === 'CLEAR') return CLEAR_SENTINEL;
    const n = parseFloat(trimmed);
    return isNaN(n) ? null : n;
  }
  if (typeof v === 'number') return v;
  const n = parseFloat(String(v));
  return isNaN(n) ? null : n;
}