/*
 * Copyright 2006-2026 HighRadius Corporation
 */
package com.highradius.daggr.pdf.parser;

import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.logging.log4j.ThreadContext;
import org.hibernate.Session;

import com.highradius.common.emailapi.model.SendEmailRequest;
import com.highradius.common.emailapi.util.HRCEmailUtil;
import com.highradius.common.session.SessionUtil;
import com.highradius.common.util.HRCLog;
import com.highradius.common.util.HRCLogFactory;
import com.highradius.core.utils.HRCCStandardCSVParser; // Standardized framework CSV engine
import com.highradius.core.utils.HRCCSVParser;
import com.highradius.daggr.parser.CustomPDFParser;
import com.highradius.daggr.parser.DaggrMetaData;
import com.highradius.util.FileUtility;
import com.highradius.util.cpa.CpaFileUtil;
import com.highradius.util.cpa.StringConstants;
import com.scraper.agents.base.Util;
import com.scraper.cpa.AgentProperties;
import com.scraper.cpa.BaseHelper;
import com.scraper.sql.cpa.HRPreparedStatement;

/**
 * @author HighRadius Custom Monitoring Integration
 * * ClaimInvoiceAdvantageReportt manages comprehensive end-to-end reconciliation between internal
 * HighRadius data-extraction processes and downstream client SOAR validation states.
 */
public class ClaimInvoiceAdvantageReportt extends CustomPDFParser {
	private static final HRCLog LOG = HRCLogFactory.getLog(ClaimInvoiceAdvantageReportt.class);

	private static final String ZIP_FILENAME_ATTR = "ZIPFILENAME";
	private static final String FILE_ROW_NUMBER_ATTR = "FILEROWNUMBER";
	private static final String MASTER_BASE_NAME = "Advantage_Reconciliation_Master_";

	private List<String> inputHeader = null;
	private Map<String, ReconciliationRow> reconciliationMap = null;

	private String targetBucketName = "gcshrc-cpa-prod";
	private String targetSourceFolder = "Email Inbox";
	
	// Runtime context captured to preserve downstream pipeline execution constants
	private String generatedMasterLocalPath = "";
	private String calculatedFileNameOnly = "";

	@Override
	public String parseFileV2(DaggrMetaData metaData, Map<String, String> emailDetailsMap) {
		String parsingStatus = STATUS_FAILED_TO_PARSE;
		LOG.info("Initializing V2 reconciliation framework processing clock.");
		
		reconciliationMap = new HashMap<>();
		String attachmentName = emailDetailsMap.get("ATTACHMENT");
		
		if (StringUtils.isNotBlank(attachmentName)) {
			attachmentName = attachmentName.replace("\\", "/");
		}
		
		File todaySoarReportFile = CpaFileUtil.getInstance().getFile(metaData.getAccountId(), attachmentName, null);

		HRPreparedStatement hpstmtExtracts = null;
		Connection con = null;
		HRCCSVParser errorReportReader = null;

		try {
			if (todaySoarReportFile == null || !todaySoarReportFile.exists()) {
				LOG.error("InvoiceReconciliationAgent: Today's incoming SOAR report file is missing.");
				return STATUS_FAILED_TO_PARSE;
			}

			Session session = BaseHelper.getDBSession();
			con = SessionUtil.getDBConnection(session);
			int accountId = metaData.getAccountId();

			// PHASE 1: Load Yesterday's Master Output File from specific Sunday folder partition
			loadYesterdayMasterFile(metaData, attachmentName);

			// PHASE 2: Fetch today's database successes (24-hour cycle window)
			updateTodaySentClaims(con, accountId);

			// PHASE 4 [SEQUENCING FIX]: Fetch today's new internal HRC failures first
			updateTodayInternalFailures(con, accountId);

			// PHASE 3: Read today's incoming SOAR report file and intercept errors (Overrides status last)
			errorReportReader = new HRCCSVParser(new FileReader(todaySoarReportFile));
			String[] lines;
			boolean startLine = false;
			int zipFileIdx = -1;
			int rowNumIdx = -1;

			while ((lines = errorReportReader.getLine()) != null) {
				if (lines.length == 0 || StringUtils.isBlank(lines[0])) {
					continue;
				}

				if (startLine) {
					String zipFilename = (zipFileIdx != -1 && zipFileIdx < lines.length) ? lines[zipFileIdx].trim() : "";
					String fileRowNumberStr = (rowNumIdx != -1 && rowNumIdx < lines.length) ? lines[rowNumIdx].trim() : "";

					if (StringUtils.isNotBlank(zipFilename) && StringUtils.isNotBlank(fileRowNumberStr)) {
						try {
							int targetRowNumber = Integer.parseInt(fileRowNumberStr.trim());
							String csvFileName = zipFilename.replaceAll("(?i)\\.zip", ".csv");

							String extractsSql = "select file_paths from arpa_extracts_details where name like ? or file_paths like ? order by extracts_details_id desc";
							hpstmtExtracts = new HRPreparedStatement(con, extractsSql);
							hpstmtExtracts.setString(1, "%" + csvFileName + "%");
							hpstmtExtracts.setString(2, "%" + csvFileName + "%");
							ResultSet rsExtracts = hpstmtExtracts.executeQuery();

							String correctS3Path = null;
							if (rsExtracts.next()) {
								String filePathsJson = rsExtracts.getString("file_paths");
								if (StringUtils.isNotBlank(filePathsJson)) {
									String[] jsonObjects = filePathsJson.split("\\}");
									for (String obj : jsonObjects) {
										if (obj.contains(csvFileName)) {
											String targetToken = "\"s3Path\":";
											int tokenIdx = obj.indexOf(targetToken);
											if (tokenIdx == -1) {
												targetToken = "\"s3Path\" :";
												tokenIdx = obj.indexOf(targetToken);
											}
											if (tokenIdx != -1) {
												int firstQuote = obj.indexOf("\"", tokenIdx + targetToken.length());
												int secondQuote = obj.indexOf("\"", firstQuote + 1);
												if (firstQuote != -1 && secondQuote != -1) {
													correctS3Path = obj.substring(firstQuote + 1, secondQuote).trim();
													break;
												}
											}
										}
									}
								}
							}
							Util.close(null, hpstmtExtracts, rsExtracts);

							if (StringUtils.isNotBlank(correctS3Path)) {
								File targetedSourceFile = CpaFileUtil.getInstance().getFile(accountId, correctS3Path, null);
								if (targetedSourceFile != null && targetedSourceFile.exists()) {
									interceptAndOverrideSoarErrors(targetedSourceFile, targetRowNumber, csvFileName, zipFilename);
								}
							}
						} catch (Exception ex) {
							LOG.error("Error processing row line pointer context: " + ex.getMessage(), ex);
						}
					}
				} else {
					if (Arrays.asList(lines).contains("FileRowNumber") || Arrays.asList(lines).contains("ZipFilename") || Arrays.asList(lines).contains("CsvFileName")) {
						inputHeader = new ArrayList<>();
						for (String headerToken : lines) {
							inputHeader.add(headerToken.replace(" ", "").toUpperCase());
						}
						zipFileIdx = inputHeader.indexOf(ZIP_FILENAME_ATTR);
						rowNumIdx = inputHeader.indexOf(FILE_ROW_NUMBER_ATTR);
						startLine = true;
					}
				}
			}

			// PHASE 4B: Hydrate descriptive parameters dynamically from customer_claim table
			enrichMetadataFromCustomerClaims(con, accountId);

			// PHASE 5: Save state parameters into today's suffix filename inside the weekly Sunday folder
			saveUpdatedMasterFile(metaData, attachmentName);

			// PHASE 6: Compile HTML body and trigger secure attachment email transfer pipeline
			String reportSubjectName = "Advantage_Claims_Reconciliation_Matrix_Report";
			String recipientEmailOverride = com.scraper.cpa.AgentProperties.getProperty("CPA_AGENT.monitor.Notifier.toAddress");
			if (StringUtils.isBlank(recipientEmailOverride)) {
				recipientEmailOverride = "cpa.notifications@highradius.com";
			}
			
			attachFileToEmail(this.generatedMasterLocalPath, reportSubjectName, recipientEmailOverride, accountId);

			parsingStatus = STATUS_PARSED;

		} catch (Exception e) {
			LOG.error("InvoiceReconciliationAgent: Global framework loop execution failed: " + e.getMessage(), e);
			parsingStatus = STATUS_FAILED_TO_PARSE;
		} finally {
			if (errorReportReader != null) {
				try { errorReportReader.close(); } catch (Exception ignored) {}
			}
		}
		return parsingStatus;
	}

