<?php
declare(strict_types=1);

namespace App\Models;

use App\Helpers\FileHandler;
use App\Lib\Database;
use App\Models\{UserSession, Restaurant};
use App\Util\{Helper, Locate};
use WebPConvert\WebPConvert;

class Customizations
{
    protected $db;
    protected Helper $helper;
    private string $tableName = 'restaurant_customized';

    public function __construct()
    {
        $this->db = Database::connectDB();
        $this->helper = new Helper;
    }

    public function isCategoryExist(int $restaurant_id, string $category_name)
    {
        $stmt = $this->db->query("SELECT category_id FROM {$this->tableName}_category WHERE restaurant_id = ? AND category_title = ? LIMIT 1", $restaurant_id, $category_name);
        return $stmt->rowCount() > 0;
    }

    public function isCategoryNameUsed(int $category_id, int $restaurant_id, string $category_name)
    {
        $stmt = $this->db->query("SELECT category_id FROM {$this->tableName}_category WHERE restaurant_id = ? AND category_title = ? AND category_id != ? LIMIT 1", $restaurant_id, $category_name, $category_id);
        return $stmt->rowCount() > 0;
    }

    public function getCategoryNextSorting(int $dish_id, int $restaurant_id)
    {
        $stmt = $this->db->fetch("SELECT IFNULL(MAX(category_sorting), 0) + 1 AS next_sorting FROM {$this->tableName}_category WHERE dish_id = ? AND restaurant_id = ? ", $dish_id, $restaurant_id);
        return $stmt;
    }

    public function getCategoryById(int $category_id, int $restaurant_id, string $selection = '*')
    {
        $stmt = $this->db->fetch("SELECT {$selection} FROM {$this->tableName}_category WHERE category_id = ? AND restaurant_id = ?", $category_id, $restaurant_id);
        return $stmt;
    }

    public function getAllCategory(int $restaurantId, string $selection = '*'): array
    {
        $stmt = $this->db->fetchAll("SELECT {$selection} FROM {$this->tableName}_category WHERE restaurant_id = ? ORDER BY category_sorting ASC", $restaurantId);
        return $stmt;
    }

    public function getAllCategoryByDishId(int $dishId, int $restaurantId, string $selection = '*'): array
    {
        $stmt = $this->db->fetchAll("SELECT {$selection} FROM {$this->tableName}_category WHERE dish_id = ? AND restaurant_id = ? ORDER BY category_sorting ASC", $dishId, $restaurantId);
        return $stmt;
    }

    public function getReservedItemId(int $restaurantId): int
    {
        $stmt = $this->db->query("SELECT item_id FROM {$this->tableName}_item WHERE is_reserved = 'true' AND restaurant_id = ? ", $restaurantId);
        $itemId = $stmt->fetch()->item_id ?? 0;

        return intval($itemId);
    }

    public function getAllItems(int $category_id, int $restaurantId, string $filterBy = 'all', $selection = '*')
    {
        if($filterBy === 'all'){
            $query = " AND item_id != '@' ";
        }

        if($filterBy === 'uncategorized'){
            $query = " AND category_id = '0' ";
        }

        if($filterBy === 'available'){
            $query = " AND item_status = 'available' ";
        }

        if($filterBy === 'unavailable'){
            $query = " AND item_status = 'unavailable' ";
        }

        if($filterBy === 'vegetarian'){
            $query = " AND item_type = 'vegan' ";
        }

        if($filterBy === 'non-vegetarian'){
            $query = " AND item_type = 'non_vegan' ";
        }

        if($filterBy === 'others' || $filterBy === 'mixed'){
            $query = " AND item_type = 'mixed' ";
        }

        $stmt = $this->db->fetchAll("SELECT {$selection} FROM {$this->tableName}_item WHERE (is_reserved = 'false' || is_reserved IS NULL) AND (restaurant_id = ? AND category_id = ?) {$query} ORDER BY item_sorting ASC", $restaurantId, $category_id);
        return $stmt;
    }

    public function isItemNameExist(string $itemName, int $restaurantId): bool
    {
        $stmt = $this->db->query("SELECT item_id FROM {$this->tableName}_item WHERE restaurant_id = ? AND item_title = ? LIMIT 1", $restaurantId, $itemName);
        return $stmt->rowCount() > 0;
    }

    public function isItemTitleUsed(string $title, int $itemId, int $restaurantId): bool
    {
        $stmt = $this->db->query("SELECT item_id FROM {$this->tableName}_item WHERE (restaurant_id = ? AND item_title = ?) AND item_id != ?  LIMIT 1", $restaurantId, $title, $itemId);
        return $stmt->rowCount() > 0;
    }

    public function getItemNextSorting(int $category_id, int $restaurant_id)
    {
        $stmt = $this->db->fetch("SELECT IFNULL(MAX(item_sorting), 0) + 1 AS next_sorting FROM {$this->tableName}_item WHERE (is_reserved = 'false' || is_reserved IS NULL) AND (restaurant_id = ? AND category_id = ?)", $restaurant_id, $category_id);
        return $stmt;
    }

    public function getItemById(int $itemId, int $restaurantId, string $selection = '*')
    {
        $stmt = $this->db->fetch("SELECT {$selection} FROM {$this->tableName}_item WHERE item_id = ? AND restaurant_id = ? LIMIT 1", $itemId, $restaurantId);
        return $stmt;
    }

    public function getItemBySlug(string $slug, int $restaurantId, string $selection = '*')
    {
        $stmt = $this->db->fetch("SELECT {$selection} FROM {$this->tableName}_item WHERE item_slug = ? AND restaurant_id = ? LIMIT 1", $slug, $restaurantId);
        return $stmt;
    }

