/**
 * Excel Export using ExcelJS - TypeScript errors FIXED
 * Proper type alignment for ExcelJS library
 */

import ExcelJS from 'exceljs';

export async function generateAuditScheduleExcelJS(
  date: string,
  rows: any[],
  fileName: string = 'Audit Schedule'
) {
  try {
    const titleDate = formatDateForTitle(date);

    // Create workbook
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('Audit Schedule', {
      pageSetup: {
        paperSize: 9,
        orientation: 'landscape',
      },
    });

    // ── Set page margins ──
   (worksheet as any).pageMargins =  {
      left: 0.5,
      right: 0.5,
      top: 0.75,
      bottom: 0.75,
    };

    // ── Set column widths ──
    worksheet.columns = [
      { header: 'S#', width: 8 },
      { header: 'AUDIT CODE', width: 20 },
      { header: 'AUDIT TYPE', width: 18 },
      { header: 'COMPANY NAME', width: 30 },
      { header: 'STANDARD', width: 38 },
      { header: 'ACCREDITATION', width: 18 },
      { header: 'STAGE', width: 14 },
      { header: 'MODE', width: 14 },
      { header: 'COORDINATOR', width: 22 },
      { header: 'AUDIT DATE', width: 16 },
      { header: 'TIME', width: 12 },
      { header: 'LEAD AUDITOR', width: 20 },
      { header: 'STATUS', width: 16 },
    ];

    // ── ROW 1: Title ──
    const titleRow = worksheet.getRow(1);
    titleRow.getCell(1).value = titleDate;
    worksheet.mergeCells('A1:M1');

    titleRow.getCell(1).font = {
      bold: true,
      size: 16,
      color: { argb: 'FF1F2937' },
    };
    titleRow.getCell(1).alignment = {
      horizontal: 'left',
      vertical: 'top',
      wrapText: true,
    };
    titleRow.height = 28;

    // ── ROW 2: Empty spacing ──
    worksheet.getRow(2).height = 8;

    // ── ROW 3: Headers ──
    const headerRow = worksheet.getRow(3);
    headerRow.height = 24;

    const headers = [
      'S#',
      'AUDIT CODE',
      'AUDIT TYPE',
      'COMPANY NAME',
      'STANDARD',
      'ACCREDITATION',
      'STAGE',
      'MODE',
      'COORDINATOR',
      'AUDIT DATE',
      'TIME',
      'LEAD AUDITOR',
      'STATUS',
    ];

    headers.forEach((header, index) => {
      const cell = headerRow.getCell(index + 1);
      cell.value = header;

      cell.font = {
        bold: true,
        size: 11,
        color: { argb: 'FFFFFFFF' },
      };

      (cell.fill as any) = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FF0F766E' },
      };

      cell.border = {
        top: { style: 'thin', color: { argb: 'FF0F766E' } },
        left: { style: 'thin', color: { argb: 'FF0F766E' } },
        bottom: { style: 'thin', color: { argb: 'FF0F766E' } },
        right: { style: 'thin', color: { argb: 'FF0F766E' } },
      };

      cell.alignment = {
        horizontal: 'center',
        vertical: 'top',
        wrapText: true,
      };
    });

    // ── DATA ROWS ──
    rows.forEach((row, index) => {
      const rowIndex = 4 + index;
      const dataRow = worksheet.getRow(rowIndex);
      dataRow.height = 20;

      const rowData = [
        index + 1,
        row.audit_code || '—',
        prettyType(row.audit_type),
        row.company?.name || '—',
        row.standards?.map((s: any) => s.name).join(', ') || '—',
        row.accreditation || '—',
        row.audit_stage || '—',
        prettyMode(row.audit_mode),
        getCoordinator(row),
        row.schedule?.schedule_date || '—',
        row.audit_time_label || row.audit_time || '—',
        row.lead_auditor ? fullName(row.lead_auditor) : '—',
        row.status || '—',
      ];

      rowData.forEach((value, colIndex) => {
        const cell = dataRow.getCell(colIndex + 1);
        cell.value = value;

        // Alternating row colors
        const isEvenRow = index % 2 === 0;
        if (!isEvenRow) {
          (cell.fill as any) = {
            type: 'pattern',
            pattern: 'solid',
            fgColor: { argb: 'FFF9FAFB' },
          };
        }

        // Borders
        cell.border = {
          top: { style: 'thin', color: { argb: 'FFE5E7EB' } },
          left: { style: 'thin', color: { argb: 'FFE5E7EB' } },
          bottom: { style: 'thin', color: { argb: 'FFE5E7EB' } },
          right: { style: 'thin', color: { argb: 'FFE5E7EB' } },
        };

        // Text styling
        cell.font = {
          size: 10,
          color: { argb: 'FF1F2937' },
        };

        cell.alignment = {
          horizontal: colIndex === 0 ? 'center' : 'left',
          vertical: 'top',
          wrapText: true,
        };

        // ── STATUS COLUMN: Color-coded ──
        if (colIndex === 12 && value) {
          const statusText = String(value).toUpperCase();
          const statusColors = getStatusColors(statusText);

          (cell.fill as any) = {
            type: 'pattern',
            pattern: 'solid',
            fgColor: { argb: statusColors.bg },
          };

          cell.font = {
            bold: true,
            size: 10,
            color: { argb: statusColors.fg },
          };

          cell.alignment = {
            horizontal: 'center',
            vertical: 'top',
            wrapText: true,
          };
        }
      });
    });

    // ── FOOTER ──
    const footerRowIndex = 4 + rows.length + 1;
    const footerRow = worksheet.getRow(footerRowIndex);
    footerRow.height = 18;

    const footerCell = footerRow.getCell(1);
    footerCell.value = `Total Audits: ${rows.length} | Generated from QRS Certification System`;
    worksheet.mergeCells(`A${footerRowIndex}:M${footerRowIndex}`);

    footerCell.font = {
      size: 10,
      color: { argb: 'FF6B7280' },
      italic: true,
    };

    (footerCell.fill as any) = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFF3F4F6' },
    };

    footerCell.alignment = {
      horizontal: 'left',
      vertical: 'top',
    };

    // ── Download ──
    const timestamp = new Date().toISOString().split('T')[0];
    const safeDate = date.replace(/-/g, '-');
    const buffer = await workbook.xlsx.writeBuffer();

    const blob = new Blob([buffer], {
      type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    });
    const url = URL.createObjectURL(blob);
    const link = document.createElement('a');
    link.href = url;
    link.download = `${fileName}_${safeDate}_${timestamp}.xlsx`;
    document.body.appendChild(link);
    link.click();
    document.body.removeChild(link);
    URL.revokeObjectURL(url);
  } catch (error) {
    console.error('Excel export error:', error);
    throw error;
  }
}

