const { pool } = require("../config/database/database.config");

/*
Class responsible for making queries to the 
involvementLookup and involvementType database.
*/

/*
Function to add an involvement type to the database
*/
async function addInvolvementTypeIfNotExists(description) {
  console.log("adding involvement type: ", description);

  // Don't add involvement type if it exists
  const existingType = await getInvolvementType(description);
  if (existingType) {
    console.log("Involvement type already exists: ", description);
    return;
  }

  // Insert SQL query
  const query = `INSERT INTO involvementType (description) VALUES (?)`;

  // Attempt to insert involvement type into table
  try {
    await pool.query(query, [description]);
    console.log(`Successfully added involvement type: ${description}`);
  } catch (error) {
    console.error(`Error adding involvement type: ${description}`, error);
    throw error;
  }
}

async function getInvolvementType(description) {
  // SQL query to get a certain involvement type
  const query = "SELECT * FROM involvementType WHERE description = ?";

  try {
    console.log("Getting involvement type: ", description);
    const [rows] = await pool.query(query, [description]);
    if (rows.length > 0) {
      console.log("Found involvement type: ", description);
      return rows[0];
    } else {
      console.log("Involvement type not found: ", description);
      return null;
    }
  } catch (error) {
    console.error(`Error fetching involvement type ${description}:`, error);
    throw error;
  }
}

/*
Function to add an involvement to the involvementLookup table
*/
async function addInvolvementIfNotExists(involvementTypeId) {
  console.log("Adding involvement for type ID: ", involvementTypeId);

   // Don't add involvement if it exists
   const existingInvolvement = await getInvolvement(involvementTypeId);
   if (existingInvolvement) {
     console.log("Involvement already exists: ", involvementTypeId);
     return;
   }

  // Insert SQL query
  const query = `INSERT INTO involvementLookup (involvementType) VALUES (?)`;

  // Attempt to insert involvement into table
  try {
    await pool.query(query, [involvementTypeId]);
    console.log(`Successfully added involvement with type ID: ${involvementTypeId}`);
  } catch (error) {
    console.error(`Error adding involvement with type ID: ${involvementTypeId}`, error);
    throw error;
  }
}

async function getInvolvement(involvementType) {
    // SQL query to get a certain involvement
    const query = "SELECT * FROM involvementLookup WHERE involvementType = ?";

    try {
    console.log("Getting involvement: ", involvementType);
    const [rows] = await pool.query(query, [involvementType]);
    if (rows.length > 0) {
        console.log("Found involvement: ", involvementType);
        return rows[0];
    } else {
        console.log("Involvement not found: ", involvementType);
        return null;
    }
    } catch (error) {
    console.error(`Error fetching involvement ${involvementType}:`, error);
    throw error;
    }
}

async function checkInvolvementTypeTableExistence() {
  try {
    const [rows] = await pool.query("SHOW TABLES LIKE 'involvementType'");
    if (rows.length > 0) {
      console.log("Table `involvementType` exists");
    } else {
      console.log("Table `involvementType` does not exist");
    }
  } catch (error) {
    console.error("Error checking involvementType table existence:", error);
  }
}

async function checkInvolvementLookupTableExistence() {
  try {
    const [rows] = await pool.query("SHOW TABLES LIKE 'involvementLookup'");
    if (rows.length > 0) {
      console.log("Table `involvementLookup` exists");
    } else {
      console.log("Table `involvementLookup` does not exist");
    }
  } catch (error) {
    console.error("Error checking involvementLookup table existence:", error);
  }
}

module.exports = {
  checkInvolvementTypeTableExistence,
  checkInvolvementLookupTableExistence,
  addInvolvementTypeIfNotExists,
  getInvolvementType,
  addInvolvementIfNotExists,
  getInvolvement
};