// const { pool } = require("../../config/db");
// const multer = require("multer");
// const path = require("path");
// const fs = require("fs");
// const pdfParse = require("pdf-parse");

// /* ─── Ensure upload directory exists ──────────────────────────────────────── */
// const uploadDir = path.join(__dirname, "../../uploads/bank_statements");
// if (!fs.existsSync(uploadDir)) {
//   fs.mkdirSync(uploadDir, { recursive: true });
//   console.log("Created upload directory:", uploadDir);
// }

// /* ─── Multer setup for bank statement uploads ──────────────────────────────── */
// const storage = multer.diskStorage({
//   destination: (req, file, cb) => {
//     cb(null, uploadDir);
//   },
//   filename: (req, file, cb) => {
//     const ext = path.extname(file.originalname);
//     const timestamp = Date.now();
//     cb(null, `bank_stmt_${timestamp}${ext}`);
//   },
// });

// const upload = multer({ 
//   storage, 
//   limits: { fileSize: 20 * 1024 * 1024 }
// });
// const uploadMiddleware = upload.single("bankStatement");


// /* ─── Helper: Parse Canara Bank Statement PDF ─── */
// const parseCanaraStatement = (text) => {
//   const transactions = [];
//   const lines = text.split('\n');
  
//   console.log("Parsing Canara Bank statement...");
  
//   let currentDate = null;
//   let currentParticulars = "";
//   let currentDeposit = "";
//   let currentWithdrawal = "";
//   let currentBalance = "";
//   let isInTransaction = false;
  
//   // Helper function to truncate text to max length
//   const truncate = (str, maxLen = 200) => {
//     if (!str) return "";
//     return str.length > maxLen ? str.substring(0, maxLen) : str;
//   };
  
//   for (let i = 0; i < lines.length; i++) {
//     const line = lines[i].trim();
//     if (!line) continue;
    
//     // Skip header/footer lines
//     if (line.includes("Statement for A/c") || 
//         line.includes("Customer Id") ||
//         line.includes("Branch Code") ||
//         line.includes("Branch Name") ||
//         line.includes("IFSC Code") ||
//         line.includes("Opening Balance") ||
//         line.includes("Closing Balance") ||
//         line.includes("Page") ||
//         line.includes("Canara Bank") ||
//         line.includes("CANARA BANK")) {
//       continue;
//     }
    
//     // Check for date pattern (DD-MM-YYYY)
//     const dateMatch = line.match(/^(\d{2}-\d{2}-\d{4})/);
    
//     if (dateMatch) {
//       // If we have a previous transaction, save it
//       if (currentDate && (currentDeposit || currentWithdrawal)) {
//         let amount = 0;
//         let isDebit = true;
//         let beneficiary = truncate(currentParticulars.trim(), 200);
        
//         if (currentDeposit && parseFloat(currentDeposit) > 0) {
//           amount = parseFloat(currentDeposit.replace(/,/g, ''));
//           isDebit = false; // Deposit is credit
//         } else if (currentWithdrawal && parseFloat(currentWithdrawal) > 0) {
//           amount = parseFloat(currentWithdrawal.replace(/,/g, ''));
//           isDebit = true; // Withdrawal is debit
//         }
        
//         if (amount > 0 && beneficiary) {
//           // Clean up beneficiary name - remove UPI IDs and long codes
//           beneficiary = beneficiary
//             .replace(/UPI\/DR\/\d+\//gi, '')
//             .replace(/UPI\/CR\/\d+\//gi, '')
//             .replace(/Chq:\s*\d+/gi, '')
//             .replace(/[A-Z0-9]{20,}/g, '') // Remove long alphanumeric codes
//             .replace(/\//g, ' ')
//             .replace(/\s+/g, ' ')
//             .trim();
          
//           // Further truncate if needed (max 200 chars)
//           beneficiary = truncate(beneficiary, 200);
          
//           // Extract transaction type
//           let type = "UPI";
//           if (beneficiary.toLowerCase().includes("neft")) type = "NEFT";
//           else if (beneficiary.toLowerCase().includes("rtgs")) type = "RTGS";
//           else if (beneficiary.toLowerCase().includes("imps")) type = "IMPS";
//           else if (beneficiary.toLowerCase().includes("cheque")) type = "CHEQUE";
          
//           // Parse date from DD-MM-YYYY to YYYY-MM-DD
//           const [day, month, year] = currentDate.split('-');
//           const formattedDate = `${year}-${month}-${day}`;
          
//           // Generate a shorter reference
//           const ref = `UPI-${formattedDate.replace(/-/g, '')}-${Math.floor(amount)}`;
          
//           transactions.push({
//             date: formattedDate,
//             ref: truncate(ref, 100),
//             beneficiary: beneficiary,
//             amount: amount,
//             type: type,
//             isDebit: isDebit,
//             narration: truncate(currentParticulars, 500)
//           });
          
//           console.log(`Parsed Canara transaction: ${formattedDate} | ${beneficiary.substring(0, 50)} | ${amount} | ${isDebit ? "DEBIT" : "CREDIT"}`);
//         }
//       }
      
//       // Start new transaction
//       currentDate = dateMatch[1];
//       currentParticulars = "";
//       currentDeposit = "";
//       currentWithdrawal = "";
//       currentBalance = "";
//       isInTransaction = true;
      
//       // The rest of the line after date contains particulars
//       let remaining = line.substring(dateMatch[0].length).trim();
//       if (remaining && !remaining.match(/^\d/)) {
//         currentParticulars = remaining;
//       }
      
//     } else if (isInTransaction) {
//       // Check if this line contains deposit amount (positive number, usually with commas)
//       const depositMatch = line.match(/^([\d,]+\.\d{2})$/);
//       // Check if this line contains withdrawal amount
//       const withdrawalMatch = line.match(/^([\d,]+\.\d{2})$/);
//       // Check if line contains balance
//       const balanceMatch = line.match(/([\d,]+\.\d{2})$/);
      
//       // Look for amounts in the line
//       const amountMatch = line.match(/([\d,]+\.\d{2})/g);
      
//       if (amountMatch) {
//         // Canara format often has: Particulars (multiline) then amount, then balance
//         // The amounts appear in order: Withdrawal/Deposit, then Balance
//         if (amountMatch.length === 1) {
//           // Single amount - could be withdrawal/deposit or balance
//           // Check previous line to determine
//           if (currentParticulars && !currentWithdrawal && !currentDeposit) {
//             // This is likely the transaction amount
//             const amount = parseFloat(amountMatch[0].replace(/,/g, ''));
//             // Check if it's a deposit (CR) or withdrawal (DR)
//             if (line.toLowerCase().includes("cr") || currentParticulars.toLowerCase().includes("credit")) {
//               currentDeposit = amountMatch[0];
//             } else {
//               currentWithdrawal = amountMatch[0];
//             }
//           } else {
//             // This is likely the balance
//             currentBalance = amountMatch[0];
//           }
//         } else if (amountMatch.length >= 2) {
//           // Multiple amounts - first is transaction amount, second is balance
//           const transactionAmount = amountMatch[0];
//           currentBalance = amountMatch[1];
          
//           // Determine if deposit or withdrawal
//           if (line.toLowerCase().includes("cr") || currentParticulars.toLowerCase().includes("credit")) {
//             currentDeposit = transactionAmount;
//           } else {
//             currentWithdrawal = transactionAmount;
//           }
//         }
//       }
      
//       // Accumulate particulars (multiline) but limit length
//       if (!line.match(/^[\d,]+\.\d{2}/) && !line.match(/^\d{2}-\d{2}-\d{4}/)) {
//         if (currentParticulars) {
//           currentParticulars += " " + line;
//         } else {
//           currentParticulars = line;
//         }
//         // Limit particulars length to avoid overflow
//         if (currentParticulars.length > 1000) {
//           currentParticulars = currentParticulars.substring(0, 1000);
//         }
//       }
//     }
//   }
  
//   // Don't forget the last transaction
//   if (currentDate && (currentDeposit || currentWithdrawal)) {
//     let amount = 0;
//     let isDebit = true;
//     let beneficiary = currentParticulars ? currentParticulars.trim() : "";
    
//     if (currentDeposit && parseFloat(currentDeposit) > 0) {
//       amount = parseFloat(currentDeposit.replace(/,/g, ''));
//       isDebit = false;
//     } else if (currentWithdrawal && parseFloat(currentWithdrawal) > 0) {
//       amount = parseFloat(currentWithdrawal.replace(/,/g, ''));
//       isDebit = true;
//     }
    
//     if (amount > 0 && beneficiary) {
//       beneficiary = beneficiary
//         .replace(/UPI\/DR\/\d+\//gi, '')
//         .replace(/UPI\/CR\/\d+\//gi, '')
//         .replace(/Chq:\s*\d+/gi, '')
//         .replace(/[A-Z0-9]{20,}/g, '')
//         .replace(/\//g, ' ')
//         .replace(/\s+/g, ' ')
//         .trim()
//         .substring(0, 200);
      
//       let type = "UPI";
//       if (beneficiary.toLowerCase().includes("neft")) type = "NEFT";
//       else if (beneficiary.toLowerCase().includes("rtgs")) type = "RTGS";
//       else if (beneficiary.toLowerCase().includes("imps")) type = "IMPS";
//       else if (beneficiary.toLowerCase().includes("cheque")) type = "CHEQUE";
      
//       const [day, month, year] = currentDate.split('-');
//       const formattedDate = `${year}-${month}-${day}`;
//       const ref = `UPI-${formattedDate.replace(/-/g, '')}-${Math.floor(amount)}`;
      
//       transactions.push({
//         date: formattedDate,
//         ref: ref.substring(0, 100),
//         beneficiary: beneficiary,
//         amount: amount,
//         type: type,
//         isDebit: isDebit,
//         narration: (currentParticulars || "").substring(0, 500)
//       });
//     }
//   }
  
//   return transactions;
// };

// /* ─── Helper: Parse HDFC Bank Statement PDF (improved for your format) ─── */
// const parseHDFCStatement = (text) => {
//   const transactions = [];
  
//   // Split into lines
//   const lines = text.split('\n');
  
//   let i = 0;
//   while (i < lines.length) {
//     const line = lines[i].trim();
    
//     // Skip empty lines and header/footer lines
//     if (!line || 
//         line.includes("Account Statement") || 
//         line.includes("Account Summary") ||
//         line.includes("Transaction Details") ||
//         line.includes("Opening Balance") ||
//         line.includes("Closing Balance") ||
//         line.includes("Page") ||
//         line.includes("HDFC BANK") ||
//         line.includes("HDFC Bank") ||
//         line.includes("Balance")) {
//       i++;
//       continue;
//     }
    
//     // Check if line starts with a date (DD MMM YYYY)
//     const dateMatch = line.match(/^(\d{2}\s+(?:Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)\s+\d{4})/i);
    
//     if (dateMatch) {
//       const dateStr = dateMatch[1];
//       const [day, month, year] = dateStr.split(' ');
//       const monthMap = {
//         'Jan': '01', 'Feb': '02', 'Mar': '03', 'Apr': '04', 'May': '05', 'Jun': '06',
//         'Jul': '07', 'Aug': '08', 'Sep': '09', 'Oct': '10', 'Nov': '11', 'Dec': '12'
//       };
//       const formattedDate = `${year}-${monthMap[month]}-${day.padStart(2, '0')}`;
      
//       // Get the remaining part after date
//       let remaining = line.substring(dateMatch[0].length).trim();
      
//       // Extract reference number
//       let ref = "";
//       const refMatch = remaining.match(/^([A-Z0-9\/]+)/i);
//       if (refMatch) {
//         ref = refMatch[1];
//         remaining = remaining.substring(refMatch[0].length).trim();
//       }
      
//       // Check if next line contains continuation of reference (like "43" on next line)
//       if (i + 1 < lines.length) {
//         const nextLine = lines[i + 1].trim();
//         if (nextLine.match(/^\d+$/) && ref) {
//           ref += nextLine;
//           i++;
//           if (i + 1 < lines.length) {
//             remaining = lines[i + 1].trim();
//           } else {
//             remaining = "";
//           }
//         }
//       }
      
//       // Look for transaction type (NEFT, RTGS, IMPS, CHEQUE)
//       let type = "";
//       const typeMatch = remaining.match(/^(NEFT|RTGS|IMPS|CHEQUE)/i);
//       if (typeMatch) {
//         type = typeMatch[1];
//         remaining = remaining.substring(typeMatch[0].length).trim();
//       }
      
//       // Check for OUTWARD/INWARD direction
//       let direction = "";
//       const directionMatch = remaining.match(/^(OUTWARD|INWARD)/i);
//       if (directionMatch) {
//         direction = directionMatch[1];
//         remaining = remaining.substring(directionMatch[0].length).trim();
//       }
      
//       // Remove dash if present
//       remaining = remaining.replace(/^-\s*/, '');
      
//       // Find amount - look for pattern like 1,25,000.00 or 1,25,000.00
//       let amount = 0;
//       let amountStr = "";
//       let beneficiary = "";
      
//       // Try to find amount in current remaining text
//       const amountMatch = remaining.match(/(\d{1,3}(?:,\d{3})*\.\d{2})$/);
//       if (amountMatch) {
//         amountStr = amountMatch[1];
//         beneficiary = remaining.substring(0, remaining.length - amountMatch[0].length).trim();
//       } else {
//         // Amount might be on next line(s)
//         let lookAhead = i + 1;
//         let beneficiaryParts = [remaining];
        
//         while (lookAhead < lines.length && lookAhead < i + 5) {
//           const nextLine = lines[lookAhead].trim();
//           if (!nextLine) {
//             lookAhead++;
//             continue;
//           }
          
