<?php
declare(strict_types=1);

namespace App\Modules\Accounting\Models;

use App\Core\Model;
use App\Core\App;

class AccountPayable extends Model
{
    protected static string $table = 'accounts_payable';
    protected static array $fillable = [
        'supplier_id', 'invoice_number', 'invoice_date', 'due_date',
        'description_ar', 'description_en', 'total_amount', 'paid_amount',
        'balance', 'currency', 'status', 'journal_entry_id', 'payment_entry_id',
        'purchase_order_id', 'cost_center_id', 'branch_id', 'notes',
    ];
    protected static bool $timestamps = true;
    protected static bool $softDelete = true;

    public static function getOutstanding(?int $supplierId = null): array
    {
        $db = App::getInstance()->db();
        $where = "ap.is_archived = 0 AND ap.status IN ('pending','partial','overdue')";
        $params = [];

        if ($supplierId !== null) {
            $where .= ' AND ap.supplier_id = ?';
            $params[] = $supplierId;
        }

        return $db->select(
            "SELECT ap.*, s.name as supplier_name
             FROM accounts_payable ap
             LEFT JOIN suppliers s ON s.id = ap.supplier_id
             WHERE {$where}
             ORDER BY ap.due_date ASC",
            $params
        );
    }

    public static function getAgingSummary(): array
    {
        $db = App::getInstance()->db();
        $today = date('Y-m-d');
        return $db->selectOne(
            "SELECT
                SUM(CASE WHEN due_date >= ? THEN balance ELSE 0 END) as current_amount,
                SUM(CASE WHEN due_date < ? AND due_date >= DATE_SUB(?, INTERVAL 30 DAY) THEN balance ELSE 0 END) as days_30,
                SUM(CASE WHEN due_date < DATE_SUB(?, INTERVAL 30 DAY) AND due_date >= DATE_SUB(?, INTERVAL 60 DAY) THEN balance ELSE 0 END) as days_60,
                SUM(CASE WHEN due_date < DATE_SUB(?, INTERVAL 60 DAY) AND due_date >= DATE_SUB(?, INTERVAL 90 DAY) THEN balance ELSE 0 END) as days_90,
                SUM(CASE WHEN due_date < DATE_SUB(?, INTERVAL 90 DAY) THEN balance ELSE 0 END) as over_90,
                SUM(balance) as total
             FROM accounts_payable
             WHERE is_archived = 0 AND status IN ('pending','partial','overdue')",
            [$today, $today, $today, $today, $today, $today, $today, $today]
        );
    }
}
