App.js

import pgPkg from 'pg';
const { Pool } = pgPkg;
const pool = new Pool()

const res = await pool.query('SELECT $1::text as message', ['DB Connection Valid!'])
console.log(res.rows[0].message)

import whatsappWebPkg from 'whatsapp-web.js';
const { Client, LocalAuth } = whatsappWebPkg;
import qrcode from 'qrcode-terminal';

const client = new Client({
  authStrategy: new LocalAuth(),
  puppeteer: {
    args: ['--no-sandbox', '--disable-setuid-sandbox'],
  }
});

client.on('qr', (qr) => {
  console.log('QR RECEIVED', qr);
  qrcode.generate(qr, {small: true});
});

client.on('ready', () => {
  console.log('WhatsApp Client is ready!');
});

async function authUser(phone) {
  const client = await pool.connect()
  try {
    phone = "+"+phone.split("@")[0]
    const userInfoRes = await client.query('SELECT * FROM person WHERE person.phone = $1', [phone]);
    if (userInfoRes.rows.length === 1) {
      let userInfo = userInfoRes.rows[0];
      const adminPermRes = await client.query('SELECT trainingunit.unit FROM trainingunit WHERE trainingunit.admin @> ARRAY[$1]', [userInfo["sccid"]]);
      userInfo["admin"] = [];
      adminPermRes.rows.forEach(row => userInfo["admin"].push(row["unit"]));
      client.release();
      return userInfo;
    } else {
      throw "[User Auth] "+phone+" have multiple records. Error Threw.";
    }
  } catch (err) {
    client.release();
    console.log(err);
    return false;
  }
}

async function saveEvent(task) {
  const client = await pool.connect()
  try {
    const eventInsertRes = await client.query('INSERT INTO event(unit, time, type) VALUES($1, $2, $3) RETURNING *', [task["unit"], task["time"], task["type"]]);
    client.release();
    return eventInsertRes.rows[0];
  } catch (err) {
    client.release();
    console.log(err);
    return false;
  }
}

async function searchUnitsEvent(units) {
  const client = await pool.connect()
  let events = {}
  try {
    const currentTime = Math.floor(Date.now() / 1000)
    for (let unit of units) {
      const eventsRes = await client.query('SELECT * FROM event WHERE unit = $1 AND time >= date(to_timestamp($2)) AND time <= date(to_timestamp($3))', [unit, currentTime-1036800, currentTime+1036800]);
      events[unit] = eventsRes.rows;
    }
    client.release();
    return events
  } catch (err) {
    client.release();
    console.log(err);
    return false;
  }
}

async function takeAttendance(attendanceList, event, recipent) {
  const pool_client = await pool.connect()
  const currentTime = Math.floor(Date.now() / 1000)
  for (let attendee of attendanceList) {
    try {
      await pool_client.query('INSERT INTO attendance(sccid, event, timestamp) VALUES($1, $2, to_timestamp($3))', [attendee, event, currentTime]);
    } catch (err) {
      client.sendMessage(recipent, "Cadet "+attendee+" unable to record attendance.")
    }
  }
  pool_client.release();
  return true;
}

async function writeShiplog(log, event, sccid, assign) {
  const client = await pool.connect()
  try {
    let writeShiplog = await client.query('INSERT INTO shiplog(event, person, log, assign) VALUES($1, $2, $3, $4) RETURNING id', [event, sccid, log, assign]);
    client.release();
    return writeShiplog.rows[0].id;
  } catch (err) {
    client.release();
    console.log(err);
    return false;
  }
}

let messageState = {};
const actionWord = "!";
const eventType = {"g": "General Meeting", "a": "Activity", "s": "Service"};

client.on('message', async message => {
  console.log(message.from+" Command")
  //Command (not in Chat State)
  if ((message.body.charAt(0) === actionWord)) {
    await handleCommand(message)
  }

  //Chat State
  await handleChatState(message)
});

