import { Injectable } from '@nestjs/common';
import * as sql from 'mssql';
import { MssqlService } from 'src/infra/mssql/mssql.service';
import { escapeXml } from 'src/common/utils/xml.util';
import { AssemblyMonitoringRepository } from './assembly-monitoring.repository';
import {
  AssemblyMonitoringDailyQueryDto,
  AssemblyMonitoringProcessesQueryDto,
  AssemblyMonitoringQueryWithFactUnit,
  AssemblyMonitoringRealtimeQueryDto,
  AssemblyMonitoringUnitDetailQueryWithDefaults,
  AssemblyMonitoringUnitListQueryWithDefaults,
} from './dto/request';
import { AssemblyMonitoringProcColumnRowDbDto } from './dto/internal/proc-column-row.db.dto';
import { AssemblyMonitoringUnitProcessStatusDbDto } from './dto/internal/unit-process-status.db.dto';
import { AssemblyMonitoringUnitDetailDbDto } from './dto/internal/unit-detail.db.dto';
import { AssemblyMonitoringUnitListResponseDto } from './dto/response/unit-list.response.dto';
import { PinoLogger } from 'nestjs-pino';
import { toSafeString } from 'src/common/utils/safe-parse.util';
import { AssemblyMonitoringInactiveWorkerDbDto } from './dto/internal/inactive-worker.db.dto';
import { AssemblyMonitoringRealtimeDbResultDto } from './dto/internal/realtime-result.db.dto';

@Injectable()
export class AssemblyMonitoringSpRepository extends AssemblyMonitoringRepository {
  constructor(
    private readonly mssql: MssqlService,
    private readonly logger: PinoLogger,
  ) {
    super();
    this.logger.setContext(AssemblyMonitoringSpRepository.name);
  }

  /**
   * XML 생성
   */
  private buildRealtimeXml(factUnit: number): string {
    return (
      `<ROOT><DataBlock1>` +
      `<WorkingTag />` +
      `<IDX_NO>1</IDX_NO>` +
      `<Status>0</Status>` +
      `<DataSeq>1</DataSeq>` +
      `<Selected>1</Selected>` +
      `<TABLE_NAME>DataBlock1</TABLE_NAME>` +
      `<IsChangedMst>0</IsChangedMst>` +
      `<FactUnit>${escapeXml(factUnit)}</FactUnit>` +
      `</DataBlock1></ROOT>`
    );
  }

  private buildDailyXml(factUnit: number, workingDate: string): string {
    return (
      `<ROOT><DataBlock1>` +
      `<WorkingTag />` +
      `<IDX_NO>1</IDX_NO>` +
      `<Status>0</Status>` +
      `<DataSeq>1</DataSeq>` +
      `<Selected>1</Selected>` +
      `<TABLE_NAME>DataBlock1</TABLE_NAME>` +
      `<IsChangedMst>0</IsChangedMst>` +
      `<FactUnit>${escapeXml(factUnit)}</FactUnit>` +
      `<WorkingDate>${escapeXml(workingDate)}</WorkingDate>` +
      `</DataBlock1></ROOT>`
    );
  }

  private buildProcessStatusXml(factUnit: number): string {
    return (
      `<ROOT><DataBlock1>` +
      `<WorkingTag />` +
      `<IDX_NO>1</IDX_NO>` +
      `<Status>0</Status>` +
      `<DataSeq>1</DataSeq>` +
      `<Selected>1</Selected>` +
      `<TABLE_NAME>DataBlock1</TABLE_NAME>` +
      `<IsChangedMst>0</IsChangedMst>` +
      `<FactUnit>${escapeXml(factUnit)}</FactUnit>` +
      `</DataBlock1></ROOT>`
    );
  }

  private buildUnitListXml(
    params: AssemblyMonitoringUnitListQueryWithDefaults,
  ): string {
    const { prodPlanMonth, factUnit, modelSeq, serialNo } = params;

    return (
      `<ROOT><DataBlock1>` +
      `<WorkingTag />` +
      `<IDX_NO>1</IDX_NO>` +
      `<DataSeq>1</DataSeq>` +
      `<Status>0</Status>` +
      `<Selected>1</Selected>` +
      `<TABLE_NAME>DataBlock1</TABLE_NAME>` +
      `<ProdPlanMonth>${escapeXml(prodPlanMonth)}</ProdPlanMonth>` +
      `<FactUnit>${escapeXml(factUnit)}</FactUnit>` +
      `<ModelSeq>${escapeXml(modelSeq)}</ModelSeq>` +
      `<SerialNo>${escapeXml(serialNo)}</SerialNo>` +
      `</DataBlock1></ROOT>`
    );
  }

