import {
  Injectable,
  BadRequestException,
  NotFoundException,
  Logger,
} from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository, SelectQueryBuilder } from 'typeorm';
import { createReadStream, existsSync, statSync } from 'fs';
import { join, normalize, basename, extname } from 'path';
import type { Readable } from 'stream';

import { Company } from '../../companies/entities/company.entity';
import { CompanyBranch } from '../../companies/entities/company-branch.entity';
import { AuditRequest } from '../../audit-requests/entities/audit-request.entity';
import { NewCertificate } from '../../certificates/entities/new-certificate.entity';  // ✅ UPDATED
import { AuditScheduleRow } from '../../audit-schedules/entities/audit-schedule-row.entity';
import { getErrorMessage } from '../../common/utils/error.helper';
import { NcEntity } from '../../nc/entities/nc.entity';


/**
 * CLIENT PORTAL SERVICE
 * 
 * Handles all client dashboard operations:
 * - Get company info
 * - Get branches list
 * - Get audit requests with filters
 * - Get audit progress/timeline
 * - Get audit report
 * - Get certificates
 */
@Injectable()
export class ClientPortalService {
  private readonly logger = new Logger('ClientPortalService');

  constructor(
    @InjectRepository(Company, 'scheme_dbs')
    private companyRepository: Repository<Company>,
    @InjectRepository(CompanyBranch, 'scheme_dbs')
    private branchRepository: Repository<CompanyBranch>,
    @InjectRepository(AuditRequest, 'scheme_dbs')
    private auditRequestRepository: Repository<AuditRequest>,
    @InjectRepository(NewCertificate, 'scheme_dbs')  // ✅ UPDATED
    private newCertificateRepository: Repository<NewCertificate>,  // ✅ UPDATED
    @InjectRepository(AuditScheduleRow, 'scheme_dbs')
    private auditScheduleRowRepository: Repository<AuditScheduleRow>,
    @InjectRepository(NcEntity, 'scheme_dbs')
    private ncRepository: Repository<NcEntity>,

  ) { }

  /**
   * ═════════════════════════════════════════════════════════
   * GET COMPANY INFO
   * ═════════════════════════════════════════════════════════
   */
  async getCompanyInfo(companyId: number): Promise<any> {
    this.logger.log(`Getting company info: company_id=${companyId}`);

    try {
      const company = await this.companyRepository.findOne({
        where: { id: companyId },
        relations: ['country', 'standards'],
      });

      if (!company) {
        throw new NotFoundException(`Company ${companyId} not found`);
      }

      // ✅ ACTUAL PROPERTIES: Use real entity property names
      return {
        id: company.id,
        company_code: company.company_code,           // ✅ Real property
        name: company.name,                           // ✅ Real property
        normalized_name: company.normalized_name,     // ✅ Real property
        trade_license_no: company.trade_license_no,   // ✅ Real property
        address: company.address,                     // ✅ Real property
        city: company.city,                           // ✅ Real property
        contact_person: company.contact_person,       // ✅ Real property
        designation: company.designation,             // ✅ Real property
        email: company.email,                         // ✅ Real property
        mobile: company.mobile,                       // ✅ CORRECT (not phone_number!)
        telephone: company.telephone,                 // ✅ Real property
        fax: company.fax,                             // ✅ Real property
        reference_number: company.reference_number,   // ✅ Real property
        validity: company.validity,                   // ✅ Real property
        certification_body: company.certification_body, // ✅ Real property
        client_group: company.client_group,           // ✅ Real property
        accreditation: company.accreditation,         // ✅ Real property
        scope_of_work: company.scope_of_work,         // ✅ Real property
        country: company.country,                     // ✅ Real relation
        standards: company.standards,                 // ✅ Real relation
        documents: company.documents || [],
        created_at: company.created_at,               // ✅ Real property
        updated_at: company.updated_at,               // ✅ Real property
      };
    } catch (error) {
      // ✅ FIX: Use error helper
      this.logger.error(
        `❌ Get company info failed: ${getErrorMessage(error)}`,
      );
      throw error;
    }
  }

