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

// Matches the shape returned by auditNcStatusReport()
export interface NcStatusExportRow {
  source: 'QRS' | 'TQS';
  audit_kind: 'client' | 'surveillance' | 'recertification';
  audit_id: number;
  company_name: string | null;
  audit_date: string | null;
  auditor_names: string[];
  nc_status: 'NC Raised' | 'NC Pending';
}
export interface NcStatusExportData {
  rows: NcStatusExportRow[];
  totals: { total: number; raised: number; pending: number };
}

const C = {
  teal: '0F766E', tealDark: '0B4F4A', tealLight: 'D5F2EC',
  white: 'FFFFFF', gray50: 'F8F9FB', gray200: 'E5E7EB', gray500: '6B7280', gray900: '111827',
  indigo: '4338CA', green: '16A34A', greenDark: '15803D', amber: 'B45309', amberDark: '92400E',
};
const COMPANY = 'CertifyHub — QRS & TQS';

function fmtDate(d?: string | null): string {
  if (!d) return '—';
  const dt = new Date(d);
  if (isNaN(dt.getTime())) return String(d);
  return dt.toLocaleDateString('en-GB', { day: '2-digit', month: 'short', year: 'numeric' });
}
const kindLabel = (k: NcStatusExportRow['audit_kind']) =>
  k === 'client' ? 'Initial Audit' : k === 'recertification' ? 'Re-Assessment' : 'Surveillance';

