<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use PDO;

/**
 * Student Model
 *
 * Handles student enrollment records with JOIN queries
 * for related user, course, and branch data.
 */
class Student extends Model
{
    /**
     * The database table associated with the model.
     */
    protected string $table = 'students';

    /**
     * Get a student record by the associated user ID.
     *
     * @param int $userId The user ID to look up
     * @return array<string, mixed>|null The student record or null if not found
     */
    public function getByUserId(int $userId): ?array
    {
        $sql = sprintf(
            'SELECT * FROM `%s` WHERE `user_id` = :user_id LIMIT 1',
            $this->table
        );

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

        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        return $result !== false ? $result : null;
    }

    /**
     * Get a student with full details (JOIN users, courses, branches).
     *
     * @param int $id The student ID
     * @return array<string, mixed>|null The student record with related data or null
     */
    public function getWithDetails(int $id): ?array
    {
        $sql = sprintf(
            'SELECT s.*, u.name AS user_name, u.email AS user_email, u.phone AS user_phone, u.photo AS user_photo,
                    c.name AS course_name, c.category AS course_category, c.duration AS course_duration,
                    b.name AS branch_name, b.city AS branch_city, b.state AS branch_state
             FROM `%s` s
             LEFT JOIN `users` u ON s.user_id = u.id
             LEFT JOIN `courses` c ON s.course_id = c.id
             LEFT JOIN `branches` b ON s.branch_id = b.id
             WHERE s.id = :id
             LIMIT 1',
            $this->table
        );

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

        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        return $result !== false ? $result : null;
    }

    /**
     * Get all students enrolled in a specific course.
     *
     * @param int $courseId The course ID to filter by
     * @return array<int, array<string, mixed>> Array of student records
     */
    public function getByCourse(int $courseId): array
    {
        $sql = sprintf(
            'SELECT s.*, u.name AS user_name, u.email AS user_email
             FROM `%s` s
             LEFT JOIN `users` u ON s.user_id = u.id
             WHERE s.course_id = :course_id
             ORDER BY s.created_at DESC',
            $this->table
        );

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

        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    /**
     * Get all students enrolled at a specific branch.
     *
     * @param int $branchId The branch ID to filter by
     * @return array<int, array<string, mixed>> Array of student records
     */
    public function getByBranch(int $branchId): array
    {
        $sql = sprintf(
            'SELECT s.*, u.name AS user_name, u.email AS user_email
             FROM `%s` s
             LEFT JOIN `users` u ON s.user_id = u.id
             WHERE s.branch_id = :branch_id
             ORDER BY s.created_at DESC',
            $this->table
        );

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

        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }
}
