lms-v2/app/Http/Controllers/Admin/DashboardController.php

<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use App\Models\User;
use App\Models\Sop;
use App\Models\ExamResult;
use Illuminate\View\View;
use Illuminate\Support\Facades\DB;
use App\Models\Department;
use Carbon\Carbon;

class DashboardController extends Controller
{
    /**
     * 1. Fungsi Dashboard Utama Admin
     */
   public function index(Request $request)
    {
        // 1. FILTER TAHUN & BULAN
        $selectedYear = $request->input('year', Carbon::now()->year);
        $selectedMonth = $request->input('month', Carbon::now()->month);

        // 2. STATISTIK OVERDUE (Training Terlewat)
        // Mengambil penugasan yang deadlinenya sudah lewat dan belum lulus
        $overdueTrainings = DB::table('exam_assignments')
            ->join('users', 'exam_assignments.user_id', '=', 'users.id')
            ->join('exams', 'exam_assignments.exam_id', '=', 'exams.id')
            ->join('departments', 'users.department_id', '=', 'departments.id')
            ->whereMonth('exam_assignments.deadline_date', $selectedMonth)
            ->whereYear('exam_assignments.deadline_date', $selectedYear)
            ->where('exam_assignments.deadline_date', '<', Carbon::now())
            ->where('exam_assignments.status', '!=', 'passed')
            ->select('users.first_name', 'users.last_name', 'exams.title', 'departments.name as dept_name', 'exam_assignments.deadline_date')
            ->orderBy('exam_assignments.deadline_date', 'asc')
            ->take(5) // Ambil 5 teratas untuk list ringkas
            ->get();

        $totalOverdue = DB::table('exam_assignments')
            ->whereMonth('deadline_date', $selectedMonth)
            ->whereYear('deadline_date', $selectedYear)
            ->where('deadline_date', '<', Carbon::now())
            ->where('status', '!=', 'passed')
            ->count();

        // 3. PERSENTASE PEMENUHAN TRAINING PER DEPARTEMEN
        // Menghitung (Total Lulus / Total Ditugaskan) * 100 per departemen
        $departments = Department::all();
        $deptFulfillment = [];

        foreach ($departments as $dept) {
            $totalAssigned = DB::table('exam_assignments')
                ->join('users', 'exam_assignments.user_id', '=', 'users.id')
                ->where('users.department_id', $dept->id)
                ->whereMonth('exam_assignments.created_at', $selectedMonth)
                ->whereYear('exam_assignments.created_at', $selectedYear)
                ->count();

            $totalPassed = DB::table('exam_assignments')
                ->join('users', 'exam_assignments.user_id', '=', 'users.id')
                ->where('users.department_id', $dept->id)
                ->where('exam_assignments.status', 'passed')
                ->whereMonth('exam_assignments.created_at', $selectedMonth)
                ->whereYear('exam_assignments.created_at', $selectedYear)
                ->count();

            $percentage = $totalAssigned > 0 ? round(($totalPassed / $totalAssigned) * 100) : 0;
            
            $deptFulfillment[] = [
                'id' => $dept->id,
                'name' => $dept->name,
                'assigned' => $totalAssigned,
                'passed' => $totalPassed,
                'percentage' => $percentage
            ];
        }

        // Urutkan dari persentase terkecil (yang butuh perhatian) ke terbesar
        usort($deptFulfillment, function($a, $b) {
            return $a['percentage'] <=> $b['percentage'];
        });

        // 4. DATA ACTIVE / ONGOING EXAM (Untuk injeksi awal Realtime)
        // Kita tarik data awal, selanjutnya Alpine.js akan melakukan polling AJAX
        $activeExamsCount = DB::table('exam_attempts')->where('status', 'ongoing')->count();

        // -------------------------------------------------------------------
        // 5. DATA UNTUK KALENDER TRAINING (Berdasarkan Role)
        // -------------------------------------------------------------------
        $user = \Illuminate\Support\Facades\Auth::user();
        
        $calendarQuery = DB::table('exam_assignments')
            ->join('users', 'exam_assignments.user_id', '=', 'users.id')
            ->join('exams', 'exam_assignments.exam_id', '=', 'exams.id')
            ->select(
                'exam_assignments.id', 
                'exams.title as exam_title', 
                'users.first_name', 
                'exam_assignments.created_at', 
                'exam_assignments.deadline_date', 
                'exam_assignments.status'
            );

        // LOGIKA ROLE: Jika BUKAN superadmin, batasi hanya melihat departemennya sendiri
        if (!$user->hasRole('superadmin')) {
            $calendarQuery->where('users.department_id', $user->department_id);
        }

        $calendarData = $calendarQuery->get()->map(function($assign) {
            // Menentukan warna event di kalender berdasarkan status
            $color = '#f59e0b'; // Amber (Pending)
            if ($assign->status === 'passed') $color = '#10b981'; // Emerald (Lulus)
            if ($assign->status === 'ongoing') $color = '#3b82f6'; // Blue (Sedang dikerjakan)
            if ($assign->status != 'passed' && Carbon::parse($assign->deadline_date)->isPast()) {
                $color = '#ef4444'; // Red (Overdue)
            }

            return [
                'id'    => $assign->id,
                'title' => $assign->first_name . ' - ' . $assign->exam_title,
                'start' => Carbon::parse($assign->created_at)->format('Y-m-d'), // Tanggal ditugaskan
                'end'   => Carbon::parse($assign->deadline_date)->addDay()->format('Y-m-d'), // Tanggal deadline (+1 agar FullCalendar merender sampai hari H penuh)
                'color' => $color,
                'allDay'=> true
            ];
        });

        return view('pages.admin.dashboard.dashboard', compact(
            'selectedYear', 'selectedMonth', 'overdueTrainings', 'totalOverdue', 'deptFulfillment', 'activeExamsCount', 'calendarData' // <--- Tambahkan ini
        ));
    }

