import {
  BadRequestException,
  Injectable,
  NotFoundException,
} from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { v4 as uuidv4 } from 'uuid';
import { createHash } from 'crypto';
import * as QRCode from 'qrcode';
import { NewCertificate } from './entities/new-certificate.entity';
import { CertificateVersionHistory } from './entities/certificate_version_history';
import { Company } from '../companies/entities/company.entity';
import { Standard } from '../standards/entities/standard.entity';
import { ExcelService } from '../excel/excel.service';
import { CreateCertificateDto } from './dto/create-certificate.dto';
import { UpdateCertificateDto } from './dto/update-certificate.dto';

@Injectable()
export class CertificatesService {
  updateFull(
    id: number,
    dto: UpdateCertificateDto,
  ): NewCertificate | PromiseLike<NewCertificate> {
    throw new Error('Method not implemented.');
  }

  constructor(
    @InjectRepository(NewCertificate, 'scheme_dbs')
    private readonly certRepo: Repository<NewCertificate>,

    @InjectRepository(CertificateVersionHistory, 'scheme_dbs')
    private readonly versionRepo: Repository<CertificateVersionHistory>,

    @InjectRepository(Company, 'scheme_dbs')
    private readonly companyRepo: Repository<Company>,

    @InjectRepository(Standard, 'scheme_dbs')
    private readonly standardRepo: Repository<Standard>,

    private readonly excelService: ExcelService,
  ) {}

  /** Build verification URL based on domain */
  /** Build verification URL based on domain */
  public buildVerificationUrl(certificate: NewCertificate): string {
    // ✅ Read from env with sensible fallback
    const localBase =
      process.env.VERIFY_URL_LOCAL || 'https://qrsyst.com/verify-certificate';
    const internationalBase =
      process.env.VERIFY_URL_INTERNATIONAL ||
      'https://cert-verification.qrs-intl.com/verify';

    const base =
      certificate.verification_domain === 'international'
        ? internationalBase
        : localBase;

    return `${base}?token=${certificate.qrcode_token}&fp=${certificate.fingerprint}`;
  }

  /** Helper: get standard short code */
  private getStandardShort(standard: Standard): string {
    return standard.title
      ? standard.title
          .split(' ')
          .map((w) => w[0])
          .join('')
          .substring(0, 3)
          .toUpperCase()
      : standard.name.slice(0, 3).toUpperCase();
  }

  /** Parse legacy cert_no string like "ADU-2687,5985,4271,NQU-60132" */
  private parseLegacyCertNo(certNo: string): string[] {
    const parts = certNo.split(',').map((s) => s.trim());
    let pfx = '';
    return parts.map((p) => {
      if (p.includes('-')) {
        pfx = p.split('-')[0];
        return p;
      }
      return pfx + '-' + p;
    });
  }

  /** Expand legacy standard label like "IMS,HACCP" into [QMS, EMS, OHA, HACCP] */
  private expandLegacyStandard(label: string): string[] {
    const parts = label.split(',').map((s) => s.trim().toUpperCase());
    const out: string[] = [];
    parts.forEach((p) => {
      if (p === 'IMS') out.push('QMS', 'EMS', 'OHA');
      else if (p === 'QMS' || p === 'ISO 9001') out.push('QMS');
      else if (p === 'EMS' || p === 'ISO 14001') out.push('EMS');
      else if (p === 'OHA' || p === 'ISO 45001') out.push('OHA');
      else if (p === 'HACCP' || p === 'ISO 22000') out.push('HACCP');
      else out.push(p);
    });
    return out;
  }

  /** Find legacy cert_no for a specific standard from excel records */
  private findLegacyMatch(
    legacyCerts: any[],
    standard: Standard,
  ): string | null {
    if (!legacyCerts?.length) return null;
    const shortCode = this.getStandardShort(standard);

    for (const legacy of legacyCerts) {
      const expanded = this.expandLegacyStandard(legacy.standard);
      const numbers = this.parseLegacyCertNo(legacy.cert_no);
      const idx = expanded.indexOf(shortCode);
      if (idx !== -1 && numbers[idx]) return numbers[idx];
    }
    return null;
  }

