<?php
require_once('includes/load.php');
page_require_level(3);

$month = isset($_GET['month']) ? $_GET['month'] : date('Y-m');

function get_sales_totals($month) {
    global $db;
    $sql = "SELECT 
                sales.part_number, 
                products.name, 
                categories.name AS category_name,
                COUNT(sales.part_number) AS resi_count, 
                SUM(sales.qty) AS total_quantity
            FROM 
                sales
            LEFT JOIN 
                products ON sales.part_number = products.part_number
            LEFT JOIN
                categories ON sales.categorie_id = categories.id
            WHERE 
                DATE_FORMAT(sales.date, '%Y-%m') = '{$month}'
                AND sales.qty IS NOT NULL
            GROUP BY 
                sales.part_number, products.name, categories.name
            ORDER BY 
                total_quantity DESC";
    return find_by_sql($sql);
}

$sales_totals = get_sales_totals($month);

require 'vendor/autoload.php'; // Include Composer's autoloader

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Sales Monthly Review');

// Set column headers
$sheet->setCellValue('A1', 'Part Number')
      ->setCellValue('B1', 'Product Name')
      ->setCellValue('C1', 'Category')
      ->setCellValue('D1', 'Total Quantity')
      ->setCellValue('E1', 'Resi Count');

// Populate data
$row = 2;
foreach ($sales_totals as $total) {
    $sheet->setCellValue('A' . $row, $total['part_number'])
          ->setCellValue('B' . $row, $total['name'])
          ->setCellValue('C' . $row, $total['category_name'])
          ->setCellValue('D' . $row, $total['total_quantity'])
          ->setCellValue('E' . $row, $total['resi_count']);
    $row++;
}

$writer = new Xlsx($spreadsheet);
$filename = 'sales_monthly_review_' . $month . '.xlsx';

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');

$writer->save('php://output');
exit;
?>
