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

const staffAttendanceDB: any = {};

staffAttendanceDB.add = async ({
  clientId,
  staffId,
  propId,
  attendance,
  checkIn,
  checkInLongitude,
  checkInLatitude,
}: staffAttendanceTypes) => {
  const query =
    "Insert into StaffAttendance (clientId, staffId, propId, attendance, checkIn, checkInLongitude, checkInLatitude) values (?, ?, ?, ?, ?, ?, ?)";
  const data = [clientId, staffId, propId, attendance, checkIn, checkInLongitude, checkInLatitude];
  const [row] = await DB.execute<ResultSetHeader>(query, data);
  return row.insertId;
};

staffAttendanceDB.getByStaffIdForMonth = async ({
  staffId,
  date,
}: staffAttendanceTypes & { date: string }) => {
  const query =
    "Select SA.id, SA.clientId, SA.staffId, SA.propId, SA.attendance, SA.checkIn, SA.checkOut, DATE_FORMAT(SA.checkIn, '%Y-%m-%d') as attendanceDate, Time(SA.createdAt) as attendanceTime , S.name, (CASE WHEN SA.propId = 0 THEN 0 ELSE P.id END) as propId, (CASE WHEN SA.propId = 0 THEN 'Office' ELSE P.name END) as propName, P.longitude as longitude, P.latitude as latitude from StaffAttendance as SA join Staffs as S on SA.staffId = S.id left join Properties as P on SA.propId = P.id where SA.staffId = ? and MONTH(SA.checkIn) = MONTH(?) and YEAR(SA.checkIn) = YEAR(?) order by SA.createdAt desc";
  const data = [staffId, date, date];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  else return [];
};

staffAttendanceDB.getByClientIdAndDate = async ({
  clientId,
  date,
}: staffAttendanceTypes & { date: string }) => {
  const query =
    "Select SA.id, SA.clientId, SA.staffId, SA.propId, SA.attendance, SA.checkIn, SA.checkOut, DATE(SA.checkIn) as attendanceDate, Time(SA.createdAt) as attendanceTime, (CASE WHEN SA.propId = 0 THEN 0 ELSE P.id END) as propId, (CASE WHEN SA.propId = 0 THEN 'Office' ELSE P.name END) as propName, P.longitude as longitude, P.latitude as latitude from StaffAttendance as SA left join Properties as P on SA.propId = P.id where SA.clientId = ? and Date(SA.checkIn) = ? order by SA.createdAt desc";
  const data = [clientId, date];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  else return [];
};

staffAttendanceDB.isCheckOutPending = async ({
  clientId,
  staffId,
  date,
}: staffAttendanceTypes & { date: string }) => {
  const query =
    "Select * from StaffAttendance where clientId = ? and staffId = ? and checkIn is not null and checkOut is null and Date(checkIn) = Date(?)";
  const data = [clientId, staffId, date];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return true;
  else return false;
};

staffAttendanceDB.getByClientIdAndStaffIdAndDate = async ({
  clientId,
  staffId,
  date,
}: staffAttendanceTypes & { date: string }) => {
  const query =
    "Select * from StaffAttendance where clientId = ? and staffId = ? and Date(checkIn) = Date(?)";
  const data = [clientId, staffId, date];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  else return false;
};

staffAttendanceDB.checkOut = async ({
  propId,
  staffId,
  clientId,
  checkOut,
  checkOutLongitude,
  checkOutLatitude,
}: staffAttendanceTypes) => {
  const query = 
    `Update StaffAttendance set checkOut = ?, checkOutLongitude = ?, checkOutLatitude = ? where clientId = ? and staffId = ? and propId = ? and checkOut is null and checkIn is not null order by id desc limit 1`;
  const data = [
    checkOut,
    checkOutLongitude,
    checkOutLatitude,
    clientId,
    staffId,
    propId,
  ];

  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};


staffAttendanceDB.checkOutManually = async ({
  staffId,
  clientId,
  checkOut,
  date,
  checkOutLongitude,
  checkOutLatitude,
}: staffAttendanceTypes & {date: string;}) => {
  const query = 
    `Update StaffAttendance set checkOut = ?, checkOutLongitude = ?, checkOutLatitude = ? where clientId = ? and staffId = ? and checkOut is null and checkIn is not null  and date(checkIn) = date(?) order by id desc limit 1`;
  const data = [
    checkOut,
    checkOutLongitude,
    checkOutLatitude,
    clientId,
    staffId,
    date,
  ];

  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

export default staffAttendanceDB;