<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use PDO;

/**
 * Demo Class Request Model
 *
 * Handles demo class booking requests submitted from the homepage.
 * Tracks status (pending → contacted → scheduled → completed/cancelled)
 * and read state for admin notifications.
 */
class DemoClassRequest extends Model
{
    /**
     * The database table associated with the model.
     */
    protected string $table = 'demo_class_requests';

    /**
     * Get all demo class requests ordered by newest first.
     *
     * @return array<int, array<string, mixed>>
     */
    public function getAllOrdered(): array
    {
        $sql = sprintf(
            'SELECT d.*, c.name AS course_name FROM `%s` d LEFT JOIN `courses` c ON d.course_id = c.id ORDER BY d.created_at DESC',
            $this->table
        );
        $stmt = $this->db->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    /**
     * Get pending (unread) demo class requests.
     *
     * @return array<int, array<string, mixed>>
     */
    public function getPending(): array
    {
        $sql = sprintf(
            'SELECT * FROM `%s` WHERE `status` = :status ORDER BY `created_at` DESC',
            $this->table
        );
        $stmt = $this->db->prepare($sql);
        $stmt->execute([':status' => 'pending']);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    /**
     * Get unread demo class requests count.
     */
    public function getUnreadCount(): int
    {
        $sql = sprintf(
            'SELECT COUNT(*) AS total FROM `%s` WHERE `is_read` = :is_read',
            $this->table
        );
        $stmt = $this->db->prepare($sql);
        $stmt->execute([':is_read' => 0]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return (int) ($result['total'] ?? 0);
    }

    /**
     * Mark a request as read.
     */
    public function markRead(int $id): bool
    {
        return $this->update($id, ['is_read' => 1]);
    }

    /**
     * Update the status of a request.
     */
    public function updateStatus(int $id, string $status, string $note = ''): bool
    {
        $data = ['status' => $status, 'is_read' => 1];
        if ($note !== '') {
            $data['admin_note'] = $note;
        }
        return $this->update($id, $data);
    }

    /**
     * Get recent demo class requests.
     *
     * @param int $limit Maximum number of requests to return
     * @return array<int, array<string, mixed>>
     */
    public function getRecent(int $limit = 5): array
    {
        $sql = sprintf(
            'SELECT * FROM `%s` ORDER BY `created_at` DESC LIMIT :limit',
            $this->table
        );
        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    /**
     * Get count by status.
     */
    public function getCountByStatus(string $status): int
    {
        $sql = sprintf(
            'SELECT COUNT(*) AS total FROM `%s` WHERE `status` = :status',
            $this->table
        );
        $stmt = $this->db->prepare($sql);
        $stmt->execute([':status' => $status]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return (int) ($result['total'] ?? 0);
    }
}
