import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { NewCertificate } from '../certificates/entities/new-certificate.entity';
import { Certificate } from '../excel/certificate.entity'; // legacy entity

export const SOURCE_MANUAL = 'Manual';
export const SOURCE_NEW = 'QRS & TQS';
export type ReportSource = typeof SOURCE_MANUAL | typeof SOURCE_NEW;

export interface AgingRow {
  source: ReportSource;
  certificate_no: string;
  company_name: string;
  standard: string;
  cert_type: string | null;
  category: string;
  status: string | null;
  originally_registered: string | null;
  issue_date: string | null;
  expire_date: string | null;
  issue_year: number | null;
  age_days: number | null;
  age_years: number | null;
  days_to_expiry: number | null; // negative = expired
  aging_bucket: string;
  expiry_status: 'expired' | 'expiring_soon' | 'valid' | 'unknown';
}

export interface AgingSummary {
  total: number;
  bySource: Record<string, number>;
  byBucket: Record<string, number>;
  byCategory: Record<string, number>;
  byStandard: Record<string, number>;
  byIssueYear: Record<string, number>;
  expired: number;
  expiringWithin90: number;
  undatedSkipped: number;
}

export interface AgingReport {
  meta: {
    from_year: number;
    to_year: number;
    from_date?: string;
    to_date?: string;
    period_label: string;
    category?: string;
    source_filter: ReportSource | 'All';
    as_of: string;
    generated_at: string;
    buckets: string[];
  };
  summary: AgingSummary;
  rows: AgingRow[];
}

export interface AgingReportOptions {
  fromYear?: number;
  toYear?: number;
  fromDate?: Date; // exact date range (overrides year filter when both set)
  toDate?: Date;
  basis?: 'issue' | 'expire'; // which date the range filters on (default issue)
  mode?: 'range' | 'cycle'; // 'cycle' = ISO lifecycle by original-registration anniversary
  year?: number; // cycle mode: target year (e.g. 2026)
  months?: number[]; // cycle mode: target months 1-12 (e.g. [7, 8])
  asOf?: Date;
  standard?: string;
  category?: string; // filter by normalized category (Re-certification, Surveillance, Initial)
  source?: ReportSource; // undefined = All
  includeUndated?: boolean;
  dedupeByCertNo?: boolean; // prefer new over manual
}

@Injectable()
export class AgingAnalysisService {
  private readonly DAY = 86_400_000;
  private readonly BUCKETS = [
    'Expired',
    '0-30 days',
    '31-90 days',
    '91-180 days',
    '180+ days',
    'No Expiry Date',
  ];

  /**
   * ISO 3-year cycle: years elapsed since ORIGINAL registration → audit category.
   * Per spec (target year 2026):
   *   reg 2023 → 3 yrs elapsed → Re-certification
   *   reg 2024 → 2 yrs elapsed → Surveillance (1st)
   *   reg 2025 → 1 yr  elapsed → Surveillance (2nd)
   * NOTE: standard ISO usually labels the 1-yr-elapsed audit as "1st". If you want
   * that convention, swap the labels on keys 1 and 2 below.
   */
  private readonly CYCLE_MAP: Record<number, string> = {
    3: 'Re-certification',
    2: 'Surveillance (1st)',
    1: 'Surveillance (2nd)',
  };

  constructor(
    @InjectRepository(NewCertificate, 'scheme_dbs')
    private readonly certRepo: Repository<NewCertificate>,
    @InjectRepository(Certificate, 'certification_db')
    private readonly legacyRepo: Repository<Certificate>,
  ) {}

