<?php

require_once __DIR__ . '/BaseModel.php';

class NewsModel extends BaseModel
{

    protected $table = 'news';

    /**
     * Get published news with pagination
     */
    public function getPublishedNews($limit = 10, $offset = 0, $category = null)
    {
        $sql = "SELECT 
                    id, title, content, summary, image_url, category, 
                    author, is_published, views_count, created_at, published_at 
                FROM {$this->table} 
                WHERE is_published = 1 
                AND published_at <= NOW()";

        $params = [];
        $types = "";

        // Filter by category if provided
        if ($category) {
            $sql .= " AND category = ?";
            $params[] = $category;
            $types .= "s";
        }

        $sql .= " ORDER BY published_at DESC LIMIT ? OFFSET ?";
        $params[] = $limit;
        $params[] = $offset;
        $types .= "ii";

        $stmt = $this->db->prepare($sql);
        if (!$stmt) {
            throw new Exception("Database prepare error: " . $this->db->error);
        }

        if (!empty($params)) {
            $stmt->bind_param($types, ...$params);
        }

        $stmt->execute();
        $result = $stmt->get_result();

        $news = [];
        while ($row = $result->fetch_assoc()) {
            $news[] = $this->formatNewsData($row);
        }

        $stmt->close();
        return $news;
    }