// ── Helpers ──
function formatDateForTitle(dateStr: string): string {
  if (!dateStr) return 'AUDIT SCHEDULE';
  const [y, m, day] = dateStr.split('-');
  const dayNum = parseInt(day, 10);
  const suffix = ['th', 'st', 'nd', 'rd'][
    dayNum % 100 > 20 ? dayNum % 10 : dayNum % 100
  ] || 'th';
  const months = [
    'JANUARY', 'FEBRUARY', 'MARCH', 'APRIL', 'MAY', 'JUNE',
    'JULY', 'AUGUST', 'SEPTEMBER', 'OCTOBER', 'NOVEMBER', 'DECEMBER',
  ];
  const month = months[parseInt(m, 10) - 1] || '';
  return `AUDIT SCHEDULE FOR ${dayNum}${suffix.toUpperCase()} ${month} ${y}`;
}

function getStatusColors(status: string): { bg: string; fg: string } {
  const statusMap: Record<string, { bg: string; fg: string }> = {
    PENDING: { bg: 'FFFEF9C3', fg: 'FF854D0E' },
    CONFIRMED: { bg: 'FFDBEAFE', fg: 'FF1E40AF' },
    SCHEDULED: { bg: 'FFDBEAFE', fg: 'FF1E40AF' },
    IN_PROGRESS: { bg: 'FFE0E7FF', fg: 'FF3730A3' },
    COMPLETED: { bg: 'FFDCFCE7', fg: 'FF166534' },
    CANCELLED: { bg: 'FFFEE2E2', fg: 'FF991B1B' },
    RESCHEDULED: { bg: 'FFFFEDD5', fg: 'FF9A3412' },
  };
  return statusMap[status] || { bg: 'FFF1F5F9', fg: 'FF475569' };
}

function prettyType(t?: string | null): string {
  const map: Record<string, string> = {
    INITIAL: 'Initial',
    SURVEILLANCE: 'Surveillance',
    RECERTIFICATION: 'Re-Certification',
  };
  return map[String(t || '').toUpperCase()] || (t ? String(t) : '—');
}

function prettyMode(m?: string | null): string {
  const map: Record<string, string> = {
    ONSITE: 'Onsite',
    OFFICE: 'Office',
    REMOTE: 'Remote',
    HYBRID: 'Hybrid',
    ONLINE: 'Online',
  };
  return map[String(m || '').toUpperCase()] || (m ? String(m) : '—');
}

function fullName(u: any): string {
  if (!u) return '';
  const f = u.firstName || u.first_name || '';
  const l = u.lastName || u.last_name || '';
  const both = `${f} ${l}`.trim();
  return both || u.email || '';
}

function getCoordinator(row: any): string {
  const type = String(row.audit_type || '').toUpperCase();
  if (type === 'INITIAL') {
    return row.schedule?.coordinator ? fullName(row.schedule.coordinator) : '—';
  }
  const sub = row.submitted_by;
  if (sub) return fullName(sub) || '—';
  return row.schedule?.coordinator ? fullName(row.schedule.coordinator) : '—';
}