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

const clientDB: any = {};

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

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

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

clientDB.getByGId = async ({ gId }: clientTypes) => {
  const query = "Select * from Clients where gId = ? and gId is not null";
  const data = [gId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

clientDB.getByPAN = async ({ panNumber }: clientTypes) => {
  const query = "Select * from Clients where panNumber = ?";
  const data = [panNumber];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

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

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

clientDB.updateAppVersion = async ({ id, currentAppVersion }: clientTypes) => {
  const query = "Update Clients set currentAppVersion = ? where id = ?";
  const data = [currentAppVersion, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

clientDB.updateProfile = async ({
  name,
  city,
  propertyCount,
  id,
}: clientTypes) => {
  const query =
    "Update Clients set name=?, city=?, propertyCount =? where id = ?";
  const data = [name, city, propertyCount, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

clientDB.updateCompleteProfile = async ({
  name,
  businessEmail,
  businessName,
  panNumber,
  gstNo,
  businessAddress,
  id,
}: clientTypes) => {
  const query =
    "Update Clients set name=?, businessEmail = ?, businessName = ?, panNumber = ?, gstNo = ?, businessAddress = ? where id = ?";
  const data = [name, businessEmail, businessName, panNumber, gstNo, businessAddress, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

// clientDB.addRegId = async ({ id, regId, deviceName=null, platform }: clientTypes & {deviceName: string|null; platform: number}) => {
//   const query = "Insert into ClientRegIds(clientId, deviceName, regId, platform) values (?, ?, ?, ?)";
//   const data = [id, deviceName, regId, platform];
//   const [rows] = await DB.query<ResultSetHeader>(query, data);
//   return rows.insertId;
// };

// clientDB.getRegIds = async ({ id }: clientTypes) => {
//   const query = "Select * from ClientRegIds where clientId = ?";
//   const data = [id];
//   const [rows] = await DB.execute<RowDataPacket[]>(query, data);
//   if (rows?.length > 0) return rows;
//   else return false;
// };

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

clientDB.updateLastLogin = async ({ id }: clientTypes) => {
  const query = "Update Clients set lastLogin = now() where id = ?";
  const data = [id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

clientDB.updateClientDevice = async ({ mobile, device }: clientTypes) => {
  const query = "Update Clients set device = ? where mobile = ?";
  const data = [device, mobile];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

clientDB.updateHoldOutBalance = async ({ id, holdOutBalance }: clientTypes) => {
  const query = "Update Clients set holdOutBalance = ? where id = ?";
  const data = [holdOutBalance, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

clientDB.addPartner = async ({
  mobile,
  name,
  businessEmail,
  ownershipPercentage,
  parentId,
  panNumber,
  city,
  businessName,
  gstNo,
}: clientTypes) => {
  const query =
    `Insert into Clients (mobile, name, businessEmail, ownershipPercentage, parentId, panNumber, city, status, businessName, gstNo) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
  
  const data = [
    mobile,
    name,
    businessEmail,
    ownershipPercentage,
    parentId,
    panNumber,
    city,
    CONSTANTS.CLIENT_STATUS.ENROLLED,
    businessName,
    gstNo,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

clientDB.editPartner = async ({
  name,
  businessEmail,
  ownershipPercentage,
  panNumber,
  businessName,
  gstNo,
  id,
}: clientTypes) => {
  const query =
    `Update Clients set name = ?, businessEmail = ?, ownershipPercentage = ?, panNumber = ?, businessName = ?, gstNo = ? where id = ?`;
  
  const data = [
    name,
    businessEmail,
    ownershipPercentage,
    panNumber,
    businessName,
    gstNo,
    id,
  ];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

clientDB.getParnters = async ({
  id,
  pageNum,
  limit,
  platform,
}: clientTypes & {pageNum: number; limit: number; platform: number;}) => {
  let query = `Select * from Clients where parentId = ? order by id desc`;
  const offset = (pageNum - 1) * limit;
  const data: any = [id];

  if (platform !== CONSTANTS.TENANT_DEVICE_TYPE.WEB) {
    query += ` limit ?, ?`;
    data.push(`${offset}`, `${limit}`);
  }

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

clientDB.getPartnersBySearch = async ({
  id,
  searchVal,
  searchType,
}: clientTypes & {searchVal: string; searchType: Number}) => {
  let filterCondition = "";
  const data: any = [id];
  if (Number(searchType) === 1) {
    filterCondition = ` and mobile like ?`
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = ` and name like ?`
    data.push(`%${searchVal}%`);
  }
  const query = `Select * from Clients where parentId = ? ${filterCondition} order by id desc`;

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

clientDB.getPartnersBySearchApp = async ({
  id,
  searchVal,
}: clientTypes & {searchVal: string}) => {
  const query = `Select * from Clients where parentId = ? and (mobile like ? or name like ?) order by id desc`;
  const data: any = [id, `%${searchVal}%`, `%${searchVal}%`];

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

clientDB.getParentByClientId = async({
  id,
}: clientTypes) => {
  const query = `Select * from Clients where id in (select parentId from Clients where id = ?)`;
  const data = [id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
} 



export default clientDB;