<?php

namespace App\Http\Controllers;

use App\Models\Taxe;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class TaxeStatController extends Controller
{
    public function sum(Request $request, string $field)
    {
        $allowedFields = ['tfpnb_amount', 'tfpb_amount', 'th_amount', 'cfe_amount'];
        $column = $field . '_amount'; 

        if (!in_array($column, $allowedFields)) {
            return response()->json(['error' => 'Champ invalide'], 400);
        }

        $query = Taxe::query();

        if ($request->has('department_id')) {
            $query->where('department_id', $request->input('department_id'));
        }
        
        if ($request->has('year')) {
            $query->where('year', $request->input('year'));
        }

        $total = $query->sum($column);

        return response()->json([
            'field' => $field,
            'sum' => $total,
            'filters' => $request->all()
        ]);
    }

    public function average(Request $request, string $field)
    {
        $allowedFields = ['tfpnb_amount', 'tfpb_amount', 'th_amount', 'cfe_amount'];
        $column = $field . '_amount'; 

        if (!in_array($column, $allowedFields)) {
            return response()->json(['error' => 'Champ invalide'], 400);
        }

        $query = Taxe::query();

        if ($request->has('department_id')) {
            $query->where('department_id', $request->input('department_id'));
        }
        
        if ($request->has('year')) {
            $query->where('year', $request->input('year'));
        }

        $average = $query->avg($column);

        return response()->json([
            'field' => $field,
            'average' => round($average, 2),
            'filters' => $request->all()
        ]);
    }

public function statsByLocation(Request $request)
{
    $groupBy = $request->input('group_by', 'department'); 
    
    $query = Taxe::query();

    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');
    }

    if ($request->has('year')) {
        $query->where('taxes.year', $request->input('year'));
    }

    $sqlSelects = [];

    $amounts = [
        'tfpnb_amount' => 'tfpnb',
        'tfpb_amount'  => 'tfpb',
        'th_amount'    => 'th',
        'cfe_amount'   => 'cfe'
    ];

    foreach ($amounts as $col => $alias) {
        $sqlSelects[] = "SUM($col) as {$alias}_total_amount";
        $sqlSelects[] = "ROUND(AVG($col), 2) as {$alias}_avg_amount";
    }

    $rates = [
        'tfpnb_percentage' => 'tfpnb',
        'tfpb_percentage'  => 'tfpb',
        'th_percentage'    => 'th',
        'cfe_percentage'   => 'cfe'
    ];

    foreach ($rates as $col => $alias) {
        $sqlSelects[] = "ROUND(AVG($col), 2) as {$alias}_avg_rate";
    }

    $query->addSelect(DB::raw(implode(', ', $sqlSelects)));

    return response()->json([
        'group_by' => $groupBy,
        'year' => $request->input('year', 'all'),
        'data' => $query->get()
    ]);
}
}