<?php

namespace App\Models;

use App\Core\Model;

class BlogPost extends Model
{
    protected static string $table = 'iw_blog_posts';

    /**
     * Most recent posts for the admin dashboard widget (lightweight, limited fields).
     */
    public static function recent(int $limit = 5): array
    {
        $limit = max(1, min(50, $limit));
        return static::db()->fetchAll(
            "SELECT id, title, slug, status, created_at
             FROM iw_blog_posts
             ORDER BY created_at DESC
             LIMIT {$limit}"
        );
    }

    /**
     * All posts with category and author – for admin listing (no status filter).
     */
    public static function allWithDetails(string $orderBy = 'created_at', string $direction = 'DESC'): array
    {
        $direction = strtoupper($direction) === 'ASC' ? 'ASC' : 'DESC';
        $allowed   = ['created_at', 'title', 'status', 'published_at'];
        $orderBy   = in_array($orderBy, $allowed, true) ? $orderBy : 'created_at';

        return static::db()->fetchAll(
            "SELECT p.*, c.name as category_name, u.username as author_name
             FROM iw_blog_posts p
             LEFT JOIN iw_blog_categories c ON p.category_id = c.id
             LEFT JOIN iw_users u ON p.author_id = u.id
             ORDER BY p.{$orderBy} {$direction}"
        );
    }

    /**
     * Find a single post by ID for admin (no status filter, includes category name).
     */
    public static function findForAdmin(int $id): ?array
    {
        return static::db()->fetch(
            "SELECT p.*, c.name as category_name
             FROM iw_blog_posts p
             LEFT JOIN iw_blog_categories c ON p.category_id = c.id
             WHERE p.id = :id
             LIMIT 1",
            ['id' => $id]
        );
    }

    /**
     * Published posts with category and author – for frontend listing.
     */
    public static function published(int $limit = 10, int $offset = 0): array
    {
        // LIMIT and OFFSET are embedded directly as PHP integers (already type-hinted int)
        // to avoid PDO binding these as strings, which fails with MySQL native prepared statements
        // when PDO::ATTR_EMULATE_PREPARES is false (MySQL 8+ strict mode).
        $limit  = max(1, $limit);
        $offset = max(0, $offset);
        return static::db()->fetchAll(
            "SELECT p.*, c.name as category_name, c.slug as category_slug, u.username as author_name
             FROM iw_blog_posts p
             LEFT JOIN iw_blog_categories c ON p.category_id = c.id
             LEFT JOIN iw_users u ON p.author_id = u.id
             WHERE p.status = 'published'
             ORDER BY p.published_at DESC
             LIMIT {$limit} OFFSET {$offset}"
        );
    }

    /**
     * Count published posts – for pagination.
     */
    public static function publishedCount(): int
    {
        $row = static::db()->fetch(
            "SELECT COUNT(*) as total FROM iw_blog_posts WHERE status = 'published'"
        );
        return (int) ($row['total'] ?? 0);
    }

    /**
     * Published posts filtered by category – for frontend category listing.
     */
    public static function publishedByCategory(int $categoryId, int $limit = 12, int $offset = 0): array
    {
        $limit  = max(1, $limit);
        $offset = max(0, $offset);
        return static::db()->fetchAll(
            "SELECT p.*, c.name as category_name, c.slug as category_slug, u.username as author_name
             FROM iw_blog_posts p
             LEFT JOIN iw_blog_categories c ON p.category_id = c.id
             LEFT JOIN iw_users u ON p.author_id = u.id
             WHERE p.status = 'published' AND p.category_id = :cat_id
             ORDER BY p.published_at DESC
             LIMIT {$limit} OFFSET {$offset}",
            ['cat_id' => $categoryId]
        );
    }

    /**
     * Count published posts in a specific category.
     */
    public static function publishedCountByCategory(int $categoryId): int
    {
        $row = static::db()->fetch(
            "SELECT COUNT(*) as total FROM iw_blog_posts
             WHERE status = 'published' AND category_id = :cat_id",
            ['cat_id' => $categoryId]
        );
        return (int) ($row['total'] ?? 0);
    }

    /**
     * Find a published post by slug (frontend detail view).
     */
    public static function findWithDetails(string $slug): ?array
    {
        return static::db()->fetch(
            "SELECT p.*, c.name as category_name, c.slug as category_slug, u.username as author_name
             FROM iw_blog_posts p
             LEFT JOIN iw_blog_categories c ON p.category_id = c.id
             LEFT JOIN iw_users u ON p.author_id = u.id
             WHERE p.slug = :slug AND p.status = 'published'
             LIMIT 1",
            ['slug' => $slug]
        );
    }

    /**
     * Find a post by ID with full details (no status filter) – for preview.
     *
     * Same JOINs as findWithDetails() but lookups by ID instead of slug
     * and does not filter by status, so drafts can be previewed.
     */
    public static function findByIdWithDetails(int $id): ?array
    {
        return static::db()->fetch(
            "SELECT p.*, c.name as category_name, c.slug as category_slug, u.username as author_name
             FROM iw_blog_posts p
             LEFT JOIN iw_blog_categories c ON p.category_id = c.id
             LEFT JOIN iw_users u ON p.author_id = u.id
             WHERE p.id = :id
             LIMIT 1",
            ['id' => $id]
        );
    }
}
