import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { Certificate } from './certificate.entity';
import { Codebook } from './codebook.entity';
import * as XLSX from 'xlsx';
import * as puppeteer from 'puppeteer';

export interface CertReportQuery {
  cert_no?:      string;
  company_name?: string;
  standard?:     string;
  status?:       string;
  from_date?:    string;
  to_date?:      string;
}

@Injectable()
export class ExcelReportService {
  constructor(
    @InjectRepository(Certificate, 'certification_db')
    private readonly certificateRepo: Repository<Certificate>,
    @InjectRepository(Codebook, 'certification_db')
    private readonly codebookRepo: Repository<Codebook>,
  ) {}

  // ─────────────────────────────────────────────────────────────────────────
  // SHARED DATA FETCHER — used by both Excel and PDF
  // ─────────────────────────────────────────────────────────────────────────
  private async fetchData(query: CertReportQuery): Promise<{
    rows:   Certificate[];
    totals: {
      count:      number;
      active:     number;
      expired:    number;
      byStandard: Record<string, number>;
    };
  }> {
    const qb = this.certificateRepo
      .createQueryBuilder('cert')
      .orderBy('cert.issue_date', 'DESC');

    if (query.cert_no?.trim()) {
      qb.andWhere('LOWER(cert.cert_no) LIKE :cert_no', {
        cert_no: `%${query.cert_no.trim().toLowerCase()}%`,
      });
    }
    if (query.company_name?.trim()) {
      qb.andWhere('LOWER(cert.company_name) LIKE :company_name', {
        company_name: `%${query.company_name.trim().toLowerCase()}%`,
      });
    }
    if (query.standard?.trim()) {
      qb.andWhere('LOWER(cert.standard) LIKE :standard', {
        standard: `%${query.standard.trim().toLowerCase()}%`,
      });
    }
    if (query.status?.trim()) {
      qb.andWhere('LOWER(cert.status) LIKE :status', {
        status: `%${query.status.trim().toLowerCase()}%`,
      });
    }
    if (query.from_date && query.to_date) {
      qb.andWhere('cert.issue_date BETWEEN :from_date AND :to_date', {
        from_date: new Date(query.from_date),
        to_date:   new Date(query.to_date),
      });
    } else if (query.from_date) {
      qb.andWhere('cert.issue_date >= :from_date', {
        from_date: new Date(query.from_date),
      });
    } else if (query.to_date) {
      qb.andWhere('cert.issue_date <= :to_date', {
        to_date: new Date(query.to_date),
      });
    }

    const rows = await qb.getMany();

    const byStandard: Record<string, number> = {};
    let active  = 0;
    let expired = 0;

    for (const r of rows) {
      const std = r.standard ?? 'Unknown';
      byStandard[std] = (byStandard[std] ?? 0) + 1;

      const statusLower = (r.status ?? '').toLowerCase();
      if (['qrs', 'tqs', 'active'].some(s => statusLower.includes(s))) active++;
      else expired++;
    }

    return {
      rows,
      totals: { count: rows.length, active, expired, byStandard },
    };
  }

  // ─────────────────────────────────────────────────────────────────────────
  // HELPER — format date safely
  // ─────────────────────────────────────────────────────────────────────────
  private fmt(date: Date | null | undefined): string {
    if (!date) return '—';
    try { return new Date(date).toLocaleDateString('en-GB'); }
    catch { return '—'; }
  }

  // ─────────────────────────────────────────────────────────────────────────
  // EXCEL REPORT
  // ─────────────────────────────────────────────────────────────────────────
  async generateExcel(query: CertReportQuery): Promise<Buffer> {
    const { rows, totals } = await this.fetchData(query);

    const wb = XLSX.utils.book_new();

    // ── Sheet 1: Certificates ──────────────────────────────────────────────
    const header = [
      '#',
      'Certificate No.',
      'Company Name',
      'Standard',
      'Originally Registered',
      'Issue Date',
      'Expiry Date',
      'Status',
    ];

    const dataRows = rows.map((r, i) => [
      i + 1,
      r.cert_no        ?? '',
      r.company_name   ?? '',
      r.standard       ?? '',
      this.fmt(r.orginally_reg),
      this.fmt(r.issue_date),
      this.fmt(r.expire_date),
      r.status         ?? '',
    ]);

    const totalsRow = [
      '',
      `TOTAL: ${totals.count} certificates`,
      '',
      '',
      '',
      '',
      '',
      `Active: ${totals.active} | Expired: ${totals.expired}`,
    ];

    const ws1 = XLSX.utils.aoa_to_sheet([header, ...dataRows, [], totalsRow]);
    ws1['!cols'] = [
      { wch: 5  },
      { wch: 26 },
      { wch: 42 },
      { wch: 14 },
      { wch: 22 },
      { wch: 14 },
      { wch: 14 },
      { wch: 14 },
    ];
    XLSX.utils.book_append_sheet(wb, ws1, 'Certificates');

    // ── Sheet 2: Summary ───────────────────────────────────────────────────
    const summaryData = [
      ['QRS Certificate Report — Summary'],
      [],
      ['Generated',     new Date().toLocaleDateString('en-GB')],
      ['Total Records', totals.count],
      ['Active',        totals.active],
      ['Expired',       totals.expired],
      [],
      ['Standard', 'Count'],
      ...Object.entries(totals.byStandard).map(([std, cnt]) => [std, cnt]),
    ];

    const ws2 = XLSX.utils.aoa_to_sheet(summaryData);
    ws2['!cols'] = [{ wch: 28 }, { wch: 14 }];
    XLSX.utils.book_append_sheet(wb, ws2, 'Summary');

    return XLSX.write(wb, { type: 'buffer', bookType: 'xlsx' });
  }

