<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\Database;
use PDO;
use Throwable;

/**
 * Lecture des galeries publiées (front public). Galeries plates (D-23).
 */
final class Gallery
{
    /** @var list<array<string,mixed>>|null */
    private static ?array $publishedCache = null;

    /** @return list<array<string,mixed>> */
    public static function publishedList(): array
    {
        if (self::$publishedCache !== null) {
            return self::$publishedCache;
        }

        try {
            $pdo  = Database::connection();
            // Couverture = image de couverture choisie, sinon 1re image publiée.
            // Seules les galeries publiées contenant au moins une image publiée sont listées.
            $rows = $pdo->query(
                "SELECT g.id, g.slug, g.title, g.intro_html, g.id AS cover_gallery_id,
                        COALESCE(
                            (SELECT ci.filename FROM gallery_images ci WHERE ci.id = g.cover_image_id AND ci.status = 'published'),
                            (SELECT fi.filename FROM gallery_images fi WHERE fi.gallery_id = g.id AND fi.status = 'published' ORDER BY fi.sort_order ASC, fi.id ASC LIMIT 1)
                        ) AS cover_filename
                 FROM galleries g
                 WHERE g.status = 'published'
                   AND EXISTS (SELECT 1 FROM gallery_images x WHERE x.gallery_id = g.id AND x.status = 'published')
                 ORDER BY g.sort_order ASC, g.title ASC"
            )->fetchAll();
            self::$publishedCache = is_array($rows) ? $rows : [];
        } catch (Throwable) {
            self::$publishedCache = [];
        }

        return self::$publishedCache;
    }

    /** @return list<string> slugs publiés et indexables (pour le sitemap). */
    public static function publishedIndexableSlugs(): array
    {
        try {
            $pdo  = Database::connection();
            $rows = $pdo->query(
                "SELECT slug FROM galleries g WHERE g.status = 'published' AND g.seo_noindex = 0
                 AND EXISTS (SELECT 1 FROM gallery_images x WHERE x.gallery_id = g.id AND x.status = 'published')
                 ORDER BY g.sort_order ASC, g.title ASC"
            )->fetchAll(PDO::FETCH_COLUMN);
            return is_array($rows) ? $rows : [];
        } catch (Throwable) {
            return [];
        }
    }

    /** @return array<string,mixed>|null */
    public static function findPublishedBySlug(string $slug): ?array
    {
        try {
            $pdo  = Database::connection();
            $stmt = $pdo->prepare("SELECT * FROM galleries WHERE slug = :slug AND status = 'published' LIMIT 1");
            $stmt->execute(['slug' => $slug]);
            $row = $stmt->fetch();
            return $row === false ? null : $row;
        } catch (Throwable) {
            return null;
        }
    }
}
