import { Injectable } from '@nestjs/common';
import * as ExcelJS from 'exceljs';
import { ReportData } from './report.service';

const C = {
  purple: 'FF8B14D4', purpleDark: 'FF4A0080', light: 'FFF8F5FF', border: 'FFEDE8FF',
  white: 'FFFFFFFF', gray50: 'FFF8F9FB', dark: 'FF1A0440', gray500: 'FF6B7280',
};

@Injectable()
export class ExcelService {
  /** Branded workbook: one sheet per bucket, with a title band + KPI + styled table. */
  async build(report: ReportData): Promise<Buffer> {
    const wb = new ExcelJS.Workbook();
    wb.creator = 'Quality Registrar Systems';
    wb.created = new Date();

    for (const bucket of report.buckets) {
      const ws = wb.addWorksheet(bucket.label, { views: [{ state: 'frozen', ySplit: 5 }] });
      ws.columns = [
        { width: 8 }, { width: 30 }, { width: 46 }, { width: 22 }, { width: 18 }, { width: 16 }, { width: 16 }, { width: 12 }, { width: 20 },
      ];
      const cols = 9;

      // Title band
      ws.mergeCells(1, 1, 1, cols);
      const t1 = ws.getCell('A1');
      t1.value = 'QUALITY REGISTRAR SYSTEMS';
      t1.font = { name: 'Calibri', size: 14, bold: true, color: { argb: C.white } };
      t1.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: C.purpleDark } };
      t1.alignment = { horizontal: 'center', vertical: 'middle' };
      ws.getRow(1).height = 30;

      ws.mergeCells(2, 1, 2, cols);
      const t2 = ws.getCell('A2');
      t2.value = `Certification Report — ${bucket.label} — ${report.monthName} ${report.year}`;
      t2.font = { name: 'Calibri', size: 11, bold: true, color: { argb: C.white } };
      t2.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: C.purple } };
      t2.alignment = { horizontal: 'center', vertical: 'middle' };
      ws.getRow(2).height = 22;

      ws.mergeCells(3, 1, 3, cols);
      const t3 = ws.getCell('A3');
      t3.value = `${bucket.count} record(s)  |  Generated: ${new Date().toLocaleDateString('en-GB')}`;
      t3.font = { name: 'Calibri', size: 9, italic: true, color: { argb: C.gray500 } };
      t3.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: C.gray50 } };
      t3.alignment = { horizontal: 'center', vertical: 'middle' };
      ws.getRow(3).height = 18;
      ws.getRow(4).height = 6;

      // Header row (row 5)
      const headers = ['S.No', 'Certificate No', 'Company Name', 'Standard', 'Originally Registered', 'Issue Date', 'Expire Date', 'Status', 'Certificate Type'];
      headers.forEach((h, i) => {
        const cell = ws.getCell(5, 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.purple } };
        cell.alignment = { horizontal: 'center', vertical: 'middle' };
        cell.border = { bottom: { style: 'thin', color: { argb: C.purpleDark } } };
      });
      ws.getRow(5).height = 22;

      // Data rows
      let row = 6;
      bucket.rows.forEach((r, i) => {
        const vals = [i + 1, r.cert_no, r.company_name, r.standard, r.orginally_reg, r.issue_date, r.expire_date, r.status, r.certType ?? bucket.label];
        vals.forEach((v, c) => {
          const cell = ws.getCell(row, c + 1);
          cell.value = v as any;
          cell.font = { name: 'Calibri', size: 10 };
          cell.alignment = { vertical: 'middle', wrapText: c === 2 };
          if (i % 2 === 1) cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: C.gray50 } };
        });
        row++;
      });

      if (bucket.rows.length === 0) {
        ws.mergeCells(row, 1, row, cols);
        const empty = ws.getCell(row, 1);
        empty.value = 'No certificates in this category for this month.';
        empty.font = { name: 'Calibri', size: 10, italic: true, color: { argb: C.gray500 } };
        empty.alignment = { horizontal: 'center' };
      }

      ws.autoFilter = { from: { row: 5, column: 1 }, to: { row: 5, column: cols } };
    }

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