<?php
/**
 * ZLO Platform - Blog Model
 */

declare(strict_types=1);

require_once __DIR__ . '/../core/Model.php';

class Blog extends Model
{
    protected string $table = 'blog_posts';
    protected string $primaryKey = 'id';
    
    /**
     * Get published posts with pagination
     */
    public function getPublishedPosts(int $page = 1, int $limit = 10, ?string $categorySlug = null, ?string $search = null): array
    {
        $sql = "SELECT bp.*, bc.name as category_name, bc.slug as category_slug, bc.color as category_color, u.name as author_name, u.avatar as author_avatar 
                FROM {$this->table} bp 
                LEFT JOIN blog_categories bc ON bp.category_id = bc.id 
                LEFT JOIN users u ON bp.author_id = u.id 
                WHERE bp.status = 'published' AND bp.published_at <= NOW()";
        
        $params = [];
        
        if ($categorySlug) {
            $sql .= " AND bc.slug = ?";
            $params[] = $categorySlug;
        }
        
        if ($search) {
            $sql .= " AND (bp.title LIKE ? OR bp.excerpt LIKE ? OR bp.content LIKE ?)";
            $searchTerm = "%{$search}%";
            $params[] = $searchTerm;
            $params[] = $searchTerm;
            $params[] = $searchTerm;
        }
        
        $sql .= " ORDER BY bp.published_at DESC";
        
        return $this->paginate($sql, $params, $page, $limit);
    }
    
    /**
     * Get post by slug
     */
    public function getBySlug(string $slug): ?array
    {
        $sql = "SELECT bp.*, bc.name as category_name, bc.slug as category_slug, bc.color as category_color, u.name as author_name, u.avatar as author_avatar, u.bio as author_bio 
                FROM {$this->table} bp 
                LEFT JOIN blog_categories bc ON bp.category_id = bc.id 
                LEFT JOIN users u ON bp.author_id = u.id 
                WHERE bp.slug = ? AND bp.status = 'published'";
        
        return $this->db->fetchOne($sql, [$slug]);
    }
    
    /**
     * Get featured posts
     */
    public function getFeatured(int $limit = 3): array
    {
        $sql = "SELECT bp.*, bc.name as category_name, bc.slug as category_slug, bc.color as category_color, u.name as author_name 
                FROM {$this->table} bp 
                LEFT JOIN blog_categories bc ON bp.category_id = bc.id 
                LEFT JOIN users u ON bp.author_id = u.id 
                WHERE bp.status = 'published' AND bp.featured = 1 
                ORDER BY bp.published_at DESC 
                LIMIT ?";
        
        return $this->db->fetchAll($sql, [$limit]);
    }
    
    /**
     * Get related posts
     */
    public function getRelated(int $postId, ?int $categoryId, int $limit = 3): array
    {
        $sql = "SELECT bp.id, bp.title, bp.slug, bp.featured_image, bp.excerpt, bp.published_at, bc.name as category_name, bc.slug as category_slug 
                FROM {$this->table} bp 
                LEFT JOIN blog_categories bc ON bp.category_id = bc.id 
                WHERE bp.id != ? AND bp.status = 'published'";
        
        $params = [$postId];
        
        if ($categoryId) {
            $sql .= " AND bp.category_id = ?";
            $params[] = $categoryId;
        }
        
        $sql .= " ORDER BY bp.published_at DESC LIMIT ?";
        $params[] = $limit;
        
        return $this->db->fetchAll($sql, $params);
    }
    
    /**
     * Increment views
     */
    public function incrementViews(int $id): bool
    {
        return $this->db->query("UPDATE {$this->table} SET views = views + 1 WHERE id = ?", [$id]);
    }
    
    /**
     * Get posts by author
     */
    public function getByAuthor(int $authorId, int $page = 1, int $limit = 10): array
    {
        $sql = "SELECT bp.*, bc.name as category_name, bc.slug as category_slug 
                FROM {$this->table} bp 
                LEFT JOIN blog_categories bc ON bp.category_id = bc.id 
                WHERE bp.author_id = ? AND bp.status = 'published' 
                ORDER BY bp.published_at DESC";
        
        return $this->paginate($sql, [$authorId], $page, $limit);
    }
    
    /**
     * Get posts by tag
     */
    public function getByTag(string $tag, int $page = 1, int $limit = 10): array
    {
        $sql = "SELECT bp.*, bc.name as category_name, bc.slug as category_slug, u.name as author_name 
                FROM {$this->table} bp 
                LEFT JOIN blog_categories bc ON bp.category_id = bc.id 
                LEFT JOIN users u ON bp.author_id = u.id 
                WHERE bp.status = 'published' AND JSON_CONTAINS(bp.tags, ?) 
                ORDER BY bp.published_at DESC";
        
        return $this->paginate($sql, [json_encode($tag)], $page, $limit);
    }
    