async function handleCommand(message) {
  let auth = await authUser(message.from);
    switch (message.body.substring(1).split(" ")[0].trim().toLowerCase()) {

      case "testauth":
        if (auth) {
          client.sendMessage(message.from, JSON.stringify(auth))
        }
        break;

      case "cancel":
        delete messageState[message.from]
        message.reply("All ongoing task cancelled.")
        break;

      case "search":
        if (auth) {
          if (auth["admin"].length > 0) {
            let events = await searchUnitsEvent(auth["admin"]);
            let eventsString = ""
            Object.keys(events).forEach(unit => {
              eventsString += "\n"+unit
              events[unit].forEach(event => {
                eventsString += "\n```"+event["id"].slice(-5)+"``` - "+event["type"]
                eventsString += "\nat *"+ event["time"].toLocaleString('en-GB') +"*"
                eventsString += "\n"
              });
              eventsString += "\n"
            });
            client.sendMessage(message.from, "Events List:"+eventsString)
          } else {
            message.reply("Unauthorized.")
          }
        }
        break;

      case "newevent":
        if (auth && !(message.from in messageState)) {
          if (auth["admin"].length > 1) {
            messageState[message.from] = {"auth": auth, "state": 10}
          } else if (auth["admin"].length === 1) {
            messageState[message.from] = {"auth": auth, "state": 12, "task": {"unit": auth["admin"][0]}};
          } else {
            message.reply("Unauthorized.")
          }
          break;
        }
      
      case "shiplog":
        if (auth && !(message.from in messageState)) {
          messageState[message.from] = {"auth": auth, "state": 20}
          break;
        }

      case "assign":
        if (auth && !(message.from in messageState)) {
          messageState[message.from] = {"auth": auth, "state": 30}
          break;
        }

      default:
        message.reply("Unknown Command!")
        break;
    }
}

