Even though when type is color_size and colors has sizes, the sizes inside colors is empty...
for example the object in handle submit is like this:
{
  "name": "test",
  "description": "test",
  "brand_id": 73,
  "has_color": true,
  "has_size": false,
  "product_type": "color_size",
  "category_ids": [
    178
  ],
  "colors": [
    {
      "name": "red",
      "images": [],
      "has_size": true,
      "sizes": [
        {
          "name": "20ml",
          "price": 20,
          "qty": 20,
          "images": []
        }
      ],
      "price": null,
      "qty": 0
    }
  ],
  "sizes": [],
  "price": null,
  "qty": 0,
  "images": []
}
the result in the backend is:
{"id":1207,"name":"test","description":"test","brand_id":73,"has_color":true,"has_size":false,"product_type":"color_size","price":0,"qty":0,"sold":0,"created_at":"2024-09-24T11:58:58.539Z","updated_at":"2024-09-24T11:58:58.539Z","categories":[{"id":178,"name":"Body Cleansers","main_category_id":2,"main_category_name":"Body Care"}],"colors":[{"id":45,"name":"red","price":0,"qty":0,"sold":0,"has_size":false,"sizes":[],"images":[]}],"sizes":[{"id":10,"name":"20ml","price":20000,"qty":20,"sold":0,"color_id":45,"images":[]}],"images":[]},
frontend:
"use client";
import React, { useState, useEffect } from "react";
import { X, Plus } from "lucide-react";
import { MultipartFileUploader } from "@/components/MultipartFileUploader";
import { toast } from "react-toastify";
import { getToken } from "@/app/auth";

