<?php

namespace App\Http\Controllers;

use DB;
use Carbon\Carbon;
use App\Models\Peserta;
use BaconQrCode\Writer;
use App\Models\Lokasi_run;
use App\Models\participant;
use Illuminate\Support\Str;
use Illuminate\Http\Request;
use App\Models\company_event;
use App\Models\KategoryEvent;
use App\Models\participant_company_event;
use App\Exports\PesertaExport;
use App\Imports\PesertaImport;
use App\Models\kategori_event;
use App\Models\ticket_company;
use App\Exports\PesertaExportKD;
use App\Exports\PesertaExportMN;
use App\Exports\PesertaExportSB;
use App\Models\Kategori_peserta;
use App\Imports\PesertaBibImport;
use App\Imports\PesertaHasilImport;
use Brian2694\Toastr\Facades\Toastr;
use Illuminate\Support\Facades\File;
use Maatwebsite\Excel\Facades\Excel;
use BaconQrCode\Renderer\ImageRenderer;

use Illuminate\Support\Facades\Session;
use SimpleSoftwareIO\QrCode\Facades\QrCode;
use App\Http\Controllers\RegistrasiController;
use BaconQrCode\Renderer\Image\GdImageBackEnd;
use BaconQrCode\Renderer\RendererStyle\RendererStyle;

class PesertaController extends Controller
{
    public function pesertaList($id)
    {
        $peserta = DB::table('peserta')
                    ->join('participant','peserta.category','=','participant.id')
                    ->join('provinsi','peserta.provinsi','=','provinsi.id_prov')
                    ->join('country_data2','peserta.negara','=','country_data2.id_country')
                    ->where('lokasi_run',$id)
                    ->select(
                        'peserta.*',
                        'peserta.updated_at as peserta_updated_at', // untuk pastikan ini dari peserta
                        'participant.*',
                        'country_data2.*',
                        'provinsi.*'
                    )
                    ->get();
        $all = $peserta->count();
        $paid = $peserta->where('pembayaran','Paid')->count();
        $exp = $peserta->where('pembayaran','!=','Paid')->count();            
        $lok = company_event::where('event_id',$id)->value('event_name'); 
        $kd_lok = $id;           

        return view('peserta.peserta', compact('peserta','lok','all','paid','exp','kd_lok'));
    }

    public function racepacklist($id)
    {
        $peserta = DB::table('peserta')
                    ->where('lokasi_run',$id)
                    ->where('pembayaran','Paid')
                    ->get();

        $lokasi = company_event::where('event_id',$id)->get();

        return view('peserta.raceBW',compact('lokasi','peserta'));
    }

    
    public function gudangSb($id)
    {
        $lok = company_event::where('event_id',$id)->get();
       
        $peserta = peserta::where('lokasi_run',$id)->where('pembayaran','Paid')->where('konfirmasi_kehadiran','=','1')->get();
        return view('peserta.gudangBw',compact('peserta','lok'));
    }


