// src/certificates/bulk-scan/bulk-scan.service.ts
import { Injectable, Logger } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import * as fs from 'fs';
import * as path from 'path';
import { NewCertificate } from './entities/new-certificate.entity';
import { ExcelService } from '../excel/excel.service';

export interface BulkScanMatched {
  filename: string;
  certificate_no: string;
  certificate_id: number;
  scan_pdf_url: string;
}

export interface BulkScanReview {
  filename: string;
  certificate_no: string;
  reason: string;
}

export interface BulkScanUnmatched {
  filename: string;
  reason: string;
}

export interface BulkScanResult {
  matched: BulkScanMatched[];
  review: BulkScanReview[];
  unmatched: BulkScanUnmatched[];
  summary: {
    total: number;
    matched: number;
    review: number;
    unmatched: number;
  };
}

@Injectable()
export class BulkScanService {
  private readonly logger = new Logger(BulkScanService.name);

  // Where uploaded scans are stored (accessible via static file serving)
  private readonly UPLOAD_DIR = path.join(
    process.cwd(),
    'uploads',
    'certificate-scans',
  );

  // ✅ ADD — base URL for building absolute scan URLs
  private readonly BASE_URL: string;

  // ═══════════════════════════════════════════════════════════════════════
  // 🆕 PERMISSIVE CERT-NUMBER PATTERNS
  //
  // Ordered MOST SPECIFIC → LEAST SPECIFIC. First match wins.
  // The frontend's `inferCertNo` MUST mirror this list — if you change
  // anything here, change it there too. (Both are commented to point at
  // each other.)
  //
  // Covers every cert format the system can auto-generate:
  //   • UAE-AB-QMS-1C33R   — 4-seg, 2-char dept (most common)
  //   • UAE-A-EMS-MK347    — 4-seg, 1-char dept           ← was failing
  //   • UAE-22-001-12345   — 4-seg, numeric segments
  //   • PA-LA-QMS-8N4T7    — 4-seg, non-UAE prefix         ← was failing
  //   • KSA-RD-OHA-12345   — 4-seg, any 2-4 char country
  //   • UAE-AB-9878        — 3-seg manual short
  //   • ADU-AB-9878        — 3-seg, any country
  //   • AAU-10133          — legacy 2-seg
  //
  // Word boundaries (\b) prevent partial matches inside longer strings.
  // ═══════════════════════════════════════════════════════════════════════
 private static readonly CERT_NUMBER_PATTERNS: RegExp[] = [
  // ── 4-SEGMENT (most specific first) ────────────────────────
  /\bUAE-[A-Z]{2}-[A-Z]{3,5}-[A-Z0-9]{4,6}(?:\.\d+)?\b/i,
  /\bUAE-[A-Z0-9]{1,5}-[A-Z0-9]{1,5}-[A-Z0-9]{3,8}(?:\.\d+)?\b/i,
  /\b[A-Z]{2,4}-[A-Z0-9]{1,5}-[A-Z0-9]{1,5}-[A-Z0-9]{3,8}(?:\.\d+)?\b/i,
  // ── 3-SEGMENT ───────────────────────────────────────────────
  /\b[A-Z]{2,4}-[A-Z0-9]{1,5}-[A-Z0-9]{3,8}(?:\.\d+)?\b/i,
  // ── LEGACY 2-SEGMENT ────────────────────────────────────────
  /\b[A-Z]{3,4}-\d{4,6}(?:\.\d+)?\b/i,
];

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