//           const amountCheck = nextLine.match(/(\d{1,3}(?:,\d{3})*\.\d{2})$/);
//           if (amountCheck) {
//             amountStr = amountCheck[1];
//             // Add this line's content before the amount
//             const lineContent = nextLine.substring(0, nextLine.length - amountCheck[0].length).trim();
//             if (lineContent) {
//               beneficiaryParts.push(lineContent);
//             }
//             i = lookAhead;
//             break;
//           } else {
//             beneficiaryParts.push(nextLine);
//             lookAhead++;
//           }
//         }
        
//         beneficiary = beneficiaryParts.join(' ').trim();
//         // Remove amount from beneficiary if found
//         if (amountStr) {
//           beneficiary = beneficiary.replace(amountStr, '').trim();
//         }
//       }
      
//       // If still no amount, try to find any number pattern
//       if (!amountStr) {
//         const anyAmountMatch = remaining.match(/(\d{1,3}(?:,\d{3})*\.\d{2})/);
//         if (anyAmountMatch) {
//           amountStr = anyAmountMatch[1];
//           beneficiary = remaining.replace(amountStr, '').trim();
//         }
//       }
      
//       if (amountStr) {
//         amount = parseFloat(amountStr.replace(/,/g, ''));
//       }
      
//       // Skip credit transactions (CREDIT) or amounts that don't make sense
//       if (amount === 0 || line.includes("CREDIT") || type === "CREDIT") {
//         i++;
//         continue;
//       }
      
//       // Clean up beneficiary name
//       if (beneficiary) {
//         beneficiary = beneficiary
//           .replace(/A\/C:\s*\d+/gi, '')
//           .replace(/IFSC:\s*[A-Z0-9]+/gi, '')
//           .replace(/REM:\s*[A-Z0-9-]+/gi, '')
//           .replace(/REF:\s*[A-Z0-9-]+/gi, '')
//           .replace(/\|/g, '')
//           .replace(/\s+/g, ' ')
//           .trim();
//       }
      
//       // Only add if we have a valid amount and beneficiary
//       if (amount > 0 && beneficiary && beneficiary.length > 0) {
//         // Determine if debit (OUTWARD) or credit
//         const isDebit = direction === "OUTWARD" || (!direction && !line.includes("CREDIT"));
        
//         transactions.push({
//           date: formattedDate,
//           ref: ref || `TX-${Date.now()}-${transactions.length}`,
//           beneficiary: beneficiary,
//           amount: amount,
//           type: type || (isDebit ? "DEBIT" : "CREDIT"),
//           isDebit: isDebit,
//           narration: line
//         });
        
//         console.log(`Parsed transaction: ${formattedDate} | ${beneficiary} | ${amount}`);
//       }
//     }
    
//     i++;
//   }
  
//   return transactions;
// };

// /* ─── Helper: Parse ICICI Bank Statement PDF ─── */
// const parseICICIStatement = (text) => {
//   const transactions = [];
//   const lines = text.split('\n');
  
//   let currentDate = null;
  
//   for (const line of lines) {
//     const trimmed = line.trim();
//     if (!trimmed) continue;
    
//     // Skip header/footer lines
//     if (trimmed.includes("Account Statement") || 
//         trimmed.includes("Account Summary") ||
//         trimmed.includes("Transaction Details") ||
//         trimmed.includes("Opening Balance") ||
//         trimmed.includes("Closing Balance") ||
//         trimmed.includes("Page") ||
//         trimmed.includes("ICICI Bank")) {
//       continue;
//     }
    
//     // Extract date if present
//     const dateMatch = trimmed.match(/^(\d{2}\s+(?:Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)\s+\d{4})/i);
//     if (dateMatch) {
//       const dateStr = dateMatch[1];
//       const [day, month, year] = dateStr.split(' ');
//       const monthMap = {
//         'Jan': '01', 'Feb': '02', 'Mar': '03', 'Apr': '04', 'May': '05', 'Jun': '06',
//         'Jul': '07', 'Aug': '08', 'Sep': '09', 'Oct': '10', 'Nov': '11', 'Dec': '12'
//       };
//       currentDate = `${year}-${monthMap[month]}-${day.padStart(2, '0')}`;
//       continue;
//     }
    
//     if (!currentDate) continue;
    
//     // Look for debit/credit patterns
//     const debitMatch = trimmed.match(/(\d{1,3}(?:,\d{3})*\.\d{2})\s*$/) || trimmed.match(/DEBIT.*?(\d{1,3}(?:,\d{3})*\.\d{2})/i);
//     const creditMatch = trimmed.match(/CREDIT.*?(\d{1,3}(?:,\d{3})*\.\d{2})/i);
    
//     let amount = 0;
//     let isDebit = true;
//     let amountStr = "";
    
//     if (debitMatch) {
//       amountStr = debitMatch[1];
//       amount = parseFloat(amountStr.replace(/,/g, ''));
//       isDebit = true;
//     } else if (creditMatch) {
//       amountStr = creditMatch[1];
//       amount = parseFloat(amountStr.replace(/,/g, ''));
//       isDebit = false;
//     }
    
//     if (amount === 0) continue;
    
//     // Extract reference
//     const refMatch = trimmed.match(/^([A-Z0-9\/]+)/i);
//     let ref = refMatch ? refMatch[1] : "";
    
//     // Extract beneficiary (remove ref, amount, and common patterns)
//     let beneficiary = trimmed
//       .replace(ref, '')
//       .replace(amountStr, '')
//       .replace(/DEBIT|CREDIT|NEFT|RTGS|IMPS|CHEQUE/gi, '')
//       .replace(/A\/C:\s*\d+/gi, '')
//       .replace(/IFSC:\s*[A-Z0-9]+/gi, '')
//       .replace(/\|/g, '')
//       .replace(/\s+/g, ' ')
//       .trim();
    
//     if (beneficiary && amount > 0) {
//       transactions.push({
//         date: currentDate,
//         ref: ref || `TX-${Date.now()}-${transactions.length}`,
//         beneficiary: beneficiary,
//         amount: amount,
//         type: isDebit ? "DEBIT" : "CREDIT",
//         isDebit: isDebit,
//         narration: trimmed
//       });
      
//       console.log(`Parsed ICICI transaction: ${currentDate} | ${beneficiary} | ${amount}`);
//     }
//   }
  
//   return transactions;
// };

// /* ─── Helper: Parse bank statement PDF with optional password ──────────── */
// const parseBankStatementPDF = async (fileBuffer, fileName, password = null) => {
//   console.log("=== Starting PDF Parse ===");
//   console.log("File name:", fileName);
//   console.log("Password provided:", password ? "Yes (length: " + password.length + ")" : "No");
  
//   try {
//     let data;
    
//     // IMPORTANT: Only try with password if one is actually provided
//     if (password && password.trim() !== "") {
//       console.log("Attempting to decrypt PDF with provided password...");
//       try {
//         data = await pdfParse(fileBuffer, { password: password.trim() });
//       } catch (pwdError) {
//         console.error("PDF password error:", pwdError.message);
//         if (pwdError.name === 'PasswordException' || 
//             pwdError.message.toLowerCase().includes('password')) {
//           throw new Error('Invalid PDF password. Please check and try again.');
//         }
//         throw pwdError;
//       }
//     } else {
//       // No password provided - try to parse normally
//       console.log("No password provided, trying to parse as unencrypted PDF...");
//       try {
//         data = await pdfParse(fileBuffer);
//       } catch (noPwdError) {
//         console.error("PDF parse error:", noPwdError.message);
//         // Check if the error indicates it needs a password
//         if (noPwdError.name === 'PasswordException' || 
//             noPwdError.message.toLowerCase().includes('password')) {
//           throw new Error('PDF is encrypted. Please provide the password to open this file.');
//         }
//         throw noPwdError;
//       }
//     }
    
//     const text = data.text;
//     console.log("PDF Text length:", text.length);
//     console.log("First 1500 chars:", text.substring(0, 1500));
    
//     let transactions = [];
    
//     // Detect bank type based on filename or content
//     const fileNameLower = fileName.toLowerCase();
//     const textLower = text.toLowerCase();
    
//     const isHDFC = fileNameLower.includes("hdfc") || textLower.includes("hdfc bank");
//     const isICICI = fileNameLower.includes("icici") || textLower.includes("icici bank");
//     const isCanara = fileNameLower.includes("canara") || textLower.includes("canara bank") || textLower.includes("cnrb");
    
//     if (isHDFC) {
//       console.log("Detected HDFC Bank statement");
//       transactions = parseHDFCStatement(text);
//     } else if (isICICI) {
//       console.log("Detected ICICI Bank statement");
//       transactions = parseICICIStatement(text);
//     } else if (isCanara) {
//       console.log("Detected Canara Bank statement");
//       transactions = parseCanaraStatement(text);
//     } else {
//       console.log("Unknown bank type, trying all parsers...");
//       // Try HDFC first
//       transactions = parseHDFCStatement(text);
//       if (transactions.length === 0) {
//         console.log("HDFC parser found nothing, trying ICICI parser");
//         transactions = parseICICIStatement(text);
//       }
//       if (transactions.length === 0) {
//         console.log("ICICI parser found nothing, trying Canara parser");
//         transactions = parseCanaraStatement(text);
//       }
//     }
    
//     console.log(`Parsed ${transactions.length} transactions from PDF`);
    
//     // Log first few transactions for debugging
//     if (transactions.length > 0) {
//       console.log("Sample transaction:", JSON.stringify(transactions[0], null, 2));
//     } else {
//       console.log("WARNING: No transactions parsed. PDF content sample:", text.substring(0, 500));
//     }
    
//     return transactions;
    
//   } catch (error) {
//     console.error("PDF Parse Error:", error);
//     throw error;
//   }
// };

// /* ─── Helper: Parse UPI transactions from any bank statement ─── */
// const parseUPITransaction = (text) => {
//   const transactions = [];
  
//   // Pattern for UPI transactions with amounts
//   // Looking for patterns like: "UPI/DR/696577264162/FLIPKART" followed by amount
//   const upiPatterns = [
//     /UPI\/(DR|CR)\/\d+\/([A-Za-z0-9\s\/]+?)(?:\d{1,3}(?:,\d{3})*\.\d{2})/gi,
//     /UPI\/(DR|CR)\/\d+\/([^\n]+)\n\s*([\d,]+\.\d{2})/gi,
//   ];
  
//   const lines = text.split('\n');
//   let i = 0;
  
//   while (i < lines.length) {
//     const line = lines[i];
    
//     // Look for UPI transaction marker
//     if (line.includes("UPI/DR") || line.includes("UPI/CR")) {
//       const isDebit = line.includes("UPI/DR");
      
//       // Extract beneficiary - look for the merchant name
//       let beneficiary = "";
//       const upiMatch = line.match(/UPI\/(?:DR|CR)\/\d+\/([A-Za-z\s\/]+)/i);
//       if (upiMatch) {
//         beneficiary = upiMatch[1].trim();
//         // Clean up beneficiary name
//         beneficiary = beneficiary
//           .replace(/\/[A-Z0-9]+@[A-Z]+\/?/gi, '')
//           .replace(/PAYMENT\/\/[A-Z0-9]+/gi, '')
//           .replace(/\//g, ' ')
//           .trim();
//       }
      
//       // Look for amount in current or next lines
//       let amount = 0;
//       let j = i;
//       while (j < Math.min(i + 5, lines.length)) {
//         const amountMatch = lines[j].match(/(\d{1,3}(?:,\d{3})*\.\d{2})/);
//         if (amountMatch && !lines[j].includes("Balance")) {
//           amount = parseFloat(amountMatch[1].replace(/,/g, ''));
//           break;
//         }
//         j++;
//       }
      
//       // Look for date
//       let date = null;
//       let dateLine = i > 0 ? lines[i-1] : "";
//       const dateMatch = dateLine.match(/(\d{2}-\d{2}-\d{4})/);
//       if (dateMatch) {
//         const [day, month, year] = dateMatch[1].split('-');
//         date = `${year}-${month}-${day}`;
//       }
      
//       if (amount > 0 && beneficiary) {
//         transactions.push({
//           date: date || new Date().toISOString().split('T')[0],
//           ref: `UPI-${Date.now()}-${transactions.length}`,
//           beneficiary: beneficiary,
//           amount: amount,
//           type: "UPI",
//           isDebit: isDebit,
//           narration: line
//         });
//         console.log(`Parsed UPI transaction: ${beneficiary} | ${amount} | ${isDebit ? "DEBIT" : "CREDIT"}`);
//       }
//     }
//     i++;
//   }
  
//   return transactions;
// };

// /* ═══════════════════════════════════════════════════════════════════════════
//    POST /bank-reconciliation/upload
// ═══════════════════════════════════════════════════════════════════════════ */
// const uploadBankStatement = (req, res) => {
//   console.log("=== uploadBankStatement called ===");
  
//   uploadMiddleware(req, res, async (err) => {
//     if (err) {
//       console.error("Multer error:", err);
//       return res.status(400).json({ success: false, message: "File upload error: " + err.message });
//     }

//     console.log("req.file exists:", !!req.file);
    
//     const client = await pool.connect();
//     try {
//       const { statementDate, resetMode = 'true', pdfPassword = '' } = req.body;
//       const orgId = req.user?.orgId || req.session?.user?.orgId;
//       const userId = req.user?.id;

//       if (!orgId) {
//         return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
//       }

//       if (!req.file) {
//         return res.status(400).json({ success: false, message: "No file uploaded" });
//       }

//       console.log("File received:", req.file.originalname, req.file.size, "bytes");
//       console.log("pdfPassword from request:", pdfPassword ? "Provided (length: " + pdfPassword.length + ")" : "Not provided");

//       // Parse PDF to extract transactions - only pass password if it's not empty
//       const fileBuffer = fs.readFileSync(req.file.path);
//       const passwordToUse = (pdfPassword && pdfPassword.trim() !== "") ? pdfPassword.trim() : null;
//       const parsedTransactions = await parseBankStatementPDF(fileBuffer, req.file.originalname, passwordToUse);
//       console.log(`Parsed ${parsedTransactions.length} transactions`);

