import { Injectable } from '@nestjs/common';
import * as ExcelJS from 'exceljs';
import type { AgingReport, AgingRow } from './aging-analysis.service';

// ── QRS brand palette (matches existing Certification Report) ──
const PURPLE_DARK = 'FF4A0080';
const PURPLE = 'FF8B14D4';
const GRAY_BG = 'FFF8F9FB';
const GRAY_TXT = 'FF6B7280';
const BORDER = 'FFE5E7EB';
const WHITE = 'FFFFFFFF';

// Aging bucket font colors
const RED = 'FFB91C1C';
const ORANGE = 'FFC2410C';
const AMBER = 'FFB45309';
const GREEN = 'FF15803D';

@Injectable()
export class AgingReportExcelService {
  async build(report: AgingReport): Promise<Buffer> {
    const wb = new ExcelJS.Workbook();
    wb.creator = 'Quality Registrar Systems';

    const sheetName = (report.meta.category || 'Certification Report').slice(0, 31);
    const ws = wb.addWorksheet(sheetName, {
      views: [{ state: 'frozen', ySplit: 5 }],
    });

    const headers = [
      'S.No',
      'Certificate No',
      'Company Name',
      'Standard',
      'Category',
      'Originally Registered',
      'Issue Date',
      'Expire Date',
      'Aging Bucket',
      'Days to Expiry',
      'Status',
    ];
    const NCOL = headers.length; // 11
    const widths = [8, 26, 42, 12, 20, 18, 15, 15, 16, 14, 14];
    widths.forEach((w, i) => (ws.getColumn(i + 1).width = w));

    // ── Title block (rows 1–3, each merged) ──
    ws.mergeCells(1, 1, 1, NCOL);
    ws.mergeCells(2, 1, 2, NCOL);
    ws.mergeCells(3, 1, 3, NCOL);

    const titleLabel = report.meta.category
      ? `Aging Analysis — ${report.meta.category} — ${report.meta.period_label}`
      : `Aging Analysis — ${report.meta.period_label}`;

    const c1 = ws.getCell(1, 1);
    c1.value = 'QUALITY REGISTRAR SYSTEMS';
    c1.font = { name: 'Calibri', size: 14, bold: true, color: { argb: WHITE } };
    c1.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: PURPLE_DARK } };
    c1.alignment = { horizontal: 'center', vertical: 'middle' };
    ws.getRow(1).height = 26;

    const c2 = ws.getCell(2, 1);
    c2.value = titleLabel;
    c2.font = { name: 'Calibri', size: 11, bold: true, color: { argb: WHITE } };
    c2.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: PURPLE } };
    c2.alignment = { horizontal: 'center', vertical: 'middle' };
    ws.getRow(2).height = 20;

    const c3 = ws.getCell(3, 1);
    c3.value =
      `${report.summary.total} record(s)  |  Expired: ${report.summary.expired}  |  ` +
      `Expiring ≤90d: ${report.summary.expiringWithin90}  |  Source: ${report.meta.source_filter}  |  ` +
      `Generated: ${this.ddmmyyyy(report.meta.generated_at)}`;
    c3.font = { name: 'Calibri', size: 9, color: { argb: GRAY_TXT } };
    c3.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: GRAY_BG } };
    c3.alignment = { horizontal: 'center', vertical: 'middle' };
    ws.getRow(3).height = 16;

    // row 4 intentionally blank

    // ── Header row (row 5) ──
    const headerRow = ws.getRow(5);
    headers.forEach((h, i) => {
      const cell = headerRow.getCell(i + 1);
      cell.value = h;
      cell.font = { name: 'Calibri', size: 9, bold: true, color: { argb: WHITE } };
      cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: PURPLE } };
      cell.alignment = { horizontal: 'center', vertical: 'middle', wrapText: true };
      cell.border = this.thin();
    });
    headerRow.height = 22;

    // ── Data rows (row 6+) ──
    let r = 6;
    report.rows.forEach((row, idx) => {
      const xr = ws.getRow(r);
      const values: Array<string | number | Date> = [
        idx + 1,
        row.certificate_no,
        row.company_name,
        row.standard,
        row.category,
        this.toDateObj(row.originally_registered),
        this.toDateObj(row.issue_date),
        this.toDateObj(row.expire_date),
        row.aging_bucket,
        row.days_to_expiry ?? '',
        this.statusDisplay(row),
      ];
      values.forEach((v, i) => {
        const cell = xr.getCell(i + 1);
        cell.value = v as ExcelJS.CellValue;
        cell.font = { name: 'Calibri', size: 10 };
        cell.border = this.thin();
        const col = i + 1;
        if ([6, 7, 8].includes(col) && v instanceof Date) {
          cell.numFmt = 'dd mmm yyyy';
          cell.alignment = { horizontal: 'center', vertical: 'middle' };
        } else if (col === 1 || col === 10) {
          cell.alignment = { horizontal: 'center', vertical: 'middle' };
        } else {
          cell.alignment = { vertical: 'middle', wrapText: col === 3 };
        }
      });

      // Color the Aging Bucket cell (col 9) + Days to Expiry (col 10)
      const bucketCell = xr.getCell(9);
      bucketCell.font = { name: 'Calibri', size: 10, bold: true, color: { argb: this.bucketColor(row.aging_bucket) } };
      bucketCell.alignment = { horizontal: 'center', vertical: 'middle' };

      const dteCell = xr.getCell(10);
      const dte = row.days_to_expiry;
      dteCell.font = {
        name: 'Calibri',
        size: 10,
        bold: true,
        color: { argb: dte == null ? GRAY_TXT : dte < 0 ? RED : dte <= 90 ? ORANGE : GREEN },
      };

      xr.height = 16;
      r++;
    });

    // ── Footer total ──
    const footRow = ws.getRow(r + 1);
    ws.mergeCells(r + 1, 1, r + 1, NCOL);
    const fc = footRow.getCell(1);
    fc.value = `TOTAL: ${report.summary.total} certificates   |   Expired: ${report.summary.expired}`;
    fc.font = { name: 'Calibri', size: 10, bold: true, color: { argb: PURPLE_DARK } };
    fc.alignment = { horizontal: 'right', vertical: 'middle' };

    const buf = await wb.xlsx.writeBuffer();
    return Buffer.from(buf as ArrayBuffer);
  }

  // ── helpers ──
  private thin(): Partial<ExcelJS.Borders> {
    const s: ExcelJS.Border = { style: 'thin', color: { argb: BORDER } };
    return { top: s, bottom: s, left: s, right: s };
  }

  private bucketColor(bucket: string): string {
    if (bucket === 'Expired') return RED;
    if (bucket === '0-30 days') return ORANGE;
    if (bucket === '31-90 days') return AMBER;
    if (bucket === '180+ days') return GREEN;
    return GRAY_TXT;
  }

  /** Status column = registrar/team. Legacy: QRS/TQS/TEAM B/Unknown; new: source label */
  private statusDisplay(row: AgingRow): string {
    if (row.source === 'Manual') return row.status ?? '—';
    return row.source; // 'QRS & TQS'
  }

  private toDateObj(iso: string | null): Date | string {
    if (!iso) return '';
    const d = new Date(iso);
    return isNaN(d.getTime()) ? '' : d;
  }

  private ddmmyyyy(isoDateTime: string): string {
    const d = new Date(isoDateTime);
    const p = (n: number) => String(n).padStart(2, '0');
    return `${p(d.getDate())}/${p(d.getMonth() + 1)}/${d.getFullYear()}`;
  }
}