import { ResultSetHeader, RowDataPacket } from "mysql2";
import DB from "../config/database/db";
import vendorsTypes from "../schemas/vendor.schema";
import CONSTANTS from "../config/constants";

const vendorDB: any = {};

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

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

vendorDB.getByMobileAndClientId = async ({ mobile, clientId }: vendorsTypes) => {
  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;
};

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

vendorDB.getByClientId = async ({ clientId }: vendorsTypes) => {
  const query =
    "Select * , ? as userType from Vendors where clientId = ? and status != ? and name not like ? and mobile != ? order by id desc";
  const data = [CONSTANTS.USER_TYPE.VENDOR, clientId, CONSTANTS.VENDOR_STATUS.DELETED, "%Payout%", "0000000001"];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

vendorDB.getByClientIdX = async ({ clientId }: vendorsTypes) => {
  const query =
    "Select * , ? as userType from Vendors where clientId = ? and status != ? and type != ? and name not like ? and mobile != ? order by id desc";
  const data = [
    CONSTANTS.USER_TYPE.VENDOR, 
    clientId, 
    CONSTANTS.VENDOR_STATUS.DELETED, 
    CONSTANTS.VENDOR_TYPES.LANDLORD,
    "%Payout%",
    "0000000001",
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

vendorDB.create = async ({ mobile, name, clientId, type, }: vendorsTypes) => {
  const query = "Insert into Vendors (clientId, name, mobile, type) values (?,?,?,?)";
  const data = [clientId, name, mobile, type];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

vendorDB.edit = async ({
  id,
  mobile,
  name,
  clientId,
  type
}: vendorsTypes) => {
  const query = "Update Vendors set name = ?, mobile = ?, type = ? where id = ? and clientId = ?";
  const data = [name, mobile, type, id, clientId];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  if (rows.affectedRows > 0) return true;
  else return false;
};

vendorDB.getByClientIdAndSearchVal = async ({
  clientId,
  searchVal,
}: vendorsTypes & { searchVal: string }) => {
  const query =
    "Select * , ? as userType from Vendors where clientId = ? and status != ? and (name like ? or mobile like ?) order by id desc";
  const data = [
    CONSTANTS.USER_TYPE.VENDOR,
    clientId,
    CONSTANTS.VENDOR_STATUS.DELETED,
    `%${searchVal}%`,
    `%${searchVal}%`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

vendorDB.getByClientIdAndSearchValX = async ({
  clientId,
  searchVal,
}: vendorsTypes & { searchVal: string }) => {
  const query =
    "Select * , ? as userType from Vendors where clientId = ? and status != ? and (name like ? or mobile like ?) order by id desc";
  const data = [
    CONSTANTS.USER_TYPE.VENDOR,
    clientId,
    CONSTANTS.VENDOR_STATUS.DELETED,
    `%${searchVal}%`,
    `%${searchVal}%`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

vendorDB.getVendorTypes = async () => {
  //To Hide landlord from vendor type list while adding vendor
  const query =
    "Select id, name from VendorTypes where status = 1 order by id asc";
  const [rows] = await DB.execute<RowDataPacket[]>(query);
  if (rows?.length > 0) return rows;
  else return false;
};

vendorDB.updateStatus = async ({
  id,
  status,
}: vendorsTypes) => {
  const query = `Update Vendors set status = ? where id = ?`;
  const data = [status, id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  if (rows.affectedRows > 0) return true;
  else return false;
};

export default vendorDB;