canteenhub/app/Models/Order.php

<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\Database\Database;
use PDO;

class Order
{
    public static function all(?string $status = null, ?string $search = null): array
    {
        $db = Database::getConnection();
        $query = "
            SELECT o.*, c.name as customer_name, c.email as customer_email,
                   (SELECT COUNT(*) FROM order_items WHERE order_id = o.id) as item_count
            FROM orders o
            JOIN customers c ON o.customer_id = c.id
        ";
        
        $conditions = [];
        $params = [];

        if ($status && $status !== 'all') {
            $conditions[] = "o.status = :status";
            $params[':status'] = $status;
        }

        if ($search) {
            $conditions[] = "(o.order_number LIKE :search OR c.name LIKE :search OR c.email LIKE :search)";
            $params[':search'] = "%{$search}%";
        }

        if (!empty($conditions)) {
            $query .= " WHERE " . implode(" AND ", $conditions);
        }

        $query .= " ORDER BY o.id DESC";

        $stmt = $db->prepare($query);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public static function find(int $id): ?array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare("
            SELECT o.*, c.name as customer_name, c.email as customer_email, 
                   c.phone as customer_phone, c.address as customer_address
            FROM orders o
            JOIN customers c ON o.customer_id = c.id
            WHERE o.id = ?
        ");
        $stmt->execute([$id]);
        $order = $stmt->fetch();

        if (!$order) {
            return null;
        }

        $order['items'] = OrderItem::getByOrderId($id);
        return $order;
    }

    public static function count(?string $status = null): int
    {
        $db = Database::getConnection();
        if ($status) {
            $stmt = $db->prepare("SELECT COUNT(*) FROM orders WHERE status = ?");
            $stmt->execute([$status]);
            return (int)$stmt->fetchColumn();
        }
        return (int)$db->query("SELECT COUNT(*) FROM orders")->fetchColumn();
    }

    public static function totalRevenue(): float
    {
        $db = Database::getConnection();
        return (float)$db->query("SELECT COALESCE(SUM(total), 0) FROM orders WHERE status != 'Cancelled'")->fetchColumn();
    }

    public static function recent(int $limit = 5): array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare("
            SELECT o.*, c.name as customer_name,
                   (SELECT COUNT(*) FROM order_items WHERE order_id = o.id) as item_count
            FROM orders o
            JOIN customers c ON o.customer_id = c.id
            ORDER BY o.id DESC
            LIMIT :limit
        ");
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public static function createOrderWithItems(array $orderData, array $items): int
    {
        $db = Database::getConnection();
        $db->beginTransaction();

        try {
            $orderNumber = 'ORD-' . date('Ymd') . '-' . strtoupper(substr(uniqid(), -4));

            $stmt = $db->prepare("
                INSERT INTO orders (order_number, customer_id, status, subtotal, tax, discount, total, notes) 
                VALUES (:order_number, :customer_id, :status, :subtotal, :tax, :discount, :total, :notes)
            ");
            
            $stmt->execute([
                ':order_number' => $orderNumber,
                ':customer_id' => $orderData['customer_id'],
                ':status' => $orderData['status'] ?? 'Pending',
                ':subtotal' => $orderData['subtotal'],
                ':tax' => $orderData['tax'] ?? 0.00,
                ':discount' => $orderData['discount'] ?? 0.00,
                ':total' => $orderData['total'],
                ':notes' => $orderData['notes'] ?? '',
            ]);

            $orderId = (int)$db->lastInsertId();

            foreach ($items as $item) {
                if (empty($item['product_id']) || empty($item['quantity'])) {
                    continue;
                }

                $productId = (int)$item['product_id'];
                $quantity = (int)$item['quantity'];
                $unitPrice = (float)$item['unit_price'];
                $totalPrice = $quantity * $unitPrice;
                $productName = $item['product_name'] ?? 'Item';

                OrderItem::create([
                    'order_id' => $orderId,
                    'product_id' => $productId,
                    'product_name' => $productName,
                    'quantity' => $quantity,
                    'unit_price' => $unitPrice,
                    'total_price' => $totalPrice,
                ]);

                // Deduct stock inventory
                Product::deductStock($productId, $quantity);
            }

            $db->commit();
            return $orderId;
        } catch (\Throwable $e) {
            $db->rollBack();
            throw $e;
        }
    }

    public static function updateStatus(int $id, string $status): bool
    {
        $db = Database::getConnection();
        $stmt = $db->prepare("UPDATE orders SET status = ?, updated_at = datetime('now', 'localtime') WHERE id = ?");
        return $stmt->execute([$status, $id]);
    }

    public static function delete(int $id): bool
    {
        $db = Database::getConnection();
        $stmt = $db->prepare("DELETE FROM orders WHERE id = ?");
        return $stmt->execute([$id]);
    }
}