<?php

declare(strict_types=1);



function timetableTerms(): array

{

    return ['First Term', 'Second Term', 'Third Term'];

}



function timetableGetCurrentTerm(mysqli $conn): string

{

    $stmt = $conn->prepare("SELECT setting_value FROM settings WHERE setting_key = 'current_term' LIMIT 1");

    $stmt->execute();

    $row = $stmt->get_result()->fetch_assoc();

    $stmt->close();

    if (!empty($row['setting_value'])) {

        return (string) $row['setting_value'];

    }

    $month = (int) date('n');

    if ($month >= 9 && $month <= 12) return 'First Term';

    if ($month >= 1 && $month <= 4) return 'Second Term';

    return 'Third Term';

}



function timetableGetCurrentYear(mysqli $conn): string

{

    $stmt = $conn->prepare("SELECT setting_value FROM settings WHERE setting_key = 'academic_year' LIMIT 1");

    $stmt->execute();

    $row = $stmt->get_result()->fetch_assoc();

    $stmt->close();

    if (!empty($row['setting_value'])) {

        return (string) $row['setting_value'];

    }

    return (string) (getAcademicYear() ?: date('Y'));

}



function timetableDays(): array

{

    return ['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat'];

}



function timetableGetClasses(mysqli $conn): array

{

    $classes = [];

    // Primary: active classes from Class Management table

    $stmt = $conn->prepare(

        "SELECT class_name FROM classes WHERE is_active = 1 ORDER BY class_name"

    );

    $stmt->execute();

    $result = $stmt->get_result();

    while ($row = $result->fetch_assoc()) {

        $classes[] = $row['class_name'];

    }

    $stmt->close();

    // Fallback: include any student class not yet in Class Management

    $stmt2 = $conn->prepare(

        "SELECT DISTINCT class FROM students WHERE class IS NOT NULL AND class != '' ORDER BY class"

    );

    $stmt2->execute();

    $result2 = $stmt2->get_result();

    while ($row = $result2->fetch_assoc()) {

        if (!in_array($row['class'], $classes)) {

            $classes[] = $row['class'];

        }

    }

    $stmt2->close();

    sort($classes);

    return $classes;

}



function timetableGetSubjects(mysqli $conn): array

{

    $stmt = $conn->prepare(

        "SELECT id, subject_name

         FROM subjects

         WHERE active = 1 OR active = 'yes'

         ORDER BY subject_name"

    );

    $stmt->execute();

    $result = $stmt->get_result();

    $subjects = [];

    while ($row = $result->fetch_assoc()) {

        $subjects[] = $row;

    }

    $stmt->close();

    return $subjects;

}



function timetableGetTeachers(mysqli $conn): array

{

    $stmt = $conn->prepare(

        "SELECT id, full_name

         FROM staff

         WHERE status = 'active'

         ORDER BY full_name"

    );

    $stmt->execute();

    $result = $stmt->get_result();

    $teachers = [];

    while ($row = $result->fetch_assoc()) {

        $teachers[] = $row;

    }

    $stmt->close();

    return $teachers;

}



function timetableGetPeriods(mysqli $conn, string $class_name = ''): array

{

    if ($class_name !== '') {

        $stmt = $conn->prepare(

            "SELECT * FROM timetable_periods WHERE class_name = ?

             ORDER BY order_no, start_time, id"

        );

        $stmt->bind_param('s', $class_name);

    } else {

        $stmt = $conn->prepare(

            // Periods are stored per class, so an unfiltered read would repeat
            // every period once for each class. Collapse to one canonical row
            // per time slot for the class-agnostic views.
            "SELECT MIN(id) AS id, period_name, start_time, end_time, is_break,
                    MIN(order_no) AS order_no, '' AS class_name,
                    MIN(created_at) AS created_at
             FROM timetable_periods
             GROUP BY period_name, start_time, end_time, is_break
             ORDER BY order_no, start_time, id"

        );

    }

    $stmt->execute();

    $result = $stmt->get_result();

    $periods = [];

    while ($row = $result->fetch_assoc()) {

        $periods[] = $row;

    }

    $stmt->close();

    return $periods;

}



function timetableGetSettings(mysqli $conn): array

{

    $stmt = $conn->prepare("SELECT setting_key, setting_value FROM settings");

    $stmt->execute();

    $result = $stmt->get_result();

    $settings = [];

    while ($row = $result->fetch_assoc()) {

        $settings[$row['setting_key']] = $row['setting_value'];

    }

    $stmt->close();

    return $settings;

}



