SkyArtShop/backend/utils/queryHelpers.js

const { query } = require("../config/database");

// Whitelist of allowed table names to prevent SQL injection
const ALLOWED_TABLES = [
  "products",
  "product_images",
  "portfolioprojects",
  "blogposts",
  "pages",
  "adminusers",
  "roles",
  "uploads",
  "media_folders",
  "team_members",
  "site_settings",
  "session",
];

// Validate table name against whitelist
const validateTableName = (table) => {
  if (!ALLOWED_TABLES.includes(table)) {
    throw new Error(`Invalid table name: ${table}`);
  }
  return table;
};

const buildSelectQuery = (
  table,
  conditions = [],
  orderBy = "createdat DESC"
) => {
  validateTableName(table);
  const whereClause =
    conditions.length > 0 ? `WHERE ${conditions.join(" AND ")}` : "";
  return `SELECT * FROM ${table} ${whereClause} ORDER BY ${orderBy}`;
};

const getById = async (table, id) => {
  validateTableName(table);
  const result = await query(`SELECT * FROM ${table} WHERE id = $1`, [id]);
  return result.rows[0] || null;
};

/**
 * Get product with images by ID
 * @param {string} productId - Product ID
 * @returns {Promise<Object|null>} Product with images or null
 */
const getProductWithImages = async (productId) => {
  const result = await query(
    `SELECT p.*, 
      COALESCE(
        json_agg(
          json_build_object(
            'id', pi.id,
            'image_url', pi.image_url,
            'color_variant', pi.color_variant,
            'color_code', pi.color_code,
            'alt_text', pi.alt_text,
            'display_order', pi.display_order,
            'is_primary', pi.is_primary,
            'variant_price', pi.variant_price,
            'variant_stock', pi.variant_stock
          ) ORDER BY pi.display_order
        ) FILTER (WHERE pi.id IS NOT NULL),
        '[]'::json
      ) as images
    FROM products p
    LEFT JOIN product_images pi ON pi.product_id = p.id
    WHERE p.id = $1
    GROUP BY p.id`,
    [productId]
  );
  return result.rows[0] || null;
};

const getAllActive = async (table, orderBy = "createdat DESC") => {
  validateTableName(table);
  const result = await query(
    `SELECT * FROM ${table} WHERE isactive = true ORDER BY ${orderBy}`
  );
  return result.rows;
};

const deleteById = async (table, id) => {
  validateTableName(table);
  const result = await query(
    `DELETE FROM ${table} WHERE id = $1 RETURNING id`,
    [id]
  );
  return result.rowCount > 0;
};

const countRecords = async (table, condition = "") => {
  validateTableName(table);
  const whereClause = condition ? `WHERE ${condition}` : "";
  const result = await query(`SELECT COUNT(*) FROM ${table} ${whereClause}`);
  return parseInt(result.rows[0].count);
};

/**
 * Check if record exists
 * @param {string} table - Table name
 * @param {string} field - Field name
 * @param {any} value - Field value
 * @returns {Promise<boolean>} True if exists
 */
const exists = async (table, field, value) => {
  validateTableName(table);
  const result = await query(
    `SELECT EXISTS(SELECT 1 FROM ${table} WHERE ${field} = $1) as exists`,
    [value]
  );
  return result.rows[0].exists;
};

/**
 * Batch insert records
 * @param {string} table - Table name
 * @param {Array<Object>} records - Array of records
 * @param {Array<string>} fields - Field names (must match for all records)
 * @returns {Promise<Array>} Inserted records
 */
const batchInsert = async (table, records, fields) => {
  if (!records || records.length === 0) return [];
  validateTableName(table);

  const values = [];
  const placeholders = [];
  let paramIndex = 1;

  records.forEach((record) => {
    const rowPlaceholders = fields.map(() => `$${paramIndex++}`);
    placeholders.push(`(${rowPlaceholders.join(", ")})`);
    fields.forEach((field) => values.push(record[field]));
  });

  const sql = `
    INSERT INTO ${table} (${fields.join(", ")}) 
    VALUES ${placeholders.join(", ")} 
    RETURNING *
  `;

  const result = await query(sql, values);
  return result.rows;
};

/**
 * Update multiple records by IDs
 * @param {string} table - Table name
 * @param {Array<string>} ids - Array of record IDs
 * @param {Object} updates - Fields to update
 * @returns {Promise<Array>} Updated records
 */
const batchUpdate = async (table, ids, updates) => {
  if (!ids || ids.length === 0) return [];
  validateTableName(table);

  const updateFields = Object.keys(updates);
  const setClause = updateFields
    .map((field, i) => `${field} = $${i + 1}`)
    .join(", ");

  const values = [...Object.values(updates), ids];
  const sql = `
    UPDATE ${table} 
    SET ${setClause}, updatedat = NOW() 
    WHERE id = ANY($${updateFields.length + 1}) 
    RETURNING *
  `;

  const result = await query(sql, values);
  return result.rows;
};

/**
 * Execute query with transaction
 * @param {Function} callback - Callback function that receives client
 * @returns {Promise<any>} Result from callback
 */
const withTransaction = async (callback) => {
  const { pool } = require("../config/database");
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    const result = await callback(client);
    await client.query("COMMIT");
    return result;
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release();
  }
};

module.exports = {
  buildSelectQuery,
  getById,
  getProductWithImages,
  getAllActive,
  deleteById,
  countRecords,
  exists,
  batchInsert,
  batchUpdate,
  withTransaction,
  validateTableName,
};