  async generate(options: AgingReportOptions = {}): Promise<AgingReport> {
    if ((options.mode ?? 'range') === 'cycle') {
      return this.generateCycle(options);
    }
    const fromYear = options.fromYear ?? 2023;
    const toYear = options.toYear ?? 2026;
    const asOf = options.asOf ?? new Date();
    const includeUndated = options.includeUndated ?? false;
    const source = options.source; // undefined = both

    // If an explicit date range is supplied, it overrides the year filter
    const useDateRange = !!(options.fromDate && options.toDate);
    const basis = options.basis ?? 'issue';
    const dateCol = basis === 'expire' ? 'expire_date' : 'issue_date';

    let undatedSkipped = 0;
    const rows: AgingRow[] = [];

    // ── QRS & TQS (new / scheme_dbs ) ──
    if (source !== SOURCE_MANUAL) {
      const nqb = this.certRepo
        .createQueryBuilder('c')
        .leftJoinAndSelect('c.company', 'company')
        .leftJoinAndSelect('c.standard', 'standard');

      if (useDateRange) {
        nqb.where(`c.${dateCol} BETWEEN :fromDate AND :toDate`, {
          fromDate: options.fromDate,
          toDate: options.toDate,
        });
      } else {
        nqb.where(`YEAR(c.${dateCol}) BETWEEN :from AND :to`, {
          from: fromYear,
          to: toYear,
        });
      }

      const newCerts = await nqb.orderBy(`c.${dateCol}`, 'DESC').getMany();

      for (const c of newCerts) {
        rows.push(
          this.buildRow({
            source: this.resolveNewSource(c),
            certificate_no: c.certificate_no,
            company_name: c.company_name_snapshot || c.company?.name || '—',
            standard:
              c.standard_short || c.standard_name || c.standard?.name || '—',
            cert_type: c.cert_type ?? null,
            status: c.status ?? null,
            origReg: this.toDate(c.originally_registered),
            issue: this.toDate(c.issue_date),
            expire: this.toDate(c.expire_date),
            asOf,
          }),
        );
      }
    }

    // ── Manual (legacy / certification_db) ──
    if (source !== SOURCE_NEW) {
      const qb = this.legacyRepo.createQueryBuilder('cert');

      if (useDateRange) {
        qb.where(`cert.${dateCol} BETWEEN :fromDate AND :toDate`, {
          fromDate: options.fromDate,
          toDate: options.toDate,
        });
      } else {
        qb.where(`YEAR(cert.${dateCol}) BETWEEN :from AND :to`, {
          from: fromYear,
          to: toYear,
        });
      }
      if (includeUndated) qb.orWhere(`cert.${dateCol} IS NULL`);

      const legacy = await qb.orderBy(`cert.${dateCol}`, 'DESC').getMany();

      for (const l of legacy) {
        const issue = this.toDate(l.issue_date);
        const expire = this.toDate(l.expire_date);
        // skip rows missing the field we're filtering on
        const basisValue = basis === 'expire' ? expire : issue;
        if (!basisValue && !includeUndated) {
          undatedSkipped++;
          continue;
        }
        // one legacy row can pack several cert nos + standards → expand
        for (const pair of this.expandLegacy(l.cert_no, l.standard)) {
          rows.push(
            this.buildRow({
              source: SOURCE_MANUAL,
              certificate_no: pair.cert_no,
              company_name: l.company_name || '—',
              standard: pair.standard,
              cert_type: null,
              status: l.status ?? null,
              origReg: this.toDate((l as any).orginally_reg),
              issue,
              expire,
              asOf,
            }),
          );
        }
      }
    }

    let out = options.standard
      ? rows.filter((r) =>
          r.standard.toLowerCase().includes(options.standard!.toLowerCase()),
        )
      : rows;

    // filter by normalized category (e.g. Re-certification)
    if (options.category?.trim()) {
      const want = options.category.trim().toLowerCase();
      out = out.filter((r) => r.category.toLowerCase() === want);
    }

    if (options.dedupeByCertNo) out = this.dedupe(out);

    const periodLabel = this.buildPeriodLabel(
      options.fromDate,
      options.toDate,
      fromYear,
      toYear,
    );

    return {
      meta: {
        from_year: fromYear,
        to_year: toYear,
        ...(useDateRange
          ? {
              from_date: this.iso(options.fromDate!),
              to_date: this.iso(options.toDate!),
            }
          : {}),
        period_label: periodLabel,
        ...(options.category?.trim()
          ? { category: options.category.trim() }
          : {}),
        source_filter: source ?? 'All',
        as_of: this.iso(asOf),
        generated_at: new Date().toISOString(),
        buckets: this.BUCKETS,
      },
      summary: this.summarize(out, undatedSkipped),
      rows: out,
    };
  }

