<?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 " . (int) $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.
     */
    /**
     * SQL fragment for "currently visible" posts.
     * Covers published posts that haven't expired, plus scheduled posts
     * whose publish_at has passed (cron may not have run yet).
     */
    private static function visibleWhere(): string
    {
        return "(p.status = 'published' AND (p.unpublish_at IS NULL OR p.unpublish_at > NOW()))
             OR (p.status = 'scheduled' AND p.publish_at IS NOT NULL AND p.publish_at <= NOW()
                 AND (p.unpublish_at IS NULL OR p.unpublish_at > NOW()))";
    }

    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);
        $where  = static::visibleWhere();
        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 {$where}
             ORDER BY COALESCE(p.published_at, p.publish_at) DESC
             LIMIT {$limit} OFFSET {$offset}"
        );
    }

    /**
     * Count published posts – for pagination.
     */
    public static function publishedCount(): int
    {
        $where = static::visibleWhere();
        $row = static::db()->fetch(
            "SELECT COUNT(*) as total FROM iw_blog_posts p WHERE {$where}"
        );
        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);
        $where  = static::visibleWhere();
        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 ({$where}) AND p.category_id = :cat_id
             ORDER BY COALESCE(p.published_at, p.publish_at) DESC
             LIMIT {$limit} OFFSET {$offset}",
            ['cat_id' => $categoryId]
        );
    }

    /**
     * Count published posts in a specific category.
     */
    public static function publishedCountByCategory(int $categoryId): int
    {
        $where = static::visibleWhere();
        $row = static::db()->fetch(
            "SELECT COUNT(*) as total FROM iw_blog_posts p
             WHERE ({$where}) AND p.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
    {
        $where = static::visibleWhere();
        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 ({$where})
             LIMIT 1",
            ['slug' => $slug]
        );
    }

    /**
     * Check if a blog post is currently visible on the frontend.
     */
    public static function isVisible(array $post): bool
    {
        if ($post['status'] === 'published') {
            if (!empty($post['unpublish_at']) && strtotime($post['unpublish_at']) <= time()) {
                return false;
            }
            return true;
        }
        if ($post['status'] === 'scheduled' && !empty($post['publish_at']) && strtotime($post['publish_at']) <= time()) {
            if (!empty($post['unpublish_at']) && strtotime($post['unpublish_at']) <= time()) {
                return false;
            }
            return true;
        }
        return false;
    }

    /**
     * Featured published posts – for theme magazine grids and highlighted content.
     */
    public static function featured(int $limit = 3): array
    {
        $limit = max(1, min(20, $limit));
        $where = static::visibleWhere();
        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 ({$where}) AND p.is_featured = 1
             ORDER BY COALESCE(p.published_at, p.publish_at) DESC
             LIMIT {$limit}"
        );
    }

    /**
     * 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]
        );
    }
}