export default function AddProductDialog({ isOpen, onClose }) {
  const [product, setProduct] = useState({
    name: "",
    description: "",
    category: "",
    subcategory: "",
    brand: "",
    hasColor: false,
    colors: [],
    hasSize: false,
    sizes: [],
    categories: [],
    price: "",
    qty: "",
    images: [],
  });

  const [categories, setCategories] = useState([]);
  const [brands, setBrands] = useState([]);

  useEffect(() => {
    fetchCategories();
    fetchBrands();
  }, []);

  const fetchCategories = async () => {
    try {
      const token = getToken(); // Retrieve the token from localStorage

      if (!token) {
        toast.error("You must be logged in to add a product.");
        return;
      }
      const response = await fetch(
        "http://localhost:3000/subcategories/non-brands",
        {
          headers: {
            "Content-Type": "application/json",
            Authorization: `Bearer ${token}`, // Include the token here
          },
        },
      );
      const data = await response.json();
      setCategories(data);
    } catch (error) {
      console.error("Error fetching categories:", error);
    }
  };

  const fetchBrands = async () => {
    try {
      const token = getToken(); // Retrieve the token from localStorage

      if (!token) {
        toast.error("You must be logged in to add a product.");
        return;
      }
      const response = await fetch(
        "http://localhost:3000/subcategories/brands",
        {
          headers: {
            "Content-Type": "application/json",
            Authorization: `Bearer ${token}`, // Include the token here
          },
        },
      );
      const data = await response.json();
      setBrands(data);
    } catch (error) {
      console.error("Error fetching brands:", error);
    }
  };

  const handleCategoryToggle = (categoryId) => {
    setProduct((prevProduct) => {
      const updatedCategories = prevProduct.categories.includes(categoryId)
        ? prevProduct.categories.filter((id) => id !== categoryId)
        : [...prevProduct.categories, categoryId];
      return { ...prevProduct, categories: updatedCategories };
    });
  };

  const handleInputChange = (e) => {
    const { name, value } = e.target;
    setProduct((prevProduct) => ({
      ...prevProduct,
      [name]: value,
    }));
  };
  useEffect(() => {
    console.log("Updated product state:", product);
  }, [product]);
  const handleColorToggle = () => {
    setProduct({ ...product, hasColor: !product.hasColor, colors: [] });
  };

  const handleBrandChange = (e) => {
    setProduct({ ...product, brand: e.target.value });
  };

  const handleSizeToggle = () => {
    setProduct({
      ...product,
      hasSize: !product.hasSize,
      sizes: !product.hasSize ? [] : product.sizes,
    });
  };

  const handleAddColor = () => {
    setProduct((prevProduct) => {
      const newColor = {
        name: "",
        hasSize: false,
        sizes: [],
        price: "",
        qty: "",
        images: [],
        inheritImages: false,
      };

      return {
        ...prevProduct,
        colors: [...prevProduct.colors, newColor],
      };
    });
  };

  const handleColorChange = (index, field, value) => {
    setProduct((prevProduct) => {
      const newColors = [...prevProduct.colors];
      newColors[index] = {
        ...newColors[index],
        [field]: value,
      };
      return { ...prevProduct, colors: newColors };
    });
  };

  const handleColorSizeToggle = (colorIndex) => {
    const newColors = [...product.colors];
    newColors[colorIndex].hasSize = !newColors[colorIndex].hasSize;
    newColors[colorIndex].sizes = [];
    setProduct({ ...product, colors: newColors });
  };

  const handleAddSize = (colorIndex) => {
    setProduct((prevProduct) => {
      if (colorIndex === -1) {
        const newSize = {
          name: "",
          images: [],
          price: "",
          qty: "",
          inheritImages: false,
        };
        return {
          ...prevProduct,
          sizes: [...prevProduct.sizes, newSize],
        };
      } else {
        const newColors = [...prevProduct.colors];
        const newSize = {
          name: "",
          images: [],
          price: "",
          qty: "",
          inheritImages: false,
        };
        newColors[colorIndex] = {
          ...newColors[colorIndex],
          sizes: [...newColors[colorIndex].sizes, newSize],
        };
        return {
          ...prevProduct,
          colors: newColors,
        };
      }
    });
  };

  const handleSizeChange = (colorIndex, sizeIndex, field, value) => {
    setProduct((prevProduct) => {
      if (colorIndex === -1) {
        const newSizes = [...prevProduct.sizes];
        newSizes[sizeIndex] = {
          ...newSizes[sizeIndex],
          [field]: value,
        };
        return { ...prevProduct, sizes: newSizes };
      } else {
        const newColors = [...prevProduct.colors];
        newColors[colorIndex].sizes[sizeIndex] = {
          ...newColors[colorIndex].sizes[sizeIndex],
          [field]: value,
        };
        return { ...prevProduct, colors: newColors };
      }
    });
  };

  const handleImageUpload = (fileIds, variantType, colorIndex, sizeIndex) => {
    setProduct((prevProduct) => {
      let updatedProduct = { ...prevProduct };

      if (variantType === "color") {
        updatedProduct.colors[colorIndex].images = fileIds;
      } else if (variantType === "size") {
        if (colorIndex === -1) {
          updatedProduct.sizes[sizeIndex].images = fileIds;
        } else {
          updatedProduct.colors[colorIndex].sizes[sizeIndex].images = fileIds;
        }
      } else {
        updatedProduct.images = fileIds;
      }

      return updatedProduct;
    });
  };

  const handleInheritImagesChange = (variantType, colorIndex, sizeIndex) => {
    setProduct((prevProduct) => {
      let updatedProduct = JSON.parse(JSON.stringify(prevProduct)); // Deep clone

      if (variantType === "color") {
        updatedProduct.colors[colorIndex].inheritImages =
          !updatedProduct.colors[colorIndex].inheritImages;

        if (updatedProduct.colors[colorIndex].inheritImages && colorIndex > 0) {
          const prevColor = updatedProduct.colors[colorIndex - 1];
          updatedProduct.colors[colorIndex].images = [...prevColor.images];
        }
      } else if (variantType === "size") {
        if (colorIndex === -1) {
          updatedProduct.sizes[sizeIndex].inheritImages =
            !updatedProduct.sizes[sizeIndex].inheritImages;

          if (updatedProduct.sizes[sizeIndex].inheritImages && sizeIndex > 0) {
            const prevSize = updatedProduct.sizes[sizeIndex - 1];
            updatedProduct.sizes[sizeIndex].images = [...prevSize.images];
          }
        } else {
          updatedProduct.colors[colorIndex].sizes[sizeIndex].inheritImages =
            !updatedProduct.colors[colorIndex].sizes[sizeIndex].inheritImages;

          if (
            updatedProduct.colors[colorIndex].sizes[sizeIndex].inheritImages &&
            sizeIndex > 0
          ) {
            const prevSize =
              updatedProduct.colors[colorIndex].sizes[sizeIndex - 1];
            updatedProduct.colors[colorIndex].sizes[sizeIndex].images = [
              ...prevSize.images,
            ];
          }
        }
      }

      return updatedProduct;
    });
  };

  const handleSubmit = async (e) => {
    e.preventDefault();

    // Determine if any color has sizes
    const colorsHaveSizes =
      product.hasColor &&
      product.colors.some((color) => color.hasSize && color.sizes.length > 0);

    // Determine productType based on the presence of colors and sizes
    let productType;
    if (colorsHaveSizes) {
      productType = "color_size";
    } else if (product.hasColor) {
      productType = "color";
    } else if (product.hasSize) {
      productType = "size";
    } else {
      productType = "simple";
    }

    // Construct productData without using ||
    const productData = {
      name: product.name,
      description: product.description,
      brand_id: parseInt(product.brand, 10),
      has_color: product.hasColor,
      has_size: product.hasSize,
      product_type: productType,
      category_ids: product.categories,
      colors: product.colors.map((color) => ({
        name: color.name,
        images: color.images,
        has_size: color.hasSize,
        sizes: color.hasSize
          ? color.sizes.map((size) => ({
              name: size.name,
              price: parseFloat(size.price),
              qty: parseInt(size.qty, 10) || 0,
              images: size.images,
            }))
          : [],
        price: parseFloat(color.price),
        qty: parseInt(color.qty, 10) || 0,
      })),
      sizes: !product.hasColor
        ? product.sizes.map((size) => ({
            name: size.name,
            price: parseFloat(size.price),
            qty: parseInt(size.qty, 10) || 0,
            images: size.images,
          }))
        : [],
      price: parseFloat(product.price),
      qty: parseInt(product.qty, 10) || 0,
      images: product.images,
    };
    console.log(productData);
    try {
      const token = getToken(); // Retrieve the token from localStorage

      if (!token) {
        toast.error("You must be logged in to add a product.");
        return;
      }

      const response = await fetch("http://localhost:3000/products", {
        method: "POST",
        headers: {
          "Content-Type": "application/json",
          Authorization: `Bearer ${token}`, // Include the token here
        },
        body: JSON.stringify(productData),
      });

      if (!response.ok) {
        const errorData = await response.json();
        throw new Error(errorData.message || "Failed to create product");
      }

      // Reset the form after successful submission
      setProduct({
        name: "",
        description: "",
        category: "",
        subcategory: "",
        brand: "",
        hasColor: false,
        colors: [],
        hasSize: false,
        sizes: [],
        categories: [],
        price: "",
        qty: "",
        images: [],
      });

      toast.success("Product added successfully!");
      onClose();
    } catch (error) {
      console.error("Error creating product:", error);
      toast.error("Failed to add product. Please try again.");
    }
    try {
      const token = getToken(); // Retrieve the token from localStorage

      if (!token) {
        toast.error("You must be logged in to add a product.");
        return;
      }

      const response = await fetch("http://localhost:3000/products", {
        method: "POST",
        headers: {
          "Content-Type": "application/json",
          Authorization: `Bearer ${token}`, // Include the token here
        },
        body: JSON.stringify(productData),
      });

      if (!response.ok) {
        const errorData = await response.json();
        throw new Error(errorData.message || "Failed to create product");
      }

      setProduct({
        name: "",
        description: "",
        category: "",
        subcategory: "",
        brand: "",
        hasColor: false,
        colors: [],
        hasSize: false,
        sizes: [],
        variants: [],
        categories: [],
        price: "",
        qty: "",
        images: [],
      });

      toast.success("Product added successfully!");
      onClose();
    } catch (error) {
      console.error("Error creating product:", error);
      toast.error("Failed to add product. Please try again.");
    }
  };

  if (!isOpen) return null;

  return (
    <div className="fixed inset-0 z-[99999999] flex h-screen transform items-center justify-center overflow-y-scroll bg-black bg-opacity-50">
      <div className="relative h-screen w-full max-w-2xl overflow-y-scroll rounded-lg bg-white p-8">
        <button
          onClick={onClose}
          className="absolute right-4 top-4 text-gray-500 hover:text-gray-700"
        >
          <X size={24} />
        </button>

        <h2 className="mb-6 text-2xl font-bold">Add New Product</h2>

        <form onSubmit={handleSubmit} className="space-y-4">
          {/* Product Name */}
          <div>
            <label className="mb-2 block font-bold" htmlFor="name">
              Product Name
            </label>
            <input
              type="text"
              id="name"
              name="name"
              value={product.name}
              onChange={handleInputChange}
              className="w-full rounded-md border p-2"
              required
            />
          </div>

          {/* Description */}
          <div>
            <label className="mb-2 block font-bold" htmlFor="description">
              Description
            </label>
            <textarea
              id="description"
              name="description"
              value={product.description}
              onChange={handleInputChange}
              className="w-full rounded-md border p-2"
              style={{ direction: "rtl" }}
              required
            />
          </div>

          {/* Categories */}
          <div>
            <label className="mb-2 block font-bold">Categories</label>
            <div className="flex flex-wrap gap-2">
              {categories.map((category) => (
                <button
                  key={category.id}
                  type="button"
                  onClick={() => handleCategoryToggle(category.id)}
                  className={`rounded-full px-3 py-1 text-sm ${
                    product.categories.includes(category.id)
                      ? "bg-blue-500 text-white"
                      : "bg-gray-200 text-gray-700"
                  }`}
                >
                  {category.name}
                </button>
              ))}
            </div>
          </div>

          {/* Brand */}
          <div>
            <label className="mb-2 block font-bold" htmlFor="brand">
              Brand
            </label>
            <select
              id="brand"
              name="brand"
              value={product.brand}
              onChange={handleBrandChange}
              className="w-full rounded-md border p-2"
              required
            >
              <option value="">Select a brand</option>
              {brands.map((brand) => (
                <option key={brand.id} value={brand.id}>
                  {brand.name}
                </option>
              ))}
            </select>
          </div>

          {/* Has Color */}
          <div>
            <label className="flex items-center">
              <input
                type="checkbox"
                checked={product.hasColor}
                onChange={handleColorToggle}
                className="mr-2"
              />
              <span className="font-bold">Has Color?</span>
            </label>
          </div>

          {/* Colors */}
          {product.hasColor && (
            <div>
              <label className="mb-2 block font-bold">Colors</label>
              <div className="space-y-4">
                {product.colors.map((color, colorIndex) => (
                  <div key={colorIndex} className="rounded-md border p-4">
                    <input
                      type="text"
                      value={color.name}
                      onChange={(e) =>
                        handleColorChange(colorIndex, "name", e.target.value)
                      }
                      className="mb-2 rounded-md border p-2"
                      placeholder="Color name"
                    />

                    <label className="ml-2 flex items-center">
                      <input
                        type="checkbox"
                        checked={color.hasSize}
                        onChange={() => handleColorSizeToggle(colorIndex)}
                        className="mr-1"
                      />
                      <span>Has Size?</span>
                    </label>

                    {colorIndex > 0 && (
                      <label className="ml-2 flex items-center">
                        <input
                          type="checkbox"
                          checked={color.inheritImages}
                          onChange={() =>
                            handleInheritImagesChange("color", colorIndex)
                          }
                          className="mr-1"
                        />
                        <span>Inherit images</span>
                      </label>
                    )}

                    {color.hasSize ? (
                      <div className="mt-2 space-y-2">
                        {color.sizes.map((size, sizeIndex) => (
                          <div
                            key={sizeIndex}
                            className="space-y-2 rounded-md border p-2"
                          >
                            <input
                              type="text"
                              value={size.name}
                              onChange={(e) =>
                                handleSizeChange(
                                  colorIndex,
                                  sizeIndex,
                                  "name",
                                  e.target.value,
                                )
                              }
                              className="w-full rounded-md border p-2"
                              placeholder="Size name"
                            />
                            <div className="flex space-x-2">
                              <input
                                type="number"
                                value={size.price}
                                onChange={(e) =>
                                  handleSizeChange(
                                    colorIndex,
                                    sizeIndex,
                                    "price",
                                    e.target.value,
                                  )
                                }
                                className="w-1/2 rounded-md border p-2"
                                placeholder="Price"
                              />
                              <input
                                type="number"
                                value={size.qty}
                                onChange={(e) =>
                                  handleSizeChange(
                                    colorIndex,
                                    sizeIndex,
                                    "qty",
                                    e.target.value,
                                  )
                                }
                                className="w-1/2 rounded-md border p-2"
                                placeholder="Quantity"
                              />
                            </div>
                            {sizeIndex > 0 && (
                              <label className="flex items-center">
                                <input
                                  type="checkbox"
                                  checked={size.inheritImages}
                                  onChange={() =>
                                    handleInheritImagesChange(
                                      "size",
                                      colorIndex,
                                      sizeIndex,
                                    )
                                  }
                                  className="mr-1"
                                />
                                <span>Inherit images</span>
                              </label>
                            )}
                            {!size.inheritImages && (
                              <MultipartFileUploader
                                onUploadSuccess={(fileIds) =>
                                  handleImageUpload(
                                    fileIds,
                                    "size",
                                    colorIndex,
                                    sizeIndex,
                                  )
                                }
                              />
                            )}
                          </div>
                        ))}
                        <button
                          type="button"
                          onClick={() => handleAddSize(colorIndex)}
                          className="mt-2 rounded-md bg-blue-500 px-2 py-1 text-white hover:bg-blue-600"
                        >
                          Add Size
                        </button>
                      </div>
                    ) : (
                      <div className="mt-2 space-y-2">
                        <div className="flex space-x-2">
                          <input
                            type="number"
                            value={color.price}
                            onChange={(e) =>
                              handleColorChange(
                                colorIndex,
                                "price",
                                e.target.value,
                              )
                            }
                            className="w-1/2 rounded-md border p-2"
                            placeholder="Price"
                          />
                          <input
                            type="number"
                            value={color.qty}
                            onChange={(e) =>
                              handleColorChange(
                                colorIndex,
                                "qty",
                                e.target.value,
                              )
                            }
                            className="w-1/2 rounded-md border p-2"
                            placeholder="Quantity"
                          />
                        </div>
                        {!color.inheritImages && (
                          <MultipartFileUploader
                            onUploadSuccess={(fileIds) =>
                              handleImageUpload(fileIds, "color", colorIndex)
                            }
                          />
                        )}
                      </div>
                    )}
                  </div>
                ))}
              </div>
              <button
                type="button"
                onClick={handleAddColor}
                className="mt-2 rounded-md bg-blue-500 px-4 py-2 text-white hover:bg-blue-600"
              >
                <Plus className="mr-1 inline-block" size={16} />
                Add Color
              </button>
            </div>
          )}

          {/* Has Size (when no color) */}
          {!product.hasColor && (
            <div>
              <label className="flex items-center">
                <input
                  type="checkbox"
                  checked={product.hasSize}
                  onChange={handleSizeToggle}
                  className="mr-2"
                />
                <span className="font-bold">Has Size?</span>
              </label>
            </div>
          )}

          {/* Sizes (when no color) */}
          {!product.hasColor && product.hasSize && (
            <div>
              <label className="mb-2 block font-bold">Sizes</label>
              <div className="space-y-2">
                {product.sizes.map((size, index) => (
                  <div key={index} className="space-y-2 rounded-md border p-2">
                    <input
                      type="text"
                      value={size.name}
                      onChange={(e) =>
                        handleSizeChange(-1, index, "name", e.target.value)
                      }
                      className="w-full rounded-md border p-2"
                      placeholder="Size name"
                    />
                    <div className="flex space-x-2">
                      <input
                        type="number"
                        value={size.price}
                        onChange={(e) =>
                          handleSizeChange(-1, index, "price", e.target.value)
                        }
                        className="w-1/2 rounded-md border p-2"
                        placeholder="Price"
                      />
                      <input
                        type="number"
                        value={size.qty}
                        onChange={(e) =>
                          handleSizeChange(-1, index, "qty", e.target.value)
                        }
                        className="w-1/2 rounded-md border p-2"
                        placeholder="Quantity"
                      />
                    </div>
                    {index > 0 && (
                      <label className="flex items-center">
                        <input
                          type="checkbox"
                          checked={size.inheritImages}
                          onChange={() =>
                            handleInheritImagesChange("size", -1, index)
                          }
                          className="mr-1"
                        />
                        <span>Inherit images</span>
                      </label>
                    )}
                    {!size.inheritImages && (
                      <MultipartFileUploader
                        onUploadSuccess={(fileIds) =>
                          handleImageUpload(fileIds, "size", -1, index)
                        }
                      />
                    )}
                  </div>
                ))}
                <button
                  type="button"
                  onClick={() => handleAddSize(-1)}
                  className="rounded-md bg-blue-500 px-4 py-2 text-white hover:bg-blue-600"
                >
                  <Plus className="mr-1 inline-block" size={16} />
                  Add Size
                </button>
              </div>
            </div>
          )}

          {/* Product Details (when no color and no size) */}
          {!product.hasColor && !product.hasSize && (
            <div>
              <label className="mb-2 block font-bold">Product Details</label>
              <div className="flex items-center space-x-2">
                <input
                  type="number"
                  value={product.price}
                  onChange={(e) =>
                    handleInputChange({
                      target: { name: "price", value: e.target.value },
                    })
                  }
                  className="rounded-md border p-2"
                  placeholder="Price"
                />
                <input
                  type="number"
                  value={product.qty}
                  onChange={(e) =>
                    handleInputChange({
                      target: { name: "qty", value: e.target.value },
                    })
                  }
                  className="rounded-md border p-2"
                  placeholder="Quantity"
                />
              </div>
              <div className="mt-2">
                <MultipartFileUploader
                  onUploadSuccess={(fileIds) =>
                    handleImageUpload(fileIds, "product")
                  }
                />
              </div>
            </div>
          )}

          <div className="flex justify-end">
            <button
              type="submit"
              className="rounded-md bg-green-500 px-4 py-2 text-white hover:bg-green-600"
            >
              Add Product
            </button>
          </div>
        </form>
      </div>
    </div>
  );
}
backend:
import { Request, Response } from 'express';
import {query} from "../config/database";
import {brandsCache, productCache} from "../cache";
import {
    Brand,
    BrandsCacheData,
    CacheData, Color, ColorInput,
    CreateProductInput,
    Image,
    ImageInput,
    Product, Size, SizeInput,
    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 sizes s ON p.id = s.product_id
        LEFT JOIN colors cl ON p.id = cl.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> = {};

        result.rows.forEach((row: any) => {
            const productId = Number(row.id);
            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: [],
                    images: []
                };
            }

            // Add category if not already present
            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 = productsMap[productId].categories.find(cat => cat.id === categoryId);
                if (!existingCategory) {
                    productsMap[productId].categories.push({
                        id: categoryId,
                        name: row.subcategory_name,
                        main_category_id: mainCategoryId,
                        main_category_name: row.main_category_name,
                    });
                }
            }

            // Add color if not already present
            if (row.color_id && row.color_name && row.has_size !== undefined) {
                const colorId = Number(row.color_id);
                const existingColor = productsMap[productId].colors.find(color => color.id === colorId);
                if (!existingColor) {
                    productsMap[productId].colors.push({
                        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: [],
                        images: []
                    });
                }
            }

            // Add size if not already present
            if (row.size_id && row.size_name) {
                const sizeId = Number(row.size_id);
                const existingSize = productsMap[productId].sizes.find(size => size.id === sizeId);
                if (!existingSize) {
                    productsMap[productId].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: row.color_id ? Number(row.color_id) : null,
                        images: []
                    });
                }
            }

            // Add image if not already present
            if (row.image_id && row.image_url) {
                const imageId = Number(row.image_id);
                const existingImage = productsMap[productId].images.find(img => img.id === imageId);
                if (!existingImage) {
                    productsMap[productId].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 color = productsMap[productId].colors.find(c => c.id === Number(row.image_color_id));
                    if (color && !color.images.includes(row.image_url)) {
                        color.images.push(row.image_url);
                    }
                }

                // Associate image with size if applicable
                if (row.image_size_id) {
                    const size = productsMap[productId].sizes.find(s => s.id === Number(row.image_size_id));
                    if (size && !size.images.includes(row.image_url)) {
                        size.images.push(row.image_url);
                    }
                }
            }
        });

        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);
        console.log(`Cached ${allProducts.length} products with minPrice: ${minPrice} and maxPrice: ${maxPrice}.`);

        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);
        console.log(`Cached ${allBrands.length} brands successfully.`);

        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('cacheData');

        if (!cacheData) {
            // Cache miss: Load from DB and cache
            cacheData = await loadAndCacheAllProducts();
            console.log('Loaded products from database and cached them.');
        } else {
            console.log('Loaded products from cache.');
        }

        const { allProducts, minPrice, maxPrice } = cacheData;

        // Log total products before any filtering
        console.log(`Total products before filtering: ${allProducts.length}`);

        // 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();
                console.log('Loaded brands from database and cached them.');
            } else {
                console.log('Loaded brands from cache.');
            }
            console.log(brandsCacheData)
            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;
            }
        }

        // 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)) {
            console.log(`Applying Brand Filter: brand_id === ${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)
            );
            console.log(`Filtered from ${preFilterCount} to ${filteredProducts.length} products after applying brand filter.`);
        }

        // Apply Price Range Filter
        if (minPriceANum > 0 || maxPriceANum < Infinity) {
            filteredProducts = filteredProducts.filter(product => {
                const productPrices = product.sizes.map(size => size.price);
                // If product has no sizes, exclude it
                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
        console.log(`Total products after filtering: ${filteredProducts.length}`);

        // 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;
            }
        }

        // 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;
    try {
        const productQuery = `
            SELECT 
                p.id AS product_id,
                p.name AS product_name,
                p.description,
                p.brand_id,
                p.has_color,
                p.has_size,
                p.product_type,
                p.price AS product_price,
                p.qty AS product_qty,
                p.sold AS product_sold,
                p.created_at,
                p.updated_at,
                pc.category_id,
                sc.name AS 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 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
        `;

        const result = await query(productQuery, [id]);

        if (result.rows.length === 0) {
            return res.status(404).json({ error: 'Product not found' });
        }

        // Process the result to group related data
        let product: {
            sold: any;
            has_size: any;
            images: any[];
            description: any;
            created_at: any;
            colors: any[];
            brand_id: any;
            has_color: any;
            product_type: any;
            updated_at: any;
            sizes: any[];
            price: any;
            qty: any;
            name: any;
            id: number;
            categories: any[]
        };
        product = {
            id: result.rows[0].product_id,
            name: result.rows[0].product_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].product_price,
            qty: result.rows[0].product_qty,
            sold: result.rows[0].product_sold,
            created_at: result.rows[0].created_at,
            updated_at: result.rows[0].updated_at,
            categories: [],
            colors: [],
            sizes: [],
            images: []
        };

        for (const row of result.rows) {
            if (row.category_id && !product.categories.some(c => c.id === row.category_id)) {
                product.categories.push({
                    id: row.category_id,
                    name: row.category_name
                });
            }

            // Add color if not already added
            if (row.color_id && !product.colors.some(c => c.id === row.color_id)) {
                product.colors.push({
                    id: row.color_id,
                    name: row.color_name,
                    price: row.color_price,
                    qty: row.color_qty,
                    sold: row.color_sold,
                    images: [],
                    sizes: []
                });
            }

            // Add size if not already added
            if (row.size_id && !product.sizes.some(s => s.id === row.size_id)) {
                const newSize = {
                    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,
                    // @ts-ignore
                    images: []
                };
                product.sizes.push(newSize);

                // Add size to the corresponding color
                if (row.color_id) {
                    const color = product.colors.find(c => c.id === row.color_id);
                    if (color && !color.sizes.some((s: { id: any; }) => s.id === row.size_id)) {
                        color.sizes.push(newSize);
                    }
                }
            }

            if (row.image_id && !product.images.some(i => i.id === row.image_id)) {
                const newImage = {
                    id: row.image_id,
                    url: row.image_url,
                    color_id: row.color_id,
                    size_id: row.size_id
                };
                product.images.push(newImage);

                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);
                    }
                }
                if (row.size_id) {
                    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);
                    }
                }
            }
        }

        res.json({ product });
    } catch (error) {
        console.error('Error fetching product by ID:', error);
        res.status(500).json({ error: 'Internal server error' });
    }
};
// Create a new product
export const createProduct = async (req: Request, res: Response) => {
    try {
        await query('BEGIN');

        const productData: CreateProductInput = req.body;

        // Adjust price if necessary (ensure it's a string representing a number)
        // Adjust price if necessary (ensure it's a number)
        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,
                productData.has_color,
                productData.has_size,
                productData.product_type,
                adjustedPrice,
                productData.qty
            ]
        );
        const productId = productResult.rows[0].id;
        const createdAt = productResult.rows[0].created_at;
        const updatedAt = productResult.rows[0].updated_at;

        // Insert category associations
        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);

        // Insert images
        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, color_id, size_id, url`,
                [productId, image.url, image.color_id || null, image.size_id || null]
            )
        );
        const insertedImagesResults = await Promise.all(imagesInsertPromises);
        const insertedImages = insertedImagesResults.map((imgResult: any) => ({
            id: imgResult.rows[0].id,
            url: imgResult.rows[0].url,
            color_id: imgResult.rows[0].color_id,
            size_id: imgResult.rows[0].size_id
        }));

        // Handle colors and sizes based on product type
        const insertedColors: number[] = [];
        const insertedSizes: number[] = [];

        if (productData.has_color && productData.colors) {
            for (const color of productData.colors) {
                // Adjust color price if necessary
                // 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`,
                    [productId, color.name, adjustedColorPrice, color.qty, color.has_size]
                );
                const colorId = colorResult.rows[0].id;
                insertedColors.push(colorId);

                // Insert color images
                const colorImagesInsertPromises = color.images.map((image: ImageInput) =>
                    query(
                        `INSERT INTO images (product_id, color_id, url, size_id)
                         VALUES ($1, $2, $3, $4)
                         RETURNING id, color_id, size_id, url`,
                        [productId, colorId, image.url, image.size_id || null]
                    )
                );
                const insertedColorImagesResults = await Promise.all(colorImagesInsertPromises);
                // You can store or process inserted color images if needed

                if (color.has_size && color.sizes) {
                    for (const size of color.sizes) {
                        // Adjust size price if necessary
                        // 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.push(sizeId);

                        // Insert size images
                        const sizeImagesInsertPromises = size.images.map((image: ImageInput) =>
                            query(
                                `INSERT INTO images (product_id, color_id, size_id, url)
                                 VALUES ($1, $2, $3, $4)
                                 RETURNING id, color_id, size_id, url`,
                                [productId, colorId, sizeId, image.url]
                            )
                        );
                        const insertedSizeImagesResults = await Promise.all(sizeImagesInsertPromises);
                        // You can store or process inserted size images if needed
                    }
                }
            }
        } else if (productData.has_size && productData.sizes) {
            for (const size of productData.sizes) {
                // Adjust size price if necessary
                // 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;
                insertedSizes.push(sizeId);

                // Insert size images
                const sizeImagesInsertPromises = size.images.map((image: ImageInput) =>
                    query(
                        `INSERT INTO images (product_id, size_id, url)
                         VALUES ($1, $2, $3)
                         RETURNING id, color_id, size_id, url`,
                        [productId, sizeId, image.url]
                    )
                );
                const insertedSizeImagesResults = await Promise.all(sizeImagesInsertPromises);
                // You can store or process inserted size images if needed
            }
        }

        // 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: createdAt.toISOString(),
            updated_at: updatedAt.toISOString(),
            categories: productData.category_ids.map((cid: number) => ({
                id: cid,
                name: "", // Optionally fetch and populate category names
                main_category_id: 0, // Set appropriately
                main_category_name: "" // Set appropriately
            })),
            colors: productData.has_color && productData.colors ? productData.colors.map((color, index) => ({
                id: insertedColors[index],
                name: color.name,
                price: color.price,
                qty: color.qty,
                sold: 0,
                has_size: color.has_size,
                sizes: color.has_size && color.sizes ? color.sizes.map((size, sIndex) => ({
                    id: insertedSizes[sIndex],
                    name: size.name,
                    price: size.price,
                    qty: size.qty,
                    sold: 0,
                    color_id: null, // Optional: Set if needed
                    images: size.images.map(img => img.url)
                })) : [],
                images: color.images.map(img => img.url)
            })) : [],
            sizes: productData.has_size && !productData.has_color && productData.sizes ? productData.sizes.map((size, index) => ({
                id: insertedSizes[index],
                name: size.name,
                price: size.price,
                qty: size.qty,
                sold: 0,
                color_id: null,
                images: size.images.map(img => img.url)
            })) : [],
            images: insertedImages
        };

        // Update the cache
        addProductToCache(newProduct);

        await query('COMMIT');
        res.status(201).json({ message: 'Product created successfully', productId });
    } catch (error) {
        await query('ROLLBACK');
        console.error('Error creating product:', error);
        res.status(500).json({ error: 'Internal server error' });
    }
};
/**
 * Update an existing product
 */