//       if (parsedTransactions.length === 0) {
//         // Clean up file
//         try { fs.unlinkSync(req.file.path); } catch(e) { console.error("Error deleting file:", e); }
//         return res.status(400).json({ 
//           success: false, 
//           message: "Could not parse any transactions from the PDF. Please check the file format or try a different statement format." 
//         });
//       }

//       await client.query("BEGIN");

//       const shouldReset = resetMode === 'true';

//       if (shouldReset) {
//         // Get existing statement
//         const existingStatement = await client.query(
//           `SELECT id FROM bank_statements 
//            WHERE org_id = $1 AND status = 'PROCESSED' 
//            ORDER BY created_at DESC LIMIT 1`,
//           [orgId]
//         );
        
//         if (existingStatement.rows.length > 0) {
//           // Delete related records first (due to foreign key constraints)
//           await client.query(`DELETE FROM reconciliation_matches WHERE org_id = $1 AND bank_transaction_id IN (SELECT id FROM bank_transactions WHERE bank_statement_id = $2)`, [orgId, existingStatement.rows[0].id]);
//           await client.query(`DELETE FROM bank_transactions WHERE bank_statement_id = $1`, [existingStatement.rows[0].id]);
//           await client.query(`DELETE FROM bank_statements WHERE id = $1`, [existingStatement.rows[0].id]);
//           console.log(`Reset mode: Deleted existing statement ID: ${existingStatement.rows[0].id}`);
//         }
//       }

//       // Insert new bank statement record
//       const stmtResult = await client.query(
//         `INSERT INTO bank_statements 
//          (org_id, file_name, file_path, statement_date, total_entries, status, uploaded_by, created_at)
//          VALUES ($1, $2, $3, $4, $5, 'PROCESSED', $6, NOW())
//          RETURNING id`,
//         [orgId, req.file.originalname, `/uploads/bank_statements/${req.file.filename}`, statementDate || new Date(), parsedTransactions.length, userId]
//       );

//       const bankStatementId = stmtResult.rows[0].id;
//       console.log(`Created bank statement ID: ${bankStatementId}`);

//       let matchedCount = 0;
//       let unmatchedCount = 0;
//       const insertedTransactions = [];

//       // Insert transactions
//       for (const txn of parsedTransactions) {
//         // Check if this transaction amount matches any existing payment
//         const existingPayment = await client.query(
//           `SELECT at.id, at.amount, at.grn_id, g.vendor_name, at.txn_date
//            FROM ap_transactions at
//            JOIN grn g ON g.id = at.grn_id
//            WHERE at.amount = $1 
//              AND g.org_id = $2
//            LIMIT 1`,
//           [txn.amount, orgId]
//         );

//         let matchedStatus = 'UNMATCHED';
//         let matchedGrnId = null;
//         let matchedAmount = null;

//         if (existingPayment.rows.length > 0) {
//           matchedStatus = 'MATCHED';
//           matchedGrnId = existingPayment.rows[0].grn_id;
//           matchedAmount = txn.amount;
//           matchedCount++;
//         } else {
//           unmatchedCount++;
//         }

//         // Insert bank transaction
//         const txnResult = await client.query(
//           `INSERT INTO bank_transactions 
//            (bank_statement_id, org_id, transaction_ref, transaction_date, beneficiary_name, amount, transaction_type, matched_status, matched_grn_id, matched_amount, description, created_at)
//            VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, NOW())
//            RETURNING id`,
//           [bankStatementId, orgId, txn.ref || '', txn.date, txn.beneficiary, txn.amount, txn.type, 
//            matchedStatus, matchedGrnId, matchedAmount, txn.narration || '']
//         );
        
//         insertedTransactions.push({
//           id: txnResult.rows[0].id,
//           ...txn,
//           matchedStatus
//         });
//       }

//       // Create reconciliation matches for already-paid transactions
//       if (matchedCount > 0) {
//         const matchedTxns = await client.query(
//           `SELECT id, matched_grn_id, matched_amount FROM bank_transactions 
//            WHERE bank_statement_id = $1 AND matched_status = 'MATCHED'`,
//           [bankStatementId]
//         );
        
//         for (const txn of matchedTxns.rows) {
//           if (txn.matched_grn_id) {
//             await client.query(
//               `INSERT INTO reconciliation_matches 
//                (org_id, bank_transaction_id, grn_id, matched_amount, status, matched_by, matched_at)
//                VALUES ($1, $2, $3, $4, 'ACTIVE', $5, NOW())`,
//               [orgId, txn.id, txn.matched_grn_id, txn.matched_amount, userId]
//             );
//           }
//         }
//       }

//       await client.query("COMMIT");

//       // Fetch all inserted transactions for response
//       const transactionsResult = await client.query(
//         `SELECT id, transaction_ref, transaction_date, beneficiary_name, amount, transaction_type, matched_status
//          FROM bank_transactions
//          WHERE bank_statement_id = $1
//          ORDER BY transaction_date DESC`,
//         [bankStatementId]
//       );

//       console.log(`Upload complete: ${matchedCount} matched, ${unmatchedCount} unmatched`);

//       // Clean up uploaded file
//       try { 
//         fs.unlinkSync(req.file.path); 
//         console.log("Deleted temp file:", req.file.path);
//       } catch(e) { 
//         console.error("Error deleting temp file:", e); 
//       }

//       return res.status(200).json({
//         success: true,
//         message: `Bank statement uploaded: ${matchedCount} already matched, ${unmatchedCount} new transactions`,
//         data: {
//           statementId: bankStatementId,
//           fileName: req.file.originalname,
//           totalEntries: parsedTransactions.length,
//           matchedEntries: matchedCount,
//           unmatchedEntries: unmatchedCount,
//           transactions: transactionsResult.rows,
//         },
//       });

//     } catch (error) {
//       await client.query("ROLLBACK");
//       console.error("Upload Bank Statement Error:", error);
//       if (req.file?.path && fs.existsSync(req.file.path)) {
//         try { fs.unlinkSync(req.file.path); } catch(e) { console.error("Error deleting temp file:", e); }
//       }
      
//       // Handle password-related errors specifically
//       const errorMessage = error.message || '';
//       if (errorMessage.toLowerCase().includes('password') || errorMessage.toLowerCase().includes('encrypted')) {
//         return res.status(400).json({
//           success: false,
//           message: errorMessage,
//           needsPassword: true,
//         });
//       }
      
//       return res.status(500).json({
//         success: false,
//         message: "Failed to upload bank statement: " + errorMessage,
//         error: process.env.NODE_ENV === "development" ? errorMessage : undefined,
//       });
//     } finally {
//       client.release();
//     }
//   });
// };

// /* ═══════════════════════════════════════════════════════════════════════════
//    GET /bank-reconciliation/payables
// ═══════════════════════════════════════════════════════════════════════════ */
// const getReconciliationPayables = async (req, res) => {
//   try {
//     const orgId = req.user?.orgId || req.session?.user?.orgId;
//     if (!orgId) {
//       return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
//     }

//     const query = `
//       SELECT 
//         g.id AS grn_id,
//         g.grn_number,
//         g.grn_date,
//         g.po_number,
//         g.vendor_name,
//         g.final_invoice_amount,
//         COALESCE(g.payment_status, 'UNPAID') AS payment_status,
//         COALESCE(
//           (SELECT SUM(gi.accepted_qty * gi.unit_price) FROM grn_items gi WHERE gi.grn_id = g.id),
//           0
//         ) AS grn_amount,
//         COALESCE(
//           (SELECT SUM(at.amount) FROM ap_transactions at WHERE at.grn_id = g.id),
//           0
//         ) AS paid_amount,
//         COALESCE(
//           (SELECT SUM(gi.accepted_qty * gi.unit_price) FROM grn_items gi WHERE gi.grn_id = g.id) - 
//           COALESCE((SELECT SUM(at.amount) FROM ap_transactions at WHERE at.grn_id = g.id), 0),
//           0
//         ) AS remaining_amount,
//         CASE 
//           WHEN COALESCE((SELECT SUM(at.amount) FROM ap_transactions at WHERE at.grn_id = g.id), 0) >= 
//                COALESCE(g.final_invoice_amount, (SELECT SUM(gi.accepted_qty * gi.unit_price) FROM grn_items gi WHERE gi.grn_id = g.id), 0) 
//           THEN 'PAID'
//           WHEN COALESCE((SELECT SUM(at.amount) FROM ap_transactions at WHERE at.grn_id = g.id), 0) > 0 
//           THEN 'PARTIAL'
//           ELSE 'UNPAID'
//         END AS derived_status,
//         pv.vendor_code,
//         pv.phone AS vendor_phone,
//         pv.email AS vendor_email,
//         pv.address AS vendor_address,
//         pv.bank_name,
//         pv.account_number AS bank_account,
//         pv.ifsc_code AS bank_ifsc,
//         pv.branch AS bank_branch,
//         pv.contact_person AS vendor_contact_person,
//         COALESCE(
//           (SELECT json_agg(json_build_object(
//             'id', rm.id,
//             'ref', bt.transaction_ref,
//             'amount', rm.matched_amount,
//             'date', bt.transaction_date,
//             'mode', bt.transaction_type
//           ))
//           FROM reconciliation_matches rm
//           JOIN bank_transactions bt ON bt.id = rm.bank_transaction_id
//           WHERE rm.grn_id = g.id AND rm.status = 'ACTIVE'
//         ), '[]'::json) AS matched_transactions
//       FROM grn g
//       LEFT JOIN procurement_vendors pv ON pv.vendor_name = g.vendor_name AND pv.org_id = g.org_id AND pv.is_deleted = false
//       WHERE g.org_id = $1
//         AND g.is_deleted = false
//         AND COALESCE(g.grn_status, 'Draft') = 'Approved'
//       ORDER BY 
//         CASE 
//           WHEN COALESCE(g.payment_status, 'UNPAID') = 'PAID' THEN 1
//           ELSE 0
//         END,
//         g.grn_date DESC
//     `;

//     const result = await pool.query(query, [orgId]);

//     const rows = result.rows;
//     const summary = {
//       total_payables: rows.length,
//       total_amount: rows.reduce((sum, r) => sum + (parseFloat(r.grn_amount) || 0), 0),
//       total_paid: rows.reduce((sum, r) => sum + (parseFloat(r.paid_amount) || 0), 0),
//       total_remaining: rows.reduce((sum, r) => sum + (parseFloat(r.remaining_amount) || 0), 0),
//       paid_count: rows.filter(r => r.derived_status === 'PAID').length,
//       partial_count: rows.filter(r => r.derived_status === 'PARTIAL').length,
//       unpaid_count: rows.filter(r => r.derived_status === 'UNPAID').length,
//     };

//     return res.status(200).json({
//       success: true,
//       data: rows,
//       summary,
//     });

//   } catch (error) {
//     console.error("Get Reconciliation Payables Error:", error);
//     return res.status(500).json({
//       success: false,
//       message: "Failed to fetch payables",
//       error: process.env.NODE_ENV === "development" ? error.message : undefined,
//     });
//   }
// };

// /* ═══════════════════════════════════════════════════════════════════════════
//    GET /bank-reconciliation/transactions
// ═══════════════════════════════════════════════════════════════════════════ */
// const getBankTransactions = async (req, res) => {
//   try {
//     const { statementId } = req.params;
//     const orgId = req.user?.orgId || req.session?.user?.orgId;

//     if (!orgId) {
//       return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
//     }

//     let stmtId = statementId;
//     if (!stmtId) {
//       const stmtResult = await pool.query(
//         `SELECT id FROM bank_statements 
//          WHERE org_id = $1 AND status = 'PROCESSED' 
//          ORDER BY created_at DESC LIMIT 1`,
//         [orgId]
//       );
//       if (stmtResult.rows.length === 0) {
//         return res.status(200).json({ 
//           success: true, 
//           data: [], 
//           hasStatement: false,
//           message: "No bank statement found" 
//         });
//       }
//       stmtId = stmtResult.rows[0].id;
//     }

//     const result = await pool.query(
//       `SELECT 
//         bt.id,
//         bt.transaction_ref,
//         bt.transaction_date,
//         bt.beneficiary_name,
//         bt.amount,
//         bt.transaction_type,
//         bt.matched_status,
//         rm.id AS match_id,
//         rm.grn_id,
//         rm.matched_amount,
//         bt.description
//        FROM bank_transactions bt
//        LEFT JOIN reconciliation_matches rm ON rm.bank_transaction_id = bt.id AND rm.status = 'ACTIVE'
//        WHERE bt.bank_statement_id = $1 AND bt.org_id = $2
//        ORDER BY bt.transaction_date DESC`,
//       [stmtId, orgId]
//     );

//     const stmtInfo = await pool.query(
//       `SELECT id, file_name, statement_date, total_entries, created_at 
//        FROM bank_statements WHERE id = $1`,
//       [stmtId]
//     );

//     return res.status(200).json({
//       success: true,
//       data: result.rows,
//       statementInfo: stmtInfo.rows[0] || null,
//       hasStatement: true,
//     });

//   } catch (error) {
//     console.error("Get Bank Transactions Error:", error);
//     return res.status(500).json({
//       success: false,
//       message: "Failed to fetch bank transactions",
//       error: process.env.NODE_ENV === "development" ? error.message : undefined,
//     });
//   }
// };

// /* ═══════════════════════════════════════════════════════════════════════════
//    POST /bank-reconciliation/match
//    Match a bank transaction to a payable (GRN) with vendor validation
// ═══════════════════════════════════════════════════════════════════════════ */
// const matchTransaction = async (req, res) => {
//   const client = await pool.connect();
//   try {
//     const { transactionId, grnId, amount } = req.body;
//     const orgId = req.user?.orgId || req.session?.user?.orgId;
//     const userId = req.user?.id;

//     if (!orgId) {
//       return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
//     }

//     if (!transactionId || !grnId) {
//       return res.status(400).json({ success: false, message: "transactionId and grnId are required" });
//     }