@Injectable()
export class AuditNcStatusExcelService {
  async generate(data: NcStatusExportData, subtitle: string): Promise<Buffer> {
    const wb = new ExcelJS.Workbook();
    wb.creator = COMPANY; wb.created = new Date(); wb.modified = new Date();
    const ws = wb.addWorksheet('NC Status', { views: [{ state: 'frozen', ySplit: 8 }] });
    ws.pageSetup = {
      orientation: 'landscape', fitToPage: true, fitToWidth: 1, fitToHeight: 0, paperSize: 9,
      margins: { top: 0.5, bottom: 0.5, left: 0.5, right: 0.5, header: 0.3, footer: 0.3 },
    };
    ws.headerFooter = { oddFooter: `&L&8${COMPANY}&C&8Page &P of &N&R&8Printed: &D` };

    const cols = 6;
    ws.columns = [
      { width: 46 }, { width: 12 }, { width: 18 }, { width: 16 }, { width: 30 }, { width: 16 },
    ];

    // ── branded header band ──
    ws.mergeCells(1, 1, 1, cols);
    const r1 = ws.getCell('A1');
    r1.value = COMPANY.toUpperCase();
    r1.font = { name: 'Calibri', size: 16, bold: true, color: { argb: C.white } };
    r1.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: C.teal } };
    r1.alignment = { horizontal: 'center', vertical: 'middle' };
    ws.getRow(1).height = 36;

    ws.mergeCells(2, 1, 2, cols);
    const r2 = ws.getCell('A2');
    r2.value = 'Audit → NC Status Report';
    r2.font = { name: 'Calibri', size: 12, bold: true, color: { argb: C.white } };
    r2.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: C.tealDark } };
    r2.alignment = { horizontal: 'center', vertical: 'middle' };
    ws.getRow(2).height = 26;

    ws.mergeCells(3, 1, 3, cols);
    const r3 = ws.getCell('A3');
    r3.value = `${subtitle}  |  Generated: ${fmtDate(new Date().toISOString())}`;
    r3.font = { name: 'Calibri', size: 10, italic: true, color: { argb: C.gray500 } };
    r3.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: C.gray50 } };
    r3.alignment = { horizontal: 'center', vertical: 'middle' };
    ws.getRow(3).height = 22;
    ws.getRow(4).height = 8;

    // ── KPI stat row (label row 5, value row 6) ──
    const t = data.totals;
    const pct = (n: number) => (t.total ? Math.round((n / t.total) * 100) : 0);
    const stats = [
      { label: 'Total Audits', value: String(t.total), sub: 'conducted', color: C.indigo },
      { label: 'NC Raised', value: String(t.raised), sub: `${pct(t.raised)}% of audits`, color: C.greenDark },
      { label: 'NC Pending', value: String(t.pending), sub: `${pct(t.pending)}% of audits`, color: C.amber },
    ];
    // each stat spans 2 columns
    stats.forEach((s, i) => {
      const c1 = i * 2 + 1;
      ws.mergeCells(5, c1, 5, c1 + 1);
      const lab = ws.getCell(5, c1);
      lab.value = s.label.toUpperCase();
      lab.font = { name: 'Calibri', size: 8, bold: true, color: { argb: C.gray500 } };
      lab.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: C.gray50 } };
      lab.alignment = { horizontal: 'center' };

      ws.mergeCells(6, c1, 6, c1 + 1);
      const val = ws.getCell(6, c1);
      val.value = `${s.value}   (${s.sub})`;
      val.font = { name: 'Calibri', size: 14, bold: true, color: { argb: s.color } };
      val.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: C.gray50 } };
      val.alignment = { horizontal: 'center', vertical: 'middle' };
    });
    ws.getRow(5).height = 18;
    ws.getRow(6).height = 30;
    ws.getRow(7).height = 8;

    // ── table header (row 8) ──
    let row = 8;
    const headers = ['CLIENT', 'SOURCE', 'AUDIT TYPE', 'AUDIT DATE', 'AUDITOR', 'NC STATUS'];
    const aligns = ['left', 'center', 'center', 'center', 'left', 'center'];
    headers.forEach((h, i) => {
      const cell = ws.getCell(row, i + 1);
      cell.value = h;
      cell.font = { name: 'Calibri', size: 9, bold: true, color: { argb: C.white } };
      cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: C.teal } };
      cell.alignment = { horizontal: aligns[i] as any, vertical: 'middle' };
      cell.border = { bottom: { style: 'thin', color: { argb: C.tealDark } } };
    });
    ws.getRow(row).height = 24;
    row++;

    // ── data rows ──
    data.rows.forEach((r, idx) => {
      const raised = r.nc_status === 'NC Raised';
      ws.getCell(row, 1).value = r.company_name ?? '—';
      ws.getCell(row, 1).font = { name: 'Calibri', size: 10, bold: true };
      ws.getCell(row, 1).alignment = { wrapText: true, vertical: 'middle' };

      ws.getCell(row, 2).value = r.source;
      ws.getCell(row, 2).alignment = { horizontal: 'center' };
      ws.getCell(row, 2).font = { name: 'Calibri', size: 9, bold: true, color: { argb: r.source === 'QRS' ? C.indigo : C.teal } };

      ws.getCell(row, 3).value = kindLabel(r.audit_kind);
      ws.getCell(row, 3).alignment = { horizontal: 'center' };

      ws.getCell(row, 4).value = fmtDate(r.audit_date);
      ws.getCell(row, 4).alignment = { horizontal: 'center' };

      ws.getCell(row, 5).value = r.auditor_names.join(', ') || '—';
      ws.getCell(row, 5).alignment = { wrapText: true, vertical: 'middle' };

      ws.getCell(row, 6).value = r.nc_status;
      ws.getCell(row, 6).alignment = { horizontal: 'center' };
      ws.getCell(row, 6).font = { name: 'Calibri', size: 9, bold: true, color: { argb: raised ? C.greenDark : C.amber } };

      if (idx % 2 === 1) {
        for (let c = 1; c <= cols; c++)
          ws.getCell(row, c).fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: C.gray50 } };
      }
      for (let c = 1; c <= cols; c++)
        ws.getCell(row, c).border = { bottom: { style: 'hair', color: { argb: C.gray200 } } };
      row++;
    });

    // ── total row ──
    ws.getCell(row, 1).value = `TOTAL: ${data.rows.length} audits`;
    ws.getCell(row, 5).value = `Raised ${t.raised}`;
    ws.getCell(row, 5).alignment = { horizontal: 'right' };
    ws.getCell(row, 6).value = `Pending ${t.pending}`;
    ws.getCell(row, 6).alignment = { horizontal: 'center' };
    for (let c = 1; c <= cols; c++) {
      const cell = ws.getCell(row, c);
      cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: C.tealLight } };
      cell.font = { name: 'Calibri', size: 10, bold: true, color: { argb: C.tealDark } };
      cell.border = {
        top: { style: 'medium', color: { argb: C.teal } },
        bottom: { style: 'medium', color: { argb: C.teal } },
      };
    }
    ws.getRow(row).height = 26;

    ws.autoFilter = { from: { row: 8, column: 1 }, to: { row: row - 1, column: cols } };
    return Buffer.from(await wb.xlsx.writeBuffer());
  }
}