<?php
/**
 * @package       Bicycle
 * @author        Egor Isaev
 * @description   TransactionRepository.php
 * @copyright (c) 11/08/2026
 */

namespace App\Repositories;

use PDO;
use System\Classes\Repository;

/**
 * Репозиторий таблицы transactions (income/expense/transfer).
 */
class TransactionRepository extends Repository
{
    /**
     * @param string|null $connection Имя подключения (Database::instance($connection)); null — по умолчанию
     */
    public function __construct(?string $connection = null)
    {
        parent::__construct('transactions', connection: $connection);
    }

    /**
     * Последние N транзакций пользователя с джойнами на категорию и счета.
     *
     * @param int $user_id
     * @param int $limit
     * @return object[]
     */
    public function getRecentForUser(int $user_id, int $limit = 50): array
    {
        $limit = (int)$limit;
        $sql   = "SELECT t.id, t.date, t.type, t.summa, t.currency, t.rate_to_rub,
                         t.categorie_id, t.account_f_id, t.account_in_id,
                         c.title  AS category_title,
                         af.title AS account_f_title,
                         ai.title AS account_in_title
                  FROM {$this->table} t
                  LEFT JOIN categories c  ON c.id  = t.categorie_id
                  LEFT JOIN accounts   af ON af.id = t.account_f_id
                  LEFT JOIN accounts   ai ON ai.id = t.account_in_id
                  WHERE t.user_id = :user_id
                  ORDER BY t.date DESC, t.id DESC
                  LIMIT {$limit}";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['user_id' => $user_id]);
        return $stmt->fetchAll(PDO::FETCH_OBJ);
    }

    /**
     * Агрегат income/expense по месяцам за год.
     *
     * @param int $user_id
     * @param int $year
     * @return array<int,array<string,float>> [month => ['income'=>..., 'expense'=>...]]
     */
    public function getMonthlyTotals(int $user_id, int $year): array
    {
        $sql = "SELECT MONTH(date) AS month, type, SUM(summa * COALESCE(rate_to_rub, 1)) AS total
                FROM {$this->table}
                WHERE user_id = :user_id AND YEAR(date) = :year AND type IN ('income','expense')
                GROUP BY MONTH(date), type
                ORDER BY MONTH(date)";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['user_id' => $user_id, 'year' => $year]);

        $result = [];
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $result[(int)$row['month']][$row['type']] = (float)$row['total'];
        }
        return $result;
    }

    /**
     * Расходы за месяц по категориям (топ 8) — для кругового распределения на аналитике.
     *
     * @param int $user_id
     * @param int $year
     * @param int $month
     * @return object[] [{title, total}]
     */
    public function getMonthExpenseByCategory(int $user_id, int $year, int $month): array
    {
        $sql = "SELECT c.id AS categorie_id, c.title, SUM(t.summa * COALESCE(t.rate_to_rub, 1)) AS total
                FROM {$this->table} t
                JOIN categories c ON c.id = t.categorie_id
                WHERE t.user_id = :user_id AND t.type = 'expense'
                  AND YEAR(t.date) = :year AND MONTH(t.date) = :month
                GROUP BY c.id, c.title
                ORDER BY total DESC
                LIMIT 8";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['user_id' => $user_id, 'year' => $year, 'month' => $month]);
        return $stmt->fetchAll(PDO::FETCH_OBJ);
    }

    /**
     * Факт за месяц по статьям — сумма реальных операций (income/expense, не transfer — у тех
     * categorie_id = null), в рублях (см. бюджет_текущий_план.md — summa * COALESCE(rate_to_rub, 1)).
     * Для дашборда/плана: план/факт по категории.
     *
     * @param int $user_id
     * @param int $year
     * @param int $month
     * @return array<int,float> [categorie_id => сумма]
     */
    public function getMonthActualByCategory(int $user_id, int $year, int $month): array
    {
        $sql = "SELECT categorie_id, SUM(summa * COALESCE(rate_to_rub, 1)) AS s
                FROM $this->table
                WHERE user_id = :user_id AND categorie_id IS NOT NULL
                  AND YEAR(date) = :year AND MONTH(date) = :month
                GROUP BY categorie_id";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['user_id' => $user_id, 'year' => $year, 'month' => $month]);

        $result = [];

        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $result[(int)$row['categorie_id']] = (float)$row['s'];
        }

        return $result;
    }
}