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

// ── Brand Colors (matching React table header #760E9E) ──
const C = {
  purple: '760E9E',
  purpleDark: '4A0762',
  purpleLight: 'F3E8F9',
  black: '000000',
  white: 'FFFFFF',
  gray50: 'F8F9FB',
  gray100: 'F3F4F6',
  gray200: 'E5E7EB',
  gray500: '6B7280',
  gray700: '374151',
  gray900: '111827',
};

function fmtDate(dateStr?: string | Date): string {
  if (!dateStr) return '';
  const d = new Date(dateStr);
  if (isNaN(d.getTime())) return '';
  return `${String(d.getDate()).padStart(2, '0')}/${String(d.getMonth() + 1).padStart(2, '0')}/${d.getFullYear()}`;
}

function borderCell(
  style: ExcelJS.BorderStyle = 'thin',
): Partial<ExcelJS.Borders> {
  const b: ExcelJS.Border = { style, color: { argb: C.black } };
  return { top: b, bottom: b, left: b, right: b };
}

@Injectable()
export class JobsExcelService {
  // ═══════════════════════════════════════════════════════
  //  MAIN ENTRY
  // ═══════════════════════════════════════════════════════

  async generateJobRegisterExcel(jobs: any[]): Promise<Buffer> {
    const wb = new ExcelJS.Workbook();
    wb.creator = 'Quality Registrar Systems';
    wb.created = new Date();

    const ws = wb.addWorksheet('Job Register', {
      views: [{ state: 'frozen', ySplit: 4 }],
      pageSetup: {
        orientation: 'landscape',
        fitToPage: true,
        fitToWidth: 1,
        fitToHeight: 0,
        paperSize: 9, // A4
        margins: {
          top: 0.5,
          bottom: 0.5,
          left: 0.4,
          right: 0.4,
          header: 0.3,
          footer: 0.3,
        },
      },
    });

    ws.headerFooter = {
      oddFooter: '&LQuality Registrar Systems&CPage &P of &N&R&D',
    };

    // ── Column widths (9 data columns) ──────────────────────
    ws.columns = [
      { width: 22 }, // Job No
      { width: 30 }, // Company Name
      { width: 14 }, // Doc Review
      { width: 14 }, // On Site Audit
      { width: 22 }, // Auditor
      { width: 12 }, // Employees
      { width: 14 }, // NACE/EAC
      { width: 10 }, // M/D
      { width: 10 }, // RISK
    ];

    const TOTAL_COLS = 9;
    let rowNum = 1;

    // ── HEADER ROW 1: Logo | Title | blank (page info in print header) ──
    rowNum = this.addHeaderRow1(ws, rowNum, TOTAL_COLS, jobs.length, wb);

    // ── HEADER ROW 2: "Job Register" + "Form 02-2" ──────────
    rowNum = this.addHeaderRow2(ws, rowNum, TOTAL_COLS);

    // ── Spacer ───────────────────────────────────────────────
    ws.getRow(rowNum).height = 4;
    rowNum++;

    // ── TABLE HEADER ─────────────────────────────────────────
    rowNum = this.addTableHeader(ws, rowNum, TOTAL_COLS);

    // ── DATA ROWS ─────────────────────────────────────────────
    rowNum = this.addDataRows(ws, rowNum, TOTAL_COLS, jobs);

    // ── FOOTER ────────────────────────────────────────────────
    rowNum++;
    this.addFooterRow(ws, rowNum, TOTAL_COLS);

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

  // ═══════════════════════════════════════════════════════
  //  HEADER ROW 1 — Logo | QUALITY REGISTRAR SYSTEMS | Page
  // ═══════════════════════════════════════════════════════
  private addHeaderRow1(
    ws: ExcelJS.Worksheet,
    rowNum: number,
    totalCols: number,
    totalJobs: number,
    wb: ExcelJS.Workbook,
  ): number {
    ws.getRow(rowNum).height = 60;

    // Col 1-2: Logo area (20%)
    ws.mergeCells(rowNum, 1, rowNum, 2);
    const logoCell = ws.getCell(rowNum, 1);

    // Try to embed logo image
    const logoPath = path.join(process.cwd(), 'assets', 'logo.png');
    if (fs.existsSync(logoPath)) {
      try {
        const logoId = wb.addImage({ filename: logoPath, extension: 'png' });
        // OR replace the entire try block with this:
        ws.addImage(logoId, {
          tl: { col: 0, row: rowNum - 1 } as any,
          br: { col: 2, row: rowNum } as any,
          editAs: 'oneCell',
        });
      } catch {
        logoCell.value = 'QRS';
        logoCell.font = {
          name: 'Arial',
          bold: true,
          size: 18,
          color: { argb: C.purple },
        };
      }
    } else {
      logoCell.value = 'QRS';
      logoCell.font = {
        name: 'Arial',
        bold: true,
        size: 18,
        color: { argb: C.purple },
      };
    }
    logoCell.alignment = { horizontal: 'center', vertical: 'middle' };
    logoCell.border = borderCell();

    // Col 3-7: Company title (60%)
    ws.mergeCells(rowNum, 3, rowNum, 7);
    const titleCell = ws.getCell(rowNum, 3);
    titleCell.value = 'QUALITY REGISTRAR\nSYSTEMS';
    titleCell.font = {
      name: 'Arial',
      bold: true,
      size: 22,
      color: { argb: C.black },
    };
    titleCell.alignment = {
      horizontal: 'center',
      vertical: 'middle',
      wrapText: true,
    };
    titleCell.border = borderCell();

    // Col 8-9: Page info (20%)
    ws.mergeCells(rowNum, 8, rowNum, totalCols);
    const pageCell = ws.getCell(rowNum, 8);
    pageCell.value = `${totalJobs} Records`;
    pageCell.font = { name: 'Arial', size: 9, color: { argb: C.gray500 } };
    pageCell.alignment = {
      horizontal: 'right',
      vertical: 'top',
      wrapText: false,
    };
    pageCell.border = borderCell();

    return rowNum + 1;
  }

  // ═══════════════════════════════════════════════════════
  //  HEADER ROW 2 — "Job Register" + "Form 02-2"
  // ═══════════════════════════════════════════════════════
  private addHeaderRow2(
    ws: ExcelJS.Worksheet,
    rowNum: number,
    totalCols: number,
  ): number {
    ws.getRow(rowNum).height = 24;
    ws.mergeCells(rowNum, 1, rowNum, totalCols);

    const cell = ws.getCell(rowNum, 1);
    cell.value = {
      richText: [
        {
          text: 'Job Register',
          font: {
            name: 'Arial',
            bold: true,
            size: 16,
            color: { argb: C.black },
          },
        },
        {
          text: '                                                                   Form 02-2',
          font: { name: 'Arial', size: 9, color: { argb: C.gray500 } },
        },
      ],
    };
    cell.alignment = { horizontal: 'center', vertical: 'middle' };
    cell.border = borderCell();

    return rowNum + 1;
  }

  // ═══════════════════════════════════════════════════════
  //  TABLE HEADER (thead tr)
  // ═══════════════════════════════════════════════════════
  private addTableHeader(
    ws: ExcelJS.Worksheet,
    rowNum: number,
    totalCols: number,
  ): number {
    ws.getRow(rowNum).height = 22;

    const headers = [
      'Job No',
      'Company Name',
      'Doc Review',
      'On Site Audit',
      'Auditor',
      'Employees',
      'NACE/EAC',
      'M/D',
      'RISK',
    ];
    const aligns: ExcelJS.Alignment['horizontal'][] = [
      'left',
      'left',
      'center',
      'center',
      'left',
      'center',
      'center',
      'center',
      'center',
    ];

    headers.forEach((h, i) => {
      const cell = ws.getCell(rowNum, i + 1);
      cell.value = h;
      cell.font = {
        name: 'Arial',
        bold: true,
        size: 9,
        color: { argb: C.white },
      };
      cell.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: C.gray900 },
      };
      cell.alignment = { horizontal: aligns[i], vertical: 'middle' };
      cell.border = {
        top: { style: 'medium', color: { argb: C.black } },
        bottom: { style: 'medium', color: { argb: C.black } },
        left: { style: 'thin', color: { argb: C.gray500 } },
        right: { style: 'thin', color: { argb: C.gray500 } },
      };
    });

