import { ResultSetHeader, RowDataPacket } from "mysql2";
import DB from "../config/database/db";
import noticeTypes from "../schemas/notice.schema";
import propertyNoticeTypes from "../schemas/propertyNotice.schema";
import noticeViewsTypes from "../schemas/noticeViews.schema";

const noticeDB: any = {};

noticeDB.getById = async ({ id }: noticeTypes) => {
  const query = "Select * from Notices where id = ? order by id desc";
  const data = [id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

noticeDB.getByClientId = async ({ clientId }: noticeTypes) => {
  const query = "Select * from Notices where clientId = ? order by id desc";
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

noticeDB.getByClientIdX = async ({ 
  clientId,
  type,
  pageNum,
  limit, 
}: noticeTypes & {pageNum: number; limit: number}) => {
  let query = "Select * from Notices where clientId = ? and type = ? order by id desc ";
  const offset = (pageNum - 1) * limit;
  const data: any = [clientId, type];

  if (limit > 0) {
    query += ` limit ?, ?`
  }
  data.push(`${offset}`, `${limit}`)

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

noticeDB.getByIdFromPropertyNotice = async ({
  noticeId,
}: propertyNoticeTypes) => {
  const query =
    "Select P.id, P.name from PropertyNotice as PN inner join Properties as P on P.id = PN.propId where PN.noticeId = ? order by PN.id desc";
  const data = [noticeId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

noticeDB.getByPropId = async ({ propId }: propertyNoticeTypes) => {
  const query =
    "Select PN.propId, N.clientId, N.type, N.id, N.title, N.img, N.description, N.createdAt from PropertyNotice as PN inner join Notices as N on N.id = PN.noticeId where PN.propId = ? order by PN.id desc";
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

noticeDB.getByPropIdAndLimit = async ({
  propId,
  limit,
}: propertyNoticeTypes & { limit: number }) => {
  const query =
    "Select PN.propId, N.img, N.type, N.clientId, N.id, N.title, N.description, N.createdAt from PropertyNotice as PN inner join Notices as N on N.id = PN.noticeId where PN.propId = ? order by PN.id desc limit ?";
  const data = [propId, limit.toString()];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

noticeDB.create = async ({
  clientId,
  type,
  title,
  description,
  publishedBy,
  img,
}: noticeTypes) => {
  const query =
    "Insert into Notices (clientId, type, title, description, img, publishedBy) values (?,?,?,?,?,?)";
  const data = [clientId, type, title, description, img, publishedBy];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

noticeDB.addToPropertyNotice = async ({
  clientId,
  propId,
  noticeId,
}: propertyNoticeTypes) => {
  const query =
    "Insert into PropertyNotice (clientId, propId, noticeId) values (?,?,?)";
  const data = [clientId, propId, noticeId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

noticeDB.remove = async ({ noticeId }: propertyNoticeTypes) => {
  const query = "Delete from Notices where id = ?";
  const data = [noticeId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

noticeDB.removeFromPropertyList = async ({ noticeId }: propertyNoticeTypes) => {
  const query = "Delete from PropertyNotice where noticeId = ?";
  const data = [noticeId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

noticeDB.addView = async ({
  clientId,
  tenantId,
  noticeId,
}: noticeViewsTypes
) => {
  const query =
    "Insert into NoticeViews (clientId, tenantId, noticeId) values (?,?,?)";
  const data = [clientId, tenantId, noticeId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

noticeDB.getViewsByNoticeId = async ({ noticeId }: noticeViewsTypes) => {
  const query =
    "Select T.name as tenantName, (select count(*) from NoticeViews where noticeId = ?) as totalViews from NoticeViews as NV inner join Tenants as T on T.id = NV.tenantId where NV.noticeId = ? order by NV.id desc";
  const data = [noticeId, noticeId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

noticeDB.isNoticeViewed = async ({
  clientId,
  tenantId,
  noticeId,
}: noticeViewsTypes) => {
  const query =
    "Select * from NoticeViews where clientId = ? and tenantId = ? and noticeId = ?";
  const data = [clientId, tenantId, noticeId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return true;
  else return false;
};

export default noticeDB;