import mysql, { ResultSetHeader, RowDataPacket } from "mysql2";
import DB from "../config/database/db";
import bookingsTypes from "../schemas/bookings.schema";
import log from "../config/log";
import CONSTANTS from "../config/constants";


const bookingsDB: any = {};

bookingsDB.create = async ({
  clientId,
  tenantId,
  propId,
  roomId,
  moveInDate,
  status,
  stayType,
  applicationNumber,
}: bookingsTypes) => {
  const query = `INSERT INTO Bookings (clientId, tenantId, propId, roomId, moveInDate, status, stayType, applicationNumber) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`;
  const data = [
    clientId,
    tenantId,
    propId,
    roomId,
    moveInDate,
    status,
    stayType,
    applicationNumber
  ];
  
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

bookingsDB.update = async ({
  id,
  propId,
  roomId,
  moveInDate,
  status,
  stayType,
  applicationNumber,
}: bookingsTypes) => {
  const query = `UPDATE Bookings SET propId = ?, roomId = ?, moveInDate = ?, status = ?, stayType = ?, applicationNumber = ? WHERE id = ?`;
  const data = [
    propId,
    roomId,
    moveInDate,
    status,
    stayType,
    applicationNumber,
    id,
  ];
  
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

bookingsDB.getByClientIdAndTenantId = async ({
  clientId,
  tenantId,
}: bookingsTypes) => {
  const query = `SELECT * FROM Bookings WHERE clientId = ? AND tenantId = ?`;
  const data = [clientId, tenantId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  
  if (rows && rows.length > 0) return rows[0];
  return false;
};

bookingsDB.getByTenantAndClientIdAndStatus = async ({
  clientId,
  tenantId,
  status,
}: bookingsTypes) => {
  const query = `SELECT * FROM Bookings WHERE clientId = ? AND tenantId = ? AND status = ?`;
  const data = [clientId, tenantId, status];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  
  if (rows && rows.length > 0) return rows[0];
  return false;
};

bookingsDB.getByClientIdAndFilters = async ({
  clientId,
  propIds,
  searchVal,
  searchType,
  startDate,
  endDate,
  pageNum,
  limit,
  linkFilter,
}: {
  clientId: number;
  propIds: number[];
  searchVal: string;
  searchType: string;
  startDate: string,
  endDate: string,
  pageNum: number;
  limit: number;
  linkFilter: string | null,
}) => {
  let query = `SELECT B.*, T.name as tenantName, T.mobile as tenantMobile, T.email as tenantEmail, T.aadharNumber as aadharNumber, T.dob as dob, T.gender as gender, T.address as address, TG.fatherName as fatherName, TG.fatherMobile as fatherMobile, TG.fatherEmail as fatherEmail, TG.motherName as motherName, TG.motherMobile as motherMobile, TG.motherEmail as motherEmail, TG.localGuardianName as localGuardianName, TG.localGuardianMobile as localGuardianMobile, TG.localGuardianEmail as localGuardianEmail, TG.localGuardianRelation as localGuardianRelation, CASE WHEN TID.occupation = 1 THEN 'Student' ELSE 'Professional' END as occupation, TID.institutionId as institutionId, TID.institutionName as institutionName, TID.tenure as tenure, TID.designation as designation, TID.domain as domain, TID.intake as intakeDate, P.name AS propertyName, R.roomNum AS roomName FROM Bookings as B join Tenants as T on B.tenantId = T.id LEFT JOIN TenantInstitutionDetails as TID on B.tenantId = TID.tenantId LEFT JOIN TenantGuardians as TG on B.tenantId = TG.tenantId LEFT JOIN Properties as P ON B.propId = P.id LEFT JOIN Rooms as R ON B.roomId = R.id WHERE B.clientId = ? `;
  const data: any = [clientId];

  
  if (Array.isArray(propIds) && propIds.length > 0) {
    query += ` AND B.propId IN (${propIds.map(() => '?').join(',')}) `;
    data.push(...propIds);
  }

  if (String(startDate) && String(startDate).toLowerCase().trim() !== "undefined" && String(endDate) && String(endDate).toLowerCase().trim() !== "undefined") {
    query += ` and DATE(B.createdAt) BETWEEN ? and ?`;
    data.push(startDate, endDate);
  }

  if (linkFilter && linkFilter === "NL") {
    //Not Linked
    query += ` and B.status in (?) `;
    data.push(CONSTANTS.BOOKING_STATUS.FORM_SUBMITTED);
  } else if (linkFilter && linkFilter === "L") {
    //Linked
    query += ` and B.status in (?, ?) `;
    data.push(CONSTANTS.BOOKING_STATUS.CONFIRMED);
    data.push(CONSTANTS.BOOKING_STATUS.MOVED_IN);
  }

  if (searchVal && String(searchVal).trim() !== "" && String(searchVal).toLowerCase().trim() !== "null" && String(searchVal).toLowerCase().trim() !== "undefined" ) {
    if (Number(searchType) === 1) {
      query += ` And T.name LIKE ?`;
      data.push(`%${searchVal}%`);
    } else if (Number(searchType) === 2) {
      query += ` And T.mobile LIKE ?`;
      data.push(`%${searchVal}%`);
    } else if (Number(searchType) === 3) {
      query += ` And B.applicationNumber LIKE ?`;
      data.push(`%${searchVal}%`);
    } else if (!searchType || Number(searchType) === 0 || !Number(searchType) || isNaN(Number(searchType))) {
      query += ` And (T.name LIKE ? or T.mobile LIKE ? or B.applicationNumber LIKE ?)`;
      data.push(`%${searchVal}%`, `%${searchVal}%`, `%${searchVal}%`);
    }
  }

  query += ` order by B.id desc`;
  
  if (pageNum && Number(pageNum) && Number(pageNum) > 0) {
    const offset = (Number(pageNum) - 1) * Number(limit);
    query += ` LIMIT ?, ?`;
    data.push(offset);
    data.push(limit);
  }



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

bookingsDB.getByClientIdAndDateRange = async ({
  clientId,
  startDate,
  endDate,
}: {
  clientId: number;
  startDate: string;
  endDate: string;
}) => {
  const query = `SELECT B.* FROM Bookings as B WHERE B.clientId = ? AND DATE(B.createdAt) BETWEEN ? AND ?`;
  const data: any = [clientId, startDate, endDate];

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

bookingsDB.getByClientIdAndDateRangeForStaffId = async ({
  clientId,
  startDate,
  endDate,
  staffId,
}: {
  clientId: number;
  startDate: string;
  endDate: string;
  staffId: number;
}) => {
  const query = `SELECT B.* FROM Bookings as B left join PropertyStaff as PS on B.propId = PS.propId WHERE B.clientId = ? AND DATE(B.createdAt) BETWEEN ? AND ? AND PS.staffId = ?`;
  const data: any = [clientId, startDate, endDate, staffId];

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

bookingsDB.getSummaryByClientId = async ({
  clientId,
  startDate,
  endDate,
}: {
  clientId: number;
  startDate: string;
  endDate: string;
}) => {
  let query = `SELECT IFNULL(COUNT(*),0) AS totalBookings, COUNT(DISTINCT CASE WHEN B.status = ? then B.tenantId END) as unAssignedTenants, IFNULL(SUM(CASE WHEN DATE(B.moveInDate) = DATE(CURDATE()) THEN 1 ELSE 0 END),0) AS todayMoveIns, IFNULL(SUM(CASE WHEN DATE(B.createdAt) = DATE(CURDATE()) THEN 1 ELSE 0 END),0) AS todayBookings FROM Bookings B WHERE B.clientId = ?`;
  let data: any = [
    CONSTANTS.BOOKING_STATUS.FORM_SUBMITTED,
    clientId,
  ];

  if (String(startDate) && String(startDate).toLowerCase().trim() !== "undefined" && String(endDate) && String(endDate).toLowerCase().trim() !== "undefined") {
    query += ` and DATE(B.createdAt) BETWEEN ? and ?`;
    data.push(startDate, endDate);
  }

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

bookingsDB.assignTenantIdByClientId = async ({
  tenantId,
  clientId,
  propId,
  roomId,
  moveInDate,
  status,
}: bookingsTypes) => {
  const query = `UPDATE Bookings SET status = ?, propId = ?, roomId = ?, moveInDate = ? WHERE clientId = ? AND tenantId = ?`;
  const data = [
    status,
    propId,
    roomId,
    moveInDate,
    clientId,
    tenantId,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

bookingsDB.updateStatusByTenantIdAndClientIdAndDetails = async ({
  tenantId,
  clientId,
  propId,
  roomId,
  moveInDate,
  status,
}: bookingsTypes) => {
  const query = `UPDATE Bookings SET status = ? WHERE propId = ? AND roomId = ? AND moveInDate = ? AND clientId = ? AND tenantId = ? order by id desc limit 1`;
  const data = [
    status,
    propId,
    roomId,
    moveInDate,
    clientId,
    tenantId,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

bookingsDB.updateBookedBy = async ({
  id,
  bookedBy
}: bookingsTypes) => {
  const query = `UPDATE Bookings SET bookedBy = ? WHERE id = ?`;
  const data = [
    bookedBy,
    id,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

bookingsDB.getLastSixMonthsTrend = async ({
  clientId,
}: bookingsTypes) => {
  const query = `SELECT YEAR(B.createdAt) AS year, MONTH(B.createdAt) AS monthNumber, DATE_FORMAT(B.createdAt, '%b') AS month, COUNT(*) AS bookings, SUM(CASE WHEN B.status = 5 THEN 1 ELSE 0 END) AS movedIn, SUM(CASE WHEN B.status = 4 THEN 1 ELSE 0 END) AS cancelled FROM Bookings B WHERE B.clientId = ? AND B.createdAt >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 5 MONTH),'%Y-%m-01') GROUP BY YEAR(B.createdAt), MONTH(B.createdAt), DATE_FORMAT(B.createdAt, '%b') ORDER BY YEAR(B.createdAt), MONTH(B.createdAt)`;

  const [rows] = await DB.query<RowDataPacket[]>(query, [clientId]);
  return rows;
};

bookingsDB.getLastSixMonthsTrendForStaff = async ({
  clientId,
  staffId,
}: {
  clientId: number;
  staffId: number;
}) => {
  const query = `
    SELECT YEAR(B.createdAt) AS year, MONTH(B.createdAt) AS monthNumber, COUNT(*) AS bookings, SUM(CASE WHEN B.status = 5 THEN 1 ELSE 0 END) AS movedIn, SUM(CASE WHEN B.status = 4 THEN 1 ELSE 0 END) AS cancelled FROM Bookings B INNER JOIN PropertyStaff PS ON B.propId = PS.propId AND PS.staffId = ?
    WHERE B.clientId = ?
      AND B.createdAt >= DATE_FORMAT(
            DATE_SUB(CURDATE(), INTERVAL 5 MONTH),
            '%Y-%m-01'
          )
    GROUP BY
      YEAR(B.createdAt),
      MONTH(B.createdAt)
    ORDER BY
      YEAR(B.createdAt),
      MONTH(B.createdAt);
  `;

  const [rows] = await DB.query<RowDataPacket[]>(query, [
    staffId,
    clientId,
  ]);

  return rows;
};

bookingsDB.getCountByClientId = async ({
  clientId,
  propertyId,
  locationId,
}: {
  clientId: number;
  staffId: number;
  propertyId: number | null;
  locationId: number | null;
}) => {
  let query = `SELECT 
    COUNT(*) AS totalBookings,
    SUM(CASE WHEN DATE(B.createdAt) = DATE(CURDATE()) THEN 1 ELSE 0 END) as todayBookings
    FROM Bookings as B LEFT JOIN Properties as P on P.id = B.propId WHERE B.clientId = ? AND B.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) AND B.status != ?
  `;

  let data: any = [clientId, CONSTANTS.BOOKING_STATUS.CANCELLED];

  if (Number(propertyId) && Number(propertyId) !== 0) {
    query += ` and B.propId = ?`;
    data.push(propertyId);
  }
  if (Number(locationId) && Number(locationId) !== 0) {
    query += ` and P.locationId = ?`;
    data.push(locationId);
  }
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  return rows[0];
}

bookingsDB.getCountByClientIdForSalesHead = async ({
  clientId,
  staffId,
  propertyId,
  locationId,
}: {
  clientId: number;
  staffId: number;
  propertyId: number | null;
  locationId: number | null;
}) => {
  let query = `SELECT 
    COUNT(*) AS totalBookings,
    SUM(CASE WHEN DATE(B.createdAt) = DATE(CURDATE()) THEN 1 ELSE 0 END) as todayBookings
    FROM Bookings as B LEFT JOIN Properties as P on P.id = B.propId LEFT JOIN PropertyStaff as PS ON B.propId = PS.propId AND PS.staffId = ? WHERE B.clientId = ? AND B.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) AND B.status != ?
  `;

  let data: any = [staffId, clientId, CONSTANTS.BOOKING_STATUS.CANCELLED];

  if (Number(propertyId) && Number(propertyId) !== 0) {
    query += ` and B.propId = ?`;
    data.push(propertyId);
  }
  if (Number(locationId) && Number(locationId) !== 0) {
    query += ` and P.locationId = ?`;
    data.push(locationId);
  }
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  return rows[0];
}

export default bookingsDB;