import { db } from "@/helpers/api";

export default async function handler(req, res) {
	if (req.method !== "GET") {
		return res.status(405).json({ error: "Method not allowed" });
	}

	try {
		if (!db.initialized) await db.initialize();

		const query = `
      SELECT 
        c.id as category_id,
        c.name as category_name,
        c.name_en as category_name_en, 
        c.name_fr as category_name_fr, 
        c.name_it as category_name_it, 
        c.name_de as category_name_de, 
        c.slug as category_slug,
        c.parentID,
        c.title as category_title,
        c.description as category_description,
        c.keywords as category_keywords,
        p.id as product_id,
        p.name as product_name,
        p.name_en as product_name_en, 
        p.name_it as product_name_it, 
        p.name_fr as product_name_fr, 
        p.name_de as product_name_de, 
        p.url as product_url,
        p.price,
        p.stock,
        p.priceReduced,
        p.images,
        ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY p.id DESC) as product_rank
      FROM categories c
      LEFT JOIN products p ON p.categorySlug = c.slug
      WHERE c.parentID = 0 AND p.visible = 1 
      ORDER BY c.id, p.id DESC
     
    `;

		const results = await db.sequelize.query(query, {
			type: db.Sequelize.QueryTypes.SELECT,
		});

		const categoriesMap = new Map();

		results.forEach((row) => {
			const categoryId = row.category_id;

			if (!categoriesMap.has(categoryId)) {
				categoriesMap.set(categoryId, {
					id: row.category_id,
					name: row.category_name,
                         name_en: row.category_name_en, 
                         name_it: row.category_name_it,
                         name_fr: row.category_name_fr,
                         name_de: row.category_name_de,
					slug: row.category_slug,
					parentID: row.parentID,
					title: row.category_title,
					description: row.category_description,
					keywords: row.category_keywords,
					products: [],
				});
			}

			if (row.product_name && row.product_rank <= 12) {
				categoriesMap.get(categoryId).products.push({
                         id: parseInt(row.product_id),
					name: row.product_name,
                         name_en: row.product_name_en, 
                         name_it: row.product_name_it,
                         name_fr: row.product_name_fr,
                         name_de: row.product_name_de,
					url: row.product_url,
					price: row.price,
                         stock: row.stock, 
					priceReduced: row.priceReduced,
					images: row.images,
				});
			}
		});

		return res.status(200).json(Array.from(categoriesMap.values()));
	} catch (error) {
		console.error("API error [categories]:", error);
		return res.status(500).json({ error: "Internal server error" });
	}
}