function timetableFindTeacherConflict(

    mysqli $conn,

    int $teacherId,

    string $dayOfWeek,

    int $periodId,

    string $academicYear,

    string $term,

    int $excludeSlotId = 0

): ?array {

    $sql = "SELECT ts.id, ts.class_name, s.subject_name, st.full_name AS teacher_name

            FROM timetable_slots ts

            LEFT JOIN subjects s ON ts.subject_id = s.id

            LEFT JOIN staff st ON ts.teacher_id = st.id

            WHERE ts.teacher_id = ?

              AND ts.day_of_week = ?

              AND ts.period_id = ?

              AND ts.academic_year = ?

              AND ts.term = ?";

    if ($excludeSlotId > 0) {

        $sql .= " AND ts.id != ?";

    }

    $sql .= " LIMIT 1";



    $stmt = $conn->prepare($sql);

    if ($excludeSlotId > 0) {

        $stmt->bind_param('isissi', $teacherId, $dayOfWeek, $periodId, $academicYear, $term, $excludeSlotId);

    } else {

        $stmt->bind_param('isiss', $teacherId, $dayOfWeek, $periodId, $academicYear, $term);

    }

    $stmt->execute();

    $result = $stmt->get_result();

    $row = $result->fetch_assoc();

    $stmt->close();

    return $row ?: null;

}



function timetableFetchClassSlots(mysqli $conn, string $className, string $academicYear, string $term): array

{

    $stmt = $conn->prepare(

        "SELECT ts.*, s.subject_name, st.full_name AS teacher_name,

                p.period_name, p.start_time, p.end_time, p.is_break, p.order_no

         FROM timetable_slots ts

         LEFT JOIN subjects s ON ts.subject_id = s.id

         LEFT JOIN staff st ON ts.teacher_id = st.id

         LEFT JOIN timetable_periods p ON ts.period_id = p.id

         WHERE ts.class_name = ? AND ts.academic_year = ? AND ts.term = ?

         ORDER BY p.order_no, FIELD(ts.day_of_week, 'Mon','Tue','Wed','Thu','Fri','Sat')"

    );

    $stmt->bind_param('sss', $className, $academicYear, $term);

    $stmt->execute();

    $result = $stmt->get_result();

    $slots = [];

    while ($row = $result->fetch_assoc()) {

        $slots[] = $row;

    }

    $stmt->close();

    return $slots;

}



function timetableFetchTeacherSlots(mysqli $conn, int $teacherId, string $academicYear, string $term): array

{

    $stmt = $conn->prepare(

        "SELECT ts.*, s.subject_name, st.full_name AS teacher_name,

                p.period_name, p.start_time, p.end_time, p.is_break, p.order_no

         FROM timetable_slots ts

         LEFT JOIN subjects s ON ts.subject_id = s.id

         LEFT JOIN staff st ON ts.teacher_id = st.id

         LEFT JOIN timetable_periods p ON ts.period_id = p.id

         WHERE ts.teacher_id = ? AND ts.academic_year = ? AND ts.term = ?

         ORDER BY p.order_no, FIELD(ts.day_of_week, 'Mon','Tue','Wed','Thu','Fri','Sat')"

    );

    $stmt->bind_param('iss', $teacherId, $academicYear, $term);

    $stmt->execute();

    $result = $stmt->get_result();

    $slots = [];

    while ($row = $result->fetch_assoc()) {

        $slots[] = $row;

    }

    $stmt->close();

    return $slots;

}



function timetableFetchAllClassesWithSlots(mysqli $conn, string $academicYear, string $term): array

{

    $stmt = $conn->prepare(

        "SELECT DISTINCT class_name

         FROM timetable_slots

         WHERE academic_year = ? AND term = ?

         ORDER BY class_name"

    );

    $stmt->bind_param('ss', $academicYear, $term);

    $stmt->execute();

    $result = $stmt->get_result();

    $classes = [];

    while ($row = $result->fetch_assoc()) {

        $classes[] = $row['class_name'];

    }

    $stmt->close();

    return $classes;

}



function timetableDateToDayOfWeek(string $date): string