  /**
   * ═════════════════════════════════════════════════════════
   * GET COMPANY BRANCHES
   * ═════════════════════════════════════════════════════════
   */
  async getBranches(companyId: number): Promise<any[]> {
    this.logger.log(`Getting branches: company_id=${companyId}`);

    try {
      // ✅ CORRECT: Use companyId (camelCase) for filtering
      const branches = await this.branchRepository.find({
        where: { companyId },  // ✅ camelCase property
        relations: ['company'],
        order: { createdAt: 'DESC' },
      });

      if (!branches || branches.length === 0) {
        this.logger.warn(`No branches found for company ${companyId}`);
        return [];
      }

      // ✅ ACTUAL PROPERTIES: Use real entity property names
      return branches.map((b) => ({
        id: b.id,
        companyId: b.companyId,                      // ✅ camelCase
        branchName: b.branchName,                    // ✅ CORRECT (not name!)
        normalizedBranchName: b.normalizedBranchName,
        tradeLicenseNo: b.tradeLicenseNo,            // ✅ camelCase
        address: b.address,
        city: b.city,
        countryId: b.countryId,
        contactPerson: b.contactPerson,              // ✅ CORRECT (not contact_person_name!)
        designation: b.designation,
        email: b.email,
        mobile: b.mobile,                            // ✅ CORRECT (not phone_number!)
        telephone: b.telephone,
        scopeOfWork: b.scopeOfWork,                  // ✅ camelCase
        certificationBody: b.certificationBody,      // ✅ camelCase
        accreditation: b.accreditation,
        isHeadOffice: b.isHeadOffice,                // ✅ camelCase
        status: b.status,
        createdAt: b.createdAt,
        updatedAt: b.updatedAt,
      }));
    } catch (error) {
      // ✅ FIX: Use error helper
      this.logger.error(
        `❌ Get branches failed: ${getErrorMessage(error)}`,
      );
      throw error;
    }
  }

  /**
   * ═════════════════════════════════════════════════════════
   * GET AUDIT REQUESTS
   * ═════════════════════════════════════════════════════════
   */
  async getAudits(
    companyId: number,
    options: {
      status?: string;
      limit?: number;
      page?: number;
      sort_by?: string;
      sort_order?: 'ASC' | 'DESC';
    },
  ): Promise<{ data: any[]; total: number; page: number; limit: number }> {
    this.logger.log(
      `Getting audits: company_id=${companyId}, status=${options.status}`,
    );

    try {
      const limit = options.limit || 10;
      const page = options.page || 1;
      const skip = (page - 1) * limit;
      const sortBy = options.sort_by || 'created_at';
      const sortOrder = options.sort_order || 'DESC';

      // ✅ BUILD QUERY: Use real property names
      let query: SelectQueryBuilder<AuditRequest> = this.auditRequestRepository
        .createQueryBuilder('audit')
        .leftJoinAndSelect('audit.branch', 'branch')
        .leftJoinAndSelect('audit.audit_schedule_row', 'asr')
        .leftJoinAndSelect('asr.lead_auditor', 'leadAuditor')
        .leftJoinAndSelect('asr.co_auditors', 'coAuditors')
        .leftJoinAndSelect('asr.standards', 'standards')
        .where('audit.company_id = :companyId', { companyId });
      // ✅ CORRECT: Use real property: standard_ids (not standards)
      // Note: standard_ids is JSON array stored in DB

      // Optional: Filter by status
      if (options.status) {
        query = query.andWhere('audit.status = :status', {
          status: options.status,
        });
      }

      // ✅ CORRECT: Use real property name proposed_date (not proposed_audit_date)
      query = query.orderBy(`audit.${sortBy}`, sortOrder);
      query = query.skip(skip).take(limit);

      const [audits, total] = await query.getManyAndCount();

      // ✅ FORMAT RESPONSE: Use real property names
      const data = audits.map((audit) => ({
        id: audit.id,
        audit_code: audit.audit_schedule_row?.audit_code || null,
        audit_stage: audit.audit_schedule_row?.audit_stage || null,
        company_id: audit.company_id,
        company_branch_id: audit.company_branch_id,
        // ✅ CORRECT: Use real property: standard_ids (JSON array)
        standard_ids: audit.standard_ids,
        // ✅ CORRECT: Use real property: proposed_date (not proposed_audit_date)
        proposed_date: audit.proposed_date,
        status: audit.status,
        audit_schedule_row_id: audit.audit_schedule_row_id,
        location: audit.location,
        mode: audit.mode,
        branch: audit.branch
          ? {
            id: audit.branch.id,
            branchName: audit.branch.branchName,    // ✅ CORRECT
            mobile: audit.branch.mobile,            // ✅ CORRECT
            contactPerson: audit.branch.contactPerson, // ✅ CORRECT
          }
          : null,
        audit_type: audit.audit_schedule_row?.audit_type ?? null,
        schedule_status: audit.audit_schedule_row?.status ?? null,
        lead_auditor: audit.audit_schedule_row?.lead_auditor
          ? {
            id: audit.audit_schedule_row.lead_auditor.id,
            firstName: audit.audit_schedule_row.lead_auditor.firstName,
            lastName: audit.audit_schedule_row.lead_auditor.lastName,
          }
          : null,
        co_auditors: (audit.audit_schedule_row?.co_auditors ?? []).map((u) => ({
          id: u.id,
          firstName: u.firstName,
          lastName: u.lastName,
        })),
        standards: (audit.audit_schedule_row?.standards ?? []).map((s) => ({
          id: s.id,
          name: s.name,
        })),
        stg1_audit_report: audit.audit_schedule_row?.stg1_audit_report || null,
        stg2_audit_report: audit.audit_schedule_row?.stg2_audit_report || null,
        attendance_doc: audit.audit_schedule_row?.attendance_doc || null,
        support_docs: audit.audit_schedule_row?.support_docs || null,
        audit_schedule_row: audit.audit_schedule_row
          ? { id: audit.audit_schedule_row.id, status: audit.audit_schedule_row.status }
          : null,
        created_at: audit.created_at,
        updated_at: audit.updated_at,
      }));

      return {
        data,
        total,
        page,
        limit,
      };
    } catch (error) {
      // ✅ FIX: Use error helper
      this.logger.error(`❌ Get audits failed: ${getErrorMessage(error)}`);
      throw error;
    }
  }

