<?php
declare(strict_types=1);

namespace App\Models;

use App\Lib\Database;
use App\Util\{Helper};

class Orders
{
    protected $db;
    protected Helper $helper;
    private string $tableName = 'orders';
    private array $orderType = ['delivery' => 'DEL', 'takeaway' => 'TKW', 'dine-in' => 'DIN'];

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

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

    public function getByReference(string|int $reference, string $selection = '*')
    {
        $stmt = $this->db->fetch("SELECT {$selection} FROM {$this->tableName} WHERE reference = ? LIMIT 1", $reference);
        return $stmt;
    }

    public function getDataByOrderId(int $order_id, string $selection = '*')
    {
        $stmt = $this->db->fetch("SELECT {$selection} FROM {$this->tableName}_data WHERE order_id = ? LIMIT 1", $order_id);
        return $stmt;
    }

    public function getRequest(int $orderId)
    {
        $stmt = $this->db->fetch("SELECT * FROM {$this->tableName}_request WHERE order_id = ? LIMIT 1", $orderId);
        return $stmt;
    }

    public function getRider(int $orderId)
    {
        $stmt = $this->db->fetch("SELECT * FROM {$this->tableName}_rider WHERE order_id = ? LIMIT 1", $orderId);
        return $stmt;
    }

    public function getReferenceCode(string $order_type)
    {
        return $this->orderType[$order_type];
    }

    public function updateStatus(int $order_id, string $status): void
    {
        $this->db->query("UPDATE {$this->tableName} SET order_status = ? WHERE order_id = ?", $status, $order_id);
    }

    public function updateColumn(string $column, string|int $data, int $order_id): void
    {
        $this->db->query("UPDATE {$this->tableName} SET {$column} = ? WHERE order_id = ?", $data, $order_id);
    }

    public function getAllByRestaurantId(int $restaurant_id, string $selection = '*', $limit = null, $offset = null)
    {
        $query = "SELECT {$selection} FROM {$this->tableName} WHERE restaurant_id = ? ORDER BY created_at DESC";
        if (!is_null($limit)) {
            $query .= " LIMIT " . intval($limit);

            if (!is_null($offset)) {
                $query .= " OFFSET " . intval($offset);
            }
        }
        $stmt = $this->db->fetchAll($query, $restaurant_id);
        return $stmt;
    }

    public function getItemsByOrderId(int $order_id, string $selection = '*', $limit = null, $offset = null)
    {
        $query = "SELECT {$selection} FROM {$this->tableName}_items WHERE order_id = ? ORDER BY created_at DESC";
        if (!is_null($limit)) {
            $query .= " LIMIT " . intval($limit);

            if (!is_null($offset)) {
                $query .= " OFFSET " . intval($offset);
            }
        }
        $stmt = $this->db->fetchAll($query, $order_id);
        return $stmt;
    }

    public function getCustomizationByOrderId(int $order_id, string $selection = '*', $limit = null, $offset = null)
    {
        $query = "SELECT {$selection} FROM {$this->tableName}_customizations WHERE order_id = ? ORDER BY created_at DESC";
        if (!is_null($limit)) {
            $query .= " LIMIT " . intval($limit);

            if (!is_null($offset)) {
                $query .= " OFFSET " . intval($offset);
            }
        }
        $stmt = $this->db->fetchAll($query, $order_id);
        return $stmt;
    }

    public function getCustomizationByItemId(int $item_id, string $selection = '*', $limit = null, $offset = null)
    {
        $query = "SELECT {$selection} FROM {$this->tableName}_customizations WHERE order_item_id = ? ORDER BY created_at DESC";
        if (!is_null($limit)) {
            $query .= " LIMIT " . intval($limit);

            if (!is_null($offset)) {
                $query .= " OFFSET " . intval($offset);
            }
        }
        $stmt = $this->db->fetchAll($query, $item_id);
        return $stmt;
    }

    public function getAllByRestaurantIdCustom(int $restaurant_id, string $selection = '*', array $condition = [])
    {
        $filterBy = $condition['filter_by'] ?? '';
        $orderBy = $condition['order_by'] ?? 'created_at';
        $orderType = $condition['order_type'] ?? 'DESC';
        $limit = $condition['limit'] ?? null;
        $offset = $condition['offset'] ?? null;

        $query = "SELECT {$selection} FROM {$this->tableName} WHERE restaurant_id = ?";

        if (!empty($filterBy)) {
            if($filterBy === 'new'){
                $query .= " AND order_status = 'pending'";
            }elseif($filterBy === 'in_complete'){
                $query .= " AND (is_preparing = 'true' OR in_transit = 'true')";
            }elseif($filterBy === 'complete'){
                $query .= " AND order_status = 'completed'";
            }elseif($filterBy === 'decline'){
                $query .= " AND order_status = 'declined'";
            }elseif($filterBy === 'delivery'){
                $query .= " AND order_type = 'delivery'";
            }elseif($filterBy === 'takeaway'){
                $query .= " AND order_type = 'takeaway'";
            }elseif($filterBy === 'dine-in'){
                $query .= " AND order_type = 'dine-in'";
            }
        }

        $query .= " ORDER BY {$orderBy} {$orderType}";
        if (!is_null($limit)) {
            $query .= " LIMIT " . intval($limit);
            if (!is_null($offset)) {
                $query .= " OFFSET " . intval($offset);
            }
        }

        $stmt = $this->db->fetchAll($query, $restaurant_id);
        return $stmt;
    }

    public function getStatistics(int $restaurant_id)
    {
        $query = "SELECT
        (SELECT COUNT(order_id) FROM {$this->tableName} WHERE (restaurant_id = {$restaurant_id} AND order_type = 'delivery')) AS total_delivery,
        (SELECT COUNT(order_id) FROM {$this->tableName} WHERE (restaurant_id = {$restaurant_id} AND order_type = 'takeaway')) AS total_takeaway,
        (SELECT COUNT(order_id) FROM {$this->tableName} WHERE (restaurant_id = {$restaurant_id} AND order_type = 'dine-in')) AS total_dine_in,
        (SELECT COUNT(order_id) FROM {$this->tableName} WHERE (restaurant_id = {$restaurant_id} AND order_status = 'accepted')) AS total_accepted,
        (SELECT COUNT(order_id) FROM {$this->tableName} WHERE (restaurant_id = {$restaurant_id} AND order_status = 'completed')) AS total_completed,
        (SELECT COUNT(order_id) FROM {$this->tableName} WHERE (restaurant_id = {$restaurant_id} AND in_transit = 'true')) AS total_in_transit,
        (SELECT COUNT(order_id) FROM {$this->tableName} WHERE restaurant_id = {$restaurant_id} AND (order_status = 'declined' OR is_refunded = 'true')) AS total_declined_refunded ";
        return $this->db->fetch($query);
    }
}
