import { Injectable, BadRequestException, NotFoundException } from '@nestjs/common';
import * as XLSX from 'xlsx';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { Certificate } from './certificate.entity';
import { CreateCertificateDto } from './create-certificate.dto';
import { Codebook } from './codebook.entity';
import * as XlsxPopulate from 'xlsx-populate';

// ─── shared pagination response shape ────────────────────────────────────────
export interface PaginatedResult<T> {
  data: T[];
  total: number;
  page: number;
  limit: number;
  lastPage: number;
  hasNext: boolean;
  hasPrev: boolean;
}

function paginate<T>(
  data: T[],
  total: number,
  page: number,
  limit: number,
): PaginatedResult<T> {
  const lastPage = Math.max(1, Math.ceil(total / limit));
  return {
    data,
    total,
    page,
    limit,
    lastPage,
    hasNext: page < lastPage,
    hasPrev: page > 1,
  };
}

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

  // ─────────────────────────────────────────────────────────────────────────
  // HELPER — parse Excel dates (unchanged)
  // ─────────────────────────────────────────────────────────────────────────
  private parseExcelDate(value: any): Date | null {
    if (!value) return null;

    if (typeof value === 'string') {
      const d = new Date(value);
      return isNaN(d.getTime()) ? null : d;
    }

    if (typeof value === 'number') {
      const excelEpoch = new Date(1899, 11, 30); // Excel day 1 is 1899-12-31
      return new Date(excelEpoch.getTime() + value * 24 * 60 * 60 * 1000);
    }

    return null;
  }

  // ─────────────────────────────────────────────────────────────────────────
  // IMPORT EXCEL — with duplicate skip
  // ─────────────────────────────────────────────────────────────────────────
  async importExcel(filePath: string) {
    const workbook = XLSX.readFile(filePath);
    const sheetName = workbook.SheetNames[0];
    const sheet = workbook.Sheets[sheetName];
    const rows: any[] = XLSX.utils.sheet_to_json(sheet, { defval: null });

    const mappedRows: CreateCertificateDto[] = rows.map((row) => ({
      cert_no: row['cert_no'] || row['Certificate No'] || 'N/A',
      company_name: row['company_name'] || row['Company Name'] || 'N/A',
      standard: row['standard'] || row['Standard'] || 'N/A',
      orginally_reg:
        row['orginally_reg'] || row['Originally Registered'] || null,
      issue_date: row['issue_date'] || row['Issue Date'] || null,
      expire_date: row['expire_date'] || row['Expire Date'] || null,
      status: row['status'] || row['Status'] || 'Unknown',
    }));

    let insertedCount = 0;
    let skippedCount = 0;

    for (const record of mappedRows) {
      if (!record.cert_no || !record.company_name) {
        skippedCount++;
        continue;
      }

      // skip exact duplicate cert_no
      const exists = await this.certificateRepo.findOne({
        where: { cert_no: record.cert_no.trim() },
      });
      if (exists) {
        skippedCount++;
        continue;
      }

      const cert: Partial<Certificate> = {
        cert_no: record.cert_no.trim(),
        company_name: record.company_name.trim(),
        standard: record.standard,
        orginally_reg: this.parseExcelDate(record.orginally_reg),
        issue_date: this.parseExcelDate(record.issue_date),
        expire_date: this.parseExcelDate(record.expire_date),
        status: record.status,
      };

      const entity = this.certificateRepo.create(cert);
      await this.certificateRepo.save(entity);
      insertedCount++;
    }

    return {
      message: 'Excel imported successfully',
      inserted: insertedCount,
      skipped: skippedCount,
      totalRows: mappedRows.length,
    };
  }

  // ─────────────────────────────────────────────────────────────────────────
  // GET ALL CERTIFICATES — paginated + sortable
  // ─────────────────────────────────────────────────────────────────────────
  async getAllCertificates(options: {
    page: number;
    limit: number;
    sort: string;
    order: string;
  }): Promise<PaginatedResult<Certificate>> {
    const { page, limit, sort, order } = options;
    const skip = (page - 1) * limit;

    const allowedSort = [
      'id',
      'cert_no',
      'company_name',
      'standard',
      'issue_date',
      'expire_date',
      'status',
    ];
    const safeSort = allowedSort.includes(sort) ? sort : 'id';
    const safeOrder = order.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';

    const [data, total] = await this.certificateRepo.findAndCount({
      order: { [safeSort]: safeOrder } as any,
      skip,
      take: limit,
    });

    return paginate(data, total, page, limit);
  }

  // ─────────────────────────────────────────────────────────────────────────
  // SEARCH — paginated + multi-filter + date range
  // ─────────────────────────────────────────────────────────────────────────
  async searchCertificates(query: {
    q?: string;
    standard?: string;
    status?: string;
    from_date?: string;
    to_date?: string;
    month?: number;
    year?: number;
    page: number;
    limit: number;
  }): Promise<PaginatedResult<Certificate>> {
    const { page, limit } = query;
    const skip = (page - 1) * limit;

    const qb = this.certificateRepo
      .createQueryBuilder('cert')
      .orderBy('cert.id', 'DESC')
      .skip(skip)
      .take(limit);

    if (query.q?.trim()) {
      const term = `%${query.q.trim().toLowerCase()}%`;
      qb.andWhere(
        `(LOWER(cert.cert_no) LIKE :term OR LOWER(cert.company_name) LIKE :term)`,
        { term },
      );
    }

    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),
      });
    }

    if (query.month && query.month >= 1 && query.month <= 12) {
      qb.andWhere('MONTH(cert.issue_date) = :month', { month: query.month });
    }

    if (query.year && query.year >= 2000) {
      qb.andWhere('YEAR(cert.issue_date) = :year', { year: query.year });
    }

    const [data, total] = await qb.getManyAndCount();
    return paginate(data, total, page, limit);
  }

  // ─────────────────────────────────────────────────────────────────────────
  // STATS — totals by standard, status, recent 30 days
  // ─────────────────────────────────────────────────────────────────────────
  async getStats(): Promise<{
    total: number;
    byStandard: { standard: string; count: number }[];
    byStatus: { status: string; count: number }[];
    recentCount: number;
  }> {
    const total = await this.certificateRepo.count();

    const byStandard = await this.certificateRepo
      .createQueryBuilder('cert')
      .select('cert.standard', 'standard')
      .addSelect('COUNT(*)', 'count')
      .groupBy('cert.standard')
      .orderBy('count', 'DESC')
      .getRawMany();

    const byStatus = await this.certificateRepo
      .createQueryBuilder('cert')
      .select('cert.status', 'status')
      .addSelect('COUNT(*)', 'count')
      .groupBy('cert.status')
      .orderBy('count', 'DESC')
      .getRawMany();

    const thirtyDaysAgo = new Date();
    thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);
    const recentCount = await this.certificateRepo
      .createQueryBuilder('cert')
      .where('cert.created_at >= :date', { date: thirtyDaysAgo })
      .getCount();

    return {
      total,
      byStandard: byStandard.map((r) => ({
        standard: r.standard,
        count: Number(r.count),
      })),
      byStatus: byStatus.map((r) => ({
        status: r.status,
        count: Number(r.count),
      })),
      recentCount,
    };
  }

  // ─────────────────────────────────────────────────────────────────────────
  // GET ALL CODEBOOKS — paginated + keyword search
  // ─────────────────────────────────────────────────────────────────────────
  async getAllCodebooks(options: {
    page: number;
    limit: number;
    search?: string;
  }): Promise<PaginatedResult<Codebook>> {
    const { page, limit, search } = options;
    const skip = (page - 1) * limit;

    const qb = this.codebookRepo
      .createQueryBuilder('cb')
      .orderBy('cb.id', 'DESC')
      .skip(skip)
      .take(limit);

    if (search?.trim()) {
      const s = `%${search.trim().toLowerCase()}%`;
      qb.andWhere(
        `LOWER(cb.eac_iaf_codes) LIKE :s
          OR LOWER(cb.division) LIKE :s
          OR LOWER(cb.group) LIKE :s
          OR LOWER(cb.nace_rev2_description) LIKE :s`,
        { s },
      );
    }

    const [data, total] = await qb.getManyAndCount();
    return paginate(data, total, page, limit);
  }

  // ─────────────────────────────────────────────────────────────────────────
  // HELPER — extract plain text from RichText or normal cell (unchanged)
  // ─────────────────────────────────────────────────────────────────────────
  private getCellValue(cell: any): string | undefined {
    if (!cell) return undefined;

    try {
      if (typeof cell.text === 'function') {
        const t = cell.text();
        if (t !== undefined && t !== null) {
          const s = String(t).trim().replace(/\s+/g, ' ');
          if (s.length) return s;
        }
      }

      const val =
        typeof cell.value === 'function' ? cell.value() : (cell.value ?? cell);

      if (val == null) return undefined;
      if (typeof val === 'string' || typeof val === 'number') {
        return String(val).trim().replace(/\s+/g, ' ');
      }

      const extractText = (v: any): string => {
        if (v == null) return '';
        if (typeof v === 'string' || typeof v === 'number')
          return String(v).trim();

        if (typeof v === 'object') {
          if (Array.isArray(v))
            return v.map(extractText).filter(Boolean).join(' ');

          if (v._richText && Array.isArray(v._richText)) {
            return v._richText.map(extractText).filter(Boolean).join(' ');
          }

          if (typeof v.value === 'function') {
            try {
              const r = v.value();
              if (r !== undefined && r !== null) return extractText(r);
            } catch {}
          }
          if (typeof v.text === 'function') {
            try {
              const r = v.text();
              if (r !== undefined && r !== null) return extractText(r);
            } catch {}
          }

          if (typeof v._text === 'string' || typeof v._text === 'number')
            return String(v._text).trim();
          if (typeof v.text === 'string' || typeof v.text === 'number')
            return String(v.text).trim();
          if (typeof v.t === 'string' || typeof v.t === 'number')
            return String(v.t).trim();

          const vals = Object.values(v);
          if (vals.length)
            return vals.map(extractText).filter(Boolean).join(' ');
        }

        return '';
      };

      const out = extractText(val).trim().replace(/\s+/g, ' ');
      return out.length ? out : undefined;
    } catch {
      return undefined;
    }
  }

  // ─────────────────────────────────────────────────────────────────────────
  // HELPER — get cell background color (unchanged)
  // ─────────────────────────────────────────────────────────────────────────
  private getCellColor(sheet: any, cellAddress: string): string | undefined {
    try {
      const cell = sheet.cell(cellAddress);
      const fill = cell.style('fill');
      if (!fill) return undefined;

      if (
        fill.type === 'solid' &&
        fill.color &&
        typeof fill.color === 'object'
      ) {
        if (fill.color.rgb) {
          const rgb = String(fill.color.rgb);
          if (rgb.length === 8) return `#${rgb.substring(2)}`;
          if (rgb.length === 6) return `#${rgb}`;
          return rgb.startsWith('#') ? rgb : `#${rgb}`;
        }
      }
    } catch {
      // ignore
    }
    return undefined;
  }

  /** Find certificates by company name for cert lookup */
  async findByCompanyName(companyName: string): Promise<Certificate[]> {
    if (!companyName?.trim()) return [];
    return this.certificateRepo
      .createQueryBuilder('cert')
      .where('LOWER(cert.company_name) LIKE :name', {
        name: `%${companyName.trim().toLowerCase()}%`,
      })
      .orderBy('cert.id', 'DESC')
      .getMany();
  }

  // ─────────────────────────────────────────────────────────────────────────
  // IMPORT CODEBOOK (unchanged)
  // ─────────────────────────────────────────────────────────────────────────
  async importCodebookExcel(filePath: string) {
    try {
      const workbook = await XlsxPopulate.fromFileAsync(filePath);
      const sheet = workbook.sheet(0);
      const usedRange = sheet.usedRange();
      const values = usedRange.value();

      if (!values || !values.length) {
        const headersRow = sheet.row(1);
        const headers: string[] = [];
        headersRow.cellCount().forEach?.(() => {});
        const lastCol =
          sheet.usedRange()._endColumnIndex ||
          (sheet.usedRange().value()[0] || []).length;
        for (let c = 1; c <= lastCol; c++) {
          const hcell = sheet.cell(1, c);
          headers.push(this.getCellValue(hcell) || `col${c}`);
        }
        const rows: any[] = [];
        const lastRow =
          sheet.usedRange()._endRowIndex || sheet.usedRange().value().length;

        for (let r = 2; r <= lastRow; r++) {
          const cells: any[] = [];
          for (let c = 1; c <= lastCol; c++) {
            cells.push(this.getCellValue(sheet.cell(r, c)) ?? null);
          }
          rows.push(cells);
        }

        values.length = 0;
        values.push(headers);
        for (const rr of rows) values.push(rr);
      }

      const headers = values[0].map((h: any) => (h ? String(h).trim() : ''));
      let insertedCount = 0;

      for (let i = 1; i < values.length; i++) {
        const row: any = {};
        for (let colIndex = 0; colIndex < headers.length; colIndex++) {
          const header = headers[colIndex];
          const cell = sheet.cell(i + 1, colIndex + 1);
          row[header] = this.getCellValue(cell) ?? null;
        }

        const codebook: Partial<Codebook> = {
          eac_iaf_codes: row['EAC/IAF Codes'] || undefined,
          division: row['Division'] || undefined,
          group: row['Group'] || undefined,
          class: row['class'] || undefined,
          nace_rev1: row['NACE Rev 1'] || undefined,
          nace_rev2_description: row['NACE Rev 2 DESCRPTION'] || undefined,
          risk: row['RISK'] || undefined,
          note: row['Note'] || undefined,
          eac_color: this.getCellColor(sheet, `A${i + 1}`),
          division_color: this.getCellColor(sheet, `B${i + 1}`),
          group_color: this.getCellColor(sheet, `C${i + 1}`),
        };

        const hasAny = Object.values(codebook).some(
          (v) => v !== null && v !== undefined && String(v).trim() !== '',
        );
        if (!hasAny) continue;

        const entity = this.codebookRepo.create(codebook);
        await this.codebookRepo.save(entity);
        insertedCount++;
      }

      return {
        message: 'Codebook Excel imported successfully',
        count: insertedCount,
      };
    } catch (error) {
      console.error('Error importing codebook:', error);
      throw error;
    }
  }

  // ═════════════════════════════════════════════════════════════════════════
  // ✅ NEW — LEGACY MANUAL ADD / UPDATE / DELETE / GET BY ID
  // ═════════════════════════════════════════════════════════════════════════

  /**
   * ✅ NEW — Manually add a legacy certificate record
   * Used by staff to add overseas client certs or fix missing legacy data
   */
  async addManualLegacy(dto: {
    cert_no: string;
    company_name: string;
    standard: string;
    orginally_reg?: string;
    issue_date?: string;
    expire_date?: string;
    status?: string;
  }): Promise<Certificate> {
    // Validate required fields
    if (
      !dto.cert_no?.trim() ||
      !dto.company_name?.trim() ||
      !dto.standard?.trim()
    ) {
      throw new BadRequestException(
        'cert_no, company_name, and standard are required',
      );
    }

    // Check for duplicate
    const existing = await this.certificateRepo.findOne({
      where: { cert_no: dto.cert_no.trim() },
    });

    if (existing) {
      throw new BadRequestException(
        `Certificate ${dto.cert_no.trim()} already exists in legacy records`,
      );
    }

    const cert: Partial<Certificate> = {
      cert_no: dto.cert_no.trim(),
      company_name: dto.company_name.trim(),
      standard: dto.standard.trim(),
      orginally_reg: dto.orginally_reg ? new Date(dto.orginally_reg) : null,
      issue_date: dto.issue_date ? new Date(dto.issue_date) : null,
      expire_date: dto.expire_date ? new Date(dto.expire_date) : null,
      status: dto.status?.trim() || 'QRS',
    };

    const entity = this.certificateRepo.create(cert);
    return this.certificateRepo.save(entity);
  }

  /**
   * ✅ NEW — Update a legacy certificate
   */
  async updateLegacy(
    id: number,
    dto: Partial<{
      cert_no: string;
      company_name: string;
      standard: string;
      orginally_reg: string;
      issue_date: string;
      expire_date: string;
      status: string;
    }>,
  ): Promise<Certificate> {
    const cert = await this.certificateRepo.findOne({ where: { id } });
    if (!cert) {
      throw new NotFoundException(`Legacy certificate ${id} not found`);
    }

    if (dto.cert_no) cert.cert_no = dto.cert_no.trim();
    if (dto.company_name) cert.company_name = dto.company_name.trim();
    if (dto.standard) cert.standard = dto.standard.trim();
    if (dto.orginally_reg !== undefined)
      cert.orginally_reg = dto.orginally_reg
        ? new Date(dto.orginally_reg)
        : null;
    if (dto.issue_date !== undefined)
      cert.issue_date = dto.issue_date ? new Date(dto.issue_date) : null;
    if (dto.expire_date !== undefined)
      cert.expire_date = dto.expire_date ? new Date(dto.expire_date) : null;
    if (dto.status) cert.status = dto.status.trim();

    return this.certificateRepo.save(cert);
  }

  /**
   * ✅ NEW — Delete a legacy certificate
   */
  async deleteLegacy(id: number): Promise<{ message: string }> {
    const cert = await this.certificateRepo.findOne({ where: { id } });
    if (!cert) {
      throw new NotFoundException(`Legacy certificate ${id} not found`);
    }
    await this.certificateRepo.remove(cert);
    return { message: 'Legacy certificate deleted successfully' };
  }

  /**
   * ✅ NEW — Get a single legacy certificate by ID
   */
  async getLegacyById(id: number): Promise<Certificate> {
    const cert = await this.certificateRepo.findOne({ where: { id } });
    if (!cert) {
      throw new NotFoundException(`Legacy certificate ${id} not found`);
    }
    return cert;
  }
}