import { rdsConnection } from "@/lib/aws";
import type { Country, LineUp, Port, Position, Vessel } from "@/types";
import { emptyVessel } from "@/types";
import type { MysqlError } from "mysql";
import mysql from "mysql";
import { VesselDetails } from "@/types/vessel";
export async function getVesselFromIMO(imo: string) {
return await new Promise<Vessel[]>((resolve, reject) => {
rdsConnection.query(
`SELECT vessel_id, dwt, imo, flag, mmsi, vessel_name, cargo_type, cargo_sub_type, year_of_build, manager FROM spotship_vessel WHERE imo LIKE ? LIMIT 20`,
[`${imo}%`],
(error: unknown, elements: Vessel[]) => {
if (error) {
return reject(error);
}
return resolve(elements);
},
);
});
}
export async function getVesselFromMMSI(mmsi: string) {
return await new Promise<Vessel[]>((resolve, reject) => {
rdsConnection.query(
`SELECT vessel_id, dwt, imo, flag, mmsi, vessel_name, cargo_type, cargo_sub_type, year_of_build, manager FROM spotship_vessel WHERE mmsi LIKE ? LIMIT 20`,
[`${mmsi}%`],
(error: unknown, elements: Vessel[]) => {
if (error) {
return reject(error);
}
return resolve(elements);
},
);
});
}
export async function getVesselFromName(name: string) {
return await new Promise<Vessel[]>((resolve, reject) => {
rdsConnection.query(
`SELECT vessel_id, dwt, imo, flag, mmsi, vessel_name, cargo_type, cargo_sub_type, year_of_build, manager FROM spotship_vessel WHERE vessel_name LIKE ? ORDER BY vessel_name LIMIT 250`,
[`%${name.toUpperCase()}%`],
(error: unknown, elements: Vessel[]) => {
if (error) {
return reject(error);
}
return resolve(elements);
},
);
});
}
export async function getVesselFromId(vessel_id: string) {
return await new Promise<Vessel[]>((resolve, reject) => {
rdsConnection.query(
`SELECT vessel_id, dwt, imo, flag, mmsi, vessel_name, cargo_type, cargo_sub_type, year_of_build FROM spotship_vessel WHERE vessel_id = ?`,
[vessel_id],
(error: unknown, elements: Vessel[]) => {
if (error) {
return reject(error);
}
return resolve(elements);
},
);
});
}
export async function getPortFromName(name: string) {
return await new Promise<Port[]>((resolve, reject) => {
rdsConnection.query(
`SELECT port.id, port.name, port.locode, country.name AS country, country.code as flag, port.longitude, port.latitude, port.number_of_berths, port.port_region, port.super_region, port.type FROM spotship_port AS port INNER JOIN spotship_country AS country ON port.country_id = country.id WHERE port.name LIKE ? ORDER BY port.name LIMIT 1000`,
[`%${name.toUpperCase()}%`],
(error: unknown, elements: Port[]) => {
if (error) {
return reject(error);
}
return resolve(elements);
},
);
});
}
export async function getPortFromId(port_id: number) {
return await new Promise<Port[]>((resolve, reject) => {
rdsConnection.query(
`SELECT port.name, port.locode, country.name AS country, country.code as flag, port.longitude, port.latitude, port.number_of_berths, port.port_region, port.super_region, port.type FROM spotship_port AS port INNER JOIN spotship_country AS country ON port.country_id = country.id WHERE port.id = ?`,
[port_id],
(error: unknown, elements: Port[]) => {
if (error) {
return reject(error);
}
return resolve(elements);
},
);
});
}
export async function getCountryFromName(name: string) {
return await new Promise<Country[]>((resolve, reject) => {
rdsConnection.query(
`SELECT id, name, code FROM spotship_country WHERE name LIKE ? ORDER BY name LIMIT 1000`,
[`${name}%`],
(error: unknown, elements: Country[]) => {
if (error) {
return reject(error);
}
return resolve(elements);
},
);
});
}
export async function getRegionFromName(name: string) {
return await new Promise<Array<{ name: string }>>((resolve, reject) => {
rdsConnection.query(
`SELECT DISTINCT port_region AS name FROM spotship_port WHERE port_region LIKE ? ORDER BY port_region LIMIT 1000`,
[`%${name}%`],
(error: unknown, elements: Array<{ name: string }>) => {
if (error) {
return reject(error);
}
return resolve(elements);
},
);
});
}
export async function getSuperRegionFromName(name: string) {
return await new Promise<Array<{ name: string }>>((resolve, reject) => {
rdsConnection.query(
`SELECT DISTINCT super_region AS name FROM spotship_port WHERE super_region LIKE ? ORDER BY super_region LIMIT 1000`,
[`%${name}%`],
(error: unknown, elements: Array<{ name: string }>) => {
if (error) {
return reject(error);
}
return resolve(elements);
},
);
});
}
function translateDirtyClean(dirtyClean: "CPP" | "DPP" | "N/A" | null) {
switch (dirtyClean) {
case "CPP":
return "WET - CLEAN";
case "DPP":
return "WET - DIRTY";
case "N/A":
return "WET";
default:
return null;
}
}
function dirtyOrClean(dirtyClean: "CPP" | "DPP" | "N/A" | null) {
switch (dirtyClean) {
case "CPP":
return "CLEAN";
case "DPP":
return "DIRTY";
case "N/A":
return "";
default:
return "";
}
}
async function updateVesselDirtyClean(
vessel_id: string,
status: "CPP" | "DPP" | "N/A" | null,
) {
return await new Promise((resolve, reject) => {
rdsConnection.query(
`UPDATE spotship_vessel SET dirty_clean = ? WHERE vessel_id = ?`,
[dirtyOrClean(status), vessel_id],
(error: unknown, elements: unknown) => {
if (error) {
console.error(
"Error origin: Updating dirty/clean in Spotship Database | ",
error,
);
return reject(error);
}
return resolve(elements);
},
);
});
}
export function insertPositionValue(
filename: string,
broker: number,
source: string,
position: Position,
) {
return `(
${mysql.escape(broker)},
'DATA TEAM',
${mysql.escape(
source.indexOf("<") === -1 || source.indexOf(">") === -1
? source
: source.substring(source.indexOf("<") + 1, source.lastIndexOf(">")),
)},
${mysql.escape(filename)},
${mysql.escape(position.vessel.vessel_id)},
${mysql.escape(position.open?.startDate ?? "")},
${mysql.escape(position.open?.endDate ?? "")},
${mysql.escape(position.port.id)},
${mysql.escape(position.status)},
${mysql.escape(position.cargo)},
${mysql.escape(position.notes)},
${mysql.escape(translateDirtyClean(position.dirtyClean))},
'AVAILABLE',
${mysql.escape(new Date().toISOString())}
),`;
}
export async function bulkInsertPosition(
brokers: Array<number>,
positions: Array<Position>,
filename: string,
source: string,
) {
if (positions.length == 0 || brokers.length == 0) {
console.error(
"Error origin: Inserting Position into Spotship Database | Broker list or positions list was empty",
);
return;
}
let query = `INSERT INTO spotship_vesselavailability
(broker_id, source, origin, origin_id, vessel_id, open_date, close_date, open_location, status, last_3_cargoes, details, wet_dry, current_status, created_at)
VALUES `;
brokers.forEach((id) => {
if (id > 0) {
positions.forEach((position: Position) => {
query += insertPositionValue(filename, id, source, position);
});
}
});
// Remove last comma
query = query.replace(/,$/, "");
return await new Promise((resolve, reject) => {
rdsConnection.query(
query,
async (error: MysqlError | null, elements: unknown) => {
if (error) {
if (error.code === "ER_LOCK_DEADLOCK") {
console.warn(
"Warning origin: Inserting Position into Spotship Database - Retrying due to ER_LOCK_DEADLOCK | ",
error,
);
// N.B. Recursion used for infinite retries.
return bulkInsertPosition(brokers, positions, filename, source);
}
console.error(
"Error origin: Inserting Position into Spotship Database | ",
error,
);
return reject(error);
}
await Promise.all(
positions.map(async (position) => {
try {
await updateVesselDirtyClean(
position.vessel.vessel_id,
position.dirtyClean,
);
} catch (e) {
console.error(
"Error origin: Updating vessel dirty/clean in Spotship Database | ",
e,
);
}
}),
);
return resolve(elements);
},
);
});
}
export function insertLineupValue(
filename: string,
broker: number,
source: string,
lineup: LineUp,
) {
let open = lineup.eta ?? new Date();
if (lineup.etb !== null && open < lineup.etb) {
open = lineup.etb;
}
if (lineup.etd !== null && open < lineup.etd) {
open = lineup.etd;
}
return `(
${mysql.escape(broker)},
'DATA TEAM',
${mysql.escape(
source.indexOf("<") === -1 || source.indexOf(">") === -1
? source
: source.substring(source.indexOf("<") + 1, source.lastIndexOf(">")),
)},
${mysql.escape(filename)},
${mysql.escape(lineup.vessel.vessel_id)},
${mysql.escape(lineup.port.id)},
${mysql.escape(
`${
lineup.toleranceType === "Min/Max"
? `${lineup.quantity} - ${lineup.tolerance}`
: `${lineup.quantity}`
}${lineup.unit}${
lineup.toleranceType === "%" || lineup.toleranceType === "Unit"
? ` ± ${lineup.tolerance}${lineup.toleranceType === "%" ? "%" : ""}`
: ""
} ${lineup.cargo}`,
)},
'AVAILABLE',
${mysql.escape(new Date().toISOString())},
${mysql.escape(lineup.eta?.toISOString())},
${mysql.escape(lineup.etb?.toISOString())},
${mysql.escape(lineup.etd?.toISOString())},
${mysql.escape(lineup.loading !== null ? (lineup.loading ? 1 : 0) : null)},
${mysql.escape(`${lineup.terminal !== null && lineup.terminal !== "" ? `TERMINAL: ${lineup.terminal}` : ""} ${lineup.pier !== null && lineup.pier !== "" ? `PIER: ${lineup.pier}` : ""} ${lineup.destination !== null && lineup.destination.name !== "" ? `DESTINATION: ${lineup.destination.name} ` : ""}`)},
${mysql.escape(open.toISOString())}
),`;
}
export async function bulkInsertLineup(
brokers: Array<number>,
lineups: Array<LineUp>,
filename: string,
source: string,
) {
if (lineups.length == 0 || brokers.length == 0) {
console.error(
"Error origin: Inserting Line ups into Spotship Database | Broker list or line up list was empty",
);
return;
}
let query = `INSERT INTO spotship_vesselavailability
(broker_id, source, origin, origin_id, vessel_id, open_location, last_3_cargoes, current_status, created_at, et_a, etb, etd, load_discharge, details, open_date)
VALUES `;
brokers.forEach((id) => {
if (id > 0) {
lineups.forEach((lineup: LineUp) => {
query += insertLineupValue(filename, id, source, lineup);
});
}
});
// Remove last comma
query = query.replace(/,$/, "");
return await new Promise((resolve, reject) => {
rdsConnection.query(
query,
async (error: MysqlError | null, elements: unknown) => {
if (error) {
if (error.code === "ER_LOCK_DEADLOCK") {
console.warn(
"Warning origin: Inserting Lineup into Spotship Database - Retrying due to ER_LOCK_DEADLOCK | ",
error,
);
// N.B. Recursion used for infinite retries.
return bulkInsertLineup(brokers, lineups, filename, source);
}
console.error(
"Error origin: Inserting Line up into Spotship Database | ",
error,
);
return reject(error);
}
return resolve(elements);
},
);
});
}
// QUERY FOR VESSEL INFO - Raf
export async function queryVesselFromIMO(imo: string) {
return await new Promise<Vessel[]>((resolve, reject) => {
rdsConnection.query(
`SELECT vessel_id, dwt, imo, flag, mmsi, vessel_name, cargo_type, cargo_sub_type, year_of_build, maximum_draft, manager FROM spotship_vessel WHERE imo LIKE ? LIMIT 20`,
[`${imo}%`],
(error: unknown, elements: Vessel[]) => {
if (error) {
return reject(error);
}
return resolve(elements);
},
);
});
}
// QUERY FOR VESSEL DETAILS INFO - Raf
export async function queryVesselDetails(vessel_id: string) {
return await new Promise<VesselDetails[]>((resolve, reject) => {
rdsConnection.query(
`SELECT vessel_id, geared, gear_details FROM spotship_vesseldetails WHERE vessel_id = ?`,
[vessel_id],
(error: unknown, elements: VesselDetails[]) => {
if (error) {
return reject(error);
}
return resolve(elements);
},
);
});
}
// ADD VESSEL TO DB - Raf
export async function addVessel(vessel: Vessel) {
return await new Promise<void>((resolve, reject) => {
rdsConnection.query(
`INSERT INTO spotship_vessel (vessel_id, dwt, imo, flag, mmsi, vessel_name, cargo_type, cargo_sub_type, year_of_build, maximum_draft, manager) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
[
vessel.vessel_id,
vessel.dwt,
vessel.imo,
vessel.flag,
vessel.mmsi,
vessel.vessel_name,
vessel.cargo_type,
vessel.cargo_sub_type,
vessel.year_of_build,
vessel.maximum_draft,
vessel.manager,
],
(error: unknown) => {
if (error) {
return reject(error);
}
return resolve();
},
);
});
}
// ADD VESSEL DETAILS TO DB - Raf
export async function addVesselDetails(details: VesselDetails) {
return await new Promise<void>((resolve, reject) => {
rdsConnection.query(
`INSERT INTO spotship_vesseldetails (vessel_id, geared, gear_details) VALUES (?, ?, ?)`,
[details.vessel_id, details.geared, details.gear_details],
(error: unknown) => {
if (error) {
return reject(error);
}
return resolve();
},
);
});
}
// UPDATE EXISTING VESSEL - Raf
export async function modernizeVessel(vessel: Vessel) {
return await new Promise<void>((resolve, reject) => {
rdsConnection.query(
`UPDATE spotship_vessel SET dwt = ?, imo = ?, flag = ?, mmsi = ?, vessel_name = ?, cargo_type = ?, cargo_sub_type = ?, year_of_build = ?, beam = ?, overall_length = ?, maximum_draft = ?, manager = ? WHERE vessel_id = ?`,
[
vessel.dwt,
vessel.imo,
vessel.flag,
vessel.mmsi,
vessel.vessel_name,
vessel.cargo_type,
vessel.cargo_sub_type,
vessel.year_of_build,
vessel.beam,
vessel.overall_length
vessel.maximum_draft,
vessel.manager,
vessel.vessel_id,
],
(error: unknown) => {
if (error) {
return reject(error);
}
return resolve();
},
);
});
}
// UPDATE EXISTING VESSEL DETAILS - Raf
export async function modernizeVesselDetails(details: VesselDetails) {
return await new Promise<void>((resolve, reject) => {
rdsConnection.query(
`UPDATE spotship_vesseldetails SET geared = ?, gear_details = ? WHERE vessel_id = ?`,
[details.geared, details.gear_details, details.vessel_id],
(error: unknown) => {
if (error) {
return reject(error);
}
return resolve();
},
);
});
}
export async function inssertVesselDetail(port: Port) {
return await new Promise((resolve, reject) => {
rdsConnection.query(
`INSERT INTO spotship_vesseldetails (geared, gear_details) VALUES (?, ?)`,
[emptyVessel.vessel_id, emptyVessel.dwt],
(error: unknown, elements: unknown) => {
if (error) {
console.error(
"Error origin: Inserting a vessel into Spotship Database | ",
error,
);
return reject(error);
}
return resolve(elements);
},
);
});
}
export async function insertPort(port: Port) {
return await new Promise((resolve, reject) => {
rdsConnection.query(
`INSERT INTO spotship_port (name, country_id, latitude, longitude, locode, port_region, super_region, type) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
[
port.name,
port.country,
port.latitude,
port.longitude,
port.locode,
port.port_region,
port.super_region,
port.type,
],
(error: unknown, elements: unknown) => {
if (error) {
console.error(
"Error origin: Inserting a port into Spotship Database | ",
error,
);
return reject(error);
}
return resolve(elements);
},
);
});
}
export async function editVessel(vessel_id: Vessel, name: string) {
return await new Promise((resolve, reject) => {
rdsConnection.query(
`UPDATE spotship_vessel SET vessel_name = ? WHERE vessel_id = ?`,
[name, vessel_id],
(error: unknown, elements: unknown) => {
if (error) {
console.error(
"Error origin: Updating a vessel in Spotship Database | ",
error,
);
return reject(error);
}
return resolve(elements);
},
);
});
}