    /**
     * Create new post
     */
    public function createPost(array $data): int
    {
        // Generate slug if not provided
        if (empty($data['slug'])) {
            $data['slug'] = $this->generateSlug($data['title']);
        }
        
        // Encode tags
        if (!empty($data['tags']) && is_array($data['tags'])) {
            $data['tags'] = json_encode($data['tags']);
        }
        
        // Set published date if publishing
        if ($data['status'] === 'published' && empty($data['published_at'])) {
            $data['published_at'] = date('Y-m-d H:i:s');
        }
        
        return $this->create($data);
    }
    
    /**
     * Update post
     */
    public function updatePost(int $id, array $data): bool
    {
        // Update slug if title changed
        if (!empty($data['title']) && empty($data['slug'])) {
            $data['slug'] = $this->generateSlug($data['title'], $id);
        }
        
        // Encode tags
        if (!empty($data['tags']) && is_array($data['tags'])) {
            $data['tags'] = json_encode($data['tags']);
        }
        
        return $this->update($id, $data);
    }
    
    /**
     * Generate unique slug
     */
    public function generateSlug(string $title, ?int $excludeId = null): string
    {
        $slug = $this->slugify($title);
        $originalSlug = $slug;
        $counter = 1;
        
        while (true) {
            $sql = "SELECT id FROM {$this->table} WHERE slug = ?";
            $params = [$slug];
            
            if ($excludeId) {
                $sql .= " AND id != ?";
                $params[] = $excludeId;
            }
            
            $existing = $this->db->fetchOne($sql, $params);
            
            if (!$existing) {
                break;
            }
            
            $slug = $originalSlug . '-' . $counter;
            $counter++;
        }
        
        return $slug;
    }
    
    /**
     * Convert title to slug
     */
    private function slugify(string $text): string
    {
        $text = preg_replace('~[^\pL\d]+~u', '-', $text);
        $text = iconv('utf-8', 'us-ascii//TRANSLIT', $text);
        $text = preg_replace('~[^-\w]+~', '', $text);
        $text = trim($text, '-');
        $text = preg_replace('~-+~', '-', $text);
        $text = strtolower($text);
        
        return $text ?: 'n-a';
    }
    
    /**
     * Get archive months
     */
    public function getArchiveMonths(): array
    {
        $sql = "SELECT 
                    DATE_FORMAT(published_at, '%Y-%m') as month,
                    DATE_FORMAT(published_at, '%M %Y') as label,
                    COUNT(*) as count 
                FROM {$this->table} 
                WHERE status = 'published' 
                GROUP BY DATE_FORMAT(published_at, '%Y-%m') 
                ORDER BY month DESC";
        
        return $this->db->fetchAll($sql);
    }
    
    /**
     * Get popular tags
     */
    public function getPopularTags(int $limit = 20): array
    {
        $sql = "SELECT tags FROM {$this->table} WHERE status = 'published' AND tags IS NOT NULL";
        $posts = $this->db->fetchAll($sql);
        
        $tagCounts = [];
        foreach ($posts as $post) {
            $tags = json_decode($post['tags'] ?? '[]', true);
            foreach ($tags as $tag) {
                $tagCounts[$tag] = ($tagCounts[$tag] ?? 0) + 1;
            }
        }
        
        arsort($tagCounts);
        
        $result = [];
        foreach (array_slice($tagCounts, 0, $limit, true) as $tag => $count) {
            $result[] = ['name' => $tag, 'count' => $count];
        }
        
        return $result;
    }
}

/**
 * Blog Category Model
 */
class BlogCategory extends Model
{
    protected string $table = 'blog_categories';
    protected string $primaryKey = 'id';
    
    /**
     * Get categories with post count
     */
    public function getAllWithCount(): array
    {
        $sql = "SELECT bc.*, COUNT(bp.id) as post_count 
                FROM {$this->table} bc 
                LEFT JOIN blog_posts bp ON bc.id = bp.category_id AND bp.status = 'published' 
                GROUP BY bc.id 
                ORDER BY bc.name";
        
        return $this->db->fetchAll($sql);
    }
    
    /**
     * Get by slug
     */
    public function getBySlug(string $slug): ?array
    {
        return $this->db->fetchOne("SELECT * FROM {$this->table} WHERE slug = ?", [$slug]);
    }
    
    /**
     * Update post count
     */
    public function updatePostCount(int $id): bool
    {
        return $this->db->query(
            "UPDATE {$this->table} SET post_count = (SELECT COUNT(*) FROM blog_posts WHERE category_id = ? AND status = 'published') WHERE id = ?",
            [$id, $id]
        );
    }
}