  /**
   * CYCLE MODE — select certs by ISO lifecycle anniversary of original registration.
   * A cert registered in month M of year (targetYear - N) is due in month M of
   * targetYear, with category from CYCLE_MAP[N]. Keyed on ORIGINAL registration
   * month/year (robust against messy legacy expire dates).
   */
  private async generateCycle(
    options: AgingReportOptions,
  ): Promise<AgingReport> {
    const asOf = options.asOf ?? new Date();
    const source = options.source;
    const year = options.year ?? new Date().getFullYear();
    const months =
      options.months && options.months.length
        ? [...new Set(options.months)].filter((m) => m >= 1 && m <= 12)
        : [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12];
    const catFilter = this.parseCycleCategory(options.category);

    const y1 = year - 1; // most recent registration year of interest
    const y3 = year - 3; // oldest (re-certification)

    let undatedSkipped = 0;
    const rows: AgingRow[] = [];

    // ── QRS & TQS (new / scheme_dbs ) ──
    if (source !== SOURCE_MANUAL) {
      const newCerts = await this.certRepo
        .createQueryBuilder('c')
        .leftJoinAndSelect('c.company', 'company')
        .leftJoinAndSelect('c.standard', 'standard')
        .where('YEAR(c.originally_registered) BETWEEN :y3 AND :y1', { y3, y1 })
        .andWhere('MONTH(c.originally_registered) IN (:...months)', { months })
        .orderBy('c.originally_registered', 'ASC')
        .getMany();

      for (const c of newCerts) {
        const origReg = this.toDate(c.originally_registered);
        if (!origReg) {
          undatedSkipped++;
          continue;
        }
        const category = this.CYCLE_MAP[year - origReg.getFullYear()];
        if (!category) continue;
        if (catFilter && !catFilter.has(category)) continue;

        const row = this.buildRow({
          source: this.resolveNewSource(c),
          certificate_no: c.certificate_no,
          company_name: c.company_name_snapshot || c.company?.name || '—',
          standard:
            c.standard_short || c.standard_name || c.standard?.name || '—',
          cert_type: c.cert_type ?? null,
          status: c.status ?? null,
          origReg,
          issue: this.toDate(c.issue_date),
          expire: this.toDate(c.expire_date),
          asOf,
        });
        row.category = category; // cycle category overrides cert_type-derived one
        rows.push(row);
      }
    }

    // ── Manual (legacy / certification_db) ──
    if (source !== SOURCE_NEW) {
      const legacy = await this.legacyRepo
        .createQueryBuilder('cert')
        .where('YEAR(cert.orginally_reg) BETWEEN :y3 AND :y1', { y3, y1 })
        .andWhere('MONTH(cert.orginally_reg) IN (:...months)', { months })
        .orderBy('cert.orginally_reg', 'ASC')
        .getMany();

      for (const l of legacy) {
        const origReg = this.toDate((l as any).orginally_reg);
        if (!origReg) {
          undatedSkipped++;
          continue;
        }
        const category = this.CYCLE_MAP[year - origReg.getFullYear()];
        if (!category) continue;
        if (catFilter && !catFilter.has(category)) continue;

        for (const pair of this.expandLegacy(l.cert_no, l.standard)) {
          const row = this.buildRow({
            source: SOURCE_MANUAL,
            certificate_no: pair.cert_no,
            company_name: l.company_name || '—',
            standard: pair.standard,
            cert_type: null,
            status: l.status ?? null,
            origReg,
            issue: this.toDate(l.issue_date),
            expire: this.toDate(l.expire_date),
            asOf,
          });
          row.category = category;
          rows.push(row);
        }
      }
    }

    let out = options.standard
      ? rows.filter((r) =>
          r.standard.toLowerCase().includes(options.standard!.toLowerCase()),
        )
      : rows;
    if (options.dedupeByCertNo) out = this.dedupe(out);

    const periodLabel = `${this.monthsLabel(months, year)}`;
    const catLabel = this.cycleCategoryLabel(options.category);

    return {
      meta: {
        from_year: y3,
        to_year: year,
        period_label: periodLabel,
        ...(catLabel ? { category: catLabel } : {}),
        source_filter: source ?? 'All',
        as_of: this.iso(asOf),
        generated_at: new Date().toISOString(),
        buckets: this.BUCKETS,
      },
      summary: this.summarize(out, undatedSkipped),
      rows: out,
    };
  }

  /** Parse a cycle category filter into the set of matching category labels */
  private parseCycleCategory(s?: string): Set<string> | null {
    if (!s?.trim()) return null; // all categories
    const v = s.trim().toLowerCase();
    if (['recert', 'recertification', 're-certification'].includes(v)) {
      return new Set(['Re-certification']);
    }
    if (['surveillance', 'surv'].includes(v)) {
      return new Set(['Surveillance (1st)', 'Surveillance (2nd)']);
    }
    if (['surveillance1', 'surveillance-1', 'surv1', 's1', '1st'].includes(v)) {
      return new Set(['Surveillance (1st)']);
    }
    if (['surveillance2', 'surveillance-2', 'surv2', 's2', '2nd'].includes(v)) {
      return new Set(['Surveillance (2nd)']);
    }
    return null;
  }

  /** Display label for the report title in cycle mode */
  private cycleCategoryLabel(s?: string): string | undefined {
    const set = this.parseCycleCategory(s);
    if (!set) return undefined;
    if (set.size === 2) return 'Surveillance';
    return [...set][0];
  }

