SkyArtShop/backend/utils/queryBuilders.js

/**
 * Optimized Query Builders
 * Reusable SQL query builders with proper field selection and pagination
 */

const PRODUCT_BASE_FIELDS = [
  "p.id",
  "p.name",
  "p.slug",
  "p.shortdescription",
  "p.description",
  "p.price",
  "p.category",
  "p.stockquantity",
  "p.sku",
  "p.weight",
  "p.dimensions",
  "p.material",
  "p.isfeatured",
  "p.isbestseller",
  "p.createdat",
];

const PRODUCT_IMAGE_AGG = `
  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,
        'is_primary', pi.is_primary,
        'display_order', pi.display_order,
        'variant_price', pi.variant_price,
        'variant_stock', pi.variant_stock
      ) ORDER BY pi.display_order, pi.created_at
    ) FILTER (WHERE pi.id IS NOT NULL),
    '[]'::json
  ) as images
`;

/**
 * Build product query with images
 * @param {Object} options - Query options
 * @param {string[]} options.fields - Additional fields to select
 * @param {string} options.where - WHERE clause
 * @param {string} options.orderBy - ORDER BY clause
 * @param {number} options.limit - LIMIT value
 * @returns {string} SQL query
 */
const buildProductQuery = ({
  fields = [],
  where = "p.isactive = true",
  orderBy = "p.createdat DESC",
  limit = null,
} = {}) => {
  const selectFields = [...PRODUCT_BASE_FIELDS, ...fields].join(", ");

  return `
    SELECT ${selectFields}, ${PRODUCT_IMAGE_AGG}
    FROM products p
    LEFT JOIN product_images pi ON pi.product_id = p.id
    WHERE ${where}
    GROUP BY p.id
    ORDER BY ${orderBy}
    ${limit ? `LIMIT ${limit}` : ""}
  `.trim();
};

/**
 * Build optimized query for single product by ID or slug
 * @param {string} identifier - Product ID or slug
 * @returns {Object} Query object with text and values
 */
const buildSingleProductQuery = (identifier) => {
  const isUUID = identifier.length === 36 && identifier.indexOf("-") === 8;
  const whereClause = isUUID ? "p.id = $1" : "(p.id = $1 OR p.slug = $1)";

  return {
    text:
      buildProductQuery({ where: `${whereClause} AND p.isactive = true` }) +
      " LIMIT 1",
    values: [identifier],
  };
};

/**
 * Build blog post query with field selection
 * @param {Object} options - Query options
 * @param {boolean} options.includeContent - Include full content
 * @param {boolean} options.publishedOnly - Filter by published status
 * @returns {string} SQL query
 */
const buildBlogQuery = ({
  includeContent = true,
  publishedOnly = true,
} = {}) => {
  const fields = includeContent
    ? "id, title, slug, excerpt, content, featuredimage, imageurl, images, ispublished, createdat"
    : "id, title, slug, excerpt, featuredimage, imageurl, ispublished, createdat";

  const whereClause = publishedOnly ? "WHERE ispublished = true" : "";

  return `SELECT ${fields} FROM blogposts ${whereClause} ORDER BY createdat DESC`;
};

/**
 * Build pages query with field selection
 * @param {Object} options - Query options
 * @param {boolean} options.includeContent - Include page content
 * @param {boolean} options.activeOnly - Filter by active status
 * @returns {string} SQL query
 */
const buildPagesQuery = ({ includeContent = true, activeOnly = true } = {}) => {
  const fields = includeContent
    ? "id, title, slug, pagecontent as content, metatitle, metadescription, isactive, createdat"
    : "id, title, slug, metatitle, metadescription, isactive, createdat";

  const whereClause = activeOnly ? "WHERE isactive = true" : "";

  return `SELECT ${fields} FROM pages ${whereClause} ORDER BY createdat DESC`;
};

/**
 * Build portfolio projects query
 * @param {boolean} activeOnly - Filter by active status
 * @returns {string} SQL query
 */
const buildPortfolioQuery = (activeOnly = true) => {
  const whereClause = activeOnly ? "WHERE isactive = true" : "";

  return `
    SELECT 
      id, title, description, imageurl, images, 
      category, categoryid, isactive, createdat, displayorder
    FROM portfolioprojects 
    ${whereClause}
    ORDER BY displayorder ASC, createdat DESC
  `.trim();
};

/**
 * Build categories query
 * @returns {string} SQL query
 */
const buildCategoriesQuery = () => {
  return `
    SELECT DISTINCT category 
    FROM products 
    WHERE isactive = true 
      AND category IS NOT NULL 
      AND category != '' 
    ORDER BY category ASC
  `.trim();
};

/**
 * Pagination helper
 * @param {number} page - Page number (1-indexed)
 * @param {number} limit - Items per page
 * @returns {Object} Offset and limit
 */
const getPagination = (page = 1, limit = 20) => {
  const validPage = Math.max(1, parseInt(page) || 1);
  const validLimit = Math.min(100, Math.max(1, parseInt(limit) || 20));
  const offset = (validPage - 1) * validLimit;

  return { offset, limit: validLimit, page: validPage };
};

/**
 * Add pagination to query
 * @param {string} query - Base SQL query
 * @param {number} page - Page number
 * @param {number} limit - Items per page
 * @returns {string} SQL query with pagination
 */
const addPagination = (query, page, limit) => {
  const { offset, limit: validLimit } = getPagination(page, limit);
  return `${query} LIMIT ${validLimit} OFFSET ${offset}`;
};

module.exports = {
  buildProductQuery,
  buildSingleProductQuery,
  buildBlogQuery,
  buildPagesQuery,
  buildPortfolioQuery,
  buildCategoriesQuery,
  getPagination,
  addPagination,
  PRODUCT_BASE_FIELDS,
  PRODUCT_IMAGE_AGG,
};