//     const finalMatchAmount = parseFloat(amount);
//     if (isNaN(finalMatchAmount) || finalMatchAmount <= 0) {
//       return res.status(400).json({ success: false, message: "Valid amount is required" });
//     }

//     await client.query("BEGIN");

//     // Get transaction details
//     const txnResult = await client.query(
//       `SELECT bt.*, bs.org_id 
//        FROM bank_transactions bt
//        JOIN bank_statements bs ON bs.id = bt.bank_statement_id
//        WHERE bt.id = $1 AND bt.matched_status = 'UNMATCHED'`,
//       [transactionId]
//     );

//     if (txnResult.rows.length === 0) {
//       await client.query("ROLLBACK");
//       return res.status(404).json({ success: false, message: "Transaction not found or already matched" });
//     }

//     const transaction = txnResult.rows[0];
//     const transactionAmount = parseFloat(transaction.amount);

//     if (finalMatchAmount > transactionAmount) {
//       await client.query("ROLLBACK");
//       return res.status(400).json({ 
//         success: false, 
//         message: `Match amount (${finalMatchAmount}) cannot exceed transaction amount (${transactionAmount})` 
//       });
//     }

//     // Get GRN details with vendor name
//     const grnResult = await client.query(
//       `SELECT 
//         g.id, g.grn_number, g.po_id, g.po_number, g.vendor_name,
//         COALESCE(
//           (SELECT SUM(gi.accepted_qty * gi.unit_price) FROM grn_items gi WHERE gi.grn_id = g.id),
//           0
//         ) AS grn_amount,
//         COALESCE(
//           (SELECT SUM(at.amount) FROM ap_transactions at WHERE at.grn_id = g.id),
//           0
//         ) AS current_paid
//        FROM grn g
//        WHERE g.id = $1 AND g.org_id = $2 AND g.is_deleted = false`,
//       [grnId, orgId]
//     );

//     if (grnResult.rows.length === 0) {
//       await client.query("ROLLBACK");
//       return res.status(404).json({ success: false, message: "GRN not found" });
//     }

//     const grn = grnResult.rows[0];
    
//     // ─────────────────────────────────────────────────────────────────
//     // CRITICAL: Validate vendor match between transaction and GRN
//     // Check if the transaction beneficiary matches the GRN vendor
//     // This prevents matching any payment to any payable
//     // ─────────────────────────────────────────────────────────────────
//     const transactionBeneficiary = (transaction.beneficiary_name || "").toLowerCase().trim();
//     const grnVendorName = (grn.vendor_name || "").toLowerCase().trim();
    
//     // Check for vendor name match (either exact or partial)
//     const isVendorMatch = 
//       transactionBeneficiary.includes(grnVendorName) || 
//       grnVendorName.includes(transactionBeneficiary) ||
//       // Check for common variations (e.g., "Tata Steel Ltd" vs "TATA STEEL LIMITED")
//       transactionBeneficiary.replace(/[^a-z0-9]/g, '') === grnVendorName.replace(/[^a-z0-9]/g, '');
    
//     if (!isVendorMatch) {
//       await client.query("ROLLBACK");
//       return res.status(400).json({ 
//         success: false, 
//         message: `Cannot match: Transaction beneficiary "${transaction.beneficiary_name}" does not match GRN vendor "${grn.vendor_name}". Only payments to the same vendor can be matched.`,
//         vendorMismatch: true,
//         transactionBeneficiary: transaction.beneficiary_name,
//         grnVendorName: grn.vendor_name
//       });
//     }
    
//     console.log(`✓ Vendor match validated: "${transaction.beneficiary_name}" ≈ "${grn.vendor_name}"`);
    
//     const newPaidAmount = parseFloat(grn.current_paid) + finalMatchAmount;
//     const totalAmount = parseFloat(grn.grn_amount);
    
//     let newStatus = "PARTIAL";
//     if (newPaidAmount <= 0) newStatus = "UNPAID";
//     else if (newPaidAmount >= totalAmount) newStatus = "PAID";
//     else newStatus = "PARTIAL";

//     // Insert reconciliation match
//     await client.query(
//       `INSERT INTO reconciliation_matches 
//        (org_id, bank_transaction_id, grn_id, po_id, matched_amount, status, matched_by, matched_at)
//        VALUES ($1, $2, $3, $4, $5, 'ACTIVE', $6, NOW())`,
//       [orgId, transactionId, grnId, grn.po_id, finalMatchAmount, userId]
//     );

//     let newMatchedStatus = 'MATCHED';
//     if (finalMatchAmount < transactionAmount) {
//       newMatchedStatus = 'PARTIAL';
//     }

//     // Update bank transaction
//     await client.query(
//       `UPDATE bank_transactions 
//        SET matched_status = $1,
//            matched_grn_id = $2,
//            matched_amount = $3::numeric
//        WHERE id = $4`,
//       [newMatchedStatus, grnId, finalMatchAmount, transactionId]
//     );

//     // Insert AP transaction record
//     await client.query(
//       `INSERT INTO ap_transactions 
//        (grn_id, amount, txn_date, payment_mode, note, created_at)
//        VALUES ($1, $2, $3, $4, $5, NOW())`,
//       [grnId, finalMatchAmount, transaction.transaction_date, transaction.transaction_type, 
//        `Matched from bank statement: ${transaction.transaction_ref} - Beneficiary: ${transaction.beneficiary_name}`]
//     );

//     // Update GRN payment status
//     await client.query(
//       `UPDATE grn 
//        SET payment_status = $1, updated_at = NOW()
//        WHERE id = $2`,
//       [newStatus, grnId]
//     );

//     await client.query("COMMIT");

//     return res.status(200).json({
//       success: true,
//       message: `Successfully matched ₹${finalMatchAmount.toLocaleString()} to ${grn.grn_number} (${grn.vendor_name})`,
//       data: {
//         grnId,
//         grnNumber: grn.grn_number,
//         vendorName: grn.vendor_name,
//         newStatus,
//         paidAmount: newPaidAmount,
//         remainingAmount: totalAmount - newPaidAmount,
//       },
//     });

//   } catch (error) {
//     await client.query("ROLLBACK");
//     console.error("Match Transaction Error:", error);
//     return res.status(500).json({ 
//       success: false, 
//       message: "Failed to match transaction",
//       error: process.env.NODE_ENV === "development" ? error.message : undefined,
//     });
//   } finally {
//     client.release();
//   }
// };

// /* ═══════════════════════════════════════════════════════════════════════════
//    DELETE /bank-reconciliation/match/:matchId
// ═══════════════════════════════════════════════════════════════════════════ */
// const cancelMatch = async (req, res) => {
//   const client = await pool.connect();
//   try {
//     const { matchId } = req.params;
//     const orgId = req.user?.orgId || req.session?.user?.orgId;
//     const userId = req.user?.id;

//     await client.query("BEGIN");

//     const matchResult = await client.query(
//       `SELECT rm.*, bt.id AS bank_txn_id, bt.amount AS txn_amount, bt.transaction_ref,
//               bt.matched_status as current_bt_status,
//               g.id AS grn_id, g.grn_number
//        FROM reconciliation_matches rm
//        JOIN bank_transactions bt ON bt.id = rm.bank_transaction_id
//        JOIN grn g ON g.id = rm.grn_id
//        WHERE rm.id = $1 AND rm.org_id = $2 AND rm.status = 'ACTIVE'`,
//       [matchId, orgId]
//     );

//     if (matchResult.rows.length === 0) {
//       await client.query("ROLLBACK");
//       return res.status(404).json({ success: false, message: "Match not found" });
//     }

//     const match = matchResult.rows[0];

//     await client.query(
//       `UPDATE reconciliation_matches 
//        SET status = 'CANCELLED', cancelled_by = $1, cancelled_at = NOW()
//        WHERE id = $2`,
//       [userId, matchId]
//     );

//     const otherMatches = await client.query(
//       `SELECT COUNT(*) as count, 
//               SUM(matched_amount) as total_matched
//        FROM reconciliation_matches 
//        WHERE bank_transaction_id = $1 
//          AND status = 'ACTIVE' 
//          AND id != $2`,
//       [match.bank_txn_id, matchId]
//     );

//     const otherMatchesCount = parseInt(otherMatches.rows[0].count);
//     const otherMatchesTotal = parseFloat(otherMatches.rows[0].total_matched || 0);
//     const transactionAmount = parseFloat(match.txn_amount);

//     let newMatchedStatus = 'UNMATCHED';
//     let newMatchedAmount = null;
//     let newMatchedGrnId = null;

//     if (otherMatchesCount > 0) {
//       if (otherMatchesTotal >= transactionAmount) {
//         newMatchedStatus = 'MATCHED';
//       } else {
//         newMatchedStatus = 'PARTIAL';
//       }
//       newMatchedAmount = otherMatchesTotal;
//       const remainingMatch = await client.query(
//         `SELECT grn_id FROM reconciliation_matches 
//          WHERE bank_transaction_id = $1 AND status = 'ACTIVE' 
//          LIMIT 1`,
//         [match.bank_txn_id]
//       );
//       if (remainingMatch.rows.length > 0) {
//         newMatchedGrnId = remainingMatch.rows[0].grn_id;
//       }
//     }

//     await client.query(
//       `UPDATE bank_transactions 
//        SET matched_status = $1,
//            matched_grn_id = $2,
//            matched_amount = $3
//        WHERE id = $4`,
//       [newMatchedStatus, newMatchedGrnId, newMatchedAmount, match.bank_txn_id]
//     );

//     await client.query(
//       `DELETE FROM ap_transactions 
//        WHERE grn_id = $1 
//          AND note ILIKE $2
//          AND amount = $3`,
//       [match.grn_id, `%${match.transaction_ref}%`, match.matched_amount]
//     );

//     const paidResult = await client.query(
//       `SELECT COALESCE(SUM(amount), 0) AS total_paid
//        FROM ap_transactions
//        WHERE grn_id = $1`,
//       [match.grn_id]
//     );

//     const totalPaid = parseFloat(paidResult.rows[0].total_paid);
//     const grnResult = await client.query(
//       `SELECT COALESCE(
//         (SELECT SUM(gi.accepted_qty * gi.unit_price) FROM grn_items gi WHERE gi.grn_id = g.id),
//         0
//        ) AS grn_amount
//        FROM grn g
//        WHERE g.id = $1`,
//       [match.grn_id]
//     );
    
//     const grnAmount = parseFloat(grnResult.rows[0].grn_amount);
//     let newStatus = "UNPAID";
//     if (totalPaid <= 0) newStatus = "UNPAID";
//     else if (totalPaid >= grnAmount) newStatus = "PAID";
//     else newStatus = "PARTIAL";

//     await client.query(
//       `UPDATE grn SET payment_status = $1, updated_at = NOW() WHERE id = $2`,
//       [newStatus, match.grn_id]
//     );

//     await client.query("COMMIT");

//     return res.status(200).json({
//       success: true,
//       message: `Match cancelled for ${match.grn_number}. Transaction is now ${newMatchedStatus}.`,
//     });

//   } catch (error) {
//     await client.query("ROLLBACK");
//     console.error("Cancel Match Error:", error);
//     return res.status(500).json({ success: false, message: "Failed to cancel match" });
//   } finally {
//     client.release();
//   }
// };

// /* ═══════════════════════════════════════════════════════════════════════════
//    GET /bank-reconciliation/summary
// ═══════════════════════════════════════════════════════════════════════════ */
// const getReconciliationSummary = async (req, res) => {
//   try {
//     const orgId = req.user?.orgId || req.session?.user?.orgId;
//     if (!orgId) {
//       return res.status(401).json({ success: false, message: "Unauthorized" });
//     }

//     const result = await pool.query(
//       `SELECT 
//         COUNT(DISTINCT bs.id) AS total_statements,
//         COUNT(bt.id) AS total_transactions,
//         COUNT(CASE WHEN bt.matched_status = 'MATCHED' THEN 1 END) AS matched_transactions,
//         COUNT(CASE WHEN bt.matched_status = 'PARTIAL' THEN 1 END) AS partial_transactions,
//         COUNT(CASE WHEN bt.matched_status = 'UNMATCHED' THEN 1 END) AS unmatched_transactions,
//         COALESCE(SUM(CASE WHEN bt.matched_status != 'UNMATCHED' THEN bt.matched_amount ELSE 0 END), 0) AS total_matched_amount,
//         COALESCE(SUM(bt.amount), 0) AS total_statement_amount
//        FROM bank_statements bs
//        JOIN bank_transactions bt ON bt.bank_statement_id = bs.id
//        WHERE bs.org_id = $1 AND bs.status = 'PROCESSED'`,
//       [orgId]
//     );

//     return res.status(200).json({
//       success: true,
//       data: result.rows[0],
//     });

//   } catch (error) {
//     console.error("Get Reconciliation Summary Error:", error);
//     return res.status(500).json({ success: false, message: "Failed to fetch summary" });
//   }
// };

// module.exports = {
//   uploadBankStatement,
//   getReconciliationPayables,
//   getBankTransactions,
//   matchTransaction,
//   cancelMatch,
//   getReconciliationSummary,

// };

const { pool } = require("../../config/db");
const multer = require("multer");
const path = require("path");
const fs = require("fs");
const pdfParse = require("pdf-parse");

/* ─── Ensure upload directory exists ──────────────────────────────────────── */
const uploadDir = path.join(__dirname, "../../uploads/bank_statements");
if (!fs.existsSync(uploadDir)) {
  fs.mkdirSync(uploadDir, { recursive: true });
  console.log("Created upload directory:", uploadDir);
}

/* ─── Multer setup for bank statement uploads ──────────────────────────────── */
const storage = multer.diskStorage({
  destination: (req, file, cb) => {
    cb(null, uploadDir);
  },
  filename: (req, file, cb) => {
    const ext = path.extname(file.originalname);
    const timestamp = Date.now();
    cb(null, `bank_stmt_${timestamp}${ext}`);
  },
});