  // ─────────────────────────────────────────────────────────────────────────
  // PDF REPORT
  // ─────────────────────────────────────────────────────────────────────────
  async generatePdf(query: CertReportQuery): Promise<Buffer> {
    const { rows, totals } = await this.fetchData(query);

    const stamp = new Date().toLocaleDateString('en-GB', {
      day: '2-digit', month: 'long', year: 'numeric',
    });

    const dateRange = query.from_date || query.to_date
      ? `${query.from_date ?? '—'} to ${query.to_date ?? '—'}`
      : 'All dates';

    const stdPills = Object.entries(totals.byStandard)
      .map(([std, cnt]) => `
        <div class="pill">
          <span class="pill-std">${std}</span>
          <span class="pill-cnt">${cnt}</span>
        </div>`)
      .join('');

    const rowsHtml = rows.map((r, i) => {
      const statusLower = (r.status ?? '').toLowerCase();
      const isActive = ['qrs', 'tqs', 'active'].some(s => statusLower.includes(s));
      return `
        <tr class="${i % 2 === 0 ? 'even' : 'odd'}">
          <td class="center">${i + 1}</td>
          <td class="mono">${r.cert_no ?? '—'}</td>
          <td>${r.company_name ?? '—'}</td>
          <td><span class="std-badge std-${(r.standard ?? 'unk').toLowerCase().replace(/[^a-z]/g, '')}">${r.standard ?? '—'}</span></td>
          <td>${this.fmt(r.orginally_reg)}</td>
          <td>${this.fmt(r.issue_date)}</td>
          <td>${this.fmt(r.expire_date)}</td>
          <td><span class="status-badge ${isActive ? 'status-active' : 'status-expired'}">${r.status ?? '—'}</span></td>
        </tr>`;
    }).join('');

    const html = `<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8"/>
<style>
  * { box-sizing: border-box; margin: 0; padding: 0; }
  body { font-family: Arial, sans-serif; font-size: 10.5px; color: #1e293b; padding: 20px 24px; background: #fff; }
  .header { display: flex; justify-content: space-between; align-items: flex-start; margin-bottom: 18px; padding-bottom: 14px; border-bottom: 2px solid #2563eb; }
  .logo-row { display: flex; align-items: center; gap: 10px; }
  .logo-mark { width: 34px; height: 34px; background: #2563eb; border-radius: 7px; display: flex; align-items: center; justify-content: center; color: #fff; font-size: 12px; font-weight: 700; }
  .report-title { font-size: 18px; font-weight: 700; color: #1e293b; }
  .report-sub { font-size: 11px; color: #64748b; margin-top: 3px; }
  .meta { text-align: right; font-size: 10px; color: #64748b; line-height: 1.7; }
  .stats { display: flex; gap: 10px; margin-bottom: 14px; }
  .stat { flex: 1; background: #f8fafc; border: 1px solid #e2e8f0; border-left: 3px solid #2563eb; padding: 9px 13px; border-radius: 4px; }
  .stat-label { font-size: 9px; color: #94a3b8; text-transform: uppercase; letter-spacing: .06em; }
  .stat-value { font-size: 16px; font-weight: 700; color: #1e293b; margin-top: 2px; font-family: monospace; }
  .stat-value.green { color: #16a34a; }
  .stat-value.red   { color: #dc2626; }
  .pills-row { display: flex; flex-wrap: wrap; gap: 6px; margin-bottom: 14px; }
  .pill { display: flex; align-items: center; border: 1px solid #e2e8f0; border-radius: 4px; overflow: hidden; font-size: 10px; font-weight: 600; }
  .pill-std { background: #eff6ff; color: #2563eb; padding: 3px 8px; }
  .pill-cnt { background: #2563eb; color: #fff; padding: 3px 8px; }
  table { width: 100%; border-collapse: collapse; font-size: 10px; }
  thead th { background: #2563eb; color: #fff; padding: 7px 9px; text-align: left; font-weight: 600; font-size: 9.5px; letter-spacing: .04em; }
  thead th.center { text-align: center; }
  td { padding: 6px 9px; border-bottom: 1px solid #f1f5f9; vertical-align: middle; }
  tr.even td { background: #fff; }
  tr.odd  td { background: #f8fafc; }
  .center { text-align: center; }
  .mono { font-family: monospace; font-size: 10px; color: #0f172a; font-weight: 500; }
  .std-badge { display: inline-block; font-size: 9px; font-weight: 700; padding: 2px 6px; border-radius: 3px; text-transform: uppercase; }
  .std-ims { background: #faf5ff; color: #7c3aed; border: 1px solid #ddd6fe; }
  .std-qms { background: #eff6ff; color: #2563eb; border: 1px solid #bfdbfe; }
  .std-oha { background: #f0fdf4; color: #16a34a; border: 1px solid #bbf7d0; }
  .std-ems { background: #ecfdf5; color: #059669; border: 1px solid #a7f3d0; }
  .std-unk { background: #f8fafc; color: #64748b; border: 1px solid #e2e8f0; }
  .status-badge { display: inline-block; font-size: 9px; font-weight: 700; padding: 2px 7px; border-radius: 10px; }
  .status-active  { background: #dcfce7; color: #15803d; }
  .status-expired { background: #fee2e2; color: #dc2626; }
  .totals-row td { background: #eff6ff !important; font-weight: 700; border-top: 2px solid #2563eb; font-size: 10.5px; }
  .footer { margin-top: 14px; padding-top: 10px; border-top: 1px solid #e2e8f0; font-size: 9px; color: #94a3b8; display: flex; justify-content: space-between; }
</style>
</head>
<body>
  <div class="header">
    <div>
      <div class="logo-row">
        <div class="logo-mark">QRS</div>
        <div>
          <div class="report-title">Legacy Certificate Register — Report</div>
          <div class="report-sub">
            Date range: ${dateRange}
            ${query.standard     ? ` &nbsp;|&nbsp; Standard: ${query.standard}`    : ''}
            ${query.company_name ? ` &nbsp;|&nbsp; Company: ${query.company_name}` : ''}
            ${query.status       ? ` &nbsp;|&nbsp; Status: ${query.status}`        : ''}
          </div>
        </div>
      </div>
    </div>
    <div class="meta">Generated: ${stamp}<br/>Total Records: ${totals.count}</div>
  </div>

  <div class="stats">
    <div class="stat">
      <div class="stat-label">Total Certificates</div>
      <div class="stat-value">${totals.count.toLocaleString()}</div>
    </div>
    <div class="stat">
      <div class="stat-label">Active / Certified</div>
      <div class="stat-value green">${totals.active.toLocaleString()}</div>
    </div>
    <div class="stat">
      <div class="stat-label">Expired / Other</div>
      <div class="stat-value red">${totals.expired.toLocaleString()}</div>
    </div>
    <div class="stat">
      <div class="stat-label">Standards in Report</div>
      <div class="stat-value">${Object.keys(totals.byStandard).length}</div>
    </div>
  </div>

  <div class="pills-row">${stdPills}</div>

  <table>
    <thead>
      <tr>
        <th class="center">#</th>
        <th>Certificate No.</th>
        <th>Company Name</th>
        <th>Standard</th>
        <th>Originally Reg.</th>
        <th>Issue Date</th>
        <th>Expiry Date</th>
        <th>Status</th>
      </tr>
    </thead>
    <tbody>
      ${rowsHtml}
      <tr class="totals-row">
        <td colspan="3">TOTALS — ${totals.count} certificates</td>
        <td colspan="2">${Object.entries(totals.byStandard).map(([s, c]) => `${s}: ${c}`).join(' | ')}</td>
        <td></td>
        <td></td>
        <td>Active: ${totals.active} | Exp: ${totals.expired}</td>
      </tr>
    </tbody>
  </table>

  <div class="footer">
    <span>QRS Certification — Legacy Certificate Register</span>
    <span>Confidential — Generated ${stamp}</span>
    <span>Total: ${totals.count} records</span>
  </div>
</body>
</html>`;

    const browser = await puppeteer.launch({
      headless: true,
      args: ['--no-sandbox', '--disable-setuid-sandbox'],
    });
    try {
      const page = await browser.newPage();
      await page.setContent(html, { waitUntil: 'domcontentloaded' });
      const pdf = await page.pdf({
        format:          'A4',
        landscape:       true,
        printBackground: true,
        margin: { top: '10mm', right: '10mm', bottom: '10mm', left: '10mm' },
      });
      return Buffer.from(pdf);
    } finally {
      await browser.close();
    }
  }

  // ─────────────────────────────────────────────────────────────────────────
  // GET PREVIEW — returns JSON summary before download
  // Called by GET /api/excel/report/preview
  // ─────────────────────────────────────────────────────────────────────────
  async getPreview(query: CertReportQuery): Promise<{
    totalRecords: number;
    active:       number;
    expired:      number;
    byStandard:   Record<string, number>;
    sampleRows:   Certificate[];
  }> {
    const { rows, totals } = await this.fetchData(query);
    return {
      totalRecords: totals.count,
      active:       totals.active,
      expired:      totals.expired,
      byStandard:   totals.byStandard,
      sampleRows:   rows.slice(0, 5),
    };
  }
}