gravity/บริหารจัดการคิวนวดแผนไทย/app/Models/Queue.php

<?php
namespace App\Models;

use PDO;

/**
 * Class Queue
 * Core Queue & Smart AI Allocator Model
 *
 * @package App\Models
 */
class Queue extends Model
{
    protected string $table = 'queues';

    /**
     * Generate Queue Number for today (e.g., A001, V001, E001)
     */
    public function generateQueueNo(int $branchId, string $priority = 'Normal'): string
    {
        $prefix = 'A';
        if ($priority === 'VIP') $prefix = 'V';
        if ($priority === 'Emergency') $prefix = 'E';

        $sql = "SELECT COUNT(*) as cnt FROM `{$this->table}` 
                WHERE `branch_id` = :bid AND `queue_date` = CURDATE() AND `priority` = :prio";
        $stmt = self::getDB()->prepare($sql);
        $stmt->execute(['bid' => $branchId, 'prio' => $priority]);
        $row = $stmt->fetch();
        $nextNum = ((int)($row['cnt'] ?? 0)) + 1;

        return sprintf("%s%03d", $prefix, $nextNum);
    }

    /**
     * Create Queue and Execute Smart AI Allocation Stored Procedure
     *
     * @param array $data
     * @return array Result containing queue_id, queue_no, therapist_id, room_id, est_time
     */
    public function createWithSmartAssign(array $data): array
    {
        $branchId = (int)$data['branch_id'];
        $priority = $data['priority'] ?? 'Normal';
        $queueNo = $this->generateQueueNo($branchId, $priority);

        $queueId = $this->create([
            'branch_id' => $branchId,
            'queue_no' => $queueNo,
            'queue_date' => date('Y-m-d'),
            'patient_id' => (int)$data['patient_id'],
            'therapist_id' => !empty($data['therapist_id']) ? (int)$data['therapist_id'] : null,
            'service_id' => (int)$data['service_id'],
            'room_id' => !empty($data['room_id']) ? (int)$data['room_id'] : null,
            'booking_type' => $data['booking_type'] ?? 'Walk_in',
            'priority' => $priority,
            'status' => 'Waiting',
            'checkin_time' => date('Y-m-d H:i:s'),
            'created_by' => (int)$data['created_by'],
            'created_at' => date('Y-m-d H:i:s'),
            'updated_at' => date('Y-m-d H:i:s'),
        ]);

        // หากผู้ใช้ไม่ได้ระบุหมอนวด ให้ใช้ Smart Queue AI Stored Procedure ทำการเลือกหมอนวดและห้องอัตโนมัติ
        $assignedTherapistId = null;
        $assignedRoomId = null;
        $estStart = null;
        $estEnd = null;
        $statusMsg = "Created without AI";

        if (empty($data['therapist_id'])) {
            $stmt = self::getDB()->prepare("CALL sp_assign_smart_queue(:qid, @therapist_id, @room_id, @est_start, @est_end, @status_msg)");
            $stmt->execute(['qid' => $queueId]);
            $stmt->closeCursor();

            $res = self::getDB()->query("SELECT @therapist_id AS t_id, @room_id AS r_id, @est_start AS e_start, @est_end AS e_end, @status_msg AS msg")->fetch();
            $assignedTherapistId = $res['t_id'] ? (int)$res['t_id'] : null;
            $assignedRoomId = $res['r_id'] ? (int)$res['r_id'] : null;
            $estStart = $res['e_start'];
            $estEnd = $res['e_end'];
            $statusMsg = $res['msg'];
        }

        return [
            'queue_id' => $queueId,
            'queue_no' => $queueNo,
            'assigned_therapist_id' => $assignedTherapistId,
            'assigned_room_id' => $assignedRoomId,
            'est_start_time' => $estStart,
            'est_end_time' => $estEnd,
            'ai_message' => $statusMsg,
        ];
    }

    /**
     * Get Realtime Queue Board from View
     */
    public function getRealtimeBoard(int $branchId = null): array
    {
        $sql = "SELECT * FROM `vw_realtime_queue_board` ";
        $params = [];
        if ($branchId !== null) {
            $sql .= " WHERE branch_id = :bid ";
            $params['bid'] = $branchId;
        }
        $sql .= " ORDER BY CASE priority WHEN 'Emergency' THEN 1 WHEN 'VIP' THEN 2 ELSE 3 END ASC, checkin_time ASC";
        
        $stmt = self::getDB()->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    /**
     * Get TV Mode Display Queues (Waiting vs Calling/In-Progress)
     */
    public function getTvDisplay(int $branchId): array
    {
        $sql = "SELECT q.queue_no, q.status, q.priority, 
                       COALESCE(r.room_no, 'รอเรียกห้อง') AS room_no,
                       COALESCE(CONCAT(u.first_name, ' ', u.last_name), '-') AS therapist_name
                FROM `{$this->table}` q
                LEFT JOIN `rooms` r ON q.room_id = r.id
                LEFT JOIN `therapists` t ON q.therapist_id = t.id
                LEFT JOIN `users` u ON t.user_id = u.id
                WHERE q.branch_id = :bid 
                  AND q.queue_date = CURDATE() 
                  AND q.status IN ('Waiting', 'Assigned', 'In_Progress')
                ORDER BY q.status DESC, q.checkin_time ASC";
        $stmt = self::getDB()->prepare($sql);
        $stmt->execute(['bid' => $branchId]);
        
        $all = $stmt->fetchAll();
        $waiting = [];
        $calling = [];

        foreach ($all as $item) {
            if (in_array($item['status'], ['Assigned', 'In_Progress'])) {
                $calling[] = $item;
            } else {
                $waiting[] = $item;
            }
        }

        return ['waiting' => $waiting, 'calling' => $calling];
    }
}