import mysql, { ResultSetHeader, RowDataPacket } from "mysql2";
import DB from "../config/database/db";
import scheduledReportTypes from "../schemas/scheduledReport.schema";
import log from "../config/log";

const scheduledReportDB: any = {};

scheduledReportDB.add = async ({
  clientId,
  reportType,
  frequency,
  sendingTime,
  propId,
  email,
  scheduledDate,
  status,
  reportTypeName = '',
  scheduledById,
  scheduledByUserType,
}: scheduledReportTypes) => {
  const query =
    "INSERT INTO ScheduledReports (clientId, reportType, frequency, sendingTime, propId, email, scheduledDate, status, reportTypeName, scheduledById, scheduledByUserType) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  const data = [
    clientId,
    reportType,
    frequency,
    sendingTime,
    propId,
    email,
    scheduledDate,
    status,
    reportTypeName,
    scheduledById,
    scheduledByUserType,
  ];
  log.info(mysql.format(query, data))
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

scheduledReportDB.edit = async ({
  id,
  reportType,
  frequency,
  sendingTime,
  propId,
  email,
  scheduledDate,
  reportTypeName = '',
}: scheduledReportTypes) => {
  const query =
    "UPDATE ScheduledReports SET reportType = ?, reportTypeName= ? , frequency = ?, sendingTime = ?, propId = ?, email = ?, scheduledDate = ? WHERE id = ?";
  const data = [
    reportType,
    reportTypeName,
    frequency,
    sendingTime,
    propId,
    email,
    scheduledDate,
    id,
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return rows.affectedRows > 0;
};


scheduledReportDB.editStatus = async ({
  id,
  status
}: scheduledReportTypes) => {
  const query =
    "UPDATE ScheduledReports SET status = ? WHERE id = ? limit 1";
  const data = [
    status,
    id,
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return rows.affectedRows > 0;
};


scheduledReportDB.editScheduledDate = async ({
  id,
  scheduledDate
}: scheduledReportTypes) => {
  const query =
    "UPDATE ScheduledReports SET scheduledDate = ? WHERE id = ? limit 1";
  const data = [
    scheduledDate,
    id,
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return rows.affectedRows > 0;
};

scheduledReportDB.delete = async ({ id }: scheduledReportTypes) => {
  const query = "DELETE FROM ScheduledReports WHERE id = ?";
  const data = [id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return rows.affectedRows > 0;
};

scheduledReportDB.getById = async ({ id }: scheduledReportTypes) => {
  const query = "SELECT * FROM ScheduledReports WHERE id = ?";
  const data = [id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows[0];
  return false;
};

scheduledReportDB.getByIdAndClientId = async ({ id, clientId }: scheduledReportTypes) => {
  const query = "SELECT * FROM ScheduledReports WHERE id = ? and clientId = ?";
  const data = [id, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows[0];
  return false;
};

scheduledReportDB.getByClientId = async ({
  clientId,
}: scheduledReportTypes) => {
  //const query = "SELECT * FROM ScheduledReports WHERE clientId = ? and status in (1,2)";
  const query = "SELECT SR.*, S.name AS scheduledByName FROM ScheduledReports SR LEFT JOIN Staffs S ON S.id = SR.scheduledById AND SR.scheduledByUserType !=1 WHERE SR.clientId = ?"
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  return false;
};

scheduledReportDB.getByClientIdAndStatus = async ({
  clientId,
  status,
}: scheduledReportTypes) => {
  const query =
    "SELECT * FROM ScheduledReports WHERE clientId = ? AND status = ?";
  const data = [clientId, status];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  return false;
};

scheduledReportDB.getByClientIdAndScheduledBy = async ({
  clientId,
  scheduledById,
  scheduledByUserType
}: scheduledReportTypes) => {
  const query = "SELECT * FROM ScheduledReports WHERE clientId = ? and scheduledById = ? and scheduledByUserType = ? and status in (1,2)";
  const data = [clientId, scheduledById, scheduledByUserType];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  return false;
};

export default scheduledReportDB;