@ -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();
          },
        );
      },
    );
  });
}