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

import {
  AUDIT_TABLES,
  AUDIT_TYPE_DATE,
  AuditType,
  AuditTable,        // 🆕 add this line
  dateSet,
  reportUploaded,
  auditorJoin,
  auditorName,
  hasAuditAssign,
} from '../shared/audit-query.helper';

export interface AuditDetailFilters {
  source?: 'QRS' | 'TQS' | 'QRS & TQS' | 'all';
  audit_type?: AuditType | 'all';
  year?: string;
  month?: string;
  report_status?: 's1_missing' | 's2_missing' | 's1_uploaded' | 's2_uploaded' | 'all';
  phase?: 'conducted' | 'upcoming' | 'all';
  auditor?: string;
  search?: string;
  sort?: 'audit_date' | 'client' | 'auditor' | 'type';
  dir?: 'asc' | 'desc';
  page?: number;
  limit?: number;
  viewerUserId?: number;   // 🆕 when set, restrict to this auditor only
}

export interface AuditDetailRow {
  record_id: number;
  source: 'QRS' | 'TQS' | 'QRS & TQS';
  table: string;
  client_name: string | null;
  audit_date: string | null;
  year: string | null;
  month: string | null;
  audit_type: AuditType;
  stage1_uploaded: boolean;
  stage1_path: string | null;
  stage2_uploaded: boolean;
  stage2_path: string | null;
   attendance_uploaded: boolean;
  attendance_path: string | null;
  support_uploaded: boolean;
  support_path: string | null;
  auditor_id: number;
  auditor: string;
  age_days: number | null;
  conducted: boolean;
}