  private buildUnitDetailXml(
    params: AssemblyMonitoringUnitDetailQueryWithDefaults,
  ): string {
    const {
      prodPlanSeq,
      modelSeq,
      serialNo,
      specSeq,
      factUnit,
      workingEndDate,
    } = params;

    return (
      `<ROOT><DataBlock1>` +
      `<WorkingTag />` +
      `<IDX_NO>1</IDX_NO>` +
      `<DataSeq>1</DataSeq>` +
      `<Status>0</Status>` +
      `<Selected>0</Selected>` +
      `<ModelSeq>${escapeXml(modelSeq)}</ModelSeq>` +
      `<SerialNo>${escapeXml(serialNo)}</SerialNo>` +
      `<ProdPlanSeq>${escapeXml(prodPlanSeq)}</ProdPlanSeq>` +
      `<SpecSeq>${escapeXml(specSeq)}</SpecSeq>` +
      `<FactUnit>${escapeXml(factUnit)}</FactUnit>` +
      (workingEndDate
        ? `<WorkingENDDate>${escapeXml(workingEndDate)}</WorkingENDDate>`
        : '') +
      `<TABLE_NAME>DataBlock1</TABLE_NAME>` +
      `</DataBlock1></ROOT>`
    );
  }

  /**
   * 실시간 모니터링 데이터 조회 (Raw 데이터 반환)
   */
  async findRealtimeData(
    req: AssemblyMonitoringQueryWithFactUnit<AssemblyMonitoringRealtimeQueryDto>,
  ): Promise<AssemblyMonitoringRealtimeDbResultDto> {
    const factUnit = req.factUnit;

    const xml = this.buildRealtimeXml(factUnit);

    this.logger.debug(
      {
        event: 'db.sp.call',
        operation: 'cp_monitoring_realtime',
        sp: 'evdm_TPDPOPLiveWorkStatusQuery',
        factUnit,
      },
      'call sp',
    );

    const request = this.mssql.request();

    request.input('xmlDocument', sql.NVarChar(sql.MAX), xml);
    request.input('xmlFlags', sql.Int, 2);
    request.input('ServiceSeq', sql.Int, 2003284);
    request.input('WorkingTag', sql.NVarChar(50), '');
    request.input('CompanySeq', sql.Int, 1);
    request.input('LanguageSeq', sql.Int, 1);
    request.input('UserSeq', sql.Int, 603);
    request.input('PgmSeq', sql.Int, 2003817);

    const result = await request.execute('evdm_TPDPOPLiveWorkStatusQuery');
    const recordsets = result.recordsets ?? [];
    const rows = (recordsets[0] ??
      result.recordset ??
      []) as AssemblyMonitoringProcColumnRowDbDto[];
    const inactiveRows = (recordsets[1] ??
      []) as AssemblyMonitoringInactiveWorkerDbDto[];
    const inactiveWorkers = inactiveRows.map((row) => {
      const empSeq = Number(row.EmpSeq);
      return {
        EmpSeq: Number.isFinite(empSeq) ? empSeq : 0,
        EmpName: toSafeString(row.EmpName),
        DeptName: toSafeString(row.DeptName),
      };
    });

    this.logger.debug(
      {
        event: 'db.sp.result',
        operation: 'cp_monitoring_realtime',
        rowCount: rows.length,
        inactiveWorkerCount: inactiveWorkers.length,
      },
      'sp result',
    );

    return { rows, inactiveWorkers };
  }

  async findDailyData(
    req: AssemblyMonitoringQueryWithFactUnit<AssemblyMonitoringDailyQueryDto>,
  ): Promise<AssemblyMonitoringProcColumnRowDbDto[]> {
    const factUnit = req.factUnit;
    const workingDate = req.date;
    this.logger.debug(
      {
        event: 'db.sp.param',
        operation: 'cp_monitoring_daily',
        workingDate,
      },
      'working date',
    );

    const xml = this.buildDailyXml(factUnit, workingDate);

    this.logger.debug(
      {
        event: 'db.sp.call',
        operation: 'cp_monitoring_daily',
        sp: 'evdm_TPDPOPDailyWorkProcDailyWorkingQuery',
        factUnit,
        workingDate,
      },
      'call sp',
    );

    const request = this.mssql.request();

    request.input('xmlDocument', sql.NVarChar(sql.MAX), xml);
    request.input('xmlFlags', sql.Int, 2);
    request.input('ServiceSeq', sql.Int, 0);
    request.input('WorkingTag', sql.NVarChar(50), '');
    request.input('CompanySeq', sql.Int, 1);
    request.input('LanguageSeq', sql.Int, 1);
    request.input('UserSeq', sql.Int, 0);
    request.input('PgmSeq', sql.Int, 0);

    const result = await request.execute(
      'evdm_TPDPOPDailyWorkProcDailyWorkingQuery',
    );
    const rows = (result.recordsets?.[0] ??
      result.recordset ??
      []) as AssemblyMonitoringProcColumnRowDbDto[];

    this.logger.debug(
      {
        event: 'db.sp.result',
        operation: 'cp_monitoring_daily',
        rowCount: rows.length,
      },
      'sp result',
    );

    return rows;
  }

