<?php

namespace Models;

use Models\OrderModel;
use Models\ProductModel;

class OrderItemModel
{
    private \PDO $pdo;

    public function __construct()
    {
        $dbFile = $_ENV['DB_FILE'];
        $dbPath = __DIR__ . '/../' . $dbFile;
        $this->pdo = new \PDO("sqlite:" . $dbPath);
        $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
        $this->pdo->exec('PRAGMA journal_mode = WAL;');
        $this->pdo->exec('PRAGMA busy_timeout = 5000;');
    }

    public function createOrderItem(
        int $orderId,
        int $productId,
        int $companyId,
        string $kitchenNote = ''
    ): int|false
    {
        $orderModel = new OrderModel();
        $productModel = new ProductModel();

        $order = $orderModel->getOrderById($orderId, $companyId, false);
        if (!$order) {
            return false;
        }

        $product = $productModel->getProductById($productId, $companyId);
        if (!$product) {
            return false;
        }

        $stmt = $this->pdo->prepare("
            INSERT INTO order_item (
                order_id,
                product_id,
                company_id,
                order_item_flag,
                order_item_kitchen_note
            ) VALUES (
                :order_id,
                :product_id,
                :company_id,
                'a',
                :order_item_kitchen_note
            )
        ");

        try {
            $executed = $stmt->execute([
                'order_id' => $orderId,
                'product_id' => $productId,
                'company_id' => $companyId,
                'order_item_kitchen_note' => $kitchenNote
            ]);

            if ($executed) {
                if (!empty($product['product_is_kitchen']) && $product['product_is_kitchen'] == 1) {
                    $this->pdo->prepare("
                        UPDATE `order`
                        SET order_flag = 'a'
                        WHERE order_id = :order_id
                          AND company_id = :company_id
                    ")->execute([
                        'order_id' => $orderId,
                        'company_id' => $companyId
                    ]);
                }
                return (int)$this->pdo->lastInsertId();
            }
            return false;

        } catch (\PDOException $e) {
            error_log("PDO Exception during order item creation: " . $e->getMessage());
            return false;
        }
    }

    public function deleteOrderItem(int $orderItemId, int $companyId): bool
    {
        $stmt = $this->pdo->prepare("
            DELETE FROM order_item
            WHERE order_item_id = :order_item_id
              AND order_id IN (
                  SELECT order_id FROM `order` WHERE company_id = :company_id AND order_flag IN ('a', 'p')
              )
        ");
        $executed = $stmt->execute(['order_item_id' => $orderItemId, 'company_id' => $companyId]);
        return $executed && $stmt->rowCount() > 0;
    }

    public function getOrderItemsByOrderId(?int $orderId, int $companyId, int $page = 1, int $limit = 10): array
    {
        if ($orderId !== null) {
            $orderModel = new OrderModel();
            $order = $orderModel->getOrderById($orderId, $companyId);
            if (!$order) {
                return [];
            }
        
            $stmt = $this->pdo->prepare("
                SELECT order_item_id, order_id, product_id, company_id 
                FROM order_item 
                WHERE order_id = :order_id AND company_id = :company_id
            ");
            $stmt->execute([
                'order_id' => $orderId,
                'company_id' => $companyId,
            ]);
        } else {
            $offset = ($page - 1) * $limit;
        
            $stmt = $this->pdo->prepare("
                SELECT order_item_id, order_id, product_id, company_id 
                FROM order_item 
                WHERE company_id = :company_id
                LIMIT :limit OFFSET :offset
            ");
            $stmt->bindValue(':company_id', $companyId, \PDO::PARAM_INT);
            $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
            $stmt->bindValue(':offset', $offset, \PDO::PARAM_INT);
            $stmt->execute();
        }
    
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    public function updateOrderItem(int $orderItemId, int $companyId): bool
    {
        $stmt = $this->pdo->prepare("
            SELECT order_item_flag, order_id, product_id
            FROM order_item
            WHERE order_item_id = :order_item_id
              AND company_id = :company_id
        ");
        $stmt->execute([
            'order_item_id' => $orderItemId,
            'company_id' => $companyId
        ]);
        $item = $stmt->fetch(\PDO::FETCH_ASSOC);

        if (!$item) {
            return false;
        }

        $newFlag = $item['order_item_flag'] === 'a' ? 'p' : 'a';

        $update = $this->pdo->prepare("
            UPDATE order_item
            SET order_item_flag = :new_flag
            WHERE order_item_id = :order_item_id
              AND company_id = :company_id
        ");
        $executed = $update->execute([
            'new_flag' => $newFlag,
            'order_item_id' => $orderItemId,
            'company_id' => $companyId
        ]);

        if ($executed && $update->rowCount() > 0) {
            $stmtCheck = $this->pdo->prepare("
                SELECT COUNT(*) 
                FROM order_item
                NATURAL JOIN product
                WHERE order_id = :order_id
                  AND company_id = :company_id
                  AND product_is_kitchen = 1
                  AND order_item_flag != 'p'
            ");
            $stmtCheck->execute([
                'order_id' => $item['order_id'],
                'company_id' => $companyId
            ]);
            $pendingKitchen = (int)$stmtCheck->fetchColumn();

            if ($pendingKitchen === 0) {
                $this->pdo->prepare("
                    UPDATE `order`
                    SET order_flag = 'p'
                    WHERE order_id = :order_id
                      AND company_id = :company_id
                ")->execute([
                    'order_id' => $item['order_id'],
                    'company_id' => $companyId
                ]);
            } else {
                $stmtOrderFlag = $this->pdo->prepare("
                    SELECT order_flag FROM `order`
                    WHERE order_id = :order_id AND company_id = :company_id
                ");
                $stmtOrderFlag->execute([
                    'order_id' => $item['order_id'],
                    'company_id' => $companyId
                ]);
                $currentOrderFlag = $stmtOrderFlag->fetchColumn();

                if ($currentOrderFlag === 'p' && $newFlag === 'a') {
                    $this->pdo->prepare("
                        UPDATE `order`
                        SET order_flag = 'a'
                        WHERE order_id = :order_id AND company_id = :company_id
                    ")->execute([
                        'order_id' => $item['order_id'],
                        'company_id' => $companyId
                    ]);
                }
            }
            return true;
        }
        return false;
    }
}