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

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

	const { categoryId } = req.query;

	if (!categoryId || isNaN(categoryId)) {
		return res.status(400).json({ error: "Invalid category ID" });
	}

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

	try {
		// Găsește categoria părinte pentru a obține slug-ul
		const parentCategory = await db.Categories.findByPk(categoryId, {
			attributes: ["id", "slug"]
		});

		if (!parentCategory) {
			return res.status(404).json({ error: "Parent category not found" });
		}

		const subcategories = await db.Categories.findAll({
			where: { parentID: categoryId },
			include: [
				{
					model: db.Categories,
					as: "subChildren", 
					required: false,
					attributes: [
						"id", 
						"name", 
                              "name_en",
                              "name_it",
                              "name_fr",
                              "name_de",
						"slug", 
						"title",
						// Count pentru third level (subcategorySlug + thirdCategorySlug)
						[
							db.sequelize.literal(`(
								SELECT COUNT(*)
								FROM products p
								WHERE p.categorySlug = '${parentCategory.slug}'
								AND p.subcategorySlug = subChildren.slug
								AND p.thirdCategorySlug = subChildren.slug
							)`),
							'productCount'
						]
					],
				},
			],
			attributes: [
				"id", 
				"name", 
                    "name_en", 
                    "name_it",
                    "name_fr",
                    "name_de",
				"slug", 
				"title",
				// Count pentru second level (categorySlug + subcategorySlug)
				[
					db.sequelize.literal(`(
						SELECT COUNT(*)
						FROM products p
						WHERE p.categorySlug = '${parentCategory.slug}'
						AND p.subcategorySlug = categories.slug AND p.visible = 1
					)`),
					'productCount'
				]
			],
			order: [
				["id", "ASC"],
				[{ model: db.Categories, as: "subChildren" }, "id", "ASC"],
			],
			raw: false,
		});

		return res.status(200).json({
			children: subcategories,
			parentId: categoryId,
			total: subcategories.length,
		});
	} catch (error) {
		console.error(`Error fetching children for category ${categoryId}:`, error);
		return res.status(500).json({
			error: "Internal server error",
			status: 500,
		});
	}
}