  /** Find previous cert_no — checks new_certificate first, then falls back to legacy */
  private async findPreviousCertNo(
    companyId: number,
    companyName: string,
    standard: Standard,
    legacyCerts: any[],
  ): Promise<{
    cert_no: string;
    source: 'new_certificate' | 'api_excel';
    existing_row_id?: number;
  } | null> {
    console.log('🔍 findPreviousCertNo CALLED');
    console.log('  Company:', companyName);
    console.log(
      '  Standard:',
      standard.name,
      `(${this.getStandardShort(standard)})`,
    );
    console.log('  Legacy certs available:', legacyCerts.length);

    // Check new_certificate table first
    const newCert = await this.certRepo.findOne({
      where: {
        company: { id: companyId },
        standard: { id: standard.id },
        status: 'active',
      },
    });

    if (newCert) {
      console.log('  ✅ FOUND in new_certificate:', newCert.certificate_no);
      return {
        cert_no: newCert.certificate_no,
        source: 'new_certificate',
        existing_row_id: newCert.id,
      };
    }

    console.log('  ❌ Not found in new_certificate table');

    // Fallback to legacy
    const legacyMatch = this.findLegacyMatch(legacyCerts, standard);
    if (legacyMatch) {
      console.log('  ✅ FOUND in legacy /api/excel:', legacyMatch);
      return {
        cert_no: legacyMatch,
        source: 'api_excel',
      };
    }

    console.log('  ❌ Not found in legacy either — will generate new');
    return null;
  }
  private async generateCertificateNo(
    country: string,
    city: string,
    standard: Standard,
  ): Promise<string> {
    if (!country || !city || !standard) {
      throw new Error(
        'Missing required fields for certificate number generation.',
      );
    }

    // Special case: UAE
    const countryCode =
      country.trim().toUpperCase() === 'UNITED ARAB EMIRATES'
        ? 'UAE'
        : country.substring(0, 2).toUpperCase();

    const cityCode = city.substring(0, 2).toUpperCase();
    const standardShort = this.getStandardShort(standard);
    const prefix = `${countryCode}-${cityCode}-${standardShort}`;

    const letters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    const digits = '0123456789';
    const tokenLength = 5;

    for (let attempt = 0; attempt < 10; attempt++) {
      const positions = Array.from({ length: tokenLength }, (_, i) => i);
      const digitPositions = new Set<number>();

      while (digitPositions.size < 3) {
        const pos = positions[Math.floor(Math.random() * positions.length)];
        digitPositions.add(pos);
      }

      let code = '';
      for (let i = 0; i < tokenLength; i++) {
        code += digitPositions.has(i)
          ? digits[Math.floor(Math.random() * digits.length)]
          : letters[Math.floor(Math.random() * letters.length)];
      }

      const candidate = `${prefix}-${code}`;

      const exists = await this.certRepo.findOne({
        where: { certificate_no: candidate },
      });
      if (!exists) return candidate;
    }

    throw new Error(
      'Unable to generate unique certificate number after multiple attempts.',
    );
  }

  async generateQrCode(
    token: string,
    fingerprint?: string,
    domain: 'local' | 'international' = 'local',
  ): Promise<string> {
    // ✅ Read from env — same source as buildVerificationUrl (consistent)
    // Fixed: was 'verify-certificates' (plural) — must match the route on qrsyst.com
    const verificationUrls = {
      local:
        process.env.VERIFY_URL_LOCAL || 'https://qrsyst.com/verify-certificate',
      international:
        process.env.VERIFY_URL_INTERNATIONAL ||
        'https://cert-verification.qrs-intl.com/verify',
    };

    const baseUrl = verificationUrls[domain] || verificationUrls.local;

    // Construct full verification URL with token + fingerprint
    let qrUrl = `${baseUrl}?token=${token}`;
    if (fingerprint) qrUrl += `&fp=${fingerprint}`;
    console.log('📌 QR URL EMBEDDED:', qrUrl);

    // Generate QR code as Base64 with proper size & margin
    const qrCodeDataUrl = await QRCode.toDataURL(qrUrl, {
      errorCorrectionLevel: 'M',
      type: 'image/png',
      margin: 1,
      width: 250,
      scale: 6,
    });

    return qrCodeDataUrl;
  }

  private async generateUniqueToken(): Promise<string> {
    const token = uuidv4();

    const exists = await this.certRepo.findOne({
      where: { qrcode_token: token },
    });
    if (!exists) return token;

    return this.generateUniqueToken();
  }

