<?php

declare(strict_types=1);

namespace App\Models;

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

class DailySession
{
    public static function create(int $creatorId, string $title, string $canteenName, string $sessionDate, ?string $notes): int
    {
        $db = Database::getConnection();
        $stmt = $db->prepare("
            INSERT INTO daily_sessions (creator_id, session_date, title, canteen_name, status, notes) 
            VALUES (?, ?, ?, ?, 'open', ?)
        ");
        $stmt->execute([
            $creatorId,
            $sessionDate,
            trim($title),
            trim($canteenName),
            trim((string)$notes)
        ]);

        return (int)$db->lastInsertId();
    }

    public static function find(int $id): ?array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare("
            SELECT s.*, u.name as creator_name, u.username as creator_username
            FROM daily_sessions s
            JOIN users u ON s.creator_id = u.id
            WHERE s.id = ?
        ");
        $stmt->execute([$id]);
        $session = $stmt->fetch();

        if (!$session) {
            return null;
        }

        $session['menus'] = SessionMenu::getBySessionId($id);
        $session['orders'] = EmployeeOrder::getBySessionId($id);
        $session['recap'] = self::getCanteenRecap($id);

        return $session;
    }

    public static function all(): array
    {
        $db = Database::getConnection();
        $stmt = $db->query("
            SELECT s.*, u.name as creator_name, u.username as creator_username,
                   (SELECT COUNT(*) FROM employee_orders WHERE session_id = s.id) as order_count,
                   (SELECT COUNT(*) FROM session_menus WHERE session_id = s.id) as menu_count,
                   (SELECT COALESCE(SUM(total_bill), 0) FROM employee_orders WHERE session_id = s.id) as total_session_amount
            FROM daily_sessions s
            JOIN users u ON s.creator_id = u.id
            ORDER BY s.id DESC
        ");
        return $stmt->fetchAll();
    }

    public static function getActiveSessions(): array
    {
        $db = Database::getConnection();
        $stmt = $db->query("
            SELECT s.*, u.name as creator_name, u.username as creator_username,
                   (SELECT COUNT(*) FROM employee_orders WHERE session_id = s.id) as order_count,
                   (SELECT COUNT(*) FROM session_menus WHERE session_id = s.id) as menu_count,
                   (SELECT COALESCE(SUM(total_bill), 0) FROM employee_orders WHERE session_id = s.id) as total_session_amount
            FROM daily_sessions s
            JOIN users u ON s.creator_id = u.id
            WHERE s.status IN ('open', 'closed', 'arrived')
            ORDER BY s.id DESC
        ");
        return $stmt->fetchAll();
    }

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

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

    public static function getCanteenRecap(int $sessionId): array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare("
            SELECT eoi.item_name, 
                   SUM(eoi.quantity) as total_quantity,
                   GROUP_CONCAT(DISTINCT eoi.notes) as combined_notes
            FROM employee_order_items eoi
            JOIN employee_orders eo ON eoi.employee_order_id = eo.id
            WHERE eo.session_id = ?
            GROUP BY eoi.item_name
            ORDER BY total_quantity DESC
        ");
        $stmt->execute([$sessionId]);
        return $stmt->fetchAll();
    }
}