export interface AuditDetailResponse {
  rows: AuditDetailRow[];
  total: number;
  page: number;
  limit: number;
  totalPages: number;
  summary: {
    total_audits: number;
    stage1_missing: number;
    stage2_missing: number;
    total_assigned: number;
    conducted_count: number;
    scheduled_count: number;
    total_completed: number;
    stage1_uploaded: number;
    stage2_uploaded: number;
    initial_count: number;
    initial_completed: number;
    surveillance_count: number;
    surveillance_completed: number;
    recert_count: number;
    recert_completed: number;
  };
  aging: {
    bucket_0_30: number;
    bucket_31_60: number;
    bucket_61_90: number;
    bucket_90_plus: number;
  };
}

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

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

  ) { }

  // 🆕 scheme_dbs s user id → this legacy DB's user id, matched by email.
  private async resolveLegacyId(ds: DataSource, schemeUserId: number): Promise<number | null> {
    const schemeRows = await this.scheme.query(
      `SELECT email FROM users WHERE id = ? LIMIT 1`, [schemeUserId],
    );
    const email = (schemeRows[0]?.email || '').trim().toLowerCase();
    if (!email) return null;
    const rows = await ds.query(
      `SELECT id FROM users WHERE LOWER(email) = ? LIMIT 1`, [email],
    );
    return rows.length ? Number(rows[0].id) : null;
  }
  // 🆕 Transfer an audit from one auditor to another
  async transferAudit(p: {
    source: 'QRS' | 'TQS' | 'QRS & TQS';
    table?: string;
    record_id: number;
    from_auditor_id: number; // the row's current auditor (legacy id for QRS/TQS, scheme id for new)
    to_user_id: number;      // the new auditor (always a scheme_dbs s user id)
  }) {
    if (!p.record_id || !p.to_user_id) {
      throw new Error('record_id and to_user_id are required');
    }

    // ── NEW records: one column, direct update ──
    if (p.source === 'QRS & TQS') {
      await this.scheme.query(
        `UPDATE audit_schedule_rows SET lead_auditor_id = ? WHERE id = ?`,
        [p.to_user_id, p.record_id],
      );
      return { ok: true };
    }

    // ── LEGACY records: auditassign is a JSON array of user-id STRINGS ──
    if (!p.table || !AUDIT_TABLES.includes(p.table as AuditTable)) {
      throw new Error('invalid table');
    }
    const ds = p.source === 'QRS' ? this.qrs : this.tqs;

    // convert the target's scheme id → this DB's legacy id (matched by email)
    const toLegacyId = await this.resolveLegacyId(ds, p.to_user_id);
    if (toLegacyId == null) {
      throw new Error(`That auditor does not have an account in the ${p.source} database`);
    }

    // read the current auditassign array
    const rows = await ds.query(
      `SELECT auditassign FROM ${p.table} WHERE id = ? LIMIT 1`,
      [p.record_id],
    );
    if (!rows.length) throw new Error('Audit record not found');

    let list: string[];
    try {
      const parsed = JSON.parse(rows[0].auditassign || '[]');
      list = Array.isArray(parsed) ? parsed.map(String) : [];
    } catch {
      throw new Error('This record has an invalid auditassign value');
    }

    const fromStr = String(p.from_auditor_id);
    const toStr = String(toLegacyId);
    if (!list.includes(fromStr)) {
      throw new Error('The current auditor is not assigned to this record anymore — refresh and try again');
    }

    // replace old id with new id, avoiding duplicates
    const updated = Array.from(
      new Set(list.map((id) => (id === fromStr ? toStr : id))),
    );

    await ds.query(
      `UPDATE ${p.table} SET auditassign = ? WHERE id = ?`,
      [JSON.stringify(updated), p.record_id],
    );
    return { ok: true };
  }
  // 🆕 All scheme_dbs s users (for the transfer dropdown)
  async listAuditors(): Promise<{ user_id: number; name: string; email: string }[]> {
    const rows = await this.scheme.query(
      `SELECT id, TRIM(CONCAT(COALESCE(firstName,''),' ',COALESCE(lastName,''))) AS name, email
       FROM users
       ORDER BY name ASC`,
    );
    return rows.map((r: any) => ({
      user_id: Number(r.id),
      name: (r.name || '').trim() || `User #${r.id}`,
      email: r.email || '',
    }));
  }
  async build(f: AuditDetailFilters): Promise<AuditDetailResponse> {
    // clean the search text: remove spaces at ends, escape SQL wildcards
    if (f.search) {
      const t = String(f.search).trim();
      f = { ...f, search: t ? t.replace(/[%_]/g, '\\$&') : undefined };
    }

    const page = Math.max(1, Number(f.page) || 1);
    const limit = Math.min(200, Math.max(1, Number(f.limit) || 50));

    // Which audit types to include (one block per type).
    const types: AuditType[] =
      f.audit_type && f.audit_type !== 'all'
        ? [f.audit_type]
        : (Object.keys(AUDIT_TYPE_DATE) as AuditType[]);

    // Which databases to query.
    const wantQrs = !f.source || f.source === 'all' || f.source === 'QRS';
    const wantTqs = !f.source || f.source === 'all' || f.source === 'TQS';
    const wantNew = !f.source || f.source === 'all' || f.source === 'QRS & TQS';   // 🆕

    // 🆕 If scoped to one auditor, resolve their id in each DB up front.
    // Legacy DBs use a per-DB id matched by email; NEW audits use the scheme id directly.
    let qrsViewerId: number | null = null;
    let tqsViewerId: number | null = null;
    let schemeViewerId: number | null = null;
    if (f.viewerUserId) {
      schemeViewerId = f.viewerUserId;
      [qrsViewerId, tqsViewerId] = await Promise.all([
        this.resolveLegacyId(this.qrs, f.viewerUserId),
        this.resolveLegacyId(this.tqs, f.viewerUserId),
      ]);
    }

    const [qrsRows, tqsRows, newRows] = await Promise.all([   // 🆕 add newRows
      wantQrs ? this.queryDb(this.qrs, 'QRS', types, f) : Promise.resolve([]),
      wantTqs ? this.queryDb(this.tqs, 'TQS', types, f) : Promise.resolve([]),
      wantNew ? this.queryNewAudits(types, f) : Promise.resolve([]),
    ]);

    let rows = [...qrsRows, ...tqsRows, ...newRows];

    // 🆕 Scope: keep only rows whose auditor_id matches the viewer in that row's source.
    // Runs BEFORE summary/aging/pagination so every number reflects the auditor's own data.
    if (f.viewerUserId) {
      rows = rows.filter((r) => {
        if (r.source === 'QRS') return qrsViewerId != null && r.auditor_id === qrsViewerId;
        if (r.source === 'TQS') return tqsViewerId != null && r.auditor_id === tqsViewerId;
        return schemeViewerId != null && r.auditor_id === schemeViewerId; // 'QRS & TQS'
      });
    }

    // ── sort ──
    const dir = f.dir === 'asc' ? 1 : -1;
    const key = f.sort || 'audit_date';
    rows.sort((a, b) => {
      // new combined-source records float to the top
      const aNew = a.source === 'QRS & TQS' ? 1 : 0;
      const bNew = b.source === 'QRS & TQS' ? 1 : 0;
      if (aNew !== bNew) return bNew - aNew;

      // then your existing date/field sort
      const dir = f.dir === 'asc' ? 1 : -1;
      const key = f.sort || 'audit_date';
      let av: any, bv: any;
      if (key === 'client') { av = a.client_name || ''; bv = b.client_name || ''; }
      else if (key === 'auditor') { av = a.auditor || ''; bv = b.auditor || ''; }
      else if (key === 'type') { av = a.audit_type; bv = b.audit_type; }
      else { av = a.audit_date || ''; bv = b.audit_date || ''; }
      return av < bv ? -dir : av > bv ? dir : 0;
    });

    // ── summary + aging from the FULL filtered set ──
    const completed = (rs: AuditDetailRow[]) => rs.filter((r) => r.stage2_uploaded).length;

    // Only CONDUCTED audits (date <= today) count toward completion / missing.
    // Future/scheduled audits are not "missing a report" — they haven't happened yet.
    const conductedRows = rows.filter((r) => r.conducted);
    const cByType = (t: AuditType) => conductedRows.filter((r) => r.audit_type === t);
    const initialC = cByType('Initial');
    const survC = cByType('Surveillance');
    const recertC = cByType('Recertification');

    const summary = {
      total_audits: rows.length,
      total_assigned: rows.length,            // every audit event in the filtered set
      conducted_count: conductedRows.length,  // date <= today
      scheduled_count: rows.length - conductedRows.length, // future
      // completion / stage stats are over CONDUCTED audits only
      total_completed: conductedRows.filter((r) => r.stage2_uploaded).length,
      stage1_uploaded: conductedRows.filter((r) => r.stage1_uploaded).length,
      stage1_missing: conductedRows.filter((r) => !r.stage1_uploaded).length,
      stage2_uploaded: conductedRows.filter((r) => r.stage2_uploaded).length,
      stage2_missing: conductedRows.filter((r) => !r.stage2_uploaded).length,
      initial_count: initialC.length,
      initial_completed: completed(initialC),
      surveillance_count: survC.length,
      surveillance_completed: completed(survC),
      recert_count: recertC.length,
      recert_completed: completed(recertC),
    };
    const aging = { bucket_0_30: 0, bucket_31_60: 0, bucket_61_90: 0, bucket_90_plus: 0 };
    for (const r of conductedRows) {
      // Age only CONDUCTED rows that still have an outstanding stage-2 report.
      if (r.stage2_uploaded || r.age_days == null) continue;
      const d = r.age_days;
      if (d <= 30) aging.bucket_0_30++;
      else if (d <= 60) aging.bucket_31_60++;
      else if (d <= 90) aging.bucket_61_90++;
      else aging.bucket_90_plus++;
    }

    // ── paginate ──
    const total = rows.length;
    const totalPages = Math.ceil(total / limit) || 1;
    const start = (page - 1) * limit;
    const pageRows = rows.slice(start, start + limit);

    this.logger.log(
      `[AUDIT-DETAIL] page ${page}/${totalPages} of ${total} rows ` +
      `(qrs=${qrsRows.length} tqs=${tqsRows.length} new=${newRows.length})` +
      (f.viewerUserId ? ` scopedTo=${f.viewerUserId}` : ''),
    );

    return { rows: pageRows, total, page, limit, totalPages, summary, aging };
  }
  /** Query scheme_dbs .audit_schedule_rows for the NEW audits. */
  private async queryNewAudits(
    types: AuditType[],
    f: AuditDetailFilters,
  ): Promise<AuditDetailRow[]> {
    // enum value in DB → our display type
    const ENUM_TO_TYPE: Record<string, AuditType> = {
      INITIAL: 'Initial',
      SURVEILLANCE: 'Surveillance',
      RECERTIFICATION: 'Recertification',
    };
    // reverse: which DB enum values match the requested display types
    const wantEnum = Object.entries(ENUM_TO_TYPE)
      .filter(([, disp]) => types.includes(disp))
      .map(([en]) => en);
    if (!wantEnum.length) return [];

    const where: string[] = [
      `r.audit_type IN (${wantEnum.map(() => '?').join(',')})`,
    ];
    const params: any[] = [...wantEnum];

    if (f.year) { where.push(`YEAR(s.schedule_date) = ?`); params.push(Number(f.year)); }
    if (f.month) { where.push(`MONTH(s.schedule_date) = ?`); params.push(Number(f.month)); }
    if (f.search) { where.push(`co.name LIKE ?`); params.push(`%${f.search}%`); }
    if (f.auditor) {
      where.push(`TRIM(CONCAT(COALESCE(u.firstName,''),' ',COALESCE(u.lastName,''))) = ?`);
      params.push(f.auditor);
    }
    if (f.report_status === 's1_missing') where.push(`(r.stg1_audit_report IS NULL OR r.stg1_audit_report = '')`);
    if (f.report_status === 's2_missing') where.push(`(r.stg2_audit_report IS NULL OR r.stg2_audit_report = '')`);
    if (f.report_status === 's1_uploaded') where.push(`(r.stg1_audit_report IS NOT NULL AND r.stg1_audit_report <> '')`);
    if (f.report_status === 's2_uploaded') where.push(`(r.stg2_audit_report IS NOT NULL AND r.stg2_audit_report <> '')`);
    if (f.phase === 'conducted') where.push(`s.schedule_date <= CURDATE()`);
    if (f.phase === 'upcoming') where.push(`s.schedule_date > CURDATE()`);

    // only real scheduled rows with a date
    where.push(`s.schedule_date IS NOT NULL AND YEAR(s.schedule_date) > 0`);

    const sql = `
    SELECT
      r.id AS record_id,
      'audit_schedule_rows' AS src_table,
      co.name AS client_name,
      s.schedule_date AS audit_date,
      r.audit_type AS audit_type_enum,
      CASE WHEN (r.stg1_audit_report IS NOT NULL AND r.stg1_audit_report <> '') THEN 1 ELSE 0 END AS stage1_uploaded,
      r.stg1_audit_report AS stage1_path,
      CASE WHEN (r.stg2_audit_report IS NOT NULL AND r.stg2_audit_report <> '') THEN 1 ELSE 0 END AS stage2_uploaded,
      r.stg2_audit_report AS stage2_path,
      CASE WHEN (r.attendance_doc IS NOT NULL AND r.attendance_doc <> '') THEN 1 ELSE 0 END AS attendance_uploaded,
      r.attendance_doc AS attendance_path,
      CASE WHEN (r.support_docs IS NOT NULL AND r.support_docs <> '') THEN 1 ELSE 0 END AS support_uploaded,
      r.support_docs AS support_path,
      u.id AS auditor_id,
      TRIM(CONCAT(COALESCE(u.firstName,''),' ',COALESCE(u.lastName,''))) AS auditor,
      DATEDIFF(CURDATE(), s.schedule_date) AS age_days
    FROM audit_schedule_rows r
    LEFT JOIN audit_schedules s ON s.id = r.schedule_id
    LEFT JOIN companies co       ON co.id = r.company_id
    LEFT JOIN users u            ON u.id = r.lead_auditor_id
    WHERE ${where.join(' AND ')}
  `;

    let raw: any[];
    try {
      raw = await this.scheme.query(sql, params);
    } catch (e: any) {
      this.logger.warn(`[AUDIT-DETAIL] NEW query failed: ${e.message}`);
      return [];
    }

    return raw.map((r): AuditDetailRow => {
      const ym = r.audit_date ? new Date(r.audit_date).toISOString().slice(0, 7) : null;
      const ageDays = r.age_days == null ? null : Number(r.age_days);
      const conducted = ageDays != null && ageDays >= 0;
      return {
        record_id: Number(r.record_id),
        source: 'QRS & TQS',
        table: r.src_table,
        client_name: r.client_name ?? null,
        audit_date: r.audit_date ?? null,
        year: ym ? ym.slice(0, 4) : null,
        month: ym,
        audit_type: ENUM_TO_TYPE[r.audit_type_enum] ?? 'Initial',
        stage1_uploaded: Number(r.stage1_uploaded) === 1,
        stage1_path: r.stage1_path ?? null,
        stage2_uploaded: Number(r.stage2_uploaded) === 1,
        stage2_path: r.stage2_path ?? null,
        attendance_uploaded: Number(r.attendance_uploaded) === 1,
        attendance_path: r.attendance_path ?? null,
        support_uploaded: Number(r.support_uploaded) === 1,
        support_path: r.support_path ?? null,
        auditor_id: Number(r.auditor_id) || 0,
        auditor: (r.auditor || '').trim() || `User #${r.auditor_id}`,
        age_days: ageDays,
        conducted,
      };
    });
  }
  /** Query one database: build a UNION of (table × type) blocks, then shape. */
  private async queryDb(
    ds: DataSource,
    source: 'QRS' | 'TQS',
    types: AuditType[],
    f: AuditDetailFilters,
  ): Promise<AuditDetailRow[]> {
    const blocks: string[] = [];
    const params: any[] = [];

    for (const table of AUDIT_TABLES) {
      for (const type of types) {
        const dateCol = AUDIT_TYPE_DATE[type];
        const where: string[] = [hasAuditAssign('c'), dateSet(`c.${dateCol}`)];

        if (f.year) { where.push(`YEAR(c.${dateCol}) = ?`); params.push(Number(f.year)); }
        if (f.month) { where.push(`MONTH(c.${dateCol}) = ?`); params.push(Number(f.month)); }
        if (f.search) { where.push(`c.company_name LIKE ?`); params.push(`%${f.search}%`); }
        if (f.auditor) { where.push(`${auditorName('u')} = ?`); params.push(f.auditor); }
        if (f.report_status === 's1_missing') where.push(`NOT ${reportUploaded('c.stg1_audit_report')}`);
        if (f.report_status === 's2_missing') where.push(`NOT ${reportUploaded('c.stg2_audit_report')}`);
        if (f.report_status === 's1_uploaded') where.push(reportUploaded('c.stg1_audit_report'));
        if (f.report_status === 's2_uploaded') where.push(reportUploaded('c.stg2_audit_report'));
        if (f.phase === 'conducted') where.push(`c.${dateCol} <= CURDATE()`);
        if (f.phase === 'upcoming') where.push(`c.${dateCol} > CURDATE()`);

        blocks.push(`
          SELECT
            c.id AS record_id,
            '${table}' AS src_table,
            c.company_name AS client_name,
            c.${dateCol} AS audit_date,
            '${type}' AS audit_type,
            CASE WHEN ${reportUploaded('c.stg1_audit_report')} THEN 1 ELSE 0 END AS stage1_uploaded,
            c.stg1_audit_report AS stage1_path,
            CASE WHEN ${reportUploaded('c.stg2_audit_report')} THEN 1 ELSE 0 END AS stage2_uploaded,
            c.stg2_audit_report AS stage2_path,
            u.id AS auditor_id,
            ${auditorName('u')} AS auditor,
            DATEDIFF(CURDATE(), c.${dateCol}) AS age_days
          FROM ${table} c
          ${auditorJoin('c')}
          WHERE ${where.join(' AND ')}
        `);
      }
    }

    const sql = blocks.join(' UNION ALL ');

    let raw: any[];
    try {
      raw = await ds.query(sql, params);
    } catch (e: any) {
      this.logger.warn(`[AUDIT-DETAIL] ${source} query failed: ${e.message}`);
      return [];
    }

    return raw.map((r): AuditDetailRow => {
      const ym = r.audit_date
        ? new Date(r.audit_date).toISOString().slice(0, 7)
        : null;
      const ageDays = r.age_days == null ? null : Number(r.age_days);
      // DATEDIFF(CURDATE(), date): >= 0 means date is today or in the past = conducted.
      const conducted = ageDays != null && ageDays >= 0;
      return {
        record_id: Number(r.record_id),
        source,
        table: r.src_table,
        client_name: r.client_name ?? null,
        audit_date: r.audit_date ?? null,
        year: ym ? ym.slice(0, 4) : null,
        month: ym,
        audit_type: r.audit_type,
        stage1_uploaded: Number(r.stage1_uploaded) === 1,
        stage1_path: r.stage1_path ?? null,
        stage2_uploaded: Number(r.stage2_uploaded) === 1,
        stage2_path: r.stage2_path ?? null,
        attendance_uploaded: false,
        attendance_path: null,
        support_uploaded: false,
        support_path: null,
        auditor_id: Number(r.auditor_id),
        auditor: (r.auditor || '').trim() || `User #${r.auditor_id}`,
        age_days: ageDays,
        conducted,
      };
    });
  }
}