import { useState, useCallback } from "react"; import * as XLSX from "xlsx"; const LOAN_TYPE_MAP = { "10 YR FIXED FHA REPAYABLE 2ND": "Bond", "15 FIXED CONF NEWREZ FT WA": "Conventional", "15 YR FIXED FHLMC <= 85K AMERIHOME": "Conventional", "15 YR FIXED FHLMC > 225K AMERIHOME": "Conventional", "15 YR FIXED FHLMC NOO AMERIHOME": "Conventional", "20 FIXED CONF NEWREZ FT WA": "Conventional", "20 YR FIXED FHA > 250K AMERIHOME": "FHA", "3/1 ARM - SOA": "Conventional", "30 FIXED CONF CITI": "Conventional", "30 FIXED CONF FNMA 200K-225K PENNYMAC": "Conventional", "30 FIXED CONF FNMA 225K-250K PENNYMAC": "Conventional", "30 FIXED CONF FNMA 250K-275K PENNYMAC": "Conventional", "30 FIXED CONF FNMA 275K-300K PENNYMAC": "Conventional", "30 FIXED CONF FNMA 300K-325K PENNYMAC": "Conventional", "30 FIXED CONF FNMA 325K-350K PENNYMAC": "Conventional", "30 FIXED CONF FNMA 350K-375K PENNYMAC": "Conventional", "30 FIXED CONF FNMA 375K-400K PENNYMAC": "Conventional", "30 FIXED CONF FNMA 400K PENNYMAC": "Conventional", "30 FIXED CONF FNMA 400K TX PENNYMAC": "Conventional", "30 FIXED FHLMC CONF COMMUNITY LENDING CHASE": "Conventional", "30 FIXED HOMEONE 175K-200K PENNYMAC": "Conventional", "30 FIXED HOMEONE 275K-300K PENNYMAC": "Conventional", "30 FIXED HOMEONE 300K PENNYMAC": "Conventional", "30 YR FIXED CONF FREDDIE ONLY NEWREZ FT WA": "Conventional", "30 YR FIXED CONF HOMEREADY 200K-<225K MR. COOPER": "Conventional", "30 YR FIXED FHA FORGIVABLE 2ND": "Bond", "30 YR FIXED FHA FORGIVABLE WITH 2ND": "Bond", "30 YR FIXED FHA GREAT CHOICE THDA": "Bond", "30 YR FIXED FHA REPAYABLE WITH 2ND": "Bond", "30 YR FIXED FHLMC 300K-325K AMERIHOME": "Conventional", "30 YR FIXED FHLMC 325K-350K AMERIHOME": "Conventional", "30 YR FIXED FHLMC 350K-375K AMERIHOME": "Conventional", "30 YR FIXED FHLMC 375K-400K AMERIHOME": "Conventional", "30 YR FIXED FNMA 300K-325K AMERIHOME": "Conventional", "30 YR FIXED FNMA 325K-350K AMERIHOME": "Conventional", "30 YR FIXED FNMA 375K-400K AMERIHOME": "Conventional", "30 YR FIXED HOME POSSIBLE 150K-175K AMERIHOME": "Conventional", "30 YR FIXED VA > 300K AMERIHOME": "VA", "30 YR FIXED VA IRRRL": "VA", "30 YR FIXED VA IRRRL > 300K AMERIHOME": "VA", "30 YR FIXED VA IRRRL 250K-275K PENNYMAC": "VA", "30 YR FIXED VA IRRRL 300K PENNYMAC": "VA", "5/1 ARM FHA PENNYMAC ( 2.25% MARGIN )": "FHA", "5/6 SOFR ARM CONF NEWREZ FT WA": "Conventional", "5/6 SOFR ARM CONF TRUIST": "Conventional", "5/6 SOFR ARM NONCONF CHASE": "Jumbo", "7/6 SOFR ARM 5/1/5 CONF FNMA PENNYMAC": "Conventional", "7/6 SOFR ARM CONF NEWREZ FT WA": "Conventional", "7/6 SOFR ARM CONF TRUIST": "Conventional", "7/6 SOFR ARM NONCONF CHASE": "Jumbo", "ADFA 2nd": "Bond", "BOND - NCHFA - 2ND LIEN": "Bond", "BOND-ADFA-FHA": "Bond", "BOND-AZ HOME PLUS-FHA": "Bond", "BOND-LHC-CONVENTIONAL": "Bond", "BOND-LHC-FHA": "Bond", "BOND-LHC-USDA": "Bond", "BOND-MHFA-CONVENTIONAL": "Bond", "BOND-MHFA-CONVENTIONAL 2nd Lien": "Bond", "BOND-NCHFA-CONVENTIONAL": "Bond", "BOND-TDHCA-CONVENTIONAL": "Bond", "BOND-TDHCA-FHA": "Bond", "BOND-TEXAS VLB-VA": "Bond", "BOND-TSAHC-CONVENTIONAL": "Bond", "BOND-TSAHC-FHA": "Bond", "BOND-TSAHC-VA": "Bond", "Bridge - 1st Lien": "Temp Loan", "BRIDGE - 1ST LIEN": "Temp Loan", "Bridge - 2nd Lien": "Temp Loan", "BRIDGE - 2ND LIEN": "Temp Loan", "C10-000-025": "Conventional", "C10-000-060": "Conventional", "C10-049-040": "Conventional", "C15-000-013": "Conventional", "C15-000-025": "Conventional", "C15-000-030": "Conventional", "C15-000-060": "Conventional", "C15-000-080": "Conventional", "C15-000-095": "Conventional", "C15-049-002 > 225K": "Conventional", "C15-049-002 200K-225K": "Conventional", "C20-000-025": "Conventional", "C20-000-040": "Conventional", "C20-000-060": "Conventional", "C20-000-080": "Conventional", "C20-000-095": "Conventional", "C20-049-002 > 300K": "Conventional", "C20-049-040": "Conventional", "C25-000-025": "Conventional", "C30-000-000": "Conventional", "C30-000-002": "Conventional", "C30-000-002 250K-275K": "Conventional", "C30-000-013": "Conventional", "C30-000-025": "Conventional", "C30-000-030": "Conventional", "C30-000-033": "Conventional", "C30-000-040": "Conventional", "C30-000-055": "Conventional", "C30-000-060": "Conventional", "C30-000-076": "Conventional", "C30-000-080": "Conventional", "C30-000-095": "Conventional", "C30-049-002": "Conventional", "C30-049-002 > 225K": "Conventional", "C30-049-002 > 300K": "Conventional", "C30-049-002 > 400K": "Conventional", "C30-049-002 125K-150K": "Conventional", "C30-049-002 150K-175K": "Conventional", "C30-049-002 175K-200K": "Conventional", "C30-049-002 200K-225K": "Conventional", "C30-049-002 225K-250K": "Conventional", "C30-049-002 250K-275K": "Conventional", "C30-049-002 275K-300K": "Conventional", "C30-049-013": "Conventional", "C30-049-025": "Conventional", "C30-049-030": "Conventional", "C30-049-040": "Conventional", "C30-049-060": "Conventional", "C30-049-080": "Conventional", "C30-HOMEONE-060": "Conventional", "C30-HOMEPOSSIBLE-002": "Conventional", "C30-HOMEPOSSIBLE-013": "Conventional", "C30-HOMEPOSSIBLE-025": "Conventional", "C30-HOMEPOSSIBLE-033": "Conventional", "C30-HOMEPOSSIBLE-060": "Conventional", "C30-HOMEPOSSIBLE-080": "Conventional", "C30-HOMEREADY-013": "Conventional", "C30-HOMEREADY-025": "Conventional", "C30-HOMEREADY-033": "Conventional", "C30-HOMEREADY-040": "Conventional", "C30-HOMEREADY-060": "Conventional", "C30-HOMEREADY-095": "Conventional", "CONVENTIONAL HOMESTYLE RENO - PENNYMAC - 30YR": "Conventional", "CONVENTIONAL HOMESTYLE RENO - PLANET HOME LENDING- 30YR": "Conventional", "F20-000-060": "FHA", "F20-000-069": "FHA", "F25-000-002": "FHA", "F25-000-069": "FHA", "F30-000-002": "FHA", "F30-000-025": "FHA", "F30-000-030": "FHA", "F30-000-033": "FHA", "F30-000-060": "FHA", "F30-000-069": "FHA", "F30-000-076": "FHA", "F30-000-080": "FHA", "F30-000-095": "FHA", "FHA 203K - PENNYMAC - 30YR": "FHA", "FHA 203K - PLANET HOME LENDING - 30YR": "FHA", "Investor Edge - 6 Month Balloon": "Temp Loan", "JUMBO - 30 YR FIXED TX A6 CASH OUT - CHASE - NON DELEGATED": "Jumbo", "JUMBO - 30YR FIXED - CHASE - NON-DELEGATED": "Jumbo", "JUMBO - 30YR FIXED - PENNYMAC - DELEGATED": "Jumbo", "JUMBO - 30YR FIXED - TRUIST - DELEGATED": "Jumbo", "JUMBO - 5/6 SOFR ARM - CHASE - Non-Delegated": "Jumbo", "JUMBO - 7/6 SOFR ARM - CHASE - Non-Delegated": "Jumbo", "NON-QM 30 YR FIXED ACCESS AGENCY PLUS ARC HOME NONDELEGATED": "Non-QM", "NON-QM 30 YR FIXED ACCESS ALT INCOME ARC HOME NONDELEGATED": "Non-QM", "NON-QM 30 YR FIXED EDGE ALT INCOME ARC HOME NONDELEGATED": "Non-QM", "NON-QM 30 YR FIXED OPEN ROAD ASSET QUALIFICATION LOGAN FINANCE NONDELEGATED": "Non-QM", "PATHWAY SHARED OWNERSHIP": "Shared Ownership", "R30-0105-030": "USDA Rural", "R30-0105-060": "USDA Rural", "R30-0105-076": "USDA Rural", "R30-105-069": "USDA Rural", "Reverse - Finance of America": "Reverse", "REVERSE - Finance of America": "Reverse", "Reverse-Refinance-Finance of America": "Reverse", "Second Lien - ADFA": "Bond", "Second Lien - SFMC - 30 Due In 3": "Temp Loan", "SFMC - 3/1 1 YR CMT ARM 2/2/6": "Temp Loan", "SFMC - INTERIM CONSTRUCTION CONFORMING": "Temp Loan", "SFMC - LOT LOAN CONFORMING": "Temp Loan", "SFMC - Near Miss - Cendera Bank": "Temp Loan", "SFMC - NEAR MISS CONFORMING": "Temp Loan", "SFMC - NEAR MISS CONFORMING - BALLOON": "Temp Loan", "SFMC - NEAR MISS CONFORMING - BRIDGE": "Temp Loan", "SFMC - NEAR MISS CONFORMING - FIXED": "Temp Loan", "SFMC - NEAR MISS JUMBO": "Temp Loan", "THDA - 2nd Lien": "Bond", "V15-000-025": "VA", "V15-000-060": "VA", "V30-000-002": "VA", "V30-000-008": "VA", "V30-000-025": "VA", "V30-000-030": "VA", "V30-000-033": "VA", "V30-000-060": "VA", "V30-000-069": "VA", "V30-000-076": "VA", "V30-000-080": "VA", "V30-000-095": "VA", "VA-IRRRL-30YR": "VA", "Wholesale - ACC": "Wholesale", "Wholesale - Acra": "Wholesale", "Wholesale - ACRA": "Wholesale", "Wholesale - AFR": "Wholesale", "Wholesale - Angel Oak": "Wholesale", "Wholesale - ARC": "Wholesale", "WHOLESALE - ARC": "Wholesale", "Wholesale - ARC - DSCR": "Wholesale", "WHOLESALE - ARC HOME LOANS": "Wholesale", "Wholesale - ARCA": "Wholesale", "Wholesale - Arca": "Wholesale", "Wholesale - BRIDGE CENDERA - Temp Financing": "Wholesale", "Wholesale - Cendera": "Wholesale", "Wholesale - Cendera - Commercial": "Wholesale", "Wholesale - Cendera - Renovation": "Wholesale", "Wholesale - Champion": "Wholesale", "Wholesale - Champions": "Wholesale", "Wholesale - Ciera": "Wholesale", "Wholesale - Ciera Bank": "Wholesale", "Wholesale - Clear Edge": "Wholesale", "Wholesale - ClearEdge": "Wholesale", "Wholesale - First National Bank": "Wholesale", "Wholesale - First National Bank of America": "Wholesale", "WHOLESALE - GMFS": "Wholesale", "Wholesale - Greenbox": "Wholesale", "Wholesale - Lend Sure": "Wholesale", "Wholesale - Lendsure": "Wholesale", "Wholesale - LendSure": "Wholesale", "Wholesale - Loan Stream": "Wholesale", "Wholesale - LoanStream": "Wholesale", "Wholesale - Logan": "Wholesale", "Wholesale - Logan Financial": "Wholesale", "Wholesale - Luxury Mortgage": "Wholesale", "Wholesale - Spring EQ": "Wholesale", "Wholesale - Spring EQ - 2nd Lien": "Wholesale", "Wholesale - SpringEQ": "Wholesale", "WHOLESALE \u2013 Spring EQ": "Wholesale", }; const TYPE_COLORS = { Conventional: { bg: "#dbeafe", text: "#1e40af", border: "#93c5fd" }, FHA: { bg: "#dcfce7", text: "#166534", border: "#86efac" }, VA: { bg: "#fef9c3", text: "#854d0e", border: "#fde047" }, Bond: { bg: "#ede9fe", text: "#5b21b6", border: "#c4b5fd" }, Jumbo: { bg: "#fee2e2", text: "#991b1b", border: "#fca5a5" }, "Temp Loan": { bg: "#ffedd5", text: "#7c2d12", border: "#fdba74" }, "Non-QM": { bg: "#fce7f3", text: "#831843", border: "#f9a8d4" }, "USDA Rural": { bg: "#d1fae5", text: "#065f46", border: "#6ee7b7" }, Reverse: { bg: "#f1f5f9", text: "#334155", border: "#cbd5e1" }, Wholesale: { bg: "#e0f2fe", text: "#075985", border: "#7dd3fc" }, "Shared Ownership": { bg: "#fdf4ff", text: "#701a75", border: "#e879f9" }, }; const Badge = ({ type }) => { const c = TYPE_COLORS[type] || { bg: "#f3f4f6", text: "#374151", border: "#d1d5db" }; return ( {type} ); }; export default function LoanTypeUpdater() { const [dragging, setDragging] = useState(false); const [status, setStatus] = useState("idle"); // idle | processing | done | error const [stats, setStats] = useState(null); const [unknowns, setUnknowns] = useState([]); const [workbook, setWorkbook] = useState(null); const [fileName, setFileName] = useState(""); const [previewRows, setPreviewRows] = useState([]); const processFile = useCallback((file) => { if (!file) return; setFileName(file.name); setStatus("processing"); setStats(null); setUnknowns([]); setPreviewRows([]); const reader = new FileReader(); reader.onload = (e) => { try { const wb = XLSX.read(e.target.result, { type: "array", cellDates: true }); const sheetName = wb.SheetNames[0]; const ws = wb.Sheets[sheetName]; const rows = XLSX.utils.sheet_to_json(ws, { defval: "" }); if (!rows.length) { setStatus("error"); return; } const loanTypeCol = Object.keys(rows[0]).find(k => k.toLowerCase().replace(/\s/g, "") === "loantype" ); const programCol = Object.keys(rows[0]).find(k => k.toLowerCase().includes("loan program") ); if (!loanTypeCol || !programCol) { setStatus("error"); return; } let updated = 0, unchanged = 0, notFound = []; const unknownSet = {}; const newRows = rows.map((row) => { const program = (row[programCol] || "").trim(); const currentType = (row[loanTypeCol] || "").trim(); const correctType = LOAN_TYPE_MAP[program]; if (!correctType) { if (program && !unknownSet[program]) { unknownSet[program] = true; notFound.push(program); } return row; } if (correctType !== currentType) { updated++; return { ...row, [loanTypeCol]: correctType }; } else { unchanged++; return row; } }); // Build preview of changed rows (first 20) const changed = []; rows.forEach((row, i) => { if (changed.length >= 20) return; const program = (row[programCol] || "").trim(); const currentType = (row[loanTypeCol] || "").trim(); const correctType = LOAN_TYPE_MAP[program]; if (correctType && correctType !== currentType) { changed.push({ program, from: currentType, to: correctType, loan: row["Loan Number"] || row[Object.keys(row)[1]] || i, }); } }); setPreviewRows(changed); // Write back to sheet const newWs = XLSX.utils.json_to_sheet(newRows, { header: Object.keys(rows[0]), cellDates: true, }); wb.Sheets[sheetName] = newWs; setWorkbook(wb); setStats({ total: rows.length, updated, unchanged, unknown: notFound.length }); setUnknowns(notFound); setStatus("done"); } catch (err) { console.error(err); setStatus("error"); } }; reader.readAsArrayBuffer(file); }, []); const handleDrop = (e) => { e.preventDefault(); setDragging(false); const file = e.dataTransfer.files[0]; if (file) processFile(file); }; const handleDownload = () => { if (!workbook) return; const base = fileName.replace(/\.[^.]+$/, ""); XLSX.writeFile(workbook, `${base}_updated.xlsx`); }; const reset = () => { setStatus("idle"); setStats(null); setUnknowns([]); setWorkbook(null); setFileName(""); setPreviewRows([]); }; return ( ); } function btnStyle(bg, color) { return { background: bg, color, border: "none", borderRadius: 6, padding: "10px 20px", fontSize: 13, fontWeight: 600, cursor: "pointer", fontFamily: "inherit" }; }