USER
updateProduct Error updating product: error: current transaction is aborted, commands ignored until end of transaction block
import { Request, Response } from 'express';
import {query} from "../config/database";
import {brandsCache, productCache} from "../cache";
import {
Brand,
BrandsCacheData,
CacheData, Category, Color, CreateProductInput,
Image,
ImageInput,
Product, Size,
UpdateProductInput
} from "product";
import {addProductToCache, deleteProductFromCache, updateProductInCache} from "../cacheHelper";
const fetchCategoryDetails = async (subcategoryIds: number[]) => {
const categoriesResult = await query(
`SELECT sc.id AS subcategory_id, sc.name AS subcategory_name,
c.id AS main_category_id, c.name AS main_category_name
FROM subcategories sc
JOIN categories c ON sc.category_id = c.id
WHERE sc.id = ANY($1::int[])`,
[subcategoryIds]
);
return categoriesResult.rows.map((row: any) => ({
id: row.subcategory_id,
name: row.subcategory_name,
main_category_id: row.main_category_id,
main_category_name: row.main_category_name
}));
};
export const loadAndCacheAllProducts = async (): Promise<CacheData> => {
const q = `
SELECT
p.id,
p.name,
p.description,
p.brand_id,
p.has_color,
p.has_size,
p.product_type,
p.price,
p.qty,
p.sold,
p.created_at,
p.updated_at,
sc.id AS subcategory_id,
sc.name AS subcategory_name,
c.id AS main_category_id,
c.name AS main_category_name,
s.id AS size_id,
s.name AS size_name,
s.price AS size_price,
s.qty AS size_qty,
s.sold AS size_sold,
cl.id AS color_id,
cl.name AS color_name,
cl.price AS color_price,
cl.qty AS color_qty,
cl.sold AS color_sold,
i.id AS image_id,
i.url AS image_url,
i.color_id AS image_color_id,
i.size_id AS image_size_id
FROM products p
LEFT JOIN product_categories pc ON p.id = pc.product_id
LEFT JOIN subcategories sc ON pc.category_id = sc.id
LEFT JOIN categories c ON sc.category_id = c.id
LEFT JOIN colors cl ON p.id = cl.product_id
LEFT JOIN sizes s ON p.id = s.product_id
LEFT JOIN images i ON (p.id = i.product_id AND (cl.id = i.color_id OR i.color_id IS NULL) AND (s.id = i.size_id OR i.size_id IS NULL))
`;
try {
const result = await query(q, []);
const productsMap: Record<number, Product> = {};
// Temporary map to hold sizes that need to be assigned to colors
const sizesMap: Record<number, Size> = {};
// First pass: Initialize products and collect sizes
result.rows.forEach((row: any) => {
const productId = Number(row.id);
// Initialize the product in the map if it doesn't exist
if (!productsMap[productId]) {
productsMap[productId] = {
id: productId,
name: row.name,
description: row.description,
brand_id: Number(row.brand_id),
has_color: Boolean(row.has_color),
has_size: Boolean(row.has_size),
product_type: row.product_type,
price: Number(row.price),
qty: Number(row.qty),
sold: Number(row.sold),
created_at: new Date(row.created_at).toISOString(),
updated_at: new Date(row.updated_at).toISOString(),
categories: [],
colors: [],
sizes: [], // Will be populated only if has_color is false
images: []
};
}
const product = productsMap[productId];
// Add category if present and not already added
if (
row.subcategory_id &&
row.subcategory_name &&
row.main_category_id &&
row.main_category_name
) {
const categoryId = Number(row.subcategory_id);
const mainCategoryId = Number(row.main_category_id);
const existingCategory = product.categories.find(cat => cat.id === categoryId);
if (!existingCategory) {
product.categories.push({
id: categoryId,
name: row.subcategory_name,
main_category_id: mainCategoryId,
main_category_name: row.main_category_name,
});
}
}
// Add color if present and not already added
if (row.color_id && row.color_name && row.has_size !== undefined) {
const colorId = Number(row.color_id);
let color = product.colors.find(color => color.id === colorId);
if (!color) {
color = {
id: colorId,
name: row.color_name,
price: Number(row.color_price),
qty: Number(row.color_qty),
sold: Number(row.color_sold),
has_size: Boolean(row.has_size),
sizes: [], // Will be populated if has_size is true
images: []
};
product.colors.push(color);
}
}
// Collect sizes to assign later based on color association
if (row.size_id && row.size_name) {
const sizeId = Number(row.size_id);
const colorId = row.color_id ? Number(row.color_id) : null;
// If the size is associated with a color, store it for later assignment
if (colorId && product.has_color) {
sizesMap[sizeId] = {
id: sizeId,
name: row.size_name,
price: Number(row.size_price),
qty: Number(row.size_qty),
sold: Number(row.size_sold),
color_id: colorId,
images: []
};
} else if (!product.has_color && product.has_size) {
// If the product has sizes but no colors, assign size directly
if (!product.sizes.find(s => s.id === sizeId)) {
product.sizes.push({
id: sizeId,
name: row.size_name,
price: Number(row.size_price),
qty: Number(row.size_qty),
sold: Number(row.size_sold),
color_id: null,
images: []
});
}
}
}
// Collect images to assign
if (row.image_id && row.image_url) {
const imageId = Number(row.image_id);
// Avoid duplicate images
if (!product.images.find(img => img.id === imageId)) {
product.images.push({
id: imageId,
url: row.image_url,
color_id: row.image_color_id ? Number(row.image_color_id) : null,
size_id: row.image_size_id ? Number(row.image_size_id) : null
});
}
// Associate image with color if applicable
if (row.image_color_id) {
const colorId = Number(row.image_color_id);
const color = product.colors.find(c => c.id === colorId);
if (color && !color.images.includes(row.image_url)) {
color.images.push(row.image_url);
}
}
// Associate image with size if applicable and size is within a color
if (row.image_size_id) {
const sizeId = Number(row.image_size_id);
const size = sizesMap[sizeId];
if (size) {
if (!size.images.includes(row.image_url)) {
size.images.push(row.image_url);
}
} else {
// If the product does not have colors, associate with product's sizes
const standaloneSize = product.sizes.find(s => s.id === Number(row.image_size_id));
if (standaloneSize && !standaloneSize.images.includes(row.image_url)) {
standaloneSize.images.push(row.image_url);
}
}
}
}
});
// Second pass: Assign sizes to their respective colors
Object.values(productsMap).forEach(product => {
if (product.has_color) {
// Initialize all product.sizes as empty
product.sizes = [];
// Iterate over collected sizes and assign to colors
Object.values(sizesMap).forEach(size => {
if (size.color_id === null) return; // Skip sizes without color_id
const color = product.colors.find(c => c.id === size.color_id);
if (color) {
color.sizes.push({
id: size.id,
name: size.name,
price: size.price,
qty: size.qty,
sold: size.sold,
color_id: size.color_id,
images: size.images
});
}
});
// Update has_size for each color based on whether it has sizes
product.colors.forEach(color => {
color.has_size = color.sizes.length > 0;
});
}
// If has_color is false, sizes are already assigned directly to product.sizes
});
const allProducts = Object.values(productsMap);
// Calculate min and max prices across all products
let minPrice = Infinity;
let maxPrice = -Infinity;
allProducts.forEach(product => {
// Product price
if (product.price < minPrice) minPrice = product.price;
if (product.price > maxPrice) maxPrice = product.price;
// Prices from colors and their sizes
product.colors.forEach(color => {
if (color.price < minPrice) minPrice = color.price;
if (color.price > maxPrice) maxPrice = color.price;
color.sizes.forEach(size => {
if (size.price < minPrice) minPrice = size.price;
if (size.price > maxPrice) maxPrice = size.price;
});
});
// Prices from standalone sizes
product.sizes.forEach(size => {
if (size.price < minPrice) minPrice = size.price;
if (size.price > maxPrice) maxPrice = size.price;
});
});
// Fallback in case no prices are present
if (minPrice === Infinity) minPrice = 0;
if (maxPrice === -Infinity) maxPrice = 0;
// Cache the data with a consistent key
const cacheData: CacheData = { allProducts, minPrice, maxPrice };
productCache.set('allProducts', cacheData);
return cacheData;
} catch (error) {
console.error('Error loading products from DB:', error);
throw error; // Propagate error to be handled by caller
}
};
/**
* Fetches all brands from the database and caches them.
* @returns {Promise<BrandsCacheData>} A promise that resolves to the cached brand data.
*/
const loadAndCacheAllBrands = async (): Promise<BrandsCacheData> => {
const q = `
SELECT s.*, c.name as category_name
FROM subcategories s
JOIN categories c ON s.category_id = c.id AND c.is_brand
ORDER BY c.name, s.name
`;
try {
const result = await query(q, []);
const allBrands: Brand[] = result.rows.map((row: any) => ({
id: Number(row.id),
name: row.name,
category_id: Number(row.category_id),
category_name: row.category_name,
}));
// Cache the brands
const brandsCacheData: BrandsCacheData = { allBrands };
brandsCache.set('allBrands', brandsCacheData);
return brandsCacheData;
} catch (error) {
console.error('Error loading brands from DB:', error);
throw error; // Propagate error to be handled by caller
}
};
/**
* Controller to handle fetching and responding with filtered products.
* Caches all products and brands on first request and performs in-memory filtering thereafter.
* @param {Request} req - Express request object containing query parameters.
* @param {Response} res - Express response object to send JSON data.
*/
export const getProducts = async (req: Request, res: Response): Promise<void> => {
try {
// Retrieve cached products
let cacheData: CacheData | undefined = productCache.get('allProducts');
if (!cacheData) {
// Cache miss: Load from DB and cache
cacheData = await loadAndCacheAllProducts();
} else {
}
const { allProducts, minPrice, maxPrice } = cacheData;
// Log total products before any filtering
// Extract and sanitize query parameters
let {
search = '',
c,
sc,
b,
minPriceA = '0',
maxPriceA = '',
availability = 'all',
sortBy = '',
page = '1',
itemsPerPage = '15',
} = req.query;
// Parse numerical values
const pageNum = parseInt(page as string, 10) || 1;
const itemsPerPageNum = parseInt(itemsPerPage as string, 10) || 15;
const minPriceANum = parseFloat(minPriceA as string) || 0;
let maxPriceANum = maxPriceA ? parseFloat(maxPriceA as string) : Infinity;
// If maxPriceA is less than minPriceA, reset to Infinity
if (!isNaN(maxPriceANum) && maxPriceANum < minPriceANum) {
console.warn('Provided maxPriceA is less than minPriceA. Resetting maxPriceANum to Infinity.');
maxPriceANum = Infinity;
} else if (isNaN(maxPriceANum)) {
maxPriceANum = Infinity;
}
// Parse c, sc, b as numbers if present
const cNum = c ? parseInt(c as string, 10) : undefined;
const scNum = sc ? parseInt(sc as string, 10) : undefined;
const bNum = b ? parseInt(b as string, 10) : undefined;
// Initialize brand-related variables if 'b' is provided
let allBrands: Brand[] = [];
if (bNum !== undefined && !isNaN(bNum)) {
// Retrieve cached brands
let brandsCacheData: BrandsCacheData | undefined = brandsCache.get('allBrands');
if (!brandsCacheData) {
// Cache miss: Load from DB and cache
brandsCacheData = await loadAndCacheAllBrands();
} else {
}
allBrands = brandsCacheData.allBrands;
// Check if 'bNum' is a valid brand ID
const isValidBrand = allBrands.some(brand => brand.category_id === bNum);
if (!isValidBrand) {
console.warn(`Brand ID ${bNum} is invalid.`);
// Return zero products since 'b' is invalid
res.json({
products: [],
total: 0,
page: pageNum,
itemsPerPage: itemsPerPageNum,
totalPages: 0,
hasMore: false,
minPrice,
maxPrice,
});
return;
}
}
// Determine if any filters are applied
const hasFilters = !!search || !!cNum || !!scNum || !!bNum ||
minPriceANum > 0 || maxPriceANum < Infinity ||
(availability !== 'all');
// Start with all products
let filteredProducts = [...allProducts];
// Apply Search Filter
if (search && typeof search === 'string') {
const searchLower = search.toLowerCase();
const searchWords = searchLower.split(/\s+/).filter(Boolean);
filteredProducts = filteredProducts.filter(product =>
searchWords.every(word => product.name.toLowerCase().includes(word))
);
}
// Apply Category Filter (c)
if (cNum !== undefined && !isNaN(cNum)) {
filteredProducts = filteredProducts.filter(product =>
product.categories.some(cat => cat.main_category_id === cNum)
);
}
// Apply Subcategory Filter (sc)
if (scNum !== undefined && !isNaN(scNum)) {
filteredProducts = filteredProducts.filter(product =>
product.brand_id === scNum ||
product.categories.some(cat => cat.id === scNum)
);
}
// Apply Brand Filter (b)
if (bNum !== undefined && !isNaN(bNum)) {
const preFilterCount = filteredProducts.length;
allBrands = allBrands.filter(brand => brand.category_id === bNum)
filteredProducts = filteredProducts.filter(product =>
allBrands.some(brand => brand.id === product.brand_id)
);
}
// Apply Price Range Filter
if (minPriceANum > 0 || maxPriceANum < Infinity) {
filteredProducts = filteredProducts.filter(product => {
const productPrices = product.sizes.map(size => size.price);
if (productPrices.length === 0) return false;
const productMinPrice = Math.min(...productPrices);
const productMaxPrice = Math.max(...productPrices);
return productMinPrice >= minPriceANum && productMaxPrice <= maxPriceANum;
});
}
// Apply Availability Filter
if (availability === 'in_stock') {
filteredProducts = filteredProducts.filter(product =>
product.sizes.some(size => size.qty > 0)
);
} else if (availability === 'out_of_stock') {
filteredProducts = filteredProducts.filter(product =>
product.sizes.every(size => size.qty === 0)
);
}
// Log total after filtering
// Apply Sorting
if (sortBy && typeof sortBy === 'string') {
switch (sortBy) {
case 'Price: Low to High':
filteredProducts.sort((a, b) => {
const aMin = Math.min(...a.sizes.map(s => s.price));
const bMin = Math.min(...b.sizes.map(s => s.price));
return aMin - bMin;
});
break;
case 'Price: High to Low':
filteredProducts.sort((a, b) => {
const aMax = Math.max(...a.sizes.map(s => s.price));
const bMax = Math.max(...b.sizes.map(s => s.price));
return bMax - aMax;
});
break;
case 'Name: A to Z':
filteredProducts.sort((a, b) => a.name.localeCompare(b.name));
break;
case 'Name: Z to A':
filteredProducts.sort((a, b) => b.name.localeCompare(a.name));
break;
case 'Best Selling':
filteredProducts.sort((a, b) => b.sold - a.sold);
break;
case 'Newest Arrivals':
filteredProducts.sort((a, b) => new Date(b.created_at).getTime() - new Date(a.created_at).getTime());
break;
default:
// If sortBy parameter doesn't match any case, do not sort
break;
}
} else if (!hasFilters) {
// If no filters are applied, sort by created_at descending to show latest products
filteredProducts.sort((a, b) => new Date(b.created_at).getTime() - new Date(a.created_at).getTime());
}
// Apply Pagination
const totalFilteredProducts = filteredProducts.length;
const totalPages = Math.ceil(totalFilteredProducts / itemsPerPageNum);
const startIdx = (pageNum - 1) * itemsPerPageNum;
const endIdx = startIdx + itemsPerPageNum;
const paginatedProducts = filteredProducts.slice(startIdx, endIdx);
// Construct Response
const response = {
products: paginatedProducts,
total: totalFilteredProducts,
page: pageNum,
itemsPerPage: itemsPerPageNum,
totalPages,
hasMore: endIdx < totalFilteredProducts,
minPrice, // Global minPrice across all products
maxPrice, // Global maxPrice across all products
};
// Send Response
res.json(response);
} catch (error) {
console.error('Error fetching products:', error);
res.status(500).json({ error: 'Internal server error' });
} finally {
// Optional: Any cleanup operations can be performed here
// For example, monitoring memory usage or logging request completion
}
};
export const getProductById = async (req: Request, res: Response) => {
const { id } = req.params;
const productId = parseInt(id, 10);
if (isNaN(productId)) {
return res.status(400).json({ error: 'Invalid product ID' });
}
try {
// Attempt to retrieve the cached products
let cacheData = productCache.get('allProducts') as CacheData | undefined;
if (cacheData) {
} else {
// Cache miss: Load all products and cache them
cacheData = await loadAndCacheAllProducts();
}
// Find the product in the cached data
const cachedProduct = cacheData.allProducts.find(product => product.id === productId);
if (cachedProduct) {
return res.json({ product: cachedProduct });
} else {
// Fallback: Fetch the product from the database
const productFromDb = await fetchProductByIdFromDb(productId);
if (productFromDb) {
// Add the fetched product to the cache
addProductToCache(productFromDb);
return res.json({ product: productFromDb });
} else {
// Product not found in the database
return res.status(404).json({ error: 'Product not found' });
}
}
} catch (error) {
console.error('Error fetching product by ID:', error);
res.status(500).json({ error: 'Internal server error' });
}
};
/**
* Fetches a single product by ID directly from the database.
* @param productId - ID of the product to fetch.
* @returns {Promise<Product | null>} The product if found, else null.
*/
const fetchProductByIdFromDb = async (productId: number): Promise<Product | null> => {
const productQuery = `
SELECT
p.id,
p.name,
p.description,
p.brand_id,
p.has_color,
p.has_size,
p.product_type,
p.price,
p.qty,
p.sold,
p.created_at,
p.updated_at,
sc.id AS subcategory_id,
sc.name AS subcategory_name,
c.id AS main_category_id,
c.name AS main_category_name,
cl.id AS color_id,
cl.name AS color_name,
cl.price AS color_price,
cl.qty AS color_qty,
cl.sold AS color_sold,
s.id AS size_id,
s.name AS size_name,
s.price AS size_price,
s.qty AS size_qty,
s.sold AS size_sold,
i.id AS image_id,
i.url AS image_url
FROM products p
LEFT JOIN product_categories pc ON p.id = pc.product_id
LEFT JOIN subcategories sc ON pc.category_id = sc.id
LEFT JOIN categories c ON sc.category_id = c.id
LEFT JOIN colors cl ON p.id = cl.product_id
LEFT JOIN sizes s ON (p.id = s.product_id AND (cl.id = s.color_id OR s.color_id IS NULL))
LEFT JOIN images i ON (p.id = i.product_id AND (cl.id = i.color_id OR i.color_id IS NULL) AND (s.id = i.size_id OR i.size_id IS NULL))
WHERE p.id = $1
ORDER BY pc.category_id, cl.id, s.id, i.id
`;
try {
const result = await query(productQuery, [productId]);
if (result.rows.length === 0) {
return null;
}
// Process the result to construct the Product object
let product: Product = {
id: result.rows[0].id,
name: result.rows[0].name,
description: result.rows[0].description,
brand_id: result.rows[0].brand_id,
has_color: result.rows[0].has_color,
has_size: result.rows[0].has_size,
product_type: result.rows[0].product_type,
price: result.rows[0].price,
qty: result.rows[0].qty,
sold: result.rows[0].sold,
created_at: new Date(result.rows[0].created_at).toISOString(),
updated_at: new Date(result.rows[0].updated_at).toISOString(),
categories: [],
colors: [],
sizes: [],
images: []
};
// Iterate through the rows to populate categories, colors, sizes, and images
for (const row of result.rows) {
// Categories
if (row.subcategory_id && row.subcategory_name && row.main_category_id && row.main_category_name) {
const existingCategory = product.categories.find(cat => cat.id === row.subcategory_id);
if (!existingCategory) {
product.categories.push({
id: row.subcategory_id,
name: row.subcategory_name,
main_category_id: row.main_category_id,
main_category_name: row.main_category_name,
});
}
}
// Colors
if (row.color_id && row.color_name) {
let color = product.colors.find(c => c.id === row.color_id);
if (!color) {
color = {
id: row.color_id,
name: row.color_name,
price: row.color_price,
qty: row.color_qty,
sold: row.color_sold,
has_size: row.has_size,
sizes: [],
images: []
};
product.colors.push(color);
}
}
// Sizes
if (row.size_id && row.size_name) {
const size: Size = {
id: row.size_id,
name: row.size_name,
price: row.size_price,
qty: row.size_qty,
sold: row.size_sold,
color_id: row.color_id || null,
images: []
};
// Associate size with color if applicable
if (row.color_id) {
const color = product.colors.find(c => c.id === row.color_id);
if (color) {
const existingSize = color.sizes.find(s => s.id === size.id);
if (!existingSize) {
color.sizes.push(size);
}
}
} else if (product.has_size) {
const existingSize = product.sizes.find(s => s.id === size.id);
if (!existingSize) {
product.sizes.push(size);
}
}
}
// Images
if (row.image_id && row.image_url) {
const image = {
id: row.image_id,
url: row.image_url,
color_id: row.color_id || null,
size_id: row.size_id || null
};
// Avoid duplicate images
if (!product.images.some(img => img.id === image.id)) {
product.images.push(image);
}
// Associate image with color if applicable
if (row.color_id) {
const color = product.colors.find(c => c.id === row.color_id);
if (color && !color.images.includes(row.image_url)) {
color.images.push(row.image_url);
}
}
// Associate image with size if applicable
if (row.size_id) {
if (row.color_id) {
const color = product.colors.find(c => c.id === row.color_id);
const size = color?.sizes.find(s => s.id === row.size_id);
if (size && !size.images.includes(row.image_url)) {
size.images.push(row.image_url);
}
} else {
const size = product.sizes.find(s => s.id === row.size_id);
if (size && !size.images.includes(row.image_url)) {
size.images.push(row.image_url);
}
}
}
}
}
return product;
} catch (error) {
console.error('Error fetching product from DB:', error);
throw error; // Let the caller handle the error
}
};
export const createProduct = async (req: Request, res: Response) => {
const client = await query('BEGIN'); // Ensure you have transaction handling
try {
const productData: CreateProductInput = req.body;
// Adjust price if necessary
let adjustedPrice = productData.price;
if (productData.price < 1000) {
adjustedPrice = parseFloat((productData.price * 1000).toFixed(2));
}
// Insert product
const productResult = await query(
`INSERT INTO products (name, description, brand_id, has_color, has_size, product_type, price, qty, sold, created_at, updated_at)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
RETURNING id, created_at, updated_at`,
[
productData.name,
productData.description,
productData.brand_id,
Boolean(productData.has_color),
Boolean(productData.has_size),
productData.product_type,
adjustedPrice,
productData.qty
]
);
const productId = productResult.rows[0].id;
// Insert category associations
if (productData.category_ids && productData.category_ids.length > 0) {
const categoryInsertPromises = productData.category_ids.map((categoryId: number) => {
return query(
`INSERT INTO product_categories (product_id, category_id)
VALUES ($1, $2)`,
[productId, categoryId]
);
});
await Promise.all(categoryInsertPromises);
}
// Insert images and collect them
const insertedImages: Image[] = [];
if (productData.images && productData.images.length > 0) {
const imagesInsertPromises = productData.images
.filter((image: ImageInput) => image.url) // Ensure URL exists
.map(async (image: ImageInput) => {
const imgResult = await query(
`INSERT INTO images (product_id, url, color_id, size_id)
VALUES ($1, $2, $3, $4)
RETURNING id, url, color_id, size_id`,
[productId, image.url, image.color_id || null, image.size_id || null]
);
return {
id: imgResult.rows[0].id,
url: imgResult.rows[0].url,
color_id: imgResult.rows[0].color_id,
size_id: imgResult.rows[0].size_id
} as Image;
});
const insertedImagesResults = await Promise.all(imagesInsertPromises);
insertedImages.push(...insertedImagesResults);
}
// Handle colors and sizes based on product type with their images
const insertedColors: { [key: number]: Color } = {}; // Map color index to Color
const insertedSizes: { [key: number]: Size } = {}; // Map size index to Size
if (productData.has_color && productData.colors && productData.colors.length > 0) {
for (const [colorIndex, color] of productData.colors.entries()) {
// Adjust color price if necessary
let adjustedColorPrice = color.price;
if (color.price < 1000) {
adjustedColorPrice = parseFloat((color.price * 1000).toFixed(2));
}
// Insert color
const colorResult = await query(
`INSERT INTO colors (product_id, name, price, qty, sold, has_size)
VALUES ($1, $2, $3, $4, 0, $5)
RETURNING id, has_size`,
[productId, color.name, adjustedColorPrice, color.qty, Boolean(color.has_size)]
);
const colorId = colorResult.rows[0].id;
const colorHasSize = colorResult.rows[0].has_size;
insertedColors[colorIndex] = {
id: colorId,
name: color.name,
price: adjustedColorPrice,
qty: color.qty,
sold: 0,
has_size: colorHasSize,
sizes: [],
images: []
};
// Insert color images
if (color.images && color.images.length > 0) {
const colorImagesInsertPromises = color.images.map(async (image: ImageInput) => {
const colorImgResult = await query(
`INSERT INTO images (product_id, color_id, url, size_id)
VALUES ($1, $2, $3, $4)
RETURNING id, url`,
[productId, colorId, image.url, image.size_id || null]
);
return {
id: colorImgResult.rows[0].id,
url: colorImgResult.rows[0].url,
color_id: colorId,
size_id: null
} as Image;
});
const insertedColorImages = await Promise.all(colorImagesInsertPromises);
insertedColors[colorIndex].images.push(...insertedColorImages.map(img => img.url));
insertedImages.push(...insertedColorImages);
}
// Insert sizes for the color
if (color.has_size && color.sizes && color.sizes.length > 0) {
for (const [sizeIndex, size] of color.sizes.entries()) {
// Adjust size price if necessary
let adjustedSizePrice = size.price;
if (size.price < 1000) {
adjustedSizePrice = parseFloat((size.price * 1000).toFixed(2));
}
// Insert size
const sizeResult = await query(
`INSERT INTO sizes (product_id, color_id, name, price, qty, sold)
VALUES ($1, $2, $3, $4, $5, 0)
RETURNING id`,
[productId, colorId, size.name, adjustedSizePrice, size.qty]
);
const sizeId = sizeResult.rows[0].id;
insertedSizes[size.id] = {
id: sizeId,
name: size.name,
price: adjustedSizePrice,
qty: size.qty,
sold: 0,
color_id: colorId,
images: []
};
// Insert size images
if (size.images && size.images.length > 0) {
const sizeImagesInsertPromises = size.images.map(async (image: ImageInput) => {
const sizeImgResult = await query(
`INSERT INTO images (product_id, color_id, size_id, url)
VALUES ($1, $2, $3, $4)
RETURNING id, url`,
[productId, colorId, sizeId, image.url]
);
return {
id: sizeImgResult.rows[0].id,
url: sizeImgResult.rows[0].url,
color_id: colorId,
size_id: sizeId
} as Image;
});
const insertedSizeImages = await Promise.all(sizeImagesInsertPromises);
insertedSizes[size.id].images.push(...insertedSizeImages.map(img => img.url));
insertedImages.push(...insertedSizeImages);
}
// Assign size to color
insertedColors[colorIndex].sizes.push(insertedSizes[size.id]);
}
}
}
}
// Handle standalone sizes (if has_size and no colors)
const standaloneSizes: Size[] = [];
if (
productData.has_size &&
(!productData.has_color || (productData.colors && productData.colors.length === 0)) &&
productData.sizes &&
productData.sizes.length > 0
) {
for (const [sizeIndex, size] of productData.sizes.entries()) {
// Adjust size price if necessary
let adjustedSizePrice = size.price;
if (size.price < 1000) {
adjustedSizePrice = parseFloat((size.price * 1000).toFixed(2));
}
// Insert size
const sizeResult = await query(
`INSERT INTO sizes (product_id, name, price, qty, sold)
VALUES ($1, $2, $3, $4, 0)
RETURNING id`,
[productId, size.name, adjustedSizePrice, size.qty]
);
const sizeId = sizeResult.rows[0].id;
const newSize: Size = {
id: sizeId,
name: size.name,
price: adjustedSizePrice,
qty: size.qty,
sold: 0,
color_id: null,
images: []
};
// Insert size images
if (size.images && size.images.length > 0) {
const sizeImagesInsertPromises = size.images.map(async (image: ImageInput) => {
const sizeImgResult = await query(
`INSERT INTO images (product_id, size_id, url)
VALUES ($1, $2, $3)
RETURNING id, url`,
[productId, sizeId, image.url]
);
return {
id: sizeImgResult.rows[0].id,
url: sizeImgResult.rows[0].url,
color_id: null,
size_id: sizeId
} as Image;
});
const insertedSizeImages = await Promise.all(sizeImagesInsertPromises);
newSize.images.push(...insertedSizeImages.map(img => img.url));
insertedImages.push(...insertedSizeImages);
}
standaloneSizes.push(newSize);
}
}
// Fetch category details
const categoriesResult = await query(
`SELECT sc.id AS subcategory_id, sc.name AS subcategory_name,
c.id AS main_category_id, c.name AS main_category_name
FROM product_categories pc
JOIN subcategories sc ON pc.category_id = sc.id
JOIN categories c ON sc.category_id = c.id
WHERE pc.product_id = $1`,
[productId]
);
const categories: Category[] = categoriesResult.rows.map((row: any) => ({
id: row.subcategory_id,
name: row.subcategory_name,
main_category_id: row.main_category_id,
main_category_name: row.main_category_name
}));
// Construct the new product object as it would appear in the cache
const newProduct: Product = {
id: productId,
name: productData.name,
description: productData.description,
brand_id: productData.brand_id,
has_color: productData.has_color,
has_size: productData.has_size,
product_type: productData.product_type,
price: adjustedPrice,
qty: productData.qty,
sold: 0,
created_at: productResult.rows[0].created_at.toISOString(),
updated_at: productResult.rows[0].updated_at.toISOString(),
categories: categories,
colors: Object.values(insertedColors),
sizes: standaloneSizes,
images: insertedImages.map(img => ({
id: img.id,
url: img.url,
color_id: img.color_id,
size_id: img.size_id
}))
};
// Update the cache
addProductToCache(newProduct);
await query('COMMIT'); // Commit transaction
res.status(201).json({ message: 'Product added successfully!', product: newProduct });
} catch (error) {
await query('ROLLBACK'); // Rollback transaction on error
console.error('Error creating product:', error);
res.status(500).json({ error: 'Internal server error' });
}
};
export const updateProduct = async (req: Request, res: Response) => {
const { id } = req.params;
const productId = parseInt(id, 10);
const productData: UpdateProductInput = req.body;
if (isNaN(productId)) {
return res.status(400).json({ error: 'Invalid product ID' });
}
const client = await query('BEGIN'); // Start transaction
try {
// Adjust price if necessary
let adjustedPrice = productData.price;
if (productData.price < 1000) {
adjustedPrice = parseFloat((productData.price * 1000).toFixed(2));
}
// Update product main fields
const existingProductResult = await query('SELECT sold FROM products WHERE id = $1', [productId]);
const existingSold = existingProductResult.rows[0]?.sold || 0;
const result = await query(
`UPDATE products
SET name = $1,
description = $2,
brand_id = $3,
has_color = $4,
has_size = $5,
product_type = $6,
price = $7,
qty = $8,
updated_at = CURRENT_TIMESTAMP
WHERE id = $9
RETURNING *`,
[
productData.name,
productData.description,
productData.brand_id,
Boolean(productData.has_color),
Boolean(productData.has_size),
productData.product_type,
adjustedPrice,
productData.qty,
productId
]
);
if (result.rows.length === 0) {
await query('ROLLBACK');
return res.status(404).json({ error: 'Product not found' });
}
// Update categories
await query('DELETE FROM product_categories WHERE product_id = $1', [productId]);
if (productData.category_ids && productData.category_ids.length > 0) {
const categoryInsertPromises = productData.category_ids.map((categoryId: number) =>
query(
`INSERT INTO product_categories (product_id, category_id)
VALUES ($1, $2)`,
[productId, categoryId]
)
);
await Promise.all(categoryInsertPromises);
}
// Update Images if provided
if (productData.images && productData.images.length > 0) {
// Optionally, you can handle adding, updating, and deleting specific images instead of deleting all
await query('DELETE FROM images WHERE product_id = $1', [productId]);
const imagesInsertPromises = productData.images.map((image: ImageInput) =>
query(
`INSERT INTO images (product_id, url, color_id, size_id)
VALUES ($1, $2, $3, $4)
RETURNING id, url, color_id, size_id`,
[productId, image.url, image.color_id || null, image.size_id || null]
)
);
await Promise.all(imagesInsertPromises);
}
// Update Colors and Sizes
if (productData.has_color) {
// Fetch existing colors
const existingColorsResult = await query(
'SELECT * FROM colors WHERE product_id = $1',
[productId]
);
const existingColors = existingColorsResult.rows;
// Iterate through incoming colors
for (const color of productData.colors) {
if (color.id) {
// Update existing color
await query(
`UPDATE colors
SET name = $1, price = $2, qty = $3, has_size = $4, updated_at = CURRENT_TIMESTAMP
WHERE id = $5 AND product_id = $6`,
[color.name, color.price, color.qty, Boolean(color.has_size), color.id, productId]
);
// Update color images if provided
if (color.images && color.images.length > 0) {
// Optionally handle image updates more granularly
await query(
`DELETE FROM images WHERE product_id = $1 AND color_id = $2`,
[productId, color.id]
);
const colorImagesInsertPromises = color.images.map(async (image: ImageInput) => {
const colorImgResult = await query(
`INSERT INTO images (product_id, color_id, url, size_id)
VALUES ($1, $2, $3, $4)
RETURNING id, url`,
[productId, color.id, image.url, image.size_id || null]
);
return {
id: colorImgResult.rows[0].id,
url: colorImgResult.rows[0].url,
color_id: color.id,
size_id: null
} as Image;
});
await Promise.all(colorImagesInsertPromises);
}
if (color.has_size) {
// Fetch existing sizes for this color
const existingSizesResult = await query(
'SELECT * FROM sizes WHERE color_id = $1',
[color.id]
);
const existingSizes = existingSizesResult.rows;
for (const size of color.sizes) {
if (size.id) {
// Update existing size
await query(
`UPDATE sizes
SET name = $1, price = $2, qty = $3, updated_at = CURRENT_TIMESTAMP
WHERE id = $4 AND product_id = $5`,
[size.name, size.price, size.qty, size.id, productId]
);
// Update size images if provided
if (size.images && size.images.length > 0) {
// Optionally handle image updates more granularly
await query(
`DELETE FROM images WHERE product_id = $1 AND size_id = $2`,
[productId, size.id]
);
const sizeImagesInsertPromises = size.images.map(async (image: ImageInput) => {
const sizeImgResult = await query(
`INSERT INTO images (product_id, size_id, url)
VALUES ($1, $2, $3)
RETURNING id, url`,
[productId, size.id, image.url]
);
return {
id: sizeImgResult.rows[0].id,
url: sizeImgResult.rows[0].url,
color_id: null,
size_id: size.id
} as Image;
});
await Promise.all(sizeImagesInsertPromises);
}
} else {
// Insert new size
const sizeResult = await query(
`INSERT INTO sizes (product_id, color_id, name, price, qty, sold)
VALUES ($1, $2, $3, $4, $5, 0)
RETURNING id`,
[productId, color.id, size.name, size.price, size.qty]
);
const newSizeId = sizeResult.rows[0].id;
// Insert size images
if (size.images && size.images.length > 0) {
const sizeImagesInsertPromises = size.images.map(async (image: ImageInput) => {
await query(
`INSERT INTO images (product_id, size_id, url)
VALUES ($1, $2, $3)`,
[productId, newSizeId, image.url]
);
});
await Promise.all(sizeImagesInsertPromises);
}
}
}
}
} else {
// Insert new color
const colorResult = await query(
`INSERT INTO colors (product_id, name, price, qty, sold, has_size)
VALUES ($1, $2, $3, $4, 0, $5)
RETURNING id`,
[productId, color.name, color.price, color.qty, Boolean(color.has_size)]
);
const newColorId = colorResult.rows[0].id;
// Insert color images if any
if (color.images && color.images.length > 0) {
const colorImagesInsertPromises = color.images.map(async (image: ImageInput) => {
await query(
`INSERT INTO images (product_id, color_id, url, size_id)
VALUES ($1, $2, $3, $4)`,
[productId, newColorId, image.url, image.size_id || null]
);
});
await Promise.all(colorImagesInsertPromises);
}
if (color.has_size && color.sizes && color.sizes.length > 0) {
for (const size of color.sizes) {
// Insert new size
const sizeResult = await query(
`INSERT INTO sizes (product_id, color_id, name, price, qty, sold)
VALUES ($1, $2, $3, $4, $5, 0)
RETURNING id`,
[productId, newColorId, size.name, size.price, size.qty]
);
const newSizeId = sizeResult.rows[0].id;
// Insert size images if any
if (size.images && size.images.length > 0) {
const sizeImagesInsertPromises = size.images.map(async (image: ImageInput) => {
await query(
`INSERT INTO images (product_id, size_id, url)
VALUES ($1, $2, $3)`,
[productId, newSizeId, image.url]
);
});
await Promise.all(sizeImagesInsertPromises);
}
}
}
}
}
} else if (productData.has_size) {
// Handle standalone sizes (no colors)
// Fetch existing standalone sizes
const existingSizesResult = await query(
'SELECT * FROM sizes WHERE product_id = $1 AND color_id IS NULL',
[productId]
);
const existingSizes = existingSizesResult.rows;
for (const size of productData.sizes) {
if (size.id) {
// Update existing size
await query(
`UPDATE sizes
SET name = $1, price = $2, qty = $3, updated_at = CURRENT_TIMESTAMP
WHERE id = $4 AND product_id = $5`,
[size.name, size.price, size.qty, size.id, productId]
);
// Update size images if provided
if (size.images && size.images.length > 0) {
// Optionally handle image updates more granularly
await query(
`DELETE FROM images WHERE product_id = $1 AND size_id = $2`,
[productId, size.id]
);
const sizeImagesInsertPromises = size.images.map(async (image: ImageInput) => {
await query(
`INSERT INTO images (product_id, size_id, url)
VALUES ($1, $2, $3)`,
[productId, size.id, image.url]
);
});
await Promise.all(sizeImagesInsertPromises);
}
} else {
// Insert new size
const sizeResult = await query(
`INSERT INTO sizes (product_id, name, price, qty, sold)
VALUES ($1, $2, $3, $4, 0)
RETURNING id`,
[productId, size.name, size.price, size.qty]
);
const newSizeId = sizeResult.rows[0].id;
// Insert size images if any
if (size.images && size.images.length > 0) {
const sizeImagesInsertPromises = size.images.map(async (image: ImageInput) => {
await query(
`INSERT INTO images (product_id, size_id, url)
VALUES ($1, $2, $3)`,
[productId, newSizeId, image.url]
);
});
await Promise.all(sizeImagesInsertPromises);
}
}
}
}
// Update the cache
const updatedProduct = await fetchProductByIdFromDb(productId);
if (updatedProduct) {
updateProductInCache(updatedProduct);
}
await query('COMMIT'); // Commit transaction
res.json({ message: 'Product updated successfully', product: updatedProduct });
} catch (error) {
await query('ROLLBACK'); // Rollback transaction on error
console.error('Error updating product:', error);
res.status(500).json({ error: 'Internal server error' });
}
};
export const getTopSellingProducts = async (req: Request, res: Response): Promise<void> => {
try {
// Retrieve cached products
let cacheData: CacheData | undefined = productCache.get('allProducts');
if (!cacheData) {
// Cache miss: Load from DB and cache
cacheData = await loadAndCacheAllProducts();
} else {
}
const { allProducts } = cacheData;
// Function to compute minimum price excluding 0
const getMinPrice = (product: Product): number | null => {
let prices: number[] = [];
// Include product base price if >0
if (product.price > 0) prices.push(product.price);
// Include colors' prices
if (product.has_color) {
product.colors.forEach(color => {
if (color.price > 0) prices.push(color.price);
// Include sizes' prices within colors
if (color.has_size) {
color.sizes.forEach(size => {
if (size.price > 0) prices.push(size.price);
});
}
});
}
// Include standalone sizes' prices
if (product.has_size) {
product.sizes.forEach(size => {
if (size.price > 0) prices.push(size.price);
});
}
if (prices.length === 0) return null;
return Math.min(...prices);
};
// Function to get one image URL (first available)
const getOneImage = (product: Product): string | null => {
if (product.images && product.images.length > 0) {
return product.images[0].url; // Assuming each image has a 'url' property
}
return null; // Or return a placeholder image URL if desired
};
// Filter products that are in stock and have a valid min price
const filteredProducts = allProducts
.map(product => {
// Determine if the product is in stock
const isInStock = product.qty > 0 ||
(product.has_color && product.colors.some(color => color.qty > 0 ||
(color.has_size && color.sizes.some(size => size.qty > 0)))) ||
(product.has_size && product.sizes.some(size => size.qty > 0));
// Calculate min price
const minPrice = getMinPrice(product);
// Get one image
const image = getOneImage(product);
return {
id: product.id,
title: product.name,
description: product.description,
sold: Math.abs(product.sold), // Ensure sold is absolute
isInStock,
minPrice,
image
};
})
.filter(product => product.isInStock && product.minPrice !== null && product.minPrice > 0);
if (filteredProducts.length === 0) {
res.status(200).json({ products: [] });
}
// Sort the products by sold in descending order using absolute sold values
filteredProducts.sort((a, b) => b.sold - a.sold);
// Select the top two products
const topTwoProducts = filteredProducts.slice(0, 2).map(product => ({
id: product.id,
title: product.title,
description: product.description,
minPrice: product.minPrice as number,
image: product.image
}));
res.status(200).json({ products: topTwoProducts });
} catch (error) {
console.error('Error fetching top selling products:', error);
res.status(500).json({ error: 'Internal server error' });
}
};
export const deleteProduct = async (req: Request, res: Response) => {
const { id } = req.params;
const productId: number = parseInt(id, 10);
if (isNaN(productId)) {
return res.status(400).json({ error: 'Invalid product ID' });
}
try {
await query('BEGIN');
// Delete related data first
await query('DELETE FROM product_categories WHERE product_id = $1', [productId]);
await query('DELETE FROM images WHERE product_id = $1', [productId]);
await query('DELETE FROM sizes WHERE product_id = $1', [productId]);
await query('DELETE FROM colors WHERE product_id = $1', [productId]);
// Delete the product
const result = await query('DELETE FROM products WHERE id = $1 RETURNING *', [productId]);
if (result.rows.length === 0) {
await query('ROLLBACK');
return res.status(404).json({ error: 'Product not found' });
}
// Delete the product from the cache
deleteProductFromCache(productId);
await query('COMMIT');
res.json({ message: 'Product and related data deleted successfully' });
} catch (error) {
await query('ROLLBACK');
console.error('Error deleting product:', error);
res.status(500).json({ error: 'Internal server error' });
}
};