import * as ExcelJS from 'exceljs';
import { MasterCounts, MasterRecord } from './my-schedule-master.types';

/**
 * Build the master schedule workbook and return an xlsx Buffer.
 * Pure presentation — exceljs only.
 */
export async function buildMasterScheduleWorkbook(
  records: MasterRecord[],
  counts: MasterCounts,
  opts?: { userName?: string },
): Promise<Buffer> {
  const workbook = new ExcelJS.Workbook();
  workbook.creator = 'QRS Certification System';
  workbook.created = new Date();
  const ws = workbook.addWorksheet('My Audit Schedule');

  // Title
  ws.mergeCells('A1:N1');
  ws.getCell('A1').value = 'MY AUDIT SCHEDULE — MASTER LIST';
  ws.getCell('A1').font = { size: 14, bold: true, color: { argb: 'FF4A0080' } };
  ws.getCell('A1').alignment = { horizontal: 'center' };

  // Counts banner
  ws.mergeCells('A2:N2');
  ws.getCell('A2').value =
    `Initial: ${counts.initial}    |    Surveillance: ${counts.surveillance}` +
    `    |    Re-Certification: ${counts.recertification}    |    Total: ${counts.total}` +
    (opts?.userName ? `        (${opts.userName})` : '');
  ws.getCell('A2').font = { size: 11, bold: true, color: { argb: 'FF334155' } };
  ws.getCell('A2').alignment = { horizontal: 'center' };

  // Header row
  const headers = [
    'S#', 'Audit Code', 'Audit Type', 'Company', 'Standard',
    'Accreditation', 'Stage', 'Mode', 'Coordinator', 'Group',
    'Date', 'Time', 'Lead Auditor', 'Status',
  ];
  const headerRow = ws.addRow(headers);
  headerRow.eachCell((cell) => {
    cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, size: 10 };
    cell.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FF4A0080' },
    };
    cell.alignment = { horizontal: 'left', vertical: 'middle' };
    cell.border = {
      top: { style: 'thin' }, left: { style: 'thin' },
      bottom: { style: 'thin' }, right: { style: 'thin' },
    };
  });

  // Data rows
  records.forEach((r) => {
    const added = ws.addRow([
      r.sno, r.audit_code, r.audit_type, r.company, r.standards,
      r.accreditation, r.stage, r.mode, r.coordinator, r.group,
      r.date, r.time, r.lead_auditor, r.status,
    ]);
    added.eachCell((cell) => {
      cell.font = { size: 10 };
      cell.alignment = { vertical: 'top', wrapText: true };
      cell.border = {
        top: { style: 'hair' }, left: { style: 'hair' },
        bottom: { style: 'hair' }, right: { style: 'hair' },
      };
    });
  });

  // Column widths
  const widths = [5, 22, 14, 28, 30, 14, 12, 10, 18, 8, 12, 12, 20, 13];
  widths.forEach((w, i) => (ws.getColumn(i + 1).width = w));

  ws.autoFilter = { from: { row: 3, column: 1 }, to: { row: 3, column: 14 } };
  ws.views = [{ state: 'frozen', ySplit: 3 }];

  const arrayBuffer = await workbook.xlsx.writeBuffer();
  return Buffer.from(arrayBuffer);
}