async function handleChatState(message) {
  let tmpState = 0;
  if (message.from in messageState) { tmpState = messageState[message.from]["state"]; }
  console.log(message.from + " CS " + tmpState);
  switch (tmpState) {

    case 10:
      let unitString = "";
      messageState[message.from]["auth"]["admin"].forEach((unit, i) => unitString += "\n" + (i + 1) + ". " + unit);
      message.reply("Please select a Training Ship to create an event.\n" + unitString);
      messageState[message.from]["state"] = 11;
      break;

    case 11:
      let unitSelection = parseInt(message.body);
      if (unitSelection && unitSelection > 0 && unitSelection < messageState[message.from]["auth"]["admin"].length) {
        messageState[message.from]["task"] = { "unit": messageState[message.from]["auth"]["admin"][unitSelection - 1] };
      } else {
        message.reply("Unparseable Input. Require Unit Number.");
        break;
      }
    case 12:
      client.sendMessage(message.from, "Please enter event time in yyyy/mm/dd hh:mm format.");
      messageState[message.from]["state"] = 13;
      break;

    case 13:
      if (Date.parse(message.body)) {
        let eventTime = new Date(message.body);
        message.reply("Inserted Time is " + eventTime.toLocaleString('en-GB'));
        let timeString = eventTime.toISOString().slice(0, 19).replace('T', ' ');
        messageState[message.from]["task"]["time"] = timeString;
      } else {
        message.reply("Unparseable Input. Require Date in yyyy/mm/dd hh:mm format.");
        break;
      }
    case 14:
      let eventTypeString = "";
      Object.keys(eventType).forEach(type => eventTypeString += "\n" + type + " - " + eventType[type]);
      client.sendMessage(message.from, "Please enter event type as following." + eventTypeString);
      messageState[message.from]["state"] = 15;
      break;

    case 15:
      message.body = message.body.toLowerCase()
      if (message.body in eventType) {
        messageState[message.from]["task"]["type"] = eventType[message.body];
      } else {
        message.reply("Unparseable Input. Require Event Type Code.");
        break;
      }
    case 16:
      client.sendMessage(message.from, "Please enter:\ny - Continue to input event shiplog.\na - To assign cadet to input event shiplog.\nn - Input details later.\nc - Cancel Create Event");
      messageState[message.from]["state"] = 17;
      break;

    case 17:
      message.body = message.body.toLowerCase()
      if (["y", "a", "n", "c"].includes(message.body)) {
        if (message.body === "c") {
          delete messageState[message.from];
          message.reply("All ongoing task cancelled.");
        } else {
          let saveRes = await saveEvent(messageState[message.from]["task"]);
          client.sendMessage(message.from, "Event Saved.")
          if (saveRes) {
            delete messageState[message.from]["task"];
            switch (message.body) {
              case "y":
                messageState[message.from]["task"] = { "event": saveRes };
                messageState[message.from]["state"] = 22;
                await handleChatState(message);
                break;
              case "a":
                messageState[message.from]["task"] = { "event": saveRes };
                messageState[message.from]["state"] = 32;
                await handleChatState(message);
                break;
              case "n":
                delete messageState[message.from];
                message.reply("Event Saved.");
                break;
            }
            break;
          } else {
            message.reply("Case 17: Unable to save event. Please contact Support.");
            break;
          }
        }
      } else {
        message.reply("Unparseable Input. Require Action Code.");
        break;
      }

    case 20:
      let cmdArg = message.body.split(" ")
      if (cmdArg.length === 1) {
        if (messageState[message.from]["auth"]["admin"].length > 0) {
          client.sendMessage(message.from, "Please select an event to complete shiplog.");
          let events = await searchUnitsEvent(messageState[message.from]["auth"]["admin"]);
          let eventsString = "";
          Object.keys(events).forEach(unit => {
            eventsString += "\n" + unit;
            events[unit].forEach(event => {
              eventsString += "\n```" + event["id"].slice(-6) + "``` - " + event["type"];
              eventsString += "\nat *"+ event["time"].toLocaleString('en-GB') +"*"
              eventsString += "\n"
            });
            eventsString += "\n";
          });
          client.sendMessage(message.from, eventsString);
          messageState[message.from]["state"] = 21;
        } else {
          message.reply("Unauthorized.")
        }
      } else {
        messageState[message.from]["state"] = 19;
        await handleChatState(message)
      }
      break;
    
    case 19:
      let eventId = message.body.split(" ")[1]
      const eventIdRes = await pool.query('SELECT * FROM event WHERE id::text LIKE $1 AND event.editor @> ARRAY[$2]', ["%"+eventId, messageState[message.from]["auth"]["sccid"]])
      if (eventIdRes.rows.length === 1) {
        messageState[message.from]["task"] = { "event": eventIdRes.rows[0], "assigned": true };
        messageState[message.from]["state"] = 22;
        await handleChatState(message)
      } else {
        message.reply("Case 21-19: Not Found. Please contact Support.");
      }
      break;

    case 21: {
      const currentTime = Math.floor(Date.now() / 1000);
      const eventRes = await pool.query('SELECT * FROM event WHERE id::text LIKE $1 AND time >= date(to_timestamp($2)) AND time <= date(to_timestamp($3))', ["%" + message.body, currentTime - 2073600, currentTime + 2073600]);
      if (eventRes.rows.length === 1) {
        messageState[message.from]["task"] = { "event": eventRes.rows[0], "assigned": false };
      } else {
        message.reply("Case 21: Not Found. Please contact Support.");
        break;
      }
    }
    case 22:
      client.sendMessage(message.from, "You are writing shiplog to event \n```" + messageState[message.from]["task"]["event"]["id"].slice(-6) + "``` - " + messageState[message.from]["task"]["event"]["type"] + ".");
      const attendanceRes = await pool.query('SELECT attendance.sccid, person.cname, person.ename FROM attendance INNER JOIN person ON attendance.sccid = person.sccid WHERE attendance.event = $1', [messageState[message.from]["task"]["event"]["id"]]);
      if (attendanceRes.rows.length === 0) {
        client.sendMessage(message.from, "Current attendance is empty.");
        messageState[message.from]["task"]["attendance"] = [];
      } else {
        let attendanceString = "";
        let attendanceList = [];
        for (let person of attendanceRes.rows) {
          attendanceList.push(person.sccid);
          attendanceString += "\n" + person.sccid + " " + person.cname + " " + person.ename;
        }
        messageState[message.from]["task"]["attendance"] = attendanceList;
        client.sendMessage(message.from, "Current attendance:" + attendanceString);
      }
      client.sendMessage(message.from, "Would you like to take attendance? y/n");
      messageState[message.from]["state"] = 23;
      break;

    case 23:
      message.body = message.body.toLowerCase()
      if (["y", "n"].includes(message.body)) {
        if (message.body === "y") {
          client.sendMessage(message.from, "Please type attended cadets SCCID line by line.");
          messageState[message.from]["state"] = 24;
        } else {
          messageState[message.from]["state"] = 25;
          await handleChatState(message);
        }
      } else {
        message.reply("Unparseable Input. Require Action Code.");
      }
      break;

    case 24:
      let attendanceList = message.body.split("\n");
      attendanceList = attendanceList.map(sccid => sccid.trim());
      attendanceList = attendanceList.filter(sccid => !(messageState[message.from]["task"]["attendance"].includes(sccid)));
      await takeAttendance(attendanceList, messageState[message.from]["task"]["event"]["id"], message.from);
      messageState[message.from]["state"] = 25;

    case 25:
      const shiplogRes = await pool.query('SELECT shiplog.event, shiplog.person, shiplog.log, shiplog.timestamp, person.cname, person.ename FROM shiplog INNER JOIN person ON shiplog.person = person.sccid WHERE shiplog.event = $1 ORDER BY shiplog.timestamp DESC', [messageState[message.from]["task"]["event"]["id"]]);
      if (shiplogRes.rows.length === 0) {
        client.sendMessage(message.from, "Current shiplog is empty.");
        messageState[message.from]["task"]["shiplog"] = "";
        client.sendMessage(message.from, "Would you like to write shiplog? y/n");
      } else {
        messageState[message.from]["task"]["shiplog"] = shiplogRes.rows[0].log;
        client.sendMessage(message.from, "Current shiplog is written by:\n*" + shiplogRes.rows[0].person + " " + shiplogRes.rows[0].cname + " " + shiplogRes.rows[0].ename + "*\nat *"+shiplogRes.rows[0].timestamp.toLocaleString('en-GB')+"*\n\n_Shiplog:_\n" + shiplogRes.rows[0].log);
        client.sendMessage(message.from, "How would you like to edit shiplog?\ny - Overwrite Shiplog\na - Append Shiplog\nn - No edit require");
      }
      messageState[message.from]["state"] = 26;
      break;

    case 26:
      message.body = message.body.toLowerCase()
      if (["y", "a", "n"].includes(message.body)) {
        messageState[message.from]["task"]["state"] = message.body;
        switch (message.body) {
          case "y":
            if (messageState[message.from]["task"]["shiplog"] != "") {
              client.sendMessage(message.from, "Here is pervious shiplog for your reference.");
              client.sendMessage(message.from, messageState[message.from]["task"]["shiplog"]);
            }
            client.sendMessage(message.from, "Please enter the shiplog.");
            messageState[message.from]["state"] = 27;
            break;

          case "a":
            client.sendMessage(message.from, "Please enter to append after on the shiplog.");
            messageState[message.from]["state"] = 27;
            break;

          case "n":
            delete messageState[message.from];
            message.reply("Shiplog Completed.");
            break;
        }
      } else {
        message.reply("Unparseable Input. Require Action Code.")
      }
      break;

    case 27:
      let shiplog = message.body.trim();
      if (messageState[message.from]["task"]["state"] === "a") { shiplog = messageState[message.from]["task"]["shiplog"] + "\n\n" + shiplog; }
      let writeShiplogRes = await writeShiplog(shiplog, messageState[message.from]["task"]["event"]["id"], messageState[message.from]["auth"]["sccid"], messageState[message.from]["task"]["assigned"]);
      if (writeShiplogRes) {
        messageState[message.from]["task"]["id"] = writeShiplogRes;
        message.reply("Shiplog Submitted.");
        client.sendMessage(message.from, "Would you like to submit photo? y/n")
        messageState[message.from]["state"] = 28;
      } else {
        message.reply("Case 27: Unable to save shiplog. Please contact Support.");
      }
      break;

    case 28:
      message.body = message.body.toLowerCase()
      if (["y", "n"].includes(message.body)) {
        if (message.body === "y") {
          client.sendMessage(message.from, "Please take photos of the form.");
          client.sendMessage(message.from, "Type ```@complete``` after you finish.");
          messageState[message.from]["state"] = 29;
        } else {
          delete messageState[message.from];
          message.reply("Shiplog Completed.");
        }
      } else {
        message.reply("Unparseable Input. Require Action Code.");
      }
      break;

    case 29:
      if (message.hasMedia) {
        const media = await message.downloadMedia();
        if (media) {
          const pool_client = await pool.connect()
          try{
            const mediaRes = await pool_client.query('INSERT INTO media(data, mime) VALUES(decode($1, \'base64\'), $2) RETURNING id', [media.data, media.mimetype])
            await pool_client.query('UPDATE shiplog SET media = array_append(media, $1) WHERE id = $2', [mediaRes.rows[0].id, messageState[message.from]["task"]["id"]])
            message.reply("Well Received.")
          } catch (err) {
            console.log(err)
            message.reply("Case 29: Unable to save media. Please contact Support.");
          } finally {
            pool_client.release()
          }
        } else {
          message.reply("Case 29: Unable to download media. Please contact Support.");
        }
      } else if (message.body === "@complete") {
        delete messageState[message.from];
        message.reply("Shiplog Completed.");
      } else {
        message.reply("Unparseable Input. Require Media.");
      }
      break;

    case 30:
      if (messageState[message.from]["auth"]["admin"].length > 0) {
        client.sendMessage(message.from, "Please select an event to assign cadet to complete shiplog.");
        let events = await searchUnitsEvent(messageState[message.from]["auth"]["admin"]);
        let eventsString = "";
        Object.keys(events).forEach(unit => {
          eventsString += "\n" + unit;
          events[unit].forEach(event => {
            eventsString += "\n```" + event["id"].slice(-6) + "``` - " + event["type"];
            eventsString += "\nat *"+ event["time"].toLocaleString('en-GB') +"*"
            eventsString += "\n"
          });
          eventsString += "\n";
        });
        client.sendMessage(message.from, eventsString);
        messageState[message.from]["state"] = 31;
      } else {
        message.reply("Unauthorized.")
      }
      break;

    case 31: {
      let currentTime = Math.floor(Date.now() / 1000);
      const eventRes = await pool.query('SELECT * FROM event WHERE id::text LIKE $1 AND time >= date(to_timestamp($2)) AND time <= date(to_timestamp($3))', ["%" + message.body, currentTime - 2073600, currentTime + 2073600]);
      if (eventRes.rows.length === 1) {
        messageState[message.from]["task"] = { "event": eventRes.rows[0] };
      } else {
        message.reply("Case 31: Not Found. Please contact Support");
        break;
      }
    }
    case 32:
      client.sendMessage(message.from, "You are assigning cadet to writing shiplog to event \n```" + messageState[message.from]["task"]["event"]["id"].slice(-6) + "``` - " + messageState[message.from]["task"]["event"]["type"] + ".");
      client.sendMessage(message.from, "Please type in assigned cadet SCCID.");
      messageState[message.from]["state"] = 33;
      break;

    case 33:
      const cadetRes = await pool.query('SELECT * FROM person WHERE sccid = $1', [message.body.trim()])
      if (cadetRes.rows.length === 1) {
        client.sendMessage(message.from, "You have assigned cadet "+cadetRes.rows[0].sccid + " " + cadetRes.rows[0].cname + " " + cadetRes.rows[0].ename+" to complete the shiplog.")
        try {
          await pool.query('UPDATE event SET editor = array_append(editor, $1) WHERE id = $2', [cadetRes.rows[0].sccid, messageState[message.from]["task"]["event"]["id"]]);
          client.sendMessage(cadetRes.rows[0].phone.slice(1)+"@c.us", "You are requested to complete shiplog for\n```" + messageState[message.from]["task"]["event"]["id"].slice(-6) + "``` - " + messageState[message.from]["task"]["event"]["type"] + "\nat *" + messageState[message.from]["task"]["event"]["time"].toLocaleString('en-GB')+"* \n\nPlease start completing it by typing \n```!shiplog "+messageState[message.from]["task"]["event"]["id"].slice(-6) + "```")
          delete messageState[message.from];
        } catch (err) {
          console.log(err)
          client.sendMessage("Case 33: Unable to assign editor. Please contact Support.")
        }
        break;
      } else {
        message.reply("Case 33: Not Found. Please contact Support.");
        break;
      }

  }
}

client.initialize();

process.on('SIGINT', async function() {
  console.log("Catch SIGINT");
  await pool.end();
  client.logout();
  process.exit();
});