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

const tenantDB: any = {};

tenantDB.create = async ({ mobile }: tenantTypes) => {
  const query = "Insert into Tenants (mobile) values (?)";
  const data = [mobile];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

tenantDB.createByOwner = async ({
  mobile,
  name,
  gender,
  occupation,
  alternateMobile,
}: tenantTypes) => {
  mobile = mobile.toString().substring(mobile.length - 10);
  let query = "";
  let data;
  if (alternateMobile != undefined && alternateMobile.length > 9) {
    alternateMobile = alternateMobile
      .toString()
      .substring(alternateMobile.length - 10);
    query =
      "Insert into Tenants (mobile, name, gender, occupation, alternateMobile) values (?, ?, ?, ?,?)";
    data = [mobile, name, gender, occupation, alternateMobile];
  } else {
    query =
      "Insert into Tenants (mobile, name, gender, occupation) values (?, ?, ?, ?)";
    data = [mobile, name, gender, occupation];
  }
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

tenantDB.createByBookingForm = async ({
  mobile,
  name,
  email,
  gender,
  occupation,
  dob,
  aadharNumber,
  address,
}: tenantTypes) => {
  mobile = mobile.toString().substring(mobile.length - 10);
  const query =
    "Insert into Tenants (mobile, name, gender, occupation, email, dob, aadharNumber, address) values (?, ?, ?, ?, ?, ?, ?, ?)";
  const data = [
    mobile,
    name,
    gender,
    occupation,
    email,
    dob,
    aadharNumber,
    address,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

tenantDB.updateByBookingForm = async ({
  mobile,
  name,
  email,
  gender,
  occupation,
  dob,
  aadharNumber,
  address,
}: tenantTypes) => {
  mobile = mobile.toString().substring(mobile.length - 10);
  const query =
    "Update Tenants set name = ?, gender = ?, occupation = ?, email = ?, dob = ?, aadharNumber = ?, address = ? where mobile = ?";
  const data = [
    name,
    gender,
    occupation,
    email,
    dob,
    aadharNumber,
    address,
    mobile,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

tenantDB.updateBasicDetails = async ({
  gender,
  name,
  occupation,
  id,
}: tenantTypes) => {
  const query =
    "Update Tenants set gender = ?, name = ?, occupation = ? where id = ?";
  const data = [gender, name, occupation, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

tenantDB.getByMobile = async ({ mobile }: tenantTypes) => {
  mobile = mobile.toString().substring(mobile.length - 10);
  const query = "Select * from Tenants where mobile = ?";
  const data = [mobile];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

tenantDB.getById = async ({ id }: tenantTypes) => {
  const query = "Select * from Tenants where id = ?";
  const data = [id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

tenantDB.getByGId = async ({ gId }: tenantTypes) => {
  const query = "Select * from Tenants where gId = ?";
  const data = [gId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

tenantDB.getByTId = async ({ tId }: tenantTypes) => {
  const query = "Select * from Tenants where tId = ?";
  const data = [tId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

tenantDB.updateStatus = async ({ status, id }: tenantTypes) => {
  const query = "Update Tenants set status = ? where id = ?";
  const data = [status, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

tenantDB.updateLastReminded = async ({ lastRemindedOn, id }: tenantTypes) => {
  const query = "Update Tenants set lastRemindedOn = ? where id = ?";
  const data = [lastRemindedOn, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

tenantDB.updateKycStatus = async ({ kycStatus, id }: tenantTypes) => {
  const query = "Update Tenants set kycStatus = ? where id = ?";
  const data = [kycStatus, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

tenantDB.updateNameGender = async ({ name, id }: tenantTypes) => {
  const query = "Update Tenants set name = ? where id = ?";
  const data = [name, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

tenantDB.updateRegId = async ({ regId, id }: tenantTypes) => {
  const query = "Update Tenants set regId = ? where id = ?";
  const data = [regId, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

tenantDB.updateGId = async ({ gId, id }: tenantTypes) => {
  const query = "Update Tenants set gId = ? where id = ?";
  const data = [gId, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

tenantDB.updateProfile = async ({
  alternateMobile,
  occupation,
  email,
  id,
}: tenantTypes) => {
  const query =
    "Update Tenants set  alternateMobile = ?, occupation = ?, email = ? where id = ?";
  const data = [alternateMobile, occupation, email, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

tenantDB.addPersonalInfo = async ({
  name,
  fatherName,
  fatherMobile,
  dob,
  gender,
  occupation,
  email,
  alternateMobile,
  address,
  id,
  kycStatus,
  institutionName,
}: tenantTypes) => {
  const query =
    "Update Tenants set name = ?, fatherName = ?, fatherMobile = ?, dob = ?, gender = ?, occupation = ?, email = ?, alternateMobile = ?, address = ?, kycStatus = ?, institutionName = ? where id = ?";
  const data = [
    name,
    fatherName,
    fatherMobile,
    dob,
    gender,
    occupation,
    email,
    alternateMobile,
    address,
    kycStatus,
    institutionName,
    id,
  ];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

tenantDB.updatePersonalInfo = async ({
  name,
  fatherName,
  dob,
  gender,
  address,
  id,
  kycStatus,
}: tenantTypes) => {
  const query =
    " Update Tenants set name = ?, fatherName = ?, dob = ?, gender = ?, address = ?, kycStatus = ? where id = ?";
  const data = [
    name,
    fatherName,
    dob,
    gender,
    address,
    kycStatus,
    id,
  ];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

tenantDB.updatePersonalInfoX = async ({
  name,
  fatherName,
  dob,
  gender,
  address,
  id,
  kycStatus,
  aadharNumber,
}: tenantTypes) => {
  const query =
    " Update Tenants set name = ?, fatherName = ?, dob = ?, gender = ?, address = ?, kycStatus = ?, aadharNumber = ? where id = ?";
  const data = [
    name,
    fatherName,
    dob,
    gender,
    address,
    kycStatus,
    aadharNumber,
    id,
  ];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

tenantDB.updateTenantOccupationDetails = async ({
  id,
  institutionName,
  institutionEmail,
  title,
  occupation,
}: tenantTypes) => {
  const query =
    "Update Tenants set institutionName = ?, institutionEmail = ?, title = ?, occupation=? where id = ?";
  const data = [institutionName, institutionEmail, title, occupation, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

tenantDB.updateTenantOccupationEndyearAndDate = async ({
  id,
  courseEndMonth,
  courseEndYear,
}: tenantTypes) => {
  const query =
    "Update Tenants set courseEndMonth = ?, courseEndYear = ? where id = ?";
  const data = [courseEndMonth, courseEndYear, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

tenantDB.updateTenantGuardianDetails = async ({
  id,
  fatherMobile,
  motherName,
  motherMobile,
}: tenantTypes) => {
  const query =
    "Update Tenants set fatherMobile = ?, motherName = ?, motherMobile = ? where id = ?";
  const data = [fatherMobile, motherName, motherMobile, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

tenantDB.updateTenantGuardianDetailsByClient = async ({
  id,
  fatherName,
  fatherMobile,
  motherName,
  motherMobile,
}: tenantTypes) => {
  const query =
    "Update Tenants set fatherName = ?, fatherMobile = ?, motherName = ?, motherMobile = ? where id = ?";
  const data = [fatherName, fatherMobile, motherName, motherMobile, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

tenantDB.updateTenantFatherName = async ({
  id,
  fatherName,
}: tenantTypes) => {
  const query =
    "Update Tenants set fatherName = ? where id = ?";
  const data = [fatherName, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

tenantDB.updateTenantFatherMobile = async ({
  id,
  fatherMobile,
}: tenantTypes) => {
  const query =
    "Update Tenants set fatherMobile = ? where id = ?";
  const data = [fatherMobile, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

tenantDB.updateTenantMotherName = async ({
  id,
  motherName,
}: tenantTypes) => {
  const query =
    "Update Tenants set motherName = ? where id = ?";
  const data = [motherName, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

tenantDB.updateTenantMotherMobile = async ({
  id,
  motherMobile,
}: tenantTypes) => {
  const query =
    "Update Tenants set motherMobile = ? where id = ?";
  const data = [motherMobile, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

tenantDB.updateTenantDevice = async ({
  mobile,
  device,
}: tenantTypes) => {
  const query =
    "Update Tenants set device = ? where mobile = ?";
  const data = [device, mobile];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

tenantDB.UpdatePersonalDetailsForWeb = async ({
  id,
  name,
  mobile,
  email,
  gender,
  dob,
  address,
  alternateMobile,
  bloodGroup,
}: tenantTypes) => {
  const query =
    "Update Tenants set name = ?, email = ?, gender = ?, dob = ?, address = ?, alternateMobile = ?, mobile = ?, bloodGroup = ? where id = ?";
  const data = [
    name,
    email,
    gender,
    dob,
    address,
    alternateMobile,
    mobile,
    bloodGroup,
    id,
  ];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

// tenantDB.updateEqaroStatus = async ({
//   mobile,
//   eqaroStatus,
// }: tenantTypes) => {
//   const query =
//     "Update Tenants set eqaroStatus = ? where mobile = ?";
//   const data = [eqaroStatus, mobile];
//   await DB.execute<ResultSetHeader[]>(query, data);
//   return true;
// };

// tenantDB.updateEqaroUserId = async ({
//   mobile,
//   eqaroUserId,
// }: tenantTypes) => {
//   const query =
//     "Update Tenants set eqaroUserId = ? where mobile = ?";
//   const data = [eqaroUserId, mobile];
//   await DB.execute<ResultSetHeader[]>(query, data);
//   return true;
// };

tenantDB.remindedToday = async ({ id }: tenantTypes) => {
  const query =
    "select * from Tenants where id = ? and Date(lastRemindedOn) = curdate();";
  const data = [id];
  const [rows] = await DB.execute<ResultSetHeader[]>(query, data);
  if (rows.length > 0) return true;
  else return false;
};

tenantDB.getAppInstalledCountByPropId = async ({ propId }: {propId: number}) => {
  const query =
    "select count(T.id) as count from Tenants as T join Occupancies as O on T.id = O.tenantId where O.propId = ? and T.device != 0;";
  const data = [propId];
  const [rows] = await DB.execute<ResultSetHeader[]>(query, data);
  if (rows.length > 0) return rows[0];
  else return false
};

tenantDB.updateAadhaarNumber = async ({
  id,
  aadharNumber,
}: tenantTypes) => {
  const query = `Update Tenants set aadharNumber = ? where id = ?`;
  const data = [aadharNumber, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
}

tenantDB.updateTenantDigiLockerIds = async ({
  verificationId,
  referenceId,
  id,
}: tenantTypes) => {
  const query =
    "Update Tenants set verificationId = ?, referenceId = ? where id = ?";
  const data = [verificationId, referenceId, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

tenantDB.getByVerificationId = async ({ verificationId }: tenantTypes) => {
  const query = "Select * from Tenants where verificationId = ?";
  const data = [verificationId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

tenantDB.updateTID = async ({
  id,
  tId,
}: tenantTypes) => {
  const query =
    `Update Tenants set tId = ? where id = ?`;
  const data = [
    tId,
    id,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

tenantDB.updateMobile = async ({
  id,
  mobile,
}: tenantTypes) => {
  const query =
    `Update Tenants set mobile = ? where id = ?`;
  const data = [
    mobile,
    id,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

tenantDB.getInfoByIds = async ({
  ids,
}: {ids: number[]}) => {
  if (!ids || ids.length === 0) return false;
  const tenantIds = ids.map(() => "?").join(",");
  const query = `Select id, name, mobile, gender from Tenants where id in (${tenantIds})`;
  const data = [...ids];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

tenantDB.updateLastLogin = async ({
  id,
}: tenantTypes) => {
  const query =
    "Update Tenants set lastLogin = now() where id = ?";
  const data = [
    id
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};


export default tenantDB;