{

    $map = [

        'Mon' => 'Mon',

        'Tue' => 'Tue',

        'Wed' => 'Wed',

        'Thu' => 'Thu',

        'Fri' => 'Fri',

        'Sat' => 'Sat',

        'Sun' => 'Mon',

    ];

    return $map[date('D', strtotime($date))] ?? 'Mon';

}



function timetableGetPeriodType(array $period): string

{

    if (!empty($period['period_type']) && $period['period_type'] !== 'teaching') {

        return (string) $period['period_type'];

    }

    $n = strtolower((string) ($period['period_name'] ?? ''));

    if (preg_match('/\bnap\b/', $n))                      return 'nap';

    if (str_contains($n, 'circle'))                        return 'circle_time';

    if (str_contains($n, 'assembly'))                      return 'assembly';

    if (preg_match('/\blunch\b|launch/', $n))              return 'lunch';

    if (str_contains($n, 'recess'))                        return 'recess';

    if (preg_match('/arrival|settling|reporting/', $n))    return 'arrival';

    if (str_contains($n, 'routine'))                       return 'routines';

    if (str_contains($n, 'silent'))                        return 'silent_hour';

    if (str_contains($n, 'worship'))                       return 'worship';
    if (str_contains($n, 'devotion'))                      return 'devotion';
    if (preg_match('/assignment|checking|marking/', $n))    return 'assignment_checking';
    if (preg_match('/sanitation|clean|sweep/', $n))         return 'sanitation';
    if (preg_match('/sports|games|\bp\.?e\.?\b/', $n))      return 'sports';
    if (str_contains($n, 'library'))                       return 'library';
    if (preg_match('/\bprep\b|\bpreps\b|study/', $n))       return 'prep';
    if (str_contains($n, 'club'))                          return 'club';
    if (str_contains($n, 'inspection'))                    return 'inspection';
    if (preg_match('/roll ?call/', $n))                    return 'roll_call';
    if (str_contains($n, 'story'))                         return 'story_time';
    if (preg_match('/dining|feeding/', $n))                return 'dining';
    if (preg_match('/extra class|remedial/', $n))           return 'extra_classes';
    if (preg_match('/general activit/', $n))               return 'general_activity';
    if (preg_match('/class activit|activit/', $n))         return 'class_activities';

    if (str_contains($n, 'closing'))                       return 'closing_time';

    if ((int) ($period['is_break'] ?? 0) === 1)            return 'break';

    return 'teaching';

}



/**
 * The single source of truth for period types.
 *
 * span=true  -> rendered as one row spanning every day column (it happens
 *               identically every day, e.g. Break, Lunch, Assembly)
 * span=false -> rendered as an individual cell per day (it happens only on
 *               particular days, e.g. Worship on Wednesday, Sports on Friday)
 *
 * Ordered for display; `group` drives the optgroups in the period editor.
 */