    private readonly excelService: ExcelService,
  ) {
    // Build base URL from env (supports dev + production seamlessly)
    const raw =
      process.env.API_BASE_URL || // ✅ reads this first
      process.env.NEXT_PUBLIC_API_URL ||
      `http://localhost:${process.env.PORT || 3007}`;
    this.BASE_URL = raw.replace(/\/api\/?$/, '').replace(/\/$/, '');

    this.logger.log(`🌐 Scan base URL: ${this.BASE_URL}`);

    // Ensure upload directory exists
    if (!fs.existsSync(this.UPLOAD_DIR)) {
      fs.mkdirSync(this.UPLOAD_DIR, { recursive: true });
      this.logger.log(`📂 Created upload directory: ${this.UPLOAD_DIR}`);
    }
  }

  /**
   * Extract cert number from filename.
   *
   * Handles every auto-generated format the system produces, plus common
   * filename chrome (numbered duplicates, "copy" suffixes, etc).
   *
   * Returns the matched cert number in UPPERCASE, or null if none found.
   */
  private extractCertNo(filename: string): string | null {
    // Normalize: strip extension, common Windows/macOS dup suffixes, then trim
    const name = filename
      .replace(/\.(pdf|PDF)$/i, '')
      .replace(/\s*\(\d+\)\s*$/, '')   // " (1)", " (2)"  ← Windows dup
      .replace(/\s*-\s*copy\s*$/i, '') // " - Copy"       ← Windows variant
      .replace(/\s*copy\s*$/i, '')     // " copy"         ← macOS dup
      .replace(/\s*-\s*/g, '-')        // 🆕 "AQS- 30012" → "AQS-30012"
      .trim();

    for (let i = 0; i < BulkScanService.CERT_NUMBER_PATTERNS.length; i++) {
      const pattern = BulkScanService.CERT_NUMBER_PATTERNS[i];
      const match = name.match(pattern);
      if (match) {
        const result = match[0].toUpperCase();
        this.logger.debug(
          `🔍 Extracted "${result}" from "${filename}" via pattern #${i + 1}`,
        );
        return result;
      }
    }

    this.logger.debug(`🔍 No cert pattern matched: "${filename}"`);
    return null;
  }

  /**
   * Process a batch of uploaded PDF files.
   */
  async processBulkUpload(
    files: Express.Multer.File[],
    userId?: number,
  ): Promise<BulkScanResult> {
    const matched: BulkScanMatched[] = [];
    const review: BulkScanReview[] = [];
    const unmatched: BulkScanUnmatched[] = [];

    this.logger.log(`📤 Processing ${files.length} uploaded file(s)`);

    for (const file of files) {
      try {
        await this.processSingleFile(file, userId, matched, review, unmatched);
      } catch (err: any) {
        this.logger.error(
          `❌ Failed to process ${file.originalname}: ${err.message}`,
        );
        unmatched.push({
          filename: file.originalname,
          reason: `Processing error: ${err.message}`,
        });
      }
    }

    const summary = {
      total: files.length,
      matched: matched.length,
      review: review.length,
      unmatched: unmatched.length,
    };

    this.logger.log(
      `✅ Bulk scan complete: ${summary.matched} matched, ${summary.review} review, ${summary.unmatched} unmatched`,
    );

    return { matched, review, unmatched, summary };
  }

  private async processSingleFile(
    file: Express.Multer.File,
    userId: number | undefined,
    matched: BulkScanMatched[],
    review: BulkScanReview[],
    unmatched: BulkScanUnmatched[],
  ): Promise<void> {
    const filename = file.originalname;

    // Step 1: Extract cert number from filename
    const certNo = this.extractCertNo(filename);
    if (!certNo) {
      unmatched.push({
        filename,
        reason:
          'No cert number pattern detected in filename ' +
          '(expected formats: UAE-XX-XXX-XXXXX, UAE-X-XXX-XXXXX, ' +
          'XX-XX-XXX-XXXXX, or XXX-NNNNN)',
      });
      return;
    }

    // Step 2: Find certificate by cert_no — exact match first
    let certs = await this.certRepo
      .createQueryBuilder('cert')
      .where('cert.certificate_no = :certNo', { certNo })
      .orderBy('cert.created_at', 'DESC')
      .getMany();

    // ✅ Step 2b: Case-insensitive fallback (handles DB collation differences)
    if (certs.length === 0) {
      certs = await this.certRepo
        .createQueryBuilder('cert')
        .where('UPPER(cert.certificate_no) = :certNo', { certNo })
        .orderBy('cert.created_at', 'DESC')
        .getMany();

      if (certs.length > 0) {
        this.logger.warn(
          `⚠️ Cert "${certNo}" matched only after UPPER() fallback — DB has different casing`,
        );
      }
    }

    // 🆕 Step 2c: Space-tolerant fallback (handles dirty DB data like "AQS- 10022")
    if (certs.length === 0) {
      certs = await this.certRepo
        .createQueryBuilder('cert')
        .where("UPPER(REPLACE(cert.certificate_no, ' ', '')) = :certNo", {
          certNo,
        })
        .orderBy('cert.created_at', 'DESC')
        .getMany();

      if (certs.length > 0) {
        this.logger.warn(
          `⚠️ Cert "${certNo}" matched only after removing spaces — DB value has spaces, please clean it`,
        );
      }
    }

    // ✅ Step 2d: Still nothing — try to suggest similar cert numbers
    if (certs.length === 0) {
      const suggestions = await this.findSimilarCerts(certNo);
      const tail =
        suggestions.length > 0
          ? ` Similar certs: ${suggestions.join(', ')}`
          : '';

      unmatched.push({
        filename,
        reason: `Certificate "${certNo}" not found in database.${tail}`,
      });
      return;
    }

    // Prefer pending_scan → active → others
    const pendingScan = certs.find((c) => c.status === 'pending_scan');
    const active = certs.find((c) => c.status === 'active');
    const targetCert = pendingScan || active || certs[0];

    if (!targetCert) {
      unmatched.push({
        filename,
        reason: `Certificate "${certNo}" found but status is unexpected`,
      });
      return;
    }

    // Step 3: Save PDF to disk
    const safeFilename = `${certNo}_${Date.now()}.pdf`;
    const filePath = path.join(this.UPLOAD_DIR, safeFilename);

    try {
      fs.writeFileSync(filePath, file.buffer);
    } catch (err: any) {
      unmatched.push({
        filename,
        reason: `Failed to save file: ${err.message}`,
      });
      return;
    }

    // Build ABSOLUTE URL (works when frontend + backend on different ports)
    const scanUrl = `${this.BASE_URL}/uploads/certificate-scans/${safeFilename}`;

    // Step 4: Update certificate — flip pending_scan → active
    targetCert.scan_pdf_url = scanUrl;
    targetCert.scan_uploaded_at = new Date();
    if (userId) targetCert.scan_uploaded_by = userId;

    if (targetCert.status === 'pending_scan') {
      targetCert.status = 'active';
    }

    await this.certRepo.save(targetCert);

    // Step 5: Report based on status change
    if (pendingScan) {
      matched.push({
        filename,
        certificate_no: certNo,
        certificate_id: targetCert.id,
        scan_pdf_url: scanUrl,
      });
    } else {
      review.push({
        filename,
        certificate_no: certNo,
        reason: `Certificate was already ${targetCert.status} — scan updated, please review`,
      });
    }
  }

  /**
   * 🆕 Best-effort lookup for cert numbers similar to the one we couldn't find.
   *
   * Strategy: drop the last segment of the cert number and search by prefix.
   * For "UAE-AJ-QMS-E0745" we'd search "UAE-AJ-QMS-%" and return up to 3.
   *
   * This catches common upload mistakes:
   *   - Typo in last segment: "UAE-AJ-QMS-E0745" vs "UAE-AJ-QMS-E0746"
   *   - Wrong revision: cert was reissued with new last segment
   *   - Filename mangled by user
   *
   * Always returns [] on any error — never throws (best-effort only).
   */
  private async findSimilarCerts(certNo: string): Promise<string[]> {
    try {
      const segments = certNo.split('-');
      if (segments.length < 2) return [];

      const prefix = segments.slice(0, -1).join('-') + '-';
      // Need at least one full segment of context to avoid spamming "AAU-%"
      // matching every cert
      if (prefix.length < 4) return [];

      const similar = await this.certRepo
        .createQueryBuilder('cert')
        .where('cert.certificate_no LIKE :pattern', { pattern: `${prefix}%` })
        .orderBy('cert.created_at', 'DESC')
        .limit(3)
        .getMany();

      return similar
        .map((c) => c.certificate_no)
        .filter((s): s is string => Boolean(s));
    } catch (err: any) {
      this.logger.warn(
        `findSimilarCerts failed for "${certNo}": ${err?.message ?? err}`,
      );
      return [];
    }
  }
}