    // ==========================================================
    // FUNGSI API UNTUK REAL-TIME MONITORING (AJAX Polling)
    // ==========================================================
    public function getLiveExams()
    {
        $liveExams = DB::table('exam_attempts')
            ->join('exam_assignments', 'exam_attempts.exam_assignment_id', '=', 'exam_assignments.id')
            ->join('users', 'exam_assignments.user_id', '=', 'users.id')
            ->join('exams', 'exam_assignments.exam_id', '=', 'exams.id')
            ->join('departments', 'users.department_id', '=', 'departments.id')
            ->where('exam_attempts.status', 'ongoing')
            ->select(
                'exam_attempts.id as attempt_id',
                'users.first_name',
                'users.last_name',
                'departments.name as dept_name',
                'exams.title as exam_title',
                'exam_attempts.started_at',
                'exams.duration_minutes'
            )
            ->get()
            ->map(function ($exam) {
                // Kalkulasi sisa waktu secara server-side
                $started = Carbon::parse($exam->started_at);
                $endsAt = $started->copy()->addMinutes($exam->duration_minutes);
                $exam->time_left_seconds = Carbon::now()->diffInSeconds($endsAt, false); // Minus jika kelebihan
                return $exam;
            });

        return response()->json($liveExams);
    }

    /**
     * 2. Fungsi untuk menu Departemen & Posisi (Master Data)
     */
    public function masterIndex(): View
    {
        return view('pages.admin.master-data.index');
    }

    public function openSopFile($id)
    {
        // 1. Cari tahu versi terbaru dari dokumen ini di database
        $sop = \Illuminate\Support\Facades\DB::connection('plantdoc')
            ->table('tblDocumentContent')
            ->where('document', $id)
            ->orderBy('version', 'desc')
            ->first();

        // Jika file tidak ada di database, kembalikan ke halaman sebelumnya dengan pesan error
        if (!$sop) {
            return back()->with('error', 'File PDF untuk dokumen ini belum diunggah atau tidak ditemukan.');
        }

        $versi = $sop->version;

        // 2. Susun URL persis seperti fitur "View Online" bawaan SeedDMS
        $seeddmsUrl = "http://192.168.10.11/plantlib/plantlibrary/op/op.ViewOnline.php?documentid=" . $id . "&version=" . $versi;
        
        // 3. Lontarkan pengguna ke URL tersebut
        return redirect()->away($seeddmsUrl);
    }

