import { Injectable, Logger } from '@nestjs/common';
import { InjectDataSource } from '@nestjs/typeorm';
import { DataSource } from 'typeorm';

export interface CertRow {
  cert_no: string;
  company_name: string;
  standard: string;
  orginally_reg: string | null;
  issue_date: string | null;
  expire_date: string | null;
  status: string;
  certType?: string;
}

export interface Bucket {
  key: 'recert' | 'surv_11' | 'surv_1';
  label: string;
  rows: CertRow[];
  count: number;
}

export interface ReportData {
  year: number;
  month: number;
  monthName: string;
  buckets: Bucket[];
  total: number;
}

export type ReportType = 'all' | 'recert' | 'surv_1' | 'surv_11';

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

  constructor(
    @InjectDataSource('certification_db')
    private readonly ds: DataSource,
  ) { }

  /** Kept for reference / fallback — not used by the year+month match below. */
  private monthWindow(year: number, month: number) {
    const pad = (n: number) => String(n).padStart(2, '0');
    const start = `${year}-${pad(month)}-01`;
    const ny = month === 12 ? year + 1 : year;
    const nm = month === 12 ? 1 : month + 1;
    const endExclusive = `${ny}-${pad(nm)}-01`;
    return { start, endExclusive };
  }

  /**
   * Matches strictly on the YEAR and MONTH of the date column.
   *
   * Why not a string range anymore: the date columns are stored as text, so a
   * comparison like `col >= '2024-08-01'` sorts them alphabetically and drags
   * in unrelated years (2010, 2016, …). YEAR()/MONTH() compare the real date
   * parts, so only the exact month/year is returned.
   */
  private async fetchRows(
    column: 'expire_date' | 'orginally_reg',
    year: number,
    month: number,
  ): Promise<CertRow[]> {
    return this.ds.query(
      `SELECT cert_no, company_name, standard, orginally_reg, issue_date, expire_date, status
         FROM certificates
        WHERE YEAR(${column}) = ? AND MONTH(${column}) = ?
        ORDER BY ${column} ASC`,
      [year, month],
    );
  }

  async buildReport(
    year: number,
    month: number,
    type: ReportType = 'all',
  ): Promise<ReportData> {
    const recertYear = year - 3;
    const surv1Year = year - 2;
    const surv2Year = year - 1;

    const [recert, surv1, surv2] = await Promise.all([
      this.fetchRows('orginally_reg', recertYear, month),
      this.fetchRows('orginally_reg', surv1Year, month),
      this.fetchRows('orginally_reg', surv2Year, month),
    ]);

    const stamp = (rows: CertRow[], certType: string): CertRow[] =>
      rows.map((r) => ({ ...r, certType }));

    let buckets: Bucket[] = [
      { key: 'recert', label: 'Re-certification', rows: stamp(recert, 'Re-certification'), count: recert.length },
      { key: 'surv_1', label: '1st Surveillance', rows: stamp(surv1, '1st Surveillance'), count: surv1.length },
      { key: 'surv_11', label: '2nd Surveillance', rows: stamp(surv2, '2nd Surveillance'), count: surv2.length },
    ];

    if (type !== 'all') {
      buckets = buckets.filter((b) => b.key === type);
    }

    const monthName = new Date(Date.UTC(year, month - 1, 1)).toLocaleString('en-US', {
      month: 'long',
      timeZone: 'UTC',
    });
    const total = buckets.reduce((s, b) => s + b.count, 0);

    this.logger.log(
      `Report ${monthName} ${year} [type=${type}] -> ` +
      `recert(orig ${recertYear})=${recert.length}, ` +
      `1st-surv(orig ${surv1Year})=${surv1.length}, ` +
      `2nd-surv(orig ${surv2Year})=${surv2.length}, total=${total}`,
    );

    return { year, month, monthName, buckets, total };
  }
}