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

const stampPaperDB: any = {};

stampPaperDB.getByClientId = async ({
  clientId,
}: stampPapersTypes) => {
  const query =
    "Select * from StampPapers where clientId = ? and status = ? order by id asc limit 1";

  const data = [clientId, CONSTANTS.STAMP_PAPERS_STATUS.AVAILABLE];

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);

  if (rows?.length > 0) return rows[0];
  else return false;
};

stampPaperDB.getByClientIdAndPropId = async ({
  clientId,
  propId,
}: stampPapersTypes) => {
  const query =
    "Select * from StampPapers where clientId = ? and propId =? and status = ? order by id asc limit 1";

  const data = [clientId, propId, CONSTANTS.STAMP_PAPERS_STATUS.AVAILABLE];

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);

  if (rows?.length > 0) return rows[0];
  else return false;
};

stampPaperDB.getStatsByClientId = async ({
  clientId,
}: stampPapersTypes) => {
  // const query =
  //   `SELECT p.id as propId, p.name as propName, p.tenantPreference, p.streetAddress, JSON_ARRAYAGG(JSON_OBJECT('stampId', sp.stampId,'status', sp.status,'tenantName', sp.tenantName,'updatedOn', sp.updatedAt)) AS stamps, sp.firstPartyName, 
  //   CAST(SUM(CASE WHEN sp.status = 1 THEN 1 ELSE 0 END) AS UNSIGNED) AS available, 
  //   CAST(SUM(CASE WHEN sp.status = 2 THEN 1 ELSE 0 END) AS UNSIGNED) AS used
  //   FROM StampPapers sp  LEFT JOIN Properties p ON p.id = sp.propId  where sp.clientId =? GROUP BY sp.propId, sp.firstPartyName`;
  const query =
    `SELECT 
    p.id as propId,
    p.name as propName,
    p.tenantPreference,
    p.streetAddress,

    JSON_ARRAYAGG(
        JSON_OBJECT(
            'stampId', sp.stampId,
            'status', sp.status,
            'tenantName', sp.tenantName,
            'updatedOn', sp.updatedAt,
            'rentAgreement', d.value
        )
    ) AS stamps,

    sp.firstPartyName,

    CAST(SUM(CASE WHEN sp.status = 1 THEN 1 ELSE 0 END) AS UNSIGNED) AS available,

    CAST(SUM(CASE WHEN sp.status = 2 THEN 1 ELSE 0 END) AS UNSIGNED) AS used

    FROM StampPapers sp

    LEFT JOIN Properties p 
        ON p.id = sp.propId

    LEFT JOIN Documents d 
        ON d.tenantId = sp.tenantId
        AND d.type = ?
        AND d.moveOut = 0

    WHERE sp.clientId = ?

    GROUP BY 
        sp.propId,
        sp.firstPartyName`;

  const data = [CONSTANTS.DOCUMENT_TYPES.RENT_AGREEMENT, clientId];

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);

  if (rows?.length > 0) return rows;
  else return false;
};


stampPaperDB.getSummaryByClientId = async ({
  clientId,
}: stampPapersTypes) => {
  const query =
    `SELECT 
    p.id AS propId,
    p.name AS propName,
    p.tenantPreference,
    p.streetAddress,
    sp.firstPartyName,

    CAST(SUM(CASE WHEN sp.status = 1 THEN 1 ELSE 0 END) AS UNSIGNED) AS available,

    CAST(SUM(CASE WHEN sp.status = 2 THEN 1 ELSE 0 END) AS UNSIGNED) AS used

FROM StampPapers sp

LEFT JOIN Properties p 
    ON p.id = sp.propId

WHERE sp.clientId = ?

GROUP BY 
    p.id,
    sp.firstPartyName;`;

  const data = [clientId];

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);

  if (rows?.length > 0) return rows;
  else return false;
};

stampPaperDB.getStampsByClientId = async ({
  clientId,
}: stampPapersTypes) => {
  const query =
    `SELECT
    sp.id,
    sp.propId,
    sp.tenantId,
    sp.tenantName,
    sp.stampId,
    sp.status,
    sp.updatedAt,

    d.value AS rentAgreementDoc

FROM StampPapers sp

LEFT JOIN Documents d
    ON d.tenantId = sp.tenantId
    AND d.type = ?
    AND d.moveOut = 0

WHERE sp.clientId = ?;`;

  const data = [CONSTANTS.DOCUMENT_TYPES.RENT_AGREEMENT, clientId];

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);

  if (rows?.length > 0) return rows;
  else return false;
};

stampPaperDB.getAvailableStampsByClientIdAndPropId = async ({
  clientId,
  propId
}: stampPapersTypes) => {
  const query =
    `select * from StampPapers where clientId =? and status = ? and propId =?`;

  const data = [clientId, CONSTANTS.STAMP_PAPERS_STATUS.AVAILABLE, propId];

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);

  if (rows?.length > 0) return rows;
  else return false;
};

// stampPaperDB.getAllByClientId = async ({
//   clientId,
// }: stampPapersTypes) => {
//   const query =
//     "SELECT p.name as propName, sp.firstPartyName, SUM(CASE WHEN sp.status = 1 THEN 1 ELSE 0 END) AS available, SUM(CASE WHEN sp.status = 2 THEN 1 ELSE 0 END) AS used  FROM StampPapers sp  LEFT JOIN Properties p ON p.id = sp.propId  where sp.clientId =? GROUP BY sp.propId, sp.firstPartyName";

//   const data = [clientId];

//   const [rows] = await DB.execute<RowDataPacket[]>(query, data);

//   if (rows?.length > 0) return rows;
//   else return false;
// };

stampPaperDB.updateAllotment = async ({
  id,
  tenantId,
  propId,
  tenantName,
  status,
}: stampPapersTypes) => {
  const query = `
    Update StampPapers 
    set tenantId = ?, 
        propId = ?, 
        tenantName = ?, 
        status = ?
    where id = ?
  `;

  const data = [
    tenantId,
    propId,
    tenantName,
    status,
    id,
  ];

  const [rows] = await DB.execute<ResultSetHeader>(query, data);

  if (rows.affectedRows > 0) return true;
  else return false;
};


stampPaperDB.ShiftStampPapers = async ({
  fromPropId,
  toPropId,
  noOfStamps
}: any) => {
  const query = `
    Update StampPapers 
    set propId = ?
    where propId = ? and status = ? limit ?
  `;

  const data = [
    toPropId,
    fromPropId,
    CONSTANTS.STAMP_PAPERS_STATUS.AVAILABLE,
    `${noOfStamps}`
  ];

  const [rows] = await DB.execute<ResultSetHeader>(query, data);

  if (rows.affectedRows > 0) return true;
  else return false;
};

export default stampPaperDB;