<?php

namespace App\Models;

use Config\Lookups;

/**
 * Operational expenses — the hotel's own costs, not guest-facing billing
 * (that's FolioLineItemModel). status/reviewed_by/reviewed_at follows the
 * same three-state approval shape as shift_swap_requests/
 * time_off_requests (Lookups::$requestStatuses); the auto-approve-under-
 * threshold decision itself lives in ExpenseController::create(), same as
 * every other status decision in this codebase.
 */
class ExpenseModel extends TenantModel
{
    protected $table            = 'expenses';
    protected $primaryKey       = 'id';
    protected $useAutoIncrement = true;
    protected $returnType       = 'object';
    protected $useTimestamps    = true;

    protected $allowedFields = [
        'category',
        'description',
        'amount',
        'expense_date',
        'status',
        'created_by',
        'reviewed_by',
        'reviewed_at',
        'notes',
    ];

    public function __construct()
    {
        parent::__construct();

        $lookups = new Lookups();

        $this->validationRules = [
            'category'     => 'required|in_list[' . implode(',', array_keys($lookups->expenseCategories)) . ']',
            'description'  => 'required|max_length[255]',
            'amount'       => 'required|decimal',
            'expense_date' => 'required|valid_date[Y-m-d]',
            'status'       => 'permit_empty|in_list[' . implode(',', array_keys($lookups->requestStatuses)) . ']',
        ];
    }

    public function withDetails()
    {
        return $this->select('expenses.*, creator.full_name as created_by_name, reviewer.full_name as reviewed_by_name')
            ->join('users creator', 'creator.id = expenses.created_by', 'left')
            ->join('users reviewer', 'reviewer.id = expenses.reviewed_by', 'left');
    }

    public function pending(): array
    {
        return $this->withDetails()->where('expenses.status', 'pending')->orderBy('expenses.expense_date')->findAll();
    }

    public function pendingCount(): int
    {
        return $this->where('status', 'pending')->countAllResults();
    }

    /** Approved expenses only — pending/denied aren't real costs yet. Mirrors FolioLineItemModel::totalBetween(). */
    public function totalApprovedBetween(string $startDate, string $endDate): string
    {
        $result = $this->where('status', 'approved')
            ->where('expense_date >=', $startDate)
            ->where('expense_date <=', $endDate)
            ->selectSum('amount')
            ->first();

        return $result->amount ?? '0.00';
    }

    /** @return array<string, string> category => summed amount, approved only. Mirrors FolioLineItemModel::byCategoryBetween(). */
    public function byCategoryApprovedBetween(string $startDate, string $endDate): array
    {
        $rows = $this->select('category, SUM(amount) as total')
            ->where('status', 'approved')
            ->where('expense_date >=', $startDate)
            ->where('expense_date <=', $endDate)
            ->groupBy('category')
            ->findAll();

        $out = [];
        foreach ($rows as $row) {
            $out[$row->category] = $row->total;
        }

        return $out;
    }
}