  /** Generate a unique fingerprint */
  private async generateFingerprint(): Promise<string> {
    const raw = uuidv4();
    const fp = createHash('sha256')
      .update(raw)
      .digest('base64url')
      .slice(0, 12);

    const exists = await this.certRepo.findOne({ where: { fingerprint: fp } });
    if (!exists) return fp;

    return this.generateFingerprint();
  }
  /** CREATE CERTIFICATE */
  async create(dto: CreateCertificateDto): Promise<{
    certificates: Array<{
      certificate: NewCertificate;
      qrCodeDataUrl: string;
      verification_url: string;
      action: string;
      source: string;
    }>;
    summary: {
      total: number;
      reused: number;
      generated: number;
    };
  }> {
    console.log('==================================');
    console.log('📥 CREATE CERT REQUEST');
    console.log('  company_id:', dto.company_id);
    console.log('  cert_type:', dto.cert_type);
    console.log('  standards:', dto.standard_ids || [dto.standard_id]);
    // ✅ NEW — log manual cert nos if frontend sent any
    if (dto.manual_cert_numbers && Object.keys(dto.manual_cert_numbers).length > 0) {
      console.log('  manual_cert_numbers:', dto.manual_cert_numbers);
    }

    const company = await this.companyRepo.findOne({
      where: { id: dto.company_id },
    });
    if (!company) throw new NotFoundException('Company not found');

    console.log('  company name:', company.name);

    // Support multiple standards
    const standardIds =
      dto.standard_ids && dto.standard_ids.length > 0
        ? dto.standard_ids
        : [dto.standard_id];

    // Fetch legacy certs once for this company
    const legacyCerts = await this.excelService.findByCompanyName(company.name);

    console.log('📋 Legacy certs loaded from /api/excel:', legacyCerts.length);
    if (legacyCerts.length > 0) {
      console.log('  Legacy records found:');
      legacyCerts.forEach((c, i) => {
        console.log(
          `    ${i + 1}. cert_no="${c.cert_no}", standard="${c.standard}"`,
        );
      });
    } else {
      console.log('  (No legacy records found for this company)');
    }
    console.log('==================================');

    const results: Array<{
      certificate: NewCertificate;
      qrCodeDataUrl: string;
      verification_url: string;
      action: string;
      source: string;
    }> = [];

    for (const standardId of standardIds) {
      if (!standardId) continue;

      const standard = await this.standardRepo.findOne({
        where: { id: standardId },
      });
      if (!standard) continue;

      const standardShort = this.getStandardShort(standard);

      // Find previous cert (in new_certificate or legacy)
      const previous = await this.findPreviousCertNo(
        company.id,
        company.name,
        standard,
        legacyCerts,
      );

      let autoCertNo: string;
      let finalCertType: string;
      let action: 'REUSED' | 'GENERATED';
      let source: string;
      let previousCert: NewCertificate | undefined = undefined;

      // Decide action based on cert_type + existence
      if (dto.cert_type === 'INITIAL') {
        if (previous) {
          throw new BadRequestException(
            `Cannot issue as INITIAL — ${standard.name} already exists as ${previous.cert_no}. Use SURVEILLANCE instead.`,
          );
        }
        autoCertNo = await this.generateCertificateNo(
          dto.country ?? company.country,
          dto.city ?? company.city,
          standard,
        );
        finalCertType = 'INITIAL';
        action = 'GENERATED';
        source = 'new';
      } else if (
        dto.cert_type === 'SURVEILLANCE' ||
        dto.cert_type === 'RECERTIFICATION'
      ) {
        // ✅ NEW — pull manual cert no for this standard, if frontend sent one
        const manualCertNo =
          dto.manual_cert_numbers?.[standardId]?.trim();

        // ✅ UPDATED — block ONLY when there is no previous AND no manual override
        if (!previous && !manualCertNo) {
          throw new BadRequestException(
            `Cannot issue as ${dto.cert_type} — no previous cert for ${standard.name}. Use INITIAL instead, or provide a manual cert number.`,
          );
        }

        if (previous) {
          // ── existing path: legacy or new_certificate match found ──
          autoCertNo = previous.cert_no;
          source = previous.source;

          // Load previous cert entity (only if from new_certificate)
          if (
            previous.source === 'new_certificate' &&
            previous.existing_row_id
          ) {
            const found = await this.certRepo.findOne({
              where: { id: previous.existing_row_id },
            });
            if (found) {
              previousCert = found;
            }
          }
        } else {
          // ✅ NEW — no previous found, but user gave us the cert no manually
          // Guard against duplicates against the unique index on certificate_no
          const clash = await this.certRepo.findOne({
            where: { certificate_no: manualCertNo! },
          });
          if (clash) {
            throw new BadRequestException(
              `Manual cert no "${manualCertNo}" already exists in the system. Please verify or use a different number.`,
            );
          }
          autoCertNo = manualCertNo!;
          source = 'manual';
          console.log(
            `  ✏️  USING MANUAL cert no "${autoCertNo}" for ${standard.name}`,
          );
        }

        finalCertType = dto.cert_type;
        action = 'REUSED';
      } else {
        throw new BadRequestException(
          `Invalid cert_type. Must be INITIAL, SURVEILLANCE, or RECERTIFICATION.`,
        );
      }

      const qrToken = await this.generateUniqueToken();
      const fingerprint = await this.generateFingerprint();

      // Build new certificate instance — use `new NewCertificate()` then assign fields
      // This avoids the DeepPartial type issues with `create()`
      const certificate = new NewCertificate();
      certificate.company = company;
      certificate.standard = standard;
      if (previousCert) {
        certificate.previous_certificate = previousCert;
      }
      certificate.company_name_snapshot = company.name;
      certificate.address_snapshot = company.address;
      certificate.country = dto.country ?? String(company.country ?? '');
      certificate.city = dto.city ?? String(company.city ?? '');
      certificate.standard_name = standard.name;
      certificate.standard_short = standardShort;
      certificate.scope_of_work = dto.scope_of_work;
      certificate.certificate_no = autoCertNo;
      certificate.ea_codes = dto.ea_codes;
      certificate.cert_type = finalCertType;
      certificate.originally_registered = dto.originally_registered as Date;
      certificate.issue_date = dto.issue_date as Date;
      certificate.expire_date = dto.expire_date as Date;
      certificate.surveillance_audit_due = dto.surveillance_audit_due as Date;
      certificate.recertification_due = dto.recertification_due as Date;
      certificate.qrcode_token = qrToken;
      certificate.fingerprint = fingerprint;
      certificate.verification_domain = dto.verification_domain ?? 'local';
      certificate.status = 'pending_scan';

      const saved: NewCertificate = await this.certRepo.save(certificate);

      // Mark previous cert as superseded (only if from new_certificate)
      if (previousCert) {
        previousCert.status = 'superseded';
        await this.certRepo.save(previousCert);
      }

      await this.addVersionHistory(
        saved,
        finalCertType === 'INITIAL' ? 'issued' : 'renewed',
      );

      const qrCodeDataUrl = await this.generateQrCode(
        qrToken,
        fingerprint,
        saved.verification_domain as 'local' | 'international',
      );

      results.push({
        certificate: saved,
        qrCodeDataUrl,
        verification_url: this.buildVerificationUrl(saved),
        action,
        source,
      });
    }

    const summary = {
      total: results.length,
      reused: results.filter((r) => r.action === 'REUSED').length,
      generated: results.filter((r) => r.action === 'GENERATED').length,
    };

    return { certificates: results, summary };
  }
  /** UPDATE PARTIAL (PATCH) */
  async updatePartial(
    id: number,
    dto: Partial<UpdateCertificateDto>,
  ): Promise<NewCertificate> {
    const certificate = await this.certRepo.findOne({
      where: { id },
      relations: ['company', 'standard'],
    });

    if (!certificate) {
      throw new NotFoundException('Certificate not found');
    }

    // Handle standard change
    if (dto.standard_id) {
      const standard = await this.standardRepo.findOne({
        where: { id: dto.standard_id },
      });

      if (!standard) {
        throw new BadRequestException('Invalid standard_id');
      }

      certificate.standard = standard;
      certificate.standard_name = standard.name;
      certificate.standard_short = this.getStandardShort(standard);
    }

    // Handle address
    if (dto.address_snapshot) {
      certificate.address_snapshot = dto.address_snapshot;
    }

    // Apply remaining fields
    Object.assign(certificate, {
      city: dto.city ?? certificate.city,
      country: dto.country ?? certificate.country,
      scope_of_work: dto.scope_of_work ?? certificate.scope_of_work,
      ea_codes: dto.ea_codes ?? certificate.ea_codes,
      cert_type: dto.cert_type ?? certificate.cert_type, // ✅ NEW — allow cert type switch in edit mode
      originally_registered:
        dto.originally_registered ?? certificate.originally_registered,
      issue_date: dto.issue_date ?? certificate.issue_date,
      expire_date: dto.expire_date ?? certificate.expire_date,
      surveillance_audit_due:
        dto.surveillance_audit_due ?? certificate.surveillance_audit_due,
      recertification_due:
        dto.recertification_due ?? certificate.recertification_due,
      verification_domain:
        dto.verification_domain ?? certificate.verification_domain,
      status: dto.status ?? certificate.status,
    });

    const saved: NewCertificate = await this.certRepo.save(certificate);
    await this.addVersionHistory(saved, 'edited');

    return saved;
  }
  /** UPDATE CERTIFICATE WITH ALL FIELDS SUPPORTED */
  async update(id: number, dto: UpdateCertificateDto): Promise<NewCertificate> {
    const certificate = await this.certRepo.findOne({
      where: { id },
      relations: ['company', 'standard'],
    });
    if (!certificate) throw new NotFoundException('Certificate not found');

    // ✅ NEW — accept either standard_id (singular, legacy) OR standard_ids (plural, from new edit UI)
    // Each certificate row holds ONE standard, so we take the first item from the array.
    const incomingStandardId: number | undefined =
      dto.standard_id ??
      (Array.isArray((dto as any).standard_ids) &&
      (dto as any).standard_ids.length > 0
        ? Number((dto as any).standard_ids[0])
        : undefined);

    // ✅ NEW — Optional logging so you can see which path the update took
    if (incomingStandardId) {
      console.log(
        `📝 UPDATE cert #${id} — incoming standard id: ${incomingStandardId}` +
          ` (current: ${certificate.standard?.id ?? 'none'})`,
      );
    }

    // Update standard if provided
    if (incomingStandardId) {
      // ✅ NEW — guard: don't redo work if user picked the same standard
      if (certificate.standard?.id !== incomingStandardId) {
        const standard = await this.standardRepo.findOne({
          where: { id: incomingStandardId },
        });
        if (!standard) throw new NotFoundException('Standard not found');

        // ✅ NEW — block standard swap if it would clash with another active cert
        // (same company + new standard already certified).
        // This protects unique business logic: a company shouldn't have 2 active
        // certs for the same standard.
        const clash = await this.certRepo.findOne({
          where: {
            company: { id: certificate.company.id },
            standard: { id: standard.id },
            status: 'active',
          },
        });
        if (clash && clash.id !== certificate.id) {
          throw new BadRequestException(
            `Cannot swap to ${standard.name} — company already has an active certificate (${clash.certificate_no}) for this standard.`,
          );
        }

        certificate.standard = standard;
        certificate.standard_name = standard.name;
        certificate.standard_short = this.getStandardShort(standard);

        console.log(
          `  ✅ Standard swapped to ${standard.name} (${this.getStandardShort(standard)})`,
        );
      } else {
        console.log('  ⏭️  Standard unchanged, skipping standard update');
      }
    }

    // Update other simple fields
    if (dto.city) certificate.city = dto.city;
    if (dto.country) certificate.country = dto.country;
    if (dto.address_snapshot)
      certificate.address_snapshot = dto.address_snapshot;
    if (dto.company_name_snapshot)
      certificate.company_name_snapshot = dto.company_name_snapshot;
    if (dto.scope_of_work) certificate.scope_of_work = dto.scope_of_work;
    if (dto.ea_codes) certificate.ea_codes = dto.ea_codes;
    if (dto.cert_type) certificate.cert_type = dto.cert_type; // ✅ NEW — allow cert type switch in edit mode
    if (dto.originally_registered)
      certificate.originally_registered = dto.originally_registered;
    if (dto.issue_date) certificate.issue_date = dto.issue_date;
    if (dto.expire_date) certificate.expire_date = dto.expire_date;
    if (dto.surveillance_audit_due)
      certificate.surveillance_audit_due = dto.surveillance_audit_due;
    if (dto.recertification_due)
      certificate.recertification_due = dto.recertification_due;
    if (dto.verification_domain)
      certificate.verification_domain = dto.verification_domain;
    if (dto.status) certificate.status = dto.status;

    // Save updated certificate
    const saved: NewCertificate = await this.certRepo.save(certificate);

    // Add version history
    await this.addVersionHistory(saved, 'edited');

    return saved;
  }

