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

const landlordDB: any = {};

landlordDB.getById = async ({ id }: landlordTypes) => {
  const query = `Select * from Landlords where id = ?`;
  const data = [id];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  else return false;
};

landlordDB.create = async ({ name, mobile, email=null }: landlordTypes) => {
  const query = `Insert into Landlords (name, mobile, email) values (?, ?, ?)`;
  const data = [name, mobile, email];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

landlordDB.edit = async ({ id, name, mobile }: landlordTypes) => {
  const query = `Update Landlords set name = ?, mobile = ? where id = ?`;
  const data = [name, mobile, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

landlordDB.editX = async ({ id, name, mobile, email }: landlordTypes) => {
  const query = `Update Landlords set name = ?, mobile = ?, email = ? where id = ?`;
  const data = [name, mobile, email, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

landlordDB.update = async ({ id, name, mobile }: landlordTypes) => {
  const query = `Update Landlords set name = ?, mobile = ? where id = ?`;
  const data = [name, mobile, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

landlordDB.getByMobile = async ({ mobile }: landlordTypes) => {
  const query = `Select * from Landlords where mobile = ?`;
  const data = [mobile];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  else return false;
};

landlordDB.getByMobileAndClientId = async ({ mobile, clientId }: {
  mobile: number;
  clientId: number;
}) => {
  const query = "Select * from Vendors where mobile = ? and clientId = ? order by id desc";
  const data = [mobile, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

landlordDB.getByClientId = async ({ clientId }:  {
  clientId: number;
}) => {
  const query =
    "Select DISTINCT L.id, L.name, L.mobile, L.createdAt, L.updatedAt from Landlords as L join PropertyLease as PL on L.id = PL.landlordId where PL.clientId = ? order by L.id desc";
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

landlordDB.getByClientIdX = async ({ clientId }:  {
  clientId: number;
}) => {
  const query =
    "Select DISTINCT L.id, L.name, L.email, L.mobile, ? as userType, L.createdAt, L.updatedAt from Landlords as L join ClientLandlords as CL on L.id = CL.landlordId where CL.clientId = ? order by L.id desc";
  const data = [CONSTANTS.USER_TYPE.LANDLORD, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

landlordDB.getByClientIdApp = async ({ clientId, pageNum, limit }:  {
  clientId: number;
  pageNum: number;
  limit: number;
}) => {
  const query =
    "Select DISTINCT L.id, L.name, L.email, L.mobile, ? as userType, L.createdAt, L.updatedAt from Landlords as L join ClientLandlords as CL on L.id = CL.landlordId where CL.clientId = ? order by L.id desc limit ?, ?";
  
  const offset: number = (pageNum - 1) * limit;
  const data = [
    CONSTANTS.USER_TYPE.LANDLORD, 
    clientId,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

landlordDB.getByClientIdForStaff = async ({ clientId, propertiesIds }:  {
  clientId: number;
  propertiesIds: string;
}) => {
  const query =
    `Select DISTINCT L.id, L.name, L.mobile, L.createdAt, L.updatedAt from Landlords as L join PropertyLease as PL on L.id = PL.landlordId where PL.clientId = ? and PL.propId in (${propertiesIds}) order by L.id desc`;
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

landlordDB.getByClientIdForStaffX = async ({ clientId, propertiesIds }:  {
  clientId: number;
  propertiesIds: string;
}) => {
  const query =
    `Select DISTINCT L.id, L.name,  ? as userType, L.email, L.mobile, L.createdAt, L.updatedAt from Landlords as L join PropertyLease as PL on L.id = PL.landlordId join ClientLandlords as CL on L.id = CL.landlordId where CL.clientId = ? and PL.propId in (${propertiesIds}) order by L.id desc`;
  const data = [CONSTANTS.USER_TYPE.LANDLORD, clientId,];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

landlordDB.getByClientIdForStaffApp = async ({ clientId, propertiesIds, pageNum, limit }:  {
  clientId: number;
  propertiesIds: string;
  pageNum: number;
  limit: number;
}) => {
  const query =
    `Select DISTINCT L.id, L.name, L.email,  ? as userType, L.mobile, L.createdAt, L.updatedAt from Landlords as L join PropertyLease as PL on L.id = PL.landlordId join ClientLandlords as CL on L.id = CL.landlordId where CL.clientId = ? and PL.propId in (${propertiesIds}) order by L.id desc limit ?, ?`;
  const offset: number = (pageNum - 1) * limit;
  const data = [
    CONSTANTS.USER_TYPE.LANDLORD,
    clientId,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

landlordDB.getByClientIdBySearch = async ({ clientId, searchVal, searchType }:  {
  clientId: number;
  searchVal: string;
  searchType: number;
}) => {
  let data : any = [clientId];
  let filterCondition = "";
  if(2 === Number(searchType)){
    filterCondition = 'AND L.name LIKE ?';
    data.push(`%${searchVal}%`);
  } else if(1 === Number(searchType)) {
    filterCondition = 'AND L.mobile LIKE ?';
    data.push(`%${searchVal}%`);
  } else if (3 === Number(searchType)) {
    filterCondition = ' AND P.name LIKE ?';
    data.push(`%${searchVal}%`);
  }
  let query =
    `Select DISTINCT L.id, L.name, L.mobile, L.createdAt, L.updatedAt from Landlords as L join PropertyLease as PL on L.id = PL.landlordId join Properties as P on P.id = PL.propId where PL.clientId = ? ${filterCondition} order by L.id desc`;

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

landlordDB.getByClientIdBySearchX = async ({ clientId, searchVal, searchType }:  {
  clientId: number;
  searchVal: string;
  searchType: number;
}) => {
  let data : any = [clientId];
  let filterCondition = "";
  if(2 === Number(searchType)){
    filterCondition = 'AND L.name LIKE ?';
    data.push(`%${searchVal}%`);
  } else if(1 === Number(searchType)) {
    filterCondition = 'AND L.mobile LIKE ?';
    data.push(`%${searchVal}%`);
  } else if (3 === Number(searchType)) {
    filterCondition = ' AND P.name LIKE ?';
    data.push(`%${searchVal}%`);
  }
  let query =
    `Select DISTINCT L.id, L.name, L.mobile, L.createdAt, L.updatedAt from Landlords as L join ClientLandlords as CL on L.id = CL.landlordId  left join PropertyLease as PL on L.id = PL.landlordId left join Properties as P on P.id = PL.propId where CL.clientId = ? ${filterCondition} order by L.id desc`;

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

landlordDB.getByClientIdBySearchApp = async ({ clientId, searchVal, pageNum, limit }:  {
  clientId: number;
  searchVal: string;
  pageNum: any;
  limit: any;
}) => {
  let query =
  `Select DISTINCT L.id, L.name, L.mobile, L.createdAt, L.updatedAt from Landlords as L join ClientLandlords as CL on L.id = CL.landlordId  left join PropertyLease as PL on L.id = PL.landlordId left join Properties as P on P.id = PL.propId where CL.clientId = ? and (L.name LIKE ? OR L.mobile LIKE ? OR P.name LIKE ?) order by L.id desc limit ?, ?`;
  const offset: number = (pageNum - 1) * limit;
  let data : any = [
    clientId,
    `%${searchVal}%`,
    `%${searchVal}%`,
    `%${searchVal}%`,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

landlordDB.getByClientIdBySearchForStaff = async ({ clientId, propertiesIds, searchVal, searchType }:  {
  clientId: number;
  propertiesIds: string;
  searchVal: string;
  searchType: number;
}) => {
  let data : any = [clientId];
  let filterCondition = "";
  if(2 === Number(searchType)){
    filterCondition = 'AND L.name LIKE ?';
    data.push(`%${searchVal}%`);
  } else if(1 === Number(searchType)) {
    filterCondition = 'AND L.mobile LIKE ?';
    data.push(`%${searchVal}%`);
  } else if(3 === Number(searchType)) {
    filterCondition = 'AND P.name like ?';
    data.push(`%${searchVal}%`);
  }
  let query =
    `Select DISTINCT L.id, L.name, L.mobile, L.createdAt, L.updatedAt from Landlords as L join PropertyLease as PL on L.id = PL.landlordId join Properties as P on P.id = PL.propId where PL.clientId = ? and PL.propId in (${propertiesIds}) ${filterCondition} order by L.id desc`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

landlordDB.getByClientIdBySearchForStaffX = async ({ clientId, propertiesIds, searchVal, searchType }:  {
  clientId: number;
  propertiesIds: string;
  searchVal: string;
  searchType: number;
}) => {
  let data : any = [clientId];
  let filterCondition = "";
  if(2 === Number(searchType)){
    filterCondition = 'AND L.name LIKE ?';
    data.push(`%${searchVal}%`);
  } else if(1 === Number(searchType)) {
    filterCondition = 'AND L.mobile LIKE ?';
    data.push(`%${searchVal}%`);
  } else if(3 === Number(searchType)) {
    filterCondition = 'AND P.name like ?';
    data.push(`%${searchVal}%`);
  }
  let query =
    `Select DISTINCT L.id, L.name, L.mobile, L.createdAt, L.updatedAt from Landlords as L join ClientLandlords as CL on L.id = CL.landlordId left join PropertyLease as PL on L.id = PL.landlordId join Properties as P on P.id = PL.propId where CL.clientId = ? and PL.propId in (${propertiesIds}) ${filterCondition} order by L.id desc`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

landlordDB.getByClientIdBySearchForStaffApp = async ({ clientId, propertiesIds, searchVal, pageNum, limit }:  {
  clientId: number;
  propertiesIds: string;
  searchVal: string;
  pageNum: any;
  limit: any;
}) => {
  let query =
  `Select DISTINCT L.id, L.name, L.mobile, L.createdAt, L.updatedAt from Landlords as L join ClientLandlords as CL on L.id = CL.landlordId left join PropertyLease as PL on L.id = PL.landlordId join Properties as P on P.id = PL.propId where CL.clientId = ? and PL.propId in (${propertiesIds}) and (L.name LIKE ? OR L.mobile LIKE ? OR P.name LIKE ?) order by L.id desc limit ?, ?`;
  const offset: number = (pageNum - 1) * limit;
  let data : any = [
    clientId,
    `%${searchVal}%`,
    `%${searchVal}%`,
    `%${searchVal}%`,
    `${offset}`,
    `${limit}`,
  ];
  
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

landlordDB.addPaymentDetails = async ({
  clientId,
  propId,
  landlordId,
  startDate,
  endDate,
  notice,
  rent,
  security,
  rentDate
} : any) => {
  const query = `Insert into LandlordAccounts (clientId, propId, landlordId, mId, key, gateway, bankName, accountNum, ifsc, holderName) values (?, ?, ?, ?, ?, ?, ?, ?, ?)`;
  const data = [
    clientId,
    propId,
    landlordId,
    startDate,
    endDate,
    notice,
    rent,
    security,
    rentDate,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};


landlordDB.editDetails = async ({
  id,
  name,
  mobile,
  alternateMobile,
  email,
  gender,
  bloodGroup,
  dob,
  address,
  nationality
}: landlordTypes) => {
  const query = `UPDATE Landlords SET name = ?, mobile = ?, alternateMobile = ?, email = ?, gender = ?, bloodGroup = ?, dob = ?, address = ?, nationality = ? WHERE id = ?`;
  const data = [
    name,
    mobile,
    alternateMobile,
    email,
    gender,
    bloodGroup,
    dob,
    address,
    nationality,
    id
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

landlordDB.getByClientIdXAndMobile = async ({ clientId, mobile }: any) => {
  const query =
    "Select DISTINCT L.id, L.name, L.email, L.mobile, ? as userType, L.createdAt, L.updatedAt from Landlords as L join ClientLandlords as CL on L.id = CL.landlordId where CL.clientId = ? and L.mobile = ? order by L.id desc";
  const data = [CONSTANTS.USER_TYPE.LANDLORD, clientId, mobile];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};


export default landlordDB;