<?php
/// file: app/Models/Project.php

declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use PDO;

final class Project extends Model
{
    /**
     * Stored in DB `category`; must match work index filter keys and translation keys work.cat_*.
     *
     * @return list<string>
     */
    public static function categorySlugs(): array
    {
        return [
            'immersive web',
            'digital campaigns',
            'motion systems',
            'brand platforms',
            'launch experiences',
            'editorial experiences',
        ];
    }

    public static function categoryMessageKey(string $slug): string
    {
        return 'work.cat_' . str_replace(' ', '_', $slug);
    }

    protected static function table(): string
    {
        return 'projects';
    }

    /** @return array<string, mixed>|null */
    public static function findBySlug(string $slug): ?array
    {
        return parent::findBy('slug', $slug);
    }

    /** @return list<array<string, mixed>> */
    public static function published(int $limit = 100): array
    {
        $st = self::pdo()->prepare(
            'SELECT * FROM projects WHERE status = ? ORDER BY sort_order ASC, year DESC LIMIT ' . (int) $limit
        );
        $st->execute(['published']);
        return $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    /** @return list<array<string, mixed>> */
    public static function filterByCategory(?string $category): array
    {
        if ($category === null || $category === '' || $category === 'all') {
            return self::published();
        }
        $st = self::pdo()->prepare(
            'SELECT * FROM projects WHERE status = ? AND category = ? ORDER BY sort_order ASC, year DESC'
        );
        $st->execute(['published', $category]);
        return $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    /** @return list<array<string, mixed>> */
    public static function related(int $excludeId, ?string $category, int $limit = 3): array
    {
        if ($category !== null && $category !== '') {
            $st = self::pdo()->prepare(
                'SELECT * FROM projects WHERE status = ? AND id != ? AND category = ? ORDER BY year DESC LIMIT ' . (int) $limit
            );
            $st->execute(['published', $excludeId, $category]);
        } else {
            $st = self::pdo()->prepare(
                'SELECT * FROM projects WHERE status = ? AND id != ? ORDER BY year DESC LIMIT ' . (int) $limit
            );
            $st->execute(['published', $excludeId]);
        }
        return $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    /** @return array<string, mixed>|null */
    public static function nextProject(int $id): ?array
    {
        $pdo = self::pdo();
        $st = $pdo->prepare('SELECT sort_order FROM projects WHERE id = ? LIMIT 1');
        $st->execute([$id]);
        $so = $st->fetchColumn();
        if ($so === false) {
            return null;
        }
        $sort = (int) $so;
        $st2 = $pdo->prepare(
            'SELECT * FROM projects WHERE status = ? AND (sort_order > ? OR (sort_order = ? AND id > ?)) ORDER BY sort_order ASC, id ASC LIMIT 1'
        );
        $st2->execute(['published', $sort, $sort, $id]);
        $row = $st2->fetch(PDO::FETCH_ASSOC);
        if ($row) {
            return $row;
        }
        $st3 = $pdo->prepare(
            'SELECT * FROM projects WHERE status = ? AND id != ? ORDER BY sort_order ASC, id ASC LIMIT 1'
        );
        $st3->execute(['published', $id]);
        $fallback = $st3->fetch(PDO::FETCH_ASSOC);
        return $fallback ?: null;
    }
}
