<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth;
use Carbon\Carbon;
use App\Traits\SendsNotifications;


class TrainingController extends Controller
{
    //
    use SendsNotifications;

    public function home()
    {
        $contributions="100";
        $contributions = DB::table('enrollments')->sum('amount');
        $students = DB::table('tbl_students_data')->get();
        $totalStudentsRegistered = DB::table('tbl_students_data')->count();
        $trainingStations = DB::table('tbl_training_stations')->count();
        $courses_registered = DB::table('tbl_courses')->count();
        $enrollments = DB::table('enrollments')->count();
        $certified_members = DB::table('certified_members')->count();


       
        $monthlyData = [];

        // Query to count certifications issued each month
        $certificationsCountByMonth = DB::table('certified_members')
            ->select(DB::raw('MONTH(approvaldate) as month'), DB::raw('COUNT(*) as total'))
            ->groupBy(DB::raw('MONTH(approvaldate)'))
            ->get();

        $currentYear = Carbon::now()->year;

        $certificationsCountByMonth = DB::table('certified_members')
                ->select(DB::raw('MONTH(approvaldate) as month'), DB::raw('COUNT(*) as total'))
                ->whereYear('approvaldate', $currentYear)
                ->groupBy(DB::raw('MONTH(approvaldate)'))
                ->get();

            
        // Loop through the result and populate the monthly data array
        foreach ($certificationsCountByMonth as $row) {
            // Extract month and total from the result row
            $month = $row->month;
            $total = $row->total;
            
            // Assign the total to the respective month in the monthly data array
            // Here, we're using month numbers as keys (1 for January, 2 for February, etc.)
            // If you want month names as keys, you can use date('F', mktime(0, 0, 0, $month, 1))
            $monthlyData[$month] = $total;
        }

        // Fill in any missing months with 0
        for ($i = 1; $i <= 12; $i++) {
            if (!isset($monthlyData[$i])) {
                $monthlyData[$i] = 0;
            }
        }

        // Sort the array by keys (months)
        ksort($monthlyData);
          // Convert to values array for the chart
        $chartData = array_values($monthlyData);


            $trainings = DB::table('certified_members as cm')
            ->join('tbl_courses as c', 'cm.courseCode', '=', 'c.id')
            ->select(
                'c.course_name',
                DB::raw('YEAR(cm.approvaldate) as training_year'),
                DB::raw('COUNT(cm.id) as total_trainings')
            )
            ->groupBy('c.course_name', 'training_year')
            ->orderBy('c.course_name')
            ->orderBy('training_year')
            ->get();

            $trainersPerformance = DB::select('
                            SELECT
                                t.name,
                                t.pin,
                                COUNT(cm.cert_serial) AS total_certifications
                            FROM tbl_trainers AS t
                            JOIN certified_members AS cm
                                ON t.pin = cm.trainerid COLLATE utf8mb4_unicode_ci
                            GROUP BY
                                t.name, t.pin
                            ORDER BY
                                total_certifications DESC
                        ');


        $data=[
            'contributions' => $contributions,
            'students' => $students,
            'totalStudentsRegistered' => $totalStudentsRegistered,
            'trainingStations' => $trainingStations,
            'monthlyData' => $monthlyData,
            'chartData' => $chartData,
            'courses_registered' => $courses_registered,
            'enrollments' => $enrollments,
            'certified_members' => $certified_members,
            'trainings' => $trainings,
            'trainersPerformance' => $trainersPerformance,
            'currentYear' => $currentYear,

        ];

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

    public function table()
    {
        $contributions="";

        $data=[
            'contributions' => $contributions,
            

        ];

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

    public function updatepaymentrequest()
    {
        $contributions="";
        $transactions = DB::table('transaction_updates')
            ->orderBy('transaction_date', 'desc')
            ->get();

        $transactions = DB::table('transaction_updates as tu')
        ->join('users as u', 'tu.user_id', '=', 'u.id')
        ->select(
            'tu.id',
            'u.name as user_name',
            'tu.transaction_id',
            'tu.account_no',
            'tu.transaction_date',
            'tu.created_at'
        )
        ->orderBy('tu.transaction_date', 'desc')
        ->get();

        $data=[
            'contributions' => $contributions,
            'transactions' => $transactions,
            'stations' => $contributions,
            

        ];

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

    public function sendtextsms()
    {
        $contributions="";
            $messages = DB::table('messages')->get(); // This returns all records

            $messages = DB::table('messages')
        ->join('users', 'messages.userid', '=', 'users.id')
        ->select('messages.*', 'users.name as name')
        ->get();

        $data=[
            'contributions' => $contributions,
            'messages' => $messages,   
        ];

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

    public function completetextsms(Request $request)
    {
        // Validate input
        $validated = $request->validate([
            'userid' => 'required|max:255',
            'mobileno' => 'required|max:10',
            'message' => 'required',
        ]);
            $phone = $validated['mobileno'];
            $message = $validated['message'];

        $Notify = $this->SendNotification($phone, $message);

        // Insert data into messages table using DB Facade
        DB::table('messages')->insert([
            'userid' => $validated['userid'],
            'phone' => $validated['mobileno'],
            'message' => $validated['message'],
            'created_at' => now(),
            'updated_at' => now(),
        ]);



        // Return success message
        return back()->with('success', 'Message submitted successfully!');
    }


    public function Studenttable()
    {
        $contributions="";
        $students = DB::table('tbl_students_data')->get();
        
        $data=[
            'contributions' => $contributions,
            'students' => $students, 
        ];

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

    
    public function registerstudent(Request $request)
    {
        // Validate the form data
        $request->validate([
            'full_names' => 'required|string',
            'national_id' => 'required|string',
            'phone' => 'required|string',
            'email' => 'required|email',
            'gender' => 'required|string',
            //'zip' => 'required|string',
        ]);

        //$registeredby=Auth::user()->name;

        // Insert data into the database using the DB facade
        try {
            DB::table('tbl_students_data')->insert([
                'full_names' => $request->input('full_names'),
                'national_id' => $request->input('national_id'),
                'phone' => $request->input('phone'),
                'email' => $request->input('email'),
                'gender' => $request->input('gender'),
                'registration_date' => now(), // Assuming you want to save the current date
                'registered_by' => $request->input('registered_by'), // You might want to get the logged-in user or set it differently
            ]);

            return redirect()->back()->with('success', 'Student registered successfully.');
        } catch (\Exception $e) {
            return redirect()->back()->with('error', 'Error registering student. Please try again.');
        }
    }


    public function registerTrainingStations()
    {
        $contributions="";
        $stations = DB::table('tbl_training_stations')->get();

        $data=[
            'contributions' => $contributions,
            'stations' => $stations,
        ];

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

    public function savetrainingstation(Request $request)
    {
        // Validate the form data
        $request->validate([
            'station_name' => 'required|string',
            'location' => 'required|string',
            'status' => 'required|in:active,inactive',
        ]);

        // Insert data into the database using the DB facade
        try {
            DB::table('tbl_training_stations')->insert([
                'station_name' => $request->input('station_name'),
                'location' => $request->input('location'),
                'status' => $request->input('status'),
            ]);

            return redirect()->back()->with('success', 'Training station registered successfully.');
        } catch (\Exception $e) {
            return redirect()->back()->with('error', 'Error registering training station. Please try again.');
        }
    }

    public function toggleStatus($id)
    {
        $station = DB::table('tbl_training_stations')->where('id', $id)->first();

        if (!$station) {
            return back()->withErrors('Station not found.');
        }

        $newStatus = $station->status === 'active' ? 'inactive' : 'active';

        DB::table('tbl_training_stations')
            ->where('id', $id)
            ->update(['status' => $newStatus]);

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


    public function registerCourses()
    {
        $contributions="";
        $courses = DB::table('tbl_courses')->get();

        $data=[
            'contributions' => $contributions,
            'courses' => $courses,           

        ];

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

    public function saveCourses(Request $request)
    {
        // Validate the form data
        $request->validate([
            'course_name' => 'required|string',
            'course_duration' => 'required|string',
            'course_cost' => 'required',
            'course_code' => 'required',
            'course_validity' => 'required',
        ]);

        // Insert data into the database using the DB facade
        try {
            DB::table('tbl_courses')->insert([
                'course_name' => $request->input('course_name'),
                'course_duration' => $request->input('course_duration'),
                'course_cost' => $request->input('course_cost'),
                'course_code' => $request->input('course_code'),
                'course_validity' => $request->input('course_validity'),
            ]);

            return redirect()->back()->with('success', 'Course registered successfully.');
        } catch (\Exception $e) {
            return redirect()->back()->with('error', 'Error registering Course. Please try again.');
        }
    }


    public function StudentEnrolmenttab()
    {
        $contributions="";

    
        $students = DB::table('tbl_students_data')->get();

        $data=[
            'contributions' => $contributions,
            'students' => $students,
            

        ];

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

    public function StudentCompletion()
    {
        $contributions="";

        $data=[
            'contributions' => $contributions,
            

        ];

        return view('straining.confirmedtable')->with($data);
    }

    public function TrainersView()
    {
        $contributions="";
        $trainers = DB::table('tbl_trainers')->get();
        $courses = DB::table('tbl_courses')->get();


        $data=[
            'contributions' => $contributions,
            'trainers' => $trainers,
            'courses' => $courses,
            

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

    public function storeTrainer(Request $request)
    {
        $request->validate([
            'pin' => 'required|unique:tbl_trainers,pin',
            'name' => 'required|string|max:255',
            'contact' => 'required|string|max:20',
        ]);

        DB::table('tbl_trainers')->insert([
            'pin' => $request->pin,
            'name' => $request->name,
            'contact' => $request->contact,
            'created_at' => now(),
            'updated_at' => now(),
        ]);

        return redirect()->back()->with('success', 'Trainer registered successfully.');
    }

    public function trainingstationsrevenue(Request $request)
    {
            $year = $request->input('year');
            $month = $request->input('month');

            $revenues = \DB::table('enrollments')
                ->select(
                    'station',
                    \DB::raw('SUM(CAST(amount AS DECIMAL(10,2))) AS total_income')
                )
                ->where('paystatus', 'paid')
                ->when($year, function ($query, $year) {
                    return $query->whereYear('startdate', $year);
                })
                ->when($month, function ($query, $month) {
                    return $query->whereMonth('startdate', $month);
                })
                ->groupBy('station')
                ->orderByDesc('total_income')
                ->get();

       // $duration="1";
         // Prepare duration label for display
        if ($year && $month) {
            $duration = \Carbon\Carbon::createFromDate($year, $month, 1)->format('F Y');
        } elseif ($year) {
            $duration = $year;
        } else {
            $duration = "All Time";
        }

        $data=[
            'month'         => $month,
            'year'          => $year,
            'duration'      => $duration,
            'revenues'      => $revenues,           

        ];

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

    public function mpesaEnrollments(Request $request)
        {
            $date = $request->input('date', now()->toDateString());

            $enrollments = \DB::table('enrollments as e')
                ->join('tbl_courses as c', 'e.course', '=', 'c.id')
                ->select('e.*', 'c.course_name')
                ->where('e.paymentmode', 'mpesa')
                ->whereDate('e.created_at', $date)
                ->get();

               // return $enrollments;

            //return response()->json($enrollments);
            $data=[
          
            'date'             => $date,
            'enrollments'      => $enrollments,           

        ];

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