<?php
require('config.php');

// Create a temporary directory for storing CSV files
$tempDir = sys_get_temp_dir() . "/categories_csv_" . uniqid();
mkdir($tempDir);

// Fetch unique product categories
$categoriesQuery = "SELECT DISTINCT productCategory FROM products";
$categoriesResult = $connect->query($categoriesQuery);

if ($categoriesResult->num_rows > 0) {
    while ($categoryRow = $categoriesResult->fetch_assoc()) {
        $category = $categoryRow['productCategory'];
        $csvFile = fopen("$tempDir/$category.csv", 'w');

        // Write the header row
        $header = ["quality", "type", "color", "size", "receipts", "issues", "closingStock"];
        fputcsv($csvFile, $header);

        // Fetch unique qualities for this category
        $qualitiesQuery = "SELECT DISTINCT productQuality FROM products WHERE productCategory = ?";
        $stmtQualities = $connect->prepare($qualitiesQuery);
        $stmtQualities->bind_param("s", $category);
        $stmtQualities->execute();
        $qualitiesResult = $stmtQualities->get_result();

        if ($qualitiesResult->num_rows > 0) {
            while ($qualityRow = $qualitiesResult->fetch_assoc()) {
                $quality = $qualityRow['productQuality'];

                // Fetch unique product types for this quality and category
                $typesQuery = "SELECT DISTINCT productType FROM products WHERE productCategory = ? AND productQuality = ?";
                $stmtTypes = $connect->prepare($typesQuery);
                $stmtTypes->bind_param("ss", $category, $quality);
                $stmtTypes->execute();
                $typesResult = $stmtTypes->get_result();

                if ($typesResult->num_rows > 0) {
                    while ($typeRow = $typesResult->fetch_assoc()) {
                        $type = $typeRow['productType'];

                        // Fetch unique product colors for this type, quality, and category
                        $colorsQuery = "SELECT DISTINCT productColor FROM products WHERE productCategory = ? AND productQuality = ? AND productType = ?";
                        $stmtColors = $connect->prepare($colorsQuery);
                        $stmtColors->bind_param("sss", $category, $quality, $type);
                        $stmtColors->execute();
                        $colorsResult = $stmtColors->get_result();

                        if ($colorsResult->num_rows > 0) {
                            while ($colorRow = $colorsResult->fetch_assoc()) {
                                $color = $colorRow['productColor'];

                                // Fetch sizes for this color, type, quality, and category
                                $sizesQuery = "SELECT id, size, quantity FROM store_stock 
                                                WHERE productID IN (SELECT id FROM products WHERE productCategory = ? AND productQuality = ? AND productType = ? AND productColor = ?)";
                                $stmtSizes = $connect->prepare($sizesQuery);
                                $stmtSizes->bind_param("ssss", $category, $quality, $type, $color);
                                $stmtSizes->execute();
                                $sizesResult = $stmtSizes->get_result();

                                if ($sizesResult->num_rows > 0) {
                                    while ($sizeRow = $sizesResult->fetch_assoc()) {
                                        $size = $sizeRow['size'];
                                        $closingStock = $sizeRow['quantity']; // Directly from store_stock.quantity

                                        // Fetch total issues for this stockID within the given timestamp range
                                        $issuesQuery = "SELECT SUM(quantity) AS totalIssues FROM releaseRecords 
                                                        WHERE stockID = ? AND _timestamp BETWEEN ? AND ?";
                                        $stmtIssues = $connect->prepare($issuesQuery);
                                        $stmtIssues->bind_param("iss", $sizeRow['id'], $_GET['timeStamp1'], $_GET['timeStamp2']);
                                        $stmtIssues->execute();
                                        $issuesResult = $stmtIssues->get_result();

                                        $issues = 0;
                                        if ($issuesResult->num_rows > 0) {
                                            $issuesRow = $issuesResult->fetch_assoc();
                                            $issues = $issuesRow['totalIssues'] ?: 0;
                                        }

                                        // Receipts are the sum of issues and closing stock
                                        $receipts = $issues + $closingStock;

                                        // Write the row to the CSV
                                        fputcsv($csvFile, [$quality, $type, $color, $size, $receipts, $issues, $closingStock]);
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        fclose($csvFile);
    }

    // Create a ZIP file containing all the CSV files
    $zipFile = tempnam(sys_get_temp_dir(), 'categories_csv') . ".zip";
    $zip = new ZipArchive;
    if ($zip->open($zipFile, ZipArchive::CREATE) === TRUE) {
        foreach (glob("$tempDir/*.csv") as $file) {
            $zip->addFile($file, basename($file));
        }
        $zip->close();

        // Serve the ZIP file for download
        header('Content-Type: application/zip');
        header('Content-Disposition: attachment; filename="categories_data.zip"');
        header('Content-Length: ' . filesize($zipFile));
        readfile($zipFile);

        // Cleanup temporary files
        array_map('unlink', glob("$tempDir/*"));
        rmdir($tempDir);
        unlink($zipFile);
    } else {
        echo json_encode(["error" => true, "message" => "Failed to create ZIP file."]);
    }
} else {
    echo json_encode(["error" => true, "message" => "No categories found."]);
}
?>