  /** GET ALL CERTIFICATES with pagination and search */
  async findAll(
    page = 1,
    search?: string,
  ): Promise<{
    data: NewCertificate[];
    total: number;
    page: number;
    lastPage: number;
  }> {
    const take = 10;
    const skip = (page - 1) * take;

    const query = this.certRepo
      .createQueryBuilder('certificate')
      .leftJoinAndSelect('certificate.company', 'company')
      .leftJoinAndSelect('certificate.standard', 'standard')
      .orderBy('certificate.created_at', 'DESC');

    if (search && search.trim().length > 0) {
      const trimmedSearch = search.trim();
      query.andWhere(
        '(certificate.certificate_no = :exactSearch OR company.name LIKE :likeSearch)',
        { exactSearch: trimmedSearch, likeSearch: `%${trimmedSearch}%` },
      );
    }

    // Fetch data and total count
    const [data, total] = await query.skip(skip).take(take).getManyAndCount();

    const lastPage = Math.ceil(total / take);
    const augmentedData = data.map((cert) => ({
      ...cert,
      verification_url: this.buildVerificationUrl(cert),
    }));

    return { data: augmentedData, total, page, lastPage };
  }

  /** GET SINGLE CERTIFICATE */
  async findOne(id: number): Promise<any> {
    const certificate = await this.certRepo.findOne({
      where: { id },
      relations: ['company', 'standard'],
    });
    if (!certificate) throw new NotFoundException('Certificate not found');

    return {
      ...certificate,
      verification_url: this.buildVerificationUrl(certificate),
    };
  }

