import DB from "../config/database/db";
import { ResultSetHeader, RowDataPacket } from "mysql2";
import feedbackTypes from "../schemas/feedback.schema";
import { freemem } from "os";

const feedbackDB: any = {};

feedbackDB.add = async ({
  tenantId,
  clientId,
  propId,
  propRemarks,
  propRating,
  staffRemarks,
  staffRating,
}: feedbackTypes) => {
  const query =
    "Insert into Feedback (tenantId, clientId, propId, propRemarks, propRating, staffRemarks, staffRating) values (?,?,?,?,?,?,?)";
  const data = [
    tenantId,
    clientId,
    propId,
    propRemarks,
    propRating,
    staffRemarks,
    staffRating,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

feedbackDB.getByTenantId = async ({ tenantId }: feedbackTypes) => {
  const query = "Select * from Feedback where tenantId = ?";
  const data = [tenantId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  return rows[0];
};

feedbackDB.update = async ({
  id,
  propRemarks,
  propRating,
  staffRemarks,
  staffRating,
}: feedbackTypes) => {
  const query =
    "Update Feedback set propRemarks = ?, propRating = ?, staffRemarks = ?, staffRating = ? where id = ?";
  const data = [propRemarks, propRating, staffRemarks, staffRating, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

feedbackDB.getFeedback = async ({ propId }: feedbackTypes) => {
  const query =
    "select F.id, F.propId, T.name as tenantName, T.mobile as tenantNumber , F.* from Feedback as F  JOIN Tenants as T  on T.id=F.tenantId where F.PropId=?";
  const data = [propId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  return [];
};

feedbackDB.getAvgRatings = async ({ propId }: feedbackTypes) => {
  const query =
    "Select AVG(propRating) as avgPropRating, AVG(staffRating) as avgStaffRating from Feedback where propId = ?";
  const data = [propId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  return rows[0];
};

export default feedbackDB;