const sequelize = require('../config/databaseConfig');

const buildDynamicQuery = (viewName, queryParams) => {
  try {
    // Build the SQL query dynamically based on the provided parameters
    const query = `SELECT * FROM ${viewName} WHERE `;
    const conditions = Object.keys(queryParams)
      .map((key) => `${key} = :${key}`)
      .join(' AND ');

    return query + conditions;
  } catch (error) {
    console.error(`Error building dynamic query: ${error.message}`);
    throw error;
  }
};

const executeDynamicQuery = async (viewName, queryParams, orderByClause = "") => {
    try {
      // Build the dynamic SQL query
      let fullQuery = buildDynamicQuery(viewName, queryParams);
      if (orderByClause !== ""){
        fullQuery = fullQuery + orderByClause;
        
      }
      // Execute the custom SQL query
      const result = await sequelize.query(fullQuery, {
        replacements: queryParams,
        type: sequelize.QueryTypes.SELECT,
      });
  
      return result;
    } catch (error) {
      console.error(`Error executing dynamic query: ${error.message}`);
      throw error;
    }
  };

  const executeDynamicQueryWithWhere = async (viewName, whereClause, queryParams) => {
    try {
        // Build the dynamic SQL query with WHERE clause
        const fullQuery = `SELECT * FROM ${viewName} WHERE ${whereClause}`;
        
        // Execute the custom SQL query
        const result = await sequelize.query(fullQuery, {
            replacements: queryParams,
            type: sequelize.QueryTypes.SELECT,
        });

        return result;
    } catch (error) {
        console.error(`Error executing dynamic query: ${error.message}`);
        throw error;
    }
};

const executeDynamicQueryWithWhereAndGroupBy = async (viewName, whereClause, viewFields = "*", groupByClause = "", queryParams) => {
  try {
      // Build the dynamic SQL query with WHERE and GROUP BY clauses
      let fullQuery = `SELECT ${viewFields} FROM ${viewName}`;
      if (whereClause) {
          fullQuery += ` WHERE ${whereClause}`;
      }
      if (groupByClause) {
          fullQuery += ` GROUP BY ${groupByClause}`;
      }
      
 
    
      // Execute the custom SQL query
      const result = await sequelize.query(fullQuery, {
          replacements: queryParams,
          type: sequelize.QueryTypes.SELECT,
      });

      return result;
  } catch (error) {
      console.error(`Error executing dynamic query: ${error.message}`);
      throw error;
  }
};


module.exports = {
  executeDynamicQuery,
  executeDynamicQueryWithWhere,
  executeDynamicQueryWithWhereAndGroupBy,
};
