<?php

ini_set('display_errors',1);
ini_set('display_startup_errors',1);
error_reporting(E_ALL);

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

$restaurant_id = $_SESSION['restaurant_id'];

/* ============================================================
   LOAD PHPSpreadsheet
============================================================ */
require_once __DIR__ . "/../vendor/autoload.php";
use PhpOffice\PhpSpreadsheet\IOFactory;

/* ============================================================
   Read Excel File
============================================================ */
$file = __DIR__ . "/excel_upload.xlsx";

if (!file_exists($file)) {
    die("No uploaded file found.");
}

$spreadsheet = IOFactory::load($file);
$sheet = $spreadsheet->getActiveSheet();
$rows = $sheet->toArray(null, true, true, true);

/* ============================================================
   Get Active Languages
============================================================ */
function langs($restaurant_id)
{
    $l = 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]);

    return $l ? array_column($l,'code') : ['ar'];
}

$langs = langs($restaurant_id);

/* ============================================================
   HELPERS
============================================================ */

function value($row, $col) {
    return isset($row[$col]) ? trim($row[$col]) : "";
}

function update_or_insert($table, $id, $fields, $values) {

    if ($id && fetch_one("SELECT id FROM $table WHERE id = ?", [$id])) {

        // BUILD UPDATE QUERY
        $set = [];
        foreach ($fields as $f) $set[] = "$f = ?";
        $sql = "UPDATE $table SET " . implode(",", $set) . " WHERE id = ?";
        $values[] = $id;

        db()->prepare($sql)->execute($values);
        return $id;

    } else {

        // BUILD INSERT QUERY
        $cols = implode(",", $fields);
        $place = rtrim(str_repeat("?,", count($fields)), ",");
        $sql = "INSERT INTO $table ($cols) VALUES ($place)";
        db()->prepare($sql)->execute($values);

        return db()->lastInsertId();
    }
}

/* ============================================================
   LOOP EXCEL ROWS
============================================================ */

foreach ($rows as $i => $row) {
    if ($i == 1) continue; // skip header

    $type = strtolower(value($row, "A"));

    /* ===========================================
       CATEGORY
    =========================================== */
    if ($type === "category") {

        $cat_id = value($row, "B");

        $data_fields = ["restaurant_id"];
        $data_values = [$restaurant_id];

        foreach ($langs as $l) {
            $col = strtoupper("C"); // AR
            if ($l == "en") $col = "D";
            if ($l == "ku") $col = "E";

            $data_fields[] = "name_$l";
            $data_values[] = value($row, $col);
        }

        // ordering ثابت مؤقتاً
        $data_fields[] = "ordering";
        $data_values[] = 9999;

        update_or_insert("categories", $cat_id, $data_fields, $data_values);
    }

    /* ===========================================
       ITEM
    =========================================== */
    if ($type === "item") {

        $item_id        = value($row, "F");
        $category_id    = value($row, "G");

        $fields = [
            "restaurant_id",
            "category_id",
            "base_price",
            "active",
            "ordering"
        ];

        $values = [
            $restaurant_id,
            $category_id,
            floatval(value($row, "N")),
            value($row, "O") ?: 1,
            value($row, "P") ?: 9999
        ];

        // names + desc by language
        foreach ($langs as $l) {
            if ($l == "ar") { $fields[]="name_ar"; $values[] = value($row,"H");
                              $fields[]="description_ar"; $values[] = value($row,"I"); }
            if ($l == "en") { $fields[]="name_en"; $values[] = value($row,"J");
                              $fields[]="description_en"; $values[] = value($row,"K"); }
            if ($l == "ku") { $fields[]="name_ku"; $values[] = value($row,"L");
                              $fields[]="description_ku"; $values[] = value($row,"M"); }
        }

        update_or_insert("items", $item_id, $fields, $values);
    }

    /* ===========================================
       OPTION
    =========================================== */
    if ($type === "option") {

        $opt_id     = value($row, "Q");
        $item_id    = value($row, "R");

        $fields = [
            "item_id",
            "price",
            "active",
            "ordering"
        ];

        $values = [
            $item_id,
            floatval(value($row,"Z")),
            value($row,"AA") ?: 1,
            value($row,"AB") ?: 9999
        ];

        // language names + descriptions
        foreach ($langs as $l) {
            if ($l == "ar") { $fields[]="name_ar"; $values[] = value($row,"S");
                              $fields[]="description_ar"; $values[] = value($row,"T"); }
            if ($l == "en") { $fields[]="name_en"; $values[] = value($row,"U");
                              $fields[]="description_en"; $values[] = value($row,"V"); }
            if ($l == "ku") { $fields[]="name_ku"; $values[] = value($row,"W");
                              $fields[]="description_ku"; $values[] = value($row,"X"); }
        }

        update_or_insert("item_options", $opt_id, $fields, $values);
    }
}

/* DONE */
echo "<h2>Import Completed Successfully</h2>";
echo "<a href='menu_builder.php'>رجوع</a>";
exit;

?>
