<?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;

// ---------------- EXPORT TO EXCEL ----------------
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// ---------------- HEADERS ----------------
$headers = [
    "Κωδικός",
    "Όνομα Προϊόντος",
    "Κόστος (€)",
    "Markup %",
    "Τιμή Markup (€)",
    "Τιμή Markup + ΦΠΑ (€)",
    "Margin %",
    "Τιμή Margin (€)",
    "Τιμή Margin + ΦΠΑ (€)",
    "ΦΠΑ %"
];
$sheet->fromArray($headers, NULL, 'A1');

// Styling headers
$sheet->getStyle("A1:J1")->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]]
]);

// Widths
$sheet->getColumnDimension('A')->setWidth(15);
$sheet->getColumnDimension('B')->setWidth(40);
for ($col = 'C'; $col <= 'J'; $col++) {
    $sheet->getColumnDimension($col)->setWidth(15);
}

// ---------------- FETCH PRODUCTS ----------------
$res = $db->query("SELECT * FROM products WHERE catalog=1 ORDER BY name ASC");
$rowNumber = 2; // ξεκινάμε από τη δεύτερη σειρά (μετά τα headers)

while ($row = $res->fetch_assoc()) {

    // ---------------- ΥΠΟΛΟΓΙΣΜΟΣ ΚΟΣΤΟΥΣ ----------------
    $stmt = $db->prepare("
        SELECT pi.quantity, COALESCE(ip.price,0) AS price
        FROM product_ingredients pi
        LEFT JOIN ingredient_prices ip ON ip.id = (
            SELECT id FROM ingredient_prices 
            WHERE ingredient_id = pi.ingredient_id 
            ORDER BY date DESC LIMIT 1
        )
        WHERE pi.product_id = ?
    ");
    $stmt->bind_param("i", $row['id']);
    $stmt->execute();
    $res_cost = $stmt->get_result();

    $cost = 0;
    while($r = $res_cost->fetch_assoc()){
        $cost += $r['quantity'] * $r['price'];
    }
    $stmt->close();

    // Prices
    $markupPriceNoVAT = $cost * (1 + $row["markup"]/100);
    $markupPriceVAT = $markupPriceNoVAT * (1 + $row["vat"]/100);

    $marginPriceNoVAT = $cost / max(1 - $row["margin"]/100, 0.0001);
    $marginPriceVAT = $marginPriceNoVAT * (1 + $row["vat"]/100);

    // --------------- DATA ROW ----------------
    $data = [
        $row['product_code'],
        $row['name'],
        round($cost,2),
        $row['markup'],
        round($markupPriceNoVAT,2),
        round($markupPriceVAT,2),
        $row['margin'],
        round($marginPriceNoVAT,2),
        round($marginPriceVAT,2),
        $row['vat']
    ];

    $sheet->fromArray($data, NULL, 'A'.$rowNumber);

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

    // Number format for prices
    for ($col = 'C'; $col <= 'I'; $col++) {
        $sheet->getStyle("{$col}{$rowNumber}")->getNumberFormat()
            ->setFormatCode('#,##0.00');
    }

    $rowNumber++; // επόμενη γραμμή
}

// Freeze top row
$sheet->freezePane('A2');

// Αποστολή αρχείου
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="price_list.xlsx"');
header('Cache-Control: max-age=0');
header('Expires: 0');
header('Pragma: public');

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