<?php

namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\Mail;
use Illuminate\Support\Facades\Auth;
use SimpleSoftwareIO\QrCode\Facades\QrCode;
use Illuminate\Support\Facades\Log;
use App\Mail\CertificationEmail; // Import the Mailable class
use App\Mail\MiniCertificationMail;
use App\Mail\ReceiptMail;
use App\Traits\SendsNotifications;

use Illuminate\Http\Request;
use Codedge\Fpdf\Fpdf\Fpdf;
use Barryvdh\DomPDF\Facade\Pdf;
use Carbon\Carbon;


class CertificatesController extends Controller
{
    use SendsNotifications;
    //
    public function MakeCertificatenew()
    {
        $text="Text";

            $id_no ="123456" ;
            $client_names = "Eliazer Bukaya Angola Local";
            $Coursename = "First Aid At Work";
            $serialNo = "TRNID123456";
            $TrainingStation = "St John HQ";
            $TrainingCounty = "County Name";
            $TrainerId = "TRNID123456";
            $personalNo = "2023123456";
            $station = "Station";


            $Designation = "Supervisor";
            $tarehe = "2023-04-23";
            $experyDate = "2023-04-23";
            $imagePath = public_path('img/signature.png'); // Update with the correct path to your image
            $priorsignature = public_path('img/prior.png'); // Update with the correct path to your image
            //$imagePath = public_path('img/bg/bg.jpg');

            $date=date_create($tarehe);
            $approval_date= date_format($date,"d F Y");

            $date=date_create($experyDate);
            $experyDate= date_format($date,"d F Y");

            $validity= "This certificate is valid until the $experyDate";

            $pdf = new Fpdf('L', 'mm', 'A4');
            $pdf->AddPage('P', 'A4');
            $pdf->Image(public_path('img/bg/certificatedesign.png'), 0, 0, $pdf->GetPageWidth(), $pdf->GetPageHeight(), 'PNG');   

            $pdf->SetFont('Arial', 'B', 16);
            $pdf->SetTextColor(0, 0, 0); // Set text color (RGB values)
            $pdf->SetXY(60, 45); // Set position for text

            // Set a larger font size for $client_names
            $pdf->SetFont('Arial', 'B', 24);
            // Get the width of the text
            $textWidth = $pdf->GetStringWidth($client_names);
            // Calculate the X position to center the text
            $x = ($pdf->GetPageWidth() - $textWidth) / 2;
            // Set the position for the text to be centered
            $pdf->SetX($x);
            // Add the text (this will now be centered horizontally on the page)
            $pdf->Cell(20, 100, $client_names, 0, 1);

            // course name
            $pdf->SetFont('Arial', 'B', 14);
            $pdf->SetTextColor(0, 0, 0); // Set text color (RGB values)
            $pdf->SetXY(60, 95); // Set position for text

            // Set a larger font size for $client_names
            $pdf->SetFont('Arial', 'B', 24);
            // Get the width of the text
            $textWidth = $pdf->GetStringWidth($Coursename);
            // Calculate the X position to center the text
            $x = ($pdf->GetPageWidth() - $textWidth) / 2;
            // Set the position for the text to be centered
            $pdf->SetX($x);
            // Add the text (this will now be centered horizontally on the page)
            $pdf->Cell(20, 100, $Coursename, 0, 1);

            //serial Number
            $pdf->SetFont('Arial', 'B', 14);
            $pdf->SetXY(35, 157); // Set position for text
            $pdf->Cell(0, 10, $serialNo, 0, 0); // Add the text


            // Reset font size for the rest of the text
            $pdf->SetFont('Arial', 'B', 14);
            $pdf->SetXY(35, 178); // Set position for text
            $pdf->Cell(0, 10, $approval_date, 0, 1); // Add the text
            $pdf->SetXY(35, 197); // Set position for text
            $pdf->Cell(0, 10, $experyDate, 0, 1); // Add the text      

            

            // Reset font size for the rest of the text
            $pdf->SetFont('Arial', 'B', 14);
            $pdf->SetXY(128, 157); // Set position for text
            $pdf->Cell(0, 10, $TrainingStation, 0, 1); // Add the text
            $pdf->SetXY(128, 178); // Set position for text
            $pdf->Cell(0, 10, $TrainingCounty, 0, 1); // Add the text
            $pdf->SetXY(128, 197); // Set position for text
            $pdf->Cell(0, 10, $TrainerId, 0, 1); // Add the text

                // Build the verification URL
                $verifyUrl = "https://verify.stjohnkenya.org/confirmation.php?ref=" . urlencode($serialNo);

                // Define where to save the QR image
                $qrImagePath = storage_path('app/temp/qr/' . $serialNo . '_qr.png');

                // Ensure the directory exists
                File::ensureDirectoryExists(storage_path('app/temp/qr'));

                // Generate and save the QR image using Google Chart API
                $qrImagePath = storage_path("app/temp/qr/{$serialNo}_qr.png");
                file_put_contents($qrImagePath, file_get_contents(
                    'https://api.qrserver.com/v1/create-qr-code/?size=150x150&data=' . urlencode($verifyUrl)
                ));

                // After adding all your PDF content
                $pdf->Image($qrImagePath, 93, 230, 20, 0, 'PNG'); // Adjust position if needed

                

            // Add the image
            $pdf->Image($imagePath, 35, 220, 47, 0, 'PNG');
            $pdf->Image($priorsignature, 126, 230, 47, 0, 'PNG');

            $pdf->SetFont('Arial', 'i', 8);
            $pdf->SetXY(70, 270); // Set position for text
            $pdf->Cell(0, 5, $validity, 0, 0); // Add the text
       
       
       //$pdf->Output();
       // Save the PDF to a temporary file
       $email="brianchemo@yopmail.com";
       //$email="anikayibramuel@gmail.com";
       // Define the directory path to save the PDF
       $pdfDirectory = storage_path('app/temp');
        
       // Ensure the directory exists, if not, create it
       File::ensureDirectoryExists($pdfDirectory);

       // Define the file path to save the PDF
       //$pdfFilePath = $pdfDirectory . '/Training_certification.pdf';
       $pdfFilePath = $pdfDirectory . '/' . $serialNo . '_Training_certification.pdf';

       // Save the PDF to the file path
       $pdf->Output($pdfFilePath, 'F');

       // Send the PDF as an attachment in an email
     // Mail::to($email)->send(new CertificationEmail($pdfFilePath, $client_names));

       // Delete the temporary PDF file
      // unlink($pdfFilePath);
       // After saving PDF
       // unlink($qrImagePath); // Clean up
       $pdf->Output();
        exit;
    }