    public function getAllItemAvailability(int $itemId, int $restaurantId, string $selection = '*')
    {
        $stmt = $this->db->query("SELECT {$selection} FROM {$this->tableName}_days WHERE item_id = ? AND restaurant_id = ?", $itemId, $restaurantId);
        return $stmt->fetchAll();
    }

    public function getItemsByCategoryId(int $categoryId, int $restaurantId, string $selection = '*')
    {
        $stmt = $this->db->query("SELECT {$selection} FROM {$this->tableName}_item WHERE category_id = ? AND restaurant_id = ?", $categoryId, $restaurantId);
        return $stmt->fetchAll();
    }

    public function getItemAvailabilityByDay(string $day, int $itemId, int $restaurantId)
    {
        $stmt = $this->db->fetch("SELECT * FROM {$this->tableName}_days WHERE working_day = ? AND item_id = ? AND restaurant_id = ?", $day, $itemId, $restaurantId);
        return $stmt;
    }

    public function toggleItemStatus(string $status, int $itemId, int $restaurantId): void
    {
        $this->db->query("UPDATE {$this->tableName}_item SET item_status = ? WHERE item_id = ? AND restaurant_id = ?", $status, $itemId, $restaurantId);
    }

    public function generateItemId(int $dish_id, int $restaurantId): array
    {
        // check if there is any available customized item id that is reserved, then return the item id that is reserved
        $itemId = $this->getReservedItemId($restaurantId);

        if(!empty($itemId) && $itemId != 0){
            return ['status' => true, 'message' => 'There is a reserved side item id', 'item_id' => intval($itemId)];
        }

        $this->db->query("INSERT INTO {$this->tableName}_item", [
            'item_id' => NULL,
            'dish_id' => $dish_id,
            'restaurant_id' => $restaurantId,
            'category_id' => 0,
            'item_title' => '',
            'item_slug' => '',
            'item_regular_price' => 0,
            'item_description' => '',
            'item_type' => 'mixed',
            'item_image' => '',
            'item_status' => 'unavailable',
            'is_reserved' => 'true',
            'reserved_at' => DATENOW
        ]);

        $customizedItemId = $this->db->getInsertId();
        return ['status' => true, 'message' => 'Customized item id generated successfully.', 'item_id' => intval($customizedItemId)];
    }

    public function deleteCategory(int $categoryId, int $restaurantId, string $restaurantEmail): void
    {
        $this->db->transaction(function ($database) use ($categoryId, $restaurantId, $restaurantEmail) {
            $restaurant = new Restaurant;
            $dishDefaultDirectory = $restaurant::$uploadDirectory . "/{$restaurantEmail}/" . $restaurant::$defaultImages . 'side-item/';
            $dishCompressedDirectory = $restaurant::$uploadDirectory . "/{$restaurantEmail}/" . $restaurant::$compressedImages . 'side-item/';

            $categoryData = $this->getCategoryById($categoryId, $restaurantId, 'dish_id, category_sorting');
            $categorySorting = $categoryData->category_sorting ?? 0;
            $dishId = $categoryData->dish_id;

            $database->query("DELETE FROM {$this->tableName}_category WHERE category_id = ? AND restaurant_id = ?", $categoryId, $restaurantId);
            $database->query("UPDATE {$this->tableName}_category SET category_sorting = category_sorting - 1 WHERE category_sorting > ? AND (dish_id = ? AND restaurant_id = ?)", $categorySorting, $dishId, $restaurantId);

            $categoryItems = $this->getItemsByCategoryId($categoryId, $restaurantId);
            // remove item images
            foreach($categoryItems as $val){
                $itemId = (int) $val->item_id;
                $itemImage = $val->item_image;

                if(Helper::isFileExists($dishDefaultDirectory . $itemImage)){
                    unlink($dishDefaultDirectory . $itemImage);
                }

                $webpName = $restaurant->getWebpName($itemImage);
                if(Helper::isFileExists($dishCompressedDirectory . $webpName)){
                    unlink($dishCompressedDirectory . $webpName);
                }
            }
            // remove item from database
            $database->query("DELETE FROM {$this->tableName}_item WHERE category_id = ? AND restaurant_id = ?", $categoryId, $restaurantId);
        });
    }

    public function deleteItemDish(int $itemId, int $restaurantId, string $restaurantEmail): void
    {
        $this->db->transaction(function ($database) use ($itemId, $restaurantId, $restaurantEmail) {
            $restaurant = new Restaurant;
            $dishDefaultDirectory = $restaurant::$uploadDirectory . "/{$restaurantEmail}/" . $restaurant::$defaultImages . 'side-item/';
            $dishCompressedDirectory = $restaurant::$uploadDirectory . "/{$restaurantEmail}/" . $restaurant::$compressedImages . 'side-item/';

            $itemData = $this->getItemById($itemId, $restaurantId, 'category_id, item_sorting, item_image');
            $itemSorting = $itemData->item_sorting ?? 0;

            if(Helper::isFileExists($dishDefaultDirectory . $itemData->item_image)){
                unlink($dishDefaultDirectory . $itemData->item_image);
            }

            $webpName = $restaurant->getWebpName($itemData->item_image);
            if(Helper::isFileExists($dishCompressedDirectory . $webpName)){
                unlink($dishCompressedDirectory . $webpName);
            }

            $database->query("DELETE FROM {$this->tableName}_item WHERE item_id = ? AND restaurant_id = ?", $itemId, $restaurantId);
            $database->query("UPDATE {$this->tableName}_item SET item_sorting = item_sorting - 1 WHERE item_sorting > ? AND (restaurant_id = ? AND category_id = ?)", $itemSorting, $restaurantId, (int) $itemData->category_id);
        });
    }
}
