// 🆕 Audit Report Approvals — QAQC gate for audit_schedule_rows.stg1/stg2_audit_report.
//   Scheme-scoped (QRS manager ↔ QRS rows). Shows ALL audit rows (missing
//   reports shown as "Missing" in the UI). Supports search + auditor + type +
//   report-status + month/year/date-range filters. Uses the scheme_dbs DataSource.
import {
  Injectable,
  Logger,
  NotFoundException,
  ForbiddenException,
  BadRequestException,
} from '@nestjs/common';
import { InjectDataSource, InjectRepository } from '@nestjs/typeorm';
import { DataSource, Repository } from 'typeorm';
import { User } from '../user/entities/user.entity';
import { AuditReportStorageService } from './services/audit-report-storage.service';

type Scope = { userId: number; scheme: 'QRS' | 'TQS' | null; seesAll: boolean };

export interface QueueFilters {
  page?: number;
  limit?: number | string;
  search?: string;
  approved?: 'all' | 'approved' | 'pending';
  auditorId?: number | string;
  auditType?: string;         // INITIAL | SURVEILLANCE | RECERTIFICATION
  status?: string;            // s1_uploaded | s1_missing | s2_uploaded | s2_missing
  year?: number | string;
  month?: number | string;    // 1-12
  from?: string;              // YYYY-MM-DD
  to?: string;                // YYYY-MM-DD
}

@Injectable()
export class AuditReportApprovalsService {
  private readonly logger = new Logger('AuditReportApprovalsService');

  private readonly ALL_SCHEME_ROLES = ['superadmin', 'admin'];
  private readonly SUPER_ADMIN_IDS = [1, 8, 29];

  constructor(
    @InjectDataSource('scheme_dbs')
    private readonly ds: DataSource,
    @InjectRepository(User, 'scheme_dbs')
    private readonly userRepo: Repository<User>,
    private readonly storage: AuditReportStorageService,
  ) {}

  // docType (frontend) → audit_schedule_rows column
  private static readonly DOC_COLUMN: Record<string, string> = {
    stage1_report: 'stg1_audit_report',
    stage2_report: 'stg2_audit_report',
    attendance: 'attendance_doc',
    support_docs: 'support_docs',
  };

  /**
   * Open an audit report/doc for a scheme approver. Unlike the my-audits
   * endpoint (which requires lead/co-auditor), this is scoped ONLY by scheme:
   * a QRS manager may open any QRS audit's files. Returns a streamable file.
   */
  async openReportFile(rowId: number, docType: string, actorId?: number) {
    const column = AuditReportApprovalsService.DOC_COLUMN[docType];
    if (!column) throw new BadRequestException(`Unknown document type: ${docType}`);

    const scope = await this.resolveActorScope(actorId);
    const row = await this.loadRow(rowId);           // has scheme
    this.assertSchemeAccess(row.scheme, scope);      // QRS↔QRS (no auditor check)

    const res = await this.ds.query(
      `SELECT \`${column}\` AS doc_path FROM audit_schedule_rows WHERE id = ? LIMIT 1`,
      [rowId],
    );
    const docPath: string | null = res?.[0]?.doc_path ?? null;
    if (!docPath) throw new NotFoundException(`No ${docType} uploaded for this audit`);

    return this.storage.openFile(docPath); // { stream, size, mimeType, filename }
  }

  private async resolveActorScope(userId?: number): Promise<Scope> {
    if (!userId) throw new ForbiddenException('Not authenticated');
    const user = await this.userRepo.findOne({
      where: { id: userId },
      relations: ['roles'],
    });
    if (!user) throw new ForbiddenException('User not found');
    const norm = (x: string) => String(x || '').toLowerCase().replace(/[\s_-]+/g, '');
    const roleNames = (user.roles || []).map((r: any) => norm(r?.name));
    const seesAll =
      this.SUPER_ADMIN_IDS.includes(Number(userId)) ||
      roleNames.some((n) => this.ALL_SCHEME_ROLES.map(norm).includes(n));
    return { userId, scheme: (user as any).primary_scheme ?? null, seesAll };
  }

  private async loadRow(id: number) {
    const rows = await this.ds.query(
      `SELECT r.id, r.report_qaqc_approved, c.scheme AS scheme
         FROM audit_schedule_rows r
         JOIN companies c ON c.id = r.company_id
        WHERE r.id = ? LIMIT 1`,
      [id],
    );
    if (!rows?.length) throw new NotFoundException('Audit report row not found');
    return rows[0];
  }

  private assertSchemeAccess(rowScheme: string | null, scope: Scope): void {
    if (scope.seesAll) return;
    if (!scope.scheme || rowScheme !== scope.scheme) {
      throw new ForbiddenException(
        `You can only act on ${scope.scheme ?? 'your assigned'} audit reports. ` +
          `This report belongs to ${rowScheme ?? 'another scheme'}.`,
      );
    }
  }

