<?php

namespace App\Models;

use CodeIgniter\Model;

class SubjectCurriculumModel extends Model
{
    protected $table = 'subject_curriculum_items';
    protected $primaryKey = 'id';
    protected $allowedFields = [
        'class_id',
        'subject',
        'unit_number',
        'unit_title',
        'chapter_name',
        'created_at',
        'updated_at',
    ];
    protected $returnType = 'array';
    protected $useTimestamps = true;
    protected $createdField  = 'created_at';
    protected $updatedField  = 'updated_at';

    public function getOptionsForClass(int $classId, string $subject): array
    {
        $rows = $this->orderedOptionsBuilder()
            ->where('subject_curriculum_items.class_id', $classId)
            ->where('subject_curriculum_items.subject', $subject)
            ->get()
            ->getResultArray();

        if (! empty($rows)) {
            return $rows;
        }

        $fallbackClassIds = $this->classIdsWithSameName($classId);
        if (empty($fallbackClassIds)) {
            return [];
        }

        $rows = $this->orderedOptionsBuilder()
            ->whereIn('subject_curriculum_items.class_id', $fallbackClassIds)
            ->where('subject_curriculum_items.subject', $subject)
            ->get()
            ->getResultArray();

        return $this->uniqueCurriculumRows($rows);
    }

    private function orderedOptionsBuilder()
    {
        return $this->db->table($this->table)
            ->select('subject_curriculum_items.*')
            ->orderBy('unit_number', 'ASC')
            ->orderBy("CAST(SUBSTRING_INDEX(chapter_name, '.', 1) AS UNSIGNED)", 'ASC', false)
            ->orderBy('chapter_name', 'ASC')
            ->orderBy('id', 'ASC');
    }

    private function classIdsWithSameName(int $classId): array
    {
        $class = $this->db->table('classes')
            ->select('class_name')
            ->where('id', $classId)
            ->get()
            ->getRowArray();

        $className = trim((string) ($class['class_name'] ?? ''));
        if ($className === '') {
            return [];
        }

        $rows = $this->db->table('classes')
            ->select('id')
            ->where('class_name', $className)
            ->where('id !=', $classId)
            ->get()
            ->getResultArray();

        return array_values(array_filter(array_map(
            static fn (array $row): int => (int) ($row['id'] ?? 0),
            $rows
        )));
    }

    private function uniqueCurriculumRows(array $rows): array
    {
        $seen = [];
        $unique = [];

        foreach ($rows as $row) {
            $key = implode('|', [
                (string) ($row['subject'] ?? ''),
                (string) ($row['unit_number'] ?? ''),
                (string) ($row['unit_title'] ?? ''),
                (string) ($row['chapter_name'] ?? ''),
            ]);

            if (isset($seen[$key])) {
                continue;
            }

            $seen[$key] = true;
            $unique[] = $row;
        }

        return $unique;
    }
}