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

import {
  AUDIT_TABLES,
  dateSet,
  reportUploaded,
  auditorJoin,
  auditorName,
  hasAuditAssign,
} from '../shared/audit-query.helper';

export interface AuditAssignMonth {
  month: string; // 'YYYY-MM'
  total_assigned: number;
  stage1_uploaded: number;
  stage2_uploaded: number;
  initial: number;
  surveillance: number;
  recertification: number;
}

export interface AuditAssignRow {
  user_id: number;
  auditor: string;
  source: 'QRS' | 'TQS' | 'QRS & TQS';
  total_assigned: number;
  stage1_uploaded: number;
  stage1_missing: number;
  stage2_uploaded: number;
  stage2_missing: number;
  initial: number;
  surveillance: number;
  recertification: number;
  months: AuditAssignMonth[];
}

export interface AuditAssignReportResponse {
  rows: AuditAssignRow[];
  totals: {
    total_assigned: number;
    stage1_uploaded: number;
    stage1_missing: number;
    stage2_uploaded: number;
    stage2_missing: number;
    initial: number;
    surveillance: number;
    recertification: number;
  };
}

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

  constructor(
    @InjectDataSource('qrs') private readonly qrs: DataSource,
    @InjectDataSource('tqs') private readonly tqs: DataSource,
    @InjectDataSource('scheme_dbs') private readonly scheme: DataSource,   // 🆕

  ) { }
  private async queryNewAssign(): Promise<AuditAssignRow[]> {
    const sql = `
    SELECT
      u.id AS user_id,
      TRIM(CONCAT(COALESCE(u.firstName,''),' ',COALESCE(u.lastName,''))) AS auditor,
      COUNT(*) AS total_assigned,
      SUM(CASE WHEN (r.stg1_audit_report IS NOT NULL AND r.stg1_audit_report <> '') THEN 1 ELSE 0 END) AS stage1_uploaded,
      SUM(CASE WHEN (r.stg1_audit_report IS NULL OR r.stg1_audit_report = '') THEN 1 ELSE 0 END) AS stage1_missing,
      SUM(CASE WHEN (r.stg2_audit_report IS NOT NULL AND r.stg2_audit_report <> '') THEN 1 ELSE 0 END) AS stage2_uploaded,
      SUM(CASE WHEN (r.stg2_audit_report IS NULL OR r.stg2_audit_report = '') THEN 1 ELSE 0 END) AS stage2_missing,
      SUM(CASE WHEN r.audit_type = 'INITIAL'         THEN 1 ELSE 0 END) AS initial,
      SUM(CASE WHEN r.audit_type = 'SURVEILLANCE'    THEN 1 ELSE 0 END) AS surveillance,
      SUM(CASE WHEN r.audit_type = 'RECERTIFICATION' THEN 1 ELSE 0 END) AS recertification
    FROM audit_schedule_rows r
    LEFT JOIN users u ON u.id = r.lead_auditor_id
    WHERE r.lead_auditor_id IS NOT NULL
    GROUP BY u.id, auditor
  `;
    let raw: any[];
    try {
      raw = await this.scheme.query(sql);
    } catch (e: any) {
      this.logger.warn(`[AUDIT-ASSIGN] NEW failed: ${e?.message}`);
      return [];
    }
    return raw.map((r): AuditAssignRow => ({
      user_id: Number(r.user_id),
      auditor: (r.auditor || '').trim() || `User #${r.user_id}`,
      source: 'QRS & TQS',
      total_assigned: Number(r.total_assigned || 0),
      stage1_uploaded: Number(r.stage1_uploaded || 0),
      stage1_missing: Number(r.stage1_missing || 0),
      stage2_uploaded: Number(r.stage2_uploaded || 0),
      stage2_missing: Number(r.stage2_missing || 0),
      initial: Number(r.initial || 0),
      surveillance: Number(r.surveillance || 0),
      recertification: Number(r.recertification || 0),
      months: [],
    }));
  }

  async build(): Promise<AuditAssignReportResponse> {
    // ── per-user totals (one query per table, UNION-ed) ──
    const totalsSql = AUDIT_TABLES.map(
      (t) => `
      SELECT
        u.id AS user_id,
        ${auditorName('u')} AS auditor,
        COUNT(*) AS total_assigned,
        SUM(CASE WHEN ${reportUploaded('c.stg1_audit_report')} THEN 1 ELSE 0 END) AS stage1_uploaded,
        SUM(CASE WHEN ${reportUploaded('c.stg1_audit_report')} THEN 0 ELSE 1 END) AS stage1_missing,
        SUM(CASE WHEN ${reportUploaded('c.stg2_audit_report')} THEN 1 ELSE 0 END) AS stage2_uploaded,
        SUM(CASE WHEN ${reportUploaded('c.stg2_audit_report')} THEN 0 ELSE 1 END) AS stage2_missing,
        SUM(CASE WHEN ${dateSet('c.auditdate')}   THEN 1 ELSE 0 END) AS initial,
        SUM(CASE WHEN ${dateSet('c.serv_date')}   THEN 1 ELSE 0 END) AS surveillance,
        SUM(CASE WHEN ${dateSet('c.recert_date')} THEN 1 ELSE 0 END) AS recertification
      FROM ${t} c
      ${auditorJoin('c')}
      WHERE ${hasAuditAssign('c')}
      GROUP BY u.id, auditor
    `,
    ).join(' UNION ALL ');

    const totalsWrapped = `
      SELECT user_id, MAX(auditor) AS auditor,
        SUM(total_assigned) AS total_assigned,
        SUM(stage1_uploaded) AS stage1_uploaded, SUM(stage1_missing) AS stage1_missing,
        SUM(stage2_uploaded) AS stage2_uploaded, SUM(stage2_missing) AS stage2_missing,
        SUM(initial) AS initial, SUM(surveillance) AS surveillance, SUM(recertification) AS recertification
      FROM ( ${totalsSql} ) AS u_all
      GROUP BY user_id
      ORDER BY total_assigned DESC
    `;

    // ── per-user per-month (each type by ITS OWN date) ──
    const monthsSql = AUDIT_TABLES.map(
      (t) => `
      SELECT u.id AS user_id, DATE_FORMAT(c.auditdate,'%Y-%m') AS month,
        COUNT(*) AS total_assigned,
        SUM(CASE WHEN ${reportUploaded('c.stg1_audit_report')} THEN 1 ELSE 0 END) AS stage1_uploaded,
        SUM(CASE WHEN ${reportUploaded('c.stg2_audit_report')} THEN 1 ELSE 0 END) AS stage2_uploaded,
        COUNT(*) AS initial, 0 AS surveillance, 0 AS recertification
      FROM ${t} c ${auditorJoin('c')}
      WHERE ${hasAuditAssign('c')} AND ${dateSet('c.auditdate')}
      GROUP BY u.id, month
      UNION ALL
      SELECT u.id AS user_id, DATE_FORMAT(c.serv_date,'%Y-%m') AS month,
        0,0,0, 0 AS initial, COUNT(*) AS surveillance, 0 AS recertification
      FROM ${t} c ${auditorJoin('c')}
      WHERE ${hasAuditAssign('c')} AND ${dateSet('c.serv_date')}
      GROUP BY u.id, month
      UNION ALL
      SELECT u.id AS user_id, DATE_FORMAT(c.recert_date,'%Y-%m') AS month,
        0,0,0, 0 AS initial, 0 AS surveillance, COUNT(*) AS recertification
      FROM ${t} c ${auditorJoin('c')}
      WHERE ${hasAuditAssign('c')} AND ${dateSet('c.recert_date')}
      GROUP BY u.id, month
    `,
    ).join(' UNION ALL ');

    const monthsWrapped = `
      SELECT user_id, month,
        SUM(total_assigned) AS total_assigned,
        SUM(stage1_uploaded) AS stage1_uploaded, SUM(stage2_uploaded) AS stage2_uploaded,
        SUM(initial) AS initial, SUM(surveillance) AS surveillance, SUM(recertification) AS recertification
      FROM ( ${monthsSql} ) AS m_all
      GROUP BY user_id, month
      ORDER BY month ASC
    `;

    const [qrsTotals, tqsTotals, qrsMonths, tqsMonths, newRows] = await Promise.all([
      this.qrs.query(totalsWrapped).catch((e) => this.warn('QRS totals', e)),
      this.tqs.query(totalsWrapped).catch((e) => this.warn('TQS totals', e)),
      this.qrs.query(monthsWrapped).catch((e) => this.warn('QRS months', e)),
      this.tqs.query(monthsWrapped).catch((e) => this.warn('TQS months', e)),
      this.queryNewAssign(),   // 🆕
    ]);

    const monthIndex = new Map<string, AuditAssignMonth[]>();
    const indexMonths = (rows: any[], source: 'QRS' | 'TQS') => {
      for (const m of rows as any[]) {
        const key = `${source}:${Number(m.user_id)}`;
        if (!monthIndex.has(key)) monthIndex.set(key, []);
        monthIndex.get(key)!.push({
          month: m.month,
          total_assigned: Number(m.total_assigned || 0),
          stage1_uploaded: Number(m.stage1_uploaded || 0),
          stage2_uploaded: Number(m.stage2_uploaded || 0),
          initial: Number(m.initial || 0),
          surveillance: Number(m.surveillance || 0),
          recertification: Number(m.recertification || 0),
        });
      }
    };
    indexMonths(qrsMonths, 'QRS');
    indexMonths(tqsMonths, 'TQS');

    const buildRows = (rows: any[], source: 'QRS' | 'TQS'): AuditAssignRow[] =>
      (rows as any[]).map((r) => ({
        user_id: Number(r.user_id),
        auditor: (r.auditor || '').trim() || `User #${r.user_id}`,
        source,
        total_assigned: Number(r.total_assigned || 0),
        stage1_uploaded: Number(r.stage1_uploaded || 0),
        stage1_missing: Number(r.stage1_missing || 0),
        stage2_uploaded: Number(r.stage2_uploaded || 0),
        stage2_missing: Number(r.stage2_missing || 0),
        initial: Number(r.initial || 0),
        surveillance: Number(r.surveillance || 0),
        recertification: Number(r.recertification || 0),
        months: monthIndex.get(`${source}:${Number(r.user_id)}`) || [],
      }));

    const rows = [
      ...buildRows(qrsTotals, 'QRS'),
      ...buildRows(tqsTotals, 'TQS'),
      ...newRows,   // 🆕
    ].sort((a, b) => b.total_assigned - a.total_assigned);
    const totals = rows.reduce(
      (acc, r) => {
        acc.total_assigned += r.total_assigned;
        acc.stage1_uploaded += r.stage1_uploaded;
        acc.stage1_missing += r.stage1_missing;
        acc.stage2_uploaded += r.stage2_uploaded;
        acc.stage2_missing += r.stage2_missing;
        acc.initial += r.initial;
        acc.surveillance += r.surveillance;
        acc.recertification += r.recertification;
        return acc;
      },
      {
        total_assigned: 0, stage1_uploaded: 0, stage1_missing: 0,
        stage2_uploaded: 0, stage2_missing: 0,
        initial: 0, surveillance: 0, recertification: 0,
      },
    );

    this.logger.log(
      `[AUDIT-ASSIGN] ${rows.length} rows (qrs=${qrsTotals.length} tqs=${tqsTotals.length}) total_assigned=${totals.total_assigned}`,
    );

    return { rows, totals };
  }

  private warn(label: string, e: any): any[] {
    this.logger.warn(`[AUDIT-ASSIGN] ${label} failed: ${e?.message}`);
    return [];
  }
}