  /** DELETE CERTIFICATE */
  async remove(id: number): Promise<{ message: string }> {
    const certificate = await this.certRepo.findOne({ where: { id } });
    if (!certificate) throw new NotFoundException('Certificate not found');
    await this.certRepo.remove(certificate);
    return { message: 'Certificate deleted successfully' };
  }

  /** VERSION HISTORY */
  private async addVersionHistory(
    certificate: NewCertificate,
    reason: string = 'issued',
  ): Promise<void> {
    const version = this.versionRepo.create({
      certificate,
      certificate_no: certificate.certificate_no,
      standard_name: certificate.standard_name,
      standard_short: certificate.standard_short ?? '',
      company_name_snapshot: certificate.company_name_snapshot,
      address_snapshot: certificate.address_snapshot,
      city_snapshot: certificate.city,
      country_snapshot: certificate.country,
      ea_codes: certificate.ea_codes,
      cert_type: certificate.cert_type,
      scope_of_work: certificate.scope_of_work,
      originally_registered: certificate.originally_registered,
      issue_date: certificate.issue_date,
      expire_date: certificate.expire_date,
      surveillance_audit_due: certificate.surveillance_audit_due,
      recertification_due: certificate.recertification_due,
      status: certificate.status,
      scan_pdf_url: certificate.scan_pdf_url,
      qrcode_token: certificate.qrcode_token,
      change_reason: reason,
    });

    await this.versionRepo.save(version);
  }