	/**
	 * Dynamically parses the sequence index integer from a filename layout string.
	 * e.g., "Deductions-20260608T043013Z-6.zip" -> returns "6"
	 */
	private String extractSequenceFromFilename(String filename) {
		if (StringUtils.isBlank(filename)) return "N/A";
		try {
			int lastHyphen = filename.lastIndexOf('-');
			int lastDot = filename.lastIndexOf('.');
			if (lastHyphen != -1 && lastDot != -1 && lastHyphen < lastDot) {
				String seq = filename.substring(lastHyphen + 1, lastDot).trim();
				if (StringUtils.isNumeric(seq)) {
					return seq;
				}
			}
		} catch (Exception e) {
			LOG.error("Failed to parse sequence from filename: " + filename);
		}
		return "N/A";
	}

	/**
	 * PHASE 1: Sunday Partition Lookback Loader Engine
	 */
	private void loadYesterdayMasterFile(DaggrMetaData metaData, String attachmentName) {
		LOG.debug("InvoiceReconciliationAgent: Commencing execution clock calendar tracking.");
		String yesterdayMasterCloudPath = null;

		try {
			String cleanName = attachmentName.replace("\\", "/");
			String[] rawParts = cleanName.split("/");
			List<String> tokens = new ArrayList<>();
			for (String part : rawParts) {
				if (StringUtils.isNotBlank(part)) tokens.add(part.trim());
			}
			
			if (tokens.size() >= 6) {
				this.targetBucketName = tokens.get(0);  
				this.targetSourceFolder = tokens.get(5);  
			}
			
			Date trueCurrentDate = new Date();
			Calendar calYesterday = Calendar.getInstance();
			calYesterday.setTime(trueCurrentDate);
			int dayOfWeek = calYesterday.get(Calendar.DAY_OF_WEEK);
			
			if (dayOfWeek == Calendar.MONDAY) {
				calYesterday.add(Calendar.DATE, -3); 
			} else if (dayOfWeek == Calendar.SUNDAY) {
				calYesterday.add(Calendar.DATE, -2); 
			} else {
				calYesterday.add(Calendar.DATE, -1); 
			}
			
			Date yesterdayFileDate = calYesterday.getTime();
			String yesterdayDateSuffixStr = new SimpleDateFormat("yyyyMMdd").format(yesterdayFileDate);
			String yesterdayFileName = MASTER_BASE_NAME + yesterdayDateSuffixStr + ".csv";
			
			Calendar calYesterdaySunday = Calendar.getInstance();
			calYesterdaySunday.setTime(yesterdayFileDate);
			while (calYesterdaySunday.get(Calendar.DAY_OF_WEEK) != Calendar.SUNDAY) {
				calYesterdaySunday.add(Calendar.DATE, -1);
			}
			String yesterdaySundayFolderStr = new SimpleDateFormat("yyyy/MM/dd").format(calYesterdaySunday.getTime());
			
			yesterdayMasterCloudPath = "/" + this.targetBucketName + "/" + metaData.getAccountId() + "/" 
					+ yesterdaySundayFolderStr + "/" + this.targetSourceFolder + "/" + yesterdayFileName;
					
		} catch (Exception e) {
			LOG.error("InvoiceReconciliationAgent: Lookback path execution clock math failed: " + e.getMessage());
		}

		if (StringUtils.isBlank(yesterdayMasterCloudPath)) {
			LOG.warn("InvoiceReconciliationAgent: Lookback target empty. Proceeding with clean session.");
			return;
		}

		File masterFile = null;
		try {
			LOG.info("InvoiceReconciliationAgent: Requesting file from GCP folder path: " + yesterdayMasterCloudPath);
			masterFile = CpaFileUtil.getInstance().getFile(metaData.getAccountId(), yesterdayMasterCloudPath, null);
		} catch (Exception cloudException) {
			LOG.warn("InvoiceReconciliationAgent: Previous log ledger not found inside this Sunday bucket partition. Starting fresh.");
			return; 
		}

		if (masterFile == null || !masterFile.exists()) {
			LOG.info("InvoiceReconciliationAgent: Ledger file missing from disk mount path. Initializing blank layout.");
			return;
		}

		HRCCSVParser reader = null;
		try {
			reader = new HRCCSVParser(new FileReader(masterFile));
			String[] lines;
			boolean headerSkipped = false;
			while ((lines = reader.getLine()) != null) {
				if (lines.length == 0 || StringUtils.isBlank(lines[0])) continue;
				if (!headerSkipped) {
					headerSkipped = true;
					continue;
				}

				ReconciliationRow row = new ReconciliationRow();
				row.invoiceNumber = lines[0].trim();
				if (lines.length > 1) row.contractNumber = lines[1].trim();
				if (lines.length > 2) row.clientName = lines[2].trim();
				if (lines.length > 3) row.clientId = lines[3].trim();
				if (lines.length > 4) row.customerName = lines[4].trim();
				if (lines.length > 5) row.lastUpdatedOn = lines[5].trim();
				if (lines.length > 6) row.hrcStatus = lines[6].trim();
				if (lines.length > 7) row.soarStatus = lines[7].trim();
				
				if (lines.length > 8) row.firstHrFileDate = lines[8].trim();
				if (lines.length > 9) row.firstHrFileSequence = lines[9].trim();
				if (lines.length > 10) row.firstHrFileCreatedUtcDate = lines[10].trim();
				if (lines.length > 11) row.firstHrCsvFileName = lines[11].trim();
				if (lines.length > 12) row.firstHrZipFilename = lines[12].trim();
				if (lines.length > 13) row.firstHrFileRowNumber = lines[13].trim();
				
				if (lines.length > 14) row.latestHrFileDate = lines[14].trim();
				if (lines.length > 15) row.latestHrFileSequence = lines[15].trim();
				if (lines.length > 16) row.latestHrFileCreatedUtcDate = lines[16].trim();
				if (lines.length > 17) row.latestHrCsvFileName = lines[17].trim();
				if (lines.length > 18) row.latestHrZipFilename = lines[18].trim();
				if (lines.length > 19) row.latestHrFileRowNumber = lines[19].trim();
				
				if (lines.length > 20) row.soarLatestHrFileDate = lines[20].trim();
				if (lines.length > 21) row.soarLatestHrFileSequence = lines[21].trim();
				if (lines.length > 22) row.soarLatestHrFileCreatedUtcDate = lines[22].trim();
				if (lines.length > 23) row.soarLatestHrCsvFileName = lines[23].trim();
				if (lines.length > 24) row.soarLatestHrZipFilename = lines[24].trim();
				if (lines.length > 25) row.soarLatestHrFileRowNumber = lines[25].trim();
				
				reconciliationMap.put(row.invoiceNumber, row);
			}
			LOG.info("InvoiceReconciliationAgent: Hydration complete. Restored " + reconciliationMap.size() + " records.");
		} catch (Exception e) {
			LOG.error("InvoiceReconciliationAgent: Error parsing history data: " + e.getMessage(), e);
		} finally {
			if (reader != null) {
				try { reader.close(); } catch (Exception ignored) {}
			}
		}
	}