  /**
   * 공정 상태 조회
   */
  async findProcesses(
    req: AssemblyMonitoringQueryWithFactUnit<AssemblyMonitoringProcessesQueryDto>,
  ): Promise<AssemblyMonitoringUnitProcessStatusDbDto[]> {
    const factUnit = req.factUnit;
    const xml = this.buildProcessStatusXml(factUnit);

    this.logger.debug(
      {
        event: 'db.sp.call',
        operation: 'cp_process_status',
        sp: 'evdm_TPDPOPUnitWorkStatusMonQuery',
        factUnit,
      },
      'call sp',
    );

    const request = this.mssql.request();

    request.input('xmlDocument', sql.NVarChar(sql.MAX), xml);
    request.input('xmlFlags', sql.Int, 2);
    request.input('ServiceSeq', sql.Int, 2003284);
    request.input('WorkingTag', sql.NVarChar(50), '');
    request.input('CompanySeq', sql.Int, 1);
    request.input('LanguageSeq', sql.Int, 1);
    request.input('UserSeq', sql.Int, 603);
    request.input('PgmSeq', sql.Int, 2003814);

    // SET LANGUAGE와 프로시저를 같은 batch로 실행 (날짜 변환 오류 방지)
    const query = `
      SET LANGUAGE Korean;
      exec evdm_TPDPOPUnitWorkStatusMonQuery
        @xmlDocument=@xmlDocument,
        @xmlFlags=@xmlFlags,
        @ServiceSeq=@ServiceSeq,
        @WorkingTag=@WorkingTag,
        @CompanySeq=@CompanySeq,
        @LanguageSeq=@LanguageSeq,
        @UserSeq=@UserSeq,
        @PgmSeq=@PgmSeq
    `;

    const result = await request.query(query);
    const rows = (result.recordsets?.[0] ??
      result.recordset ??
      []) as AssemblyMonitoringUnitProcessStatusDbDto[];

    this.logger.debug(
      {
        event: 'db.sp.result',
        operation: 'cp_process_status',
        rowCount: rows.length,
      },
      'sp result',
    );

    return rows;
  }

  /**
   * 대기/제공 공정 상태 조회
   */
  async findWaitingProcesses(
    req: AssemblyMonitoringQueryWithFactUnit<AssemblyMonitoringProcessesQueryDto>,
  ): Promise<AssemblyMonitoringUnitProcessStatusDbDto[]> {
    const factUnit = req.factUnit;
    const xml = this.buildProcessStatusXml(factUnit);

    this.logger.debug(
      {
        event: 'db.sp.call',
        operation: 'cp_process_waiting',
        sp: 'evdm_TPDPOPUnitWorkDelayMonQuery',
        factUnit,
      },
      'call sp',
    );

    const request = this.mssql.request();

    request.input('xmlDocument', sql.NVarChar(sql.MAX), xml);
    request.input('xmlFlags', sql.Int, 2);
    request.input('ServiceSeq', sql.Int, 2003284);
    request.input('WorkingTag', sql.NVarChar(50), '');
    request.input('CompanySeq', sql.Int, 1);
    request.input('LanguageSeq', sql.Int, 1);
    request.input('UserSeq', sql.Int, 603);
    request.input('PgmSeq', sql.Int, 2003814);

    const query = `
      SET LANGUAGE Korean;
      exec evdm_TPDPOPUnitWorkDelayMonQuery
        @xmlDocument=@xmlDocument,
        @xmlFlags=@xmlFlags,
        @ServiceSeq=@ServiceSeq,
        @WorkingTag=@WorkingTag,
        @CompanySeq=@CompanySeq,
        @LanguageSeq=@LanguageSeq,
        @UserSeq=@UserSeq,
        @PgmSeq=@PgmSeq
    `;

    const result = await request.query(query);
    const rows = (result.recordsets?.[0] ??
      result.recordset ??
      []) as AssemblyMonitoringUnitProcessStatusDbDto[];

    this.logger.debug(
      {
        event: 'db.sp.result',
        operation: 'cp_process_waiting',
        rowCount: rows.length,
      },
      'sp result',
    );

    return rows;
  }