  /**
   * ═════════════════════════════════════════════════════════
   * GET AUDIT PROGRESS/TIMELINE
   * ═════════════════════════════════════════════════════════
   */
  async getAuditProgress(
    auditId: number,
    companyId: number,
  ): Promise<any> {
    this.logger.log(`Getting audit progress: audit_id=${auditId}`);

    try {
      const audit = await this.auditRequestRepository.findOne({
        where: { id: auditId, company_id: companyId },
        relations: [
          'branch',
          'audit_schedule_row',
          'audit_schedule_row.lead_auditor',
          'audit_schedule_row.co_auditors',
          'audit_schedule_row.standards',
        ],
      });

      if (!audit) {
        throw new NotFoundException(`Audit ${auditId} not found`);
      }

      return {
        id: audit.id,
        audit_code: audit.audit_schedule_row?.audit_code || null,
        audit_stage: audit.audit_schedule_row?.audit_stage || null,
        company_id: audit.company_id,
        status: audit.status,
        schedule_status: audit.audit_schedule_row?.status ?? null,
        audit_type: audit.audit_schedule_row?.audit_type ?? null,
        proposed_date: audit.proposed_date,
        location: audit.location,
        mode: audit.mode,
        branch: audit.branch
          ? {
            id: audit.branch.id,
            branchName: audit.branch.branchName,
            mobile: audit.branch.mobile,
            contactPerson: audit.branch.contactPerson,
          }
          : null,
        lead_auditor: audit.audit_schedule_row?.lead_auditor
          ? {
            id: audit.audit_schedule_row.lead_auditor.id,
            firstName: audit.audit_schedule_row.lead_auditor.firstName,
            lastName: audit.audit_schedule_row.lead_auditor.lastName,
          }
          : null,
        co_auditors: (audit.audit_schedule_row?.co_auditors ?? []).map((u) => ({
          id: u.id,
          firstName: u.firstName,
          lastName: u.lastName,
        })),
        standards: (audit.audit_schedule_row?.standards ?? []).map((s) => ({
          id: s.id,
          name: s.name,
        })),
        stg1_audit_report: audit.audit_schedule_row?.stg1_audit_report || null,
        stg2_audit_report: audit.audit_schedule_row?.stg2_audit_report || null,
        attendance_doc: audit.audit_schedule_row?.attendance_doc || null,
        support_docs: audit.audit_schedule_row?.support_docs || null,
        // The fabricated Draft/Submitted/Scheduled timeline that used to be
        // here was removed - it was identical for every audit regardless of
        // real status.
      };
    } catch (error) {
      // ✅ FIX: Use error helper
      this.logger.error(
        `❌ Get audit progress failed: ${getErrorMessage(error)}`,
      );
      throw error;
    }
  }