    return rowNum + 1;
  }

  // ═══════════════════════════════════════════════════════
  //  DATA ROWS
  // ═══════════════════════════════════════════════════════
  private addDataRows(
    ws: ExcelJS.Worksheet,
    startRow: number,
    totalCols: number,
    jobs: any[],
  ): number {
    const aligns: ExcelJS.Alignment['horizontal'][] = [
      'left',
      'left',
      'center',
      'center',
      'left',
      'center',
      'center',
      'center',
      'center',
    ];

    let rowNum = startRow;

    jobs.forEach((job, idx) => {
      ws.getRow(rowNum).height = 18;

      const cells = this.extractCells(job);
      const isAlt = idx % 2 === 1;

      cells.forEach((val, ci) => {
        const cell = ws.getCell(rowNum, ci + 1);
        cell.value = val;
        cell.font = { name: 'Arial', size: 9, color: { argb: C.gray900 } };
        cell.alignment = {
          horizontal: aligns[ci],
          vertical: 'middle',
          wrapText: false,
        };

        if (isAlt) {
          cell.fill = {
            type: 'pattern',
            pattern: 'solid',
            fgColor: { argb: C.gray50 },
          };
        }

        cell.border = {
          bottom: { style: 'hair', color: { argb: C.gray200 } },
          left: { style: 'hair', color: { argb: C.gray200 } },
          right: { style: 'hair', color: { argb: C.gray200 } },
        };
      });

      rowNum++;
    });

    // Close outer border
    for (let c = 1; c <= totalCols; c++) {
      const lastCell = ws.getCell(rowNum - 1, c);
      lastCell.border = {
        ...lastCell.border,
        bottom: { style: 'medium', color: { argb: C.black } },
        left: lastCell.border?.left || {
          style: 'hair',
          color: { argb: C.gray200 },
        },
        right: lastCell.border?.right || {
          style: 'hair',
          color: { argb: C.gray200 },
        },
      };
    }
    // Left/right border on first col
    for (let r = startRow; r < rowNum; r++) {
      const first = ws.getCell(r, 1);
      first.border = {
        ...first.border,
        left: { style: 'medium', color: { argb: C.black } },
      };
      const last = ws.getCell(r, totalCols);
      last.border = {
        ...last.border,
        right: { style: 'medium', color: { argb: C.black } },
      };
    }

    return rowNum;
  }

  // ═══════════════════════════════════════════════════════
  //  FOOTER — Issue | Revision | Date  (matching React footer)
  // ═══════════════════════════════════════════════════════
  private addFooterRow(
    ws: ExcelJS.Worksheet,
    rowNum: number,
    totalCols: number,
  ) {
    ws.getRow(rowNum).height = 28;

    // 3 equal sections mapped across 9 columns: cols 1-3 | 4-6 | 7-9
    const sections = [
      { cols: [1, 3], label: 'Issue', value: '01' },
      { cols: [4, 6], label: 'Revision', value: '00' },
      { cols: [7, totalCols], label: 'Date', value: '20.02.2011' },
    ];

    sections.forEach(({ cols, label, value }) => {
      ws.mergeCells(rowNum, cols[0], rowNum, cols[1]);
      const cell = ws.getCell(rowNum, cols[0]);
      cell.value = {
        richText: [
          {
            text: `${label}\n`,
            font: { name: 'Arial', size: 9, color: { argb: C.gray700 } },
          },
          {
            text: value,
            font: {
              name: 'Arial',
              size: 10,
              bold: true,
              color: { argb: C.black },
            },
          },
        ],
      };
      cell.alignment = {
        horizontal: 'center',
        vertical: 'middle',
        wrapText: true,
      };
      cell.border = borderCell('medium');
    });
  }

  // ═══════════════════════════════════════════════════════
  //  CELL VALUE EXTRACTOR
  // ═══════════════════════════════════════════════════════
  private extractCells(job: any): string[] {
    return [
      (job.jobCodes || []).join(', ') || 'N/A',
      job.company?.name || 'N/A',
      fmtDate(job.docReview),
      fmtDate(job.date),
      job.leadAuditor
        ? `${job.leadAuditor.firstName} ${job.leadAuditor.lastName}`
        : 'N/A',
      String(job.numEmployees ?? 'N/A'),
      job.naceEacCodes || 'N/A',
      job.md || 'N/A',
      job.mdRisk || 'N/A',
    ];
  }
}