const db = require("../config/db");
const openAiService = require("../services/openAiService");

let countCache = {};
let sqlQueryCache = {};

const setupLeads = async (req, res) => {
  try {
    const userId = req.body.userId;
    const conds = req.body.conds;
    const needTotalCount = req.body.needTotalCount;
    const page = req.body.page || 1;
    const limit = req.body.limit || 20;
    const offset = (page - 1) * limit;
    let totalCount;
    let baseQuery;

    console.log(needTotalCount, userId);

    const sampleArr = await getSampleData();

    // Check if SQL query is cached
    if (sqlQueryCache[conds] && sqlQueryCache[conds].expiry > Date.now()) {
      baseQuery = sqlQueryCache[conds].query;
    } else {
      // Generate and cache new SQL query
      baseQuery = await openAiService.generateSqlQuery(
        conds,
        sampleArr,
        userId
      );
      sqlQueryCache[conds] = {
        query: baseQuery,
        expiry: Date.now() + 1000 * 60 * 60, // Cache for 1 hour
      };
    }

    // Append LIMIT and OFFSET to the base query for pagination
    const paginatedQuery = `${baseQuery} LIMIT ${limit} OFFSET ${offset}`;

    // Fetch total count only if it's needed
    if (needTotalCount) {
      if (!countCache[conds] || countCache[conds].expiry < Date.now()) {
        // Fetch total count and cache it
        totalCount = await fetchTotalCount(conds, sampleArr, userId);
        countCache[conds] = {
          count: totalCount,
          expiry: Date.now() + 1000 * 60 * 60, // Cache for 1 hour
        };
      }
    }

    totalCount = countCache[conds]?.count || totalCount;

    // Execute the generated SQL query for data
    db.query(paginatedQuery, (error, queryResults) => {
      if (error) {
        console.error("SQL query execution error:", error);
        res.status(500).json({ error: error.message });
        return;
      }

      // Send SQL query, its results, and total count
      res.json({
        query: paginatedQuery,
        totalResults: totalCount,
        data: queryResults,
      });
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
};

async function fetchTotalCount(conds, sampleArr, userId) {
  const countQuery = await openAiService.generateCountQuery(
    conds,
    sampleArr,
    userId
  );
  return new Promise((resolve, reject) => {
    db.query(countQuery, (err, countResults) => {
      if (err) {
        reject(err);
        return;
      }
      resolve(countResults.length);
    });
  });
}

const getSampleData = async () => {
  return new Promise((resolve, reject) => {
    const query = "SELECT * FROM haraidata LIMIT 20";

    db.query(query, (error, results) => {
      if (error) {
        console.error("Database query error:", error);
        reject(error);
        return;
      }

      if (results.length === 0) {
        console.log("No data found in the database.");
        resolve([]);
        return;
      }

      const formattedResults = results.map((row) => ({
        leadimid: row.leadimid,
        policy_classification: row.policy_classification,
        second_branch: row.second_branch,
        first_branch: row.first_branch,
        insurance_company: row.insurance_company,
        premium_type: row.premium_type,
        policy_number: row.policy_number,
        premium: row.premium,
        product_type: row.product_type,
        end_date: row.end_date,
        start_date: row.start_date,
      }));

      resolve(formattedResults);
    });
  });
};

const searchLeads = async (req, res) => {
  try {
    const userId = req.body.userId;
    const searchTerm = req.body.searchTerm;
    const startDate = req.body.startDate;
    const endDate = req.body.endDate;
    const page = req.body.page || 1;
    const limit = req.body.limit || 20;
    const offset = (page - 1) * limit;

    const tableName = `user_${userId}`;

    // Construct a SQL query to search for leads
    let searchQuery = `
     SELECT *
     FROM ${tableName}
     WHERE
   `;
    let countQuery = `SELECT COUNT(*) AS total FROM ${tableName} WHERE `; // Count query

    let conditions = [];

    // Check if searchTerm is provided
    if (searchTerm) {
      const searchCondition = `
        (leadimid LIKE '%${searchTerm}%' OR
        policy_classification LIKE '%${searchTerm}%' OR
        second_branch LIKE '%${searchTerm}%' OR
        first_branch LIKE '%${searchTerm}%' OR
        insurance_company LIKE '%${searchTerm}%' OR
        premium_type LIKE '%${searchTerm}%' OR
        policy_number LIKE '%${searchTerm}%' OR
        premium LIKE '%${searchTerm}%' OR
        product_type LIKE '%${searchTerm}%')
      `;
      conditions.push(searchCondition);
    }

    // Check if both startDate and endDate are provided
    if (startDate && endDate) {
      const dateCondition = `(start_date >= '${startDate}' AND end_date <= '${endDate}')`;
      conditions.push(dateCondition);
    }

    const whereClause = conditions.join(" AND ");
    searchQuery += whereClause;
    countQuery += whereClause;

    // Add pagination to search query
    searchQuery += ` LIMIT ${limit} OFFSET ${offset}`;

    // Execute the SQL query for searching leads
    db.query(searchQuery, async (error, queryResults) => {
      if (error) {
        console.error("SQL query execution error:", error);
        res.status(500).json({ error: error.message });
        return;
      }

      // Fetch total count for pagination
      db.query(countQuery, (countError, countResults) => {
        if (countError) {
          console.error("SQL count query execution error:", countError);
          res.status(500).json({ error: countError.message });
          return;
        }

        // Send the search results along with total count
        res.json({
          query: searchQuery,
          totalResults: countResults[0].total,
          data: queryResults,
        });
      });
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
};

const invalidateCache = () => {
  countCache = {}; // Clear count cache
  sqlQueryCache = {}; // Clear SQL query cache
};

module.exports = {
  setupLeads,
  searchLeads,
  invalidateCache,
};
