const dbConnect = require('../configs/dbConnection');


/**
 * Helper to build WHERE clause from conditions object
 */
function buildWhereClause(conditions) {
  if (!conditions || typeof conditions !== 'object') {
    return { clause: '', values: [] };
  }

  const values = [];
  const andParts = [];
  const orParts = [];

  // Handle standard AND conditions
  for (const key in conditions) {
    if (key === 'or') continue; // Skip 'or' for now

    const value = conditions[key];
    if (Array.isArray(value)) {
      andParts.push(`\`${key}\` IN (${value.map(() => '?').join(', ')})`);
      values.push(...value);
    } else {
      andParts.push(`\`${key}\` = ?`);
      values.push(value);
    }
  }

  // Handle OR conditions
  if (Array.isArray(conditions.or)) {
    const orClauses = conditions.or.map((cond) => {
      const [key, value] = Object.entries(cond)[0];
      values.push(value);
      return `\`${key}\` = ?`;
    });
    if (orClauses.length) {
      orParts.push('(' + orClauses.join(' OR ') + ')');
    }
  }

  // Combine both parts
  const allParts = [...andParts, ...orParts];
  if (!allParts.length) return { clause: '', values };
  
  console.log({
    clause: 'WHERE ' + allParts.join(' AND '),
    values
  });

  return {
    clause: 'WHERE ' + allParts.join(' AND '),
    values
  };
}


/**
 * SELECT handler: fetch rows from a table
 * Expects: { table, columns, conditions? }
 */
async function selectHandler(req, res) {
  const { table, columns, conditions } = req.body;
  console.log( table, columns, conditions );
  
  if (!table || !columns || !Array.isArray(columns)) {
    return res.status(400).json({ error: 'Missing table or columns array' });
  }

  try {
    // Handle "*" without backticks
    const cols = columns.includes('*') ? '*' : columns.map(c => `\`${c}\``).join(', ');

    // Build WHERE clause from conditions
    const { clause, values } = buildWhereClause(conditions);

    // Final SQL query
    const sql = `SELECT ${cols} FROM \`${table}\` ${clause}`;
    console.log('sql & values:', sql, values)

    // Execute query
    const [rows] = await dbConnect.query(sql, values);
    // console.log(rows);
    // Send result
    res.status(200).json({ rows });
  } catch (err) {
    console.error('SELECT error:', err);
    res.status(500).json({ error: 'Database error', details: err.message });
  }
}


/**
 * INSERT handler: add a new row to a table
 * Expects: { table, data }
 */
async function insertHandler(req, res) {
  const { table, data } = req.body;
  if (!table || !data || typeof data !== 'object') {
    return res.status(400).json({ error: 'Missing table or data object' });
  }
  try {
    const keys = Object.keys(data);
    const values = keys.map(key => data[key]);
    const cols = keys.map(key => `\`${key}\``).join(', ');
    const placeholders = keys.map(() => '?').join(', ');
    const sql = `INSERT INTO \`${table}\` (${cols}) VALUES (${placeholders})`;
    const [result] = await dbConnect.query(sql, values);
    res.json({ insertId: result.insertId });
  } catch (err) {
    console.error('INSERT error:', err);
    res.status(500).json({ error: 'Database error', details: err.message });
  }
}

/**
 * UPDATE handler: modify existing rows in a table
 * Expects: { table, data, conditions }
 */
async function updateHandler(req, res) {
  const { table, data, conditions } = req.body;
  if (!table || !data || typeof data !== 'object' || !conditions || typeof conditions !== 'object') {
    return res.status(400).json({ error: 'Missing table, data, or conditions object' });
  }
  try {
    const setKeys = Object.keys(data);
    const setClause = setKeys.map(key => `\`${key}\` = ?`).join(', ');
    const setValues = setKeys.map(key => data[key]);

    const { clause: whereClause, values: whereValues } = buildWhereClause(conditions);
    const sql = `UPDATE \`${table}\` SET ${setClause} ${whereClause}`;
    const [result] = await dbConnect.query(sql, [...setValues, ...whereValues]);
    res.json({ affectedRows: result.affectedRows });
  } catch (err) {
    console.error('UPDATE error:', err);
    res.status(500).json({ error: 'Database error', details: err.message });
  }
}

module.exports = { selectHandler, insertHandler, updateHandler };