import { ResultSetHeader, RowDataPacket } from "mysql2";
import DB from "../config/database/db";
import locationTypes from "../schemas/location.schema";
import log from "../config/log";

const locationDB: any = {};

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

locationDB.getByClientId = async ({ clientId }: locationTypes) => {
  const query = "Select * from Locations where clientId = ? order by id desc";
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

locationDB.getByClientIdAndPage = async ({ 
  clientId,
  pageNum,
  limit,
}: locationTypes & { pageNum: number; limit: number; }) => {
  const query = "Select * from Locations where clientId = ? order by id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [
    clientId,
    `${offset}`, 
    `${limit}`,
  ];
  
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

locationDB.getByClientIdWithLimitedFields = async ({ clientId }: locationTypes) => {
  const query = "Select id, name, clientId from Locations where clientId = ?";
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

locationDB.getByClientIdAndName = async ({ clientId, name }: locationTypes) => {
  const query = "Select * from Locations where clientId = ? and name = ?";
  const data = [clientId, name];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

locationDB.getByClientIdAndNameForEdit = async ({ clientId, name, id }: locationTypes) => {
  const query = "Select * from Locations where clientId = ? and name = ? and id != ? ";
  const data = [clientId, name, id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

locationDB.addLocation = async ({
  clientId,
  name,
  description,
}: locationTypes) => {
  const query = "Insert into Locations(clientId, name, description) values (?, ?, ?)";
  const data = [clientId, name, description];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

locationDB.editLocation = async ({
  id,
  name,
  description
}: locationTypes) => {
  const query = "Update Locations set name = ?, description = ? where id = ?";
  const data = [name, description, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

locationDB.deleteLocation = async ({
  id,
}: locationTypes) => {
  const query = "Delete from Locations where id = ?";
  const data = [id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

export default locationDB;