// src/controllers/productController.ts

export const updateProduct = async (req: Request, res: Response) => {
    const { id } = req.params;
    const productData: UpdateProductInput = req.body;
    const productId: number = parseInt(id, 10);

    if (isNaN(productId)) {
        return res.status(400).json({ error: 'Invalid product ID' });
    }

    try {
        await query('BEGIN');

        // Adjust product price if necessary
        let adjustedProductPrice: number = productData.price;
        if (adjustedProductPrice < 1000) {
            adjustedProductPrice = parseFloat((adjustedProductPrice * 1000).toFixed(2));
        }

        // Update product details
        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,
                productData.has_color,
                productData.has_size,
                productData.product_type,
                adjustedProductPrice,
                productData.qty,
                productId
            ]
        );

        if (result.rows.length === 0) {
            await query('ROLLBACK');
            return res.status(404).json({ error: 'Product not found' });
        }

        // Delete existing associations and data
        await query('DELETE FROM product_categories WHERE product_id = $1', [productId]);
        await query('DELETE FROM images WHERE product_id = $1', [productId]);

        // Get existing colors and sizes
        const existingColorsResult = await query('SELECT id FROM colors WHERE product_id = $1', [productId]);
        const existingColorIds = new Set(existingColorsResult.rows.map((row: any) => row.id));

        const existingSizesResult = await query('SELECT id FROM sizes WHERE product_id = $1', [productId]);
        const existingSizeIds = new Set(existingSizesResult.rows.map((row: any) => row.id));

        // Determine colors and sizes to delete
        const updatedColorIds = new Set(
            productData.colors
                ?.map((color: ColorInput) => color.id)
                .filter((id: number | undefined): id is number => typeof id === 'number') || []
        );
        const colorsToDelete = [...existingColorIds].filter(id => !updatedColorIds.has(id));

        const updatedSizeIds = new Set([
            ...(productData.sizes
                ?.map((size: SizeInput) => size.id)
                .filter((id: number | undefined): id is number => typeof id === 'number') || []),
            ...(productData.colors?.flatMap((color: ColorInput) =>
                color.sizes.map((size: SizeInput) => size.id).filter((id: number | undefined): id is number => typeof id === 'number')
            ) || [])
        ]);
        const sizesToDelete = [...existingSizeIds].filter(id => !updatedSizeIds.has(id));

        // Delete colors and associated sizes and images
        for (const colorId of colorsToDelete) {
            await query('DELETE FROM sizes WHERE color_id = $1', [colorId]);
            await query('DELETE FROM images WHERE color_id = $1', [colorId]);
            await query('DELETE FROM colors WHERE id = $1', [colorId]);
        }

        // Delete sizes and associated images
        for (const sizeId of sizesToDelete) {
            await query('DELETE FROM images WHERE size_id = $1', [sizeId]);
            await query('DELETE FROM sizes WHERE id = $1', [sizeId]);
        }

        // Insert category associations
        if (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);
        }

        // Insert images
        let insertedImages: Image[] = [];
        if (productData.images.length > 0) {
            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]
                )
            );
            const insertedImagesResults = await Promise.all(imagesInsertPromises);
            insertedImages = insertedImagesResults.map((imgResult: any) => ({
                id: imgResult.rows[0].id,
                url: imgResult.rows[0].url,
                color_id: imgResult.rows[0].color_id,
                size_id: imgResult.rows[0].size_id
            }));
        }

        // Insert colors and sizes
        let insertedColors: number[] = [];
        let insertedSizes: number[] = [];
        if (productData.has_color && productData.colors && productData.colors.length > 0) {
            for (const colorInput of productData.colors) {
                // Adjust color price if necessary
                let adjustedColorPrice: number = colorInput.price;
                if (adjustedColorPrice < 1000) {
                    adjustedColorPrice = parseFloat((adjustedColorPrice * 1000).toFixed(2));
                }

                // Insert or Update color
                let colorId: number;
                if (colorInput.id) {
                    // Update existing color
                    await query(
                        `
                        UPDATE colors 
                        SET 
                            name = $1, 
                            price = $2, 
                            qty = $3, 
                            sold = $4, 
                            has_size = $5 
                        WHERE id = $6
                        `,
                        [
                            colorInput.name,
                            adjustedColorPrice,
                            colorInput.qty,
                            colorInput.sold || 0,
                            colorInput.has_size,
                            colorInput.id
                        ]
                    );
                    colorId = colorInput.id;
                } 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, colorInput.name, adjustedColorPrice, colorInput.qty, colorInput.has_size]
                    );
                    colorId = colorResult.rows[0].id;
                    insertedColors.push(colorId);
                }

                // Insert color images
                if (colorInput.images.length > 0) {
                    const colorImagesInsertPromises = colorInput.images.map((image: ImageInput) =>
                        query(
                            `
                            INSERT INTO images 
                                (product_id, color_id, url, size_id)
                            VALUES 
                                ($1, $2, $3, $4)
                            RETURNING id, url, color_id, size_id
                            `,
                            [productId, colorId, image.url, image.size_id || null]
                        )
                    );
                    await Promise.all(colorImagesInsertPromises);
                }

                // Insert sizes within color
                if (colorInput.has_size && colorInput.sizes && colorInput.sizes.length > 0) {
                    for (const sizeInput of colorInput.sizes) {
                        // Adjust size price if necessary
                        let adjustedSizePrice: number = sizeInput.price;
                        if (adjustedSizePrice < 1000) {
                            adjustedSizePrice = parseFloat((adjustedSizePrice * 1000).toFixed(2));
                        }

                        // Insert or Update size
                        let sizeId: number;
                        if (sizeInput.id) {
                            // Update existing size
                            await query(
                                `
                                UPDATE sizes 
                                SET 
                                    name = $1, 
                                    price = $2, 
                                    qty = $3, 
                                    sold = $4, 
                                    color_id = $5 
                                WHERE id = $6
                                `,
                                [
                                    sizeInput.name,
                                    adjustedSizePrice,
                                    sizeInput.qty,
                                    sizeInput.sold || 0,
                                    colorId,
                                    sizeInput.id
                                ]
                            );
                            sizeId = sizeInput.id;
                        } 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, colorId, sizeInput.name, adjustedSizePrice, sizeInput.qty]
                            );
                            sizeId = sizeResult.rows[0].id;
                            insertedSizes.push(sizeId);
                        }

                        // Insert size images
                        if (sizeInput.images.length > 0) {
                            const sizeImagesInsertPromises = sizeInput.images.map((image: ImageInput) =>
                                query(
                                    `
                                    INSERT INTO images 
                                        (product_id, color_id, size_id, url)
                                    VALUES 
                                        ($1, $2, $3, $4)
                                    RETURNING id, url, color_id, size_id
                                    `,
                                    [productId, colorId, sizeId, image.url]
                                )
                            );
                            await Promise.all(sizeImagesInsertPromises);
                        }
                    }
                }
            }
        } else if (productData.has_size && productData.sizes && productData.sizes.length > 0) {
            for (const sizeInput of productData.sizes) {
                // Adjust size price if necessary
                let adjustedSizePrice: number = sizeInput.price;
                if (adjustedSizePrice < 1000) {
                    adjustedSizePrice = parseFloat((adjustedSizePrice * 1000).toFixed(2));
                }

                // Insert or Update size
                let sizeId: number;
                if (sizeInput.id) {
                    // Update existing size
                    await query(
                        `
                        UPDATE sizes 
                        SET 
                            name = $1, 
                            price = $2, 
                            qty = $3, 
                            sold = $4 
                        WHERE id = $5
                        `,
                        [
                            sizeInput.name,
                            adjustedSizePrice,
                            sizeInput.qty,
                            sizeInput.sold || 0,
                            sizeInput.id
                        ]
                    );
                    sizeId = sizeInput.id;
                } 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, sizeInput.name, adjustedSizePrice, sizeInput.qty]
                    );
                    sizeId = sizeResult.rows[0].id;
                    insertedSizes.push(sizeId);
                }

                // Insert size images
                if (sizeInput.images.length > 0) {
                    const sizeImagesInsertPromises = sizeInput.images.map((image: ImageInput) =>
                        query(
                            `
                            INSERT INTO images 
                                (product_id, size_id, url)
                            VALUES 
                                ($1, $2, $3)
                            RETURNING id, url, color_id, size_id
                            `,
                            [productId, sizeId, image.url]
                        )
                    );
                    await Promise.all(sizeImagesInsertPromises);
                }
            }
        }

        // Fetch and populate categories with details
        const categories = await fetchCategoryDetails(productData.category_ids);

        // Construct the updated product object as it would appear in the cache
        const updatedProduct: 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: adjustedProductPrice,
            qty: productData.qty,
            sold: 0, // Assuming sold remains unchanged; adjust if necessary
            created_at: result.rows[0].created_at.toISOString(),
            updated_at: new Date().toISOString(),
            categories: categories,
            colors: productData.has_color && productData.colors
                ? productData.colors.map((colorInput, index) => ({
                    id: colorInput.id || insertedColors[index],
                    name: colorInput.name,
                    price: colorInput.price,
                    qty: colorInput.qty,
                    sold: colorInput.sold || 0,
                    has_size: colorInput.has_size,
                    sizes: colorInput.has_size && colorInput.sizes
                        ? colorInput.sizes.map((sizeInput, sIndex) => ({
                            id: sizeInput.id || insertedSizes[sIndex],
                            name: sizeInput.name,
                            price: sizeInput.price,
                            qty: sizeInput.qty,
                            sold: sizeInput.sold || 0,
                            color_id: colorInput.id || insertedColors[index],
                            images: sizeInput.images.map(img => img.url)
                        }))
                        : [],
                    images: colorInput.images.map(img => img.url)
                }))
                : [],
            sizes: productData.has_size && !productData.has_color && productData.sizes
                ? productData.sizes.map((sizeInput, index) => ({
                    id: sizeInput.id || insertedSizes[index],
                    name: sizeInput.name,
                    price: sizeInput.price,
                    qty: sizeInput.qty,
                    sold: sizeInput.sold || 0,
                    color_id: null,
                    images: sizeInput.images.map(img => img.url)
                }))
                : [],
            images: insertedImages
        };

        // Update the cache
        updateProductInCache(updatedProduct);

        await query('COMMIT');
        res.json({ message: 'Product updated successfully', product: updatedProduct });
    } catch (error) {
        await query('ROLLBACK');
        console.error('Error updating product:', 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' });
    }
};