<?php

namespace App\Models;

use App\Models\BaseModel;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class CalendarEvent extends BaseModel
{
    protected $table = 'calendar_events';

    // ✅ CI uses created_at / updated_at
    public $timestamps = true;

    protected $fillable = [
        'title',
        'date',
        'description',
        'notify_parent',
        'notify_admin',
        'notify_teacher',
        'no_school',
        'semester',
        'school_year',
        'notification_sent',
        'created_at',
        'updated_at',
    ];

    protected $casts = [
        // If your column is DATE use 'date', if it's DATETIME use 'datetime'
        'date'           => 'date',
        'notify_parent'  => 'boolean',
        'notify_admin'   => 'boolean',
        'notify_teacher' => 'boolean',
        'no_school'      => 'integer',
    ];

    private static ?bool $hasEventTypeColumn = null;

    /* =========================
     * CI method equivalents
     * ========================= */

    public static function getEvents()
    {
        $events = static::query()->get()->toArray();
        return static::withNoSchoolFlag($events);
    }

    public static function getEventByDate(string $date): array
    {
        $events = static::query()->where('date', $date)->get()->toArray();
        return static::withNoSchoolFlag($events);
    }

    public static function addEvent(array $data): int
    {
        $row = static::create($data);
        return (int) $row->id;
    }

    public static function getEventsBySchoolYear(string $schoolYear): array
    {
        $events = static::query()
            ->where('school_year', $schoolYear)
            ->orderBy('date', 'asc')
            ->get()
            ->toArray();

        return static::withNoSchoolFlag($events);
    }

    public static function getEventsBySchoolYearAndSemester(string $schoolYear, ?string $semester = null): array
    {
        $q = static::query()->where('school_year', $schoolYear);

        if (is_string($semester) && $semester !== '') {
            $q->where('semester', $semester);
        }

        $events = $q->orderBy('date', 'asc')->get()->toArray();

        return static::withNoSchoolFlag($events);
    }

    public static function supportsEventType(): bool
    {
        if (static::$hasEventTypeColumn === null) {
            static::$hasEventTypeColumn = static::columnExists((new static)->getTable(), 'event_type');
        }
        return (bool) static::$hasEventTypeColumn;
    }

    /* =========================
     * Helpers (ported from CI)
     * ========================= */

    /**
     * Add computed no_school flag to each event without requiring DB schema changes.
     */
    protected static function withNoSchoolFlag(array $events): array
    {
        return array_map(function ($event) {
            $event = (array) $event;

            // Respect DB value if present; otherwise infer heuristically
            if (array_key_exists('no_school', $event)) {
                $event['no_school'] = (int) ($event['no_school'] ?? 0);
            } else {
                $event['no_school'] = static::isNoSchoolEvent($event) ? 1 : 0;
            }

            return $event;
        }, $events);
    }

    /**
     * Heuristic to determine "no school" days from event text.
     */
    protected static function isNoSchoolEvent(array $event): bool
    {
        $title = strtolower((string)($event['title'] ?? ''));
        $desc  = strtolower((string)($event['description'] ?? ''));
        $text  = $title . ' ' . $desc;

        $keywords = ['no school', 'no-school', 'cancel', 'vacation', 'holiday', 'break'];

        foreach ($keywords as $kw) {
            if ($kw !== '' && strpos($text, $kw) !== false) {
                return true;
            }
        }

        return false;
    }

    protected static function columnExists(string $table, string $column): bool
    {
        $db = DB::connection();
        if ($db->getDriverName() === 'sqlite') {
            return Schema::hasColumn($table, $column);
        }

        $database = $db->getDatabaseName();

        $count = $db->table('information_schema.columns')
            ->where('table_schema', $database)
            ->where('table_name', $table)
            ->where('column_name', $column)
            ->count();

        return $count > 0;
    }
}