    public function MakeminiCertificatenew($serialNo)
    {
        //$serialNo="STJAK20240313125"; //get from request

        $today = Carbon::today();

        $result = DB::table('certified_members')
            ->join('tbl_courses', 'certified_members.courseCode', '=', 'tbl_courses.id')
            ->join('tbl_training_stations', 'certified_members.training_location', '=', 'tbl_training_stations.station_name')
            ->select(
                'certified_members.*',
                'tbl_courses.course_name',
                'tbl_courses.course_duration',
                'tbl_training_stations.location'
            )
            ->where('certified_members.cert_serial', $serialNo)
            ->whereDate('certified_members.experydate', '>=', $today)
            ->orderBy('certified_members.experydate', 'desc')
            ->first();


            if ($result) {
            // Record found
                $client_names     = $result->full_names;
                $tarehe           = $result->approvaldate;
                $experyDate       = $result->experydate;
                $email            = $result->email;
                $TrainingStation  = $result->training_location;
                $serialNo         = $result->cert_serial;
                $Coursename       = $result->course_name;
                $TrainingCounty   = $result->location;
                $TrainerId        = $result->trainerid;
            } else {
                // No record found, handle accordingly
                return redirect()->back()->with('error', "No valid certificate found for Serial Number: $serialNo");
            }        

            $date=date_create($tarehe);
            $approval_date= date_format($date,"d F Y");

            $date=date_create($experyDate);
            $experyDate= date_format($date,"d F Y");

            $validity= "This ecard is valid until the $experyDate";

            $pdf = new Fpdf('P', 'mm', 'A4');
            $pdf->AddPage('P', 'A4');
            $pdf->Image(public_path('img/bg/CardTemplate.png'), 0, 0, $pdf->GetPageWidth(), $pdf->GetPageHeight(), 'PNG');   

            //trainee names
            $pdf->SetFont('Arial', 'B', 10);
            $pdf->SetXY(45, 36); // Set position for text
            $pdf->Cell(20, 10, $client_names, 0, 1); // Add the text

            $pdf->SetFont('Arial', 'B', 9);
            $pdf->SetXY(30, 49); // Set position for text
            $pdf->Cell(20, 10, $Coursename, 0, 1); // Add the text            
           

            //serial Number
            $pdf->SetFont('Arial', 'B', 6);
            $pdf->SetXY(58, 52); // Set position for text
            $pdf->Cell(20, 10, $serialNo, 0, 1); // Add the text


            // Reset font size for the rest of the text
            $pdf->SetFont('Arial', 'B', 6);
            $pdf->SetXY(39, 52); // Set position for text
            $pdf->Cell(0, 10, $approval_date, 0, 1); // Add the text
            $pdf->SetXY(20, 52); // Set position for text
            $pdf->Cell(0, 10, $experyDate, 0, 1); // Add the text   
            
            $pdf->SetFont('Arial', 'i', 4);
            $pdf->SetXY(40, 64); // Set position for text
            $pdf->Cell(0, 5, $validity, 0, 0); // Add the text

            

               // Build the verification URL
                $verifyUrl = "https://verify.stjohnkenya.org/confirmation.php?ref=" . urlencode($serialNo);

                // Define where to save the QR image
                $qrImagePath = storage_path('app/temp/qr/' . $serialNo . '_qr.png');

                // Ensure the directory exists
                File::ensureDirectoryExists(storage_path('app/temp/qr'));

                // Generate and save the QR image using Google Chart API
                $qrImagePath = storage_path("app/temp/qr/{$serialNo}_qr.png");
                file_put_contents($qrImagePath, file_get_contents(
                    'https://api.qrserver.com/v1/create-qr-code/?size=150x150&data=' . urlencode($verifyUrl)
                ));

                // After adding all your PDF content
                $pdf->Image($qrImagePath, 80, 60, 10, 0, 'PNG'); // Adjust position if needed

   
            //$pdf->Output();
            // Save the PDF to a temporary file
            //$email="brianchemo@yopmail.com";
            //$email="anikayibramuel@gmail.com";
            // Define the directory path to save the PDF
            $pdfDirectory = storage_path('app/temp');
                
            // Ensure the directory exists, if not, create it
            File::ensureDirectoryExists($pdfDirectory);

            // Define the file path to save the PDF
            //$pdfFilePath = $pdfDirectory . '/Training_certification.pdf';
            $pdfFilePath = $pdfDirectory . '/' . $serialNo . '_Training_mini_certificate.pdf';

            // Save the PDF to the file path
            $pdf->Output($pdfFilePath, 'F');

            // Send the PDF as an attachment in an email
           Mail::to($email)->send(new MiniCertificationMail($pdfFilePath, $client_names));

            // Delete the temporary PDF file
            //unlink($pdfFilePath);
            // After saving PDF
                unlink($qrImagePath); // Clean up
            $pdf->Output();
                exit;
    }

    public function MakeCertificate()
    {
        $text="Text";

             $id_no ="123456" ;
             $client_names = "Anikayi Bramuel";
            $serialNo = "Serial No: 255255";
            $personalNo = "2023123456";
            $station = "Station";
            $Designation = "Supervisor";
            $tarehe = "2023-04-23";
            $experyDate = "2023-04-23";
            $imagePath = public_path('img/signature.png'); // Update with the correct path to your image
            $priorsignature = public_path('img/prior.png'); // prior signature
            //$imagePath = public_path('img/bg/bg.jpg');

            $date=date_create($tarehe);
            $approval_date= date_format($date,"d F Y");

            $date=date_create($experyDate);
            $experyDate= date_format($date,"d F Y");

            $validity= "This certificate is valid until the $experyDate ";


            $pdf = new Fpdf('L', 'mm', 'A4');
            $pdf->AddPage('L', 'A4');

            $pdf->Image(public_path('img/bg/stjohncert.png'), 0, 0, $pdf->GetPageWidth(), $pdf->GetPageHeight(), 'PNG');
                

                // Add the image
            $pdf->Image($imagePath, 70, 145, 70, 0, 'PNG');

            $pdf->SetFont('Arial', 'B', 16);
            $pdf->SetTextColor(0, 0, 0); // Set text color (RGB values)
            $pdf->SetXY(120, 63); // Set position for text


            // Set a larger font size for $client_names
            $pdf->SetFont('Arial', 'B', 24);
            // $pdf->SetFont('Calligrapher', '', 24); // 'Calligrapher' is an example font name
            $pdf->Cell(0, 90, $client_names, 0, 1); // Add the text

        // Reset font size for the rest of the text
        $pdf->SetFont('Arial', 'B', 16);


       //$pdf->SetXY(12, 153); // Set position for text
      // $pdf->Cell(0, 10, $experyDate, 0, 1); // Add the text


       $pdf->SetXY(175, 160); // Set position for text
       $pdf->Cell(0, 10, $approval_date, 0, 1); // Add the text
       
       
       $pdf->SetFont('Arial', 'B', 12);
       $pdf->SetXY(120, 175); // Set position for text
       $pdf->Cell(0, 10, $serialNo, 0, 0); // Add the text

       $pdf->SetFont('Arial', 'i', 8);
       $pdf->SetXY(20, 180); // Set position for text
       $pdf->Cell(0, 5, $validity, 0, 0); // Add the text
       
       
       //$pdf->Output();
       // Save the PDF to a temporary file
       $email="brianchemo@yopmail.com";
       //$email="anikayibramuel@gmail.com";
       // Define the directory path to save the PDF
       $pdfDirectory = storage_path('app/temp');
        
       // Ensure the directory exists, if not, create it
       File::ensureDirectoryExists($pdfDirectory);

       // Define the file path to save the PDF
       $pdfFilePath = $pdfDirectory . '/Training_certification.pdf';

       // Save the PDF to the file path
       $pdf->Output($pdfFilePath, 'F');

       // Send the PDF as an attachment in an email
      // Mail::to($email)->send(new CertificationEmail($pdfFilePath, $client_names));

       // Delete the temporary PDF file
       unlink($pdfFilePath);
       $pdf->Output();
        exit;
    }

