<?php

namespace App\Models;

use App\Core\Model;

/**
 * ITSWEBER CMS – Event Model (Phase 4b)
 *
 * Dual-status design:
 *   publish_status  ENUM('draft', 'published')
 *   event_status    ENUM('scheduled', 'postponed', 'cancelled')
 */
class Event extends Model
{
    protected static string $table = 'iw_events';

    // -------------------------------------------------------------------------
    // Admin queries
    // -------------------------------------------------------------------------

    /**
     * All events with category and media data for admin listing.
     * Supports search + status filters + sorting by start_date.
     *
     * @param string $search       Full-text search on title/location.
     * @param string $publishStatus Filter by publish_status ('', 'draft', 'published').
     * @param string $eventStatus   Filter by event_status ('', 'scheduled', 'postponed', 'cancelled').
     * @param int    $categoryId    Filter by category (0 = all).
     * @param int    $featured      Filter: -1 = all, 1 = featured only, 0 = non-featured only.
     */
    public static function adminList(
        string $search        = '',
        string $publishStatus = '',
        string $eventStatus   = '',
        int    $categoryId    = 0,
        int    $featured      = -1
    ): array {
        $where  = [];
        $params = [];

        if ($search !== '') {
            $where[]          = '(e.title LIKE :search OR e.location LIKE :search)';
            $params['search'] = '%' . $search . '%';
        }

        if ($publishStatus !== '') {
            $where[]                   = 'e.publish_status = :publish_status';
            $params['publish_status'] = $publishStatus;
        }

        if ($eventStatus !== '') {
            $where[]                  = 'e.event_status = :event_status';
            $params['event_status']  = $eventStatus;
        }

        if ($categoryId > 0) {
            $where[]               = 'e.category_id = :category_id';
            $params['category_id'] = $categoryId;
        }

        if ($featured === 1) {
            $where[] = 'e.is_featured = 1';
        } elseif ($featured === 0) {
            $where[] = 'e.is_featured = 0';
        }

        $whereClause = $where ? ('WHERE ' . implode(' AND ', $where)) : '';

        return static::db()->fetchAll(
            "SELECT e.*,
                    c.name AS category_name,
                    m.file_path AS media_path,
                    m.alt_text  AS media_alt
             FROM   iw_events e
             LEFT JOIN iw_event_categories c ON c.id = e.category_id
             LEFT JOIN iw_media m            ON m.id = e.media_id
             {$whereClause}
             ORDER BY e.start_date DESC, e.start_time DESC",
            $params
        );
    }

    /**
     * Find a single event by ID for admin (no status filter).
     */
    public static function findForAdmin(int $id): ?array
    {
        return static::db()->fetch(
            "SELECT e.*,
                    c.name AS category_name,
                    m.file_path AS media_path,
                    m.alt_text  AS media_alt,
                    m.title     AS media_title
             FROM   iw_events e
             LEFT JOIN iw_event_categories c ON c.id = e.category_id
             LEFT JOIN iw_media m            ON m.id = e.media_id
             WHERE  e.id = :id
             LIMIT  1",
            ['id' => $id]
        );
    }

    // -------------------------------------------------------------------------
    // Frontend queries
    // -------------------------------------------------------------------------

    /**
     * Published upcoming/current events for the frontend listing.
     * Includes events whose start_date is today or in the future,
     * ordered by start_date ascending (soonest first).
     *
     * @param int    $limit
     * @param int    $offset
     * @param int    $categoryId  0 = all
     * @param bool   $featuredOnly
     */
    public static function publishedUpcoming(
        int  $limit        = 20,
        int  $offset       = 0,
        int  $categoryId   = 0,
        bool $featuredOnly = false
    ): array {
        $where  = ["e.publish_status = 'published'", "e.start_date >= CURDATE()"];
        $params = [];

        if ($categoryId > 0) {
            $where[]               = 'e.category_id = :category_id';
            $params['category_id'] = $categoryId;
        }

        if ($featuredOnly) {
            $where[] = 'e.is_featured = 1';
        }

        $whereClause = 'WHERE ' . implode(' AND ', $where);

        return static::db()->fetchAll(
            "SELECT e.*,
                    c.name AS category_name,
                    c.slug AS category_slug,
                    m.file_path AS media_path,
                    m.alt_text  AS media_alt
             FROM   iw_events e
             LEFT JOIN iw_event_categories c ON c.id = e.category_id
             LEFT JOIN iw_media m            ON m.id = e.media_id
             {$whereClause}
             ORDER BY e.start_date ASC, e.start_time ASC
             LIMIT " . (int) $limit . " OFFSET " . (int) $offset,
            $params
        );
    }

    /**
     * Count published upcoming events.
     */
    public static function countPublishedUpcoming(int $categoryId = 0): int
    {
        $where  = ["e.publish_status = 'published'", "e.start_date >= CURDATE()"];
        $params = [];

        if ($categoryId > 0) {
            $where[]               = 'e.category_id = :category_id';
            $params['category_id'] = $categoryId;
        }

        $whereClause = 'WHERE ' . implode(' AND ', $where);

        return (int) static::db()->fetchColumn(
            "SELECT COUNT(*) FROM iw_events e {$whereClause}",
            $params
        );
    }

    /**
     * Find a single published event by slug for the frontend detail view.
     */
    public static function findPublished(string $slug): ?array
    {
        return static::db()->fetch(
            "SELECT e.*,
                    c.name AS category_name,
                    c.slug AS category_slug,
                    m.file_path AS media_path,
                    m.alt_text  AS media_alt
             FROM   iw_events e
             LEFT JOIN iw_event_categories c ON c.id = e.category_id
             LEFT JOIN iw_media m            ON m.id = e.media_id
             WHERE  e.slug = :slug
               AND  e.publish_status = 'published'
             LIMIT  1",
            ['slug' => $slug]
        );
    }

    /**
     * Featured published upcoming events – convenience shortcut.
     */
    public static function featuredUpcoming(int $limit = 4): array
    {
        return static::publishedUpcoming($limit, 0, 0, true);
    }

    /**
     * Next upcoming published events for dashboard widget (lightweight).
     */
    public static function upcomingCompact(int $limit = 3): array
    {
        $limit = max(1, min(20, $limit));
        return static::db()->fetchAll(
            "SELECT id, title, slug, start_date, start_time, event_status
             FROM iw_events
             WHERE publish_status = 'published'
               AND start_date >= CURDATE()
             ORDER BY start_date ASC, start_time ASC
             LIMIT {$limit}"
        );
    }
}