  /** "August 2026" | "July–August 2026" | "Jul, Sep 2026" */
  private monthsLabel(months: number[], year: number): string {
    const M = [
      'January', 'February', 'March', 'April', 'May', 'June',
      'July', 'August', 'September', 'October', 'November', 'December',
    ];
    const s = [...new Set(months)].sort((a, b) => a - b);
    if (s.length === 0) return `${year}`;
    if (s.length === 1) return `${M[s[0] - 1]} ${year}`;
    const contiguous = s.every((m, i) => i === 0 || m === s[i - 1] + 1);
    if (contiguous) return `${M[s[0] - 1]}–${M[s[s.length - 1] - 1]} ${year}`;
    return `${s.map((m) => M[m - 1].slice(0, 3)).join(', ')} ${year}`;
  }

  /**
   * Decides the source label for a NEW certificate.
   * Currently every new cert is 'QRS & TQS'. If you later add a brand/registrar
   * field on NewCertificate, split here, e.g.:
   *   return cert.registrar === 'TQS' ? 'TQS' : 'QRS';
   */
  private resolveNewSource(_cert: NewCertificate): ReportSource {
    return SOURCE_NEW;
  }

  /**
   * Normalize a stored cert_type into a display Category.
   * Legacy rows have no cert_type → defaulted to 'Re-certification'
   * (existing certs entering a renewal cycle). Change the default here if needed.
   */
  private normalizeCategory(certType: string | null): string {
    if (!certType) return 'Re-certification';
    const v = certType.toUpperCase();
    if (v.includes('RECERT')) return 'Re-certification';
    if (v.includes('SURV')) return 'Surveillance';
    if (v.includes('INIT')) return 'Initial';
    return certType;
  }

  /**
   * Normalize standard labels so numeric ISO codes and short codes merge:
   *   9001 / ISO 9001  → QMS
   *   14001 / ISO 14001 → EMS
   *   45001 / OH&S / OHS → OHA
   *   22000 → HACCP
   * Anything else (e.g. ISO 37001:2016, ISO 50001) is kept as-is.
   */
  private normalizeStandard(raw: string): string {
    if (!raw || !raw.trim()) return '—';
    const s = raw.trim();
    const u = s.toUpperCase();
    const has = (n: string) => new RegExp(`\\b${n}\\b`).test(u);
    if (u === 'QMS' || has('9001')) return 'QMS';
    if (u === 'EMS' || has('14001')) return 'EMS';
    if (u === 'OHA' || u === 'OHS' || u === 'OH&S' || has('45001')) return 'OHA';
    if (u === 'HACCP' || has('22000')) return 'HACCP';
    return s;
  }

  /** Human-friendly period label for report titles */
  private buildPeriodLabel(
    fromDate: Date | undefined,
    toDate: Date | undefined,
    fromYear: number,
    toYear: number,
  ): string {
    const MONTHS = [
      'January', 'February', 'March', 'April', 'May', 'June',
      'July', 'August', 'September', 'October', 'November', 'December',
    ];
    if (fromDate && toDate) {
      const sameYear = fromDate.getFullYear() === toDate.getFullYear();
      const sameMonth = sameYear && fromDate.getMonth() === toDate.getMonth();
      if (sameMonth) {
        return `${MONTHS[fromDate.getMonth()]} ${fromDate.getFullYear()}`;
      }
      if (sameYear) {
        return `${MONTHS[fromDate.getMonth()]}–${MONTHS[toDate.getMonth()]} ${fromDate.getFullYear()}`;
      }
      const fmt = (d: Date) =>
        `${String(d.getDate()).padStart(2, '0')} ${MONTHS[d.getMonth()].slice(0, 3)} ${d.getFullYear()}`;
      return `${fmt(fromDate)} – ${fmt(toDate)}`;
    }
    return fromYear === toYear ? `${fromYear}` : `${fromYear}–${toYear}`;
  }

  // ── row + aging math ──
  private buildRow(input: {
    source: ReportSource;
    certificate_no: string;
    company_name: string;
    standard: string;
    cert_type: string | null;
    status: string | null;
    origReg: Date | null;
    issue: Date | null;
    expire: Date | null;
    asOf: Date;
  }): AgingRow {
    const { issue, expire, asOf } = input;
    const age_days = issue
      ? Math.floor((asOf.getTime() - issue.getTime()) / this.DAY)
      : null;
    const days_to_expiry = expire
      ? Math.floor((expire.getTime() - asOf.getTime()) / this.DAY)
      : null;

    return {
      source: input.source,
      certificate_no: input.certificate_no,
      company_name: input.company_name,
      standard: this.normalizeStandard(input.standard),
      cert_type: input.cert_type,
      category: this.normalizeCategory(input.cert_type),
      status: input.status,
      originally_registered: input.origReg ? this.iso(input.origReg) : null,
      issue_date: issue ? this.iso(issue) : null,
      expire_date: expire ? this.iso(expire) : null,
      issue_year: issue ? issue.getFullYear() : null,
      age_days,
      age_years: age_days !== null ? +(age_days / 365).toFixed(2) : null,
      days_to_expiry,
      aging_bucket: this.bucket(days_to_expiry),
      expiry_status: this.expiryStatus(days_to_expiry),
    };
  }