    public function getcertificate()
    {

        return view('certificates');
    }

    public function oldgetcert(Request $request)
    {
        // return $request->all();
        $id_no = $request->idno;

        $result = DB::table('certified_members')
        ->where('national_id', $id_no)
        ->orwhere('cert_serial', $id_no)
        ->first();

        if ($result) {

            // Check the status field
        if ($result->status !== 'valid') {
            return redirect()->back()->with('error', "The certificate with ID or Serial Number $id_no has expired.");
        } 
            // The record exists
            // Accessing other values from the result
            $client_names = $result->full_names;
            $tarehe = $result->approvaldate;
            $experyDate = $result->experydate;
            $email = $result->email;
            $training_location = $result->training_location;
            $cert_serial = $result->cert_serial;

                $id_no ="123456" ;
                //$client_names = "Dennis Matara";
                $serialNo = "Serial No: $cert_serial";
                $personalNo = "2023123456";
                $station = "Station";
                $Designation = "Supervisor";
                //$tarehe = "2023-04-23";
                //$experyDate = "2023-04-23";
                $imagePath = public_path('img/signature.png'); // Update with the correct path to your image
                //$imagePath = public_path('img/bg/bg.jpg');

                $date=date_create($tarehe);
                $approval_date= date_format($date,"d F Y");

                $date=date_create($experyDate);
                $experyDate= date_format($date,"d F Y");

                $validity= "This certificate is valid until the $experyDate ";


                $pdf = new Fpdf('L', 'mm', 'A4');
                $pdf->AddPage('L', 'A4');

                $pdf->Image(public_path('img/bg/stjohncert.png'), 0, 0, $pdf->GetPageWidth(), $pdf->GetPageHeight(), 'PNG');
                

                // Add the image
                $pdf->Image($imagePath, 70, 145, 70, 0, 'PNG');

                $pdf->SetFont('Arial', 'B', 16);
                $pdf->SetTextColor(0, 0, 0); // Set text color (RGB values)
                $pdf->SetXY(120, 63); // Set position for text


            // Set a larger font size for $client_names
                $pdf->SetFont('Arial', 'B', 24);
            // $pdf->SetFont('Calligrapher', '', 24); // 'Calligrapher' is an example font name
                $pdf->Cell(0, 90, $client_names, 0, 1); // Add the text

                // Reset font size for the rest of the text
                $pdf->SetFont('Arial', 'B', 16);


            //$pdf->SetXY(12, 153); // Set position for text
            // $pdf->Cell(0, 10, $experyDate, 0, 1); // Add the text


            $pdf->SetXY(175, 160); // Set position for text
            $pdf->Cell(0, 10, $approval_date, 0, 1); // Add the text

            $pdf->SetXY(120, 175); // Set position for text
            $pdf->Cell(0, 10, $serialNo, 0, 0); // Add the text

            $pdf->SetFont('Arial', 'i', 8);
            $pdf->SetXY(20, 180); // Set position for text
            $pdf->Cell(0, 5, $validity, 0, 0); // Add the text
            
            
            $pdf->Output();
        exit;
        }
            else {
                // The record does not exist
                //return "No record found for ID: $id_no";
                return redirect()->back()->with('error', "No record found for ID or Serial Number: $id_no");
            }
    }

    public function getcert(Request $request)
    {
        // return $request->all();
        $id_no = $request->idno;
        $today = Carbon::today();

        $result = DB::table('certified_members')
        ->where('national_id', $id_no)
        ->orwhere('cert_serial', $id_no)
        ->first();

        $result = DB::table('certified_members')
            ->where('national_id', $id_no)
            ->orWhere('cert_serial', $id_no)
            ->orderBy('experydate', 'desc') // Order by experydate, most recent first
            ->first();

        //return $result;

        if ($result->status !== 'valid') {
            return redirect()->back()->with('error', "The certificate with ID or Serial Number $id_no has expired.");
        } 

       
        $result = DB::table('certified_members')
        ->join('tbl_courses', 'certified_members.courseCode', '=', 'tbl_courses.id')
        ->join('tbl_training_stations', 'certified_members.training_location', '=', 'tbl_training_stations.station_name') // Add this join
        ->select(
            'certified_members.*',
            'tbl_courses.course_name',
            'tbl_courses.course_duration',
            'tbl_training_stations.location' // Select the training station name
        )
        ->where(function ($query) use ($id_no) {
            $query->where('certified_members.national_id', $id_no)
                ->orWhere('certified_members.cert_serial', $id_no);
        })
        ->whereDate('certified_members.experydate', '>=', $today)
        ->orderBy('certified_members.experydate', 'desc') // Prioritize most recent valid
        ->first();

        //return $result;

        if ($result) {

            // Check the status field
        if ($result->status !== 'valid') {
            return redirect()->back()->with('error', "The certificate with ID or Serial Number $id_no has expired.");
        } 
            // The record exists
            // Accessing other values from the result
            $client_names = $result->full_names;
            $tarehe = $result->approvaldate;
            $experyDate = $result->experydate;
            $email = $result->email;
            $TrainingStation = $result->training_location;
            $serialNo = $result->cert_serial; 
            $Coursename = "First Aid At Work"; 
            $Coursename = $result->course_name;                  
            $TrainingCounty = "County Name";
            $TrainingCounty = $result->location;
            $TrainerId = "TRNID123456";
            $TrainerId = $result->trainerid;
                
            
            $imagePath = public_path('img/signature.png'); // Update with the correct path to your image
            $priorsignature = public_path('img/prior.png'); // Update with the correct path to your image
           
            $date=date_create($tarehe);
            $approval_date= date_format($date,"d F Y");

            $date=date_create($experyDate);
            $experyDate= date_format($date,"d F Y");

            $validity= "This certificate is valid until the $experyDate ";
            
            $pdf = new Fpdf('L', 'mm', 'A4');
            $pdf->AddPage('P', 'A4');
            $pdf->Image(public_path('img/bg/certificatedesign.png'), 0, 0, $pdf->GetPageWidth(), $pdf->GetPageHeight(), 'PNG');   

            $pdf->SetFont('Arial', 'B', 16);
            $pdf->SetTextColor(0, 0, 0); // Set text color (RGB values)
            $pdf->SetXY(60, 45); // Set position for text

             // Set a larger font size for $client_names
             $pdf->SetFont('Arial', 'B', 24);
             // Get the width of the text
             $textWidth = $pdf->GetStringWidth($client_names);
             // Calculate the X position to center the text
             $x = ($pdf->GetPageWidth() - $textWidth) / 2;
             // Set the position for the text to be centered
             $pdf->SetX($x);
             // Add the text (this will now be centered horizontally on the page)
             $pdf->Cell(20, 100, $client_names, 0, 1);
 
             // course name
             $pdf->SetFont('Arial', 'B', 16);
             $pdf->SetTextColor(0, 0, 0); // Set text color (RGB values)
             $pdf->SetXY(60, 95); // Set position for text
 
             // Set a larger font size for $client_names
             $pdf->SetFont('Arial', 'B', 24);
             // Get the width of the text
             $textWidth = $pdf->GetStringWidth($Coursename);
             // Calculate the X position to center the text
             $x = ($pdf->GetPageWidth() - $textWidth) / 2;
             // Set the position for the text to be centered
             $pdf->SetX($x);
             // Add the text (this will now be centered horizontally on the page)
             $pdf->Cell(20, 100, $Coursename, 0, 1);
 
             //serial Number
             $pdf->SetFont('Arial', 'B', 14);
             $pdf->SetXY(35, 157); // Set position for text
             $pdf->Cell(0, 10, $serialNo, 0, 0); // Add the text
 
 
             // Reset font size for the rest of the text
             $pdf->SetFont('Arial', 'B', 14);
             $pdf->SetXY(35, 178); // Set position for text
             $pdf->Cell(0, 10, $approval_date, 0, 1); // Add the text
             $pdf->SetXY(35, 197); // Set position for text
             $pdf->Cell(0, 10, $experyDate, 0, 1); // Add the text
        
 
             
 
             // Reset font size for the rest of the text
             $pdf->SetFont('Arial', 'B', 14);
             $pdf->SetXY(128, 157); // Set position for text
             $pdf->Cell(0, 10, $TrainingStation, 0, 1); // Add the text
             $pdf->SetXY(128, 178); // Set position for text
             $pdf->Cell(0, 10, $TrainingCounty, 0, 1); // Add the text
             $pdf->SetXY(128, 197); // Set position for text
             $pdf->Cell(0, 10, $TrainerId, 0, 1); // Add the text

                // Build the verification URL
                $verifyUrl = "https://verify.stjohnkenya.org/confirmation.php?ref=" . urlencode($serialNo);

                // Define where to save the QR image
                $qrImagePath = storage_path('app/temp/qr/' . $serialNo . '_qr.png');

                // Ensure the directory exists
                File::ensureDirectoryExists(storage_path('app/temp/qr'));

                // Generate and save the QR image using Google Chart API
                $qrImagePath = storage_path("app/temp/qr/{$serialNo}_qr.png");
                file_put_contents($qrImagePath, file_get_contents(
                    'https://api.qrserver.com/v1/create-qr-code/?size=150x150&data=' . urlencode($verifyUrl)
                ));

                // After adding all your PDF content
                $pdf->Image($qrImagePath, 93, 230, 20, 0, 'PNG'); // Adjust position if needed
                // After saving PDF
                unlink($qrImagePath); // Clean up
 
             // Add the image
             $pdf->Image($imagePath, 35, 220, 47, 0, 'PNG');
             $pdf->Image($priorsignature, 126, 230, 47, 0, 'PNG');
 
             $pdf->SetFont('Arial', 'i', 8);
             $pdf->SetXY(70, 270); // Set position for text
             $pdf->Cell(0, 5, $validity, 0, 0); // Add the text        
          
            $pdf->Output();
        exit;
        }
            else {
                // The record does not exist
                //return "No record found for ID: $id_no";
                return redirect()->back()->with('error', "No record found for ID or Serial Number: $id_no");
            }
    }