  /**
   * ═════════════════════════════════════════════════════════
   * GET AUDIT REPORT
   * ═════════════════════════════════════════════════════════
   */
  async getAuditReport(
    auditId: number,
    companyId: number,
  ): Promise<any> {
    this.logger.log(`Getting audit report: audit_id=${auditId}`);

    try {
      const audit = await this.auditRequestRepository.findOne({
        where: { id: auditId, company_id: companyId },
        relations: ['branch', 'audit_schedule_row'],
      });

      if (!audit) {
        throw new NotFoundException(`Audit ${auditId} not found`);
      }

      // Get related schedule row for findings/recommendations
      const scheduleRow = audit.audit_schedule_row;

      // ✅ ACTUAL PROPERTIES: Use real entity property names
      return {
        id: audit.id,
        company_id: audit.company_id,
        status: audit.status,
        proposed_date: audit.proposed_date,          // ✅ CORRECT
        branch: audit.branch
          ? {
            id: audit.branch.id,
            branchName: audit.branch.branchName,   // ✅ CORRECT
            mobile: audit.branch.mobile,           // ✅ CORRECT
            contactPerson: audit.branch.contactPerson, // ✅ CORRECT
          }
          : null,
        standard_ids: audit.standard_ids,            // ✅ CORRECT (JSON array)
        report: {
          summary: 'Audit report summary',
          findings:
            scheduleRow && (scheduleRow as any).findings
              ? (scheduleRow as any).findings
              : [],
          recommendations:
            scheduleRow && (scheduleRow as any).recommendations
              ? (scheduleRow as any).recommendations
              : [],
          status: scheduleRow?.status || 'PENDING',
        },
      };
    } catch (error) {
      // ✅ FIX: Use error helper
      this.logger.error(
        `❌ Get audit report failed: ${getErrorMessage(error)}`,
      );
      throw error;
    }
  }

  /**
   * ═════════════════════════════════════════════════════════
   * GET CERTIFICATES
   * ═════════════════════════════════════════════════════════
   * Uses NewCertificate entity (not old Certificate)
   */
  async getCertificates(companyId: number): Promise<any[]> {
    this.logger.log(`Getting certificates: company_id=${companyId}`);

    try {
      // ✅ Query NewCertificate entity
      const certificates = await this.newCertificateRepository.find({
        where: { company: { id: companyId } },  // ✅ Filter by company relation
        relations: ['company', 'standard', 'previous_certificate'],
        order: { created_at: 'DESC' }  // NewCertificate uses snake_case!
      });

      if (!certificates || certificates.length === 0) {
        this.logger.warn(`No certificates found for company ${companyId}`);
        return [];
      }

      // ✅ FORMAT RESPONSE: Use NewCertificate property names
      return certificates.map((cert) => ({
        id: cert.id,
        certificate_no: cert.certificate_no,        // ✅ CORRECT property
        company_name_snapshot: cert.company_name_snapshot,
        address_snapshot: cert.address_snapshot,
        city: cert.city,
        country: cert.country,
        standard_name: cert.standard_name,
        standard_short: cert.standard_short,
        scope_of_work: cert.scope_of_work,
        cert_type: cert.cert_type,                  // ✅ INITIAL, SURVEILLANCE, RECERTIFICATION
        originally_registered: cert.originally_registered,
        issue_date: cert.issue_date,                // ✅ CORRECT property
        expire_date: cert.expire_date,              // ✅ CORRECT property
        surveillance_audit_due: cert.surveillance_audit_due,
        recertification_due: cert.recertification_due,
        qrcode_token: cert.qrcode_token,
        fingerprint: cert.fingerprint,
        verification_domain: cert.verification_domain,
        scan_pdf_url: cert.scan_pdf_url,            // ✅ The uploaded signed PDF
        scan_uploaded_at: cert.scan_uploaded_at,
        scan_uploaded_by: cert.scan_uploaded_by,
        status: cert.status,                        // ✅ pending_scan, active, superseded, expired, revoked, fake
        company: cert.company
          ? {
            id: cert.company.id,
            name: cert.company.name,
            mobile: cert.company.mobile,
            email: cert.company.email,
          }
          : null,
        standard: cert.standard
          ? {
            id: cert.standard.id,
            name: cert.standard.name,
          }
          : null,
        previous_certificate: cert.previous_certificate
          ? {
            id: cert.previous_certificate.id,
            certificate_no: cert.previous_certificate.certificate_no,
          }
          : null,
        created_by: cert.created_by,
        updated_by: cert.updated_by,
        created_at: cert.created_at,
        updated_at: cert.updated_at,
      }));
    } catch (error) {
      // ✅ FIX: Use error helper
      this.logger.error(
        `❌ Get certificates failed: ${getErrorMessage(error)}`,
      );
      throw error;
    }
  }

