<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

include "db.php";
require __DIR__ . '/vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

// Έλεγχος ID
$product_id = isset($_GET['id']) ? (int)$_GET['id'] : 0;
if($product_id <= 0) exit("Μη έγκυρο προϊόν");

// Πάρε όνομα προϊόντος
$stmt = $db->prepare("SELECT * FROM products WHERE id=?");
$stmt->bind_param("i",$product_id);
$stmt->execute();
$product = $stmt->get_result()->fetch_assoc();
if(!$product) exit("Προϊόν δεν βρέθηκε");

// Πάρε υλικά
$stmt2 = $db->prepare("
SELECT pi.quantity, u.name AS unit_name, i.name AS ingredient_name, COALESCE(ip.price,0) AS price
FROM product_ingredients pi
JOIN ingredients i ON pi.ingredient_id=i.id
JOIN units u ON pi.unit_id=u.id
LEFT JOIN ingredient_prices ip ON ip.id=(
    SELECT id FROM ingredient_prices WHERE ingredient_id=i.id ORDER BY date DESC LIMIT 1
)
WHERE pi.product_id=? ORDER BY i.name
");
$stmt2->bind_param("i",$product_id);
$stmt2->execute();
$ingredients = $stmt2->get_result()->fetch_all(MYSQLI_ASSOC);

// Δημιουργία Excel
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Headers
$headers = ["Υλικό","Ποσότητα","Τιμή (€)","Κόστος (€)"];
$sheet->fromArray($headers,NULL,'A1');
$sheet->getStyle('A1:D1')->applyFromArray([
    'font'=>['bold'=>true,'color'=>['rgb'=>'FFFFFF']],
    'fill'=>['fillType'=>Fill::FILL_SOLID,'startColor'=>['rgb'=>'4a90e2']],
    'alignment'=>['horizontal'=>Alignment::HORIZONTAL_CENTER],
    'borders'=>['allBorders'=>['borderStyle'=>Border::BORDER_THIN]]
]);

// Columns width
$sheet->getColumnDimension('A')->setWidth(30);
$sheet->getColumnDimension('B')->setWidth(15);
$sheet->getColumnDimension('C')->setWidth(15);
$sheet->getColumnDimension('D')->setWidth(15);

// Data
$rowNumber = 2;
$total_cost = 0;
foreach($ingredients as $ing){
    $sheet->setCellValue("A$rowNumber",$ing['ingredient_name']." (".$ing['unit_name'].")");
    $sheet->setCellValue("B$rowNumber",$ing['quantity']);
    $sheet->setCellValue("C$rowNumber",$ing['price']);
    $sheet->setCellValue("D$rowNumber",$ing['price']*$ing['quantity']);
    $total_cost += $ing['price']*$ing['quantity'];

    $fillColor = ($rowNumber%2==0)?'F2F2F2':'FFFFFF';
    $sheet->getStyle("A$rowNumber:D$rowNumber")->applyFromArray([
        'fill'=>['fillType'=>Fill::FILL_SOLID,'startColor'=>['rgb'=>$fillColor]],
        'borders'=>['allBorders'=>['borderStyle'=>Border::BORDER_THIN]],
        'alignment'=>['horizontal'=>Alignment::HORIZONTAL_CENTER]
    ]);

    $sheet->getStyle("C$rowNumber:D$rowNumber")->getNumberFormat()->setFormatCode('#,##0.00');
    $rowNumber++;
}

// Total
$sheet->setCellValue("C$rowNumber","ΣΥΝΟΛΙΚΟ");
$sheet->setCellValue("D$rowNumber",$total_cost);
$sheet->getStyle("A$rowNumber:D$rowNumber")->applyFromArray([
    'font'=>['bold'=>true],
    'borders'=>['allBorders'=>['borderStyle'=>Border::BORDER_THIN]],
    'alignment'=>['horizontal'=>Alignment::HORIZONTAL_CENTER],
    'fill'=>['fillType'=>Fill::FILL_SOLID,'startColor'=>['rgb'=>'FFE599']]
]);
$sheet->getStyle("D$rowNumber")->getNumberFormat()->setFormatCode('#,##0.00');
$sheet->freezePane('A2');

// Αποστολή αρχείου
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.htmlspecialchars($product['name']).'_Υλικά.xlsx"');
header('Cache-Control: max-age=0');

$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
exit;