    public function reprintCert($refno)
    {
        // return $request->all();
        $id_no = $refno;
        $today = Carbon::today();

        $result = DB::table('certified_members')
            ->where('national_id', $id_no)
            ->orWhere('cert_serial', $id_no)
            ->orderBy('experydate', 'desc') // Order by experydate, most recent first
            ->first();



       
        $result = DB::table('certified_members')
        ->join('tbl_courses', 'certified_members.courseCode', '=', 'tbl_courses.id')
        ->join('tbl_training_stations', 'certified_members.training_location', '=', 'tbl_training_stations.station_name') // Add this join
        ->select(
            'certified_members.*',
            'tbl_courses.course_name',
            'tbl_courses.course_duration',
            'tbl_training_stations.location' // Select the training station name
        )
        ->where(function ($query) use ($id_no) {
            $query->where('certified_members.national_id', $id_no)
                ->orWhere('certified_members.cert_serial', $id_no);
        })
        ->whereDate('certified_members.experydate', '>=', $today)
        ->orderBy('certified_members.experydate', 'desc') // Prioritize most recent valid
        ->first();

        //return $result;

        if ($result) {

            // Check the status field
        if ($result->status !== 'valid') {
            return redirect()->back()->with('error', "The certificate with ID or Serial Number $id_no has expired.");
        } 
            // The record exists
            // Accessing other values from the result
            $client_names = $result->full_names;
            $tarehe = $result->approvaldate;
            $experyDate = $result->experydate;
            $email = $result->email;
            $TrainingStation = $result->training_location;
            $serialNo = $result->cert_serial; 
            $Coursename = "First Aid At Work"; 
            $Coursename = $result->course_name;                  
            $TrainingCounty = "County Name";
            $TrainingCounty = $result->location;
            $TrainerId = "TRNID123456";
            $TrainerId = $result->trainerid;
                
            
            $imagePath = public_path('img/signature.png'); // Update with the correct path to your image
            $priorsignature = public_path('img/prior.png'); // Update with the correct path to your image
           
            $date=date_create($tarehe);
            $approval_date= date_format($date,"d F Y");

            $date=date_create($experyDate);
            $experyDate= date_format($date,"d F Y");

            $validity= "This certificate is valid until the $experyDate ";
            
            $pdf = new Fpdf('L', 'mm', 'A4');
            $pdf->AddPage('P', 'A4');
            $pdf->Image(public_path('img/bg/certificatedesign.png'), 0, 0, $pdf->GetPageWidth(), $pdf->GetPageHeight(), 'PNG');   

            $pdf->SetFont('Arial', 'B', 16);
            $pdf->SetTextColor(0, 0, 0); // Set text color (RGB values)
            $pdf->SetXY(60, 45); // Set position for text

             // Set a larger font size for $client_names
             $pdf->SetFont('Arial', 'B', 24);
             // Get the width of the text
             $textWidth = $pdf->GetStringWidth($client_names);
             // Calculate the X position to center the text
             $x = ($pdf->GetPageWidth() - $textWidth) / 2;
             // Set the position for the text to be centered
             $pdf->SetX($x);
             // Add the text (this will now be centered horizontally on the page)
             $pdf->Cell(20, 100, $client_names, 0, 1);
 
             // course name
             $pdf->SetFont('Arial', 'B', 16);
             $pdf->SetTextColor(0, 0, 0); // Set text color (RGB values)
             $pdf->SetXY(60, 95); // Set position for text
 
             // Set a larger font size for $client_names
             $pdf->SetFont('Arial', 'B', 24);
             // Get the width of the text
             $textWidth = $pdf->GetStringWidth($Coursename);
             // Calculate the X position to center the text
             $x = ($pdf->GetPageWidth() - $textWidth) / 2;
             // Set the position for the text to be centered
             $pdf->SetX($x);
             // Add the text (this will now be centered horizontally on the page)
             $pdf->Cell(20, 100, $Coursename, 0, 1);
 
             //serial Number
             $pdf->SetFont('Arial', 'B', 14);
             $pdf->SetXY(35, 157); // Set position for text
             $pdf->Cell(0, 10, $serialNo, 0, 0); // Add the text
 
 
             // Reset font size for the rest of the text
             $pdf->SetFont('Arial', 'B', 14);
             $pdf->SetXY(35, 178); // Set position for text
             $pdf->Cell(0, 10, $approval_date, 0, 1); // Add the text
             $pdf->SetXY(35, 197); // Set position for text
             $pdf->Cell(0, 10, $experyDate, 0, 1); // Add the text
        
 
             
 
             // Reset font size for the rest of the text
             $pdf->SetFont('Arial', 'B', 14);
             $pdf->SetXY(128, 157); // Set position for text
             $pdf->Cell(0, 10, $TrainingStation, 0, 1); // Add the text
             $pdf->SetXY(128, 178); // Set position for text
             $pdf->Cell(0, 10, $TrainingCounty, 0, 1); // Add the text
             $pdf->SetXY(128, 197); // Set position for text
             $pdf->Cell(0, 10, $TrainerId, 0, 1); // Add the text

                // Build the verification URL
                $verifyUrl = "https://verify.stjohnkenya.org/confirmation.php?ref=" . urlencode($serialNo);

                // Define where to save the QR image
                $qrImagePath = storage_path('app/temp/qr/' . $serialNo . '_qr.png');

                // Ensure the directory exists
                File::ensureDirectoryExists(storage_path('app/temp/qr'));

                // Generate and save the QR image using Google Chart API
                $qrImagePath = storage_path("app/temp/qr/{$serialNo}_qr.png");
                file_put_contents($qrImagePath, file_get_contents(
                    'https://api.qrserver.com/v1/create-qr-code/?size=150x150&data=' . urlencode($verifyUrl)
                ));

                // After adding all your PDF content
                $pdf->Image($qrImagePath, 93, 230, 20, 0, 'PNG'); // Adjust position if needed
                // After saving PDF
                unlink($qrImagePath); // Clean up
 
             // Add the image
             $pdf->Image($imagePath, 35, 220, 47, 0, 'PNG');
             $pdf->Image($priorsignature, 126, 230, 47, 0, 'PNG');
 
             $pdf->SetFont('Arial', 'i', 8);
             $pdf->SetXY(70, 270); // Set position for text
             $pdf->Cell(0, 5, $validity, 0, 0); // Add the text   
             
             // Output with custom name
            $filename = 'Certificate_' . $refno . '.pdf'; 
            $pdf->Output('I', $filename);
          
            $pdf->Output();
        exit;
        }
            else {
                // The record does not exist
                //return "No record found for ID: $id_no";
                return redirect()->back()->with('error', "No record found for ID or Serial Number: $id_no");
            }
    }

