import { ResultSetHeader, RowDataPacket } from "mysql2";
import DB from "../config/database/db";
import noticePollResponseTypes from "../schemas/noticePollResponse.schema";

const noticePollResponseDB: any = {};

noticePollResponseDB.addPollResponse = async ({
  clientId,
  tenantId,
  optionId,
}: noticePollResponseTypes) => {
  const query =
    "INSERT INTO NoticePollResponses (clientId, tenantId, optionId) VALUES (?, ?, ?)";
  const data = [clientId, tenantId, optionId];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return rows.affectedRows > 0 ? true : false;
};

noticePollResponseDB.getPollResponsesByNoticeId = async ({
  noticeId,
}: { noticeId: number }) => {
  const query = "SELECT NPR.tenantId, T.name as tenantName, N.id as noticeId, NPO.id as optionId, NPO.description as description, R.roomNum as roomNum FROM NoticePollResponses as NPR join NoticePollOptions as NPO on NPR.optionId = NPO.id join Notices as N on NPO.noticeId = N.id join Tenants as T on NPR.tenantId = T.id join Occupancies as O on O.tenantId = T.id join Rooms as R on R.id = O.roomId WHERE N.id = ? and O.id in (select max(id) from Occupancies group by tenantId)";
  const data = [noticeId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  return false;
};

noticePollResponseDB.getPollResponsesByNoticeIdAndTenantId = async ({
  noticeId,
  tenantId,
}:noticePollResponseTypes & { noticeId: number }) => {
  const query = "SELECT NPR.tenantId, T.name as tenantName, N.id as noticeId, NPO.id as optionId FROM NoticePollResponses as NPR join NoticePollOptions as NPO on NPR.optionId = NPO.id join Notices as N on NPO.noticeId = N.id join Tenants as T on NPR.tenantId = T.id WHERE N.id = ? and NPR.tenantId = ?";
  const data = [noticeId, tenantId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows[0];
  return false;
};

noticePollResponseDB.getTotalResponsesByNoticeId = async ({
  noticeId,
}: { noticeId: number }) => {
  const query = 
    `Select count(*) as total from NoticePollResponses as NPR join NoticePollOptions as NPO on NPR.optionId = NPO.id join Notices as N on NPO.noticeId = N.id where N.id = ?`;
  const data = [
    noticeId,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows[0].total;
  return 0;
};

noticePollResponseDB.getTotalResponsesByNoticeIdPerOption = async ({
  noticeId,
}: { noticeId: number }) => {
  const query = 
    `Select count(*) as total, NPO.id as optionId from NoticePollResponses as NPR join NoticePollOptions as NPO on NPR.optionId = NPO.id join Notices as N on NPO.noticeId = N.id where N.id = ? group by NPO.id`;
  const data = [
    noticeId,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  return 0;
};

noticePollResponseDB.remove = async ({ noticeId }: { noticeId: number }) => {
  const query = "Delete from NoticePollResponses where optionId IN (select id from NoticePollOptions where noticeId = ?)";
  const data = [noticeId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

export default noticePollResponseDB;