<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use PDO;

/**
 * BlogPost Model
 *
 * Handles blog post data retrieval with pagination, slug lookups,
 * and author-based filtering.
 */
class BlogPost extends Model
{
    /**
     * The database table associated with the model.
     */
    protected string $table = 'blog_posts';

    /**
     * Get published blog posts with pagination.
     *
     * Returns posts that are published and have a published_at date
     * that is in the past (or now), ordered by most recent first.
     *
     * @param int $page The current page number (1-based)
     * @param int $perPage Number of posts per page
     * @return array<int, array<string, mixed>> Array of blog post records
     */
    public function getPublished(int $page = 1, int $perPage = 9): array
    {
        $offset = ($page - 1) * $perPage;

        $sql = sprintf(
            'SELECT bp.*, u.name AS author_name
             FROM `%s` bp
             LEFT JOIN `users` u ON bp.author_id = u.id
             WHERE bp.is_published = :published
               AND (bp.published_at IS NULL OR bp.published_at <= NOW())
             ORDER BY bp.published_at DESC, bp.created_at DESC
             LIMIT :limit OFFSET :offset',
            $this->table
        );

        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(':published', 1, PDO::PARAM_INT);
        $stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    /**
     * Get a published blog post by its slug.
     *
     * @param string $slug The URL-friendly slug to look up
     * @return array<string, mixed>|null The blog post record or null if not found
     */
    public function getBySlug(string $slug): ?array
    {
        $sql = sprintf(
            'SELECT bp.*, u.name AS author_name
             FROM `%s` bp
             LEFT JOIN `users` u ON bp.author_id = u.id
             WHERE bp.slug = :slug AND bp.is_published = :published
             LIMIT 1',
            $this->table
        );

        $stmt = $this->db->prepare($sql);
        $stmt->execute([':slug' => $slug, ':published' => 1]);

        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        return $result !== false ? $result : null;
    }

    /**
     * Get the most recent published blog posts.
     *
     * @param int $limit Maximum number of posts to return
     * @return array<int, array<string, mixed>> Array of recent blog posts
     */
    public function getRecent(int $limit = 5): array
    {
        $sql = sprintf(
            'SELECT bp.*, u.name AS author_name
             FROM `%s` bp
             LEFT JOIN `users` u ON bp.author_id = u.id
             WHERE bp.is_published = :published
               AND (bp.published_at IS NULL OR bp.published_at <= NOW())
             ORDER BY bp.published_at DESC, bp.created_at DESC
             LIMIT :limit',
            $this->table
        );

        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(':published', 1, PDO::PARAM_INT);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    /**
     * Get all published blog posts by a specific author.
     *
     * @param int $authorId The author (user) ID to filter by
     * @return array<int, array<string, mixed>> Array of blog posts by the author
     */
    public function getByAuthor(int $authorId): array
    {
        $sql = sprintf(
            'SELECT * FROM `%s`
             WHERE `author_id` = :author_id AND `is_published` = :published
             ORDER BY `created_at` DESC',
            $this->table
        );

        $stmt = $this->db->prepare($sql);
        $stmt->execute([':author_id' => $authorId, ':published' => 1]);

        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    /**
     * Get the total count of published blog posts.
     *
     * @return int The count of published posts
     */
    public function getPublishedCount(): int
    {
        $sql = sprintf(
            'SELECT COUNT(*) AS total FROM `%s`
             WHERE `is_published` = :published
               AND (`published_at` IS NULL OR `published_at` <= NOW())',
            $this->table
        );

        $stmt = $this->db->prepare($sql);
        $stmt->execute([':published' => 1]);

        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        return (int) ($result['total'] ?? 0);
    }
}
