<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class TargetReportController extends Controller
{
    public function getTargetAchievement(Request $request)
    {
        try {
            $user = auth()->user();
            $requestedBranch = $request->query('branch_id') ?? $request->branch_id;
            $branchId = ($user && !in_array($user->role, ['owner', 'super_admin'])) ? $user->branch_id : $requestedBranch;

            $startDate = $request->start_date ?? date('Y-m-01');
            $endDate = $request->end_date ?? date('Y-m-t');

            $query = DB::table('users as spv')
                ->join('salesmen as sls', 'spv.id', '=', 'sls.supervisor_id')
                ->leftJoin('salesman_targets as trg', function($join) use ($startDate, $endDate) {
                    $join->on('sls.id', '=', 'trg.salesman_id')
                         ->whereBetween('trg.start_date', [$startDate, $endDate]);
                })
                ->select(
                    'spv.id as spv_id', 'spv.name as spv_name', 'sls.id as salesman_id', 'sls.code as salesman_code',
                    DB::raw("(SELECT name FROM users WHERE id = sls.user_id) as salesman_name"),
                    DB::raw("IFNULL(trg.target_value_rp, 0) as target_nominal"),
                    'trg.id as target_header_id',
                    DB::raw("IFNULL(trg.is_value_target, 0) as is_value_target"),
                    DB::raw("IFNULL(trg.is_quantity_target, 0) as is_quantity_target")
                );

            if (!empty($branchId) && $branchId !== 'all') {
                $query->where('spv.branch_id', $branchId);
            }

            $rawData = $query->get();

            $report = $rawData->map(function ($item) use ($startDate, $endDate) {
                // --- A. KALKULASI VALUE ---
                $salesDpp = 0; $returnsDpp = 0;
                if ($item->is_value_target) {
                    $salesDpp = DB::table('sales')
                        ->where('delivery_status', '!=', 'void')
                        ->whereExists(function ($q) use ($item) {
                            $q->select(DB::raw(1))->from('customers')
                              ->whereColumn('customers.id', 'sales.customer_id')
                              ->where('customers.salesman_id', $item->salesman_id);
                        })
                        ->whereBetween('created_at', [$startDate . ' 00:00:00', $endDate . ' 23:59:59'])
                        ->sum(DB::raw('(subtotal - discount_amount) - tax_amount'));

                    if (Schema::hasTable('sale_returns')) {
                        $returnsDpp = DB::table('sale_returns')
                            ->join('sales', 'sale_returns.sale_id', '=', 'sales.id')
                            ->whereExists(function ($q) use ($item) {
                                $q->select(DB::raw(1))->from('customers')
                                  ->whereColumn('customers.id', 'sales.customer_id')
                                  ->where('customers.salesman_id', $item->salesman_id);
                            })
                            ->whereBetween('sale_returns.created_at', [$startDate . ' 00:00:00', $endDate . ' 23:59:59'])
                            ->sum('total_refund');
                    }
                }
                $netValue = (float)$salesDpp - (float)$returnsDpp;

                // --- B. KALKULASI QUANTITY (PROTEKSI BONUS & KOLOM UNIT_PRICE) ---
$targetQty = 0; $realisasiQtySales = 0; $realisasiQtyRetur = 0;
if ($item->target_header_id && $item->is_quantity_target) {
    $targetItems = DB::table('salesman_target_items')->where('target_id', $item->target_header_id)->get();
    foreach ($targetItems as $ti) {
        $targetQty += $ti->target_qty;
        $prod = DB::table('products')->where('id', $ti->reference_id)->first();
        
        $convFactor = 1;
        if ($prod) {
            // Deteksi satuan target untuk pembagi konversi
            if ($ti->unit == $prod->buy_unit) $convFactor = (float)$prod->conversion_factor;
            elseif ($ti->unit == $prod->unit_3_name) $convFactor = (float)$prod->conversion_3_qty;
            elseif ($ti->unit == $prod->unit_4_name) $convFactor = (float)$prod->conversion_4_qty;
        }

        // PERBAIKAN: Gunakan 'unit_price' sesuai skema tabel sale_items
        $realisasiQtySales += DB::table('sale_items')
            ->join('sales', 'sale_items.sale_id', '=', 'sales.id')
            ->where('sale_items.product_id', $ti->reference_id)
            ->where('sales.delivery_status', '!=', 'void')
            ->where('sale_items.unit_price', '>', 0) // <--- PENGGUNAAN KOLOM YANG BENAR: unit_price
            ->whereExists(function ($q) use ($item) {
                $q->select(DB::raw(1))->from('customers')
                  ->whereColumn('customers.id', 'sales.customer_id')
                  ->where('customers.salesman_id', $item->salesman_id);
            })
            ->whereBetween('sales.created_at', [$startDate . ' 00:00:00', $endDate . ' 23:59:59'])
            ->sum(DB::raw('sale_items.quantity / ' . $convFactor));

        if (Schema::hasTable('sale_return_items')) {
            $realisasiQtyRetur += DB::table('sale_return_items')
                ->join('sale_returns', 'sale_return_items.sales_return_id', '=', 'sale_returns.id')
                ->join('sales', 'sale_returns.sale_id', '=', 'sales.id')
                ->where('sale_return_items.product_id', $ti->reference_id)
                ->whereBetween('sale_returns.created_at', [$startDate . ' 00:00:00', $endDate . ' 23:59:59'])
                ->sum(DB::raw('sale_return_items.quantity / ' . $convFactor));
        }
    }
}   
                $netQty = (float)$realisasiQtySales - (float)$realisasiQtyRetur;

                return [
                    'salesman_id' => $item->salesman_id, 'salesman_code' => $item->salesman_code, 'salesman_name' => $item->salesman_name,
                    'spv_id' => $item->spv_id, 'spv_name' => $item->spv_name,
                    'target_value' => (float)$item->target_nominal, 'realisasi_value' => $netValue, 'ach_value' => $item->target_nominal > 0 ? round(($netValue / $item->target_nominal) * 100, 2) : 0,
                    'target_qty' => (float)$targetQty, 'realisasi_qty' => $netQty, 'ach_qty' => $targetQty > 0 ? round(($netQty / $targetQty) * 100, 2) : 0,
                    'is_value_target' => (bool)$item->is_value_target, 'is_quantity_target' => (bool)$item->is_quantity_target
                ];
            });

            $grouped = $report->groupBy('spv_id')->map(function ($items) {
                $first = $items->first();
                $tVal = $items->sum('target_value'); $rVal = $items->sum('realisasi_value');
                $tQty = $items->sum('target_qty'); $rQty = $items->sum('realisasi_qty');
                return [
                    'supervisor_name' => $first['spv_name'], 'target_value' => $tVal, 'realisasi_value' => $rVal,
                    'ach_value' => $tVal > 0 ? round(($rVal / $tVal) * 100, 2) : 0, 'target_qty' => $tQty,
                    'realisasi_qty' => $rQty, 'ach_qty' => $tQty > 0 ? round(($rQty / $tQty) * 100, 2) : 0,
                    'has_value_target' => $items->contains('is_value_target', true),
                    'has_qty_target' => $items->contains('is_quantity_target', true),
                    'details' => $items->values()
                ];
            })->values();

            return response()->json(['success' => true, 'data' => $grouped], 200);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => 'Gagal: ' . $e->getMessage()], 500);
        }
    }
}