    public function CertificiedStudents()
    {
        $contributions="";
        $students = DB::table('certified_members')->get();
        $courses = DB::table('tbl_courses')->get();
        $stations = DB::table('tbl_training_stations')->get();

        $trainers = DB::table('tbl_trainers')->get();
        

        $students = DB::table('certified_members')
            ->join('tbl_courses', 'certified_members.courseCode', '=', 'tbl_courses.id')
            ->select('certified_members.*', 'tbl_courses.*') // Select columns you need
            ->get();
        //return $students;

        $data=[
            'contributions' => $contributions,
            'students' => $students, 
            'courses' => $courses, 
            'stations' => $stations,
            'trainers' => $trainers, 
            
        ];

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

    public function registerCertifiedStudent(Request $request)
    {
        $userId = Auth::id();

        // Check if user is in allowed_users table
        $isAllowed = DB::table('allowed_users')
            ->where('user_id', $userId)
            ->exists();

        if (!$isAllowed) {
            return back()->withErrors('You are not allowed to make this transaction. Contact administrator.');
        }


        try {
            // Validate the form data
            $validatedData = $request->validate([
                'full_names' => 'required|string|max:255',
                'national_id' => 'required|string|max:255',
                'phone' => 'required|string|max:255',
                'email' => 'required|email|max:255',
                'approvaldate' => 'required|date',
                'training_station' => 'required',
                'trainerid' => 'required',
                'admissionid' => 'required',
                'course_id' => 'required|exists:tbl_courses,id',
                'send_email' => 'nullable|in:yes,no', // new field
            ]);

            $courseid=$validatedData['course_id'];
            $coursecode = DB::table('tbl_courses')
                    ->where('id', $courseid)
                    ->value('course_code');
                    

            // Generate cert_serial
            //$certSerial = $coursecode . date('Ymd') . mt_rand(1, 500);
            do {
                //$certSerial = $coursecode . date('Ymd') . mt_rand(1, 500);
                $certSerial = $coursecode . date('YmdHis') . mt_rand(1, 100);
            } while (
                DB::table('certified_members')->where('cert_serial', $certSerial)->exists()
            );

            // Retrieve the approval date from the validated data
            $approvalDate = $validatedData['approvaldate'];

            // Calculate the expiration date one year after the approval date
            //$expirationDate = date('Y-m-d', strtotime('+1 year', strtotime($approvalDate)));

            // Get the course validity (in years) from tbl_courses
            $courseValidity = DB::table('tbl_courses')
                ->where('id', $courseid) // adjust this based on your data
                ->value('course_validity');


            // Fallback to 1 year if null
            $courseValidity = $courseValidity ?? 1;

            // Calculate expiration date: add validity in years, then subtract 1 day
            $expirationDate = date(
                'Y-m-d',
                strtotime("-1 day", strtotime("+{$courseValidity} years", strtotime($approvalDate)))
            );

            
            // Insert data into the certified_members table
            DB::table('certified_members')->insert([
                'full_names' => $validatedData['full_names'],
                'national_id' => $validatedData['national_id'],
                'phone' => $validatedData['phone'],
                'email' => $validatedData['email'],
                'approvaldate' => $validatedData['approvaldate'],
                'experydate' =>  $expirationDate,
                'courseCode' => $validatedData['course_id'],
                'training_location' => $validatedData['training_station'],
                'trainerid' => $validatedData['trainerid'],
                'cert_serial' => $certSerial,                
                'status' => "valid",
                'admid' => $validatedData['admissionid'],
            ]);
            $idnumber=$validatedData['national_id'];


            // ✅ Email sending decision
            if (!isset($validatedData['send_email']) || $validatedData['send_email'] === 'yes') {
                // Default: send email if not set or explicitly yes
                $Notify = $this->Emailcert($idnumber);
            } else {
                // Do nothing if "no"
            }


            // Format expiration date in dd-mm-yyyy
            $expirationDateFormatted = date('d-m-Y', strtotime($expirationDate));

            // Prepare SMS notification
            $firstName = explode(' ', trim($validatedData['full_names']))[0];
            $message = "Dear {$firstName}, your certificate (Ref: {$certSerial}) has been issued. "
                    . "It will expire on {$expirationDateFormatted}.\n "
                    . "A copy has been sent to your email.\n"
                    . "You can use the link to download https://surl.li/wftyqe .";

            $this->SendNotification($validatedData['phone'], $message);

            // If insertion is successful, redirect back with success message
            return redirect()->back()->with('success', 'Certified member registered successfully!');
        } catch (\Exception $e) {
            // If an error occurs during insertion, redirect back with error message
            return redirect()->back()->with('error', 'Failed to register certified member. Please try again.');
        }
    }

    public function registerCertifiedStudentold(Request $request)
    {
        try {
            // Validate the form data
            $validatedData = $request->validate([
                'full_names' => 'required|string|max:255',
                'national_id' => 'required|string|max:255',
                'phone' => 'required|string|max:255',
                'email' => 'required|email|max:255',
                'approvaldate' => 'required|date',
                'training_station' => 'required',
                'trainerid' => 'required',
                'admissionid' => 'required',
                'course_id' => 'required|exists:tbl_courses,id',
            ]);

            $courseid=$validatedData['course_id'];
            $coursecode = DB::table('tbl_courses')
                    ->where('id', $courseid)
                    ->value('course_code');
                    

            // Generate cert_serial
            $certSerial = $coursecode . date('Ymd') . mt_rand(1, 500);
            // Retrieve the approval date from the validated data
            $approvalDate = $validatedData['approvaldate'];

            // Calculate the expiration date one year after the approval date
            //$expirationDate = date('Y-m-d', strtotime('+1 year', strtotime($approvalDate)));

            // Get the course validity (in years) from tbl_courses
            $courseValidity = DB::table('tbl_courses')
                ->where('id', $courseid) // adjust this based on your data
                ->value('course_validity');


            // Fallback to 1 year if null
            $courseValidity = $courseValidity ?? 1;

            // Calculate expiration date: add validity in years, then subtract 1 day
            $expirationDate = date(
                'Y-m-d',
                strtotime("-1 day", strtotime("+{$courseValidity} years", strtotime($approvalDate)))
            );

            
            // Insert data into the certified_members table
            DB::table('certified_members')->insert([
                'full_names' => $validatedData['full_names'],
                'national_id' => $validatedData['national_id'],
                'phone' => $validatedData['phone'],
                'email' => $validatedData['email'],
                'approvaldate' => $validatedData['approvaldate'],
                'experydate' =>  $expirationDate,
                'courseCode' => $validatedData['course_id'],
                'training_location' => $validatedData['training_station'],
                'trainerid' => $validatedData['trainerid'],
                'cert_serial' => $certSerial,                
                'status' => "valid",
                'admid' => $validatedData['admissionid'],
            ]);
            $idnumber=$validatedData['national_id'];


            $Notify = $this->Emailcert($idnumber);

            // If insertion is successful, redirect back with success message
            return redirect()->back()->with('success', 'Certified member registered successfully!');
        } catch (\Exception $e) {
            // If an error occurs during insertion, redirect back with error message
            return redirect()->back()->with('error', 'Failed to register certified member. Please try again.');
        }
    }

    public function oldEmailcert($idnumber)
    {
        // return $request->all();
        $id_no = $idnumber;

        $result = DB::table('certified_members')
        ->where('national_id', $id_no)
       // ->orwhere('cert_serial', $id_no)
        ->first();

        if ($result) {
            // The record exists
            // Accessing other values from the result
            $client_names = $result->full_names;
            $tarehe = $result->approvaldate;
            $email = $result->email;
            $training_location = $result->training_location;
            $cert_serial = $result->cert_serial;
            $experyDate = $result->experydate;

              
                
                $serialNo = "Serial No: $cert_serial";
                $station = "Station";
               
               
                $imagePath = public_path('img/signature.png'); // Update with the correct path to your image
                //$imagePath = public_path('img/bg/bg.jpg');

                $date=date_create($tarehe);
                $approval_date= date_format($date,"d F Y");

                $date=date_create($experyDate);
                $experyDate= date_format($date,"d F Y");

                $validity= "This certificate is valid until the $experyDate ";


                $pdf = new Fpdf('L', 'mm', 'A4');
                $pdf->AddPage('L', 'A4');

                $pdf->Image(public_path('img/bg/stjohncert.png'), 0, 0, $pdf->GetPageWidth(), $pdf->GetPageHeight(), 'PNG');
                

                // Add the image
                $pdf->Image($imagePath, 70, 145, 70, 0, 'PNG');

                $pdf->SetFont('Arial', 'B', 16);
                $pdf->SetTextColor(0, 0, 0); // Set text color (RGB values)
                $pdf->SetXY(120, 63); // Set position for text


            // Set a larger font size for $client_names
                $pdf->SetFont('Arial', 'B', 24);
            // $pdf->SetFont('Calligrapher', '', 24); // 'Calligrapher' is an example font name
                $pdf->Cell(0, 90, $client_names, 0, 1); // Add the text

                // Reset font size for the rest of the text
                $pdf->SetFont('Arial', 'B', 16);


            //$pdf->SetXY(12, 153); // Set position for text
            // $pdf->Cell(0, 10, $experyDate, 0, 1); // Add the text


            $pdf->SetXY(175, 160); // Set position for text
            $pdf->Cell(0, 10, $approval_date, 0, 1); // Add the text

            $pdf->SetFont('Arial', 'B', 12);
            $pdf->SetXY(120, 175); // Set position for text
            $pdf->Cell(0, 10, $serialNo, 0, 0); // Add the text

            $pdf->SetFont('Arial', 'i', 8);
            $pdf->SetXY(20, 180); // Set position for text
            $pdf->Cell(0, 5, $validity, 0, 0); // Add the text
            
            
            // Define the directory path to save the PDF
        $pdfDirectory = storage_path('app/temp');
            
        // Ensure the directory exists, if not, create it
        File::ensureDirectoryExists($pdfDirectory);

        // Define the file path to save the PDF
        $pdfFilePath = $pdfDirectory . '/Training_certification.pdf';

        // Save the PDF to the file path
        $pdf->Output($pdfFilePath, 'F');

        // Send the PDF as an attachment in an email
        Mail::to($email)->send(new CertificationEmail($pdfFilePath, $client_names));

        // Delete the temporary PDF file
        unlink($pdfFilePath);
           // exit;
           
     }   
    }

    public function Emailcert($idnumber)
    {
        // return $request->all();
        $id_no = $idnumber;
        $today = Carbon::today();

        $result = DB::table('certified_members')
        ->where('national_id', $id_no)
       // ->orwhere('cert_serial', $id_no)
        ->first();  

        $result = DB::table('certified_members')
        ->join('tbl_courses', 'certified_members.courseCode', '=', 'tbl_courses.id')
        ->join('tbl_training_stations', 'certified_members.training_location', '=', 'tbl_training_stations.station_name') // Add this join
        ->select(
            'certified_members.*',
            'tbl_courses.course_name',
            'tbl_courses.course_duration',
            'tbl_training_stations.location' // Select the training station name
        )
        ->where(function ($query) use ($id_no) {
            $query->where('certified_members.national_id', $id_no)
                ->orWhere('certified_members.cert_serial', $id_no);
        })
        ->whereDate('certified_members.experydate', '>=', $today)
        ->orderBy('certified_members.experydate', 'desc') // Prioritize most recent valid
        ->first();

        if ($result) {
            // The record exists
            // Accessing other values from the result
            $client_names = $result->full_names;
            $tarehe = $result->approvaldate;
            $experyDate = $result->experydate;
            $email = $result->email;
            $TrainingStation = $result->training_location;
            $serialNo = $result->cert_serial; 
            //$Coursename = "First Aid At Work"; 
            $Coursename = $result->course_name;                  
            $TrainingCounty = "County Name";
            $TrainingCounty = $result->location;
            //$TrainerId = "TRNID123456";
            $TrainerId = $result->trainerid;

              
                
                //$serialNo = "Serial No: $cert_serial";
                $station = "Station";
               
               //start
                $imagePath = public_path('img/signature.png'); // Update with the correct path to your image
                $priorsignature = public_path('img/prior.png'); // Update with the correct path to your image
                //$imagePath = public_path('img/bg/bg.jpg');

                $date=date_create($tarehe);
                $approval_date= date_format($date,"d F Y");

                $date=date_create($experyDate);
                $experyDate= date_format($date,"d F Y");

                $validity= "This certificate is valid until the $experyDate ";

                $pdf = new Fpdf('L', 'mm', 'A4');
                $pdf->AddPage('P', 'A4');
                $pdf->Image(public_path('img/bg/certificatedesign.png'), 0, 0, $pdf->GetPageWidth(), $pdf->GetPageHeight(), 'PNG');   

                $pdf->SetFont('Arial', 'B', 16);
                $pdf->SetTextColor(0, 0, 0); // Set text color (RGB values)
                $pdf->SetXY(60, 45); // Set position for text

                    // Set a larger font size for $client_names
                $pdf->SetFont('Arial', 'B', 24);
                // Get the width of the text
                $textWidth = $pdf->GetStringWidth($client_names);
                // Calculate the X position to center the text
                $x = ($pdf->GetPageWidth() - $textWidth) / 2;
                // Set the position for the text to be centered
                $pdf->SetX($x);
                // Add the text (this will now be centered horizontally on the page)
                $pdf->Cell(20, 100, $client_names, 0, 1);

                // course name
                $pdf->SetFont('Arial', 'B', 16);
                $pdf->SetTextColor(0, 0, 0); // Set text color (RGB values)
                $pdf->SetXY(60, 95); // Set position for text

                // Set a larger font size for $client_names
                $pdf->SetFont('Arial', 'B', 24);
                // Get the width of the text
                $textWidth = $pdf->GetStringWidth($Coursename);
                // Calculate the X position to center the text
                $x = ($pdf->GetPageWidth() - $textWidth) / 2;
                // Set the position for the text to be centered
                $pdf->SetX($x);
                // Add the text (this will now be centered horizontally on the page)
                $pdf->Cell(20, 100, $Coursename, 0, 1);

                //serial Number
                $pdf->SetFont('Arial', 'B', 14);
                $pdf->SetXY(35, 157); // Set position for text
                $pdf->Cell(0, 10, $serialNo, 0, 0); // Add the text


                // Reset font size for the rest of the text
                $pdf->SetFont('Arial', 'B', 14);
                $pdf->SetXY(35, 178); // Set position for text
                $pdf->Cell(0, 10, $approval_date, 0, 1); // Add the text
                $pdf->SetXY(35, 197); // Set position for text
                $pdf->Cell(0, 10, $experyDate, 0, 1); // Add the text
        

                

                // Reset font size for the rest of the text
                $pdf->SetFont('Arial', 'B', 14);
                $pdf->SetXY(128, 157); // Set position for text
                $pdf->Cell(0, 10, $TrainingStation, 0, 1); // Add the text
                $pdf->SetXY(128, 178); // Set position for text
                $pdf->Cell(0, 10, $TrainingCounty, 0, 1); // Add the text
                $pdf->SetXY(128, 197); // Set position for text
                $pdf->Cell(0, 10, $TrainerId, 0, 1); // Add the text

                // Build the verification URL
                $verifyUrl = "https://verify.stjohnkenya.org/confirmation.php?ref=" . urlencode($serialNo);

                // Define where to save the QR image
                $qrImagePath = storage_path('app/temp/qr/' . $serialNo . '_qr.png');

                // Ensure the directory exists
                File::ensureDirectoryExists(storage_path('app/temp/qr'));

                // Generate and save the QR image using Google Chart API
                $qrImagePath = storage_path("app/temp/qr/{$serialNo}_qr.png");
                file_put_contents($qrImagePath, file_get_contents(
                    'https://api.qrserver.com/v1/create-qr-code/?size=150x150&data=' . urlencode($verifyUrl)
                ));

                // After adding all your PDF content
                $pdf->Image($qrImagePath, 93, 230, 20, 0, 'PNG'); // Adjust position if needed
               

                // Add the image
                $pdf->Image($imagePath, 35, 220, 47, 0, 'PNG');
                $pdf->Image($priorsignature, 126, 230, 47, 0, 'PNG');

                $pdf->SetFont('Arial', 'i', 8);
                $pdf->SetXY(70, 270); // Set position for text
                $pdf->Cell(0, 5, $validity, 0, 0); // Add the text
            
                // Define the directory path to save the PDF
                $pdfDirectory = storage_path('app/temp');
            
                // Ensure the directory exists, if not, create it
                File::ensureDirectoryExists($pdfDirectory);

                // Define the file path to save the PDF
                //$pdfFilePath = $pdfDirectory . '/Training_certification.pdf';
                $pdfFilePath = $pdfDirectory . '/' . $serialNo . '_Training_certification.pdf';

            // Save the PDF to the file path
            $pdf->Output($pdfFilePath, 'F');

            // Send the PDF as an attachment in an email
            Mail::to($email)->send(new CertificationEmail($pdfFilePath, $client_names));

            // Delete the temporary PDF file
           // unlink($pdfFilePath);
            // After saving PDF
            unlink($qrImagePath); // Clean up
            // exit;
            
        }   
    }

    public function receiptbook()
    {


        $studentnames="Elizabeth Sabore";
        $date=now();
        $paymentAmount="6500";

        $studentmail="briananikayi@yopmail.com";

        $data=[
           'studentnames' => $studentnames,
           'date' => $date,
           'paymentAmount' => $paymentAmount,
           // 'trainingStations' => $trainingStations,
            
        ];

        //return view('receipt.receipttemplete')->with($data);
        $pdf = Pdf::loadView('receipt.receipttemplete', $data);
 
        //return $pdf->download(); 
        return $pdf->stream(); 

        Mail::to($studentmail)->cc(['briananikayi@outlook.com'])->send(new ReceiptMail($pdf,$studentnames));
        return response()->json(['status' => 'success', 'message' => 'Report has been sent successfully.']);
    }

    public function newreceiptbook()
    {


        $studentnames="Elizabeth Sabore";
        $date=now();
        $paymentAmount="6500";

        $studentmail="briananikayi@yopmail.com";

        $data=[
           'studentnames' => $studentnames,
           'date' => $date,
           'paymentAmount' => $paymentAmount,
           // 'trainingStations' => $trainingStations,
            
        ];

        //return view('receipt.newreceipttemplete')->with($data);
        $pdf = Pdf::loadView('receipt.newreceipttemplete', $data);
 
        //return $pdf->download(); 
        return $pdf->stream(); 

        Mail::to($studentmail)->cc(['briananikayi@outlook.com'])->send(new ReceiptMail($pdf,$studentnames));
        return response()->json(['status' => 'success', 'message' => 'Report has been sent successfully.']);
    }

    function updateExpiredStatus() {
        // Get the current date
        $currentDate = Carbon::now()->toDateString(); // Format as Y-m-d
    
        // Update the status to 'expired' where expirydate is in the past and status is not already 'expired'
        DB::table('certified_members')
            ->where('experydate', '<', $currentDate)
            ->where('status', '!=', 'expired')
            ->update(['status' => 'expired']);

            return response()->json(['message' => 'Expired statuses updated successfully']);
    }

    public function resendCertificate(Request $request, $cert_serial)
    {
        $today = now()->toDateString();

        // Fetch the certificate record
        $result = DB::table('certified_members')
            ->join('tbl_courses', 'certified_members.courseCode', '=', 'tbl_courses.id')
            ->join('tbl_training_stations', 'certified_members.training_location', '=', 'tbl_training_stations.station_name')
            ->select(
                'certified_members.*',
                'tbl_courses.course_name',
                'tbl_courses.course_duration',
                'tbl_training_stations.location'
            )
            ->where('certified_members.cert_serial', $cert_serial)
            ->whereDate('certified_members.experydate', '>=', $today)
            ->orderBy('certified_members.experydate', 'desc')
            ->first();

        if (!$result) {
            return response()->json([
                'status' => 'error',
                'message' => 'Certificate not found or expired.'
            ], 404);
        }

         if ($result) {
            // The record exists
            // Accessing other values from the result
                $client_names = $result->full_names;
                $tarehe = $result->approvaldate;
                $experyDate = $result->experydate;
                $email = $result->email;
                $TrainingStation = $result->training_location;
                $serialNo = $result->cert_serial;           
                $Coursename = $result->course_name;
                $TrainingCounty = $result->location;
                $TrainerId = $result->trainerid;
               
               //start
                $imagePath = public_path('img/signature.png'); // Update with the correct path to your image
                $priorsignature = public_path('img/prior.png'); // Update with the correct path to your image
                //$imagePath = public_path('img/bg/bg.jpg');

                $date=date_create($tarehe);
                $approval_date= date_format($date,"d F Y");

                $date=date_create($experyDate);
                $experyDate= date_format($date,"d F Y");

                $validity= "This certificate is valid until the $experyDate ";

                $pdf = new Fpdf('L', 'mm', 'A4');
                $pdf->AddPage('P', 'A4');
                $pdf->Image(public_path('img/bg/certificatedesign.png'), 0, 0, $pdf->GetPageWidth(), $pdf->GetPageHeight(), 'PNG');   

                $pdf->SetFont('Arial', 'B', 16);
                $pdf->SetTextColor(0, 0, 0); // Set text color (RGB values)
                $pdf->SetXY(60, 45); // Set position for text

                    // Set a larger font size for $client_names
                $pdf->SetFont('Arial', 'B', 24);
                // Get the width of the text
                $textWidth = $pdf->GetStringWidth($client_names);
                // Calculate the X position to center the text
                $x = ($pdf->GetPageWidth() - $textWidth) / 2;
                // Set the position for the text to be centered
                $pdf->SetX($x);
                // Add the text (this will now be centered horizontally on the page)
                $pdf->Cell(20, 100, $client_names, 0, 1);

                // course name
                $pdf->SetFont('Arial', 'B', 16);
                $pdf->SetTextColor(0, 0, 0); // Set text color (RGB values)
                $pdf->SetXY(60, 95); // Set position for text

                // Set a larger font size for $client_names
                $pdf->SetFont('Arial', 'B', 24);
                // Get the width of the text
                $textWidth = $pdf->GetStringWidth($Coursename);
                // Calculate the X position to center the text
                $x = ($pdf->GetPageWidth() - $textWidth) / 2;
                // Set the position for the text to be centered
                $pdf->SetX($x);
                // Add the text (this will now be centered horizontally on the page)
                $pdf->Cell(20, 100, $Coursename, 0, 1);

                //serial Number
                $pdf->SetFont('Arial', 'B', 14);
                $pdf->SetXY(35, 157); // Set position for text
                $pdf->Cell(0, 10, $serialNo, 0, 0); // Add the text


                // Reset font size for the rest of the text
                $pdf->SetFont('Arial', 'B', 14);
                $pdf->SetXY(35, 178); // Set position for text
                $pdf->Cell(0, 10, $approval_date, 0, 1); // Add the text
                $pdf->SetXY(35, 197); // Set position for text
                $pdf->Cell(0, 10, $experyDate, 0, 1); // Add the text
                        

                // Reset font size for the rest of the text
                $pdf->SetFont('Arial', 'B', 14);
                $pdf->SetXY(128, 157); // Set position for text
                $pdf->Cell(0, 10, $TrainingStation, 0, 1); // Add the text
                $pdf->SetXY(128, 178); // Set position for text
                $pdf->Cell(0, 10, $TrainingCounty, 0, 1); // Add the text
                $pdf->SetXY(128, 197); // Set position for text
                $pdf->Cell(0, 10, $TrainerId, 0, 1); // Add the text

                // Build the verification URL
                $verifyUrl = "https://verify.stjohnkenya.org/confirmation.php?ref=" . urlencode($serialNo);

                // Define where to save the QR image
                $qrImagePath = storage_path('app/temp/qr/' . $serialNo . '_qr.png');

                // Ensure the directory exists
                File::ensureDirectoryExists(storage_path('app/temp/qr'));

                // Generate and save the QR image using Google Chart API
                $qrImagePath = storage_path("app/temp/qr/{$serialNo}_qr.png");
                file_put_contents($qrImagePath, file_get_contents(
                    'https://api.qrserver.com/v1/create-qr-code/?size=150x150&data=' . urlencode($verifyUrl)
                ));

                // After adding all your PDF content
                $pdf->Image($qrImagePath, 93, 230, 20, 0, 'PNG'); // Adjust position if needed
               

                // Add the image
                $pdf->Image($imagePath, 35, 220, 47, 0, 'PNG');
                $pdf->Image($priorsignature, 126, 230, 47, 0, 'PNG');

                $pdf->SetFont('Arial', 'i', 8);
                $pdf->SetXY(70, 270); // Set position for text
                $pdf->Cell(0, 5, $validity, 0, 0); // Add the text
            
                // Define the directory path to save the PDF
                $pdfDirectory = storage_path('app/temp');
            
                // Ensure the directory exists, if not, create it
                File::ensureDirectoryExists($pdfDirectory);

                // Define the file path to save the PDF
                //$pdfFilePath = $pdfDirectory . '/Training_certification.pdf';
                $pdfFilePath = $pdfDirectory . '/' . $serialNo . '_Training_certification.pdf';

                // Save the PDF to the file path
                $pdf->Output($pdfFilePath, 'F');

                // Send the PDF as an attachment in an email
                Mail::to($email)->send(new CertificationEmail($pdfFilePath, $client_names));

                // Delete the temporary PDF file
                unlink($pdfFilePath);
                // After saving PDF
                unlink($qrImagePath); // Clean up
                // exit;
            
        } 


        return response()->json([
            'status' => 'success',
            'message' => 'Certificate resent successfully!',
            'data' => $result
        ]);
    }

    
}
