rds.ts 27.07.2024 (have valid parts)

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