@ -0,0 +1,121 @@
// regularVesselRds.ts
import {rdsConnection} from "@/lib/aws";
import type {Vessel, VesselDetails} from "@/types";
// Fetch regular vessel information by vessel_id or imo
export async function getRegularVessel(identifier: {
vessel_id?: string;
imo?: string;
}) {
const { vessel_id, imo } = identifier;
const query = vessel_id
? `SELECT v.vessel_id, v.dwt, v.imo, v.flag, v.mmsi, v.vessel_name, v.cargo_type, v.cargo_sub_type, v.year_of_build, v.maximum_draft, d.geared, d.gear_details
FROM spotship_vessel AS v
LEFT JOIN spotship_vesseldetails AS d ON v.vessel_id = d.vessel_id
WHERE v.vessel_id = ? LIMIT 1`
: `SELECT v.vessel_id, v.dwt, v.imo, v.flag, v.mmsi, v.vessel_name, v.cargo_type, v.cargo_sub_type, v.year_of_build, v.maximum_draft, d.geared, d.gear_details
FROM spotship_vessel AS v
LEFT JOIN spotship_vesseldetails AS d ON v.vessel_id = d.vessel_id
WHERE v.imo = ? LIMIT 1`;
const value = vessel_id || imo;
return await new Promise<{ vessel: Vessel; details: VesselDetails }[]>(
(resolve, reject) => {
rdsConnection.query(
query,
[value],
(
error: unknown,
elements: { vessel: Vessel; details: VesselDetails }[],
) => {
if (error) {
return reject(error);
}
return resolve(elements);
},
);
},
);
}
// Insert new regular vessel information
export async function insertRegularVessel(data: any) {
const { vessel, details } = data;
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)
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,
],
(error: unknown) => {
if (error) {
console.error("Insert spotship_vessel error: ", error); // Log error
return reject(error);
}
rdsConnection.query(
`INSERT INTO spotship_vesseldetails (vessel_id, geared, gear_details) VALUES (?, ?, ?)`,
[vessel.vessel_id, details.geared, details.gear_details],
(error: unknown) => {
if (error) {
console.error("Insert spotship_vesseldetails error: ", error); // Log error
return reject(error);
}
return resolve();
},
);
},
);
});
}
// Update existing regular vessel information
export async function updateRegularVessel(data: any) {
const { vessel, details } = data;
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 = ?, maximum_draft = ?
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.maximum_draft,
vessel.vessel_id,
],
(error: unknown) => {
if (error) {
console.error("Update spotship_vessel error: ", error); // Log error
return reject(error);
}
rdsConnection.query(
`UPDATE spotship_vesseldetails SET geared = ?, gear_details = ? WHERE vessel_id = ?`,
[details.geared, details.gear_details, vessel.vessel_id],
(error: unknown) => {
if (error) {
console.error("Update spotship_vesseldetails error: ", error); // Log error
return reject(error);
}
return resolve();
},
);
},
);
});
}