<?php

namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

use Illuminate\Http\Request;

class AdmitController extends Controller
{
    //

    public function admitStudent(Request $request)
    {
        $validated = $request->validate([
            'student_id' => 'required|exists:enrollments,id',
            'course_id' => 'required|exists:tbl_courses,id',
            'admission_date' => 'required|date',
        ]);

          // Check if the student is already admitted
            $exists = DB::table('tbl_admissions')
                ->where('student_id', $validated['student_id'])
                ->exists();

            if ($exists) {
                return redirect()->back()->with('error', 'This student is already admitted.');
            }

        // Store the admission data
        DB::table('tbl_admissions')->insert([
            'student_id' => $validated['student_id'],
            'course_id' => $validated['course_id'],
            'admission_date' => $validated['admission_date'],
            'created_at' => now(),
            'updated_at' => now(),
        ]);

        return redirect()->back()->with('success', 'Student admitted successfully!');
    }

    public function showAdmissions()
    {
        $admissions = DB::table('tbl_admissions')
        ->join('enrollments', 'tbl_admissions.student_id', '=', 'enrollments.id')
        ->join('tbl_courses', 'tbl_admissions.course_id', '=', 'tbl_courses.id')
        ->select(
            'tbl_admissions.*',
            'enrollments.name as student_name',
            'enrollments.idno',
            'enrollments.station',
            'enrollments.phone',
            'enrollments.account',
            'enrollments.email',
            'enrollments.paystatus',
            'enrollments.amount',
            'tbl_courses.course_name'
        )
        ->get();

        $courses = DB::table('tbl_courses')->get();
        $stations = DB::table('tbl_training_stations')->get();
        $trainers = DB::table('tbl_trainers')->get();
        $certifiedIds = DB::table('certified_members')->pluck('admid')->toArray();

        //return $admissions;
    
        // Prepare data for the view
        $data = [
            'admissions' => $admissions,
            'courses' => $courses,
            'stations' => $stations,
            'trainers' => $trainers,
            'certifiedIds' => $certifiedIds,
            'duration' => "all Admissions",
        ];
    
        // Return view with data
        return view('training.admissionstable')->with($data);
    }


    public function showweeklyAdmissions()
    {

        // Define start and end of current week (Monday to Sunday)
        $startOfWeek = Carbon::now()->setTimezone('Africa/Nairobi')->startOfWeek()->toDateTimeString();
        $endOfWeek = Carbon::now()->setTimezone('Africa/Nairobi')->endOfWeek()->toDateTimeString();

        // Filter admissions done within this week
        $admissions = DB::table('tbl_admissions')
        ->join('enrollments', 'tbl_admissions.student_id', '=', 'enrollments.id')
        ->join('tbl_courses', 'tbl_admissions.course_id', '=', 'tbl_courses.id')
        ->whereBetween('tbl_admissions.admission_date', [$startOfWeek, $endOfWeek])
        ->select(
            'tbl_admissions.*',
            'enrollments.name as student_name',
            'enrollments.idno',
            'enrollments.station',
            'enrollments.phone',
            'enrollments.account',
            'enrollments.email',
            'enrollments.paystatus',
            'enrollments.amount',
            'tbl_courses.course_name'
        )
        ->orderBy('tbl_admissions.admission_date', 'desc')
        ->get();

        $courses = DB::table('tbl_courses')->get();
        $stations = DB::table('tbl_training_stations')->get();
        $trainers = DB::table('tbl_trainers')->get();
       $certifiedIds = DB::table('certified_members')->pluck('admid')->toArray();
       
        // Prepare data for the view
        $data = [
            'admissions' => $admissions,
            'courses' => $courses,
            'stations' => $stations,
            'trainers' => $trainers,
            'certifiedIds' => $certifiedIds,
            'duration' => "Weekly Admissions",
        ];
    
        // Return view with data
        return view('training.admissionstable')->with($data);
    }

    
    public function showMonthlyAdmissions()
    {
        // Define start and end of the current month
        $startOfMonth = Carbon::now()->setTimezone('Africa/Nairobi')->startOfMonth()->toDateTimeString();
        $endOfMonth = Carbon::now()->setTimezone('Africa/Nairobi')->endOfMonth()->toDateTimeString();

        // Filter admissions for the current month
        $admissions = DB::table('tbl_admissions')
            ->join('enrollments', 'tbl_admissions.student_id', '=', 'enrollments.id')
            ->join('tbl_courses', 'tbl_admissions.course_id', '=', 'tbl_courses.id')
            ->whereBetween('tbl_admissions.admission_date', [$startOfMonth, $endOfMonth])
            ->select(
                'tbl_admissions.*',
                'enrollments.name as student_name',
                'enrollments.idno',
                'enrollments.station',
                'enrollments.phone',
                'enrollments.account',
                'enrollments.email',
                'enrollments.paystatus',
                'enrollments.amount',
                'tbl_courses.course_name'
            )
            ->orderBy('tbl_admissions.admission_date', 'desc')
            ->get();

           // return  $admissions;

        // Fetch supporting data
        $courses = DB::table('tbl_courses')->get();
        $stations = DB::table('tbl_training_stations')->get();
        $trainers = DB::table('tbl_trainers')->get();
        $certifiedIds = DB::table('certified_members')->pluck('admid')->toArray();

        // Pass to view
        return view('training.admissionstable')->with([
            'admissions' => $admissions,
            'courses' => $courses,
            'stations' => $stations,
            'trainers' => $trainers,
            'certifiedIds' => $certifiedIds,
            'duration' => Carbon::now()->setTimezone('Africa/Nairobi')->format('F Y'),
        ]);
    }

