expense/app/Services/DashboardService.php

<?php

namespace App\Services;

use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

class DashboardService
{
    protected $startDate;
    protected $endDate;
    protected $allowedCabangIds = [];
    protected $user;

    public function __construct($user)
    {
        $this->user = $user;
        $this->setPeriode();
        $this->setUserVisibility();
    }

    /**
     * Set Periode Cut-off (11 - 10)
     */
    protected function setPeriode(): void
    {
        $startDay = (int) env('STARTING_DATE', 11);
        $closingDay = (int) env('CLOSING_DATE', 10);
        $now = Carbon::now();

        if ($now->day >= $startDay) {
            $this->startDate = $now->copy()->day($startDay)->startOfDay();
            $this->endDate = $now->copy()->addMonth()->day($closingDay)->endOfDay();
        } else {
            $this->startDate = $now->copy()->subMonth()->day($startDay)->startOfDay();
            $this->endDate = $now->copy()->day($closingDay)->endOfDay();
        }
    }

    /**
     * Atur Visibilitas Berdasarkan Hierarki Role
     */
    protected function setUserVisibility(): void
    {
        // 1. MIS / HEAD / SUPERADMIN: Akses Nasional
        if ($this->user->hasRole(['Marketing Information System', 'Head of Sales Management', 'Super Admin'])) {
            $this->allowedCabangIds = []; 
            return;
        }

        $myCabangs = DB::table('user_has_cabang')
            ->where('user_id', $this->user->id)
            ->whereNull('deleted_at')
            ->pluck('cabang_id')
            ->toArray();

        // 2. MOM / ADMIN REGION: Akses Regional (Semua cabang di dalam regionnya)
        if ($this->user->hasRole(['Marketing Operational Manager Region', 'Admin Region'])) {
            $regionIds = DB::table('cabang')
                ->whereIn('id', $myCabangs)
                ->pluck('region_id')
                ->toArray();

            $this->allowedCabangIds = DB::table('cabang')
                ->whereIn('region_id', $regionIds)
                ->pluck('id')
                ->toArray();
        } 
        // 3. AM / MR: Akses Cabang Spesifik
        else {
            $this->allowedCabangIds = $myCabangs;
        }
    }

    /**
     * Helper Filter: Digunakan di semua query analitik
     */
    protected function applyFilters($query)
    {
        if (!empty($this->allowedCabangIds)) {
            $query->whereIn('combined_forms.cabang_id', $this->allowedCabangIds);
        }
        return $query;
    }

    /**
     * Query Utama: Menggabungkan 5 tabel kategori expense
     */
    protected function getCombinedFormsQuery()
    {
        $tables = [
            'form_up_country', 
            'form_vehicle_running_cost', 
            'form_entertainment_presentation', 
            'form_meeting_seminar', 
            'form_others'
        ];
        
        $query = null;
        foreach ($tables as $table) {
            $sub = DB::table($table)
                ->select(
                    'id', 
                    'cabang_id', 
                    'total', 
                    'status', 
                    'created_at', 
                    'final_approved_at',
                    DB::raw("'$table' as source_table")
                )
                ->whereNull('deleted_at');
            $query = ($query === null) ? $sub : $query->unionAll($sub);
        }

        return DB::table(DB::raw("({$query->toSql()}) as combined_forms"))
            ->mergeBindings($query)
            ->join('cabang', 'combined_forms.cabang_id', '=', 'cabang.id')
            ->join('region', 'cabang.region_id', '=', 'region.id');
    }

