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

// dump errors into a debug file
register_shutdown_function(function(){
    $out = ob_get_contents();
    if ($out && strlen(trim($out)) > 0) {
        file_put_contents(__DIR__."/debug_output.log", $out);
    }
});

require_once "includes/auth.php";
ensure_manager();
require_once "includes/functions.php";

require_once __DIR__ . "/../vendor/autoload.php";

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

/* =======================
   RESTAURANT & LANGUAGES
======================= */
$restaurant_id = $_SESSION['restaurant_id'];

$langs = fetch_all("
    SELECT l.code 
    FROM restaurant_languages rl
    JOIN languages l ON l.id = rl.language_id
    WHERE rl.restaurant_id = ? AND rl.active = 1
",[$restaurant_id]);

$langs = $langs ? array_column($langs,'code') : ['ar'];

/* =======================
   SPREADSHEET
======================= */
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle("Menu Template");
$spreadsheet->getDefaultStyle()->getFont()->setName('Arial')->setSize(12);

/* =======================
   HEADER COLUMNS
======================= */

$header = [
    "Row_Type",             // category | item | option
    "Category_ID"
];

// Category names
foreach($langs as $l){
    $header[] = "Category_Name_".strtoupper($l);
}

$header[] = "Item_ID";

// Item names + desc
foreach($langs as $l){
    $header[] = "Item_Name_".strtoupper($l);
    $header[] = "Item_Desc_".strtoupper($l);
}

$header[] = "Item_Price";
$header[] = "Item_Active";
$header[] = "Item_Ordering";

$header[] = "Option_ID";
$header[] = "Option_Price";
$header[] = "Option_Active";
$header[] = "Option_Ordering";

// Option names
foreach($langs as $l){
    $header[] = "Option_Name_".strtoupper($l);
    $header[] = "Option_Desc_".strtoupper($l);
}

/* Write header */
$col = 1;
foreach($header as $h){
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col)."1", $h);
    $sheet->getColumnDimensionByColumn($col)->setWidth(22);
    $col++;
}

/* =======================
   FETCH DATA
======================= */

// Categories
$categories = fetch_all("
    SELECT * FROM categories
    WHERE restaurant_id = ?
    ORDER BY ordering ASC
", [$restaurant_id]);

$row = 2;

foreach($categories as $cat){

    /* --------------------------------------
       1) Category ROW
    --------------------------------------- */
    $col = 1;

    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++).$row, "category");
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++).$row, $cat['id']);

    // Category names
    foreach($langs as $l){
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++).$row, $cat["name_$l"]);
    }

    // empty item fields
    $item_fields = 1 + (count($langs)*2) + 3; // item id + (name/desc) * langs + price+active+ordering
    for($i=0;$i<$item_fields;$i++){
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++).$row, "");
    }

    // empty options fields
    $option_fields = 4 + (count($langs)*2);
    for($i=0;$i<$option_fields;$i++){
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++).$row, "");
    }

    $row++;

    /* --------------------------------------
       2) ITEMS
    --------------------------------------- */

    $items = fetch_all("
        SELECT * FROM items
        WHERE restaurant_id = ? AND category_id = ?
        ORDER BY ordering ASC
    ", [$restaurant_id, $cat['id']]);

    foreach($items as $item){

        /* ITEM row ------------------------ */
        $col = 1;

        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++).$row, "item");
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++).$row, $cat['id']);

        // category names
        foreach($langs as $l){
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++).$row, $cat["name_$l"]);
        }

        // item id
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++).$row, $item['id']);

        // item names + desc
        foreach($langs as $l){
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++).$row, $item["name_$l"]);
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++).$row, $item["description_$l"]);
        }

        // price, active, ordering
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++).$row, $item['base_price']);
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++).$row, $item['active']);
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++).$row, $item['ordering']);

        // option fields empty
        $option_fields = 4 + (count($langs)*2);
        for($i=0;$i<$option_fields;$i++){
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++).$row, "");
        }

        $row++;

        /* OPTION rows ------------------------ */

        $options = fetch_all("
            SELECT * FROM item_options
            WHERE item_id = ?
            ORDER BY ordering ASC
        ", [$item['id']]);

        foreach($options as $op){

            $col = 1;

            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++).$row, "option");
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++).$row, $cat['id']);

            // category names
            foreach($langs as $l){
                $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++).$row, $cat["name_$l"]);
            }

            // item id again
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++).$row, $item['id']);

            // item fields empty
            $item_fields = (count($langs)*2) + 3;
            for($i=0;$i<$item_fields;$i++){
                $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++).$row, "");
            }

            // option id
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++).$row, $op['id']);
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++).$row, $op['price']);
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++).$row, $op['active']);
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++).$row, $op['ordering']);

            // option names
            foreach($langs as $l){
                $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++).$row, $op["name_$l"]);
                $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++).$row, $op["description_$l"]);
            }

            $row++;
        }
    }
}

/* =======================
   SEND FILE
======================= */

$restaurant_name = fetch_value("SELECT name FROM restaurants WHERE id = ?",[$restaurant_id]);
$filename = "menu_template_" . preg_replace("/[^A-Za-z0-9]/","_",$restaurant_name) . ".xlsx";

while(ob_get_level()) ob_end_clean();

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

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