const upload = multer({ 
  storage, 
  limits: { fileSize: 20 * 1024 * 1024 }
});
const uploadMiddleware = upload.single("bankStatement");

/* ─── Helper: Check if PDF is encrypted ─── */
function checkIfPDFEncrypted(buffer) {
  try {
    // Convert buffer to string and check PDF header for encryption flags
    const header = buffer.toString('binary', 0, 2048);
    
    // Check for common PDF encryption indicators
    const encryptionIndicators = [
      '/Encrypt',
      '/Encryption',
      '/Filter /Standard',
      '/V /',  // Encryption version
      '/R ',   // Revision number
    ];
    
    for (const indicator of encryptionIndicators) {
      if (header.includes(indicator)) {
        console.log(`Found encryption indicator: ${indicator}`);
        return true;
      }
    }
    
    // Also check PDF trailer for encryption
    const trailer = buffer.toString('binary', Math.max(0, buffer.length - 2048), buffer.length);
    if (trailer.includes('/Encrypt') || trailer.includes('/Encryption')) {
      console.log('Found encryption in PDF trailer');
      return true;
    }
    
    return false;
  } catch (error) {
    console.error("Error checking PDF encryption:", error);
    return false;
  }
}

/* ─── Helper: Parse Canara Bank Statement PDF ─── */
const parseCanaraStatement = (text) => {
  const transactions = [];
  const lines = text.split('\n');
  
  console.log("Parsing Canara Bank statement...");
  
  let currentDate = null;
  let currentParticulars = "";
  let currentDeposit = "";
  let currentWithdrawal = "";
  let currentBalance = "";
  let isInTransaction = false;
  
  // Helper function to truncate text to max length
  const truncate = (str, maxLen = 200) => {
    if (!str) return "";
    return str.length > maxLen ? str.substring(0, maxLen) : str;
  };
  
  for (let i = 0; i < lines.length; i++) {
    const line = lines[i].trim();
    if (!line) continue;
    
    // Skip header/footer lines
    if (line.includes("Statement for A/c") || 
        line.includes("Customer Id") ||
        line.includes("Branch Code") ||
        line.includes("Branch Name") ||
        line.includes("IFSC Code") ||
        line.includes("Opening Balance") ||
        line.includes("Closing Balance") ||
        line.includes("Page") ||
        line.includes("Canara Bank") ||
        line.includes("CANARA BANK")) {
      continue;
    }
    
    // Check for date pattern (DD-MM-YYYY)
    const dateMatch = line.match(/^(\d{2}-\d{2}-\d{4})/);
    
    if (dateMatch) {
      // If we have a previous transaction, save it
      if (currentDate && (currentDeposit || currentWithdrawal)) {
        let amount = 0;
        let isDebit = true;
        let beneficiary = truncate(currentParticulars.trim(), 200);
        
        if (currentDeposit && parseFloat(currentDeposit) > 0) {
          amount = parseFloat(currentDeposit.replace(/,/g, ''));
          isDebit = false; // Deposit is credit
        } else if (currentWithdrawal && parseFloat(currentWithdrawal) > 0) {
          amount = parseFloat(currentWithdrawal.replace(/,/g, ''));
          isDebit = true; // Withdrawal is debit
        }
        
        if (amount > 0 && beneficiary) {
          // Clean up beneficiary name - remove UPI IDs and long codes
          beneficiary = beneficiary
            .replace(/UPI\/DR\/\d+\//gi, '')
            .replace(/UPI\/CR\/\d+\//gi, '')
            .replace(/Chq:\s*\d+/gi, '')
            .replace(/[A-Z0-9]{20,}/g, '') // Remove long alphanumeric codes
            .replace(/\//g, ' ')
            .replace(/\s+/g, ' ')
            .trim();
          
          // Further truncate if needed (max 200 chars)
          beneficiary = truncate(beneficiary, 200);
          
          // Extract transaction type
          let type = "UPI";
          if (beneficiary.toLowerCase().includes("neft")) type = "NEFT";
          else if (beneficiary.toLowerCase().includes("rtgs")) type = "RTGS";
          else if (beneficiary.toLowerCase().includes("imps")) type = "IMPS";
          else if (beneficiary.toLowerCase().includes("cheque")) type = "CHEQUE";
          
          // Parse date from DD-MM-YYYY to YYYY-MM-DD
          const [day, month, year] = currentDate.split('-');
          const formattedDate = `${year}-${month}-${day}`;
          
          // Generate a shorter reference
          const ref = `UPI-${formattedDate.replace(/-/g, '')}-${Math.floor(amount)}`;
          
          transactions.push({
            date: formattedDate,
            ref: truncate(ref, 100),
            beneficiary: beneficiary,
            amount: amount,
            type: type,
            isDebit: isDebit,
            narration: truncate(currentParticulars, 500)
          });
          
          console.log(`Parsed Canara transaction: ${formattedDate} | ${beneficiary.substring(0, 50)} | ${amount} | ${isDebit ? "DEBIT" : "CREDIT"}`);
        }
      }
      
      // Start new transaction
      currentDate = dateMatch[1];
      currentParticulars = "";
      currentDeposit = "";
      currentWithdrawal = "";
      currentBalance = "";
      isInTransaction = true;
      
      // The rest of the line after date contains particulars
      let remaining = line.substring(dateMatch[0].length).trim();
      if (remaining && !remaining.match(/^\d/)) {
        currentParticulars = remaining;
      }
      
    } else if (isInTransaction) {
      // Check if this line contains deposit amount (positive number, usually with commas)
      const depositMatch = line.match(/^([\d,]+\.\d{2})$/);
      // Check if this line contains withdrawal amount
      const withdrawalMatch = line.match(/^([\d,]+\.\d{2})$/);
      // Check if line contains balance
      const balanceMatch = line.match(/([\d,]+\.\d{2})$/);
      
      // Look for amounts in the line
      const amountMatch = line.match(/([\d,]+\.\d{2})/g);
      
      if (amountMatch) {
        // Canara format often has: Particulars (multiline) then amount, then balance
        // The amounts appear in order: Withdrawal/Deposit, then Balance
        if (amountMatch.length === 1) {
          // Single amount - could be withdrawal/deposit or balance
          // Check previous line to determine
          if (currentParticulars && !currentWithdrawal && !currentDeposit) {
            // This is likely the transaction amount
            const amount = parseFloat(amountMatch[0].replace(/,/g, ''));
            // Check if it's a deposit (CR) or withdrawal (DR)
            if (line.toLowerCase().includes("cr") || currentParticulars.toLowerCase().includes("credit")) {
              currentDeposit = amountMatch[0];
            } else {
              currentWithdrawal = amountMatch[0];
            }
          } else {
            // This is likely the balance
            currentBalance = amountMatch[0];
          }
        } else if (amountMatch.length >= 2) {
          // Multiple amounts - first is transaction amount, second is balance
          const transactionAmount = amountMatch[0];
          currentBalance = amountMatch[1];
          
          // Determine if deposit or withdrawal
          if (line.toLowerCase().includes("cr") || currentParticulars.toLowerCase().includes("credit")) {
            currentDeposit = transactionAmount;
          } else {
            currentWithdrawal = transactionAmount;
          }
        }
      }
      
      // Accumulate particulars (multiline) but limit length
      if (!line.match(/^[\d,]+\.\d{2}/) && !line.match(/^\d{2}-\d{2}-\d{4}/)) {
        if (currentParticulars) {
          currentParticulars += " " + line;
        } else {
          currentParticulars = line;
        }
        // Limit particulars length to avoid overflow
        if (currentParticulars.length > 1000) {
          currentParticulars = currentParticulars.substring(0, 1000);
        }
      }
    }
  }
  
  // Don't forget the last transaction
  if (currentDate && (currentDeposit || currentWithdrawal)) {
    let amount = 0;
    let isDebit = true;
    let beneficiary = currentParticulars ? currentParticulars.trim() : "";
    
    if (currentDeposit && parseFloat(currentDeposit) > 0) {
      amount = parseFloat(currentDeposit.replace(/,/g, ''));
      isDebit = false;
    } else if (currentWithdrawal && parseFloat(currentWithdrawal) > 0) {
      amount = parseFloat(currentWithdrawal.replace(/,/g, ''));
      isDebit = true;
    }
    
    if (amount > 0 && beneficiary) {
      beneficiary = beneficiary
        .replace(/UPI\/DR\/\d+\//gi, '')
        .replace(/UPI\/CR\/\d+\//gi, '')
        .replace(/Chq:\s*\d+/gi, '')
        .replace(/[A-Z0-9]{20,}/g, '')
        .replace(/\//g, ' ')
        .replace(/\s+/g, ' ')
        .trim()
        .substring(0, 200);
      
      let type = "UPI";
      if (beneficiary.toLowerCase().includes("neft")) type = "NEFT";
      else if (beneficiary.toLowerCase().includes("rtgs")) type = "RTGS";
      else if (beneficiary.toLowerCase().includes("imps")) type = "IMPS";
      else if (beneficiary.toLowerCase().includes("cheque")) type = "CHEQUE";
      
      const [day, month, year] = currentDate.split('-');
      const formattedDate = `${year}-${month}-${day}`;
      const ref = `UPI-${formattedDate.replace(/-/g, '')}-${Math.floor(amount)}`;
      
      transactions.push({
        date: formattedDate,
        ref: ref.substring(0, 100),
        beneficiary: beneficiary,
        amount: amount,
        type: type,
        isDebit: isDebit,
        narration: (currentParticulars || "").substring(0, 500)
      });
    }
  }
  
  return transactions;
};

/* ─── Helper: Parse HDFC Bank Statement PDF (improved for your format) ─── */
const parseHDFCStatement = (text) => {
  const transactions = [];
  
  // Split into lines
  const lines = text.split('\n');
  
  let i = 0;
  while (i < lines.length) {
    const line = lines[i].trim();
    
    // Skip empty lines and header/footer lines
    if (!line || 
        line.includes("Account Statement") || 
        line.includes("Account Summary") ||
        line.includes("Transaction Details") ||
        line.includes("Opening Balance") ||
        line.includes("Closing Balance") ||
        line.includes("Page") ||
        line.includes("HDFC BANK") ||
        line.includes("HDFC Bank") ||
        line.includes("Balance")) {
      i++;
      continue;
    }
    
    // Check if line starts with a date (DD MMM YYYY)
    const dateMatch = line.match(/^(\d{2}\s+(?:Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)\s+\d{4})/i);
    
    if (dateMatch) {
      const dateStr = dateMatch[1];
      const [day, month, year] = dateStr.split(' ');
      const monthMap = {
        'Jan': '01', 'Feb': '02', 'Mar': '03', 'Apr': '04', 'May': '05', 'Jun': '06',
        'Jul': '07', 'Aug': '08', 'Sep': '09', 'Oct': '10', 'Nov': '11', 'Dec': '12'
      };
      const formattedDate = `${year}-${monthMap[month]}-${day.padStart(2, '0')}`;
      
      // Get the remaining part after date
      let remaining = line.substring(dateMatch[0].length).trim();
      
      // Extract reference number
      let ref = "";
      const refMatch = remaining.match(/^([A-Z0-9\/]+)/i);
      if (refMatch) {
        ref = refMatch[1];
        remaining = remaining.substring(refMatch[0].length).trim();
      }
      
      // Check if next line contains continuation of reference (like "43" on next line)
      if (i + 1 < lines.length) {
        const nextLine = lines[i + 1].trim();
        if (nextLine.match(/^\d+$/) && ref) {
          ref += nextLine;
          i++;
          if (i + 1 < lines.length) {
            remaining = lines[i + 1].trim();
          } else {
            remaining = "";
          }
        }
      }
      
      // Look for transaction type (NEFT, RTGS, IMPS, CHEQUE)
      let type = "";
      const typeMatch = remaining.match(/^(NEFT|RTGS|IMPS|CHEQUE)/i);
      if (typeMatch) {
        type = typeMatch[1];
        remaining = remaining.substring(typeMatch[0].length).trim();
      }
      
      // Check for OUTWARD/INWARD direction
      let direction = "";
      const directionMatch = remaining.match(/^(OUTWARD|INWARD)/i);
      if (directionMatch) {
        direction = directionMatch[1];
        remaining = remaining.substring(directionMatch[0].length).trim();
      }
      
      // Remove dash if present
      remaining = remaining.replace(/^-\s*/, '');
      
      // Find amount - look for pattern like 1,25,000.00 or 1,25,000.00
      let amount = 0;
      let amountStr = "";
      let beneficiary = "";
      
      // Try to find amount in current remaining text
      const amountMatch = remaining.match(/(\d{1,3}(?:,\d{3})*\.\d{2})$/);
      if (amountMatch) {
        amountStr = amountMatch[1];
        beneficiary = remaining.substring(0, remaining.length - amountMatch[0].length).trim();
      } else {
        // Amount might be on next line(s)
        let lookAhead = i + 1;
        let beneficiaryParts = [remaining];
        
        while (lookAhead < lines.length && lookAhead < i + 5) {
          const nextLine = lines[lookAhead].trim();
          if (!nextLine) {
            lookAhead++;
            continue;
          }
          
          const amountCheck = nextLine.match(/(\d{1,3}(?:,\d{3})*\.\d{2})$/);
          if (amountCheck) {
            amountStr = amountCheck[1];
            // Add this line's content before the amount
            const lineContent = nextLine.substring(0, nextLine.length - amountCheck[0].length).trim();
            if (lineContent) {
              beneficiaryParts.push(lineContent);
            }
            i = lookAhead;
            break;
          } else {
            beneficiaryParts.push(nextLine);
            lookAhead++;
          }
        }
        
        beneficiary = beneficiaryParts.join(' ').trim();
        // Remove amount from beneficiary if found
        if (amountStr) {
          beneficiary = beneficiary.replace(amountStr, '').trim();
        }
      }
      
      // If still no amount, try to find any number pattern
      if (!amountStr) {
        const anyAmountMatch = remaining.match(/(\d{1,3}(?:,\d{3})*\.\d{2})/);
        if (anyAmountMatch) {
          amountStr = anyAmountMatch[1];
          beneficiary = remaining.replace(amountStr, '').trim();
        }
      }
      
      if (amountStr) {
        amount = parseFloat(amountStr.replace(/,/g, ''));
      }
      
      // Skip credit transactions (CREDIT) or amounts that don't make sense
      if (amount === 0 || line.includes("CREDIT") || type === "CREDIT") {
        i++;
        continue;
      }
      
      // Clean up beneficiary name
      if (beneficiary) {
        beneficiary = beneficiary
          .replace(/A\/C:\s*\d+/gi, '')
          .replace(/IFSC:\s*[A-Z0-9]+/gi, '')
          .replace(/REM:\s*[A-Z0-9-]+/gi, '')
          .replace(/REF:\s*[A-Z0-9-]+/gi, '')
          .replace(/\|/g, '')
          .replace(/\s+/g, ' ')
          .trim();
      }
      
      // Only add if we have a valid amount and beneficiary
      if (amount > 0 && beneficiary && beneficiary.length > 0) {
        // Determine if debit (OUTWARD) or credit
        const isDebit = direction === "OUTWARD" || (!direction && !line.includes("CREDIT"));
        
        transactions.push({
          date: formattedDate,
          ref: ref || `TX-${Date.now()}-${transactions.length}`,
          beneficiary: beneficiary,
          amount: amount,
          type: type || (isDebit ? "DEBIT" : "CREDIT"),
          isDebit: isDebit,
          narration: line
        });
        
        console.log(`Parsed transaction: ${formattedDate} | ${beneficiary} | ${amount}`);
      }
    }
    
    i++;
  }
  
  return transactions;
};

/* ─── Helper: Parse ICICI Bank Statement PDF ─── */
const parseICICIStatement = (text) => {
  const transactions = [];
  const lines = text.split('\n');
  
  let currentDate = null;
  
  for (const line of lines) {
    const trimmed = line.trim();
    if (!trimmed) continue;
    
    // Skip header/footer lines
    if (trimmed.includes("Account Statement") || 
        trimmed.includes("Account Summary") ||
        trimmed.includes("Transaction Details") ||
        trimmed.includes("Opening Balance") ||
        trimmed.includes("Closing Balance") ||
        trimmed.includes("Page") ||
        trimmed.includes("ICICI Bank")) {
      continue;
    }
    
    // Extract date if present
    const dateMatch = trimmed.match(/^(\d{2}\s+(?:Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)\s+\d{4})/i);
    if (dateMatch) {
      const dateStr = dateMatch[1];
      const [day, month, year] = dateStr.split(' ');
      const monthMap = {
        'Jan': '01', 'Feb': '02', 'Mar': '03', 'Apr': '04', 'May': '05', 'Jun': '06',
        'Jul': '07', 'Aug': '08', 'Sep': '09', 'Oct': '10', 'Nov': '11', 'Dec': '12'
      };
      currentDate = `${year}-${monthMap[month]}-${day.padStart(2, '0')}`;
      continue;
    }
    
    if (!currentDate) continue;
    
    // Look for debit/credit patterns
    const debitMatch = trimmed.match(/(\d{1,3}(?:,\d{3})*\.\d{2})\s*$/) || trimmed.match(/DEBIT.*?(\d{1,3}(?:,\d{3})*\.\d{2})/i);
    const creditMatch = trimmed.match(/CREDIT.*?(\d{1,3}(?:,\d{3})*\.\d{2})/i);
    
    let amount = 0;
    let isDebit = true;
    let amountStr = "";
    
    if (debitMatch) {
      amountStr = debitMatch[1];
      amount = parseFloat(amountStr.replace(/,/g, ''));
      isDebit = true;
    } else if (creditMatch) {
      amountStr = creditMatch[1];
      amount = parseFloat(amountStr.replace(/,/g, ''));
      isDebit = false;
    }
    
    if (amount === 0) continue;
    
    // Extract reference
    const refMatch = trimmed.match(/^([A-Z0-9\/]+)/i);
    let ref = refMatch ? refMatch[1] : "";
    
    // Extract beneficiary (remove ref, amount, and common patterns)
    let beneficiary = trimmed
      .replace(ref, '')
      .replace(amountStr, '')
      .replace(/DEBIT|CREDIT|NEFT|RTGS|IMPS|CHEQUE/gi, '')
      .replace(/A\/C:\s*\d+/gi, '')
      .replace(/IFSC:\s*[A-Z0-9]+/gi, '')
      .replace(/\|/g, '')
      .replace(/\s+/g, ' ')
      .trim();
    
    if (beneficiary && amount > 0) {
      transactions.push({
        date: currentDate,
        ref: ref || `TX-${Date.now()}-${transactions.length}`,
        beneficiary: beneficiary,
        amount: amount,
        type: isDebit ? "DEBIT" : "CREDIT",
        isDebit: isDebit,
        narration: trimmed
      });
      
      console.log(`Parsed ICICI transaction: ${currentDate} | ${beneficiary} | ${amount}`);
    }
  }
  
  return transactions;
};

/* ─── Helper: Parse bank statement PDF with optional password ──────────── */
const parseBankStatementPDF = async (fileBuffer, fileName, password = null) => {
  console.log("=== Starting PDF Parse ===");
  console.log("File name:", fileName);
  console.log("Password provided:", password ? "Yes (length: " + password.length + ")" : "No");
  console.log("File buffer size:", fileBuffer.length, "bytes");
  
  // Debug: Check PDF header
  const pdfHeader = fileBuffer.slice(0, 8).toString();
  console.log("PDF Header (first 8 bytes):", pdfHeader);
  if (!pdfHeader.startsWith('%PDF-')) {
    console.error("WARNING: File does not appear to be a valid PDF!");
    throw new Error('Invalid file format. Please upload a valid PDF file.');
  }
  
  try {
    let data;
    
    // First, check if PDF is actually encrypted
    const isEncrypted = checkIfPDFEncrypted(fileBuffer);
    console.log("PDF encryption detection result:", isEncrypted);
    
    // IMPORTANT: Only try with password if PDF is actually encrypted AND password is provided
    if (isEncrypted) {
      if (!password || password.trim() === "") {
        throw new Error('PDF is encrypted. Please provide the password to open this file.');
      }
      
      console.log("Attempting to decrypt PDF with provided password...");
      try {
        // Try with password - pdf-parse expects password as option
        data = await pdfParse(fileBuffer, { password: password.trim() });
        console.log("Successfully decrypted PDF with password");
      } catch (pwdError) {
        console.error("PDF decryption error details:", pwdError);
        console.error("PDF decryption error message:", pwdError.message);
        
        // Check specific error types
        if (pwdError.message && (
          pwdError.message.toLowerCase().includes('password') ||
          pwdError.message.toLowerCase().includes('encrypted')
        )) {
          throw new Error('Invalid PDF password. Please check and try again.');
        }
        throw pwdError;
      }
    } else {
      // PDF is not encrypted - parse normally without password
      console.log("PDF is not encrypted, parsing normally...");
      try {
        data = await pdfParse(fileBuffer);
        console.log("Successfully parsed unencrypted PDF");
      } catch (parseError) {
        console.error("PDF parse error on unencrypted file:", parseError.message);
        
        // If we get a password error on an apparently unencrypted file,
        // it might be a false positive - try one more time with empty password
        if (parseError.message && parseError.message.toLowerCase().includes('password')) {
          console.log("Got password error on unencrypted file, retrying with empty password...");
          try {
            data = await pdfParse(fileBuffer, { password: '' });
            console.log("Successfully parsed with empty password");
          } catch (retryError) {
            console.error("Retry also failed:", retryError.message);
            throw retryError;
          }
        } else {
          throw parseError;
        }
      }
    }
    
    const text = data.text;
    console.log("PDF Text length:", text.length);
    console.log("First 1500 chars:", text.substring(0, 1500));
    
    if (!text || text.length === 0) {
      throw new Error('PDF contains no extractable text. The file might be scanned or image-based.');
    }
    
    let transactions = [];
    
    // Detect bank type based on filename or content
    const fileNameLower = fileName.toLowerCase();
    const textLower = text.toLowerCase();
    
    const isHDFC = fileNameLower.includes("hdfc") || textLower.includes("hdfc bank");
    const isICICI = fileNameLower.includes("icici") || textLower.includes("icici bank");
    const isCanara = fileNameLower.includes("canara") || textLower.includes("canara bank") || textLower.includes("cnrb");
    
    if (isHDFC) {
      console.log("Detected HDFC Bank statement");
      transactions = parseHDFCStatement(text);
    } else if (isICICI) {
      console.log("Detected ICICI Bank statement");
      transactions = parseICICIStatement(text);
    } else if (isCanara) {
      console.log("Detected Canara Bank statement");
      transactions = parseCanaraStatement(text);
    } else {
      console.log("Unknown bank type, trying all parsers...");
      // Try HDFC first
      transactions = parseHDFCStatement(text);
      if (transactions.length === 0) {
        console.log("HDFC parser found nothing, trying ICICI parser");
        transactions = parseICICIStatement(text);
      }
      if (transactions.length === 0) {
        console.log("ICICI parser found nothing, trying Canara parser");
        transactions = parseCanaraStatement(text);
      }
    }
    
    console.log(`Parsed ${transactions.length} transactions from PDF`);
    
    // Log first few transactions for debugging
    if (transactions.length > 0) {
      console.log("Sample transaction:", JSON.stringify(transactions[0], null, 2));
    } else {
      console.log("WARNING: No transactions parsed. PDF content sample:", text.substring(0, 500));
    }
    
    return transactions;
    
  } catch (error) {
    console.error("PDF Parse Error:", error);
    throw error;
  }
};

/* ─── Helper: Parse UPI transactions from any bank statement ─── */
const parseUPITransaction = (text) => {
  const transactions = [];
  
  // Pattern for UPI transactions with amounts
  // Looking for patterns like: "UPI/DR/696577264162/FLIPKART" followed by amount
  const upiPatterns = [
    /UPI\/(DR|CR)\/\d+\/([A-Za-z0-9\s\/]+?)(?:\d{1,3}(?:,\d{3})*\.\d{2})/gi,
    /UPI\/(DR|CR)\/\d+\/([^\n]+)\n\s*([\d,]+\.\d{2})/gi,
  ];
  
  const lines = text.split('\n');
  let i = 0;
  
  while (i < lines.length) {
    const line = lines[i];
    
    // Look for UPI transaction marker
    if (line.includes("UPI/DR") || line.includes("UPI/CR")) {
      const isDebit = line.includes("UPI/DR");
      
      // Extract beneficiary - look for the merchant name
      let beneficiary = "";
      const upiMatch = line.match(/UPI\/(?:DR|CR)\/\d+\/([A-Za-z\s\/]+)/i);
      if (upiMatch) {
        beneficiary = upiMatch[1].trim();
        // Clean up beneficiary name
        beneficiary = beneficiary
          .replace(/\/[A-Z0-9]+@[A-Z]+\/?/gi, '')
          .replace(/PAYMENT\/\/[A-Z0-9]+/gi, '')
          .replace(/\//g, ' ')
          .trim();
      }
      
      // Look for amount in current or next lines
      let amount = 0;
      let j = i;
      while (j < Math.min(i + 5, lines.length)) {
        const amountMatch = lines[j].match(/(\d{1,3}(?:,\d{3})*\.\d{2})/);
        if (amountMatch && !lines[j].includes("Balance")) {
          amount = parseFloat(amountMatch[1].replace(/,/g, ''));
          break;
        }
        j++;
      }
      
      // Look for date
      let date = null;
      let dateLine = i > 0 ? lines[i-1] : "";
      const dateMatch = dateLine.match(/(\d{2}-\d{2}-\d{4})/);
      if (dateMatch) {
        const [day, month, year] = dateMatch[1].split('-');
        date = `${year}-${month}-${day}`;
      }
      
      if (amount > 0 && beneficiary) {
        transactions.push({
          date: date || new Date().toISOString().split('T')[0],
          ref: `UPI-${Date.now()}-${transactions.length}`,
          beneficiary: beneficiary,
          amount: amount,
          type: "UPI",
          isDebit: isDebit,
          narration: line
        });
        console.log(`Parsed UPI transaction: ${beneficiary} | ${amount} | ${isDebit ? "DEBIT" : "CREDIT"}`);
      }
    }
    i++;
  }
  
  return transactions;
};

/* ═══════════════════════════════════════════════════════════════════════════
   POST /bank-reconciliation/upload
═══════════════════════════════════════════════════════════════════════════ */
const uploadBankStatement = (req, res) => {
  console.log("=== uploadBankStatement called ===");
  
  uploadMiddleware(req, res, async (err) => {
    if (err) {
      console.error("Multer error:", err);
      return res.status(400).json({ success: false, message: "File upload error: " + err.message });
    }

    console.log("req.file exists:", !!req.file);
    
    const client = await pool.connect();
    try {
      const { statementDate, resetMode = 'true', pdfPassword = '' } = req.body;
      const orgId = req.user?.orgId || req.session?.user?.orgId;
      const userId = req.user?.id;

      if (!orgId) {
        return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
      }

      if (!req.file) {
        return res.status(400).json({ success: false, message: "No file uploaded" });
      }

      console.log("File received:", req.file.originalname, req.file.size, "bytes");
      console.log("pdfPassword from request:", pdfPassword ? "Provided (length: " + pdfPassword.length + ")" : "Not provided");
      console.log("resetMode:", resetMode);

      // Read file buffer
      const fileBuffer = fs.readFileSync(req.file.path);
      let passwordToUse = null;
      
      // Only use password if it's provided AND not empty
      if (pdfPassword && pdfPassword.trim() !== "") {
        passwordToUse = pdfPassword.trim();
        console.log("Will attempt PDF with provided password");
      } else {
        console.log("No password provided, will try without password first");
      }
      
      let parsedTransactions;
      try {
        parsedTransactions = await parseBankStatementPDF(fileBuffer, req.file.originalname, passwordToUse);
      } catch (parseError) {
        console.error("PDF parsing failed:", parseError);
        
        // Clean up file
        try { fs.unlinkSync(req.file.path); } catch(e) { console.error("Error deleting file:", e); }
        
        // Special handling for password errors
        if (parseError.message && (
          parseError.message.toLowerCase().includes('password') ||
          parseError.message.toLowerCase().includes('encrypted')
        )) {
          return res.status(400).json({
            success: false,
            message: parseError.message,
            needsPassword: true,
          });
        }
        
        // Return the error to the frontend
        return res.status(400).json({
          success: false,
          message: parseError.message || "Failed to parse PDF file",
        });
      }
      
      console.log(`Parsed ${parsedTransactions.length} transactions`);

      if (parsedTransactions.length === 0) {
        // Clean up file
        try { fs.unlinkSync(req.file.path); } catch(e) { console.error("Error deleting file:", e); }
        return res.status(400).json({ 
          success: false, 
          message: "Could not parse any transactions from the PDF. Please check the file format or try a different statement format." 
        });
      }

      await client.query("BEGIN");

      const shouldReset = resetMode === 'true';

      if (shouldReset) {
        // Get existing statement
        const existingStatement = await client.query(
          `SELECT id FROM bank_statements 
           WHERE org_id = $1 AND status = 'PROCESSED' 
           ORDER BY created_at DESC LIMIT 1`,
          [orgId]
        );
        
        if (existingStatement.rows.length > 0) {
          // Delete related records first (due to foreign key constraints)
          await client.query(`DELETE FROM reconciliation_matches WHERE org_id = $1 AND bank_transaction_id IN (SELECT id FROM bank_transactions WHERE bank_statement_id = $2)`, [orgId, existingStatement.rows[0].id]);
          await client.query(`DELETE FROM bank_transactions WHERE bank_statement_id = $1`, [existingStatement.rows[0].id]);
          await client.query(`DELETE FROM bank_statements WHERE id = $1`, [existingStatement.rows[0].id]);
          console.log(`Reset mode: Deleted existing statement ID: ${existingStatement.rows[0].id}`);
        }
      }

      // Insert new bank statement record
      const stmtResult = await client.query(
        `INSERT INTO bank_statements 
         (org_id, file_name, file_path, statement_date, total_entries, status, uploaded_by, created_at)
         VALUES ($1, $2, $3, $4, $5, 'PROCESSED', $6, NOW())
         RETURNING id`,
        [orgId, req.file.originalname, `/uploads/bank_statements/${req.file.filename}`, statementDate || new Date(), parsedTransactions.length, userId]
      );

      const bankStatementId = stmtResult.rows[0].id;
      console.log(`Created bank statement ID: ${bankStatementId}`);

      let matchedCount = 0;
      let unmatchedCount = 0;
      const insertedTransactions = [];

      // Insert transactions
      for (const txn of parsedTransactions) {
        // Check if this transaction amount matches any existing payment
        const existingPayment = await client.query(
          `SELECT at.id, at.amount, at.grn_id, g.vendor_name, at.txn_date
           FROM ap_transactions at
           JOIN grn g ON g.id = at.grn_id
           WHERE at.amount = $1 
             AND g.org_id = $2
           LIMIT 1`,
          [txn.amount, orgId]
        );

        let matchedStatus = 'UNMATCHED';
        let matchedGrnId = null;
        let matchedAmount = null;

        if (existingPayment.rows.length > 0) {
          matchedStatus = 'MATCHED';
          matchedGrnId = existingPayment.rows[0].grn_id;
          matchedAmount = txn.amount;
          matchedCount++;
        } else {
          unmatchedCount++;
        }

        // Insert bank transaction
        const txnResult = await client.query(
          `INSERT INTO bank_transactions 
           (bank_statement_id, org_id, transaction_ref, transaction_date, beneficiary_name, amount, transaction_type, matched_status, matched_grn_id, matched_amount, description, created_at)
           VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, NOW())
           RETURNING id`,
          [bankStatementId, orgId, txn.ref || '', txn.date, txn.beneficiary, txn.amount, txn.type, 
           matchedStatus, matchedGrnId, matchedAmount, txn.narration || '']
        );
        
        insertedTransactions.push({
          id: txnResult.rows[0].id,
          ...txn,
          matchedStatus
        });
      }

      // Create reconciliation matches for already-paid transactions
      if (matchedCount > 0) {
        const matchedTxns = await client.query(
          `SELECT id, matched_grn_id, matched_amount FROM bank_transactions 
           WHERE bank_statement_id = $1 AND matched_status = 'MATCHED'`,
          [bankStatementId]
        );
        
        for (const txn of matchedTxns.rows) {
          if (txn.matched_grn_id) {
            await client.query(
              `INSERT INTO reconciliation_matches 
               (org_id, bank_transaction_id, grn_id, matched_amount, status, matched_by, matched_at)
               VALUES ($1, $2, $3, $4, 'ACTIVE', $5, NOW())`,
              [orgId, txn.id, txn.matched_grn_id, txn.matched_amount, userId]
            );
          }
        }
      }

      await client.query("COMMIT");

      // Fetch all inserted transactions for response
      const transactionsResult = await client.query(
        `SELECT id, transaction_ref, transaction_date, beneficiary_name, amount, transaction_type, matched_status
         FROM bank_transactions
         WHERE bank_statement_id = $1
         ORDER BY transaction_date DESC`,
        [bankStatementId]
      );

      console.log(`Upload complete: ${matchedCount} matched, ${unmatchedCount} unmatched`);

      // Clean up uploaded file
      try { 
        fs.unlinkSync(req.file.path); 
        console.log("Deleted temp file:", req.file.path);
      } catch(e) { 
        console.error("Error deleting temp file:", e); 
      }

      return res.status(200).json({
        success: true,
        message: `Bank statement uploaded: ${matchedCount} already matched, ${unmatchedCount} new transactions`,
        data: {
          statementId: bankStatementId,
          fileName: req.file.originalname,
          totalEntries: parsedTransactions.length,
          matchedEntries: matchedCount,
          unmatchedEntries: unmatchedCount,
          transactions: transactionsResult.rows,
        },
      });

    } catch (error) {
      await client.query("ROLLBACK");
      console.error("Upload Bank Statement Error:", error);
      if (req.file?.path && fs.existsSync(req.file.path)) {
        try { fs.unlinkSync(req.file.path); } catch(e) { console.error("Error deleting temp file:", e); }
      }
      
      // Handle password-related errors specifically
      const errorMessage = error.message || '';
      if (errorMessage.toLowerCase().includes('password') || errorMessage.toLowerCase().includes('encrypted')) {
        return res.status(400).json({
          success: false,
          message: errorMessage,
          needsPassword: true,
        });
      }
      
      return res.status(500).json({
        success: false,
        message: "Failed to upload bank statement: " + errorMessage,
        error: process.env.NODE_ENV === "development" ? errorMessage : undefined,
      });
    } finally {
      client.release();
    }
  });
};

/* ═══════════════════════════════════════════════════════════════════════════
   GET /bank-reconciliation/payables
═══════════════════════════════════════════════════════════════════════════ */
const getReconciliationPayables = async (req, res) => {
  try {
    const orgId = req.user?.orgId || req.session?.user?.orgId;
    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    const query = `
      SELECT 
        g.id AS grn_id,
        g.grn_number,
        g.grn_date,
        g.po_number,
        g.vendor_name,
        g.final_invoice_amount,
        COALESCE(g.payment_status, 'UNPAID') AS payment_status,
        COALESCE(
          (SELECT SUM(gi.accepted_qty * gi.unit_price) FROM grn_items gi WHERE gi.grn_id = g.id),
          0
        ) AS grn_amount,
        COALESCE(
          (SELECT SUM(at.amount) FROM ap_transactions at WHERE at.grn_id = g.id),
          0
        ) AS paid_amount,
        COALESCE(
          (SELECT SUM(gi.accepted_qty * gi.unit_price) FROM grn_items gi WHERE gi.grn_id = g.id) - 
          COALESCE((SELECT SUM(at.amount) FROM ap_transactions at WHERE at.grn_id = g.id), 0),
          0
        ) AS remaining_amount,
        CASE 
          WHEN COALESCE((SELECT SUM(at.amount) FROM ap_transactions at WHERE at.grn_id = g.id), 0) >= 
               COALESCE(g.final_invoice_amount, (SELECT SUM(gi.accepted_qty * gi.unit_price) FROM grn_items gi WHERE gi.grn_id = g.id), 0) 
          THEN 'PAID'
          WHEN COALESCE((SELECT SUM(at.amount) FROM ap_transactions at WHERE at.grn_id = g.id), 0) > 0 
          THEN 'PARTIAL'
          ELSE 'UNPAID'
        END AS derived_status,
        pv.vendor_code,
        pv.phone AS vendor_phone,
        pv.email AS vendor_email,
        pv.address AS vendor_address,
        pv.bank_name,
        pv.account_number AS bank_account,
        pv.ifsc_code AS bank_ifsc,
        pv.branch AS bank_branch,
        pv.contact_person AS vendor_contact_person,
        COALESCE(
          (SELECT json_agg(json_build_object(
            'id', rm.id,
            'ref', bt.transaction_ref,
            'amount', rm.matched_amount,
            'date', bt.transaction_date,
            'mode', bt.transaction_type
          ))
          FROM reconciliation_matches rm
          JOIN bank_transactions bt ON bt.id = rm.bank_transaction_id
          WHERE rm.grn_id = g.id AND rm.status = 'ACTIVE'
        ), '[]'::json) AS matched_transactions
      FROM grn g
      LEFT JOIN procurement_vendors pv ON pv.vendor_name = g.vendor_name AND pv.org_id = g.org_id AND pv.is_deleted = false
      WHERE g.org_id = $1
        AND g.is_deleted = false
        AND COALESCE(g.grn_status, 'Draft') = 'Approved'
      ORDER BY 
        CASE 
          WHEN COALESCE(g.payment_status, 'UNPAID') = 'PAID' THEN 1
          ELSE 0
        END,
        g.grn_date DESC
    `;

    const result = await pool.query(query, [orgId]);

    const rows = result.rows;
    const summary = {
      total_payables: rows.length,
      total_amount: rows.reduce((sum, r) => sum + (parseFloat(r.grn_amount) || 0), 0),
      total_paid: rows.reduce((sum, r) => sum + (parseFloat(r.paid_amount) || 0), 0),
      total_remaining: rows.reduce((sum, r) => sum + (parseFloat(r.remaining_amount) || 0), 0),
      paid_count: rows.filter(r => r.derived_status === 'PAID').length,
      partial_count: rows.filter(r => r.derived_status === 'PARTIAL').length,
      unpaid_count: rows.filter(r => r.derived_status === 'UNPAID').length,
    };

    return res.status(200).json({
      success: true,
      data: rows,
      summary,
    });

  } catch (error) {
    console.error("Get Reconciliation Payables Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch payables",
      error: process.env.NODE_ENV === "development" ? error.message : undefined,
    });
  }
};

/* ═══════════════════════════════════════════════════════════════════════════
   GET /bank-reconciliation/transactions
═══════════════════════════════════════════════════════════════════════════ */
const getBankTransactions = async (req, res) => {
  try {
    const { statementId } = req.params;
    const orgId = req.user?.orgId || req.session?.user?.orgId;

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    let stmtId = statementId;
    if (!stmtId) {
      const stmtResult = await pool.query(
        `SELECT id FROM bank_statements 
         WHERE org_id = $1 AND status = 'PROCESSED' 
         ORDER BY created_at DESC LIMIT 1`,
        [orgId]
      );
      if (stmtResult.rows.length === 0) {
        return res.status(200).json({ 
          success: true, 
          data: [], 
          hasStatement: false,
          message: "No bank statement found" 
        });
      }
      stmtId = stmtResult.rows[0].id;
    }

    const result = await pool.query(
      `SELECT 
        bt.id,
        bt.transaction_ref,
        bt.transaction_date,
        bt.beneficiary_name,
        bt.amount,
        bt.transaction_type,
        bt.matched_status,
        rm.id AS match_id,
        rm.grn_id,
        rm.matched_amount,
        bt.description
       FROM bank_transactions bt
       LEFT JOIN reconciliation_matches rm ON rm.bank_transaction_id = bt.id AND rm.status = 'ACTIVE'
       WHERE bt.bank_statement_id = $1 AND bt.org_id = $2
       ORDER BY bt.transaction_date DESC`,
      [stmtId, orgId]
    );

    const stmtInfo = await pool.query(
      `SELECT id, file_name, statement_date, total_entries, created_at 
       FROM bank_statements WHERE id = $1`,
      [stmtId]
    );

    return res.status(200).json({
      success: true,
      data: result.rows,
      statementInfo: stmtInfo.rows[0] || null,
      hasStatement: true,
    });

  } catch (error) {
    console.error("Get Bank Transactions Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch bank transactions",
      error: process.env.NODE_ENV === "development" ? error.message : undefined,
    });
  }
};

/* ═══════════════════════════════════════════════════════════════════════════
   POST /bank-reconciliation/match
   Match a bank transaction to a payable (GRN) with enhanced vendor validation
   - First 3 letters matching
   - Alphanumeric-only comparison
   - Partial contains matching
═══════════════════════════════════════════════════════════════════════════ */
const matchTransaction = async (req, res) => {
  const client = await pool.connect();
  try {
    const { transactionId, grnId, amount } = req.body;
    const orgId = req.user?.orgId || req.session?.user?.orgId;
    const userId = req.user?.id;

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    if (!transactionId || !grnId) {
      return res.status(400).json({ success: false, message: "transactionId and grnId are required" });
    }

    const finalMatchAmount = parseFloat(amount);
    if (isNaN(finalMatchAmount) || finalMatchAmount <= 0) {
      return res.status(400).json({ success: false, message: "Valid amount is required" });
    }

    await client.query("BEGIN");

    // Get transaction details
    const txnResult = await client.query(
      `SELECT bt.*, bs.org_id 
       FROM bank_transactions bt
       JOIN bank_statements bs ON bs.id = bt.bank_statement_id
       WHERE bt.id = $1 AND bt.matched_status = 'UNMATCHED'`,
      [transactionId]
    );

    if (txnResult.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "Transaction not found or already matched" });
    }

    const transaction = txnResult.rows[0];
    const transactionAmount = parseFloat(transaction.amount);

    if (finalMatchAmount > transactionAmount) {
      await client.query("ROLLBACK");
      return res.status(400).json({ 
        success: false, 
        message: `Match amount (${finalMatchAmount}) cannot exceed transaction amount (${transactionAmount})` 
      });
    }

    // Get GRN details with vendor name
    const grnResult = await client.query(
      `SELECT 
        g.id, g.grn_number, g.po_id, g.po_number, g.vendor_name,
        COALESCE(
          (SELECT SUM(gi.accepted_qty * gi.unit_price) FROM grn_items gi WHERE gi.grn_id = g.id),
          0
        ) AS grn_amount,
        COALESCE(
          (SELECT SUM(at.amount) FROM ap_transactions at WHERE at.grn_id = g.id),
          0
        ) AS current_paid
       FROM grn g
       WHERE g.id = $1 AND g.org_id = $2 AND g.is_deleted = false`,
      [grnId, orgId]
    );

    if (grnResult.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "GRN not found" });
    }

    const grn = grnResult.rows[0];
    
    // ─────────────────────────────────────────────────────────────────
    // ENHANCED VENDOR VALIDATION - Multiple matching strategies
    // 1. Exact match
    // 2. Contains match (partial)
    // 3. First 3 letters match (for YASWAN THT vs YASWANTHT)
    // 4. Alphanumeric-only comparison (remove spaces, special chars)
    // 5. First word match (for "SWIGGY LI" vs "SWIGGY")
    // ─────────────────────────────────────────────────────────────────
    const transactionBeneficiary = (transaction.beneficiary_name || "").toLowerCase().trim();
    const grnVendorName = (grn.vendor_name || "").toLowerCase().trim();
    
    // Helper function to clean string (remove special chars, spaces)
    const cleanString = (str) => {
      return str
        .toLowerCase()
        .replace(/[^a-z0-9]/g, '')  // Remove all non-alphanumeric
        .trim();
    };
    
    // Get first 3 letters
    const txnFirst3 = transactionBeneficiary.substring(0, 3);
    const grnFirst3 = grnVendorName.substring(0, 3);
    
    // Get first word (before space)
    const txnFirstWord = transactionBeneficiary.split(' ')[0];
    const grnFirstWord = grnVendorName.split(' ')[0];
    
    // Cleaned versions (no spaces, no special chars)
    const cleanedTxn = cleanString(transactionBeneficiary);
    const cleanedGrn = cleanString(grnVendorName);
    
    // Check for vendor name match using multiple strategies
    const isExactMatch = transactionBeneficiary === grnVendorName;
    const isContainsMatch = transactionBeneficiary.includes(grnVendorName) || grnVendorName.includes(transactionBeneficiary);
    const isFirst3Match = (txnFirst3 === grnFirst3 && grnFirst3.length >= 3);
    const isFirstWordMatch = (txnFirstWord === grnFirstWord && grnFirstWord.length >= 3);
    const isCleanedMatch = cleanedTxn === cleanedGrn;
    const isPartialCleanedMatch = cleanedTxn.includes(cleanedGrn) || cleanedGrn.includes(cleanedTxn);
    
    const isVendorMatch = 
      isExactMatch ||
      isContainsMatch ||
      isFirst3Match ||
      isFirstWordMatch ||
      isCleanedMatch ||
      isPartialCleanedMatch;
    
    // Log matching details for debugging
    console.log(`=== Vendor Match Check ===`);
    console.log(`Transaction: "${transactionBeneficiary}"`);
    console.log(`GRN Vendor: "${grnVendorName}"`);
    console.log(`Exact match: ${isExactMatch}`);
    console.log(`Contains match: ${isContainsMatch}`);
    console.log(`First 3 letters (${txnFirst3} vs ${grnFirst3}): ${isFirst3Match}`);
    console.log(`First word (${txnFirstWord} vs ${grnFirstWord}): ${isFirstWordMatch}`);
    console.log(`Cleaned (${cleanedTxn} vs ${cleanedGrn}): ${isCleanedMatch}`);
    console.log(`Partial cleaned: ${isPartialCleanedMatch}`);
    console.log(`Final result: ${isVendorMatch}`);
    
    if (!isVendorMatch) {
      await client.query("ROLLBACK");
      return res.status(400).json({ 
        success: false, 
        message: `Cannot match: "${transaction.beneficiary_name}" is not the vendor for this payable ("${grn.vendor_name}"). Only payments to the same vendor can be matched.`,
        vendorMismatch: true,
        transactionBeneficiary: transaction.beneficiary_name,
        grnVendorName: grn.vendor_name
      });
    }
    
    console.log(`✓ Vendor match validated: "${transaction.beneficiary_name}" ≈ "${grn.vendor_name}"`);
    
    const newPaidAmount = parseFloat(grn.current_paid) + finalMatchAmount;
    const totalAmount = parseFloat(grn.grn_amount);
    
    let newStatus = "PARTIAL";
    if (newPaidAmount <= 0) newStatus = "UNPAID";
    else if (newPaidAmount >= totalAmount) newStatus = "PAID";
    else newStatus = "PARTIAL";

    // Insert reconciliation match
    await client.query(
      `INSERT INTO reconciliation_matches 
       (org_id, bank_transaction_id, grn_id, po_id, matched_amount, status, matched_by, matched_at)
       VALUES ($1, $2, $3, $4, $5, 'ACTIVE', $6, NOW())`,
      [orgId, transactionId, grnId, grn.po_id, finalMatchAmount, userId]
    );

    let newMatchedStatus = 'MATCHED';
    if (finalMatchAmount < transactionAmount) {
      newMatchedStatus = 'PARTIAL';
    }

    // Update bank transaction
    await client.query(
      `UPDATE bank_transactions 
       SET matched_status = $1,
           matched_grn_id = $2,
           matched_amount = $3::numeric
       WHERE id = $4`,
      [newMatchedStatus, grnId, finalMatchAmount, transactionId]
    );

    // Insert AP transaction record
    await client.query(
      `INSERT INTO ap_transactions 
       (grn_id, amount, txn_date, payment_mode, note, created_at)
       VALUES ($1, $2, $3, $4, $5, NOW())`,
      [grnId, finalMatchAmount, transaction.transaction_date, transaction.transaction_type, 
       `Matched from bank statement: ${transaction.transaction_ref} - Beneficiary: ${transaction.beneficiary_name}`]
    );

    // Update GRN payment status
    await client.query(
      `UPDATE grn 
       SET payment_status = $1, updated_at = NOW()
       WHERE id = $2`,
      [newStatus, grnId]
    );

    await client.query("COMMIT");

    return res.status(200).json({
      success: true,
      message: `Successfully matched ₹${finalMatchAmount.toLocaleString()} to ${grn.grn_number} (${grn.vendor_name})`,
      data: {
        grnId,
        grnNumber: grn.grn_number,
        vendorName: grn.vendor_name,
        newStatus,
        paidAmount: newPaidAmount,
        remainingAmount: totalAmount - newPaidAmount,
      },
    });

  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Match Transaction Error:", error);
    return res.status(500).json({ 
      success: false, 
      message: "Failed to match transaction",
      error: process.env.NODE_ENV === "development" ? error.message : undefined,
    });
  } finally {
    client.release();
  }
};

/* ═══════════════════════════════════════════════════════════════════════════
   DELETE /bank-reconciliation/match/:matchId
═══════════════════════════════════════════════════════════════════════════ */
const cancelMatch = async (req, res) => {
  const client = await pool.connect();
  try {
    const { matchId } = req.params;
    const orgId = req.user?.orgId || req.session?.user?.orgId;
    const userId = req.user?.id;

    await client.query("BEGIN");

    const matchResult = await client.query(
      `SELECT rm.*, bt.id AS bank_txn_id, bt.amount AS txn_amount, bt.transaction_ref,
              bt.matched_status as current_bt_status,
              g.id AS grn_id, g.grn_number
       FROM reconciliation_matches rm
       JOIN bank_transactions bt ON bt.id = rm.bank_transaction_id
       JOIN grn g ON g.id = rm.grn_id
       WHERE rm.id = $1 AND rm.org_id = $2 AND rm.status = 'ACTIVE'`,
      [matchId, orgId]
    );

    if (matchResult.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "Match not found" });
    }

    const match = matchResult.rows[0];

    await client.query(
      `UPDATE reconciliation_matches 
       SET status = 'CANCELLED', cancelled_by = $1, cancelled_at = NOW()
       WHERE id = $2`,
      [userId, matchId]
    );

    const otherMatches = await client.query(
      `SELECT COUNT(*) as count, 
              SUM(matched_amount) as total_matched
       FROM reconciliation_matches 
       WHERE bank_transaction_id = $1 
         AND status = 'ACTIVE' 
         AND id != $2`,
      [match.bank_txn_id, matchId]
    );

    const otherMatchesCount = parseInt(otherMatches.rows[0].count);
    const otherMatchesTotal = parseFloat(otherMatches.rows[0].total_matched || 0);
    const transactionAmount = parseFloat(match.txn_amount);

    let newMatchedStatus = 'UNMATCHED';
    let newMatchedAmount = null;
    let newMatchedGrnId = null;

    if (otherMatchesCount > 0) {
      if (otherMatchesTotal >= transactionAmount) {
        newMatchedStatus = 'MATCHED';
      } else {
        newMatchedStatus = 'PARTIAL';
      }
      newMatchedAmount = otherMatchesTotal;
      const remainingMatch = await client.query(
        `SELECT grn_id FROM reconciliation_matches 
         WHERE bank_transaction_id = $1 AND status = 'ACTIVE' 
         LIMIT 1`,
        [match.bank_txn_id]
      );
      if (remainingMatch.rows.length > 0) {
        newMatchedGrnId = remainingMatch.rows[0].grn_id;
      }
    }

    await client.query(
      `UPDATE bank_transactions 
       SET matched_status = $1,
           matched_grn_id = $2,
           matched_amount = $3
       WHERE id = $4`,
      [newMatchedStatus, newMatchedGrnId, newMatchedAmount, match.bank_txn_id]
    );

    await client.query(
      `DELETE FROM ap_transactions 
       WHERE grn_id = $1 
         AND note ILIKE $2
         AND amount = $3`,
      [match.grn_id, `%${match.transaction_ref}%`, match.matched_amount]
    );

    const paidResult = await client.query(
      `SELECT COALESCE(SUM(amount), 0) AS total_paid
       FROM ap_transactions
       WHERE grn_id = $1`,
      [match.grn_id]
    );

    const totalPaid = parseFloat(paidResult.rows[0].total_paid);
    const grnResult = await client.query(
      `SELECT COALESCE(
        (SELECT SUM(gi.accepted_qty * gi.unit_price) FROM grn_items gi WHERE gi.grn_id = g.id),
        0
       ) AS grn_amount
       FROM grn g
       WHERE g.id = $1`,
      [match.grn_id]
    );
    
    const grnAmount = parseFloat(grnResult.rows[0].grn_amount);
    let newStatus = "UNPAID";
    if (totalPaid <= 0) newStatus = "UNPAID";
    else if (totalPaid >= grnAmount) newStatus = "PAID";
    else newStatus = "PARTIAL";

    await client.query(
      `UPDATE grn SET payment_status = $1, updated_at = NOW() WHERE id = $2`,
      [newStatus, match.grn_id]
    );

    await client.query("COMMIT");

    return res.status(200).json({
      success: true,
      message: `Match cancelled for ${match.grn_number}. Transaction is now ${newMatchedStatus}.`,
    });

  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Cancel Match Error:", error);
    return res.status(500).json({ success: false, message: "Failed to cancel match" });
  } finally {
    client.release();
  }
};

