<?php

namespace App\Services;

use App\Models\Taxe;
use Illuminate\Support\Facades\DB;
use InvalidArgumentException;

class TaxeStatService
{
    /**
     * Calculate sum for a specific tax field with optional filters.
     */
    public function calculateSum(string $field, ?string $departmentId = null, ?int $year = null): float
    {
        $column = $this->validateAndGetColumn($field);
        $query = $this->buildBaseQuery($departmentId, $year);

        return $query->sum($column);
    }

    /**
     * Calculate average for a specific tax field with optional filters.
     */
    public function calculateAverage(string $field, ?string $departmentId = null, ?int $year = null): float
    {
        $column = $this->validateAndGetColumn($field);
        $query = $this->buildBaseQuery($departmentId, $year);

        return round($query->avg($column), 2);
    }

    /**
     * Get statistics grouped by location (department or region).
     */
    public function getStatsByLocation(string $groupBy = 'department', ?int $year = null): array
    {
        // Validate groupBy parameter (whitelist approach)
        if (!in_array($groupBy, ['department', 'region'], true)) {
            throw new InvalidArgumentException('Le paramètre group_by doit être "department" ou "region".');
        }

        $query = Taxe::query();

        // Setup grouping
        if ($groupBy === 'region') {
            $query->join('departments', 'taxes.department_id', '=', 'departments.department_id');
            $query->groupBy('departments.region_name');
            $query->select('departments.region_name as location');
        } else {
            $query->groupBy('taxes.department_id');
            $query->select('taxes.department_id as location');
        }

        // Apply year filter if provided
        if ($year !== null) {
            $query->where('taxes.year', $year);
        }

        // Build aggregate selects
        $sqlSelects = $this->buildAggregateSelects();
        $query->addSelect(DB::raw(implode(', ', $sqlSelects)));

        return $query->get()->toArray();
    }

    /**
     * Validate field and return the corresponding column name.
     */
    private function validateAndGetColumn(string $field): string
    {
        if (!in_array($field, Taxe::ALLOWED_STAT_FIELDS)) {
            throw new InvalidArgumentException('Champ invalide');
        }

        return $field . '_amount';
    }

    /**
     * Build base query with optional filters.
     */
    private function buildBaseQuery(?string $departmentId, ?int $year)
    {
        $query = Taxe::query();

        if ($departmentId !== null) {
            $query->where('department_id', $departmentId);
        }

        if ($year !== null) {
            $query->where('year', $year);
        }

        return $query;
    }

    /**
     * Build aggregate SELECT statements for stats query.
     */
    private function buildAggregateSelects(): array
    {
        $sqlSelects = [];

        // Aggregate amount fields
        foreach (Taxe::AMOUNT_FIELDS as $col => $alias) {
            $sqlSelects[] = "SUM({$col}) as {$alias}_total_amount";
            $sqlSelects[] = "ROUND(AVG({$col}), 2) as {$alias}_avg_amount";
        }

        // Aggregate percentage fields
        foreach (Taxe::PERCENTAGE_FIELDS as $col => $alias) {
            $sqlSelects[] = "ROUND(AVG({$col}), 2) as {$alias}_avg_rate";
        }

        return $sqlSelects;
    }
}