<?php

namespace src\Service;

use src\Conexao\Conexao;

class relatorioService
{

    private $conn;

    public function __construct()
    {
        $conexao = new Conexao();
        $this->conn = $conexao->getConexao();
    }

    public function totais($ano)
    {
        $query = "SELECT 
                    (SELECT COUNT(id) FROM cliente WHERE YEAR(data_cadastro) = ?) AS total_clientes,
                    (SELECT SUM(valor) FROM financeiro WHERE YEAR(data) = ? AND situacao = 'entrada') AS total_entradas,
                    (SELECT SUM(valor) FROM financeiro WHERE YEAR(data) = ? AND situacao = 'saida') AS total_saidas";

        $stmt = $this->conn->prepare($query);
        if (!$stmt) {
            throw new \Exception("Erro ao preparar a consulta de total: " . $this->conn->error);
        }

        $stmt->bind_param('iii', $ano, $ano, $ano);

        $stmt->execute();

        $result = $stmt->get_result();

        $registros = [];
        while ($row = $result->fetch_object()) {
            $registros[] = $row;
        }

        return $registros;
    }

    public function financeiroGrafico($dataInicial, $dataFinal)
    {
        $query = "SELECT data, situacao, SUM(valor) as total_valor
                  FROM financeiro 
                  WHERE data >= ? AND data <= ? 
                  GROUP BY data, situacao 
                  ORDER BY data";

        $stmt = $this->conn->prepare($query);
        if (!$stmt) {
            throw new \Exception("Erro ao preparar a consulta de financeiro gráfico: " . $this->conn->error);
        }

        $stmt->bind_param('ss', $dataInicial, $dataFinal);
        $stmt->execute();
        $result = $stmt->get_result();

        $registros = [];
        while ($row = $result->fetch_object()) {
            $registros[] = $row;
        }

        return $registros;
    }

    public function financeiro($dataInicial, $dataFinal) {
        $query = "SELECT situacao, SUM(valor) AS total_valor FROM financeiro WHERE data BETWEEN ? AND ? GROUP BY situacao";

        $stmt = $this->conn->prepare($query);

        if (!$stmt) {
            throw new \Exception("Erro ao preparar a consulta de financeiro: " . $this->conn->error);
        }

        $stmt->bind_param('ss', $dataInicial, $dataFinal);

        $stmt->execute();

        $result = $stmt->get_result();

        $registros = [];
        while ($row = $result->fetch_object()) {
            $registros[] = $row;
        }

        return $registros;
    }
}
