import { Injectable } from '@nestjs/common';
import * as ExcelJS from 'exceljs';
import type { AuditDetailResponse, AuditDetailRow } from './audit-detail-report.service';

// ── Brand Colors ──────────────────────────
const C = {
  teal: '0F766E',
  tealDark: '0B4F4A',
  tealLight: 'D5F2EC',
  white: 'FFFFFF',
  gray50: 'F8F9FB',
  gray200: 'E5E7EB',
  gray500: '6B7280',
  gray900: '111827',
  blue: '2563EB',
  green: '16A34A',
  greenDark: '15803D',
  orange: 'D97706',
  red: 'DC2626',
  redDark: 'B91C1C',
  purple: '7C3AED',
};

const COMPANY = 'CertifyHub — QRS & TQS';

function fmtDate(d?: string | null): string {
  if (!d) return '—';
  const dt = new Date(d);
  if (isNaN(dt.getTime())) return d;
  return dt.toLocaleDateString('en-GB', { day: '2-digit', month: 'short', year: 'numeric' });
}

@Injectable()
export class AuditReportExcelService {
  private createWorkbook(): ExcelJS.Workbook {
    const wb = new ExcelJS.Workbook();
    wb.creator = COMPANY;
    wb.created = new Date();
    wb.modified = new Date();
    return wb;
  }

  private addBrandedHeader(ws: ExcelJS.Worksheet, title: string, subtitle: string, colCount: number): number {
    ws.mergeCells(1, 1, 1, colCount);
    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, colCount);
    const r2 = ws.getCell('A2');
    r2.value = title;
    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 = 28;