	/**
	 * PHASE 2: Database Success Scan Engine Match Layout Profile Columns
	 */
	private void updateTodaySentClaims(Connection con, int accountId) throws Exception {
		String sentClaimsQuery = "select cb.claim_number, cc.customer_name, cb.filename, cb.exported_date "
				+ "from customer_claim_export_callback cb "
				+ "join customer_claim cc on cc.customer_claim_id = cb.customer_claim_id "
				+ "where cc.account_id = ? and cb.exported_date between DATE_SUB(NOW(), INTERVAL 24 HOUR) and NOW()";
		
		HRPreparedStatement hpstmt = null;
		ResultSet rs = null;
		try {
			hpstmt = new HRPreparedStatement(con, sentClaimsQuery);
			hpstmt.setInt(1, accountId);
			rs = hpstmt.executeQuery();
			String activeTimestampStr = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());

			while (rs.next()) {
				String claimNum = rs.getString("claim_number");
				if (StringUtils.isNotBlank(claimNum)) {
					ReconciliationRow row = reconciliationMap.get(claimNum);
					String zipName = rs.getString("filename");
					String inferredCsvName = StringUtils.isNotBlank(zipName) ? zipName.replaceAll("(?i)\\.zip", ".csv") : "N/A";
					String fileDateStr = rs.getTimestamp("exported_date") != null ? rs.getTimestamp("exported_date").toString() : activeTimestampStr;
					String dynamicSequence = extractSequenceFromFilename(zipName);

					if (row == null) {
						row = new ReconciliationRow();
						row.invoiceNumber = claimNum;
						row.customerName = rs.getString("customer_name");
						row.firstHrFileDate = fileDateStr;
						row.firstHrFileCreatedUtcDate = fileDateStr;
						row.firstHrZipFilename = zipName;
						row.firstHrCsvFileName = inferredCsvName;
						row.firstHrFileSequence = dynamicSequence;
						row.firstHrFileRowNumber = "N/A";
					}
					row.hrcStatus = "Success";
					row.soarStatus = "Sent to SOAR";
					row.latestHrFileDate = fileDateStr;
					row.latestHrFileCreatedUtcDate = fileDateStr;
					row.latestHrZipFilename = zipName;
					row.latestHrCsvFileName = inferredCsvName;
					row.latestHrFileSequence = dynamicSequence;
					row.latestHrFileRowNumber = "N/A";
					row.lastUpdatedOn = activeTimestampStr;
					
					reconciliationMap.put(claimNum, row);
				}
			}
		} finally {
			Util.close(null, hpstmt, rs);
		}
	}

	/**
	 * PHASE 3: Intercept Downstream Errors and Extract Dynamic Details
	 * [STABILITY FIX]: Counts all lines to avoid SOAR layout drift, using fallback identifiers if invoice number is missing.
	 */
	private void interceptAndOverrideSoarErrors(File targetedSourceFile, int targetRowNumber, String csvName, String zipName) throws Exception {
		HRCCSVParser cloudCsvReader = null;
		try {
			cloudCsvReader = new HRCCSVParser(new FileReader(targetedSourceFile));
			String[] cloudLines;
			int currentCloudRow = 0;
			boolean cloudHeaderProcessed = false;
			int invoiceIdx = -1;
			int dateIdx = -1;
			int contractIdx = -1;
			int customerIdx = -1;

			while ((cloudLines = cloudCsvReader.getLine()) != null) {
				// Counts all entries sequentially to align perfectly with downstream SOAR tracking metrics
				if (cloudLines.length == 0) continue;

				if (!cloudHeaderProcessed) {
					int exactInvoiceIdx = -1; int looseInvoiceIdx = -1;
					int exactDateIdx = -1; int looseDateIdx = -1;

					for (int i = 0; i < cloudLines.length; i++) {
						String h = cloudLines[i].replace(" ", "").replace("_", "").toUpperCase();
						if (h.equals("INVOICENUMBER") || h.equals("CLAIMNUMBER")) {
							exactInvoiceIdx = i;
						} else if (h.contains("INVOICENUMBER") || h.contains("CLAIM") || h.contains("NUMBER")) {
							if (looseInvoiceIdx == -1) looseInvoiceIdx = i;
						}

						if (h.equals("INVOICEDATE") || h.equals("CLAIMDATE")) {
							exactDateIdx = i;
						} else if (h.contains("DATE") || h.contains("TIME")) {
							if (looseDateIdx == -1) looseDateIdx = i;
						}

						if (h.contains("CONTRACT")) contractIdx = i;
						if (h.contains("CUSTOMER")) customerIdx = i;
					}
					invoiceIdx = (exactInvoiceIdx != -1) ? exactInvoiceIdx : looseInvoiceIdx;
					dateIdx = (exactDateIdx != -1) ? exactDateIdx : looseDateIdx;
					cloudHeaderProcessed = true;
				} else {
					currentCloudRow++;
					if (currentCloudRow == targetRowNumber) {
						String invoiceNum = (invoiceIdx != -1 && invoiceIdx < cloudLines.length) ? cloudLines[invoiceIdx].trim() : "";
						String fileDateStr = (dateIdx != -1 && dateIdx < cloudLines.length) ? cloudLines[dateIdx].trim() : "";
						String contractNum = (contractIdx != -1 && contractIdx < cloudLines.length) ? cloudLines[contractIdx].trim() : "";
						String customerNm = (customerIdx != -1 && customerIdx < cloudLines.length) ? cloudLines[customerIdx].trim() : "";
						
						String activeTimestampStr = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
						if (StringUtils.isBlank(fileDateStr)) {
							fileDateStr = activeTimestampStr;
						}
						String dynamicSequence = extractSequenceFromFilename(zipName);
						
						// FALLBACK STRATEGY: Applies placeholder tracking key if source data row had a blank invoice number
						if (StringUtils.isBlank(invoiceNum)) {
							invoiceNum = "MISSING-INV-" + zipName.replaceAll("(?i)\\.zip", "") + "-ROW-" + targetRowNumber;
						}
						
						ReconciliationRow row = reconciliationMap.get(invoiceNum);
						if (row == null) {
							row = new ReconciliationRow();
							row.invoiceNumber = invoiceNum;
							row.contractNumber = contractNum;
							row.customerName = customerNm;
							row.firstHrFileDate = fileDateStr;
							row.firstHrFileCreatedUtcDate = fileDateStr;
							row.firstHrZipFilename = zipName;
							row.firstHrCsvFileName = csvName;
							row.firstHrFileRowNumber = String.valueOf(targetRowNumber);
							row.firstHrFileSequence = dynamicSequence;
						}
						row.hrcStatus = "Success";
						row.soarStatus = "Failure";
						row.soarLatestHrFileDate = fileDateStr;
						row.soarLatestHrFileCreatedUtcDate = fileDateStr;
						row.soarLatestHrCsvFileName = csvName;
						row.soarLatestHrZipFilename = zipName;
						row.soarLatestHrFileRowNumber = String.valueOf(targetRowNumber);
						row.soarLatestHrFileSequence = dynamicSequence;
						row.lastUpdatedOn = activeTimestampStr;
						
						reconciliationMap.put(invoiceNum, row);
						break;
					}
				}
			}
		} finally {
			if (cloudCsvReader != null) {
				cloudCsvReader.close();
			}
		}
	}

	/**
	 * PHASE 4: Core OCR System Failure Exceptions Trace Engine Mapping Profiles
	 */
	private void updateTodayInternalFailures(Connection con, int accountId) throws Exception {
		String internalFailsQuery = "select cc.claim_number, cc.customer_name, cc.update_time "
				+ "from customer_claim cc "
				+ "where cc.account_id = ? and cc.error_code_id != 2 "
				+ "and cc.update_time between DATE_SUB(NOW(), INTERVAL 24 HOUR) and NOW()";
		
		HRPreparedStatement hpstmt = null;
		ResultSet rs = null;
		try {
			hpstmt = new HRPreparedStatement(con, internalFailsQuery);
			hpstmt.setInt(1, accountId);
			rs = hpstmt.executeQuery();
			String activeTimestampStr = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());

			while (rs.next()) {
				String claimNum = rs.getString("claim_number");
				if (StringUtils.isNotBlank(claimNum)) {
					ReconciliationRow row = reconciliationMap.get(claimNum);
					String fileDateStr = rs.getTimestamp("update_time") != null ? rs.getTimestamp("update_time").toString() : activeTimestampStr;

					if (row == null) {
						row = new ReconciliationRow();
						row.invoiceNumber = claimNum;
						row.customerName = rs.getString("customer_name");
						row.firstHrFileDate = fileDateStr;
						row.firstHrFileCreatedUtcDate = fileDateStr;
						row.firstHrZipFilename = "N/A";
						row.firstHrCsvFileName = "N/A";
						row.firstHrFileSequence = "N/A";
						row.firstHrFileRowNumber = "N/A";
					}
					row.hrcStatus = "Failed";
					row.soarStatus = "Failed";
					row.latestHrFileDate = fileDateStr;
					row.latestHrFileCreatedUtcDate = fileDateStr;
					row.lastUpdatedOn = activeTimestampStr;
					
					reconciliationMap.put(claimNum, row);
				}
			}
		} finally {
			Util.close(null, hpstmt, rs);
		}
	}

	/**
	 * PHASE 4B: Hydrate descriptive fields (vendor_id, vendor_name, customer_name) dynamically from customer_claim
	 */
	private void enrichMetadataFromCustomerClaims(Connection con, int accountId) {
		LOG.info("Executing hydration routine for client descriptive parameters mapping data blocks.");
		String hydrationSql = "select vendor_id, vendor_name, customer_name from customer_claim "
				+ "where account_id = ? and (claim_number = ? or invoice_number = ?) order by customer_claim_id desc limit 1";
		
		HRPreparedStatement hpstmt = null;
		ResultSet rs = null;

		try {
			for (String invoiceKey : reconciliationMap.keySet()) {
				ReconciliationRow row = reconciliationMap.get(invoiceKey);
				if (row == null || StringUtils.isBlank(invoiceKey) || invoiceKey.startsWith("MISSING-INV-")) continue;

				hpstmt = new HRPreparedStatement(con, hydrationSql);
				hpstmt.setInt(1, accountId);
				hpstmt.setString(2, invoiceKey.trim());
				hpstmt.setString(3, invoiceKey.trim());
				rs = hpstmt.executeQuery();

				if (rs.next()) {
					String aggregatedVendorId = rs.getString("vendor_id");
					String aggregatedVendorName = rs.getString("vendor_name");
					String aggregatedCustomerName = rs.getString("customer_name");

					if (StringUtils.isNotBlank(aggregatedVendorId)) {
						row.clientId = aggregatedVendorId.trim();
					}
					if (StringUtils.isNotBlank(aggregatedVendorName)) {
						row.clientName = aggregatedVendorName.trim();
					}
					if (StringUtils.isNotBlank(aggregatedCustomerName)) {
						row.customerName = aggregatedCustomerName.trim();
					}
				}
				Util.close(null, hpstmt, rs);
			}
		} catch (Exception ex) {
			LOG.error("InvoiceReconciliationAgent: Failed to source parameters from customer_claim: " + ex.getMessage(), ex);
		} finally {
			Util.close(null, hpstmt, rs);
		}
	}

	/**
	 * PHASE 5: Symmetrical Output Synchronizer
	 */
	private void saveUpdatedMasterFile(DaggrMetaData metaData, String attachmentName) {
		SimpleDateFormat sdfFolder = new SimpleDateFormat("yyyy/MM/dd");
		String todayFileName = MASTER_BASE_NAME + "FreshData.csv";
		String todaySundayFolderStr = "2026/01/01";

		try {
			Date trueCurrentDate = new Date();
			String todayDateSuffixStr = new SimpleDateFormat("yyyyMMdd").format(trueCurrentDate);
			todayFileName = MASTER_BASE_NAME + todayDateSuffixStr + ".csv";
			this.calculatedFileNameOnly = todayFileName;
			
			Calendar calTodaySunday = Calendar.getInstance();
			calTodaySunday.setTime(trueCurrentDate);
			while (calTodaySunday.get(Calendar.DAY_OF_WEEK) != Calendar.SUNDAY) {
				calTodaySunday.add(Calendar.DATE, -1);
			}
			todaySundayFolderStr = sdfFolder.format(calTodaySunday.getTime());
			
		} catch (Exception e) {
			LOG.error("InvoiceReconciliationAgent: Save path formatting execution clock failure: " + e.getMessage());
		}

		String providerShortName = "MONITORING";
		if (metaData.getDaggrFileTypeParser() != null && 
			metaData.getDaggrFileTypeParser().getMapProviderDatatype() != null && 
			metaData.getDaggrFileTypeParser().getMapProviderDatatype().getLuServiceProvider() != null) {
			providerShortName = metaData.getDaggrFileTypeParser().getMapProviderDatatype().getLuServiceProvider().getShortname();
		}

		String baseDirLocal = AgentProperties.getProperty("BaseDirectory_Local");
		if (StringUtils.isBlank(baseDirLocal)) {
			baseDirLocal = "/tmp/vcm/";
		}
		if (!baseDirLocal.endsWith("/")) {
			baseDirLocal += "/";
		}

		this.generatedMasterLocalPath = baseDirLocal + metaData.getAccountId() + StringConstants.FORWARD_SLASH 
				+ this.targetSourceFolder + StringConstants.FORWARD_SLASH + todayFileName;
		
		File localMasterOutputFile = new File(this.generatedMasterLocalPath);
		File parentDir = localMasterOutputFile.getParentFile();
		if (parentDir != null && !parentDir.exists()) {
			parentDir.mkdirs();
		}

		FileWriter writer = null;
		try {
			writer = new FileWriter(localMasterOutputFile);
			writer.write("InvoiceNumber,ContractNumber,ClientName,ClientId,CustomerName,Last Updated on,HRC Status,SOAR Status,"
					+ "First HR FileDate,First HR FileSequence,First HR FileCreatedUtcDate,First HR CsvFileName,First HR ZipFilename,First HR FileRowNumber,"
					+ "Latest HR FileDate,Latest HR FileSequence,Latest HR FileCreatedUtcDate,Latest HR CsvFileName,Latest HR ZipFilename,Latest HR FileRowNumber,"
					+ "SOAR Latest HR FileDate,SOAR Latest HR FileSequence,SOAR Latest HR FileCreatedUtcDate,SOAR Latest HR CsvFileName,SOAR Latest HR ZipFilename,SOAR Latest HR FileRowNumber\n");
			
			for (ReconciliationRow r : reconciliationMap.values()) {
				writer.write(String.format("%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s\n",
						sanitizeCsv(r.invoiceNumber), sanitizeCsv(r.contractNumber), sanitizeCsv(r.clientName), sanitizeCsv(r.clientId), sanitizeCsv(r.customerName), sanitizeCsv(r.lastUpdatedOn), sanitizeCsv(r.hrcStatus), sanitizeCsv(r.soarStatus),
						sanitizeCsv(r.firstHrFileDate), sanitizeCsv(r.firstHrFileSequence), sanitizeCsv(r.firstHrFileCreatedUtcDate), sanitizeCsv(r.firstHrCsvFileName), sanitizeCsv(r.firstHrZipFilename), sanitizeCsv(r.firstHrFileRowNumber),
						sanitizeCsv(r.latestHrFileDate), sanitizeCsv(r.latestHrFileSequence), sanitizeCsv(r.latestHrFileCreatedUtcDate), sanitizeCsv(r.latestHrCsvFileName), sanitizeCsv(r.latestHrZipFilename), sanitizeCsv(r.latestHrFileRowNumber),
						sanitizeCsv(r.soarLatestHrFileDate), sanitizeCsv(r.soarLatestHrFileSequence), sanitizeCsv(r.soarLatestHrFileCreatedUtcDate), sanitizeCsv(r.soarLatestHrCsvFileName), sanitizeCsv(r.soarLatestHrZipFilename), sanitizeCsv(r.soarLatestHrFileRowNumber)));
			}
			writer.flush();
		} catch (Exception e) {
			LOG.error("InvoiceReconciliationAgent: Error writing local file spreadsheet: " + e.getMessage(), e);
		} finally {
			if (writer != null) {
				try { writer.close(); } catch (Exception ignored) {}
			}
		}

		try {
			if (localMasterOutputFile.exists() && localMasterOutputFile.length() > 0) {
				String localNormalizedPathStr = localMasterOutputFile.getPath().replace("\\", "/");
				String path = StringUtils.substringAfter(localNormalizedPathStr, "vcm");
				if (StringUtils.isBlank(path)) {
					path = this.generatedMasterLocalPath;
				}
				
				// Standard framework copy logic copies reports into standard historical partitions
				FileUtility.copyProcessingFiletoCloud(metaData.getAccountId(), "Reports", localMasterOutputFile.getName(), localMasterOutputFile, null);
				LOG.info("InvoiceReconciliationAgent: Sync to S3 complete for weekly partition track: " + path);
			}
		} catch (Exception e) {
			LOG.error("InvoiceReconciliationAgent: Cloud upload failed: " + e.getMessage(), e);
		}
	}

	private String sanitizeCsv(String input) {
		if (StringUtils.isBlank(input)) return "";
		return input.replace(",", " ").replace("\r", "").replace("\n", "").trim();
	}

	/**
	 * PHASE 6: Full Comprehensive HTML Display Matrix Dashboard for the Email Template Summary Body
	 */
	private String htmlBodyReportSummaryBuilder() {
		StringBuilder html = new StringBuilder();
		html.append("<html><head><style>")
		    .append("table {border-collapse: collapse; width: 100%; font-family: Arial, sans-serif; font-size: 10px;}")
		    .append("th {background-color: #003366; color: white; padding: 4px; text-align: left; border: 1px solid #ddd; white-space: nowrap;}")
		    .append("td {padding: 4px; border: 1px solid #ddd; text-align: left;}")
		    .append("tr:nth-child(even) {background-color: #f2f2f2;}")
		    .append("</style></head><body>");
		
		html.append("<h2 style='color:#003366; font-family: Arial, sans-serif;'>Daily Claims Reconciliation Automation Full Matrix Tracking Summary Dashboard</h2>");
		html.append("<p style='font-family: Arial, sans-serif; font-size: 12px;'>Please find the comprehensive report attached to this email notification for full verification matrix analysis details.</p>");
		html.append("<table><tr>")
		    .append("<th>Invoice Number</th><th>Contract Number</th><th>Client Name</th><th>Client ID</th><th>Customer Name</th><th>Last Updated on</th>")
		    .append("<th>HRC Status</th><th>SOAR Status</th>")
		    .append("<th>First HRC Zip</th><th>First HRC Csv</th><th>First HRC Seq</th><th>First HRC Row</th><th>First HRC UTC Date</th>")
		    .append("<th>Latest HRC Zip</th><th>Latest HRC Csv</th><th>Latest HRC Seq</th><th>Latest HRC Row</th><th>Latest HRC UTC Date</th>")
		    .append("<th>SOAR Latest Zip</th><th>SOAR Latest Csv</th><th>SOAR Latest Seq</th><th>SOAR Latest Row</th><th>SOAR Latest UTC Date</th>")
		    .append("</tr>");

		for (ReconciliationRow row : reconciliationMap.values()) {
			String hrcColor = "Success".equalsIgnoreCase(row.hrcStatus) ? "green" : "red";
			String soarColor = "Sent to SOAR".equalsIgnoreCase(row.soarStatus) ? "green" : "red";

			html.append("<tr>")
			    .append("<td><b>").append(row.invoiceNumber).append("</b></td>")
			    .append("<td>").append(StringUtils.defaultString(row.contractNumber, "N/A")).append("</td>")
			    .append("<td>").append(StringUtils.defaultString(row.clientName, "N/A")).append("</td>")
			    .append("<td>").append(StringUtils.defaultString(row.clientId, "N/A")).append("</td>")
			    .append("<td>").append(StringUtils.defaultString(row.customerName, "N/A")).append("</td>")
			    .append("<td>").append(StringUtils.defaultString(row.lastUpdatedOn, "N/A")).append("</td>")
			    .append("<td style='color:").append(hrcColor).append("; font-weight:bold;'>").append(row.hrcStatus).append("</td>")
			    .append("<td style='color:").append(soarColor).append("; font-weight:bold;'>").append(row.soarStatus).append("</td>")
			    
			    // First HR Execution Metrics
			    .append("<td>").append(StringUtils.defaultString(row.firstHrZipFilename, "N/A")).append("</td>")
			    .append("<td>").append(StringUtils.defaultString(row.firstHrCsvFileName, "N/A")).append("</td>")
			    .append("<td>").append(StringUtils.defaultString(row.firstHrFileSequence, "N/A")).append("</td>")
			    .append("<td>").append(StringUtils.defaultString(row.firstHrFileRowNumber, "N/A")).append("</td>")
			    .append("<td>").append(StringUtils.defaultString(row.firstHrFileCreatedUtcDate, "N/A")).append("</td>")
			    
			    // Latest HR Execution Metrics
			    .append("<td>").append(StringUtils.defaultString(row.latestHrZipFilename, "N/A")).append("</td>")
			    .append("<td>").append(StringUtils.defaultString(row.latestHrCsvFileName, "N/A")).append("</td>")
			    .append("<td>").append(StringUtils.defaultString(row.latestHrFileSequence, "N/A")).append("</td>")
			    .append("<td>").append(StringUtils.defaultString(row.latestHrFileRowNumber, "N/A")).append("</td>")
			    .append("<td>").append(StringUtils.defaultString(row.latestHrFileCreatedUtcDate, "N/A")).append("</td>")
			    
			    // Downstream SOAR Execution Metrics
			    .append("<td>").append(StringUtils.defaultString(row.soarLatestHrZipFilename, "N/A")).append("</td>")
			    .append("<td>").append(StringUtils.defaultString(row.soarLatestHrCsvFileName, "N/A")).append("</td>")
			    .append("<td>").append(StringUtils.defaultString(row.soarLatestHrFileSequence, "N/A")).append("</td>")
			    .append("<td>").append(StringUtils.defaultString(row.soarLatestHrFileRowNumber, "N/A")).append("</td>")
			    .append("<td>").append(StringUtils.defaultString(row.soarLatestHrFileCreatedUtcDate, "N/A")).append("</td>")
			    .append("</tr>");
		}
		html.append("</table></body></html>");
		return html.toString();
	}

	/**
	 * Dispatches summary emails with full dashboard matrix logs and appends the 
	 * output CSV master document onto the outgoing mailing stream as an attachment asset.
	 */
	private void attachFileToEmail(String folderPath, String reportName, String email, Integer accountId) {
		LOG.debug("Entering into attachFileToEmail processing routine.");
		
		HRCEmailUtil hrcEmailUtil = (HRCEmailUtil) SpringApplicationContext.getBean("hrcEmailUtil");
		String fromAddress = AgentProperties.getProperty("cpa.mail.aggregatedData.notifier.fromAddress");
		if (StringUtils.isBlank(fromAddress)) {
			fromAddress = "cpa.notifications@notify.highradius.com";
		}
		
		String[] arrayOfToAddresses = null;
		if (email == null) {
			String defaultNotifierList = AgentProperties.getProperty("CPA_AGENT.monitor.Notifier.toAddress");
			arrayOfToAddresses = StringUtils.isNotBlank(defaultNotifierList) ? defaultNotifierList.split(",") : new String[]{"cpa.notifications@highradius.com"};
		} else {
			arrayOfToAddresses = email.split(",");
		}
		
		String subject = reportName + "_" + new SimpleDateFormat("MM/dd/yyyy").format(new Date());
		String compiledHtmlBody = htmlBodyReportSummaryBuilder();

		try {
			List<String> to = Arrays.asList(arrayOfToAddresses);
			ArrayList<String> attachmentsList = new ArrayList<String>();
			
			// Verify file presence before adding attachment pointer
			File masterAttachmentAsset = new File(folderPath);
			if (masterAttachmentAsset.exists() && masterAttachmentAsset.length() > 0) {
				attachmentsList.add(folderPath);
			} else {
				LOG.warn("Master report asset was empty or missing from server workspace. Dispatching text matrix summary only.");
			}

			SendEmailRequest sendEmailRequest = new SendEmailRequest(
				accountId, 
				com.highradius.util.cpa.StringConstants.CPA_PRODUCT, 
				ThreadContext.get(StringConstants.LANGUAGE_CODE)
			).body(compiledHtmlBody).from(fromAddress).to(to).subject(subject).attachMents(attachmentsList);

			hrcEmailUtil.sendEmailWithoutCatchingException(sendEmailRequest);
			LOG.info("Reconciliation notification matrix safely dispatched to: " + to.toString());

		} catch (Exception em) {
			LOG.error("Failed while sending report matrix mail attachment stack trace error: " + em.getMessage(), em);
			
			// Fallback block handles mail transmission exceptions (e.g. SMTP 552 Size Overflows)
			if (em.getMessage() != null && em.getMessage().contains("552")) {
				String internalSubject = "Location of report file";
				String locationOverflowNotice = "Please find the report file at \"" + folderPath + "\" location as the data size exceeds standard mail exchange boundaries.";
				
				try {
					List<String> toFallback = Arrays.asList(arrayOfToAddresses);
					SendEmailRequest fallbackEmailRequest = new SendEmailRequest(
						accountId, 
						com.highradius.util.cpa.StringConstants.CPA_PRODUCT, 
						ThreadContext.get(StringConstants.LANGUAGE_CODE)
					).body(locationOverflowNotice).from(fromAddress).to(toFallback).subject(internalSubject);
					
					hrcEmailUtil.sendEmailWithoutCatchingException(fallbackEmailRequest);
				} catch (Exception fallbackEx) {
					LOG.error("Critical failure dropping size overflow path notification to exchange server context: " + fallbackEx.getMessage(), fallbackEx);
				}
			}
		}
		LOG.debug("Exiting safely from attachFileToEmail operation hook.");
	}

	private static class ReconciliationRow {
		public String invoiceNumber = "";
		public String contractNumber = "";
		public String clientName = "";
		public String clientId = "";
		public String customerName = "";
		public String lastUpdatedOn = "";
		public String hrcStatus = "";
		public String soarStatus = "";
		
		public String firstHrFileDate = "";
		public String firstHrFileSequence = "";
		public String firstHrFileCreatedUtcDate = "";
		public String firstHrCsvFileName = "";
		public String firstHrZipFilename = "";
		public String firstHrFileRowNumber = "";
		
		public String latestHrFileDate = "";
		public String latestHrFileSequence = "";
		public String latestHrFileCreatedUtcDate = "";
		public String latestHrCsvFileName = "";
		public String latestHrZipFilename = "";
		public String latestHrFileRowNumber = "";
		
		public String soarLatestHrFileDate = "";
		public String soarLatestHrFileSequence = "";
		public String soarLatestHrFileCreatedUtcDate = "";
		public String soarLatestHrCsvFileName = "";
		public String soarLatestHrZipFilename = "";
		public String soarLatestHrFileRowNumber = "";
	}
}