<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Laporan extends Model
{
    use HasFactory;

    // Tidak menggunakan tabel database karena hanya helper class
    protected $table = null;
    public $timestamps = false;

    /**
     * Get statistics for dashboard
     */
    public static function getStatistics()
    {
        return [
            'total_kasbon' => DB::table('kasbons')->count(),
            'total_teknisi' => DB::table('teknisis')->count(),
            'total_pekerjaan' => DB::table('penugasans')->count(),
            'total_absensi' => DB::table('absensis')
                ->whereMonth('tanggal', date('m'))
                ->whereYear('tanggal', date('Y'))
                ->count(),
            
            // Kasbon statistics
            'total_lunas' => DB::table('kasbons')
                ->where('status', 'lunas')
                ->sum('jumlah_kasbon'),
            'total_belum_lunas' => DB::table('kasbons')
                ->where('status', 'belum_lunas')
                ->sum('jumlah_kasbon'),
            
            // Teknisi statistics
            'teknisi_aktif' => DB::table('teknisis')
                ->where('status', 'aktif')
                ->count(),
            'teknisi_nonaktif' => DB::table('teknisis')
                ->where('status', 'nonaktif')
                ->count(),
            
            // Absensi statistics
            'hadir' => DB::table('absensis')
                ->where('status', 'hadir')
                ->whereMonth('tanggal', date('m'))
                ->whereYear('tanggal', date('Y'))
                ->count(),
            'izin' => DB::table('absensis')
                ->where('status', 'izin')
                ->whereMonth('tanggal', date('m'))
                ->whereYear('tanggal', date('Y'))
                ->count(),
            'sakit' => DB::table('absensis')
                ->where('status', 'sakit')
                ->whereMonth('tanggal', date('m'))
                ->whereYear('tanggal', date('Y'))
                ->count(),
            'alpha' => DB::table('absensis')
                ->where('status', 'alpha')
                ->whereMonth('tanggal', date('m'))
                ->whereYear('tanggal', date('Y'))
                ->count(),
            
            // Pekerjaan statistics - using penugasans table
            'selesai' => DB::table('penugasans')
                ->where('status_pekerjaan', 'selesai')
                ->count(),
            'progress' => DB::table('penugasans')
                ->where('status_pekerjaan', 'proses')
                ->count(),
            'pending' => DB::table('penugasans')
                ->where('status_pekerjaan', 'pending')
                ->count(),
        ];
    }

    /**
     * Get kasbon data with filters
     */
    public static function getKasbonData($filters = [])
    {
        $query = DB::table('kasbons')
            ->leftJoin('teknisis', 'kasbons.id_teknisi', '=', 'teknisis.id_teknisi')
            ->select(
                'kasbons.*',
                'teknisis.nama as nama_teknisi'
            );

        if (!empty($filters['search'])) {
            $query->where(function($q) use ($filters) {
                $q->where('teknisis.nama', 'like', '%' . $filters['search'] . '%')
                  ->orWhere('kasbons.keperluan', 'like', '%' . $filters['search'] . '%');
            });
        }

        if (!empty($filters['tanggal_dari'])) {
            $query->whereDate('kasbons.tanggal_kasbon', '>=', $filters['tanggal_dari']);
        }

        if (!empty($filters['tanggal_sampai'])) {
            $query->whereDate('kasbons.tanggal_kasbon', '<=', $filters['tanggal_sampai']);
        }

        if (!empty($filters['status'])) {
            $query->where('kasbons.status', $filters['status']);
        }

        if (!empty($filters['id_teknisi'])) {
            $query->where('kasbons.id_teknisi', $filters['id_teknisi']);
        }

        return $query->orderBy('kasbons.tanggal_kasbon', 'desc');
    }

    /**
     * Get teknisi data with filters
     */
    public static function getTeknisiData($filters = [])
    {
        // Use raw SQL for groupBy to avoid MySQL strict mode issues
        $query = DB::table('teknisis')
            ->leftJoin('absensis', function($join) use ($filters) {
                $join->on('teknisis.id_teknisi', '=', 'absensis.id_teknisi');
                if (!empty($filters['tanggal_dari'])) {
                    $join->whereDate('absensis.tanggal', '>=', $filters['tanggal_dari']);
                }
                if (!empty($filters['tanggal_sampai'])) {
                    $join->whereDate('absensis.tanggal', '<=', $filters['tanggal_sampai']);
                }
            })
            ->select(
                'teknisis.id_teknisi',
                'teknisis.nama',
                'teknisis.status',
                DB::raw('COUNT(absensis.id_absensi) as total_absensi'),
                DB::raw('COUNT(CASE WHEN absensis.status = "hadir" THEN 1 END) as hadir'),
                DB::raw('COUNT(CASE WHEN absensis.status = "izin" THEN 1 END) as izin'),
                DB::raw('COUNT(CASE WHEN absensis.status = "sakit" THEN 1 END) as sakit'),
                DB::raw('COUNT(CASE WHEN absensis.status = "alpha" THEN 1 END) as alpha')
            )
            ->groupBy('teknisis.id_teknisi', 'teknisis.nama', 'teknisis.status');

        if (!empty($filters['search'])) {
            $query->where('teknisis.nama', 'like', '%' . $filters['search'] . '%');
        }

        return $query->orderBy('teknisis.nama', 'asc');
    }

    /**
     * Get absensi data with filters
     */
    public static function getAbsensiData($filters = [])
    {
        $query = DB::table('absensis')
            ->leftJoin('teknisis', 'absensis.id_teknisi', '=', 'teknisis.id_teknisi')
            ->select(
                'absensis.*',
                'teknisis.nama as nama_teknisi'
            );

        if (!empty($filters['search'])) {
            $query->where('teknisis.nama', 'like', '%' . $filters['search'] . '%');
        }

        if (!empty($filters['tanggal_dari'])) {
            $query->whereDate('absensis.tanggal', '>=', $filters['tanggal_dari']);
        }

        if (!empty($filters['tanggal_sampai'])) {
            $query->whereDate('absensis.tanggal', '<=', $filters['tanggal_sampai']);
        }

        if (!empty($filters['status'])) {
            $query->where('absensis.status', $filters['status']);
        }

        if (!empty($filters['id_teknisi'])) {
            $query->where('absensis.id_teknisi', $filters['id_teknisi']);
        }

        return $query->orderBy('absensis.tanggal', 'desc');
    }

    /**
     * Get pekerjaan data with filters
     */
    public static function getPekerjaanData($filters = [])
    {
        $query = DB::table('penugasans')
            ->leftJoin('teknisis', 'penugasans.id_teknisi', '=', 'teknisis.id_teknisi')
            ->select(
                'penugasans.*',
                'teknisis.nama as nama_teknisi'
            );

        if (!empty($filters['search'])) {
            $query->where(function($q) use ($filters) {
                $q->where('teknisis.nama', 'like', '%' . $filters['search'] . '%')
                  ->orWhere('penugasans.jenis_pekerjaan', 'like', '%' . $filters['search'] . '%')
                  ->orWhere('penugasans.catatan_admin', 'like', '%' . $filters['search'] . '%');
            });
        }

        if (!empty($filters['tanggal_dari'])) {
            $query->whereDate('penugasans.tanggal_mulai', '>=', $filters['tanggal_dari']);
        }

        if (!empty($filters['tanggal_sampai'])) {
            $query->whereDate('penugasans.tanggal_diselesaikan', '<=', $filters['tanggal_sampai']);
        }

        if (!empty($filters['status'])) {
            $query->where('penugasans.status_pekerjaan', $filters['status']);
        }

        if (!empty($filters['id_teknisi'])) {
            $query->where('penugasans.id_teknisi', $filters['id_teknisi']);
        }

        return $query->orderBy('penugasans.created_at', 'desc');
    }
}