  private bucket(d: number | null): string {
    if (d === null) return 'No Expiry Date';
    if (d < 0) return 'Expired';
    if (d <= 30) return '0-30 days';
    if (d <= 90) return '31-90 days';
    if (d <= 180) return '91-180 days';
    return '180+ days';
  }

  private expiryStatus(d: number | null): AgingRow['expiry_status'] {
    if (d === null) return 'unknown';
    if (d < 0) return 'expired';
    if (d <= 90) return 'expiring_soon';
    return 'valid';
  }

  // ── legacy expansion (mirrors CertificatesService logic) ──
  private expandLegacy(certNo: string, label: string) {
    const numbers = this.parseLegacyCertNo(certNo || '');
    const standards = this.expandLegacyStandard(label || '');
    const count = Math.max(numbers.length, standards.length, 1);
    const out: Array<{ cert_no: string; standard: string }> = [];
    for (let i = 0; i < count; i++) {
      out.push({
        cert_no: numbers[i] ?? numbers[0] ?? certNo ?? '—',
        standard: standards[i] ?? standards[0] ?? label ?? '—',
      });
    }
    return out;
  }

  private parseLegacyCertNo(certNo: string): string[] {
    if (!certNo) return [];
    const parts = certNo.split(/[,\s]+/).map((s) => s.trim()).filter(Boolean);
    let pfx = '';
    return parts.map((p) => {
      if (p.includes('-')) {
        pfx = p.split('-')[0];
        return p;
      }
      return pfx ? `${pfx}-${p}` : p;
    });
  }

  private expandLegacyStandard(label: string): string[] {
    if (!label) return [];
    const parts = label.split(',').map((s) => s.trim().toUpperCase()).filter(Boolean);
    const out: string[] = [];
    for (const p of parts) {
      if (p === 'IMS') out.push('QMS', 'EMS', 'OHA');
      else if (p === 'ISO 9001') out.push('QMS');
      else if (p === 'ISO 14001') out.push('EMS');
      else if (p === 'ISO 45001') out.push('OHA');
      else if (p === 'ISO 22000') out.push('HACCP');
      else out.push(p);
    }
    return out;
  }

  private dedupe(rows: AgingRow[]): AgingRow[] {
    const map = new Map<string, AgingRow>();
    for (const r of rows) {
      const key = r.certificate_no.trim().toUpperCase();
      const ex = map.get(key);
      if (!ex || (ex.source === SOURCE_MANUAL && r.source !== SOURCE_MANUAL)) {
        map.set(key, r);
      }
    }
    return [...map.values()];
  }

  private summarize(rows: AgingRow[], undatedSkipped: number): AgingSummary {
    const inc = (o: Record<string, number>, k: string) => (o[k] = (o[k] ?? 0) + 1);
    const bySource: Record<string, number> = {};
    const byBucket: Record<string, number> = {};
    const byCategory: Record<string, number> = {};
    const byStandard: Record<string, number> = {};
    const byIssueYear: Record<string, number> = {};
    let expired = 0;
    let expiringWithin90 = 0;

    for (const r of rows) {
      inc(bySource, r.source);
      inc(byBucket, r.aging_bucket);
      inc(byCategory, r.category);
      inc(byStandard, r.standard || '—');
      inc(byIssueYear, r.issue_year ? String(r.issue_year) : 'Unknown');
      if (r.expiry_status === 'expired') expired++;
      if (r.expiry_status === 'expiring_soon') expiringWithin90++;
    }
    return {
      total: rows.length,
      bySource,
      byBucket,
      byCategory,
      byStandard,
      byIssueYear,
      expired,
      expiringWithin90,
      undatedSkipped,
    };
  }

  private toDate(v: any): Date | null {
    if (!v) return null;
    const d = v instanceof Date ? v : new Date(v);
    return isNaN(d.getTime()) ? null : d;
  }
  private iso(d: Date): string {
    return d.toISOString().slice(0, 10);
  }
}