<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Support\Facades\Schema;

class SubjectCurriculum extends BaseModel
{
    protected $table = 'subject_curriculum_items';

    protected $fillable = ['class_id', 'school_year', 'subject', 'unit_number', 'unit_title', 'chapter_name', 'created_at', 'updated_at'];

    public $timestamps = true;

    protected $casts = [
        'class_id' => 'integer',
        'unit_number' => 'integer',
        'created_at' => 'datetime',
        'updated_at' => 'datetime',
    ];

    /* ============================================================
     * Relationships (optional)
     * ============================================================
     */

    public function class(): BelongsTo
    {
        // Rename SchoolClass/ClassModel to match your project
        return $this->belongsTo(SchoolClass::class, 'class_id');
    }

    /* ============================================================
     * Scopes
     * ============================================================
     */

    public function scopeForClass(Builder $q, int $classId): Builder
    {
        return $q->where('class_id', $classId);
    }

    public function scopeForSubject(Builder $q, string $subject): Builder
    {
        return $q->where('subject', $subject);
    }

    public function scopeForSchoolYear(Builder $q, ?string $schoolYear): Builder
    {
        $schoolYear = trim((string) $schoolYear);
        if ($schoolYear === '' || ! Schema::hasColumn($this->getTable(), 'school_year')) {
            return $q;
        }

        return $q->where(function (Builder $yearQuery) use ($schoolYear) {
            $yearQuery->where('school_year', $schoolYear)
                ->orWhereNull('school_year')
                ->orWhere('school_year', '');
        });
    }

    public function scopeOrdered(Builder $q): Builder
    {
        return $q->orderBy('unit_number', 'asc')
            ->orderBy('chapter_name', 'asc');
    }

    /* ============================================================
     * legacy-compatible method
     * ============================================================
     */

    /**
     * legacy: getOptionsForClass($classId, $subject)
     */
    public static function getOptionsForClass(int $classId, string $subject, ?string $schoolYear = null): array
    {
        return static::query()
            ->forClass($classId)
            ->forSubject($subject)
            ->forSchoolYear($schoolYear)
            ->ordered()
            ->get()
            ->all();
    }

    /* ============================================================
     * Enhancements for dropdowns (optional)
     * ============================================================
     */

    /**
     * Distinct unit options for dropdowns:
     * returns array of [unit_number, unit_title]
     */
    public static function unitOptions(int $classId, string $subject): array
    {
        return static::query()
            ->forClass($classId)
            ->forSubject($subject)
            ->select('unit_number', 'unit_title')
            ->whereNotNull('unit_number')
            ->orderBy('unit_number', 'asc')
            ->distinct()
            ->get()
            ->map(fn ($r) => [
                'unit_number' => (int) $r->unit_number,
                'unit_title' => (string) ($r->unit_title ?? ''),
            ])
            ->all();
    }

    /**
     * Distinct chapter names for a unit (useful for 2nd dropdown).
     */
    public static function chapterOptions(int $classId, string $subject, int $unitNumber): array
    {
        return static::query()
            ->forClass($classId)
            ->forSubject($subject)
            ->where('unit_number', $unitNumber)
            ->select('chapter_name')
            ->whereNotNull('chapter_name')
            ->orderBy('chapter_name', 'asc')
            ->distinct()
            ->pluck('chapter_name')
            ->filter(fn ($v) => $v !== null && trim((string) $v) !== '')
            ->values()
            ->all();
    }

    /* ============================================================
     * Optional validation helper
     * ============================================================
     */

    public static function rules(bool $updating = false): array
    {
        $req = $updating ? 'sometimes' : 'required';

        return [
            'class_id' => [$req, 'integer', 'min:1'],
            'subject' => [$req, 'string', 'max:120'],
            'unit_number' => ['nullable', 'integer', 'min:0'],
            'unit_title' => ['nullable', 'string', 'max:255'],
            'chapter_name' => ['nullable', 'string', 'max:255'],
        ];
    }
}