<?php

namespace App\Models;

use App\Core\Model;

/**
 * ITSWEBER CMS – Media Model
 *
 * Represents a record in iw_media – the central media store.
 * Provides search/filter capabilities for the media manager and picker.
 */
class Media extends Model
{
    protected static string $table = 'iw_media';

    /**
     * Search media with optional filters.
     *
     * @param string   $q         Search query (matches original_name or title).
     * @param int|null $folderId  NULL = all, 0 = unassigned, 1+ = specific folder.
     * @param string   $type      MIME-type prefix filter (e.g. 'image', 'video').
     * @param int      $page      1-based page number.
     * @param int      $perPage   Items per page.
     *
     * @return array{items: array, total: int}
     */
    public static function search(
        string $q        = '',
        ?int   $folderId = null,
        string $type     = '',
        int    $page     = 1,
        int    $perPage  = 40
    ): array {
        $where  = [];
        $params = [];

        if ($q !== '') {
            $where[]     = '(m.original_name LIKE :q OR m.title LIKE :q)';
            $params['q'] = '%' . $q . '%';
        }

        if ($folderId === 0) {
            $where[] = 'm.folder_id IS NULL';
        } elseif ($folderId !== null) {
            $where[]               = 'm.folder_id = :folder_id';
            $params['folder_id']   = $folderId;
        }

        if ($type !== '') {
            // Primary: match by MIME type prefix (e.g. "image" → "image/%")
            // Fallback: also include files with image extensions but wrong/missing MIME type
            if ($type === 'image') {
                $imageExts = "'jpg','jpeg','png','gif','webp','svg','ico','avif'";
                $where[] = "(m.file_type LIKE :type OR LOWER(SUBSTRING_INDEX(m.file_path, '.', -1)) IN ({$imageExts}))";
            } else {
                $where[] = 'm.file_type LIKE :type';
            }
            $params['type'] = $type . '/%';
        }

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

        $total = (int) static::db()->fetchColumn(
            "SELECT COUNT(*) FROM iw_media m {$whereClause}",
            $params
        );

        $offset = max(0, ($page - 1) * $perPage);
        $items  = static::db()->fetchAll(
            "SELECT m.*, f.name AS folder_name
             FROM   iw_media m
             LEFT JOIN iw_media_folders f ON f.id = m.folder_id
             {$whereClause}
             ORDER  BY m.created_at DESC
             LIMIT  {$perPage} OFFSET {$offset}",
            $params
        );

        return ['items' => $items, 'total' => $total];
    }

    /**
     * Count media items not assigned to any folder.
     */
    public static function countUnassigned(): int
    {
        return (int) static::db()->fetchColumn(
            "SELECT COUNT(*) FROM iw_media WHERE folder_id IS NULL"
        );
    }

    /**
     * Get images only (filter by MIME type 'image/*').
     * Used for gallery media picker (Phase 2.2).
     *
     * @param int|null $folderId NULL = all, 0 = unassigned, 1+ = specific folder
     * @return array Array of image records with folder info
     */
    public static function imagesOnly(?int $folderId = null): array
    {
        $imageExts = "'jpg','jpeg','png','gif','webp','svg','ico','avif'";
        $where  = ["(m.file_type LIKE :type OR LOWER(SUBSTRING_INDEX(m.file_path, '.', -1)) IN ({$imageExts}))"];
        $params = ['type' => 'image/%'];

        if ($folderId === 0) {
            $where[] = 'm.folder_id IS NULL';
        } elseif ($folderId !== null) {
            $where[] = 'm.folder_id = :folder_id';
            $params['folder_id'] = $folderId;
        }

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

        return static::db()->fetchAll(
            "SELECT m.*, f.name AS folder_name
             FROM iw_media m
             LEFT JOIN iw_media_folders f ON f.id = m.folder_id
             WHERE {$whereClause}
             ORDER BY m.created_at DESC",
            $params
        );
    }
}