  async getNcs(companyId: number): Promise<any[]> {
    this.logger.log(`Getting NCs: company_id=${companyId}`);
    try {
      const ncs = await this.ncRepository.find({
        where: { company_id: companyId } as any,
        relations: ['entries'],
        order: { created_at: 'DESC' } as any,
      });
      return ncs.map((nc) => ({
        id: nc.id,
        audit_id: nc.audit_id,
        auditee_name: nc.auditee_name,
        audit_type: nc.audit_type,
        nc_type: nc.nc_type,
        status: nc.status,
        follow_up_date: nc.follow_up_date,
        due_date: nc.due_date,
        closed_at: nc.closed_at,
        created_at: nc.created_at,
        findings: (nc.entries || []).map((e) => ({
          id: e.id,
          nc_type: e.nc_type,
          ncr_statement: e.ncr_statement,
          criteria_clause: e.criteria_clause,
          corrective_action: e.corrective_action,
          status: e.status,
        })),
      }));
    } catch (error) {
      this.logger.error(`❌ Get NCs failed: ${getErrorMessage(error)}`);
      throw error;
    }
  }
  async openAuditReportFile(
    auditId: number,
    companyId: number,
    stage: 1 | 2,
  ): Promise<{ stream: Readable; size: number; mime: string; filename: string }> {
    const audit = await this.auditRequestRepository.findOne({
      where: { id: auditId, company_id: companyId },
      relations: ['audit_schedule_row'],
    });
    if (!audit) throw new NotFoundException(`Audit ${auditId} not found`);

    const row: any = audit.audit_schedule_row;
    const stored = String(
      (stage === 1 ? row?.stg1_audit_report : row?.stg2_audit_report) || '',
    ).trim();
    if (!stored) {
      throw new NotFoundException(`No stage-${stage} report available for this audit`);
    }

    // "new:stage1/160/FILE.pdf" → /…/storage/auditReport/stage1/160/FILE.pdf
    const ROOT =
      process.env.AUDIT_REPORT_ROOT ||
      '/var/www/scheme_certiifcation/backend/storage/auditReport';
    const rel = stored.replace(/^new:/i, '').replace(/^\/+/, '');
    const abs = normalize(join(ROOT, rel));
    if (!abs.startsWith(normalize(ROOT))) {
      throw new BadRequestException('Invalid file path');
    }
    if (!existsSync(abs)) {
      this.logger.error(`[report-file] not on disk: ${abs} (from "${stored}")`);
      throw new NotFoundException('Report file not found on server');
    }

    const ext = extname(abs).toLowerCase();
    const mime =
      ext === '.pdf' ? 'application/pdf'
        : ext === '.png' ? 'image/png'
          : ext === '.jpg' || ext === '.jpeg' ? 'image/jpeg'
            : ext === '.doc' ? 'application/msword'
              : ext === '.docx' ? 'application/vnd.openxmlformats-officedocument.wordprocessingml.document'
                : 'application/octet-stream';

    return {
      stream: createReadStream(abs),
      size: statSync(abs).size,
      mime,
      filename: basename(abs),
    };
  }
  /**
   * ═════════════════════════════════════════════════════════
   * HELPER: Get audit request number (formatted)
   * ═════════════════════════════════════════════════════════
   */
  private getAuditRequestNumber(auditId: number): string {
    // ✅ CORRECT: Audit request number is formatted as AUD-{id}
    // NOT from a dedicated audit_request_no field
    return `AUD-${String(auditId).padStart(5, '0')}`;
  }
}