function timetableBuiltinPeriodTypes(): array
{
    return [
        // Instruction
        'teaching'            => ['icon' => '📖', 'label' => 'Teaching',              'bg' => '#198754', 'text' => '#ffffff', 'span' => false, 'group' => 'Instruction'],
        'extra_classes'       => ['icon' => '📗', 'label' => 'Extra Classes',         'bg' => '#d1fae5', 'text' => '#065f46', 'span' => false, 'group' => 'Instruction'],
        'prep'                => ['icon' => '✍️', 'label' => 'Prep / Study',          'bg' => '#e0f2fe', 'text' => '#075985', 'span' => false, 'group' => 'Instruction'],
        'assignment_checking' => ['icon' => '📝', 'label' => 'Assignment / Checking', 'bg' => '#ede9fe', 'text' => '#5b21b6', 'span' => false, 'group' => 'Instruction'],
        'library'             => ['icon' => '📔', 'label' => 'Library',               'bg' => '#cffafe', 'text' => '#155e75', 'span' => false, 'group' => 'Instruction'],
        'silent_hour'         => ['icon' => '📚', 'label' => 'Silent Hour',           'bg' => '#cffafe', 'text' => '#155e75', 'span' => true,  'group' => 'Instruction'],

        // Assembly and worship
        'assembly'            => ['icon' => '🎓', 'label' => 'Assembly',              'bg' => '#e0e7ff', 'text' => '#3730a3', 'span' => true,  'group' => 'Assembly & Worship'],
        'worship'             => ['icon' => '🙏', 'label' => 'Worship',               'bg' => '#fef9e7', 'text' => '#7d6608', 'span' => false, 'group' => 'Assembly & Worship'],
        'devotion'            => ['icon' => '🕊️', 'label' => 'Devotion',              'bg' => '#fef9e7', 'text' => '#7d6608', 'span' => true,  'group' => 'Assembly & Worship'],
        'roll_call'           => ['icon' => '📣', 'label' => 'Roll Call',             'bg' => '#e2e8f0', 'text' => '#334155', 'span' => true,  'group' => 'Assembly & Worship'],
        'inspection'          => ['icon' => '🔍', 'label' => 'Inspection',            'bg' => '#e2e8f0', 'text' => '#334155', 'span' => true,  'group' => 'Assembly & Worship'],

        // Activities
        'class_activities'    => ['icon' => '🎨', 'label' => 'Class Activities',      'bg' => '#fce7f3', 'text' => '#9d174d', 'span' => false, 'group' => 'Activities'],
        'general_activity'    => ['icon' => '🤸', 'label' => 'General Activity',      'bg' => '#ffe4e6', 'text' => '#9f1239', 'span' => true,  'group' => 'Activities'],
        'sports'              => ['icon' => '🏅', 'label' => 'Sports / P.E.',         'bg' => '#dcfce7', 'text' => '#166534', 'span' => false, 'group' => 'Activities'],
        'club'                => ['icon' => '🤝', 'label' => 'Club Meeting',          'bg' => '#e0e7ff', 'text' => '#3730a3', 'span' => false, 'group' => 'Activities'],
        'circle_time'         => ['icon' => '⭕', 'label' => 'Circle Time',           'bg' => '#fce7f3', 'text' => '#9d174d', 'span' => true,  'group' => 'Activities'],
        'story_time'          => ['icon' => '📕', 'label' => 'Story Time',            'bg' => '#fae8ff', 'text' => '#86198f', 'span' => true,  'group' => 'Activities'],
        'sanitation'          => ['icon' => '🧹', 'label' => 'Sanitation / Clean-up', 'bg' => '#ecfccb', 'text' => '#3f6212', 'span' => true,  'group' => 'Activities'],
        'routines'            => ['icon' => '📋', 'label' => 'Routines',              'bg' => '#f1f5f9', 'text' => '#475569', 'span' => true,  'group' => 'Activities'],

        // Rest and meals
        'break'               => ['icon' => '☕', 'label' => 'Break',                 'bg' => '#fef3c7', 'text' => '#92400e', 'span' => true,  'group' => 'Rest & Meals'],
        'recess'              => ['icon' => '🏃', 'label' => 'Recess',                'bg' => '#fed7aa', 'text' => '#9a3412', 'span' => true,  'group' => 'Rest & Meals'],
        'lunch'               => ['icon' => '🍽', 'label' => 'Lunch',                 'bg' => '#dcfce7', 'text' => '#166534', 'span' => true,  'group' => 'Rest & Meals'],
        'dining'              => ['icon' => '🍛', 'label' => 'Dining / Feeding',      'bg' => '#dcfce7', 'text' => '#166534', 'span' => true,  'group' => 'Rest & Meals'],
        'nap'                 => ['icon' => '💤', 'label' => 'Nap Time',              'bg' => '#f3e8ff', 'text' => '#6b21a8', 'span' => true,  'group' => 'Rest & Meals'],

        // Start and end of day
        'arrival'             => ['icon' => '🌅', 'label' => 'Arrival / Settling',    'bg' => '#dbeafe', 'text' => '#1e40af', 'span' => true,  'group' => 'Start & End of Day'],
        'closing_time'        => ['icon' => '🔔', 'label' => 'Closing Time',          'bg' => '#fde8e8', 'text' => '#991b1b', 'span' => false, 'group' => 'Start & End of Day'],
    ];
}

/** Settings key holding this school's own period types as {slug: label} JSON. */
const TIMETABLE_CUSTOM_TYPES_KEY = 'timetable_period_types_custom';

/** Turn a free-text label into a storable slug, e.g. "Nature Walk" -> nature_walk. */
function timetableSlugifyPeriodType(string $label): string
{
    $slug = strtolower(trim($label));
    $slug = (string) preg_replace('/[^a-z0-9]+/', '_', $slug);
    return substr(trim($slug, '_'), 0, 30);
}

/**
 * Period types this school has defined for itself, as slug => label.
 *
 * Read from settings, then topped up from any type already saved on a period —
 * so a type still survives in the editor even if the settings row is lost.
 */
