<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use App\Models\WasteTransactions;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

class WasteDashboardController extends Controller
{
    public function index(Request $request)
    {
        // filter opsional: tahun & lokasi
        $year       = $request->input('year', Carbon::now()->year);
        $locationId = $request->input('location_id'); // boleh null

        $baseQuery = WasteTransactions::query()
            ->whereYear('trx_date', $year);

        if ($locationId) {
            $baseQuery->where('location_id', $locationId);
        }

        // ====== RINGKASAN KPI UTAMA ======
        $totalIn = (clone $baseQuery)
            ->where('direction', 'IN')
            ->sum('weight_kg');

        $totalOut = (clone $baseQuery)
            ->where('direction', 'OUT')
            ->sum('weight_kg');

        // Proses keluar (OUT) dianggap "diproses" (kompos, daur ulang, RDF, landfill)
        $totalProcessedOut = $totalOut;

        $totalRecycleOut = (clone $baseQuery)
            ->where('direction', 'OUT')
            ->whereIn('process_type_id', [1, 2, 3]) // 1: Kompos, 2: Daur Ulang, 3: RDF
            ->sum('weight_kg');

        $totalLandfillOut = (clone $baseQuery)
            ->where('direction', 'OUT')
            ->where('process_type_id', 4) // 4: Landfill
            ->sum('weight_kg');

        $recycleRate = $totalProcessedOut > 0
            ? round(($totalRecycleOut / $totalProcessedOut) * 100, 1)
            : 0;

        $landfillRate = $totalProcessedOut > 0
            ? round(($totalLandfillOut / $totalProcessedOut) * 100, 1)
            : 0;

        // ====== TREND BULANAN (UNTUK GRAFIK LINE/BAR) ======
        $monthly = (clone $baseQuery)
            ->selectRaw('
                DATE_FORMAT(trx_date, "%Y-%m") as month,
                SUM(CASE WHEN direction = "IN"  THEN weight_kg ELSE 0 END) as in_kg,
                SUM(CASE WHEN direction = "OUT" THEN weight_kg ELSE 0 END) as out_kg
            ')
            ->groupBy('month')
            ->orderBy('month')
            ->get();

        // ====== KOMPOSISI BERDASARKAN KATEGORI (PIE CHART) ======
        $byCategory = (clone $baseQuery)
            ->join('master_waste_categories', 'master_waste_categories.id', '=', 'waste_transactions.category_id')
            ->selectRaw('
        waste_transactions.category_id,
        master_waste_categories.name as category_name,
        master_waste_categories.color_hex,
        SUM(CASE WHEN direction = "IN"  THEN weight_kg ELSE 0 END) as in_kg,
        SUM(CASE WHEN direction = "OUT" THEN weight_kg ELSE 0 END) as out_kg,
        SUM(weight_kg) as total_kg
    ')
            ->groupBy('waste_transactions.category_id', 'master_waste_categories.name', 'master_waste_categories.color_hex')
            ->get();

        // ====== KOMPOSISI BERDASARKAN JENIS PROSES (UNTUK GRAFIK DONUT/PIE) ======
        $byProcessType = (clone $baseQuery)
            ->where('direction', 'OUT')
            ->join('master_process_type', 'master_process_type.id', '=', 'waste_transactions.process_type_id')
            ->selectRaw('
        waste_transactions.process_type_id,
        master_process_type.name_process_type,
        SUM(weight_kg) as total_kg
    ')
            ->groupBy('waste_transactions.process_type_id', 'master_process_type.name_process_type')
            ->get();

        // ====== DATA UNTUK KPI "TINGKAT DAUR ULANG PER BULAN" ======
        $monthlyRecycleRate = (clone $baseQuery)
            ->where('direction', 'OUT')
            ->selectRaw('
                DATE_FORMAT(trx_date, "%Y-%m") as month,
                SUM(weight_kg) as total_out_kg,
                SUM(CASE WHEN process_type_id IN (1,2,3) THEN weight_kg ELSE 0 END) as recycle_kg
            ')
            ->groupBy('month')
            ->orderBy('month')
            ->get()
            ->map(function ($row) {
                $rate = $row->total_out_kg > 0
                    ? round(($row->recycle_kg / $row->total_out_kg) * 100, 1)
                    : 0;

                return [
                    'month'        => $row->month,
                    'total_out_kg' => (float) $row->total_out_kg,
                    'recycle_kg'   => (float) $row->recycle_kg,
                    'recycle_rate' => $rate,
                ];
            });

        return response()->json([
            'filters' => [
                'year'        => (int) $year,
                'location_id' => $locationId,
            ],
            'summary' => [
                'total_in_kg'        => (float) $totalIn,
                'total_out_kg'       => (float) $totalOut,
                'net_kg'             => (float) ($totalIn - $totalOut),
                'recycle_out_kg'     => (float) $totalRecycleOut,
                'landfill_out_kg'    => (float) $totalLandfillOut,
                'recycle_rate_pct'   => $recycleRate,
                'landfill_rate_pct'  => $landfillRate,
            ],
            'charts' => [
                'monthly_in_out'       => $monthly,
                'by_category'          => $byCategory,
                'by_process_type_out'  => $byProcessType,
                'monthly_recycle_rate' => $monthlyRecycleRate,
            ],
        ]);
    }
}
