<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;
class News extends Model
{
    protected $table = 'tbl_news';
    protected $primaryKey = 'news_id';
    public $incrementing = true;
    public $timestamps = false;

    protected $fillable = [
        'news_id', 'news_title', 'news_content', 'news_content_short', 'news_date',
        'photo', 'banner', 'category_id', 'comment', 'meta_title', 'meta_keyword',
        'meta_description', 'lang_id', 'stitle', 'title', 'img_alt', 'domain_id', 'encode_title'
    ];
public function getFormattedDateAttribute()
{
    return Carbon::parse($this->news_date)->format('F j, Y');
}
    // 1. Search news
    public static function searchNews($query)
{
    return DB::table('tbl_news as n')
        ->select(
            'n.news_id',
            'n.news_title',
            'n.encode_title',
            'n.photo',          // add image
            'n.img_alt',        // add alt text
            'c.category_name'
        )
        ->leftJoin('tbl_category as c', 'c.category_id', '=', 'n.category_id')
        ->where('n.news_title', 'like', "%$query%")
        ->orWhere('n.news_content_short', 'like', "%$query%")
        ->orWhere('n.news_content', 'like', "%$query%")
        ->limit(10)
        ->get();
}


    // 2. Get SEO data
    public static function getSEOData($category_id, $encode_title)
    {
        return DB::table('tbl_news as n')
            ->select('n.meta_title', 'n.meta_description', 'n.meta_keyword', 'c.category_name')
            ->leftJoin('tbl_category as c', 'c.category_id', '=', 'n.category_id')
            ->where('n.category_id', $category_id)
            ->where('n.encode_title', $encode_title)
            ->first();
    }

    // 3. Get latest news in a category
    public static function getLatestByCategory($category_id)
    {
        return DB::table('tbl_news as n')
            ->select('n.*', 'c.category_name')
            ->leftJoin('tbl_category as c', 'c.category_id', '=', 'n.category_id')
            ->where('n.category_id', $category_id)
            ->orderByDesc('n.news_date')
            ->limit(1)
            ->first();
    }

    // 4. Get some news by category
    public static function getSomeNews($category_id, $limit = 0, $offset = 0)
{
    $query = DB::table('tbl_news as n')
        ->select('n.*', 'c.category_name')
        ->leftJoin('tbl_category as c', 'c.category_id', '=', 'n.category_id')
        ->where('n.category_id', $category_id)
        ->orderBy('n.news_date', 'DESC'); // 👈 LATEST FIRST

    // limit only if provided
    if ($limit > 0) {
        $query->limit($limit)->offset($offset);
    }

    return $query->get();
}

    // 5. Get specific news article
    public static function getView($category_id, $encoded_title)
    {
        return DB::table('tbl_news as n')
            ->select('n.*', 'c.category_name')
            ->leftJoin('tbl_category as c', 'c.category_id', '=', 'n.category_id')
            ->where([
                ['n.category_id', '=', $category_id],
                ['n.encode_title', '=', $encoded_title],
            ])
            ->first();
    }

    // 6. Paginated news by category
    public static function getPage($category_id, $limit = 0, $offset = 0)
{
    $query = DB::table('tbl_news as n')
        ->select('n.*', 'c.category_name')
        ->leftJoin('tbl_category as c', 'c.category_id', '=', 'n.category_id')
        ->where('n.category_id', $category_id)
        ->orderBy('n.news_date', 'DESC'); // LATEST FIRST

    if ($limit > 0) {
        $query->limit($limit)->offset($offset);
    }

    return $query->get();
}


    // 7. Get category by slug
    public static function getCategoryBySlug($category_slug)
    {
        return DB::table('tbl_category')
            ->where('encode_title', $category_slug)
            ->first();
    }

    // 8. General news pagination
//     public static function getNews($limit, $offset)
// {
//     $categories = [
//         'Technology',
//         'Economy',
//         'Money',
//         'Business',
//         'Finance',
//         'Politics',
//         'Banking',
//         'Venture-capital',
//         'Investment'
//     ];

//     return DB::table('tbl_news as n')
//         ->select('n.*', 'c.category_name')
//         ->leftJoin('tbl_category as c', 'c.category_id', '=', 'n.category_id')
//         ->whereIn('c.category_name', $categories)
//         ->orderByDesc('n.news_date')
//         ->offset($offset)
//         ->limit($limit)
//         ->get();
// }
    public static function getNews($limit, $offset)
    {
        return DB::table('tbl_news as n')
            ->select('n.*', 'c.category_name')
            ->leftJoin('tbl_category as c', 'c.category_id', '=', 'n.category_id')
            ->orderByDesc('n.news_date')
            ->offset($offset)
            ->limit($limit)
            ->get();
    }

    // 9. Homepage index news
    public static function getIndex($category_id, $limit, $offset)
    {
        return DB::table('tbl_news as n')
            ->select('n.*', 'c.category_name')
            ->leftJoin('tbl_category as c', 'c.category_id', '=', 'n.category_id')
            ->where('n.category_id', $category_id)
            ->offset($offset)
            ->limit($limit)
            ->get();
    }

    // 10. More news with pagination
    public static function getMoreNews($category_id, $offset, $limit)
    {
        return DB::table('tbl_news as n')
            ->select('n.*', 'c.category_name')
            ->leftJoin('tbl_category as c', 'c.category_id', '=', 'n.category_id')
            ->where('n.category_id', $category_id)
            ->orderByDesc('n.news_date')
            ->offset($offset)
            ->limit($limit)
            ->get();
    }

    // 11. Client-specific news
    public static function client($category_id, $limit, $offset)
    {
        return DB::table('tbl_news as n')
            ->select('n.*', 'c.category_name')
            ->leftJoin('tbl_category as c', 'c.category_id', '=', 'n.category_id')
            ->where('n.category_id', $category_id)
            ->orderByDesc('n.news_date')
            ->offset($offset)
            ->limit($limit)
            ->get();
    }

    // 12. Latest news using "is_latestt" flag
    public static function getLatest($limit, $offset)
    {
        return DB::table('tbl_news as n')
            ->select('n.*', 'c.category_name')
            ->leftJoin('tbl_category as c', 'c.category_id', '=', 'n.category_id')
            ->orderByDesc('n.is_latestt')
            ->offset($offset)
            ->limit($limit)
            ->get();
    }
}
