SkinbaseNova/app/Http/Controllers/User/TodayInHistoryController.php

<?php

namespace App\Http\Controllers\User;

use App\Http\Controllers\Controller;
use App\Models\Artwork;
use App\Support\AvatarUrl;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Str;

class TodayInHistoryController extends Controller
{
    public function index(Request $request)
    {
        $perPage   = 36;
        $artworks  = null;
        $today     = now();

        // ── Strategy 1: legacy featured_works table (historical data from old site) ─
        $hasFeaturedWorks = false;
        try { $hasFeaturedWorks = Schema::hasTable('featured_works'); } catch (\Throwable) {}

        if ($hasFeaturedWorks) {
            try {
                $artworks = DB::table('featured_works as f')
                    ->join('artworks as a', 'f.artwork_id', '=', 'a.id')
                    ->where('a.is_approved', true)
                    ->where('a.is_public', true)
                    ->whereNull('a.deleted_at')
                    ->whereRaw('MONTH(f.post_date) = ?', [$today->month])
                    ->whereRaw('DAY(f.post_date) = ?',   [$today->day])
                    ->select('a.id', 'a.title as name', 'a.slug', 'a.hash', 'a.thumb_ext',
                             DB::raw('f.post_date as featured_date'))
                    ->orderBy('f.post_date', 'desc')
                    ->paginate($perPage);
            } catch (\Throwable $e) {
                $artworks = null;
            }
        }

        // ── Strategy 2: new artwork_features table ───────────────────────────────
        if (!$artworks || $artworks->total() === 0) {
            try {
                $artworks = DB::table('artwork_features as f')
                    ->join('artworks as a', 'f.artwork_id', '=', 'a.id')
                    ->where('f.is_active', true)
                    ->where('a.is_approved', true)
                    ->where('a.is_public', true)
                    ->whereNull('a.deleted_at')
                    ->whereNotNull('a.published_at')
                    ->whereRaw('MONTH(f.featured_at) = ?', [$today->month])
                    ->whereRaw('DAY(f.featured_at) = ?',   [$today->day])
                    ->select('a.id', 'a.title as name', 'a.slug', 'a.hash', 'a.thumb_ext',
                             DB::raw('f.featured_at as featured_date'))
                    ->orderBy('f.featured_at', 'desc')
                    ->paginate($perPage);
            } catch (\Throwable $e) {
                $artworks = null;
            }
        }

        // ── Enrich with CDN thumbnails (batch load to avoid N+1) ─────────────────
        if ($artworks && method_exists($artworks, 'getCollection') && $artworks->count() > 0) {
            $ids = $artworks->getCollection()->pluck('id')->filter()->map(fn ($id) => (int) $id)->all();
            $modelsById = Artwork::query()
                ->with([
                    'user:id,name,username',
                    'categories' => function ($query) {
                        $query->select('categories.id', 'categories.name', 'categories.slug', 'categories.sort_order');
                    },
                ])
                ->whereIn('id', $ids)
                ->get()
                ->keyBy('id');

            $artworks->getCollection()->transform(function ($row) use ($modelsById) {
                /** @var ?Artwork $art */
                $art = $modelsById->get($row->id);
                $row->slug = $row->slug ?? Str::slug($row->name ?? '');

                if ($art) {
                    $primaryCategory = $art->categories?->sortBy('sort_order')->first();
                    $author = $art->user;

                    try {
                        $present = \App\Services\ThumbnailPresenter::present($art, 'md');
                        $row->thumb_url = $present['url'];
                        $row->thumb_srcset = $present['srcset'] ?? $present['url'];
                    } catch (\Throwable $e) {
                        $row->thumb_url = $art->thumbUrl('md') ?? 'https://files.skinbase.org/default/missing_md.webp';
                        $row->thumb_srcset = $row->thumb_url;
                    }

                    $row->url = url('/art/' . $art->id . '/' . ($art->slug ?: Str::slug($art->title ?: ($row->name ?? 'artwork'))));
                    $row->art_url = $row->url;
                    $row->name = $art->title ?: ($row->name ?? 'Untitled');
                    $row->slug = $art->slug ?: $row->slug;
                    $row->width = $art->width;
                    $row->height = $art->height;
                    $row->content_type_name = $primaryCategory?->contentType?->name ?? '';
                    $row->content_type_slug = $primaryCategory?->contentType?->slug ?? '';
                    $row->category_name = $primaryCategory->name ?? '';
                    $row->category_slug = $primaryCategory->slug ?? '';
                    $row->uname = $author->name ?? 'Skinbase';
                    $row->username = $author->username ?? $author->name ?? '';
                    $row->avatar_url = $author
                        ? AvatarUrl::forUser((int) $author->getKey(), null, 64)
                        : AvatarUrl::default();
                } else {
                    $row->thumb_url = 'https://files.skinbase.org/default/missing_md.webp';
                    $row->thumb_srcset = $row->thumb_url;
                    $row->url = url('/art/' . $row->id . '/' . ($row->slug ?: Str::slug($row->name ?? 'artwork')));
                    $row->art_url = $row->url;
                    $row->name = $row->name ?? 'Untitled';
                    $row->content_type_name = $row->content_type_name ?? '';
                    $row->content_type_slug = $row->content_type_slug ?? '';
                    $row->category_name = $row->category_name ?? '';
                    $row->category_slug = $row->category_slug ?? '';
                    $row->uname = $row->uname ?? 'Skinbase';
                    $row->username = $row->username ?? '';
                    $row->avatar_url = $row->avatar_url ?? AvatarUrl::default();
                    $row->width = $row->width ?? null;
                    $row->height = $row->height ?? null;
                }

                return $row;
            });
        }

        return view('legacy::today-in-history', [
            'artworks'   => $artworks,
            'page_title' => 'Popular on this day in history',
            'todayLabel' => $today->format('F j'),
        ]);
    }
}