<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Illuminate\Support\Facades\DB;

class Account extends Model
{
    protected $fillable = [
        'company_id', 
        'user_id', 
        'account_type_id', 
        'account_group_id', 
        'code', 
        'name', 
        'slug', 
        'is_active'
    ];

    /**
     * Standard relationship name for the sum logic in your controller.
     */
    public function journal_entries(): HasMany
    {
        return $this->hasMany(JournalEntry::class);
    }

    public function type(): BelongsTo
    {
        return $this->belongsTo(AccountType::class, 'account_type_id');
    }
    
    
    

    public function group(): BelongsTo
    {
        return $this->belongsTo(AccountGroup::class, 'account_group_id');
    }
    
    /**
 * Shortcut relationship to reach the Primary Category.
 * Chain: Account -> AccountType -> AccountGroup -> PrimaryCategory
 */
/**
 * Shortcut relationship to reach the Primary Category.
 * Chain: Account -> AccountGroup -> PrimaryCategory
 */
/**
 * Relationship: Reach the category through the group.
 * This allows $account->category to work and whereHas('category') to work.
 */
public function category()
{
    // Return the relationship from the group model
    return $this->group()->first() ? $this->group->category() : $this->belongsTo(PrimaryCategory::class, 'id', 'id')->whereRaw('1 = 0');
}
// Alternatively, since you already have the chain logic:
public function getCategoryAttribute()
{
    return $this->type->group->category ?? null;
}

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

    /**
     * Financial Logic: Real-time balance based on Account Category
     */
    public function getBalance($startDate = null, $endDate = null)
    {
        $query = $this->journal_entries();
        
        if ($startDate && $endDate) {
            $query->whereBetween('transaction_date', [$startDate, $endDate]);
        }

        $sums = $query->selectRaw('SUM(debit) as total_debit, SUM(credit) as total_credit')->first();
        $debits = $sums->total_debit ?? 0;
        $credits = $sums->total_credit ?? 0;

        // Fetch category through the relationship chain: Type -> Group -> Category
        $category = strtolower($this->type->group->category->name ?? '');

        // Assets and Expenses are Debit-Normal accounts
        if (in_array($category, ['asset', 'expense', 'non-current assets', 'bank and cash'])) {
            return $debits - $credits;
        }

        // Liabilities, Equity, and Revenue are Credit-Normal accounts
        return $credits - $debits;
    }

    /**
     * Scope to filter accounts by their top-level primary category name.
     * Useful for Profit & Loss or Balance Sheet generation.
     */
    public function scopeByCategory($query, $categoryName)
    {
        return $query->whereHas('type.group.category', function($q) use ($categoryName) {
            $q->where('name', $categoryName);
        });
    }
}