<?php

namespace App\Models;

use App\Core\Model;

/**
 * ITSWEBER CMS – Download Model (Phase 8)
 *
 * Featured image is referenced via media_id (FK → iw_media).
 * The downloadable file is stored as a relative path in file_path.
 */
class Download extends Model
{
    protected static string $table = 'iw_downloads';

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

    /**
     * All downloads with category and media data for admin listing.
     * Supports search + status + category filters.
     */
    public static function adminList(
        string $search     = '',
        string $status     = '',
        int    $categoryId = 0
    ): array {
        $where  = [];
        $params = [];

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

        if ($status !== '') {
            $where[]            = 'd.status = :status';
            $params['status']   = $status;
        }

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

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

        return static::db()->fetchAll(
            "SELECT d.*,
                    c.name AS category_name,
                    m.file_path AS media_path,
                    m.alt_text  AS media_alt
             FROM   iw_downloads d
             LEFT JOIN iw_download_categories c ON c.id = d.category_id
             LEFT JOIN iw_media m               ON m.id = d.media_id
             {$whereClause}
             ORDER BY d.sort_order ASC, d.created_at DESC",
            $params
        );
    }

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

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

    /**
     * Published downloads for the frontend listing.
     *
     * @param int    $limit
     * @param int    $offset
     * @param int    $categoryId  0 = all
     * @param string $search      Full-text search on title/excerpt
     */
    public static function published(
        int    $limit      = 12,
        int    $offset     = 0,
        int    $categoryId = 0,
        string $search     = ''
    ): array {
        $limit  = max(1, $limit);
        $offset = max(0, $offset);

        $where  = ["d.status = 'published'"];
        $params = [];

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

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

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

        return static::db()->fetchAll(
            "SELECT d.*,
                    c.name AS category_name,
                    c.slug AS category_slug,
                    m.file_path AS media_path,
                    m.alt_text  AS media_alt
             FROM   iw_downloads d
             LEFT JOIN iw_download_categories c ON c.id = d.category_id
             LEFT JOIN iw_media m               ON m.id = d.media_id
             {$whereClause}
             ORDER BY d.sort_order ASC, d.created_at DESC
             LIMIT {$limit} OFFSET {$offset}",
            $params
        );
    }

    /**
     * Count published downloads.
     */
    public static function countPublished(int $categoryId = 0, string $search = ''): int
    {
        $where  = ["d.status = 'published'"];
        $params = [];

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

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

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

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

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