  /**
   * 호기 목록 조회
   */
  async findUnitList(
    req: AssemblyMonitoringUnitListQueryWithDefaults,
  ): Promise<AssemblyMonitoringUnitListResponseDto[]> {
    const xml = this.buildUnitListXml(req);

    this.logger.debug(
      {
        event: 'db.sp.call',
        operation: 'cp_unit_list',
        sp: 'evdm_SPDPOPModelSerialDailyQuery',
        prodPlanMonth: req.prodPlanMonth,
        factUnit: req.factUnit,
      },
      'call sp',
    );

    const request = this.mssql.request();

    request.input('xmlDocument', sql.NVarChar(sql.MAX), xml);
    request.input('xmlFlags', sql.Int, 2);
    request.input('ServiceSeq', sql.Int, 2003267);
    request.input('WorkingTag', sql.NVarChar(50), '');
    request.input('CompanySeq', sql.Int, 1);
    request.input('LanguageSeq', sql.Int, 1);
    request.input('UserSeq', sql.Int, 0);
    request.input('PgmSeq', sql.Int, 2003796);

    const result = await request.execute('evdm_SPDPOPModelSerialDailyQuery');
    const rows = (result.recordset ?? []) as Array<Record<string, unknown>>;

    this.logger.debug(
      {
        event: 'db.sp.result',
        operation: 'cp_unit_list',
        rowCount: rows.length,
      },
      'sp result',
    );

    const filteredRows = rows.filter((rs) => {
      const modelName = toSafeString(rs.ModelName ?? '').toUpperCase();
      return !modelName.includes('COMMON');
    });

    return filteredRows.map((rs, index: number) => {
      const dto: AssemblyMonitoringUnitListResponseDto = {
        prodPlanSeq: Number(rs.ProdPlanSeq),
        prodPlanNo: toSafeString(rs.ProdPlanNo),
        modelSeq: Number(rs.ModelSeq),
        modelName: toSafeString(rs.ModelName),
        serialNo: toSafeString(rs.SerialNo),
        factUnit: Number(rs.FactUnit),
        prodPlanMonth: req.prodPlanMonth,
        recentWorkingDate: rs.RecentWorkingDate
          ? toSafeString(rs.RecentWorkingDate)
          : null,
        specSeq: rs.SpecSeq ? Number(rs.SpecSeq) : null,
      };

      this.logger.debug(
        {
          event: 'db.sp.map_row',
          operation: 'cp_unit_list',
          index,
          prodPlanSeq: dto.prodPlanSeq,
          modelName: dto.modelName,
          serialNo: dto.serialNo,
        },
        'row',
      );

      return dto;
    });
  }

  /**
   * 특정 호기 공정 상세 조회
   */
  async findUnitDetail(
    req: AssemblyMonitoringUnitDetailQueryWithDefaults,
  ): Promise<AssemblyMonitoringUnitDetailDbDto[]> {
    const xml = this.buildUnitDetailXml(req);

    this.logger.debug(
      {
        event: 'db.sp.call',
        operation: 'cp_unit_detail',
        sp: 'evdm_SPDPOPModelSerialProcTrackQuery',
        prodPlanSeq: req.prodPlanSeq,
        modelSeq: req.modelSeq,
        serialNo: req.serialNo,
      },
      'call sp',
    );

    const request = this.mssql.request();

    request.input('xmlDocument', sql.NVarChar(sql.MAX), xml);
    request.input('xmlFlags', sql.Int, 2);
    request.input('ServiceSeq', sql.Int, 2003267);
    request.input('WorkingTag', sql.NVarChar(50), '');
    request.input('CompanySeq', sql.Int, 1);
    request.input('LanguageSeq', sql.Int, 1);
    request.input('UserSeq', sql.Int, 0);
    request.input('PgmSeq', sql.Int, 2003796);

    const result = await request.execute(
      'evdm_SPDPOPModelSerialProcTrackQuery',
    );
    const rows = (result.recordset ??
      []) as AssemblyMonitoringUnitDetailDbDto[];

    this.logger.debug(
      {
        event: 'db.sp.result',
        operation: 'cp_unit_detail',
        rowCount: rows.length,
      },
      'sp result',
    );

    return rows;
  }
}