    /**
     * Get news by ID and increment view count
     */
    public function getNewsById($id)
    {
        $stmt = $this->db->prepare("
            SELECT 
                id, title, content, summary, image_url, category, 
                author, is_published, views_count, created_at, published_at 
            FROM {$this->table} 
            WHERE id = ? 
            AND is_published = 1 
            AND published_at <= NOW()
            LIMIT 1
        ");

        if (!$stmt) {
            throw new Exception("Database prepare error: " . $this->db->error);
        }

        $stmt->bind_param("i", $id);
        $stmt->execute();
        $result = $stmt->get_result();
        $news = $result->fetch_assoc();
        $stmt->close();

        if ($news) {
            // Increment view count
            $this->incrementViewCount($id);
            return $this->formatNewsData($news);
        }

        return null;
    }

    /**
     * Increment view count
     */
    public function incrementViewCount($id)
    {
        $stmt = $this->db->prepare("UPDATE {$this->table} SET views_count = views_count + 1 WHERE id = ?");
        if (!$stmt) {
            throw new Exception("Database prepare error: " . $this->db->error);
        }

        $stmt->bind_param("i", $id);
        $result = $stmt->execute();
        $stmt->close();

        return $result;
    }

    /**
     * Count published news
     */
    public function countPublishedNews($category = null)
    {
        $sql = "SELECT COUNT(*) as total FROM {$this->table} WHERE is_published = 1 AND published_at <= NOW()";
        $params = [];
        $types = "";

        if ($category) {
            $sql .= " AND category = ?";
            $params[] = $category;
            $types .= "s";
        }

        $stmt = $this->db->prepare($sql);
        if (!$stmt) {
            throw new Exception("Database prepare error: " . $this->db->error);
        }

        if (!empty($params)) {
            $stmt->bind_param($types, ...$params);
        }

        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $stmt->close();

        return (int) $row['total'];
    }

    /**
     * Get news categories
     */
    public function getCategories()
    {
        $stmt = $this->db->prepare("
            SELECT DISTINCT category 
            FROM {$this->table} 
            WHERE is_published = 1 
            AND published_at <= NOW() 
            AND category IS NOT NULL 
            AND category != ''
            ORDER BY category ASC
        ");

        if (!$stmt) {
            throw new Exception("Database prepare error: " . $this->db->error);
        }

        $stmt->execute();
        $result = $stmt->get_result();

        $categories = [];
        while ($row = $result->fetch_assoc()) {
            $categories[] = $row['category'];
        }

        $stmt->close();
        return $categories;
    }

    /**
     * Get related news by category
     */
    public function getRelatedNews($newsId, $category, $limit = 5)
    {
        $stmt = $this->db->prepare("
            SELECT 
                id, title, summary, image_url, category, 
                author, views_count, published_at 
            FROM {$this->table} 
            WHERE id != ? 
            AND category = ? 
            AND is_published = 1 
            AND published_at <= NOW()
            ORDER BY published_at DESC 
            LIMIT ?
        ");

        if (!$stmt) {
            throw new Exception("Database prepare error: " . $this->db->error);
        }

        $stmt->bind_param("isi", $newsId, $category, $limit);
        $stmt->execute();
        $result = $stmt->get_result();

        $relatedNews = [];
        while ($row = $result->fetch_assoc()) {
            $relatedNews[] = [
                'id' => (int) $row['id'],
                'title' => $row['title'],
                'summary' => $row['summary'],
                'image_url' => $row['image_url'],
                'category' => $row['category'],
                'author' => $row['author'],
                'views_count' => (int) $row['views_count'],
                'published_at' => $row['published_at']
            ];
        }

        $stmt->close();
        return $relatedNews;
    }

    /**
     * Get popular news (most viewed)
     */
    public function getPopularNews($limit = 10, $category = null)
    {
        $sql = "SELECT 
                    id, title, summary, image_url, category, 
                    author, views_count, published_at 
                FROM {$this->table} 
                WHERE is_published = 1 
                AND published_at <= NOW()";

        $params = [];
        $types = "";

        if ($category) {
            $sql .= " AND category = ?";
            $params[] = $category;
            $types .= "s";
        }

        $sql .= " ORDER BY views_count DESC, published_at DESC LIMIT ?";
        $params[] = $limit;
        $types .= "i";

        $stmt = $this->db->prepare($sql);
        if (!$stmt) {
            throw new Exception("Database prepare error: " . $this->db->error);
        }

        $stmt->bind_param($types, ...$params);
        $stmt->execute();
        $result = $stmt->get_result();

        $popularNews = [];
        while ($row = $result->fetch_assoc()) {
            $popularNews[] = [
                'id' => (int) $row['id'],
                'title' => $row['title'],
                'summary' => $row['summary'],
                'image_url' => $row['image_url'],
                'category' => $row['category'],
                'author' => $row['author'],
                'views_count' => (int) $row['views_count'],
                'published_at' => $row['published_at']
            ];
        }

        $stmt->close();
        return $popularNews;
    }

    /**
     * Search news
     */
    public function searchNews($keyword, $limit = 10, $offset = 0)
    {
        $searchTerm = "%{$keyword}%";

        $stmt = $this->db->prepare("
            SELECT 
                id, title, content, summary, image_url, category, 
                author, is_published, views_count, created_at, published_at 
            FROM {$this->table} 
            WHERE is_published = 1 
            AND published_at <= NOW()
            AND (title LIKE ? OR content LIKE ? OR summary LIKE ?)
            ORDER BY published_at DESC 
            LIMIT ? OFFSET ?
        ");

        if (!$stmt) {
            throw new Exception("Database prepare error: " . $this->db->error);
        }

        $stmt->bind_param("sssii", $searchTerm, $searchTerm, $searchTerm, $limit, $offset);
        $stmt->execute();
        $result = $stmt->get_result();

        $news = [];
        while ($row = $result->fetch_assoc()) {
            $news[] = $this->formatNewsData($row);
        }

        $stmt->close();
        return $news;
    }

    /**
     * Create new news
     */
    public function createNews($newsData)
    {
        $newsData['created_at'] = date('Y-m-d H:i:s');
        $newsData['is_published'] = $newsData['is_published'] ?? 0;
        $newsData['views_count'] = 0;

        return $this->create($newsData);
    }

    /**
     * Update news
     */
    public function updateNews($id, $newsData)
    {
        $newsData['updated_at'] = date('Y-m-d H:i:s');

        return $this->update($id, $newsData);
    }

    /**
     * Format news data for API response
     */
    private function formatNewsData($news)
    {
        return [
            'id' => (int) $news['id'],
            'title' => $news['title'],
            'content' => $news['content'],
            'summary' => $news['summary'],
            'image_url' => $news['image_url'],
            'category' => $news['category'],
            'author' => $news['author'],
            'is_published' => (bool) $news['is_published'],
            'views_count' => (int) $news['views_count'],
            'created_at' => $news['created_at'],
            'published_at' => $news['published_at']
        ];
    }
}
