<?php

declare(strict_types=1);

namespace App\Models;

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

class SessionMenu
{
    public static function getBySessionId(int $sessionId): array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare("SELECT * FROM session_menus WHERE session_id = ? ORDER BY id ASC");
        $stmt->execute([$sessionId]);
        return $stmt->fetchAll();
    }

    public static function add(int $sessionId, string $name, float $price = 0.00): int
    {
        $db = Database::getConnection();
        $stmt = $db->prepare("INSERT INTO session_menus (session_id, name, final_price) VALUES (?, ?, ?)");
        $stmt->execute([$sessionId, trim($name), $price]);
        return (int)$db->lastInsertId();
    }

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

    /**
     * Update final prices from canteen bill and recalculate all employee order line items
     */
    public static function updatePricesAndRecalculate(int $sessionId, array $menuPrices): void
    {
        $db = Database::getConnection();
        $db->beginTransaction();

        try {
            $stmtUpdateMenu = $db->prepare("UPDATE session_menus SET final_price = ? WHERE id = ? AND session_id = ?");
            $stmtUpdateItems = $db->prepare("
                UPDATE employee_order_items 
                SET unit_price = ?, total_price = quantity * ? 
                WHERE session_menu_id = ?
            ");

            foreach ($menuPrices as $menuId => $price) {
                $menuId = (int)$menuId;
                $price = (float)$price;

                $stmtUpdateMenu->execute([$price, $menuId, $sessionId]);
                $stmtUpdateItems->execute([$price, $price, $menuId]);
            }

            // Recalculate total_bill on employee_orders
            $stmtOrders = $db->prepare("SELECT id FROM employee_orders WHERE session_id = ?");
            $stmtOrders->execute([$sessionId]);
            $orders = $stmtOrders->fetchAll();

            $stmtUpdateOrderTotal = $db->prepare("
                UPDATE employee_orders 
                SET total_bill = (SELECT COALESCE(SUM(total_price), 0) FROM employee_order_items WHERE employee_order_id = ?) 
                WHERE id = ?
            ");

            foreach ($orders as $order) {
                $stmtUpdateOrderTotal->execute([$order['id'], $order['id']]);
            }

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