    public function grafikReport($id)
   {
        $kota = $id;
        $kota1 = 'SB';
        $peserta = Peserta::where('lokasi_run', $kota)->get();

        $t_all = $peserta->unique('full_name')->count();

        $paid = $peserta->where('pembayaran', 'Paid')->unique('full_name');
        $t_peserta = $paid->count();

        $pes = Peserta::where('lokasi_run', $kota)
                    ->where('pembayaran', 'Paid') // filter yang paid
                    ->get();    // Hitung jumlah full_name yang duplikat

        $duplicateCount = $pes->groupBy('full_name')  // Kelompokkan berdasarkan full_name
                        ->filter(function ($items) {
                            return $items->count() > 1;  // Ambil hanya yang muncul lebih dari sekali
                        })
                        ->count(); // Hitung jumlah grup duplikat

        // cek data double
        // Kelompokkan berdasarkan full_name
            $grouped = $peserta->where('pembayaran', 'Paid')->groupBy('full_name');

            // Ambil hanya yang muncul lebih dari 1 kali
            $duplikat = $grouped->filter(fn($items) => $items->count() > 1);

            // Hitung total baris dalam semua duplikat
            $totalBaris = $duplikat->flatten()->count(); // semua baris duplicate
            $totalUnikFullName = $duplikat->count();      // jumlah grup unik (nama duplikat)
            $extraBaris = $totalBaris - $totalUnikFullName; // jumlah baris ekstra karena duplikat
            $duplik = $duplikat->map->count();

        $unpaid = $peserta->where('pembayaran', '!=', 'Paid')
                    ->reject(function ($item) use ($paid) {
                        return $paid->contains('full_name', $item->full_name);
                    })
                    ->unique('full_name');
        $t_exp = $unpaid->count();
    
        $persen = $t_all > 0 ? number_format(($t_peserta / $t_all) * 100, 2) : 0;
        $target_peserta = company_event::where('event_id',$kota)->value('target_peserta');
        $presentase_pencapaian = $target_peserta > 0 ? round(($t_peserta / $target_peserta) * 100, 2) : 0;
        
        $lokasi = DB::table('company_event')->where('event_id', $kota)->first();

        if (!$lokasi) {
            abort(404, 'Lokasi tidak ditemukan.');
        }

        $start = Carbon::parse($lokasi->start_event);
        $end = Carbon::parse($lokasi->end_event);

        $categories = [];
        $paid = [];
        $unpaid = [];

            for ($date = $start->copy(); $date->lte($end); $date->addDay()) {
                $tanggal = $date->toDateString();
                $categories[] = $tanggal;

                $paidNames = Peserta::whereDate('created_at', $tanggal)
                                ->where('lokasi_run', $kota)
                                ->where('pembayaran', 'Paid')
                                ->pluck('full_name')
                                ->unique();
                // Jumlah paid (hanya nama unik)
                $paid[] = $paidNames->count();                

                $unpaid[] = Peserta::whereDate('created_at', $tanggal)
                                ->where('lokasi_run', $kota)
                                ->where('pembayaran', '!=', 'Paid')
                                ->whereNotIn('full_name', $paidNames)
                                ->distinct('full_name')
                                ->count('full_name');
            }

        // Participant category
        $k_peserta = participant::join('peserta','participant.id','=','peserta.category')
                    ->where('participant_active',1)
                    ->get();
        $kategori_map = $k_peserta->pluck('participant_name', 'id')->toArray();

        // Hitung total tiap kategori
        $kategori_counts_raw = \App\Models\Peserta::select('category', \DB::raw('count(*) as total'))
                    ->where('Pembayaran','Paid')
                    ->where('lokasi_run',$kota)
                    ->groupBy('category')
                    ->pluck('total', 'category');

        // Ubah ke format array untuk Chart JS
        $labels = [];
        $data = [];
        $totalPeserta = $target_peserta; // berdasarkan target company_event
        $jumlah_peserta = [];
       foreach ($kategori_map as $key => $label) {
                $count = $kategori_counts_raw->get($key, 0);
                $percentage = ($totalPeserta > 0) ? round(($count / $totalPeserta) * 100, 2) : 0; // Ambil data, default 0 kalau tidak ada
                
                $labels[] = $label;
                $data[] = $percentage;
                $jumlah_peserta[] = $count;
        }

        // Daily Visit Insights

         // Buat rentang tanggal
        $dateLabels  = collect();
            for ($date = $start->copy(); $date->lte($end); $date->addDay()) {
                $dateLabels->push($date->format('Y-m-d'));
            }

        $subquery = DB::table('peserta')
                    ->selectRaw('MIN(order_id) as order_id') // Gunakan alias yang sesuai
                    ->whereBetween('created_at', [$start, $end])
                    ->where('pembayaran', 'Paid')
                    ->groupBy('full_name'); // Ambil satu peserta unik berdasarkan nama
         
                    // Ambil data peserta per tanggal dan gender
        $rawData = DB::table('peserta')
                ->joinSub($subquery, 'unique_peserta', function ($join) {
                    $join->on('peserta.order_id', '=', 'unique_peserta.order_id');
                })
                ->selectRaw("DATE(peserta.created_at) as date, peserta.jenis_kelamin, COUNT(*) as total")
                ->groupBy('date', 'jenis_kelamin')
                ->get();

            // Format untuk ApexCharts
            $genderSeries  = [
                'pria' => [],
                'Wanita' => [],
            ];

            foreach ($dateLabels as $date) {
                foreach (['pria', 'Wanita'] as $gender) {
                    $found = $rawData->firstWhere(fn ($d) => $d->date === $date && $d->jenis_kelamin === $gender);
                    $genderSeries[$gender][] = $found ? $found->total : 0;
                }
            }

            $genderSeriesFormatted = collect($genderSeries)->map(function ($data, $gender) {
                    return [
                        'name' => ucfirst($gender), // Misal: 'Pria', 'Wanita'
                        'data' => $data
                    ];
                })->values();

        // Competition category
            $kat_event = kategori_event::where('active_kategoriEvent',1)->get();
            $availableColors = [
                'bg-green-500',
                'bg-yellow-500',
                'bg-red-500',
                'bg-purple-500',
                'bg-pink-500',
                'bg-orange-500',

            ];

            $colorMap = [];

            foreach ($kat_event as $index => $event) {
                    // Gunakan warna berdasarkan index, looping jika warna habis
                    $color = $availableColors[$index % count($availableColors)];
                    $colorMap[$event->nama_kat_event] = $color;
                }

            $packageCounts = DB::table('peserta')
                        ->join('kategori_event', 'peserta.package', '=', 'kategori_event.kategoriEvent_name')
                        ->select('kategori_event.id','kategori_event.kategoriEvent_name', DB::raw('COUNT(*) as total'))
                        ->whereIn('package', $kat_event->pluck('kategoriEvent_name'))
                        ->where('pembayaran','Paid')
                        ->where('lokasi_run',$kota)
                        ->groupBy('kategori_event.id','kategori_event.kategoriEvent_name')
                        ->get();
            $totalAll = $packageCounts->sum('total');

            $packages = $packageCounts->map(function ($item) use ($totalAll, $colorMap) {
                $color = $colorMap[$item->kategoriEvent_name] ?? 'bg-gray-500'; // fallback jika tidak ada warna
                return [
                    'kode' => $item->id,
                    'label' => $item->kategoriEvent_name,
                    'count' => $item->total,
                    'percent' => $totalAll > 0 ? round(($item->total / $totalAll) * 100, 1) : 0,
                    'color' => $color,
                ];
            });

            // Participant age
            $peserta = DB::table('peserta')
                        ->select(DB::raw("
                            CASE
                                WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) < 11 THEN 'Under 11 years'
                                WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) >= 11 AND TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) < 18 THEN '11 to 17 years'
                                WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) >= 18 AND TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) < 45 THEN '18 to 44 years'
                                ELSE '45+ years'
                            END as age_group,
                            COUNT(*) as total
                        "))
                        ->where('pembayaran', 'Paid')
                        ->where('lokasi_run',$kota)
                        ->groupBy('age_group')
                        ->get();
            $total_peserta = $peserta->sum('total');

            $ageStats = $peserta->map(function ($item) use ($total_peserta) {
                $item->percentage = $total_peserta > 0 ? round(($item->total / $total_peserta) * 100, 1) : 0;
                return $item;
            });            


        return view('report.grafikReport', compact(
            't_peserta',
            't_all',
            't_exp',
            'duplicateCount',
            'persen',
            'categories', 'paid', 'unpaid','presentase_pencapaian',
            'labels', 'data','jumlah_peserta',
            'genderSeriesFormatted', // gunakan ini
            'dateLabels',
            'packages',
            'ageStats',
            'kota', // tambahan untuk backlink
            'totalBaris',
            'totalUnikFullName',
            'totalUnikFullName',
            'extraBaris',
            'duplik',

            
            
        ));
    }

    public function grefikReport1($kota,$id)
        {
                $k = kategori_event::where('id',$id)->value('kategoriEvent_name');
                $c_e = company_event::where('event_id',$kota)->value('event_name');
                $kota = $kota;
                $kota1 = 'SB';
                $peserta = Peserta::where('lokasi_run', $kota)->where('package',$k)->get();

                $t_all = $peserta->unique('full_name')->count();

                $paid = $peserta->where('pembayaran', 'Paid')->unique('full_name');
                $t_peserta = $paid->count();

                $pes = Peserta::where('lokasi_run', $kota)
                            ->where('package',$k)
                            ->where('pembayaran', 'Paid') // filter yang paid
                            ->get();    // Hitung jumlah full_name yang duplikat

                $duplicateCount = $pes->groupBy('full_name')  // Kelompokkan berdasarkan full_name
                                ->filter(function ($items) {
                                    return $items->count() > 1;  // Ambil hanya yang muncul lebih dari sekali
                                })
                                ->count(); // Hitung jumlah grup duplikat


                

                $unpaid = $peserta->where('pembayaran', '!=', 'Paid')
                            ->reject(function ($item) use ($paid) {
                                return $paid->contains('full_name', $item->full_name);
                            })
                            ->unique('full_name');
                $t_exp = $unpaid->count();
            
                $persen = $t_all > 0 ? number_format(($t_peserta / $t_all) * 100, 2) : 0;
                $target_peserta = company_event::where('event_id',$kota)->value('target_peserta');
                $presentase_pencapaian = $target_peserta > 0 ? round(($t_peserta / $target_peserta) * 100, 2) : 0;

                $lokasi = company_event::where('event_id', $kota)->first();

                if (!$lokasi) {
                    abort(404, 'Lokasi tidak ditemukan.');
                }

                $start = Carbon::parse($lokasi->start_event);
                $end = Carbon::parse($lokasi->end_event);

                $categories = [];
                $paid = [];
                $unpaid = [];

                    for ($date = $start->copy(); $date->lte($end); $date->addDay()) {
                        $tanggal = $date->toDateString();
                        $categories[] = $tanggal;

                        $paidNames = Peserta::whereDate('created_at', $tanggal)
                                        ->where('lokasi_run', $kota)
                                        ->where('pembayaran', 'Paid')
                                        ->where('package',$k)
                                        ->pluck('full_name')
                                        ->unique();
                        // Jumlah paid (hanya nama unik)
                        $paid[] = $paidNames->count();                

                        $unpaid[] = Peserta::whereDate('created_at', $tanggal)
                                        ->where('lokasi_run', $kota)
                                        ->where('pembayaran', '!=', 'Paid')
                                        ->where('package',$k)
                                        ->whereNotIn('full_name', $paidNames)
                                        ->distinct('full_name')
                                        ->count('full_name');
                    }

                // Subscription Distribution
                $k_peserta = Kategori_peserta::where('active_idp',1)->get();
                $kategori_map = $k_peserta->pluck('nama_kategori', 'kategori_idp')->toArray();

                // Hitung total tiap kategori
                $kategori_counts_raw = \App\Models\Peserta::select('category', \DB::raw('count(*) as total'))
                            ->where('Pembayaran','Paid')
                            ->where('package',$k)
                            ->where('lokasi_run',$kota)
                            ->groupBy('category')
                            ->pluck('total', 'category');

                // Ubah ke format array untuk Chart JS
                $labels = [];
                $data = [];

            foreach ($kategori_map as $key => $label) {
                    $labels[] = $label;
                    $data[] = $kategori_counts_raw->get($key, 0); // Ambil data, default 0 kalau tidak ada
                }

                // Daily Visit Insights

                // Buat rentang tanggal
                $dateLabels  = collect();
                    for ($date = $start->copy(); $date->lte($end); $date->addDay()) {
                        $dateLabels->push($date->format('Y-m-d'));
                    }

                $subquery = DB::table('peserta')
                            ->selectRaw('MIN(order_id) as order_id') // Gunakan alias yang sesuai
                            ->whereBetween('created_at', [$start, $end])
                            ->where('pembayaran', 'Paid')
                            ->where('package',$k)
                            ->groupBy('full_name'); // Ambil satu peserta unik berdasarkan nama
                
                            // Ambil data peserta per tanggal dan gender
                $rawData = DB::table('peserta')
                        ->joinSub($subquery, 'unique_peserta', function ($join) {
                            $join->on('peserta.order_id', '=', 'unique_peserta.order_id');
                        })
                        ->selectRaw("DATE(peserta.created_at) as date, peserta.jenis_kelamin, COUNT(*) as total")
                        ->where('package',$k)
                        ->groupBy('date', 'jenis_kelamin')
                        ->get();

                    // Format untuk ApexCharts
                    $genderSeries  = [
                        'MALE' => [],
                        'FEMALE' => [],
                    ];

                    foreach ($dateLabels as $date) {
                        foreach (['MALE', 'FEMALE'] as $gender) {
                            $found = $rawData->firstWhere(fn ($d) => $d->date === $date && $d->jenis_kelamin === $gender);
                            $genderSeries[$gender][] = $found ? $found->total : 0;
                        }
                    }

                    $genderSeriesFormatted = collect($genderSeries)->map(function ($data, $gender) {
                            return [
                                'name' => ucfirst($gender), // Misal: 'Pria', 'Wanita'
                                'data' => $data
                            ];
                        })->values();

                // Competition category
                    $kat_event = kategori_event::where('active_kategoriEvent',1)->get();
                    $availableColors = [
                        'bg-green-500',
                        'bg-yellow-500',
                        'bg-red-500',
                        'bg-purple-500',
                        'bg-pink-500',
                        'bg-orange-500',

                    ];

                    $colorMap = [];

                    foreach ($kat_event as $index => $event) {
                            // Gunakan warna berdasarkan index, looping jika warna habis
                            $color = $availableColors[$index % count($availableColors)];
                            $colorMap[$event->nama_kat_event] = $color;
                        }

                    $packageCounts = DB::table('peserta')
                                ->join('kategori_event', 'peserta.package', '=', 'kategori_event.kategoriEvent_name')
                                ->select('kategori_event.id','kategori_event.kategoriEvent_name', DB::raw('COUNT(*) as total'))
                                ->whereIn('package', $kat_event->pluck('id'))
                                ->where('pembayaran','Paid')
                                ->where('package',$k)
                                ->where('lokasi_run',$kota)
                                ->groupBy('kategori_event.id','kategori_event.kategoriEvent_name')
                                ->get();
                    $totalAll = $packageCounts->sum('total');

                    $packages = $packageCounts->map(function ($item) use ($totalAll, $colorMap) {
                        $color = $colorMap[$item->kategoriEvent_name] ?? 'bg-gray-500'; // fallback jika tidak ada warna
                        return [
                            'kode' => $item->id,
                            'label' => $item->kategoriEvent_name,
                            'count' => $item->total,
                            'percent' => $totalAll > 0 ? round(($item->total / $totalAll) * 100, 1) : 0,
                            'color' => $color,
                        ];
                    });

                    // Participant age
                    $ticket = DB::table('ticket_company')
                        ->where('event_id', $kota)
                        ->where('ticket_name', $k)
                        ->where('active_ticket_company', 1)
                        ->first();

                    if (!$ticket) {
                        // Tidak ditemukan tiket yang cocok
                        $peserta = collect(); // kosongkan hasil
                    } else {
                        $peserta = DB::table('peserta')
                                ->selectRaw("
                                    category,
                                    CASE
                                        WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) BETWEEN 1 AND 10 THEN '1 - 10 years'
                                        WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) BETWEEN 11 AND 20 THEN '11 - 20 years'
                                        WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) BETWEEN 21 AND 30 THEN '21 - 30 years'
                                        WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) BETWEEN 31 AND 40 THEN '31 - 40 years'
                                        WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) BETWEEN 41 AND 50 THEN '41 - 50 years'
                                        WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) BETWEEN 51 AND 60 THEN '51 - 60 years'
                                        ELSE '61+ years'
                                    END AS age_group,
                                    COUNT(order_id) as total
                                ")
                                ->where('pembayaran', 'Paid')
                                ->where('lokasi_run', $kota)
                                ->where('package', $k)
                                ->groupBy('category', 'age_group')
                                ->orderBy('category')
                                ->orderByRaw("
                                    MIN(
                                        CASE
                                            WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) BETWEEN 1 AND 10 THEN 1
                                            WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) BETWEEN 11 AND 20 THEN 2
                                            WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) BETWEEN 21 AND 30 THEN 3
                                            WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) BETWEEN 31 AND 40 THEN 4
                                            WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) BETWEEN 41 AND 50 THEN 5
                                            WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) BETWEEN 51 AND 60 THEN 6
                                            ELSE 7
                                        END
                                    )
                                ")
                                ->get();


                            // dd($debugJoin);
                    }

                    $total = $peserta->sum('total');

                    // Urutkan berdasarkan umur
                    $ageOrder = [
                        '1 - 10 years'  => 1,
                        '11 - 20 years' => 2,
                        '21 - 30 years' => 3,
                        '31 - 40 years' => 4,
                        '41 - 50 years' => 5,
                        '51 - 60 years' => 6,
                        '61+ years'     => 7,
                    ];


                    $ageStats = $peserta->map(function ($item) use ($total, $ageOrder) {
                                $item->percentage = $total > 0 ? round(($item->total / $total) * 100, 1) : 0;
                                $item->age_order = $ageOrder[$item->age_group] ?? 99;
                                return $item;
                            })->sortBy('age_order')->values();  

                            
                    // peserta percategory participant
                    
                    $tcc = ticket_company::where('event_id',$kota)->where('category_event_id',$id)->value('category_participant_id');
                  
                    $tcc1 = json_decode($tcc, true);
            
                    
                    $query = DB::table('participant')
                            ->where('participant_active', 1);
                        if ($tcc) {
                            $query->whereIn('id', $tcc1);
                        }

                    $categories = $query->pluck('participant_name', 'id')->toArray(); // hasil: [1 => 'Umum', 2 => 'Pelajar', ...]
                
                    $allStats = [];

                    foreach ($categories as $catId => $catLabel) {
                        $peserta = DB::table('peserta')
                            ->select(DB::raw("
                                CASE
                                    WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) BETWEEN 1 AND 10 THEN '1 - 10 years'
                                        WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) BETWEEN 11 AND 20 THEN '11 - 20 years'
                                        WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) BETWEEN 21 AND 30 THEN '21 - 30 years'
                                        WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) BETWEEN 31 AND 40 THEN '31 - 40 years'
                                        WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) BETWEEN 41 AND 50 THEN '41 - 50 years'
                                        WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) BETWEEN 51 AND 60 THEN '51 - 60 years'
                                        ELSE '61+ years'
                                END as age_group,
                                COUNT(*) as total,
                                SUM(CASE WHEN jenis_kelamin = 'MALE' THEN 1 ELSE 0 END) as total_pria,
                                SUM(CASE WHEN jenis_kelamin = 'FEMALE' THEN 1 ELSE 0 END) as total_wanita
                            "))
                            ->where('pembayaran', 'Paid')
                            ->where('category', $catId)
                            ->where('package', $k)
                            ->where('lokasi_run', $kota)
                            ->groupBy('age_group')
                            ->get();

                        $total = $peserta->sum('total');

                        $stats = $peserta->map(function ($item) use ($total) {
                            $item->percentage = $total > 0 ? round(($item->total / $total) * 100, 1) : 0;
                            return $item;
                        });

                        $allStats[] = [
                            'category_label' => $catLabel,
                            'stats' => $stats,
                        ];

                       
                    }










                    
                    // umum
                    $pesertaumum = DB::table('peserta')
                                ->select(DB::raw("
                                    CASE
                                        WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) < 11 THEN 'Under 11 years'
                                        WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) >= 11 AND TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) < 18 THEN '11 to 17 years'
                                        WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) >= 18 AND TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) < 45 THEN '18 to 44 years'
                                        ELSE '45+ years'
                                    END as age_group,
                                    COUNT(*) as total,
                                    SUM(CASE WHEN jenis_kelamin = 'MALE' THEN 1 ELSE 0 END) as total_pria,
                                    SUM(CASE WHEN jenis_kelamin = 'FEMALE' THEN 1 ELSE 0 END) as total_wanita
                                "))
                                ->where('pembayaran', 'Paid')
                                ->where('category',1)
                                ->where('package',$id)
                                ->where('lokasi_run',$kota)
                                ->groupBy('age_group')
                                ->get();
                    $total_peserta = $pesertaumum->sum('total');

                    $ageStatsumum = $pesertaumum->map(function ($item) use ($total_peserta) {
                        $item->percentage = $total_peserta > 0 ? round(($item->total / $total_peserta) * 100, 1) : 0;
                        return $item;
                    });

                    // master
                    // $pesertamaster = DB::table('peserta')
                    //     ->select(DB::raw("
                    //         CASE
                    //             WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) < 11 THEN 'Under 11 years'
                    //             WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) >= 11 AND TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) < 18 THEN '11 to 17 years'
                    //             WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) >= 18 AND TIMESTAMPDIFF(YEAR, tgl_lahir, created_at) < 45 THEN '18 to 44 years'
                    //             ELSE '45+ years'
                    //         END as age_group,
                    //         COUNT(*) as total,
                    //         SUM(CASE WHEN jenis_kelamin = 'MALE' THEN 1 ELSE 0 END) as total_pria,
                    //         SUM(CASE WHEN jenis_kelamin = 'FEMALE' THEN 1 ELSE 0 END) as total_wanita
                    //     "))
                    //     ->where('pembayaran', 'Paid')
                    //     ->where('category', 5)
                    //     ->where('package', $id)
                    //     ->where('lokasi_run', $kota)
                    //     ->groupBy('age_group')
                    //     ->get();

                    // $total_peserta = $pesertamaster->sum('total');

                    // $ageStatsmaster = $pesertamaster->map(function ($item) use ($total_peserta) {
                    //     $item->percentage = $total_peserta > 0 ? round(($item->total / $total_peserta) * 100, 1) : 0;
                    //     return $item;
                    // });

                $pesertassss = DB::table('peserta')
                        ->select(
                            'order_id','alamat','full_name', 'email', 'tgl_lahir','phone','tshirt_size','category', 'peserta.created_at', 'package', 'lokasi_run','participant.participant_name', // sesuaikan kolom yang ingin ditampilkan
                            DB::raw("
                                CASE
                                    WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, peserta.created_at) < 11 THEN 'Under 11 years'
                                    WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, peserta.created_at) >= 11 AND TIMESTAMPDIFF(YEAR, tgl_lahir, peserta.created_at) < 18 THEN '11 to 17 years'
                                    WHEN TIMESTAMPDIFF(YEAR, tgl_lahir, peserta.created_at) >= 18 AND TIMESTAMPDIFF(YEAR, tgl_lahir, peserta.created_at) < 45 THEN '18 to 44 years'
                                    ELSE '45+ years'
                                END as age_group
                            ")
                        )
                        
                        ->join('participant','peserta.category','=','participant.id')
                        ->where('pembayaran', 'Paid')
                        ->where('package', $k)
                        ->where('lokasi_run', $kota)
                        ->get();

                    $test = $pesertassss;

                return view('report.grafikReport1', compact(
                    'test',
                    'peserta',
                    'k',
                    'c_e',
                    't_peserta',
                    't_all',
                    't_exp',
                    'duplicateCount',
                    'persen',
                    'categories', 'paid', 'unpaid','presentase_pencapaian',
                    'labels', 'data',
                    'genderSeriesFormatted', // gunakan ini
                    'dateLabels',
                    'packages',
                    'ageStats',
                    'ageStatsumum',
                    // 'ageStatsmaster',
                    // 'ageStatspelajar',
                    'allStats',
                    'kota'
                    
                ));
        }

   
    public function participantlistEvent($id)
    {

        $list = DB::table('participant_company_event')
            ->join('ticket_company', 'participant_company_event.ticket_id', '=', 'ticket_company.ticket_id') // join by ticket_id
            ->join('category_ticket','ticket_company.category_ticket_id','=','category_ticket.id')
            ->where('participant_company_event.event_id', $id)
            ->select(
                'participant_company_event.id',
                'participant_company_event.ticket_id',
                'participant_company_event.participant_id',
                'participant_company_event.participant_name',
                'participant_company_event.quota_participant_event',
                'participant_company_event.bib_number_male',
                'participant_company_event.bib_number_female',
                'participant_company_event.age_of',
                'participant_company_event.age_up_to',
                'ticket_company.ticket_name',
                'ticket_company.event_id',
                'category_ticket.category_ticket_name'
            )
            ->get();



        $party = participant::where('participant_active',1)->get(); 
        $comp  = company_event::where('event_id',$id)->value('event_name');       
        $event_id = $id;
        $kE = ticket_company::join('category_ticket','ticket_company.category_ticket_id','=','category_ticket.id')->where('event_id',$id)->get();

        return view('admin.participantCompanyevent', compact('list','party','comp','event_id','kE'));
    }    

     public function saveRecordParticipantCompany(Request $request)
    {
        // $request->validate([

        //     'participant_name' => 'required', 
        //     'bib_number'       => 'required',      
        // ]);
        
        $pname = participant::where('id',$request->participant_id)->value('participant_name');

        try {
                $parti = new participant_company_event();
                $parti->fill([
                    'event_id'                      => $request->event_id,
                    'ticket_id'                     => $request->ticket_id,
                    'participant_id'                => $request->participant_id,
                    'participant_name'              => Str::title($pname),
                    'quota_participant_event'       => $request->quota_participant_event,
                    'bib_number_male'               => $request->bib_number_male, 
                    'bib_number_female'             => $request->bib_number_female, 
                    'age_of'                        => $request->age_of, 
                    'age_up_to'                     => $request->age_up_to, 
                ]);
                $parti->save();

                $partiCev = ticket_company::where('ticket_id', $request->ticket_id)->get();

                foreach ($partiCev as $item) {
                    // Ambil data lama, kalau null atau kosong jadi array kosong
                    $existing = $item->category_participant_id 
                        ? json_decode($item->category_participant_id, true) 
                        : [];

                    // Pastikan existing array
                    if (!is_array($existing)) {
                        $existing = [];
                    }

                    // Tambahkan participant_id baru kalau belum ada
                    if (!in_array($request->participant_id, $existing)) {
                        $existing[] = $request->participant_id;
                    }

                    // Simpan kembali (encode ke JSON)
                    $item->update([
                        'category_participant_id' => json_encode($existing)
                    ]);
                }



            Toastr::success('Participant Company Berhasil dibuat', 'Success');
            return redirect()->back();
        } catch (\Exception $e) {
            \Log::error($e);
            Toastr::error('Participant Company dibuat', 'Error');
            return redirect()->back();
        }
        
    }

    public function participantCompanyUpdateRecord(Request $request)
    {
    //    dd($request->ticket_id);
          try {

            $parti = participant_company_event::findOrFail($request->id);
            $pname = participant::where('id',$request->participant_id)->value('participant_name');

            $parti->update([
               
                $parti->participant_id              = $request->participant_id,
                $parti->participant_name            = Str::title($pname),
                $parti->quota_participant_event     = $request->quota_participant_event,
                $parti->bib_number_male             = $request->bib_number_male,
                $parti->bib_number_female           = $request->bib_number_female,
                $parti->age_of                      = $request->age_of,
                $parti->age_up_to                   = $request->age_up_to,
            ]);

            $ptr = participant_company_event::where('ticket_id',$request->ticket_id)
                                                ->pluck('participant_id')
                                                ->toArray();
            
            $partiCev = ticket_company::where('ticket_id', $request->ticket_id)->get();

                foreach ($partiCev as $item) {
                    // Ambil data lama, kalau null atau kosong jadi array kosong
                    $existing = $item->category_participant_id 
                        ? json_decode($item->category_participant_id, true) 
                        : [];

                    // Pastikan existing array
                    if (!is_array($existing)) {
                        $existing = [];
                    }

                    // menghapus nilai yang sama
                    $merged = array_unique(($ptr));

                    // Simpan kembali (encode ke JSON)
                    $item->update([
                        'category_participant_id' => json_encode($merged)
                    ]);
                }


            Toastr::success('updated sukses','Success');
            return redirect()->back();
        } catch(\Exception $e) {
            \Log::info($e);
            Toastr::error('gagal, update record','Error');
            return redirect()->back();
        }
    }

    public function participantCompanyDeleteRecord(Request $request)
    {
         try {
            
            $syarat = participant_company_event::findOrFail($request->id_delete);
            $syarat->delete();

            $ptr = participant_company_event::where('ticket_id',$request->ticket_id)
                                                ->pluck('participant_id')
                                                ->toArray();
            
            $partiCev = ticket_company::where('ticket_id', $request->ticket_id)->get();

                foreach ($partiCev as $item) {
                    // Ambil data lama, kalau null atau kosong jadi array kosong
                    $existing = $item->category_participant_id 
                        ? json_decode($item->category_participant_id, true) 
                        : [];

                    // Pastikan existing array
                    if (!is_array($existing)) {
                        $existing = [];
                    }

                    // menghapus nilai yang sama
                    $merged = array_unique(($ptr));

                    // Simpan kembali (encode ke JSON)
                    $item->update([
                        'category_participant_id' => json_encode($merged)
                    ]);
                }

            
            Toastr::success('Perticipant Berhasil Dihapus', 'Success');
            return redirect()->back();
        } catch (\Exception $e) {
            \Log::error($e); // Log the error
            Toastr::error('Perticipant Gagal Dihapus', 'Error');
            return redirect()->back();
        }
    }




    public function emailSB($id)
    {
       
        $lok = company_event::where('event_id',$id)->get();
    
        $peserta = DB::table('peserta')
                    ->join('kabupatens','peserta.kabupaten','=','kabupatens.id')
                    ->join('country_data2','peserta.negara','=','country_data2.id_country')
                    ->select('peserta.*', 'kabupatens.*', 'country_data2.*', 'peserta.updated_at as updated') 
                    ->where('lokasi_run',$id)
                    ->where('pembayaran','Paid')
                    ->where('konfirmasi_email',0)
                    ->orderBy('full_name','ASC')
                    ->get();

        return view('email.emailBW',compact('peserta','lok'));
    }


    public function pesertaMadiun(){
        $lok = 'Madiun';
        $kd_lok = ['MN'];
        $peserta = DB::table('peserta')
            ->join('kabupatens','peserta.kabupaten','=','kabupatens.id')
            ->join('country_data2','peserta.negara','=','country_data2.id_country')
            ->join('kat_event','peserta.package','=','kat_event.id_kat_event')
            ->select('peserta.*', 'kabupatens.*', 'country_data2.*', 'kat_event.*', 'peserta.updated_at as updated') 
            ->whereIn('lokasi_run',['MN','AL','AL2'])
            ->orderBy('full_name','ASC')
            ->get();
        $tp = DB::table('peserta')->whereIn('lokasi_run',['MN','AL','AL2'])->count();
        $tb = DB::table('peserta')->whereIn('lokasi_run',['MN','AL','AL2'])->where('pembayaran','Paid')->count();
        $ex = DB::table('peserta')->whereIn('lokasi_run',['MN','AL','AL2'])->where('pembayaran','Pending')->count();
        return view('peserta.peserta', compact('peserta','tp','tb','ex','lok','kd_lok'));
    }

    public function pesertaKediri()
    {
        $lok = 'Kediri';
        $kd_lok = ['KD','AL','AL2'];
        $peserta = DB::table('peserta')
            ->join('kabupatens','peserta.kabupaten','=','kabupatens.id')
            ->join('country_data2','peserta.negara','=','country_data2.id_country')
            ->join('kat_event','peserta.package','=','kat_event.id_kat_event')
            ->select('peserta.*', 'kabupatens.*', 'country_data2.*', 'kat_event.*', 'peserta.updated_at as updated') 
            ->whereIn('lokasi_run',['KD','AL','AL2'])
            ->orderBy('full_name','ASC')
            ->get();
        $tp = DB::table('peserta')->whereIn('lokasi_run',['KD','AL','AL2'])->count();
        $tb = DB::table('peserta')->whereIn('lokasi_run',['KD','AL','AL2'])->where('pembayaran','Paid')->count();
        $ex = DB::table('peserta')->whereIn('lokasi_run',['KD','AL','AL2'])->where('pembayaran','Pending')->count();
        return view('peserta.peserta', compact('peserta','tp','tb','ex','lok','kd_lok'));
    }

    public function pesertaSurabaya()
    {
        $lok = 'Surabaya';
        $kd_lok = ['SB','AL'];
        $peserta = DB::table('peserta')
                    ->join('kabupatens','peserta.kabupaten','=','kabupatens.id')
                    ->join('country_data2','peserta.negara','=','country_data2.id_country')
                    ->join('kat_event','peserta.package','=','kat_event.id_kat_event')
                    ->select('peserta.*', 'kabupatens.*', 'country_data2.*', 'kat_event.*', 'peserta.updated_at as updated') 
                    ->whereIn('lokasi_run',['SB','AL'])
                    ->orderBy('full_name','ASC')
                    ->get();
        $tp = DB::table('peserta')->whereIn('lokasi_run',['SB','AL'])->count();
        $tb = DB::table('peserta')->whereIn('lokasi_run',['SB','AL'])->where('pembayaran','Paid')->count();
        $ex = DB::table('peserta')->whereIn('lokasi_run',['SB','AL'])->where('pembayaran','Pending')->count();
        return view('peserta.peserta', compact('peserta','tp','tb','ex','lok','kd_lok'));
    }

    public function pesertaExport($id)
    {
        $nm = company_event::where('event_id',$id)->value('event_name');
        return Excel::download(new PesertaExport($id), 'peserta_'.$nm.'.xlsx');
    }

    public function kirimEmail($id)
    {
        $peser  = Peserta::findOrFail($id);
        $peser->pembayaran = 'Paid'; 
        $peser->konfirmasi_email = 1 ;
        $peser->update();

        $lokasi = $peser ->lokasi_run;

        $path = public_path('assets/qrcode/'.$lokasi.'/'.$id.'.png');
        $folderPath = public_path('assets/qrcode/'.$lokasi);
        // Buat folder jika belum ada
        if (!File::exists($folderPath)) {
            File::makeDirectory($folderPath, 0755, true);
        }

        $image = QrCode::format('png')
       
            ->size(500)
            ->errorCorrection('H')
            ->generate($id,$path);

        $kirim = (new RegistrasiController)->kirimEmail($id);
        return redirect()->back();
    }

    public function raceBw()
    {
        $lokasi = 'banyuwangi';
        $kode_lok ='BW';
        return view('peserta.raceBW',compact('lokasi', 'kode_lok'));
    }

    public function raceBw1()
    {
        $lok = 'Banyuwangi';
        $kd_lok = ['BW'];
        $peserta = DB::table('peserta')
                    ->join('kabupatens','peserta.kabupaten','=','kabupatens.id')
                    ->join('country_data2','peserta.negara','=','country_data2.id_country')
                    ->join('kat_event','peserta.package','=','kat_event.id_kat_event')
                    ->select('peserta.*', 'kabupatens.*', 'country_data2.*', 'kat_event.*', 'peserta.updated_at as updated') 
                    ->whereIn('lokasi_run',['BW','AL','AL2'])
                    ->where('pembayaran','Paid')
                    ->orderBy('full_name','ASC')
                    ->get();

        return view('peserta.raceBW1',compact('lok','kd_lok','peserta'));
    }

    public function search(Request $request, $id)
    {
        $keyword = $request->input('search');
        $pesertas = DB::table('peserta')
                            
                            ->where('lokasi_run',$id)
                            ->where('peserta.pembayaran','paid')
                            ->where('order_id', 'like', '%' . $keyword . '%')
                            ->orWhere('full_name', 'like', '%' . $keyword . '%')
                            ->where('peserta.pembayaran','paid')
                            ->get();
        
        return response()->json([
            'pesertas' => $pesertas
        ]);
    }

    public function konfirmasiKehadiran($id)
    {
        $peserta = Peserta::find($id);
        if ($peserta) {
            $peserta->kehadiran = true;
            $peserta->save();
            return response()->json(['message' => 'Kehadiran peserta terkonfirmasi!']);
        }

        return response()->json(['message' => 'Peserta tidak ditemukan!'], 404);
    }

    public function addpeserta()
    {
        return view('peserta.addpeserta');
    }
    
    public function gudangBw()
    {
        $lok = 'Banyuwangi';
        $kd_lok = ['BW'];
        $peserta = peserta::whereIn('lokasi_run',['BW','AL','AL2'])->where('pembayaran','Paid')->where('konfirmasi_kehadiran','=','1')->get();
        return view('peserta.gudangBw',compact('peserta','lok','kd_lok'));
    }

    public function importGet()
    {
        return view('peserta.importGet');
    }

    public function import(Request $request) 
    {
        // Validate incoming request data
        $request->validate([
            'file' => 'required|max:2048',
        ]);
  
        Excel::import(new PesertaImport, $request->file('file'));
                 
        return back()->with('success', 'Users imported successfully.');
    }

    public function importBib(Request $request)
    {
        // Validasi file yang diupload
        $request->validate([
            'file' => 'required|mimes:xlsx,xls,csv',
        ]);

        $file = $request->file('file');
        Excel::import(new PesertaBibImport, $file);

        return back()->with('success', 'Data updated successfully!');
    } 

    public function importHasilrace(Request $request)
    {
        // Validasi file yang diupload
        $request->validate([
            'file' => 'required|mimes:xlsx,xls,csv',
        ]);

        $file = $request->file('file');
        Excel::import(new PesertaHasilimport, $file);

        return back()->with('success', 'Data updated successfully!');
    }

    public function emailBW()
    {
        // $data = Peserta::where('pembayaran','Paid')->whereIn('lokasi_run',['BW','AL','AL2'])->orderBy('full_name','ASC')->get();
        $lok = 'Banyuwangi';
        $kd_lok = ['BW'];
        $peserta = DB::table('peserta')
                    ->join('kabupatens','peserta.kabupaten','=','kabupatens.id')
                    ->join('country_data2','peserta.negara','=','country_data2.id_country')
                    ->join('kat_event','peserta.package','=','kat_event.id_kat_event')
                    ->select('peserta.*', 'kabupatens.*', 'country_data2.*', 'kat_event.*', 'peserta.updated_at as updated') 
                    ->whereIn('lokasi_run',['BW','AL','AL2'])
                    ->where('pembayaran','Paid')
                    ->where('konfirmasi_email',0)
                    ->wherenotlike('alamat','SMADATARA')
                    ->orderBy('full_name','ASC')
                    ->get();

        return view('email.emailBW',compact('peserta','lok','kd_lok'));
    }

    public function kirimEmailkonfirmasi()
    {
        $peser  = Peserta::findOrFail($id);
        $peser->pembayaran = 'Paid'; 
        $peser->konfirmasi_email = 1 ;
        $peser->update();

        $path = public_path('assets/qrcode/'.$id.'.png');
        if (!file_exists(dirname($path))) {
            mkdir(dirname($path), 0755, true);
        }
       // Simpan QR code ke file PNG
            file_put_contents($path, QrCode::format('png')
                
                ->size(500)
                ->errorCorrection('H')
                ->generate($id)
            );
      

      
       
        $kirim = (new RegistrasiController)->kirimEmail($id);
        return redirect()->back();
    }

    public function raceBwKonfirmasi($id)
    {
       
        // $peserta = Peserta::find($id);
        $peserta = DB::table('peserta')
                ->join('kabupatens','peserta.kabupaten','=','kabupatens.id')
                ->join('company_event','peserta.lokasi_run','=','company_event.event_id')
                ->join('participant','peserta.category','=','participant.id')
                ->select('peserta.*', 'kabupatens.*','company_event.*','participant.*', 'peserta.updated_at as updated') 
                ->where('order_id',$id)
                ->get();

        return view('peserta.konfirmasiBW',compact('peserta'));
    }

    public function konfirmasiKehadiran2($id)
    {
      
        $peserta = Peserta::find($id);
        if ($peserta) {
            $peserta->konfirmasi_kehadiran = 1;
            $peserta->loket = Session::get('loket');
            $peserta->save();
            return redirect()->back();
        }

        return response()->json(['message' => 'Peserta tidak ditemukan!'], 404);
    }
    

    public function konfirmasiDiterima($id)
    {
       
        $peserta = Peserta::find($id);
        $lokasi = $peserta->lokasi_run;
     
        if ($peserta) {
            $peserta->konfirmasi_kehadiran = 2;
            $peserta->save();
            return redirect()->route('peserta/racepacklist', ['id' => $lokasi]);
        }

        return response()->json(['message' => 'Peserta tidak ditemukan!'], 404);
    }

    // Kediri
    public function raceKd()
    {
        $lokasi = 'kediri';
        $kode_lok ='KD';
        return view('peserta.raceKD',compact('lokasi','kode_lok'));
    }

    public function searchKD(Request $request)
    {
        $keyword = $request->input('search');
        $pesertas = DB::table('peserta')
                            ->join('kategori_peserta','peserta.category','=','kategori_peserta.kategori_idp')
                            ->join('kabupatens','peserta.kabupaten','=','kabupatens.id')
                            ->join('country_data2','peserta.negara','=','country_data2.id_country')
                            ->join('kat_event','peserta.package','=','kat_event.id_kat_event')
                            ->whereIn('lokasi_run',['KD','AL','AL2'])
                            ->where('peserta.pembayaran','paid')
                            ->where('order_id', 'like', '%' . $keyword . '%')
                            ->orWhere('full_name', 'like', '%' . $keyword . '%')
                            ->where('peserta.pembayaran','paid')
                            ->get();
        
        return response()->json([
            'pesertas' => $pesertas
        ]);
    }

    public function gudangKd()
    {
        $lok = 'Kediri';
        $kd_lok = ['KD'];
        $peserta = peserta::whereIn('lokasi_run',['KD','AL','AL2'])->where('pembayaran','Paid')->where('konfirmasi_kehadiran','=','1')->get();
        return view('peserta.gudangBw',compact('peserta','lok','kd_lok'));
    }

    public function emailKD()
    {
        // $data = Peserta::where('pembayaran','Paid')->whereIn('lokasi_run',['BW','AL','AL2'])->orderBy('full_name','ASC')->get();
        $lok = 'kediri';
        $kd_lok = ['KD'];
        $peserta = DB::table('peserta')
                    ->join('kabupatens','peserta.kabupaten','=','kabupatens.id')
                    ->join('country_data2','peserta.negara','=','country_data2.id_country')
                    ->join('kat_event','peserta.package','=','kat_event.id_kat_event')
                    ->select('peserta.*', 'kabupatens.*', 'country_data2.*', 'kat_event.*', 'peserta.updated_at as updated') 
                    ->whereIn('lokasi_run',['KD','AL','AL2'])
                    ->where('pembayaran','Paid')
                    ->wherenotlike('alamat','SMADATARA')
                    ->where('konfirmasi_email',0)
                    ->orderBy('full_name','ASC')
                    ->get();

        return view('email.emailBW',compact('peserta','lok','kd_lok'));
    }

    // end kediri

    // Madiun
    public function raceMn()
    {
        $lokasi = 'madiun';
        $kode_lok ='MN';
        return view('peserta.raceKD',compact('lokasi','kode_lok'));
    }

    public function searchMN(Request $request)
    {
        $keyword = $request->input('search');
        $pesertas = DB::table('peserta')
                            ->join('kategori_peserta','peserta.category','=','kategori_peserta.kategori_idp')
                            ->join('kabupatens','peserta.kabupaten','=','kabupatens.id')
                            ->join('country_data2','peserta.negara','=','country_data2.id_country')
                            ->join('kat_event','peserta.package','=','kat_event.id_kat_event')
                            ->whereIn('lokasi_run',['MN','AL','AL2'])
                            ->where('peserta.pembayaran','paid')
                            ->where('order_id', 'like', '%' . $keyword . '%')
                            ->orWhere('full_name', 'like', '%' . $keyword . '%')
                            ->where('peserta.pembayaran','paid')
                            ->get();
        
        return response()->json([
            'pesertas' => $pesertas
        ]);
    }

    public function gudangMn()
    {
        $lok = 'Madiun';
        $kd_lok = ['MN'];
        $peserta = peserta::whereIn('lokasi_run',['MN','AL','AL2'])->where('pembayaran','Paid')->where('konfirmasi_kehadiran','=','1')->get();
        return view('peserta.gudangBw',compact('peserta','lok','kd_lok'));
    }

    public function emailMN()
    {
        // $data = Peserta::where('pembayaran','Paid')->whereIn('lokasi_run',['BW','AL','AL2'])->orderBy('full_name','ASC')->get();
        $lok = 'madiun';
        $kd_lok = ['KD'];
        $peserta = DB::table('peserta')
                    ->join('kabupatens','peserta.kabupaten','=','kabupatens.id')
                    ->join('country_data2','peserta.negara','=','country_data2.id_country')
                    ->join('kat_event','peserta.package','=','kat_event.id_kat_event')
                    ->select('peserta.*', 'kabupatens.*', 'country_data2.*', 'kat_event.*', 'peserta.updated_at as updated') 
                    ->whereIn('lokasi_run',['MN','AL','AL2'])
                    ->where('pembayaran','Paid')
                    ->where('konfirmasi_email',0)
                    ->orderBy('full_name','ASC')
                    ->get();

        return view('email.emailBW',compact('peserta','lok','kd_lok'));
    }
    // end madiun

    // Surabaya

    public function raceSb()
    {
        $lokasi = 'surabaya';
        $kode_lok ='SB';
        return view('peserta.raceKD',compact('lokasi','kode_lok'));
    }

    public function searchSB(Request $request)
    {
        $keyword = $request->input('search');
        $pesertas = DB::table('peserta')
                            ->join('kategori_peserta','peserta.category','=','kategori_peserta.kategori_idp')
                            ->join('kabupatens','peserta.kabupaten','=','kabupatens.id')
                            ->join('country_data2','peserta.negara','=','country_data2.id_country')
                            ->join('kat_event','peserta.package','=','kat_event.id_kat_event')
                            ->whereIn('lokasi_run',['SB','AL'])
                            ->where('peserta.pembayaran','paid')
                            ->where('order_id', 'like', '%' . $keyword . '%')
                            ->orWhere('full_name', 'like', '%' . $keyword . '%')
                            ->where('peserta.pembayaran','paid')
                            ->get();
        
        return response()->json([
            'pesertas' => $pesertas
        ]);
    }

  
    // end Surabaya

   
    
   
    
     // Generate Nomor BIB
    public function generateAllNomorUrut()
    {
        $kategoriPrefix = [
            'funwalk' => 'A-',
            '5k_open_man' => 'B-',
            '5k_open_woman' => 'C-',
            'pelajar_5k_man' => 'D-',
            'pelajar_5k_woman' => 'E-',
            '5k_master_man' => 'F-',
            '5k_master_woman' => 'G-'
        ];

        $generatedNomorUrut = [];

        foreach ($kategoriPrefix as $kategori => $prefix) {
            // Ambil nomor urut terakhir untuk kategori ini
            $pes = Peserta::whereIn('lokasi_run',['KD','AL','AL2'])->where('pembayaran','Paid')->get();
            $lastNomorUrut = $pes::where('kategori', $kategori)->latest()->first();

            $nomorUrut = $lastNomorUrut ? (int) substr($lastNomorUrut->nomor_urut, 2) + 1 : 1;
            $nomorUrutFormatted = sprintf('%04d', $nomorUrut);

            // Simpan nomor urut baru
            $nomorUrutRecord = NomorUrut::create([
                'kategori' => $kategori,
                'nomor_urut' => $prefix . $nomorUrutFormatted
            ]);

            $generatedNomorUrut[] = [
                'kategori' => $kategori,
                'nomor_urut' => $nomorUrutRecord->nomor_urut
            ];
        }

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

// opsi 2 generate bib number
public function generateBibNumbers($id)
    {
        $rules = [
            'KBch0|pria|1' => '51',
            'KBch0|wanita|1' => '52',
            'KBch0|pria|2' => '53',
            'KBch0|wanita|2' => '54',
            'KBch0|pria|5' => '55',
            'KBch0|wanita|5' => '56',

            'q72VN|pria|1' => '11',
            'q72VN|wanita|1' => '12',
            'q72VN|pria|2' => '13',
            'q72VN|wanita|2' => '14',
            'q72VN|pria|5' => '15',
            'q72VN|wanita|5' => '16',

            'ELyx4|all|1' => '8',

        ];

        foreach ($rules as $key => $prefix) {
            [$category, $gender, $run,$prices] = array_pad(explode('|', $key), 4, null);

            $query = Peserta::where('package', $category)->where('category', $run)->where('pembayaran','paid')->where('lokasi_run',$id);

            // Jika gender tidak "all", tambahkan filter berdasarkan gender
            if ($gender !== 'all') {
                $query->where('jenis_kelamin', $gender);
            }
            if ($prices) {
                $priceArray = explode(',', $prices);
                $query->whereIn('price', $priceArray);
            }

            $peserta = $query->orderBy('order_id')->get();

            $counter = 1;
            foreach ($peserta as $p) {
                $p->update(['bib_number' => sprintf('%s%03d', $prefix, $counter++)]);
            }
        }

        // $pesertaSNS3O = Peserta::where('package', 'SNS3O')
        // ->where('jenis_kelamin', 'pria')
        // ->where('category', '1')
        // ->where('pembayaran','Paid')
        // ->whereIn('price', [780000, 1000000])
        // ->orderBy('order_id')
        // ->get();

        // $counter = 1;
        // foreach ($pesertaSNS3O as $p) {
        //     $p->update(['bib_number' => sprintf('C-%04d', $counter++)]);
        // }

        return redirect()->back()->with('success', 'Nomor urut berhasil digenerate!');
    }    
    // end Generate Nomor BIB

    public function saveRecordPeserta(Request $request)
    {
        $request->validate([
           
            'order_id'    => 'required|string',
            'full_name' => 'required|string',
          
        ]);

        try {
            $pm = Peserta::updateOrCreate(
                ['order_id' => $request->order_id],
                [
                    'full_name' => $request->full_name,
                    'first_name'  => $request->first_name,
                    'last_name' => $request->last_name,
                    'email'  => $request->email,
                    'phone' => $request->phone,
                    'bib_name'  => $request->bib_name,
                    'dokumen_id' => $request->dokumen_id,
                    'jenis_kelamin'  => $request->jenis_kelamin,
                    'tshirt_size' => $request->tshirt_size,
                    'pembayaran'  => $request->pembayaran,
                   
                  
                    
                ]
            );
           
            Toastr::success('Peserta Berhasil diedit', 'Success');
            return redirect()->back();
        } catch (\Exception $e) {
            \Log::error($e);
            Toastr::error('Peserta Gagal diedit', 'Error');
            return $e;
        }
    
    }



    

}
