# Define file paths
$excelFilePath = "C:\Users\danielc\Desktop\Changed Addrs12.xlsx"
$accessDbPath = "\\kci-app01\c$\inetpub\Data\webdata - Copy.mdb"
$tableName = "Jurisdiction"

# Open Excel file
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false # Set to $true if you want to see Excel open

$workbook = $excel.Workbooks.Open($excelFilePath)
$sheet = $workbook.Sheets.Item(1)

# Get Excel range
$usedRange = $sheet.UsedRange
$rowCount = $usedRange.Rows.Count
$colCount = $usedRange.Columns.Count

Write-Host "Total Rows: $rowCount, Total Columns: $colCount"

# Retrieve column headers (first row) & trim spaces
$headers = @()
for ($col = 1; $col -le $colCount; $col++)
{
	$headers += ($sheet.Cells.Item(1, $col).Text).Trim()
}

# Print headers for debugging
Write-Host "Excel Headers Found: $($headers -join ', ')"

# Ensure necessary columns exist
$jcodeIndex = $headers.IndexOf("Jurisdiction Code")
$newAddIndex = $headers.IndexOf("New Add")
$newCityIndex = $headers.IndexOf("New city")
$newZipIndex = $headers.IndexOf("New Zip")

if ($jcodeIndex -eq -1 -or $newAddIndex -eq -1 -or $newCityIndex -eq -1 -or $newZipIndex -eq -1)
{
	Write-Error "One or more required columns (Jurisdiction Code, New Add, New City, New Zip) not found in Excel."
	$workbook.Close($false)
	$excel.Quit()
	exit
}

# Open Access Database
$connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$accessDbPath;"
$conn = New-Object -ComObject ADODB.Connection

try
{
	$conn.Open($connectionString)
	Write-Host "Connected to Access Database: $accessDbPath"
	
	# Iterate through Excel rows and generate SQL updates
	for ($row = 2; $row -le $rowCount; $row++)
	{
		# Read Jurisdiction Code and ensure zero-padded format
		$rawJurisdictionCode = $sheet.Cells.Item($row, $jcodeIndex + 1).Text
		if ($rawJurisdictionCode -match "^\d+$")
		{
			$jurisdictionCode = "{0:D5}" -f [int]$rawJurisdictionCode
		}
		else
		{
			Write-Host "Skipping row $row Invalid Jurisdiction Code '$rawJurisdictionCode'"
			continue
		}
		
		# Read new address fields from Excel
		$newAdd = $sheet.Cells.Item($row, $newAddIndex + 1).Text
		$newCity = $sheet.Cells.Item($row, $newCityIndex + 1).Text
		$newZip = $sheet.Cells.Item($row, $newZipIndex + 1).Text
		
		# Generate CSZ (keeping the "-" in ZIP code)
		$csz = "$newCity $newZip"
		
		# Remove "-" from ZIP code for IMB_Digits
		$cleanZip = $newZip -replace "-", ""
		$imbDigits = "00778903419785000000$cleanZip"
		
		if (-not $newAdd -and -not $newCity -and -not $newZip)
		{
			Write-Host "Skipping row $row No updates found for Mailing_Address, CSZ, IMB, or IMB_Digits."
			continue
		}
		
		# Make API call to get IMB value
		$apiUrl = "https://postalpro.usps.com/ppro-tools-api/imb/encode?imb=$imbDigits"
		try
		{
			$response = Invoke-RestMethod -Uri $apiUrl -Method Get -Headers @{ "Accept" = "application/json" }
			$imbValue = $response.imb
		}
		catch
		{
			Write-Host "Error retrieving IMB for row $row (Jurisdiction Code: $jurisdictionCode), using fallback IMB_Digits."
			$imbValue = $imbDigits
		}
		
		# Escape SQL values
		$newAddEscaped = $newAdd -replace "'", "''"
		$cszEscaped = $csz -replace "'", "''"
		$imbDigitsEscaped = $imbDigits -replace "'", "''"
		$imbValueEscaped = $imbValue -replace "'", "''"
		
		# Generate SQL Update statement
		$updateSQL = "UPDATE [$tableName] SET [Mailing_Address] = '$newAddEscaped', [CSZ] = '$cszEscaped', [IMB] = '$imbValueEscaped', [IMB_Digits] = '$imbDigitsEscaped' WHERE [JCode] = '$jurisdictionCode';"
		Write-Host "Executing: $updateSQL"
		
		# Execute the SQL Update
		$cmd = $conn.Execute($updateSQL)
	}
	
}
catch
{
	Write-Error "Error updating Access database: $_"
}
finally
{
	# Close the Access database
	$conn.Close()
	[System.Runtime.Interopservices.Marshal]::ReleaseComObject($conn) | Out-Null
}

# Close Excel
$workbook.Close($false)
$excel.Quit()

# Release COM objects
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

Write-Host "Excel and Access database update completed."