function timetableCustomPeriodTypes(mysqli $conn): array
{
    $builtin = timetableBuiltinPeriodTypes();
    $custom  = [];

    try {
        $stmt = $conn->prepare('SELECT setting_value FROM settings WHERE setting_key = ? LIMIT 1');
        $key = TIMETABLE_CUSTOM_TYPES_KEY;
        $stmt->bind_param('s', $key);
        $stmt->execute();
        $row = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        $decoded = json_decode((string) ($row['setting_value'] ?? ''), true);
        if (is_array($decoded)) {
            foreach ($decoded as $slug => $label) {
                $slug = timetableSlugifyPeriodType((string) $slug);
                if ($slug !== '' && !isset($builtin[$slug])) {
                    $custom[$slug] = trim((string) $label) !== '' ? (string) $label : ucwords(str_replace('_', ' ', $slug));
                }
            }
        }
    } catch (Throwable $e) {
        // Settings unreadable - fall through to the period table.
    }

    try {
        $res = $conn->query('SELECT DISTINCT period_type FROM timetable_periods');
        while ($res && ($r = $res->fetch_assoc())) {
            $slug = (string) $r['period_type'];
            if ($slug !== '' && !isset($builtin[$slug]) && !isset($custom[$slug])) {
                $custom[$slug] = ucwords(str_replace('_', ' ', $slug));
            }
        }
    } catch (Throwable $e) {
        // Pre-migration schema without period_type - nothing to add.
    }

    return $custom;
}

/** Built-in types plus this school's own, keyed by slug. */
function timetablePeriodTypes(mysqli $conn): array
{
    $types = timetableBuiltinPeriodTypes();
    foreach (timetableCustomPeriodTypes($conn) as $slug => $label) {
        $types[$slug] = [
            'icon' => '🏷️', 'label' => $label, 'bg' => '#e2e8f0',
            'text' => '#334155', 'span' => true, 'group' => 'School-defined',
        ];
    }
    return $types;
}

/** Display metadata for a type. Unknown slugs are prettified rather than dropped. */
function timetablePeriodTypeMeta(string $type, ?array $types = null): array
{
    $types = $types ?? timetableBuiltinPeriodTypes();
    if (isset($types[$type])) {
        return $types[$type];
    }
    return [
        'icon' => '🏷️', 'label' => ucwords(str_replace('_', ' ', $type)),
        'bg' => '#e2e8f0', 'text' => '#334155', 'span' => true, 'group' => 'School-defined',
    ];
}

/** Persist a school-defined type so the editor offers it again next time. */
function timetableRegisterCustomPeriodType(mysqli $conn, string $slug, string $label): bool
{
    $slug = timetableSlugifyPeriodType($slug);
    if ($slug === '' || isset(timetableBuiltinPeriodTypes()[$slug])) {
        return false;
    }
    $custom = timetableCustomPeriodTypes($conn);
    $custom[$slug] = trim($label) !== '' ? trim($label) : ucwords(str_replace('_', ' ', $slug));
    ksort($custom);

    $json = (string) json_encode($custom, JSON_UNESCAPED_UNICODE);
    $key  = TIMETABLE_CUSTOM_TYPES_KEY;
    $stmt = $conn->prepare(
        'INSERT INTO settings (setting_key, setting_value) VALUES (?, ?)
         ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value)'
    );
    if (!$stmt) {
        return false;
    }
    $stmt->bind_param('ss', $key, $json);
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}

/**

 * Types that can carry a subject lesson. Their cells stay addable in the

 * timetable grid even though they have display metadata of their own.

 */

function timetableLessonCapableTypes(): array

{

    return [

        'teaching', 'extra_classes', 'prep', 'assignment_checking',

        'library', 'sports', 'club', 'class_activities',

    ];

}



function timetableSpecialPeriodMeta(string $type, ?array $types = null): ?array

{

    // null means "an ordinary lesson row" - the grid renders subject cells

    // and, when empty, an Add button.

    if ($type === '' || in_array($type, timetableLessonCapableTypes(), true)) {

        return null;

    }

    return timetablePeriodTypeMeta($type, $types);

}



function timetableBuildGrid(array $periods, array $slots): array

{

    $grid = [];

    foreach ($slots as $slot) {

        $grid[(int) $slot['period_id']][$slot['day_of_week']] = $slot;

    }

    return $grid;

}