/* ═══════════════════════════════════════════════════════════════════════════
   GET /bank-reconciliation/summary
═══════════════════════════════════════════════════════════════════════════ */
const getReconciliationSummary = async (req, res) => {
  try {
    const orgId = req.user?.orgId || req.session?.user?.orgId;
    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }

    const result = await pool.query(
      `SELECT 
        COUNT(DISTINCT bs.id) AS total_statements,
        COUNT(bt.id) AS total_transactions,
        COUNT(CASE WHEN bt.matched_status = 'MATCHED' THEN 1 END) AS matched_transactions,
        COUNT(CASE WHEN bt.matched_status = 'PARTIAL' THEN 1 END) AS partial_transactions,
        COUNT(CASE WHEN bt.matched_status = 'UNMATCHED' THEN 1 END) AS unmatched_transactions,
        COALESCE(SUM(CASE WHEN bt.matched_status != 'UNMATCHED' THEN bt.matched_amount ELSE 0 END), 0) AS total_matched_amount,
        COALESCE(SUM(bt.amount), 0) AS total_statement_amount
       FROM bank_statements bs
       JOIN bank_transactions bt ON bt.bank_statement_id = bs.id
       WHERE bs.org_id = $1 AND bs.status = 'PROCESSED'`,
      [orgId]
    );

    return res.status(200).json({
      success: true,
      data: result.rows[0],
    });

  } catch (error) {
    console.error("Get Reconciliation Summary Error:", error);
    return res.status(500).json({ success: false, message: "Failed to fetch summary" });
  }
};

module.exports = {
  uploadBankStatement,
  getReconciliationPayables,
  getBankTransactions,
  matchTransaction,
  cancelMatch,
  getReconciliationSummary,
};