<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\Database;

/**
 * Accès aux galeries pour le back-office (toutes statuts, écritures).
 */
final class GalleryRepository
{
    /** @return list<array<string,mixed>> */
    public static function all(): array
    {
        $pdo = Database::connection();
        return $pdo->query(
            "SELECT g.id, g.slug, g.title, g.status, g.sort_order,
                    (SELECT COUNT(*) FROM gallery_images gi WHERE gi.gallery_id = g.id) AS image_count
             FROM galleries g
             ORDER BY g.sort_order ASC, g.title ASC"
        )->fetchAll();
    }

    /** @return array<string,mixed>|null */
    public static function find(int $id): ?array
    {
        $pdo  = Database::connection();
        $stmt = $pdo->prepare('SELECT * FROM galleries WHERE id = :id');
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch();
        return $row === false ? null : $row;
    }

    /** @return list<array<string,mixed>> Images (id, name) pour le choix de couverture. */
    public static function imagesFor(int $galleryId): array
    {
        $pdo  = Database::connection();
        $stmt = $pdo->prepare('SELECT id, name FROM gallery_images WHERE gallery_id = :id ORDER BY sort_order ASC, id ASC');
        $stmt->execute(['id' => $galleryId]);
        return $stmt->fetchAll();
    }

    public static function uniqueSlug(string $base, ?int $exceptId = null): string
    {
        $pdo  = Database::connection();
        $base = $base !== '' ? $base : 'galerie';
        $slug = $base;
        $n    = 1;

        while (true) {
            $sql = 'SELECT id FROM galleries WHERE slug = :slug' . ($exceptId !== null ? ' AND id <> :except' : '') . ' LIMIT 1';
            $stmt = $pdo->prepare($sql);
            $params = ['slug' => $slug];
            if ($exceptId !== null) {
                $params['except'] = $exceptId;
            }
            $stmt->execute($params);

            if ($stmt->fetchColumn() === false) {
                return $slug;
            }
            $n++;
            $slug = $base . '-' . $n;
        }
    }

    public static function nextSortOrder(): int
    {
        $pdo = Database::connection();
        return (int) $pdo->query('SELECT COALESCE(MAX(sort_order), 0) FROM galleries')->fetchColumn() + 1;
    }

    /** @param array<string,mixed> $data */
    public static function create(array $data): int
    {
        $pdo  = Database::connection();
        $stmt = $pdo->prepare(
            "INSERT INTO galleries (slug, title, intro_html, sort_order, status, seo_title, seo_description, seo_noindex)
             VALUES (:slug, :title, :intro, :sort_order, :status, :seo_title, :seo_description, :seo_noindex)"
        );
        $stmt->execute([
            'slug'            => $data['slug'],
            'title'           => $data['title'],
            'intro'           => $data['intro_html'],
            'sort_order'      => $data['sort_order'],
            'status'          => $data['status'],
            'seo_title'       => $data['seo_title'],
            'seo_description' => $data['seo_description'],
            'seo_noindex'     => $data['seo_noindex'],
        ]);

        return (int) $pdo->lastInsertId();
    }

    /** @param array<string,mixed> $data */
    public static function update(int $id, array $data): void
    {
        $pdo  = Database::connection();
        $stmt = $pdo->prepare(
            "UPDATE galleries SET
                slug = :slug, title = :title, intro_html = :intro, status = :status,
                cover_image_id = :cover, seo_title = :seo_title, seo_description = :seo_description,
                seo_noindex = :seo_noindex
             WHERE id = :id"
        );
        $stmt->execute([
            'slug'            => $data['slug'],
            'title'           => $data['title'],
            'intro'           => $data['intro_html'],
            'status'          => $data['status'],
            'cover'           => $data['cover_image_id'],
            'seo_title'       => $data['seo_title'],
            'seo_description' => $data['seo_description'],
            'seo_noindex'     => $data['seo_noindex'],
            'id'              => $id,
        ]);
    }

    public static function delete(int $id): void
    {
        $pdo = Database::connection();
        $pdo->prepare('DELETE FROM galleries WHERE id = :id')->execute(['id' => $id]);
    }

    /** Déplace une galerie d'un cran (échange de sort_order avec la voisine). */
    public static function move(int $id, string $direction): void
    {
        $current = self::find($id);
        if ($current === null) {
            return;
        }

        $pdo = Database::connection();
        $cmp = $direction === 'up' ? '<' : '>';
        $ord = $direction === 'up' ? 'DESC' : 'ASC';

        $stmt = $pdo->prepare(
            "SELECT id, sort_order FROM galleries WHERE sort_order {$cmp} :order ORDER BY sort_order {$ord} LIMIT 1"
        );
        $stmt->execute(['order' => $current['sort_order']]);
        $neighbour = $stmt->fetch();
        if ($neighbour === false) {
            return;
        }

        $upd = $pdo->prepare('UPDATE galleries SET sort_order = :order WHERE id = :id');
        $upd->execute(['order' => $neighbour['sort_order'], 'id' => $id]);
        $upd->execute(['order' => $current['sort_order'], 'id' => $neighbour['id']]);
    }

    /**
     * Réordonne les galeries selon la liste d'ids fournie (position 1..n).
     * @param list<int> $ids
     */
    public static function reorder(array $ids): void
    {
        $pdo  = Database::connection();
        $stmt = $pdo->prepare('UPDATE galleries SET sort_order = :order WHERE id = :id');
        $position = 1;
        foreach ($ids as $id) {
            $stmt->execute(['order' => $position, 'id' => (int) $id]);
            $position++;
        }
    }

    public static function setStatus(int $id, string $status): void
    {
        Database::connection()
            ->prepare('UPDATE galleries SET status = :s WHERE id = :id')
            ->execute(['s' => $status, 'id' => $id]);
    }
}
