import DB from "../config/database/db";
import { ResultSetHeader, RowDataPacket } from "mysql2";
import clientDocumentTypes from "../schemas/clientDocument.schema";
import documentFolderTypes from "../schemas/documentFolder.schema";
import log from "../config/log";

const clientDocumentsDB: any = {};

clientDocumentsDB.createDocumentFolder = async ({
  clientId,
  name,
  path,
  parentFolderId,
  createdBy,
}: documentFolderTypes) => {
  const query = ` INSERT INTO DocumentFolders ( clientId, name, path, parentFolderId, createdBy ) VALUES (?, ?, ?, ?, ?)`;
  const data = [
    clientId,
    name,
    path,
    parentFolderId,
    createdBy,
  ];

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

clientDocumentsDB.addDocument = async ({
  clientId,
  folderId,
  title,
  path,
  fileName,
}: clientDocumentTypes) => {
  const query = `INSERT INTO ClientDocuments ( clientId, folderId, title, path, fileName ) VALUES (?, ?, ?, ?, ?)`;
    
  const data = [
    clientId,
    folderId,
    title,
    path,
    fileName,
  ];

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

clientDocumentsDB.getDocumentsByClientIdAndFolderId = async ({
  clientId,
  folderId,
}: {
  clientId: number;
  folderId: number;
}) => {
  let query = `SELECT CD.* FROM ClientDocuments as CD WHERE CD.clientId = ?`;

  let data = [clientId,];

  if (folderId) {
    query += ` AND CD.folderId = ?`;
    data.push(folderId);
  }
  else {
    query += ` AND CD.folderId is null`;
    data.push(folderId);
  }
  query += ` ORDER BY CD.createdAt DESC`;

  const [rows] = await DB.query<any[]>(query, data);
  if (rows && rows.length > 0) return rows;
  return [];
};

clientDocumentsDB.getDocumentFolderById = async ({
  id,
}: documentFolderTypes) => {
  const query = ` SELECT * FROM DocumentFolders WHERE id = ? LIMIT 1 `;
  const data = [id];

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

clientDocumentsDB.getDocumentById = async ({
  id,
}: clientDocumentTypes) => {
  const query = ` SELECT * FROM ClientDocuments WHERE id = ? LIMIT 1 `;
  const data = [id];

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


clientDocumentsDB.getFoldersByClientIdAndParentId = async ({
  clientId,
  parentFolderId,
}: documentFolderTypes) => {
  let query = ` SELECT DF.*, (Select COUNT(id) from ClientDocuments where folderId = DF.id) as totalFiles, (Select COUNT(id) from DocumentFolders where parentFolderId = DF.id) as totalSubFolders FROM DocumentFolders as DF WHERE DF.clientId = ? `;
  const data: any[] = [clientId];

  if (parentFolderId) {
    query += ` AND DF.parentFolderId = ? ORDER BY DF.createdAt DESC `;
    data.push(parentFolderId);
  } else {
    query += ` AND DF.parentFolderId IS NULL ORDER BY DF.createdAt DESC `;
  }

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

clientDocumentsDB.getByDocumentFolderByClientIdAndNameAndParentId = async ({
  clientId,
  name,
  parentFolderId,
}: documentFolderTypes) => {
  let query = ` SELECT * FROM DocumentFolders WHERE clientId = ? AND name = ? `;
  const data: any[] = [clientId, name];

  if (parentFolderId) {
    query += ` AND parentFolderId = ? LIMIT 1 `;
    data.push(parentFolderId);
  } else {
    query += ` AND parentFolderId IS NULL LIMIT 1 `;
  }

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

clientDocumentsDB.deleteFolderById = async ({ id }: documentFolderTypes) => {
  const query = ` DELETE FROM DocumentFolders WHERE id = ? `;
  const data = [id];

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

clientDocumentsDB.deleteDocument = async ({ id }: clientDocumentTypes) => {
  const query = ` DELETE FROM ClientDocuments WHERE id = ? `;
  const data = [id];

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

export default clientDocumentsDB;