<?php

declare(strict_types=1);

namespace App\Modules\Reporting\Application\Services;

use App\Core\SchemaRuntime;
use App\Shared\Schema\FinanceSchemaSupport;
use PDO;

final class ExpenseReportService
{
    public function __construct(private PDO $pdo)
    {
    }

    public function ensureSchema(): void
    {
        SchemaRuntime::ensureMany($this->pdo, [
            [
                'key' => 'cash_payments_schema',
                'callback' => function (): void { FinanceSchemaSupport::ensureCashPaymentsSchema($this->pdo); },
                'migration' => 'sql/migrate_v1_22_cash_payments.sql',
            ],
            [
                'key' => 'expenses_schema',
                'callback' => function (): void { FinanceSchemaSupport::ensureExpensesSchema($this->pdo); },
                'migration' => 'sql/migrate_v1_17_expenses.sql',
            ],
            [
                'key' => 'cashboxes_schema',
                'callback' => function (): void { FinanceSchemaSupport::ensureCashboxesSchema($this->pdo); },
                'migration' => 'sql/migrate_v1_27_cashboxes.sql',
            ],
            [
                'key' => 'bank_accounts_schema',
                'callback' => function (): void { FinanceSchemaSupport::ensureBankAccountsSchema($this->pdo); },
                'migration' => 'sql/migrate_v1_21_bank_accounts.sql',
            ],
        ]);
    }

    /** @return array<string,mixed> */
    public function report(
        string $from,
        string $to,
        int $categoryId,
        int $itemId,
        string $accountSourceType,
        int $cashboxId,
        int $bankAccountId,
        string $q
    ): array {
        $from = trim($from) !== '' ? trim($from) : date('Y-m-01');
        $to = trim($to) !== '' ? trim($to) : date('Y-m-d');
        $categoryId = max(0, $categoryId);
        $itemId = max(0, $itemId);
        $cashboxId = max(0, $cashboxId);
        $bankAccountId = max(0, $bankAccountId);
        $accountSourceType = trim($accountSourceType);
        $q = trim($q);

        $categories = $this->pdo->query("SELECT id,name FROM expense_categories WHERE is_active=1 ORDER BY sort_order ASC, name ASC")->fetchAll() ?: [];
        $items = $this->pdo->query("SELECT ei.id, ei.name, ec.name AS category_name FROM expense_items ei INNER JOIN expense_categories ec ON ec.id=ei.category_id WHERE ei.is_active=1 ORDER BY ec.sort_order ASC, ec.name ASC, ei.sort_order ASC, ei.name ASC")->fetchAll() ?: [];
        $cashboxes = $this->pdo->query("SELECT id,name FROM cashboxes WHERE is_active=1 ORDER BY name ASC")->fetchAll() ?: [];
        $bankAccounts = $this->pdo->query("SELECT id,name FROM bank_accounts WHERE is_active=1 ORDER BY name ASC")->fetchAll() ?: [];

        $fromDateTime = $from . ' 00:00:00';
        $toExclusive = date('Y-m-d 00:00:00', strtotime($to . ' +1 day'));
        $where = ["p.payment_type='expense'", "UPPER(COALESCE(p.status,'POSTED'))='POSTED'", "p.voucher_date >= ?", "p.voucher_date < ?"];
        $params = [$fromDateTime, $toExclusive];
        if ($categoryId > 0) { $where[] = 'ec.id=?'; $params[] = $categoryId; }
        if ($itemId > 0) { $where[] = 'ei.id=?'; $params[] = $itemId; }
        if ($accountSourceType === 'cashbox' || $accountSourceType === 'bank_account') { $where[] = 'p.account_source_type=?'; $params[] = $accountSourceType; }
        if ($cashboxId > 0) { $where[] = 'p.cashbox_id=?'; $params[] = $cashboxId; }
        if ($bankAccountId > 0) { $where[] = 'p.bank_account_id=?'; $params[] = $bankAccountId; }
        if ($q !== '') {
            $where[] = '(p.voucher_no LIKE ? OR COALESCE(p.notes,"") LIKE ? OR COALESCE(p.payee_name,"") LIKE ? OR ei.name LIKE ? OR ec.name LIKE ?)';
            $like = "%{$q}%";
            array_push($params, $like, $like, $like, $like, $like);
        }

        $whereSql = implode(' AND ', $where);
        $sql = "SELECT p.*, ei.name AS expense_item_name, ec.name AS expense_category_name,
                       cb.name AS cashbox_name, ba.name AS bank_account_name
                FROM cash_payments p
                INNER JOIN expense_items ei ON ei.id=p.expense_item_id
                INNER JOIN expense_categories ec ON ec.id=ei.category_id
                LEFT JOIN cashboxes cb ON cb.id=p.cashbox_id
                LEFT JOIN bank_accounts ba ON ba.id=p.bank_account_id
                WHERE {$whereSql}
                ORDER BY p.voucher_date ASC, p.id ASC";
        $st = $this->pdo->prepare($sql);
        $st->execute($params);
        $rows = $st->fetchAll() ?: [];

        $totals = ['amount' => 0.0];
        $byCategory = [];
        $byItem = [];
        foreach ($rows as $r) {
            $amt = (float) ($r['amount'] ?? 0);
            $totals['amount'] += $amt;
            $cat = (string) ($r['expense_category_name'] ?? 'غير مصنف');
            $item = (string) ($r['expense_item_name'] ?? 'غير محدد');
            $byCategory[$cat] = ($byCategory[$cat] ?? 0.0) + $amt;
            $byItem[$cat . '||' . $item] = ($byItem[$cat . '||' . $item] ?? 0.0) + $amt;
        }
        arsort($byCategory);
        arsort($byItem);

        return [
            'rows' => $rows,
            'categories' => $categories,
            'items' => $items,
            'cashboxes' => $cashboxes,
            'bankAccounts' => $bankAccounts,
            'from' => $from,
            'to' => $to,
            'category_id' => $categoryId,
            'item_id' => $itemId,
            'account_source_type' => $accountSourceType,
            'cashbox_id' => $cashboxId,
            'bank_account_id' => $bankAccountId,
            'q' => $q,
            'totals' => $totals,
            'byCategory' => $byCategory,
            'byItem' => $byItem,
        ];
    }
}