    /**
     * Data untuk 4 Kartu Statistik Utama
     */
    public function getApprovalStats(): array
{
    // Ambil semua data tanpa filter tanggal dulu untuk kartu "Waiting"
    $allData = $this->getCombinedFormsQuery()
        ->select('combined_forms.status', 'combined_forms.total', 'combined_forms.created_at')
        ->get();

    $map = [
        // 1. TOTAL PENDING: Khusus periode aktif (11 Maret - 10 April) & Status On Progress
        'total_pending' => [
            'label' => 'Total Pending', 
            'keys'  => ['on progress'], 
            'color' => 'warning', 
            'icon'  => 'fas fa-hourglass-half',
            'use_period' => true 
        ],
        // 2. WAITING APPR 1: Semua yang statusnya On Progress
        'appr1' => [
            'label' => 'Waiting Appr 1', 
            'keys'  => ['on progress'], 
            'color' => 'info', 
            'icon'  => 'fas fa-user-check',
            'use_period' => false
        ],
        // 3. WAITING APPR 2: Semua yang statusnya Approved 1
        'appr2' => [
            'label' => 'Waiting Appr 2', 
            'keys'  => ['approved 1'], 
            'color' => 'primary', 
            'icon'  => 'fas fa-file-invoice-dollar',
            'use_period' => false
        ],
        // 4. WAITING FINAL APPROVED: Semua yang statusnya Approved 2
        'waiting_final' => [
            'label' => 'Waiting Final Approved', 
            'keys'  => ['approved 2'], 
            'color' => 'secondary', 
            'icon'  => 'fas fa-tasks',
            'use_period' => false
        ],
        // 5. FINAL APPROVED (CLOSED): Semua yang sudah Closed
        'closed' => [
            'label' => 'Final Approved', 
            'keys'  => ['closed', 'approved'], 
            'color' => 'success', 
            'icon'  => 'fas fa-check-double',
            'use_period' => false
        ],
    ];

    $results = [];
    foreach ($map as $config) {
        $filtered = $allData->filter(function ($item) use ($config) {
            $statusMatch = in_array(strtolower(trim((string)$item->status)), $config['keys']);
            
            // Jika kartu membutuhkan filter periode (Maret 11 - April 10)
            if ($config['use_period']) {
                $dateMatch = Carbon::parse($item->created_at)->between($this->startDate, $this->endDate);
                return $statusMatch && $dateMatch;
            }

            return $statusMatch;
        });

        $results[] = [
            'label'  => $config['label'],
            'count'  => $filtered->count(),
            'amount' => (float) $filtered->sum('total'),
            'color'  => $config['color'],
            'icon'   => $config['icon']
        ];
    }
    return $results;
}

    /**
     * Mengambil daftar pengajuan dengan status 'Approved 2'
     * Untuk antrian persetujuan akhir (HOSM/MIS)
     */
    public function getPendingFinalApproval()
    {
        $query = $this->getCombinedFormsQuery()
            ->select(
                'combined_forms.*', 
                'cabang.name as cabang_name', 
                'region.name as region_name'
            )
            ->where('combined_forms.status', 'Approved 2');

        $this->applyFilters($query);

        return $query->orderBy('combined_forms.created_at', 'desc')->paginate(10);
    }

    /**
     * Summary per Kategori untuk Chart
     */
    public function getTotals(): array
    {
        $query = $this->getCombinedFormsQuery()
            ->whereBetween('combined_forms.created_at', [$this->startDate, $this->endDate])
            ->whereIn('combined_forms.status', ['Approved', 'Closed']); 

        $this->applyFilters($query);

        $results = $query->select('combined_forms.source_table', DB::raw('SUM(combined_forms.total) as total_per_table'))
            ->groupBy('combined_forms.source_table')
            ->get();

        return [
            'forms' => [
                'upcountry'     => $results->where('source_table', 'form_up_country')->first()->total_per_table ?? 0,
                'vehicle'       => $results->where('source_table', 'form_vehicle_running_cost')->first()->total_per_table ?? 0,
                'entertainment' => $results->where('source_table', 'form_entertainment_presentation')->first()->total_per_table ?? 0,
                'meeting'       => $results->where('source_table', 'form_meeting_seminar')->first()->total_per_table ?? 0,
                'others'        => $results->where('source_table', 'form_others')->first()->total_per_table ?? 0,
            ],
            'grand_total' => $results->sum('total_per_table')
        ];
    }

    /**
     * Top 5 Pengeluaran Terbanyak per Cabang
     */
   /**
 * Top 10 Pengeluaran Terbanyak (Volume Giants)
 */
public function getTopExpenses()
{
    $query = $this->getCombinedFormsQuery()
        ->whereBetween('combined_forms.created_at', [$this->startDate, $this->endDate]);

    $this->applyFilters($query);

    return $query->select('cabang.name', 'region.name as region_name', DB::raw('SUM(combined_forms.total) as total_amount'))
                 ->groupBy('cabang.id', 'cabang.name', 'region.name')
                 ->orderBy('total_amount', 'desc')
                 ->take(10) // Update ke 10
                 ->get();
}

/**
 * Top 10 Efisiensi Approval (Speed Kings)
 */
public function getFastestClosing()
{
    $query = $this->getCombinedFormsQuery()
        ->select('cabang.name', DB::raw('AVG(TIMESTAMPDIFF(HOUR, combined_forms.created_at, combined_forms.final_approved_at)) as avg_hours'))
        ->whereIn('combined_forms.status', ['Approved', 'Closed'])
        ->whereNotNull('combined_forms.final_approved_at')
        ->whereBetween('combined_forms.final_approved_at', [$this->startDate, $this->endDate]);

    $this->applyFilters($query);

    return $query->groupBy('cabang.id', 'cabang.name')
                 ->orderBy('avg_hours', 'asc')
                 ->take(10) // Update ke 10
                 ->get();
}

}