  /** MANAGE LIST — ALL audit rows (scheme-scoped) with rich filters. */
  async queue(actorId: number | undefined, f: QueueFilters = {}) {
    const scope = await this.resolveActorScope(actorId);
    const page = Number(f.page) > 0 ? Number(f.page) : 1;
    const take = Math.min(Math.max(Number(f.limit) || 10, 1), 100);
    const skip = (page - 1) * take;

    const where: string[] = ['1 = 1'];
    const params: any[] = [];

    if (!scope.seesAll) {
      if (!scope.scheme) {
        return { data: [], total: 0, page, lastPage: 0, scope: null, seesAll: false };
      }
      where.push('c.scheme = ?');
      params.push(scope.scheme);
    }

    // approval state (chips)
    if (f.approved === 'approved') where.push('r.report_qaqc_approved = 1');
    else if (f.approved === 'pending') where.push('r.report_qaqc_approved = 0');

    // report-status filter
    if (f.status === 's1_uploaded') where.push('r.stg1_audit_report IS NOT NULL');
    else if (f.status === 's1_missing') where.push('r.stg1_audit_report IS NULL');
    else if (f.status === 's2_uploaded') where.push('r.stg2_audit_report IS NOT NULL');
    else if (f.status === 's2_missing') where.push('r.stg2_audit_report IS NULL');

    if (f.auditType) { where.push('r.audit_type = ?'); params.push(String(f.auditType)); }
    if (f.auditorId) { where.push('r.lead_auditor_id = ?'); params.push(Number(f.auditorId)); }
    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.from) { where.push('s.schedule_date >= ?'); params.push(String(f.from)); }
    if (f.to) { where.push('s.schedule_date <= ?'); params.push(String(f.to)); }

    if (f.search && f.search.trim()) {
      where.push('(r.audit_code = ? OR c.name LIKE ?)');
      params.push(f.search.trim(), `%${f.search.trim()}%`);
    }

    const whereSql = 'WHERE ' + where.join(' AND ');

    const rows = await this.ds.query(
      `SELECT r.id, r.audit_code, r.audit_type, r.audit_stage, r.status,
              r.stg1_audit_report, r.stg2_audit_report,
              r.attendance_doc, r.support_docs,
              COALESCE(r.original_audit_date, s.schedule_date) AS audit_date,
              la.firstName AS auditor_first, la.lastName AS auditor_last,
              r.report_qaqc_approved, r.report_qaqc_rejected_reason,
              r.company_id, c.name AS company_name, c.scheme AS scheme
         FROM audit_schedule_rows r
         JOIN companies c ON c.id = r.company_id
         LEFT JOIN audit_schedules s ON s.id = r.schedule_id
         LEFT JOIN users la ON la.id = r.lead_auditor_id
         ${whereSql}
        ORDER BY audit_date DESC, r.id DESC
        LIMIT ? OFFSET ?`,
      [...params, take, skip],
    );

    const countRows = await this.ds.query(
      `SELECT COUNT(*) AS cnt
         FROM audit_schedule_rows r
         JOIN companies c ON c.id = r.company_id
         LEFT JOIN audit_schedules s ON s.id = r.schedule_id
         LEFT JOIN users la ON la.id = r.lead_auditor_id
         ${whereSql}`,
      params,
    );
    const total = Number(countRows?.[0]?.cnt || 0);

    const data = rows.map((r: any) => ({
      ...r,
      auditor_name: [r.auditor_first, r.auditor_last].filter(Boolean).join(' ') || '—',
    }));

    return { data, total, page, lastPage: Math.ceil(total / take), scope: scope.scheme, seesAll: scope.seesAll };
  }

  async approve(id: number, actorId?: number) {
    const scope = await this.resolveActorScope(actorId);
    const row = await this.loadRow(id);
    this.assertSchemeAccess(row.scheme, scope);
    await this.ds.query(
      `UPDATE audit_schedule_rows
          SET report_qaqc_approved = 1, report_qaqc_approved_by = ?,
              report_qaqc_approved_at = NOW(), report_qaqc_rejected_reason = NULL
        WHERE id = ?`,
      [actorId, id],
    );
    return { id, report_qaqc_approved: true };
  }

  async reject(id: number, actorId?: number, reason?: string) {
    const scope = await this.resolveActorScope(actorId);
    const row = await this.loadRow(id);
    this.assertSchemeAccess(row.scheme, scope);
    await this.ds.query(
      `UPDATE audit_schedule_rows
          SET report_qaqc_approved = 0, report_qaqc_approved_by = ?,
              report_qaqc_approved_at = NOW(), report_qaqc_rejected_reason = ?
        WHERE id = ?`,
      [actorId, (reason ?? '').slice(0, 500) || null, id],
    );
    return { id, report_qaqc_approved: false };
  }
}