    public function filteradmition(Request $request)
    {


            $month = $request->input('month');
            $year  = $request->input('year');

            $admissions = collect(); // empty collection by default
            $duration   = null;

            if ($month && $year) {
            // Define start and end of selected month/year
            $startOfMonth = Carbon::createFromDate($year, $month, 1, 'Africa/Nairobi')->startOfMonth()->toDateTimeString();
            $endOfMonth   = Carbon::createFromDate($year, $month, 1, 'Africa/Nairobi')->endOfMonth()->toDateTimeString();

            // Get filtered admissions
            $admissions = DB::table('tbl_admissions')
                ->join('enrollments', 'tbl_admissions.student_id', '=', 'enrollments.id')
                ->join('tbl_courses', 'tbl_admissions.course_id', '=', 'tbl_courses.id')
                ->whereBetween('tbl_admissions.admission_date', [$startOfMonth, $endOfMonth])
                ->select(
                    'tbl_admissions.*',
                    'enrollments.name as student_name',
                    'enrollments.idno',
                    'enrollments.station',
                    'enrollments.phone',
                    'enrollments.account',
                    'enrollments.email',
                    'enrollments.paystatus',
                    'enrollments.amount',
                    'enrollments.companyname',
                    'tbl_courses.course_code as course_name'

                )
                ->orderBy('tbl_admissions.admission_date', 'desc')
                ->get();

            $duration = Carbon::createFromDate($year, $month, 1, 'Africa/Nairobi')->format('F Y');
        }


            // Supporting data
            $courses = DB::table('tbl_courses')->get();
            $stations = DB::table('tbl_training_stations')->get();
            $trainers = DB::table('tbl_trainers')->get();
            $certifiedIds = DB::table('certified_members')->pluck('admid')->toArray();



        $monthfilter=$yearfilter="1";
        $contributions="";
          $data=[
            'contributions' => $contributions,
            'monthfilter' => $monthfilter,
            'yearfilter' => $yearfilter,
            'admissions'   => $admissions,
            'courses'      => $courses,
            'stations'     => $stations,
            'trainers'     => $trainers,
            'certifiedIds' => $certifiedIds,
            'duration'     => $duration,
            'month'        => $month,
            'year'         => $year,
            

        ];

        return view('training.filteradmissionstable')->with($data);
    }
    


}
