<?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 Dish
{
    protected $db;
    protected Helper $helper;
    private string $tableName = 'restaurant';

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

    public function getAllByMenuId(int $menu_id, int $restaurantId, string $selection = '*')
    {
        $stmt = $this->db->fetchAll("SELECT {$selection} FROM {$this->tableName}_dish WHERE menu_id = ? AND restaurant_id = ?", $menu_id, $restaurantId);
        return $stmt;
    }

    public function getAllMenuDishes(int $restaurantId): array
    {
        $stmt = $this->db->fetchAll("SELECT
        rm.*,
        COUNT(rd.dish_id) AS total_dish FROM {$this->tableName}_menu rm LEFT JOIN {$this->tableName}_dish rd ON rm.menu_id = rd.menu_id WHERE rm.restaurant_id = ? GROUP BY
        rm.menu_id ORDER BY rm.menu_sorting ASC ", $restaurantId);
        return $stmt;
    }

    public function getAllUncategorized(int $restaurantId, string $selection = '*')
    {
        $stmt = $this->db->fetchAll("SELECT {$selection} FROM {$this->tableName}_dish WHERE (restaurant_id = ? AND menu_id = 0) AND (is_reserved = 'false' OR is_reserved IS NULL)", $restaurantId);
        return $stmt;
    }

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

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

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

    public function getAvailabilityByDay(string $day, int $dishId, int $restaurantId)
    {
        $stmt = $this->db->fetch("SELECT * FROM {$this->tableName}_dish_days WHERE working_day = ? AND dish_id = ? AND restaurant_id = ?", $day, $dishId, $restaurantId);
        return $stmt;
    }

    public function getBySearching(string $searching, int $restaurantId, string $selection = '*')
    {
        $stmt = $this->db->fetchAll("SELECT {$selection} FROM {$this->tableName}_dish WHERE restaurant_id = ? AND dish_title LIKE ?", $restaurantId, "%{$searching}%");
        return $stmt;
    }

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

        return intval($dishId);
    }

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

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

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

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

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

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

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

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

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

    public function countAllByMenuId(int $menu_id, int $restaurantId): int
    {
        $stmt = $this->db->query("SELECT dish_id FROM {$this->tableName}_dish WHERE menu_id = ? AND restaurant_id = ?", $menu_id, $restaurantId);
        return $stmt->rowCount();
    }

    public function countUncategorized(int $restaurantId): int
    {
        $stmt = $this->db->query("SELECT dish_id AS count FROM {$this->tableName}_dish WHERE (restaurant_id = ? AND menu_id = 0) AND (is_reserved = 'false' || is_reserved IS NULL)", $restaurantId);
        return $stmt->rowCount();
    }

    public function isNameExist(string $dish_name, int $restaurant_id)
    {
        $stmt = $this->db->query("SELECT dish_id FROM {$this->tableName}_dish WHERE restaurant_id = ? AND dish_title = ? LIMIT 1", $restaurant_id, $dish_name);
        return $stmt->rowCount() > 0;
    }

    public function isNameUsed(int $dish_id, string $dish_name, int $restaurant_id)
    {
        $stmt = $this->db->query("SELECT dish_id FROM {$this->tableName}_dish WHERE restaurant_id = ? AND dish_title = ? AND dish_id != ? LIMIT 1", $restaurant_id, $dish_name, $dish_id);
        return $stmt->rowCount() > 0;
    }

    public function isIdUsed(int $dishId, int $restaurantId): bool
    {
        $stmt = $this->db->query("SELECT dish_id FROM {$this->tableName}_dish WHERE (dish_id = ? AND restaurant_id = ?) AND (is_reserved = 'true' AND dish_title !='' AND dish_image !='')", $dishId, $restaurantId);
        return $stmt->rowCount() > 0;
    }

    public function toggleMenuDishStatus(int $menuId, int $restaurantId, string $status): void
    {
        $this->db->query("UPDATE {$this->tableName}_dish SET dish_status = ? WHERE menu_id = ? AND restaurant_id = ?", $status, $menuId, $restaurantId);
    }

    public function toggleStatus(int $dishId, int $restaurantId, string $status): void
    {
        $this->db->query("UPDATE {$this->tableName}_dish SET dish_status = ? WHERE dish_id = ? AND restaurant_id = ?", $status, $dishId, $restaurantId);
    }

    public function releaseDishId(int $dishId, int $restaurantId): bool {
        $this->db->query("UPDATE {$this->tableName}_dish SET
        is_reserved = 'false',
        created_at = NOW()
        WHERE (dish_id = ? AND restaurant_id = ?) AND (dish_title !='' AND dish_image !='') ORDER BY reserved_at ASC LIMIT 1", $dishId, $restaurantId);
        return true;
    }

    public function generateId(int $restaurantId): array
    {
        // check if there is any available dish id that is reserved, then return the dish id that is reserved
        $dishId = $this->getReservedId($restaurantId);
        if($dishId != 0){
            return ['status' => true, 'message' => 'There is a reserved dish id', 'dish_id' => intval($dishId)];
        }

        $this->db->query("INSERT INTO {$this->tableName}_dish", [
            'dish_id' => NULL,
            'restaurant_id' => $restaurantId,
            'menu_id' => 0,
            'dish_title' => '',
            'dish_slug' => '',
            'dish_regular_price' => 0,
            'dish_description' => '',
            'dish_image' => '',
            'dish_status' => 'unavailable',
            'is_reserved' => 'true',
            'reserved_at' => DATENOW
        ]);

        $dishId = $this->db->getInsertId();
        return ['status' => true, 'message' => 'Dish ID generated successfully.', 'dish_id' => intval($dishId)];
    }

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

            $dishData = $this->getById($dishId, $restaurantId, 'menu_id, dish_sorting, dish_image');
            $dishSorting = $dishData->dish_sorting ?? 0;

            if(Helper::isFileExists($dishDefaultDirectory . '/dish/' . $dishData->dish_image)){
                unlink($dishDefaultDirectory . '/dish/' . $dishData->dish_image);
            }
            
            $webpName = $restaurant->getWebpName($dishData->dish_image);
            if(Helper::isFileExists($dishCompressedDirectory . '/dish/' . $webpName)){
                unlink($dishCompressedDirectory . '/dish/' . $webpName);
            }

            $database->query("DELETE FROM {$this->tableName}_dish WHERE dish_id = ? AND restaurant_id = ?", $dishId, $restaurantId);
            $database->query("UPDATE {$this->tableName}_dish SET dish_sorting = dish_sorting - 1 WHERE dish_sorting > ? AND (restaurant_id = ? AND menu_id = ?)", $dishSorting, $restaurantId, (int) $dishData->menu_id);
        });
    }

    public function bulkDelete(int $menuId, int $restaurant_id, string $restaurant_email): void
    {

        $dishArray = $this->getAllByMenuId($menuId, $restaurant_id, 'dish_id');
        foreach($dishArray as $val){
            $dishId = (int) $val->dish_id;

            $this->deleteDish($dishId, $restaurant_id, $restaurant_email);
        }
    }

    public function bulkDishStatus(int $restaurant_id, int $menuId, string $action)
    {
        $this->db->query("UPDATE {$this->tableName}_dish SET dish_status = ? WHERE menu_id = ? AND restaurant_id = ?", $action, $menuId, $restaurant_id);
    }
}