    /**
     * 4. Fungsi untuk menu Dokumen SOP
     */
    public function sopIndex(\Illuminate\Http\Request $request)
    {
        $search = $request->input('search');
        $department = $request->input('department');
        $category = $request->input('category');
        $version = $request->input('version');

        // 1. Departemen
        $departments = \Illuminate\Support\Facades\DB::connection('plantdoc')
            ->table('tblFolders')
            ->where('parent', 59)
            ->orderBy('name', 'asc')
            ->get();

        // 2. Kategori
        $categories = \Illuminate\Support\Facades\DB::connection('plantdoc')
            ->table('tblCategory')
            ->orderBy('name', 'asc')
            ->get();

        // 3. Versi: Diambil dengan Logika Fallback (Cek Nomor -> Cek Judul)
        $allDocs = \Illuminate\Support\Facades\DB::connection('plantdoc')
            ->table('tblDocuments')
            ->select('name', 'comment')
            ->get();
            
        $versions = $allDocs->map(function ($doc) {
            // Prioritas 1: 2 Karakter Terakhir dari Nomor SOP
            $vName = substr(trim($doc->name), -2);
            if (is_numeric($vName)) return $vName;
            
            // Prioritas 2: 2 Karakter Terakhir dari Judul SOP
            $vComment = substr(trim($doc->comment), -2);
            if (is_numeric($vComment)) return $vComment;
            
            return null; // Abaikan jika keduanya tidak memiliki akhiran angka
        })->filter()->unique()->sort()->values();

        // 4. Query Utama
        $query = \Illuminate\Support\Facades\DB::connection('plantdoc')
            ->table('tblDocuments as d')
            ->leftJoin('tblFolders as f_doc', 'd.folder', '=', 'f_doc.id')
            ->leftJoin('tblFolders as f_parent', 'f_doc.parent', '=', 'f_parent.id')
            ->leftJoin('tblDocumentCategory as dc', 'd.id', '=', 'dc.documentID')
            ->leftJoin('tblCategory as cat', 'dc.categoryID', '=', 'cat.id')
            ->leftJoin('tblDocumentContent as c', 'd.id', '=', 'c.document')
            ->select(
                'd.id',
                'd.name as nomor_sop',
                'd.comment as judul_sop',
                'c.orgFileName as file_pdf',
                'c.dir as direktori',
                'd.date as tanggal_dibuat',
                'cat.name as nama_kategori',
                \Illuminate\Support\Facades\DB::raw("
                    CASE 
                        WHEN f_doc.parent = 59 THEN f_doc.name
                        WHEN f_parent.parent = 59 THEN f_parent.name
                        ELSE f_doc.name 
                    END as nama_departemen
                ")
            );

        // 5. Logika Filter
        if ($search) {
            $query->where(function($q) use ($search) {
                $q->where('d.name', 'LIKE', "%{$search}%")
                  ->orWhere('d.comment', 'LIKE', "%{$search}%");
            });
        }
        if ($department) {
            $query->where(function($q) use ($department) {
                $q->where('f_doc.id', $department)
                  ->orWhere('f_doc.parent', $department)
                  ->orWhere('f_doc.folderList', 'LIKE', "%:{$department}:%");
            });
        }
        if ($category) {
            $query->where('dc.categoryID', $category);
        }
        
        // Filter Versi Cerdas SQL (Jika 2 digit nama bukan angka, cari di judul)
        if ($version !== null && $version !== '') {
            $query->where(function($q) use ($version) {
                $q->whereRaw("RIGHT(TRIM(d.name), 2) = ?", [$version])
                  ->orWhere(function($sub) use ($version) {
                      $sub->whereRaw("RIGHT(TRIM(d.name), 2) NOT REGEXP '^[0-9]+$'")
                          ->whereRaw("RIGHT(TRIM(d.comment), 2) = ?", [$version]);
                  });
            });
        }

        // 6. Logika Ekspor dengan Metadata
        if ($request->has('export')) {
            $dataExport = $query->orderBy('d.date', 'desc')->get();
            
            $totalRecords = $dataExport->count();
            $downloader = auth()->user()->first_name . ' ' . auth()->user()->last_name;
            $downloadTime = now()->format('d M Y H:i:s');
            
            if ($request->export == 'excel') {
                return $this->exportCsv($dataExport, $totalRecords, $downloader, $downloadTime);
            } elseif ($request->export == 'pdf') {
                $pdf = \Barryvdh\DomPDF\Facade\Pdf::loadView('pages.admin.sops.pdf', compact('dataExport', 'totalRecords', 'downloader', 'downloadTime'))
                            ->setPaper('a4', 'landscape');
                return $pdf->download('Data_Dokumen_Tunggal_Pharma.pdf');
            }
        }

        $sops = $query->orderBy('d.date', 'desc')->paginate(20);

        return view('pages.admin.sops.index', compact('sops', 'departments', 'categories', 'versions'));
    }

    private function exportCsv($data, $totalRecords, $downloader, $downloadTime)
    {
        $fileName = 'Daftar_Dokumen_' . date('Ymd_His') . '.csv';
        $headers = [
            "Content-type"        => "text/csv",
            "Content-Disposition" => "attachment; filename=$fileName",
            "Pragma"              => "no-cache",
            "Cache-Control"       => "must-revalidate, post-check=0, pre-check=0",
            "Expires"             => "0"
        ];

        $callback = function() use($data, $totalRecords, $downloader, $downloadTime) {
            $file = fopen('php://output', 'w');
            
            fputcsv($file, ['DAFTAR MASTER DOKUMEN - TUNGGAL PHARMA']);
            fputcsv($file, ['Diunduh Oleh:', $downloader]);
            fputcsv($file, ['Waktu Unduh:', $downloadTime . ' WIB']);
            fputcsv($file, ['Total Record:', $totalRecords . ' Dokumen']);
            fputcsv($file, []); 
            
            fputcsv($file, ['No', 'Departemen/Folder', 'Kategori', 'Nomor Dokumen', 'Judul Dokumen', 'Versi', 'Tanggal Upload']);

            $i = 1;
            foreach ($data as $row) {
                // Logika Ekstraksi Versi untuk Excel
                $versi = substr(trim($row->nomor_sop), -2);
                if (!is_numeric($versi)) {
                    $versi = substr(trim($row->judul_sop), -2);
                    if (!is_numeric($versi)) $versi = '-';
                }

                $tanggal = \Carbon\Carbon::createFromTimestamp($row->tanggal_dibuat)->format('d-m-Y');
                
                fputcsv($file, [
                    $i++, 
                    $row->nama_departemen ?? '-', 
                    $row->nama_kategori ?? '-', 
                    $row->nomor_sop, 
                    $row->judul_sop, 
                    "v." . $versi, 
                    $tanggal
                ]);
            }
            fclose($file);
        };

        return response()->stream($callback, 200, $headers);
    }

    // ==========================================================
    // FUNGSI UNTUK HALAMAN DETAIL PEMENUHAN DEPARTEMEN
    // ==========================================================
    public function departmentFulfillment($id, Request $request)
    {
        $department = Department::findOrFail($id);
        $selectedMonth = $request->input('month', Carbon::now()->month);
        $selectedYear = $request->input('year', Carbon::now()->year);

        // Ambil detail penugasan ujian khusus departemen ini di bulan/tahun yang dipilih
        $assignments = DB::table('exam_assignments')
            ->join('users', 'exam_assignments.user_id', '=', 'users.id')
            ->join('exams', 'exam_assignments.exam_id', '=', 'exams.id')
            ->where('users.department_id', $id)
            ->whereMonth('exam_assignments.created_at', $selectedMonth)
            ->whereYear('exam_assignments.created_at', $selectedYear)
            ->select(
                'users.first_name', 'users.last_name', 'users.nik', 
                'exams.title', 'exam_assignments.status', 
                'exam_assignments.deadline_date', 'exam_assignments.created_at'
            )
            ->orderBy('exam_assignments.created_at', 'desc')
            ->paginate(20);

        return view('pages.admin.dashboard.department-detail', compact('department', 'assignments', 'selectedMonth', 'selectedYear'));
    }

}