// @ts-nocheck
import ExcelJS from ‘npm:exceljs@4.4.0’
import officeCrypto from ‘npm:officecrypto-tool@0.0.19’
import { Buffer } from ‘node:buffer’
const uelApiBaseUrl = Deno.env.get(‘UEL_API_BASE_URL’) || ‘https://remote.uel.ie:8045’
const uelApiBearerToken = Deno.env.get(‘UEL_API_BEARER_TOKEN’) || ”
const resendApiKey = Deno.env.get(‘RESEND_API_KEY’) || ”
const resendFromEmail = Deno.env.get(‘RESEND_FROM_EMAIL’) || ‘noreply@uel.ie’
const LARGE_CONFIDENTIAL_ROW_THRESHOLD = 5000
const MAX_EXCEL_ROWS = 15000
const MAX_EXCEL_COLUMNS = 20
const MAX_ATTACHMENT_FALLBACK_BYTES = 12 * 1024 * 1024
const CORS_HEADERS = {
‘Access-Control-Allow-Origin’: ‘*’,
‘Access-Control-Allow-Methods’: ‘POST, OPTIONS’,
‘Access-Control-Allow-Headers’: ‘Content-Type, Authorization’,
}
function toBase64(bytes: Uint8Array): string {
let binary = ”
const chunkSize = 0x8000
for (let i = 0; i {
const day = String(date.getDate()).padStart(2, ‘0’)
const month = String(date.getMonth() + 1).padStart(2, ‘0’)
return `${day}${month}`
}
const buildConfidentialPassword = (email: string, exportedAt: Date) => {
const normalized = String(email || ”).trim().toLowerCase()
const username = normalized.split(‘@’)[0]?.trim() || ”
const safeUsername = username.replace(/[^a-z0-9._-]/g, ”)
if (!safeUsername) {
throw new Error(‘Unable to derive confidential password from sender email’)
}
return `${safeUsername}${formatDdmm(exportedAt)}`
}
async function fetchUelData(path: string) {
const response = await fetch(`${uelApiBaseUrl}${path}`, {
method: ‘GET’,
headers: {
‘Content-Type’: ‘application/json’,
Authorization: `Bearer ${uelApiBearerToken}`,
},
})
if (!response.ok) {
const body = await response.text()
throw new Error(`UEL API ${response.status}: ${body}`)
}
return response.json()
}
async function sendEmail(
recipients: string[],
subject: string,
htmlBody: string,
attachment?: { filename: string; contentBase64: string; contentType: string },
fromEmail?: string
) {
if (!resendApiKey) {
throw new Error(‘RESEND_API_KEY is not configured’)
}
const from = fromEmail || resendFromEmail
const response = await fetch(‘https://api.resend.com/emails’, {
method: ‘POST’,
headers: {
Authorization: `Bearer ${resendApiKey}`,
‘Content-Type’: ‘application/json’,
},
body: JSON.stringify({
from,
to: recipients,
subject,
html: htmlBody,
attachments: attachment
? [
{
filename: attachment.filename,
content: attachment.contentBase64,
content_type: attachment.contentType,
},
]
: [],
}),
})
const payload = await response.json().catch(() => null)
if (!response.ok) {
throw new Error(`Resend ${response.status}: ${JSON.stringify(payload)}`)
}
return {
success: true,
messageId: payload?.id || `msg_${Date.now()}`,
recipients,
}
}
async function buildCustomerFullPriceListData(customerCode: string, productClass: string = ”, exportType: string = ‘classified’) {
const normalizedClass = productClass == null ? ” : String(productClass).trim()
const isNotClassified = String(exportType || ”).toLowerCase() === ‘not-classified’
const baseEndpoint = isNotClassified
? ‘CustomerFullPriceList_NotClassified_APP’
: ‘CustomerFullPriceListAPP’
const endpoint = `/${baseEndpoint}?CustomerCode=’${encodeURIComponent(
customerCode
)}’&ProductClass=’${encodeURIComponent(normalizedClass)}’`
const response = await fetchUelData(endpoint)
const items = Array.isArray(response?.Data)
? response.Data
: Array.isArray(response)
? response
: []
const headerSource = {
…(response?.Header || {}),
…(response?.header || {}),
…(response || {}),
…(items[0] || {}),
}
const getHeaderValue = (aliases: string[], fallback = ”) => {
const entries = Object.entries(headerSource || {}).map(([k, v]) => [String(k).toLowerCase(), v] as const)
for (const alias of aliases) {
const key = String(alias).toLowerCase()
const match = entries.find(([k]) => k === key)
if (match && match[1] != null && String(match[1]).trim() !== ”) {
return String(match[1])
}
}
return fallback
}
const columns = isNotClassified
? [
{ key: ‘product_code’, label: ‘Product Code’, aliases: [‘product_code’, ‘ProductCode’, ‘productcode’] },
{ key: ‘product_description’, label: ‘Description’, aliases: [‘product_description’, ‘ProductDescription’, ‘description’] },
{ key: ‘category’, label: ‘Category’, aliases: [‘category’, ‘Category’] },
{ key: ‘group’, label: ‘Group’, aliases: [‘group’, ‘Group’] },
{ key: ‘class’, label: ‘Class’, aliases: [‘class’, ‘Class’] },
{ key: ‘price’, label: ‘Price’, aliases: [‘price’, ‘Price’] },
]
: [
{ key: ‘product_code’, label: ‘Product Code’, aliases: [‘product_code’, ‘ProductCode’, ‘productcode’] },
{ key: ‘product_description’, label: ‘Description’, aliases: [‘product_description’, ‘ProductDescription’, ‘description’] },
{ key: ‘category’, label: ‘Category’, aliases: [‘category’, ‘Category’] },
{ key: ‘group’, label: ‘Group’, aliases: [‘group’, ‘Group’] },
{ key: ‘class’, label: ‘Class’, aliases: [‘class’, ‘Class’] },
{
key: ‘standardprice’,
label: ‘Standard Price’,
aliases: [
‘standardprice’,
‘standarprice’,
‘standard_price’,
‘standar_price’,
‘StandardPrice’,
‘standard price’,
‘STANDARDPRICE’,
‘STANDARD_PRICE’,
],
},
{
key: ‘customer_special_price’,
label: ‘Special Price’,
aliases: [
‘customer_special_price’,
‘CustomerSpecialPrice’,
‘special_price’,
‘specialprice’,
‘SpecialPrice’,
‘customer special price’,
‘SPECIAL_PRICE’,
],
},
{ key: ‘average_cost’, label: ‘Cost’, aliases: [‘average_cost’, ‘AverageCost’, ‘cost’] },
{ key: ‘standarmargin’, label: ‘Standard Margin’, aliases: [‘standarmargin’, ‘standardmargin’, ‘standard_margin’, ‘StandardMargin’] },
{ key: ‘special_margin’, label: ‘Special Margin’, aliases: [‘special_margin’, ‘SpecialMargin’] },
]
const getFieldValue = (item: Record, aliases: string[]) => {
for (const alias of aliases) {
if (Object.prototype.hasOwnProperty.call(item, alias)) {
return item[alias]
}
}
const loweredEntries = Object.entries(item || {}).map(([k, v]) => [String(k).toLowerCase(), v] as const)
for (const alias of aliases) {
const target = String(alias).toLowerCase()
const found = loweredEntries.find(([k]) => k === target)
if (found) {
return found[1]
}
}
return ”
}
const headers = columns.map((c) => c.label)
const rows = items.map((item: Record) => columns.map((c) => getFieldValue(item, c.aliases)))
const headerInfo = {
customerListCode: getHeaderValue([‘customer_list_code’, ‘customerlistcode’], String(productClass || ”).trim() || ‘ALL’),
customerCode: getHeaderValue([‘customer_code’, ‘customercode’], customerCode),
customerName: getHeaderValue([‘customer_name’, ‘customername’], ”),
date: getHeaderValue([‘Date’, ‘date’], new Date().toISOString().slice(0, 10)),
}
if (rows.length === 0) {
return {
headers,
rows: [columns.map(() => ”)],
columns,
headerInfo,
}
}
return { headers, rows, columns, headerInfo }
}
function generateCSV(headers: string[], rows: any[][]): string {
const escapeCsvValue = (value: any) => {
const text = String(value ?? ”).replace(/\r?\n|\r/g, ‘ ‘).trim()
if (text.includes(‘,’) || text.includes(‘”‘)) {
return `”${text.replace(/”/g, ‘””‘)}”`
}
return text
}
const headerLine = headers.map(escapeCsvValue).join(‘,’)
const rowLines = rows.map((row) => row.map(escapeCsvValue).join(‘,’))
return [headerLine, …rowLines].join(‘\n’)
}
async function generateXlsxBytes(
sheetName: string,
headers: string[],
rows: any[][],
options?: {
headerFillArgb?: string
bandedRowFillArgb?: string
autoSizeColumns?: boolean
}
): Promise {
const workbook = new ExcelJS.Workbook()
const worksheet = workbook.addWorksheet(sheetName)
const autoSizeColumns = options?.autoSizeColumns !== false
worksheet.addRow(headers)
for (const row of rows) {
worksheet.addRow(row)
}
worksheet.views = [{ state: ‘frozen’, ySplit: 1, xSplit: 1 }]
const headerRow = worksheet.getRow(1)
headerRow.font = { bold: true, color: { argb: ‘FF1F2937’ } }
headerRow.fill = {
type: ‘pattern’,
pattern: ‘solid’,
fgColor: { argb: options?.headerFillArgb || ‘FFE5E7EB’ },
}
if (options?.bandedRowFillArgb) {
for (let rowIndex = 2; rowIndex {
let maxLength = 12
column.eachCell({ includeEmpty: true }, (cell) => {
const len = String(cell.value ?? ”).length
maxLength = Math.max(maxLength, len + 2)
})
column.width = Math.min(maxLength, 42)
})
}
const buffer = await workbook.xlsx.writeBuffer()
return new Uint8Array(buffer as ArrayBuffer)
}
async function generateCrmPricingXlsxBytes(
headerInfo: {
customerListCode: string
customerCode: string
customerName: string
date: string
},
columns: { key: string; label: string }[],
rows: any[][],
options?: { isConfidential?: boolean }
): Promise {
const workbook = new ExcelJS.Workbook()
const worksheet = workbook.addWorksheet(‘CRM Pricing’)
const isConfidential = !!options?.isConfidential
const priceColumnKeys = new Set([‘price’, ‘standardprice’, ‘customer_special_price’, ‘average_cost’])
const isNumericLike = (value: any) => {
if (value == null || value === ”) return false
const normalized = String(value).replace(/,/g, ”).trim()
const parsed = Number(normalized)
return Number.isFinite(parsed)
}
const topRows = [
[‘Customer Price List’, headerInfo.customerListCode || ”],
[‘Customer Code’, headerInfo.customerCode || ”],
[‘Customer Name’, headerInfo.customerName || ”],
[‘Date’, headerInfo.date || new Date().toISOString().slice(0, 10)],
]
for (const row of topRows) {
worksheet.addRow(row)
}
worksheet.addRow([])
const dataHeaderRowIndex = worksheet.rowCount + 1
worksheet.addRow(columns.map((c) => c.label))
const dataStartRow = dataHeaderRowIndex + 1
for (const row of rows) {
worksheet.addRow(row)
}
for (let i = 1; i {
const firstCell = row.getCell(1)
firstCell.font = { …(firstCell.font || {}), bold: true, color: { argb: ‘FF111827’ } }
})
// Apply banded rows for data output. Confidential exports use a light red tone.
for (let rowIndex = dataStartRow; rowIndex <= worksheet.rowCount; rowIndex += 1) {
if ((rowIndex – dataStartRow) % 2 === 1) {
worksheet.getRow(rowIndex).fill = {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: isConfidential ? 'FFFFEEEE' : 'FFEEF5FF' },
}
}
}
// Format non-zero price values to 2 decimal places.
for (let rowIndex = dataStartRow; rowIndex <= worksheet.rowCount; rowIndex += 1) {
const row = worksheet.getRow(rowIndex)
for (let colIndex = 1; colIndex {
const colKey = columns[idx]?.key || ”
if (colKey === ‘product_description’) {
col.width = 42
} else if (colKey === ‘product_code’) {
col.width = 18
} else {
col.width = 14
}
})
const buffer = await workbook.xlsx.writeBuffer()
return new Uint8Array(buffer as ArrayBuffer)
}
Deno.serve(async (req) => {
if (req.method === ‘OPTIONS’) {
return new Response(‘OK’, {
headers: CORS_HEADERS,
})
}
if (req.method !== ‘POST’) {
return new Response(JSON.stringify({ success: false, error: ‘Method not allowed’ }), {
status: 405,
headers: { ‘Content-Type’: ‘application/json’, …CORS_HEADERS },
})
}
try {
const payload = await req.json()
const { customerCode, customerName, productClass, exportType, format, recipients, senderEmail } = payload
const requestedFormat = String(format || ‘xlsx’).toLowerCase() === ‘csv’ ? ‘csv’ : ‘xlsx’
const isConfidential = String(exportType || ”).toLowerCase() === ‘classified’
const exportedAt = new Date()
if (!customerCode) {
return new Response(
JSON.stringify({ success: false, error: ‘Missing customerCode’ }),
{ status: 400, headers: { ‘Content-Type’: ‘application/json’, …CORS_HEADERS } }
)
}
if (!recipients || !Array.isArray(recipients) || recipients.length === 0) {
return new Response(
JSON.stringify({ success: false, error: ‘No recipients configured’, code: ‘RECIPIENTS_REQUIRED’ }),
{ status: 400, headers: { ‘Content-Type’: ‘application/json’, …CORS_HEADERS } }
)
}
const exportData = await buildCustomerFullPriceListData(customerCode, productClass, exportType)
if (exportData.rows.length > MAX_EXCEL_ROWS || exportData.headers.length > MAX_EXCEL_COLUMNS) {
return new Response(
JSON.stringify({
success: false,
error: `Export exceeds supported formatted Excel limits (${MAX_EXCEL_ROWS} rows, ${MAX_EXCEL_COLUMNS} columns).`,
code: ‘EXPORT_LIMIT_EXCEEDED’,
rows: exportData.rows.length,
columns: exportData.headers.length,
}),
{
status: 413,
headers: { ‘Content-Type’: ‘application/json’, …CORS_HEADERS },
}
)
}
const isLargeConfidentialExport = isConfidential && exportData.rows.length > LARGE_CONFIDENTIAL_ROW_THRESHOLD
const effectiveFormat = requestedFormat
let confidentialPassword: string | null = null
if (isConfidential && effectiveFormat === ‘xlsx’) {
const passwordSeedEmail = String(senderEmail || recipients[0] || ”).trim().toLowerCase()
confidentialPassword = buildConfidentialPassword(passwordSeedEmail, exportedAt)
}
let fileBytes: Uint8Array
let fileName: string
let contentType: string
if (effectiveFormat === ‘xlsx’) {
fileBytes = await generateCrmPricingXlsxBytes(
exportData.headerInfo,
exportData.columns,
exportData.rows,
{ isConfidential }
)
if (isConfidential) {
fileBytes = encryptXlsxForOpenPassword(fileBytes, confidentialPassword || ”)
}
fileName = `crm-price-list-${customerCode}-${Date.now()}.xlsx`
contentType = ‘application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’
} else {
fileBytes = new TextEncoder().encode(generateCSV(exportData.headers, exportData.rows))
fileName = `crm-price-list-${customerCode}-${Date.now()}.csv`
contentType = ‘text/csv’
}
// Prefer Storage link delivery; fall back to attachment if Storage auth is unavailable.
const supabaseUrl = String(Deno.env.get(‘SUPABASE_URL’) || ”).trim()
const supabaseServiceKey = String(Deno.env.get(‘SUPABASE_SERVICE_ROLE_KEY’) || ”).trim()
const storagePath = `${customerCode}/${fileName}`
const storageHeaders = {
Authorization: `Bearer ${supabaseServiceKey}`,
apikey: supabaseServiceKey,
}
let downloadUrl: string | null = null
let useAttachmentFallback = false
if (!supabaseUrl || !supabaseServiceKey) {
console.warn(‘[CRMExportEmail] Storage disabled: missing SUPABASE_URL or SUPABASE_SERVICE_ROLE_KEY’)
useAttachmentFallback = true
} else {
try {
const uploadRes = await fetch(
`${supabaseUrl}/storage/v1/object/crm-exports/${storagePath}`,
{
method: ‘POST’,
headers: {
…storageHeaders,
‘Content-Type’: contentType,
‘x-upsert’: ‘true’,
},
body: fileBytes,
}
)
if (!uploadRes.ok) {
const uploadErr = await uploadRes.text()
throw new Error(`Storage upload failed: ${uploadRes.status} ${uploadErr}`)
}
// Generate a signed download URL valid for 48 hours.
const signRes = await fetch(
`${supabaseUrl}/storage/v1/object/sign/crm-exports/${storagePath}`,
{
method: ‘POST’,
headers: {
…storageHeaders,
‘Content-Type’: ‘application/json’,
},
body: JSON.stringify({ expiresIn: 172800 }),
}
)
if (!signRes.ok) {
const signErr = await signRes.text()
throw new Error(`Storage sign failed: ${signRes.status} ${signErr}`)
}
const signData = await signRes.json().catch(() => ({}))
const signedPath = signData?.signedURL || signData?.signedUrl || ”
if (!signedPath) {
throw new Error(‘Failed to create signed URL for export file’)
}
downloadUrl = `${supabaseUrl}/storage/v1${signedPath}`
} catch (storageError) {
console.error(‘[CRMExportEmail] Storage flow failed, falling back to attachment:’, storageError)
useAttachmentFallback = true
}
}
const classLabel = String(productClass || ”).trim() || ‘ALL’
const subject = isConfidential
? `CONFIDENTIAL – CRM Price List Export – ${customerCode}`
: `CRM Price List Export – ${customerCode}`
const htmlBody = `
${isConfidential ? ‘
CONFIDENTIAL
‘ : ”}
Your CRM price list export for customer ${customerCode} is ready.
Customer Name: ${customerName || customerCode}
Product Class: ${classLabel}
Rows: ${exportData.rows.length}
Generated: ${exportedAt.toISOString()}
${downloadUrl ? `
Download ${effectiveFormat.toUpperCase()} Export
This download link expires in 48 hours.
` : ‘
The export file is attached to this email.
‘}
`
if (useAttachmentFallback && fileBytes.byteLength > MAX_ATTACHMENT_FALLBACK_BYTES) {
return new Response(
JSON.stringify({
success: false,
error:
‘Export file is too large for direct email attachment. Configure Storage delivery for crm-export-email to send large ALL exports safely.’,
code: ‘ATTACHMENT_TOO_LARGE’,
bytes: fileBytes.byteLength,
}),
{
status: 413,
headers: { ‘Content-Type’: ‘application/json’, …CORS_HEADERS },
}
)
}
const emailResult = await sendEmail(
recipients,
subject,
htmlBody,
useAttachmentFallback
? {
filename: fileName,
contentBase64: toBase64(fileBytes),
contentType,
}
: undefined,
senderEmail || undefined
)
return new Response(
JSON.stringify({
success: true,
message: ‘CRM export email sent’,
recipientCount: recipients.length,
recipients,
format: effectiveFormat,
requestedFormat,
fileName,
providerMessageId: emailResult.messageId,
jobId: `crm_${Date.now()}`,
}),
{
status: 200,
headers: { ‘Content-Type’: ‘application/json’, …CORS_HEADERS },
}
)
} catch (error) {
console.error(‘[CRMExportEmail] Error:’, error)
return new Response(
JSON.stringify({
success: false,
error: error.message || ‘Email send failed’,
code: ‘EMAIL_SEND_FAILED’,
}),
{
status: 500,
headers: { ‘Content-Type’: ‘application/json’, …CORS_HEADERS },
}
)
}
})
Site Title 