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

const clientLandlordDB: any = {};

clientLandlordDB.create = async ({ clientId, landlordId }: clientLandlordTypes) => {
  const query = `Insert into ClientLandlords (clientId, landlordId) values (?, ?)`;
  const data = [clientId, landlordId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

clientLandlordDB.getByClientIdAndLandlordId = async ({ clientId, landlordId }: clientLandlordTypes) => {
  const query = `Select * from ClientLandlords where clientId = ? and landlordId = ?`;
  const data = [clientId, landlordId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  else return false;
};

clientLandlordDB.getByLandlordId = async ({ landlordId }: clientLandlordTypes) => {
  const query = `Select * from ClientLandlords where landlordId = ?`;
  const data = [landlordId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

clientLandlordDB.getByClientId = async ({ clientId, landlordId }: clientLandlordTypes) => {
  const query = `Select * from ClientLandlords where clientId = ?`;
  const data = [clientId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  else return false;
};

clientLandlordDB.updateKycStatus = async ({ clientId, landlordId, kycStatus }: clientLandlordTypes) => {
  const query = `Update ClientLandlords set kycStatus = ? where clientId = ? and landlordId = ?`;
  const data = [kycStatus, clientId, landlordId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

clientLandlordDB.getClientsByLandlordId = async ({
  landlordId,
} : clientLandlordTypes) => {
  const query = `Select C.id as clientId, C.name as clientName, C.mobile as clientMobile, CL.createdAt, (Select Count(*) from PropertyLease where clientId = C.id and landlordId = CL.landlordId) as propLeaseCount from Clients as C join ClientLandlords as CL on C.id = CL.clientId where CL.landlordId = ?`;
  const data = [landlordId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

clientLandlordDB.getClientsByLandlordIdAndSearch = async ({
  landlordId,
  searchValue,
  searchType,
} : clientLandlordTypes & {searchValue: string; searchType: string;}) => {
  const data: any = [landlordId];

  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " AND C.mobile LIKE ? ";
    data.push(`${searchValue}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " AND C.name LIKE ? ";
    data.push(`%${searchValue}%`);
  }

  const query = `Select C.id as clientId, C.name as clientName, C.mobile as clientMobile, CL.createdAt, (Select Count(*) from PropertyLease where clientId = C.id and landlordId = CL.landlordId) as propLeaseCount from Clients as C join ClientLandlords as CL on C.id = CL.clientId where CL.landlordId = ? ${filterCondition}`;
  
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

clientLandlordDB.getClientCountByLandlordId = async ({ landlordId } : clientLandlordTypes) => {
  const query = `Select count(DISTINCT clientId) as count from ClientLandlords where landlordId = ?`;
  const data = [landlordId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0].count;
  else return false;
};

export default clientLandlordDB;