    ws.mergeCells(3, 1, 3, colCount);
    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;
    return 5;
  }

  private addStatRow(
    ws: ExcelJS.Worksheet,
    startRow: number,
    stats: { label: string; value: string; color: string }[],
  ): number {
    stats.forEach((s, i) => {
      const cell = ws.getCell(startRow, i + 1);
      cell.value = s.label.toUpperCase();
      cell.font = { name: 'Calibri', size: 8, bold: true, color: { argb: C.gray500 } };
      cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: C.gray50 } };
      cell.alignment = { horizontal: 'center' };
      cell.border = {
        top: { style: 'thin', color: { argb: C.gray200 } },
        left: { style: 'thin', color: { argb: C.gray200 } },
        right: { style: 'thin', color: { argb: C.gray200 } },
      };
    });
    ws.getRow(startRow).height = 18;

    const valRow = startRow + 1;
    stats.forEach((s, i) => {
      const cell = ws.getCell(valRow, i + 1);
      cell.value = s.value;
      cell.font = { name: 'Calibri', size: 14, bold: true, color: { argb: s.color } };
      cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: C.gray50 } };
      cell.alignment = { horizontal: 'center', vertical: 'middle' };
      cell.border = {
        bottom: { style: 'thin', color: { argb: C.gray200 } },
        left: { style: 'thin', color: { argb: C.gray200 } },
        right: { style: 'thin', color: { argb: C.gray200 } },
      };
    });
    ws.getRow(valRow).height = 30;

    return valRow + 2;
  }

  private styleTableHeader(ws: ExcelJS.Worksheet, row: number, colCount: number, bgColor = C.teal) {
    ws.getRow(row).height = 24;
    for (let c = 1; c <= colCount; c++) {
      const cell = ws.getCell(row, c);
      cell.font = { name: 'Calibri', size: 9, bold: true, color: { argb: C.white } };
      cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: bgColor } };
      cell.alignment = { ...cell.alignment, vertical: 'middle' };
      cell.border = { bottom: { style: 'thin', color: { argb: C.tealDark } } };
    }
  }

  private styleDataRow(ws: ExcelJS.Worksheet, row: number, colCount: number, alt: boolean) {
    if (alt) {
      for (let c = 1; c <= colCount; c++) {
        ws.getCell(row, c).fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: C.gray50 } };
      }
    }
    for (let c = 1; c <= colCount; c++) {
      ws.getCell(row, c).border = { bottom: { style: 'hair', color: { argb: C.gray200 } } };
    }
  }

  private styleTotalRow(ws: ExcelJS.Worksheet, row: number, colCount: number) {
    for (let c = 1; c <= colCount; 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;
  }

  private addPageSetup(ws: ExcelJS.Worksheet, landscape = true) {
    ws.pageSetup = {
      orientation: landscape ? 'landscape' : 'portrait',
      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`,
    };
  }

  // ═══════════════════════════════════════════════
  //  AUDIT REPORT EXCEL
  // ═══════════════════════════════════════════════

  async generateAuditReportExcel(data: AuditDetailResponse, subtitle: string): Promise<Buffer> {
    const wb = this.createWorkbook();
    const ws = wb.addWorksheet('Audit Report', { views: [{ state: 'frozen', ySplit: 9 }] });
    this.addPageSetup(ws);

    const cols = 8;
    const s = data.summary;
    const rows = data.rows;

    ws.columns = [
      { width: 12 }, { width: 40 }, { width: 22 }, { width: 16 },
      { width: 16 }, { width: 16 }, { width: 16 }, { width: 10 },
    ];

    let row = this.addBrandedHeader(ws, 'Audit Report — Detail', subtitle, cols);

    row = this.addStatRow(ws, row, [
      { label: 'Total Assign', value: String(s.total_assigned), color: C.gray900 },
      { label: 'Performed', value: String(s.conducted_count), color: C.blue },
      { label: 'Upcoming', value: String(s.scheduled_count), color: C.purple },
      { label: 'Stage 1 Uploaded', value: String(s.stage1_uploaded), color: C.green },
      { label: 'Stage 1 Missing', value: String(s.stage1_missing), color: C.red },
      { label: 'Stage 2 Uploaded', value: String(s.stage2_uploaded), color: C.green },
      { label: 'Stage 2 Missing', value: String(s.stage2_missing), color: C.red },
    ]);

    const headers = ['CLIENT ID', 'CLIENT', 'AUDITOR', 'AUDIT DATE', 'AUDIT TYPE', 'STAGE 1 REPORT', 'STAGE 2 REPORT', 'SOURCE'];
    const aligns = ['center', 'left', 'left', 'center', 'center', 'center', 'center', 'center'];
    headers.forEach((h, i) => {
      ws.getCell(row, i + 1).value = h;
      ws.getCell(row, i + 1).alignment = { horizontal: aligns[i] as any, vertical: 'middle' };
    });
    this.styleTableHeader(ws, row, cols);
    row++;

    const reportLabel = (uploaded: boolean, conducted: boolean) =>
      uploaded ? 'Uploaded' : !conducted ? 'Scheduled' : 'Missing';
    const reportColor = (uploaded: boolean, conducted: boolean) =>
      uploaded ? C.greenDark : !conducted ? C.gray500 : C.red;

    rows.forEach((r: AuditDetailRow, idx) => {
      ws.getCell(row, 1).value = r.record_id;
      ws.getCell(row, 1).alignment = { horizontal: 'center' };
      ws.getCell(row, 1).font = { name: 'Calibri', size: 9, bold: true, color: { argb: C.teal } };

      ws.getCell(row, 2).value = r.client_name ?? '—';
      ws.getCell(row, 2).font = { name: 'Calibri', size: 10, bold: true };

      ws.getCell(row, 3).value = r.auditor;

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

      ws.getCell(row, 5).value = r.audit_type;
      ws.getCell(row, 5).alignment = { horizontal: 'center' };

      ws.getCell(row, 6).value = reportLabel(r.stage1_uploaded, r.conducted);
      ws.getCell(row, 6).alignment = { horizontal: 'center' };
      ws.getCell(row, 6).font = { name: 'Calibri', size: 9, bold: true, color: { argb: reportColor(r.stage1_uploaded, r.conducted) } };

      ws.getCell(row, 7).value = reportLabel(r.stage2_uploaded, r.conducted);
      ws.getCell(row, 7).alignment = { horizontal: 'center' };
      ws.getCell(row, 7).font = { name: 'Calibri', size: 9, bold: true, color: { argb: reportColor(r.stage2_uploaded, r.conducted) } };

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

      this.styleDataRow(ws, row, cols, idx % 2 === 1);
      row++;
    });

    // Totals row
    ws.getCell(row, 1).value = 'TOTAL';
    ws.getCell(row, 2).value = `${rows.length} audits`;
    ws.getCell(row, 6).value = `${s.stage1_uploaded} up / ${s.stage1_missing} miss`;
    ws.getCell(row, 6).alignment = { horizontal: 'center' };
    ws.getCell(row, 7).value = `${s.stage2_uploaded} up / ${s.stage2_missing} miss`;
    ws.getCell(row, 7).alignment = { horizontal: 'center' };
    this.styleTotalRow(ws, row, cols);

    ws.autoFilter = { from: { row: row - rows.length - 1, column: 1 }, to: { row: row - 1, column: cols } };

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