<?php

namespace Artemis\Faq\Models;

use App\Models\User;
use Illuminate\Support\Facades\DB;
use Artemis\Faq\Traits\HasTablePrefix;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\MorphMany;

class Answer extends Model
{
    use SoftDeletes, HasTablePrefix;

    protected $fillable = [
        'user_id',
        'question_id',
        'body',
        'is_best'
    ];

    /**
     * Scope to include vote score (upvotes - downvotes)
     */
    public function scopeWithVoteScore($query)
    {
        $sub = DB::table('faq_votes')
            ->selectRaw("
            votable_id,
            SUM(CASE WHEN vote_type = 'up' THEN 1
                     WHEN vote_type = 'down' THEN -1
                     ELSE 0 END) as vote_score
        ")
            ->where('votable_type', Answer::class)
            ->groupBy('votable_id');

        return $query->leftJoinSub($sub, 'vote_sub', 'vote_sub.votable_id', '=', 'faq_answers.id')
            ->addSelect('faq_answers.*', DB::raw('COALESCE(vote_sub.vote_score, 0) as vote_score'));
    }
    public function question(): BelongsTo
    {
        return $this->belongsTo(Question::class);
    }

    public function user(): BelongsTo
    {
        return $this->belongsTo(User::class);
    }

    public function votes(): MorphMany
    {
        return $this->morphMany(Vote::class, 'votable');
    }

    public function comments(): MorphMany
    {
        return $this->morphMany(Comment::class, 'commentable')->latest();
    }
    public function flags(): MorphMany
    {
        return $this->morphMany(Flag::class, 'flaggable');
    }
}
