242 lines
9.1 KiB
TypeScript
242 lines
9.1 KiB
TypeScript
import { createHash } from 'node:crypto'
|
|
import { readFileSync } from 'node:fs'
|
|
import { ensureGraph, getDb } from '../dist/db.js'
|
|
|
|
type SupplierSeed = { lei: string; name: string; countryCode: string; city: string }
|
|
type HubSeed = {
|
|
code: string
|
|
name: string
|
|
countryCode: string
|
|
latitude: number
|
|
longitude: number
|
|
functionCode: string
|
|
subdivision: string
|
|
}
|
|
type ProductSeed = { name: string; hsCodes: string[]; label: string }
|
|
|
|
const countryNames = new Intl.DisplayNames(['en'], { type: 'region' })
|
|
const productImages = [
|
|
'https://images.unsplash.com/photo-1625246333195-78d9c38ad449?auto=format&fit=crop&w=900&q=80',
|
|
'https://images.unsplash.com/photo-1574323347407-f5e1ad6d020b?auto=format&fit=crop&w=900&q=80',
|
|
'https://images.unsplash.com/photo-1592924357228-91a4daadcfea?auto=format&fit=crop&w=900&q=80',
|
|
]
|
|
|
|
function parseCsvLine(line: string): string[] {
|
|
const fields: string[] = []
|
|
let field = ''
|
|
let quoted = false
|
|
for (let index = 0; index < line.length; index += 1) {
|
|
const character = line[index]
|
|
if (character === '"') {
|
|
if (quoted && line[index + 1] === '"') {
|
|
field += '"'
|
|
index += 1
|
|
} else {
|
|
quoted = !quoted
|
|
}
|
|
} else if (character === ',' && !quoted) {
|
|
fields.push(field)
|
|
field = ''
|
|
} else {
|
|
field += character
|
|
}
|
|
}
|
|
if (quoted) throw new Error(`Unclosed CSV quote: ${line}`)
|
|
fields.push(field)
|
|
return fields
|
|
}
|
|
|
|
function stableUuid(namespace: string, value: string): string {
|
|
const hex = createHash('sha256').update(`${namespace}:${value}`).digest('hex')
|
|
return `${hex.slice(0, 8)}-${hex.slice(8, 12)}-4${hex.slice(13, 16)}-8${hex.slice(17, 20)}-${hex.slice(20, 32)}`
|
|
}
|
|
|
|
function normalize(value: string): string {
|
|
return value.normalize('NFKD').replace(/[^a-zA-Z0-9]/g, '').toLowerCase()
|
|
}
|
|
|
|
function readSuppliers(): SupplierSeed[] {
|
|
const suppliers = JSON.parse(
|
|
readFileSync(new URL('../data/africa_suppliers.json', import.meta.url), 'utf8'),
|
|
) as SupplierSeed[]
|
|
for (const supplier of suppliers) {
|
|
if (!supplier.lei || !supplier.name || supplier.countryCode.length !== 2) {
|
|
throw new Error(`Invalid Africa supplier reference: ${JSON.stringify(supplier)}`)
|
|
}
|
|
}
|
|
return suppliers
|
|
}
|
|
|
|
function readHubs(): HubSeed[] {
|
|
const csv = readFileSync(
|
|
new URL('../data/unlocode_africa_road_terminals.csv', import.meta.url),
|
|
'utf8',
|
|
)
|
|
return csv.trim().split(/\r?\n/).slice(1).map((line) => {
|
|
const [code, name, countryCode, latitude, longitude, functionCode, subdivision] = parseCsvLine(line)
|
|
const parsedLatitude = Number(latitude)
|
|
const parsedLongitude = Number(longitude)
|
|
if (!code || !name || countryCode.length !== 2 || !Number.isFinite(parsedLatitude) || !Number.isFinite(parsedLongitude)) {
|
|
throw new Error(`Invalid UN/LOCODE row: ${line}`)
|
|
}
|
|
return { code, name, countryCode, latitude: parsedLatitude, longitude: parsedLongitude, functionCode, subdivision }
|
|
})
|
|
}
|
|
|
|
function readProducts(): ProductSeed[] {
|
|
const csv = readFileSync(new URL('../data/africa_products.csv', import.meta.url), 'utf8')
|
|
const grouped = new Map<string, ProductSeed>()
|
|
for (const line of csv.trim().split(/\r?\n/).slice(1)) {
|
|
const [name, hsCode, label] = parseCsvLine(line)
|
|
if (!name || !/^\d{6}$/.test(hsCode) || !label) throw new Error(`Invalid product reference: ${line}`)
|
|
const current = grouped.get(name)
|
|
if (current === undefined) grouped.set(name, { name, hsCodes: [hsCode], label })
|
|
else current.hsCodes.push(hsCode)
|
|
}
|
|
return [...grouped.values()]
|
|
}
|
|
|
|
function transportTypes(functionCode: string): string[] {
|
|
const values: string[] = []
|
|
if (functionCode.includes('1')) values.push('sea')
|
|
if (functionCode.includes('2')) values.push('rail')
|
|
if (functionCode.includes('3')) values.push('auto')
|
|
if (values.length === 0) throw new Error(`Unsupported UN/LOCODE function: ${functionCode}`)
|
|
return values
|
|
}
|
|
|
|
function supplierHub(supplier: SupplierSeed, hubs: HubSeed[]): HubSeed {
|
|
const countryHubs = hubs.filter((hub) => hub.countryCode === supplier.countryCode)
|
|
if (countryHubs.length === 0) throw new Error(`No Africa hub for ${supplier.countryCode}`)
|
|
const city = normalize(supplier.city)
|
|
const cityHub = city.length > 0
|
|
? countryHubs.find((hub) => normalize(hub.name).includes(city) || city.includes(normalize(hub.name)))
|
|
: undefined
|
|
return cityHub ?? countryHubs[0]
|
|
}
|
|
|
|
async function upsertDocuments(collection: string, documents: Record<string, unknown>[]) {
|
|
const batchSize = 250
|
|
for (let offset = 0; offset < documents.length; offset += batchSize) {
|
|
await getDb().query(
|
|
`FOR document IN @documents
|
|
UPSERT { _key: document._key }
|
|
INSERT document
|
|
UPDATE document
|
|
IN @@collection`,
|
|
{ documents: documents.slice(offset, offset + batchSize), '@collection': collection },
|
|
)
|
|
}
|
|
}
|
|
|
|
await ensureGraph()
|
|
|
|
const timestamp = new Date().toISOString()
|
|
const suppliers = readSuppliers()
|
|
const hubs = readHubs()
|
|
const products = readProducts()
|
|
const supplierHubs = new Map(suppliers.map((supplier) => [supplier.lei, supplierHub(supplier, hubs)]))
|
|
const priorityHubCodes = new Set([...supplierHubs.values()].map((hub) => hub.code))
|
|
for (const hub of hubs) {
|
|
if (!hubs.some((candidate) => candidate.countryCode === hub.countryCode && priorityHubCodes.has(candidate.code))) {
|
|
priorityHubCodes.add(hub.code)
|
|
}
|
|
}
|
|
|
|
const hubNodes = hubs.map((hub) => {
|
|
const country = countryNames.of(hub.countryCode)
|
|
if (country === undefined) throw new Error(`Unknown country code: ${hub.countryCode}`)
|
|
return {
|
|
_key: `africa-${hub.code.toLowerCase()}`,
|
|
node_type: 'logistics',
|
|
name: hub.name,
|
|
latitude: hub.latitude,
|
|
longitude: hub.longitude,
|
|
country,
|
|
country_code: hub.countryCode,
|
|
transport_types: transportTypes(hub.functionCode),
|
|
navigation_priority: priorityHubCodes.has(hub.code) ? 1 : 0,
|
|
source: 'UN/LOCODE Africa',
|
|
locode: hub.code,
|
|
function_code: hub.functionCode,
|
|
subdivision: hub.subdivision,
|
|
synced_at: timestamp,
|
|
created_at: timestamp,
|
|
}
|
|
})
|
|
|
|
const supplierNodes = suppliers.map((supplier, index) => {
|
|
const hub = supplierHubs.get(supplier.lei)
|
|
if (hub === undefined) throw new Error(`Supplier hub is missing: ${supplier.lei}`)
|
|
const country = countryNames.of(supplier.countryCode)
|
|
if (country === undefined) throw new Error(`Unknown country code: ${supplier.countryCode}`)
|
|
return {
|
|
_key: stableUuid('africa-supplier', supplier.lei),
|
|
node_type: 'supplier',
|
|
name: supplier.name,
|
|
latitude: hub.latitude + ((index % 5) - 2) * 0.012,
|
|
longitude: hub.longitude + ((Math.floor(index / 5) % 5) - 2) * 0.012,
|
|
country,
|
|
country_code: supplier.countryCode,
|
|
supplier_logo_url: `https://ui-avatars.com/api/?name=${encodeURIComponent(supplier.name)}&background=0B6BFF&color=fff&bold=true`,
|
|
lei: supplier.lei,
|
|
city: supplier.city,
|
|
synced_at: timestamp,
|
|
created_at: timestamp,
|
|
}
|
|
})
|
|
|
|
const offerNodes: Record<string, unknown>[] = []
|
|
const offerEdges: Record<string, unknown>[] = []
|
|
for (const [supplierIndex, supplier] of suppliers.entries()) {
|
|
const hub = supplierHubs.get(supplier.lei)
|
|
if (hub === undefined) throw new Error(`Supplier hub is missing: ${supplier.lei}`)
|
|
const supplierNode = supplierNodes[supplierIndex]
|
|
for (let productOffset = 0; productOffset < 2; productOffset += 1) {
|
|
const product = products[(supplierIndex * 2 + productOffset) % products.length]
|
|
const productIndex = products.indexOf(product)
|
|
const quoteUuid = stableUuid('africa-quote', `${supplier.lei}:${product.name}`)
|
|
const offer = {
|
|
_key: quoteUuid,
|
|
node_type: 'offer',
|
|
name: `${supplier.name} — ${product.name}`,
|
|
latitude: Number(supplierNode.latitude) + (productOffset === 0 ? -0.006 : 0.006),
|
|
longitude: Number(supplierNode.longitude) + (productOffset === 0 ? 0.006 : -0.006),
|
|
country: supplierNode.country,
|
|
country_code: supplier.countryCode,
|
|
product_uuid: stableUuid('africa-product', product.name),
|
|
product_name: product.name,
|
|
product_image_url: productImages[productIndex % productImages.length],
|
|
supplier_uuid: supplierNode._key,
|
|
supplier_name: supplier.name,
|
|
supplier_logo_url: supplierNode.supplier_logo_url,
|
|
price_per_unit: String(700 + (supplierIndex % 10) * 65 + productOffset * 40),
|
|
currency: 'USD',
|
|
quantity: String(250 + (supplierIndex % 12) * 50 + productOffset * 25),
|
|
unit: 'ton',
|
|
synced_at: timestamp,
|
|
created_at: timestamp,
|
|
}
|
|
offerNodes.push(offer)
|
|
offerEdges.push({
|
|
_key: `africa-offer-${quoteUuid}`,
|
|
_from: `nodes/africa-${hub.code.toLowerCase()}`,
|
|
_to: `nodes/${quoteUuid}`,
|
|
to_uuid: quoteUuid,
|
|
to_name: offer.name,
|
|
to_latitude: offer.latitude,
|
|
to_longitude: offer.longitude,
|
|
distance_km: 2 + productOffset,
|
|
travel_time_seconds: 900 + productOffset * 300,
|
|
transport_type: 'offer',
|
|
created_at: timestamp,
|
|
synced_at: timestamp,
|
|
})
|
|
}
|
|
}
|
|
|
|
await upsertDocuments('nodes', [...hubNodes, ...supplierNodes, ...offerNodes])
|
|
await upsertDocuments('edges', offerEdges)
|
|
|
|
console.log(`Seeded Africa geo reference data: hubs ${hubNodes.length}, suppliers ${supplierNodes.length}, offers ${offerNodes.length}, edges ${offerEdges.length}`)
|