<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use PDO;

/**
 * BatchSchedule Model
 *
 * Manages upcoming batch schedules for courses.
 */
class BatchSchedule extends Model
{
    protected string $table = 'batch_schedules';

    /**
     * Get upcoming batches (public listing).
     */
    public function getUpcoming(int $limit = 20): array
    {
        $sql = "SELECT b.*, c.name AS course_name
                FROM `{$this->table}` b
                LEFT JOIN `courses` c ON b.course_id = c.id
                WHERE b.status IN ('upcoming', 'running')
                ORDER BY b.is_featured DESC, b.start_date ASC, b.sort_order ASC
                LIMIT :limit";

        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    /**
     * Get all batches with course name (for admin).
     */
    public function getAllWithCourse(): array
    {
        $sql = "SELECT b.*, c.name AS course_name
                FROM `{$this->table}` b
                LEFT JOIN `courses` c ON b.course_id = c.id
                ORDER BY b.start_date DESC, b.sort_order ASC";

        $stmt = $this->db->prepare($sql);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    /**
     * Get featured upcoming batches for homepage.
     */
    public function getFeatured(int $limit = 4): array
    {
        $sql = "SELECT b.*, c.name AS course_name
                FROM `{$this->table}` b
                LEFT JOIN `courses` c ON b.course_id = c.id
                WHERE b.status IN ('upcoming', 'running') AND b.is_featured = 1
                ORDER BY b.start_date ASC
                LIMIT :limit";

        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    /**
     * Get the next batch that has show_countdown enabled (for countdown timer).
     */
    public function getCountdownBatch(): ?array
    {
        $sql = "SELECT b.*, c.name AS course_name
                FROM `{$this->table}` b
                LEFT JOIN `courses` c ON b.course_id = c.id
                WHERE b.show_countdown = 1 AND b.status = 'upcoming' AND b.start_date > CURDATE()
                ORDER BY b.start_date ASC
                LIMIT 1";

        $stmt = $this->db->prepare($sql);
        $stmt->execute();

        $result = $stmt->fetch();
        return $result !== false ? $result : null;
    }

    /**
     * Filter batches by course.
     */
    public function getByCourse(int $courseId): array
    {
        $sql = "SELECT b.*, c.name AS course_name
                FROM `{$this->table}` b
                LEFT JOIN `courses` c ON b.course_id = c.id
                WHERE b.course_id = :cid AND b.status IN ('upcoming', 'running')
                ORDER BY b.start_date ASC";

        $stmt = $this->db->prepare($sql);
        $stmt->execute([':cid' => $courseId]);

        return $stmt->fetchAll();
    }

    /**
     * Update batch status automatically based on dates.
     *
     * Marks batches as 'running' once start_date <= today (when status='upcoming'),
     * and as 'completed' once end_date < today (when status='running').
     *
     * @return int Number of rows transitioned from upcoming → running
     */
    public function autoUpdateStatuses(): int
    {
        // Mark as 'running' if start_date <= today AND status = upcoming
        $sql1 = "UPDATE `{$this->table}` SET `status` = 'running'
                 WHERE `status` = 'upcoming' AND `start_date` <= CURDATE()";
        $stmt1 = $this->db->prepare($sql1);
        $stmt1->execute();
        $runningCount = $stmt1->rowCount();

        // Mark as 'completed' if end_date < today AND status = running
        $sql2 = "UPDATE `{$this->table}` SET `status` = 'completed'
                 WHERE `status` = 'running' AND `end_date` IS NOT NULL AND `end_date` < CURDATE()";
        $stmt2 = $this->db->prepare($sql2);
        $stmt2->execute();

        return (int) $runningCount;
    }
}
