<?php
namespace App\Models;

class NewsModel
{
    public static function getFeatured(string $lang = 'fr', int $limit = 4): array
    {
        $limit = max(1, (int)$limit);

        return Database::fetchAll(
            "SELECT n.*, s.slug AS subsidiary_slug, st.name AS subsidiary_name
             FROM news n
             LEFT JOIN subsidiaries s ON s.id = n.subsidiary_id
             LEFT JOIN subsidiary_translations st
                    ON st.subsidiary_id = n.subsidiary_id AND st.lang = :lang
             WHERE n.lang = :lang2
               AND n.is_published = 1
               AND n.is_featured = 1
             ORDER BY n.published_at DESC
             LIMIT {$limit}",
            [':lang' => $lang, ':lang2' => $lang]
        );
    }

    public static function getAll(string $lang = 'fr', int $page = 1, int $perPage = 9): array
    {
        $offset  = max(0, ($page - 1) * $perPage);
        $perPage = max(1, (int)$perPage);

        return Database::fetchAll(
            "SELECT n.*, st.name AS subsidiary_name
             FROM news n
             LEFT JOIN subsidiaries s ON s.id = n.subsidiary_id
             LEFT JOIN subsidiary_translations st
                    ON st.subsidiary_id = n.subsidiary_id AND st.lang = :lang
             WHERE n.lang = :lang2 AND n.is_published = 1
             ORDER BY n.published_at DESC
             LIMIT {$perPage} OFFSET {$offset}",
            [':lang' => $lang, ':lang2' => $lang]
        );
    }

    public static function getBySlug(string $slug, string $lang): array|false
    {
        return Database::fetchOne(
            'SELECT n.*, st.name AS subsidiary_name
             FROM news n
             LEFT JOIN subsidiaries s ON s.id = n.subsidiary_id
             LEFT JOIN subsidiary_translations st
                    ON st.subsidiary_id = n.subsidiary_id AND st.lang = :lang
             WHERE n.slug = :slug AND n.lang = :lang2 AND n.is_published = 1',
            [':slug' => $slug, ':lang' => $lang, ':lang2' => $lang]
        );
    }

    public static function count(string $lang = 'fr'): int
    {
        $row = Database::fetchOne(
            'SELECT COUNT(*) AS cnt FROM news WHERE lang = :lang AND is_published = 1',
            [':lang' => $lang]
        );
        return (int)($row['cnt'] ?? 0);
    }

    public static function create(array $data): int
    {
        return Database::insert(
            'INSERT INTO news
                (slug, lang, subsidiary_id, title, excerpt, content,
                 cover_image, is_published, is_featured, published_at, created_by)
             VALUES
                (:slug, :lang, :subsidiary_id, :title, :excerpt, :content,
                 :cover_image, :is_published, :is_featured, :published_at, :created_by)',
            $data
        );
    }
}