<?php

declare(strict_types=1);

namespace App\Modules\Expenses\Application\Services;

use App\Core\AppBootstrap;
use PDO;

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

    public function ensureSchema(): void
    {
        AppBootstrap::bootExpenses($this->pdo, true);
    }

    public function categories(string $q, string $status): array
    {
        $q = trim($q);
        $status = trim($status) !== '' ? trim($status) : 'active';
        $where = ['1=1'];
        $params = [];
        if ($q !== '') {
            $where[] = '(name LIKE ? OR code LIKE ?)';
            $like = '%' . $q . '%';
            $params[] = $like;
            $params[] = $like;
        }
        if ($status === 'active') {
            $where[] = 'is_active = 1';
        } elseif ($status === 'inactive') {
            $where[] = 'is_active = 0';
        }

        $sql = "SELECT c.*, (SELECT COUNT(*) FROM expense_items i WHERE i.category_id = c.id) items_count
                FROM expense_categories c
                WHERE " . implode(' AND ', $where) . "
                ORDER BY c.sort_order ASC, c.name ASC";
        $statement = $this->pdo->prepare($sql);
        $statement->execute($params);

        return [
            'rows' => $statement->fetchAll() ?: [],
            'q' => $q,
            'status' => $status,
        ];
    }

    public function items(string $q, int $categoryId, string $status): array
    {
        $q = trim($q);
        $categoryId = max(0, $categoryId);
        $status = trim($status) !== '' ? trim($status) : 'active';
        $where = ['1=1'];
        $params = [];
        if ($q !== '') {
            $where[] = '(i.name LIKE ? OR i.code LIKE ? OR c.name LIKE ?)';
            $like = '%' . $q . '%';
            array_push($params, $like, $like, $like);
        }
        if ($categoryId > 0) {
            $where[] = 'i.category_id = ?';
            $params[] = $categoryId;
        }
        if ($status === 'active') {
            $where[] = 'i.is_active = 1';
        } elseif ($status === 'inactive') {
            $where[] = 'i.is_active = 0';
        }

        $categories = $this->pdo->query('SELECT id, name FROM expense_categories ORDER BY sort_order ASC, name ASC')->fetchAll() ?: [];
        $sql = "SELECT i.*, c.name AS category_name
                FROM expense_items i
                JOIN expense_categories c ON c.id = i.category_id
                WHERE " . implode(' AND ', $where) . "
                ORDER BY c.sort_order ASC, c.name ASC, i.sort_order ASC, i.name ASC";
        $statement = $this->pdo->prepare($sql);
        $statement->execute($params);

        return [
            'rows' => $statement->fetchAll() ?: [],
            'categories' => $categories,
            'q' => $q,
            'category_id' => $categoryId,
            'status' => $status,
        ];
    }
}