  /** FIND CERTIFICATE BY certificate_no */
  async findByCertificateNo(certificateNo: string): Promise<NewCertificate> {
    const certificate = await this.certRepo.findOne({
      where: { certificate_no: certificateNo },
      relations: ['company', 'standard'],
    });

    if (!certificate) {
      throw new NotFoundException(
        `Certificate not found with number: ${certificateNo}`,
      );
    }

    return certificate;
  }

  /** VERIFY CERTIFICATE */
  async verifyCertificate(token: string, fingerprint?: string): Promise<any> {
    if (!token || token.trim().length === 0) {
      throw new BadRequestException('Token is required for verification');
    }

    // Find certificate by token
    const certificate = await this.certRepo
      .createQueryBuilder('certificate')
      .leftJoinAndSelect('certificate.company', 'company')
      .leftJoinAndSelect('certificate.standard', 'standard')
      .where('certificate.qrcode_token = :token', { token: token.trim() })
      .getOne();

    if (!certificate) {
      return { valid: false, message: 'Certificate not found' };
    }

    // Check fingerprint if provided
    if (fingerprint && certificate.fingerprint !== fingerprint) {
      return {
        valid: false,
        message: 'Certificate has been tampered with or is fake',
      };
    }

    // Don't expose pending_scan certs publicly
    if (certificate.status === 'pending_scan') {
      return {
        valid: false,
        message: 'Certificate pending verification — scan not yet uploaded',
      };
    }

    // Certificate is valid
    return {
      valid: true,
      status: certificate.status,
      certificate_no: certificate.certificate_no,
      cert_type: certificate.cert_type,
      issue_date: certificate.issue_date,
      expiry_date: certificate.expire_date,
      surveillance_audit_due: certificate.surveillance_audit_due,
      recertification_due: certificate.recertification_due,
      originally_registered: certificate.originally_registered,
      company: certificate.company,
      standard: certificate.standard,
      scope_of_work: certificate.scope_of_work,
      ea_codes: certificate.ea_codes,
      verification_domain: certificate.verification_domain,
      // The uploaded scan IS the digital certificate
      digital_certificate_url: certificate.scan_pdf_url,
    };
  }
  // ── Get certificate with all relations needed for draft/PDF/Word generation ──
  async getCertificateForDraft(id: number) {
    const cert = await this.certRepo
      .createQueryBuilder('cert')
      .leftJoinAndSelect('cert.company', 'company')
      .leftJoinAndSelect('cert.standard', 'standard')
      .where('cert.id = :id', { id })
      .getOne();

    if (!cert) return null;

    // ✅ FIX — Generate QR code FIRST, then embed the result
    const qrCodeDataUrl = await this.generateQrCode(
      cert.qrcode_token,
      cert.fingerprint,
      cert.verification_domain as 'local' | 'international',
    );

    // Map to interface expected by draft generator
    return {
      id: cert.id,
      cert_no: cert.certificate_no,
      certificate_no: cert.certificate_no,
      certificate_number: cert.certificate_no,
      scope_of_work: cert.scope_of_work,
      ea_codes: cert.ea_codes,
      originally_registered: cert.originally_registered,
      issue_date: cert.issue_date,
      expire_date: cert.expire_date,
      surveillance_audit_due: cert.surveillance_audit_due,
      recertification_due: cert.recertification_due,
      qrCode: qrCodeDataUrl,
      qr_code: qrCodeDataUrl,
      verification_url: this.buildVerificationUrl(cert),
      city: cert.city,
      country: cert.country,
      company: cert.company
        ? {
            name: cert.company.name,
            address: cert.address_snapshot ?? cert.company.address,
            accreditation: cert.ea_codes,
            city: cert.city,
            country: cert.country,
          }
        : undefined,
      standard: cert.standard
        ? {
            id: cert.standard.id,
            name: cert.standard.name,
            title: cert.standard.title,
          }
        : undefined,
    };
  }
  /** RESOLVE TOKEN — for the mobile app scanner (authenticated, returns any status) */
async resolveToken(token: string, fingerprint?: string) {
  if (!token?.trim()) {
    throw new BadRequestException('Token is required');
  }

  const cert = await this.certRepo
    .createQueryBuilder('certificate')
    .where('certificate.qrcode_token = :token', { token: token.trim() })
    .getOne();

  if (!cert) throw new NotFoundException('Certificate not found');
  if (fingerprint && cert.fingerprint !== fingerprint) {
    throw new NotFoundException('Certificate not found'); // don't reveal fp mismatch
  }

  return {
    id: cert.id,
    certificate_no: cert.certificate_no,
    company_name: cert.company_name_snapshot,
    standard: cert.standard_name,
    status: cert.status,
    expire_date: cert.expire_date,
    scan_pdf_url: cert.scan_pdf_url, // lets the app warn "copy already exists"
  };
}

/** ATTACH UPLOADED SCAN as the certificate's digital copy */
async attachDigitalCopy(id: number, fileUrl: string): Promise<NewCertificate> {
  const cert = await this.certRepo.findOne({ where: { id } });
  if (!cert) throw new NotFoundException('Certificate not found');

  cert.scan_pdf_url = fileUrl;
  (cert as any).scan_uploaded_at = new Date(); // remove `as any` if it's on the entity
  if (cert.status === 'pending_scan') cert.status = 'active';

  const saved = await this.certRepo.save(cert);
  await this.addVersionHistory(saved, 'scan_uploaded');
  return saved;
}
}