<?php session_start();

class StudentController {

    private $con;

    function __construct(){
        include_once("../../includes/DB/Connection.php");
		$db = new Connection();
		$this->con = $db->connect();
    }

    public function viewPendingRegistrants($request){

        $logged_user_id = $_SESSION['log_session']['user_id']; // counselor
        $logged_user_role_id = $_SESSION['log_session']['user_role_id']; // counselor.
        $logged_user_camp_id = $_SESSION['log_session']['campus_id'];
        $logged_user_bran_id = $_SESSION['log_session']['branch_id'];

        $lead_status = 6; // Completed - Ready to register as a student

        $sql_viewPendingRegistrants = $this->con->query("SELECT 
            l.lead_id,
            CONCAT(l.lead_first_name, ' ', l.lead_last_name) AS registrant_name,
            l.lead_phone_1,
            l.lead_email,
            l.lead_source,
            cam.campus_name,
            CONCAT(l.lead_created_date, ' ', l.lead_created_time) AS lead_created_date_time,
            (SELECT IFNULL(n.note_description, '')
                FROM note n
                WHERE n.lead_id = l.lead_id
                ORDER BY n.note_id DESC
                LIMIT 1) AS latest_note_description,
            l.lead_status
        FROM lead l
            INNER JOIN campus cam ON l.campus_id = cam.campus_id
            INNER JOIN branch bran ON l.branch_id = bran.branch_id
            INNER JOIN user u ON l.user_id = u.user_id
            INNER JOIN user_role ur ON u.user_role_id = ur.user_role_id
            WHERE 
                u.user_id = '$logged_user_id' 
                AND u.user_role_id = '$logged_user_role_id'
                AND l.campus_id = '$logged_user_camp_id'
                AND l.branch_id = '$logged_user_bran_id'
                AND l.lead_status = '$lead_status'
            ORDER BY l.lead_id DESC");

        $data = [];
        if($sql_viewPendingRegistrants->num_rows > 0){

            while($row = $sql_viewPendingRegistrants->fetch_assoc()){
                $data[] = $row;
            }

            return ['status' => 1, 'message' => $data];

        }else{
            return ['status' => 0, 'message' => 'No data available!'];
        }

    }

    public function viewRegistrant($request){
        
        if(isset($request['lead_id']) && !empty($request['lead_id'])){

            $lead_id = trim($request['lead_id']);
            $logged_user_id = $_SESSION['log_session']['user_id'];
            $logged_user_role_id = $_SESSION['log_session']['user_role_id'];
            $logged_user_camp_id = $_SESSION['log_session']['campus_id'];
            $logged_user_bran_id = $_SESSION['log_session']['branch_id'];

            $lead_status = 6; // Completed - Ready to register as a student

            $sql_viewLead = $this->con->query("SELECT 
                l.lead_id,
                l.lead_first_name,
                l.lead_last_name,
                l.lead_phone_1,
                l.lead_phone_2,
                l.lead_email,
                l.lead_address,
                l.lead_source,
                l.lead_pref_lang,
                cam.campus_name,
                bran.branch_name,
                CONCAT(u.user_first_name, ' ', u.user_last_name) AS assigned_counselor_name,
                CONCAT(l.lead_created_date, ' ', l.lead_created_time) AS lead_created_date_time,
                CONCAT(l.lead_updated_date, ' ', l.lead_updated_time) AS lead_updated_date_time,
                (SELECT IFNULL(CONCAT('[',GROUP_CONCAT(JSON_OBJECT('note_id', n.note_id, 'note_description', n.note_description, 'note_created_date_time', CONCAT(n.note_created_date, ' ', n.note_created_time))),']'),'[]')
                    FROM note n
                    WHERE n.lead_id = l.lead_id) AS all_notes,
                l.lead_status
            FROM lead l
                INNER JOIN campus cam ON l.campus_id = cam.campus_id
                INNER JOIN branch bran ON l.branch_id = bran.branch_id
                INNER JOIN user u ON l.user_id = u.user_id
                INNER JOIN user_role ur ON u.user_role_id = ur.user_role_id
                WHERE u.user_id = '$logged_user_id' 
                    AND u.user_role_id = '$logged_user_role_id'
                    AND l.campus_id = '$logged_user_camp_id'
                    AND l.branch_id = '$logged_user_bran_id'
                    AND l.lead_id = '$lead_id'
                    AND l.lead_status = '$lead_status'
                ORDER BY l.lead_id DESC");

            $data = [];
            if($sql_viewLead->num_rows > 0){

                while($row = $sql_viewLead->fetch_assoc()){
                    $data[] = $row;
                }

                return ['status' => 1, 'message' => $data];

            }else{
                return ['status' => 0, 'message' => 'No data available!'];
            }
        }
    }

    public function createStudentAccount($request){

        if(isset($request['lead_id']) && !empty($request['lead_id'])){

            if(!empty($request['entered_student_note']) && !empty($request['student_pass'])){
                
                $lead_id = trim($request['lead_id']);
                $student_note = trim($request['entered_student_note']);
                $student_pass = $request['student_pass'];

                $logged_user_id = $_SESSION['log_session']['user_id'];
                $logged_user_role_id = $_SESSION['log_session']['user_role_id'];
                $logged_user_camp_id = $_SESSION['log_session']['campus_id'];
                $logged_user_bran_id = $_SESSION['log_session']['branch_id'];

                $lead_status = 6; // Completed - Ready to register as a student

                $sql_get_lead = $this->con->query("SELECT 
                    l.lead_id,
                    l.lead_first_name,
                    l.lead_last_name,
                    l.lead_phone_1,
                    l.lead_phone_2,
                    l.lead_email,
                    l.lead_address,
                    l.lead_pref_lang,
                    l.campus_id,
                    l.branch_id,
                    l.user_id AS counselor_id
                FROM lead l
                    INNER JOIN user u ON l.user_id = u.user_id
                    INNER JOIN user_role ur ON u.user_role_id = ur.user_role_id
                    WHERE u.user_id = '$logged_user_id' 
                        AND u.user_role_id = '$logged_user_role_id'
                        AND l.campus_id = '$logged_user_camp_id'
                        AND l.branch_id = '$logged_user_bran_id'
                        AND l.lead_id = '$lead_id'
                        AND l.lead_status = '$lead_status'
                    ORDER BY l.lead_id DESC");

                if($sql_get_lead->num_rows > 0){
                    
                    $lead_data = [];
                    while($row = $sql_get_lead->fetch_assoc()){
                        $lead_data = $row;
                    }

                    $student_first_name = $lead_data['lead_first_name'];
                    $student_last_name = $lead_data['lead_last_name'];
                    $student_gender = '';
                    $student_nic_passport = '';
                    $student_phone_1 = $lead_data['lead_phone_1'];
                    $student_phone_2 = $lead_data['lead_phone_2'];
                    $student_email = $lead_data['lead_email'];
                    $student_address = $lead_data['lead_address'];
                    $student_password_hashed = password_hash($student_pass, PASSWORD_BCRYPT, ["COST"=> 8]);
                    $student_pref_lang = $lead_data['lead_pref_lang'];
                    $student_note = $student_note;
                    $student_status = 1; // Registered as Student
  
                    date_default_timezone_set('Asia/Colombo');
                    $server_date = date("Y-m-d");
                    $server_time = date("H:i:s");

                    $lead_id = $lead_data['lead_id'];
                    $campus_id = $lead_data['campus_id'];
                    $branch_id = $lead_data['branch_id'];
                    $student_role_id = 5; // student role id
                    $counselor_id = $lead_data['counselor_id'];

                    
                    // Prepare and bind parameters for the INSERT query
                    $stmt_insert_student = $this->con->prepare("INSERT INTO student (student_first_name, student_last_name, student_gender, student_nic_or_passport_id, student_phone_1, student_phone_2, student_email, student_address, student_password, student_pref_lang, student_note, student_status, student_created_date, student_created_time, student_updated_date, student_updated_time, lead_id, campus_id, branch_id, user_role_id, user_id) 
                    VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                    
                    $stmt_insert_student->bind_param("sssssssssssissssiiiii", $student_first_name, $student_last_name, $student_gender, $student_nic_passport, $student_phone_1, $student_phone_2, $student_email, $student_address, $student_password_hashed, $student_pref_lang, $student_note, $student_status, $server_date, $server_time, $server_date, $server_time, $lead_id, $campus_id, $branch_id, $student_role_id, $counselor_id);

                    // Execute the INSERT query
                    $stmt_insert_student->execute();

                    // Check if the INSERT was successful
                    if ($stmt_insert_student->affected_rows > 0) {

                        // Get the last inserted ID (student_id)
                        $last_student_id = $this->con->insert_id;


                        // Student's document empty records insertion, when student account create
                        // $return_document_record = $this->insert_document_record($last_student_id, $server_date, $server_time);
                        
                        $return_lead_status = $this->update_lead_status($lead_id, $server_date, $server_time, $student_email);
                        
                        if($return_lead_status['status'] == 1){
                            return $return_lead_status;

                        }else{
                            return $return_lead_status;

                        }


                    }else {
                        return ['status' => 0, 'message' => 'Error. Student Account not Created!'];
                        
                    }


                }else{
                    return ['status' => 0, 'message' => 'Lead not available. Account Creation Failed!.'];

                }



            }else{
                return ['status' => 0, 'message' => 'Both student note and password are required!'];

            }

        }else{
            return ['status' => 0, 'message' => 'Invalid Request. Please Try Again!'];

        }

    }

    public function viewStudentsByCounselor(){

        $logged_user_id = $_SESSION['log_session']['user_id'];
        $logged_user_camp_id = $_SESSION['log_session']['campus_id'];
        $logged_user_bran_id = $_SESSION['log_session']['branch_id'];

        $sql_all_students = $this->con->query("SELECT 
            s.student_id,
            CONCAT(s.student_first_name, ' ', s.student_last_name) AS student_name,
            s.student_nic_or_passport_id,
            s.student_phone_1,
            s.student_email,
            cam.campus_name,
            CONCAT(s.student_created_date, ' ', s.student_created_time) AS student_created_date_time,
            s.student_status
        FROM student s
            INNER JOIN campus cam ON s.campus_id = cam.campus_id
            INNER JOIN branch bran ON s.branch_id = bran.branch_id
            WHERE s.user_id = '$logged_user_id' AND s.campus_id = '$logged_user_camp_id' AND s.branch_id = '$logged_user_bran_id'
            ORDER BY s.student_id DESC");


        if($sql_all_students->num_rows > 0){

            $data = [];
            while($row = $sql_all_students -> fetch_assoc()){
                $data[] = $row;
            }

            return ['status' => 1, 'message' => $data];

        }else{
            return ['status' => 0, 'message' => 'No data available!'];

        }

    }

    public function viewStudent($request){

        $student_id = trim($request['student_id']);

        if(isset($student_id) && !empty($student_id)){
            
            $student_info = $this->get_student_info($student_id);
            $education_info = $this->get_education_info($student_id);
            $work_info = $this->get_work_info($student_id);
            $parent_info = $this->get_parent_info($student_id);
            $portfolio_info = $this->get_portfolio_info($student_id);

            $student_profile_array = ['student_info'=>$student_info, 'education_info' => $education_info, 'work_info' => $work_info, 'parent_info' => $parent_info, 'portfolio_info' => $portfolio_info];
            return ['status' => 1, 'message' => $student_profile_array];
            
        }else {
            
            return ['status' => 0, 'message' => 'Invalid Request!']; 
        }
    }

    private function get_student_info($student_id){

            $logged_user_id = $_SESSION['log_session']['user_id'];
            $logged_user_camp_id = $_SESSION['log_session']['campus_id'];
            $logged_user_bran_id = $_SESSION['log_session']['branch_id'];

            $sql_student_info = $this->con->query("SELECT
            s.student_id,
            s.student_first_name,
            s.student_last_name,
            s.student_gender,
            s.student_nic_or_passport_id,
            s.student_pref_lang,
            c.campus_name,
            b.branch_name,
            s.student_phone_1,
            s.student_phone_2,
            s.student_email,
            s.student_address,
            s.student_note,
            s.student_status
        FROM student s
            INNER JOIN campus c ON s.campus_id = c.campus_id
            INNER JOIN branch b ON s.branch_id = b.branch_id
            WHERE s.campus_id = '$logged_user_camp_id' AND s.branch_id = '$logged_user_bran_id' AND s.user_id = '$logged_user_id' AND s.student_id = '$student_id'");

            if($sql_student_info -> num_rows > 0){

                $student_data = [];
                while($row = $sql_student_info->fetch_assoc()){
                    $student_data[] = $row;
                }

                return ['status' => 1, 'message' => $student_data];

            }else{
                return ['status' => 0, 'message' => 'No data available!'];

            }
    
    }

    // Create a public method to access the private method
    public function access_education_info($request) {
        return $this->get_education_info($request['student_id']);
    }

    private function get_education_info($student_id){

        $logged_user_id = $_SESSION['log_session']['user_id'];
        $logged_user_camp_id = $_SESSION['log_session']['campus_id'];
        $logged_user_bran_id = $_SESSION['log_session']['branch_id'];

        $stmt_education_info = $this->con->prepare("SELECT
        edu.education_id,
        edu.education_type_id,
        et.education_type,
        edu.education_qual_id,
        eq.education_qual,
        edu.education_additional_info,
        edu.education_institute,
        edu.education_start_date,
        edu.education_end_date,
        edu.education_current_study,
        edu.education_doc_path,
        edu.education_doc_status,
        edu.student_id
        FROM education edu
        INNER JOIN education_type et ON edu.education_type_id = et.education_type_id
        INNER JOIN education_qual eq ON edu.education_qual_id = eq.education_qual_id
        INNER JOIN student s ON edu.student_id = s.student_id
            WHERE s.campus_id = ? AND s.branch_id = ? AND s.user_id = ? AND s.student_id = ?
            ORDER BY edu.education_id");

        // Bind parameters
        $stmt_education_info->bind_param("iiii", $logged_user_camp_id, $logged_user_bran_id, $logged_user_id, $student_id);

        // Execute the statement
        $stmt_education_info->execute();

        // Get the result
        $result = $stmt_education_info->get_result();

        $education_info = [];
        if($result->num_rows > 0){

            while($row = $result->fetch_assoc()){
                $education_info[] = $row;
            }
            return ['status' => 1, 'message' => $education_info];

        }else{
            
            return ['status' => 0, 'message' => $education_info];
        }

    }

    private function get_work_info($student_id){

        $logged_user_id = $_SESSION['log_session']['user_id'];
        $logged_user_camp_id = $_SESSION['log_session']['campus_id'];
        $logged_user_bran_id = $_SESSION['log_session']['branch_id'];

        $stmt_work_info = $this->con->prepare("SELECT
        wrk.work_id,
        wrk.work_job_title,
        wrk.work_organization,
        wrk.work_start_date,
        wrk.work_end_date,
        wrk.work_current_work,
        wrk.work_doc_status,
        wrk.work_additional_info,
        wrk.work_doc_path
        FROM work wrk
        INNER JOIN student s ON wrk.student_id = s.student_id
        WHERE s.campus_id = ? AND s.branch_id = ? AND s.user_id = ? AND s.student_id = ?
        ORDER BY wrk.work_id");

        // Bind parameters
        $stmt_work_info->bind_param("iiii", $logged_user_camp_id, $logged_user_bran_id, $logged_user_id, $student_id);

        // Execute the statement
        $stmt_work_info->execute();

        // Get the result
        $result = $stmt_work_info->get_result();

        $work_info = [];
        if($result->num_rows > 0){

            while($row = $result->fetch_assoc()){
                $work_info[] = $row;
            }
            return ['status' => 1, 'message' => $work_info];

        }else{
            
            return ['status' => 0, 'message' => $work_info];
        }

    }

    private function get_parent_info($student_id){

        $logged_user_id = $_SESSION['log_session']['user_id'];
        $logged_user_camp_id = $_SESSION['log_session']['campus_id'];
        $logged_user_bran_id = $_SESSION['log_session']['branch_id'];

        $stmt_parent_info = $this->con->prepare("SELECT
        pi.personal_info_id,
        pi.student_id,
        pi.father_name,
        pi.father_occupation,
        pi.father_organization,
        pi.father_phone_1,
        pi.father_phone_2,
        pi.mother_name,
        pi.mother_occupation,
        pi.mother_organization,
        pi.mother_phone_1,
        pi.mother_phone_2,
        pi.student_id
        FROM student s
        INNER JOIN personal_info pi ON s.student_id = pi.student_id
        WHERE s.campus_id = ? AND s.branch_id = ? AND s.user_id = ? AND s.student_id = ?");

        // Bind parameters
        $stmt_parent_info->bind_param("iiii", $logged_user_camp_id, $logged_user_bran_id, $logged_user_id, $student_id);

        // Execute the statement
        $stmt_parent_info->execute();

        // Get the result
        $result = $stmt_parent_info->get_result();

        $parent_info = [];
        if($result->num_rows > 0){

            while($row = $result->fetch_assoc()){
                $parent_info[] = $row;
            }
            return ['status' => 1, 'message' => $parent_info];

        }else{
            
            return ['status' => 0, 'message' => $parent_info];
        }

    }

    // Create a public method to access the private method
    public function access_portfolio_info($request) {
        return $this->get_portfolio_info($request['student_id']);
    }

    private function get_portfolio_info($student_id){

        $logged_user_id = $_SESSION['log_session']['user_id'];
        $logged_user_camp_id = $_SESSION['log_session']['campus_id'];
        $logged_user_bran_id = $_SESSION['log_session']['branch_id'];
        
        $stmt_portfolio_info = $this->con->prepare("SELECT
        p.portfolio_id,
        p.portfolio_question,
        p.portfolio_answer,
        p.student_id
        FROM portfolio p
        INNER JOIN student s ON p.student_id = s.student_id
        WHERE s.campus_id = ? AND s.branch_id = ? AND s.user_id = ? AND s.student_id = ?");

        // Bind parameters
        $stmt_portfolio_info->bind_param("iiii", $logged_user_camp_id, $logged_user_bran_id, $logged_user_id, $student_id);

        // Execute the statement
        $stmt_portfolio_info->execute();

        // Get the result
        $result = $stmt_portfolio_info->get_result();

        $portfolio_info = [];
        if($result->num_rows > 0){

            while($row = $result->fetch_assoc()){
                $portfolio_info[] = $row;
            }
            return ['status' => 1, 'message' => $portfolio_info];

        }else{
            
            return ['status' => 0, 'message' => $portfolio_info];
        }

    }

    private function update_lead_status($lead_id, $server_date, $server_time, $student_email){

        $lead_id = $lead_id;
        $server_date = $server_date;
        $server_time = $server_time;
        $lead_registered_status = 7;
        $student_email = $student_email;

        // Prepare and bind parameters for the UPDATE query
        $stmt_update_lead = $this->con->prepare("UPDATE lead l SET l.lead_status = ?, l.lead_updated_date = ?, l.lead_updated_time = ? WHERE l.lead_id = ?");
        $stmt_update_lead->bind_param("issi", $lead_registered_status, $server_date, $server_time , $lead_id);

        // Execute the UPDATE query
        $stmt_update_lead->execute();

        // Check the number of affected rows
        if ($stmt_update_lead->affected_rows > 0) {

            $student_acc_created_msg = 'Student Account for <strong>' . htmlspecialchars($student_email, ENT_QUOTES, 'UTF-8') . '</strong> created successfully!';
            return ['status' => 1, 'message' => $student_acc_created_msg];
        
        }else {
            return ['status' => 0, 'message' => 'Error. Lead Status not Updated!'];
            
        }



    }

    private function get_student_enrollment($logged_user_id, $logged_user_camp_id, $logged_user_bran_id, $student_id){

        $sql_enrollment_info = $this->con->query("SELECT
            s.student_id,
            se.student_enrollment_id,
            prog.program_id,
            prog.program_code,
            prog.program_name,
            prog.program_cost,
            prog.program_start_date,
            prog.program_end_date,
            CONCAT(se.enrollment_created_date, ' ', se.enrollment_created_time) AS enrolled_date_time,
            se.enrollment_status
        FROM student s
            INNER JOIN student_enrollment se ON se.student_id = s.student_id
            INNER JOIN program prog ON se.program_id = prog.program_id
        WHERE s.campus_id = '$logged_user_camp_id' AND s.branch_id = '$logged_user_bran_id' AND s.user_id = '$logged_user_id' AND s.student_id = '$student_id'");


        if($sql_enrollment_info -> num_rows > 0){

            $enrollment_data = [];
            while($row = $sql_enrollment_info->fetch_assoc()){
                $enrollment_data[] = $row;
            }

            return ['status' => 1, 'message' => $enrollment_data];

        }else{
            return ['status' => 0, 'message' => 'No data available!'];

        }

    }

    public function loadEducationTypes(){


        $stmt_load_edu_types = $this->con->prepare("SELECT * FROM education_type");

        // Execute the UPDATE query
        $stmt_load_edu_types->execute();

        // Get the result
        $result = $stmt_load_edu_types->get_result();

        $education_types = [];
        if($result->num_rows > 0){

            while($row = $result->fetch_assoc()){
                $education_types[] = $row;
            }
            return ['status' => 1, 'message' => $education_types];

        }else{
            
            return ['status' => 0, 'message' => $education_types];
        }

    }

    public function loadQualification($request){

        if($request['education_type_id'] != ''){

            $education_type_id = trim($request['education_type_id']);

            $stmt_load_edu_qual = $this->con->prepare("SELECT * FROM education_qual WHERE education_type_id = ?");

            $stmt_load_edu_qual->bind_param('i', $education_type_id);

            // Execute the UPDATE query
            $stmt_load_edu_qual->execute();

            // Get the result
            $result = $stmt_load_edu_qual->get_result();

            $education_qual = [];
            if($result->num_rows > 0){

                while($row = $result->fetch_assoc()){
                    $education_qual[] = $row;
                }
                return ['status' => 1, 'message' => $education_qual];

            }else{
                return ['status' => 0, 'message' => $education_qual];
            }

        }else{
            return ['status' => 0, 'message' => 'Please select an Education Type!'];
        }
        


    }

    public function submitEducationData($request, $files){

        if(isset($request['student_id']) && !empty($request['student_id'])){

            $edu_type_id = trim($request['edu_type_select']);
            $edu_qual_id = trim($request['edu_qual_select']);

            if(isset($edu_type_id) && !empty($edu_type_id) && isset($edu_qual_id) && !empty($edu_qual_id)) {

                $edu_start_date = trim($request['edu_start_date']);
                $edu_end_date = trim($request['edu_end_date']);
                $edu_cur_study_check = trim($request['edu_cur_study_check']);
                $edu_institute = trim($request['edu_institute']);
                $edu_additional_info = trim($request['edu_additional_info']);
                $student_id = trim($request['student_id']);
                $edu_doc_status = 0; // 0-N/A|1-Pending|2-Verified|3-Rejected

                date_default_timezone_set('Asia/Colombo');
                $server_date = date("Y-m-d");
                $server_time = date("H:i:s");

                $relative_file_path = ''; // path to insert to database table document path
                $target_file_path = '';
    
                // Check if the file has been uploaded
                if(isset($files['edu_document']) && $files['edu_document']['error'] === UPLOAD_ERR_OK) {

                    // File details
                    $file_name = $files['edu_document']['name']; // File name
                    $file_tmp = $files['edu_document']['tmp_name']; // Temporary file path

                    // Check if the uploaded file is a PDF
                    $file_ext = pathinfo($file_name, PATHINFO_EXTENSION);
                    if(strtolower($file_ext) !== 'pdf') {
                        // File is not a PDF, handle error as needed (e.g., show error message)
                        return ['status' => 0, 'message' => 'Please try again!. Uploaded file is not a PDF!'];

                    }

                    // Generate a unique identifier
                    $uniqueId = uniqid();

                    // Combine the original filename with the unique ID
                    $new_file_name = pathinfo($file_name, PATHINFO_FILENAME) . '_' . $uniqueId . '.' . $file_ext;


                    // Navigate up two directories to reach the project root
                    $rootPath = dirname(dirname(dirname(__FILE__)));

                    //Relative path
                    $relative_upload_path = '/student/assets/uploads/documents/education/';

                    // Define the target directory
                    $target_upload_dir = $rootPath . $relative_upload_path;

                    // Define the student's unique directory
                    $student_dir = $target_upload_dir . $student_id . '/';

                    
                    // Create the student's directory if it doesn't exist
                    if (!file_exists($student_dir)) {
                        mkdir($student_dir, 0777, true);
                    }

                    // Define the target file path
                    $target_file_path = $student_dir . $new_file_name;


                    // Move the uploaded file to the student's directory
                    if (!move_uploaded_file($file_tmp, $target_file_path)) {
                        return ['status' => 0, 'message' => 'The file upload was unsuccessful!'];
                    }


                    $edu_doc_status = 1; // 0-N/A|1-Pending|2-Verified|3-Rejected
        
                    //relative file path to insert to database
                    $relative_file_path = $relative_upload_path . $student_id . '/' . $new_file_name;

        
                }else{
                    // If no file is uploaded, set target_file_path to null
                    $target_file_path = null;
                }

                // Start a transaction
                $this->con->begin_transaction();

                try {
                        
                    $stmt_insert_education = $this->con->prepare("INSERT INTO education (education_additional_info, education_institute, education_start_date, education_end_date, education_current_study, education_doc_path, education_doc_status, education_created_date, education_created_time, education_updated_date, education_updated_time, education_type_id, education_qual_id, student_id)
                    VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

                    $stmt_insert_education->bind_param("ssssisissssiii",$edu_additional_info, $edu_institute, $edu_start_date, $edu_end_date, $edu_cur_study_check, $relative_file_path, $edu_doc_status, $server_date, $server_time, $server_date, $server_time, $edu_type_id, $edu_qual_id, $student_id);

                    // Execute the statement
                    $stmt_insert_education->execute();

                    // Commit the transaction
                    $this->con->commit();

                    return ['status' => 1, 'message' => 'Education Information Saved Successfully!'];

                } catch (Throwable $e) {

                    // Rollback the transaction if something failed
                    $this->con->rollback();

                    // Delete the uploaded file if it exists
                    if (isset($target_file_path) && file_exists($target_file_path)) {
                        unlink($target_file_path);
                    }

                    return ['status' => 0, 'message' => 'Data insertion failed: ' . $e->getMessage()];                  
                    
                }

            }else{
                return ['status' => 0, 'message' => 'Both Education Type and Qualification are required!'];
    
            }


        }else{
            return ['status' => 0, 'message' => 'Invalid Request!'];
        }
        
    }

    public function submitWorkData($request, $files){

        if(isset($request['student_id']) && !empty($request['student_id'])){

            $pe_job_title = trim($request['pe_job_title']);

            if(isset($pe_job_title) && !empty($pe_job_title)) {

                $pe_organization = trim($request['pe_organization']);
                $pe_start_date = trim($request['pe_start_date']);
                $pe_end_date = trim($request['pe_end_date']);
                $pe_current_work_check = trim($request['pe_current_work_check']);
                $pe_additional_info = trim($request['pe_additional_info']);
                $student_id = trim($request['student_id']);
                $work_doc_status = 0; // 0-N/A|1-Available

                date_default_timezone_set('Asia/Colombo');
                $server_date = date("Y-m-d");
                $server_time = date("H:i:s");

                $relative_file_path = ''; // path to insert to database table document path
                $target_file_path = '';
    
                // Check if the file has been uploaded
                if(isset($files['pe_document']) && $files['pe_document']['error'] === UPLOAD_ERR_OK) {

                    // File details
                    $file_name = $files['pe_document']['name']; // File name
                    $file_tmp = $files['pe_document']['tmp_name']; // Temporary file path

                    // Check if the uploaded file is a PDF
                    $file_ext = pathinfo($file_name, PATHINFO_EXTENSION);
                    if(strtolower($file_ext) !== 'pdf') {
                        // File is not a PDF, handle error as needed (e.g., show error message)
                        return ['status' => 0, 'message' => 'Please try again!. Uploaded file is not a PDF!'];

                    }

                    // Generate a unique identifier
                    $uniqueId = uniqid();

                    // Combine the original filename with the unique ID
                    $new_file_name = pathinfo($file_name, PATHINFO_FILENAME) . '_' . $uniqueId . '.' . $file_ext;

                    // Navigate up two directories to reach the project root
                    $rootPath = dirname(dirname(dirname(__FILE__)));

                    //Relative path
                    $relative_upload_path = '/student/assets/uploads/documents/work/';

                    // Define the target directory
                    $target_upload_dir = $rootPath . $relative_upload_path;

                    // Define the student's unique directory
                    $work_dir = $target_upload_dir . $student_id . '/';

                    
                    // Create the student's directory if it doesn't exist
                    if (!file_exists($work_dir)) {
                        mkdir($work_dir, 0777, true);
                    }

                    // Define the target file path
                    $target_file_path = $work_dir . $new_file_name;


                    // Move the uploaded file to the student's directory
                    if (!move_uploaded_file($file_tmp, $target_file_path)) {
                        return ['status' => 0, 'message' => 'The file upload was unsuccessful!'];
                    }


                    $work_doc_status = 1; // 0-N/A|1-Available
        
                    //relative file path to insert to database
                    $relative_file_path = $relative_upload_path . $student_id . '/' . $new_file_name;

        
                }else{
                    // If no file is uploaded, set target_file_path to null
                    $target_file_path = null;
                }

                // Start a transaction
                $this->con->begin_transaction();

                try {
                        
                    $stmt_insert_work = $this->con->prepare("INSERT INTO work (work_job_title, work_organization, work_start_date, work_end_date, work_current_work, work_additional_info, work_doc_path, work_doc_status, work_created_date, work_created_time, work_updated_date, work_updated_time, student_id)
                    VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)");

                    $stmt_insert_work->bind_param("ssssississssi",$pe_job_title, $pe_organization, $pe_start_date, $pe_end_date, $pe_current_work_check, $pe_additional_info, $relative_file_path, $work_doc_status, $server_date, $server_time, $server_date, $server_time, $student_id);

                    // Execute the statement
                    $stmt_insert_work->execute();

                    // Commit the transaction
                    $this->con->commit();

                    return ['status' => 1, 'message' => 'Job Information Saved Successfully!'];

                } catch (Throwable $e) {

                    // Rollback the transaction if something failed
                    $this->con->rollback();

                    // Delete the uploaded file if it exists
                    if (isset($target_file_path) && file_exists($target_file_path)) {
                        unlink($target_file_path);
                    }

                    return ['status' => 0, 'message' => 'Data insertion failed: ' . $e->getMessage()];                  
                    
                }

            }else{
                return ['status' => 0, 'message' => 'Job Title is required!'];
    
            }


        }else{
            return ['status' => 0, 'message' => 'Invalid Request!'];
        }
        
    }

    public function submitPortfolioData($request){

        $ques_title = trim($request['ques_title']);
        $ques_answer = trim($request['ques_answer']);

        if(isset($ques_title) && !empty($ques_title) && isset($ques_answer) && !empty($ques_answer)) {

            $student_id = trim($request['student_id']);
            
            date_default_timezone_set('Asia/Colombo');
            $server_date = date("Y-m-d");
            $server_time = date("H:i:s");

            // Start a transaction
            $this->con->begin_transaction();

            try {
                    
                $stmt_insert_portfolio = $this->con->prepare("INSERT INTO portfolio (portfolio_question, portfolio_answer, portfolio_created_date, portfolio_created_time, portfolio_updated_date, portfolio_updated_time, student_id)
                VALUES(?,?,?,?,?,?,?)");

                $stmt_insert_portfolio->bind_param("ssssssi",$ques_title, $ques_answer, $server_date, $server_time, $server_date, $server_time, $student_id);

                // Execute the statement
                $stmt_insert_portfolio->execute();

                // Commit the transaction
                $this->con->commit();

                return ['status' => 1, 'message' => 'Portfolio Information Saved Successfully!'];

            } catch (Throwable $e) {

                // Rollback the transaction if something failed
                $this->con->rollback();
                return ['status' => 0, 'message' => 'Data insertion failed: ' . $e->getMessage()];                  
                
            }


        }else{
            return ['status' => 0, 'message' => 'Both the Question and the Answer are required!'];
        }

    }

    public function updateEducationDocument($request, $files){
        
        $student_id = trim($request['student_id']);
        $education_id = trim($request['education_id']);

        if(isset($student_id) && !empty($student_id) && isset($education_id) && !empty($education_id)){

            // Check if file upload exists and is valid
            if (!empty($files['edu_document_update']) && $files['edu_document_update']['error'] == UPLOAD_ERR_OK) {

                // File details
                $file_name = $files['edu_document_update']['name']; // File name
                $file_tmp = $files['edu_document_update']['tmp_name']; // Temporary file path

                date_default_timezone_set('Asia/Colombo');
                $server_date = date("Y-m-d");
                $server_time = date("H:i:s");

                // Check if the uploaded file is a PDF
                $file_ext = pathinfo($file_name, PATHINFO_EXTENSION);
                if(strtolower($file_ext) !== 'pdf') {
                    // File is not a PDF, handle error as needed (e.g., show error message)
                    return ['status' => 0, 'message' => 'Please try again!. Uploaded file is not a PDF!'];

                }

                // Generate a unique identifier
                $uniqueId = uniqid();

                // Combine the original filename with the unique ID
                $new_file_name = pathinfo($file_name, PATHINFO_FILENAME) . '_' . $uniqueId . '.' . $file_ext;

                // Navigate up two directories to reach the project root
                $rootPath = dirname(dirname(dirname(__FILE__)));

                //Relative path
                $relative_upload_path = '/student/assets/uploads/documents/education/';

                // Define the target directory
                $target_upload_dir = $rootPath . $relative_upload_path;

                // Define the student's unique directory
                $education_dir = $target_upload_dir . $student_id . '/';

                
                // Create the student's directory if it doesn't exist
                if (!file_exists($education_dir)) {
                    mkdir($education_dir, 0777, true);
                }

                // Define the target file path
                $target_file_path = $education_dir . $new_file_name;


                // Move the uploaded file to the student's directory
                if (!move_uploaded_file($file_tmp, $target_file_path)) {
                    return ['status' => 0, 'message' => 'The file upload was unsuccessful!'];
                }

                $edu_doc_status = 1; // 0-N/A|1-Pending|2-Verified|3-Rejected
    
                //relative file path to insert to database
                $relative_file_path = $relative_upload_path . $student_id . '/' . $new_file_name;

                // Start a transaction
                $this->con->begin_transaction();

                try {
                        
                    $stmt_update_doc = $this->con->prepare("UPDATE education SET education_doc_path = ?, education_doc_status = ?, education_updated_date = ?, education_updated_time = ? WHERE education_id = ?");

                    $stmt_update_doc->bind_param("sissi", $relative_file_path, $edu_doc_status, $server_date, $server_time, $education_id);

                    // Execute the statement
                    $stmt_update_doc->execute();

                    // Commit the transaction
                    $this->con->commit();

                    // Check if the update was successful
                    if ($stmt_update_doc->affected_rows > 0) {
                        return ['status' => 1, 'message' => 'Document uploaded successfully!'];
                    } else {
                        return ['status' => 0, 'message' => 'No changes were made or the record does not exist.'];
                    }


                } catch (Throwable $e) {

                    // Rollback the transaction if something failed
                    $this->con->rollback();

                    // Delete the uploaded file if it exists
                    if (isset($target_file_path) && file_exists($target_file_path)) {
                        unlink($target_file_path);
                    }

                    return ['status' => 0, 'message' => 'Data insertion failed: ' . $e->getMessage()];                  
                    
                }


            }else {
                return ['status' => 0, 'message' => '<strong>No file selected.</strong> Please choose a file to upload!'];

            }



        }else{
            return ['status' => 0, 'message' => 'Invalid Request!'];

        }

    }

    public function updateWorkDocument($request, $files){

        $student_id = trim($request['student_id']);
        $work_id = trim($request['work_id']);

        if(isset($student_id) && !empty($student_id) && isset($work_id) && !empty($work_id)){

            // Check if file upload exists and is valid
            if (!empty($files['pe_document_update']) && $files['pe_document_update']['error'] == UPLOAD_ERR_OK) {

                // File details
                $file_name = $files['pe_document_update']['name']; // File name
                $file_tmp = $files['pe_document_update']['tmp_name']; // Temporary file path

                date_default_timezone_set('Asia/Colombo');
                $server_date = date("Y-m-d");
                $server_time = date("H:i:s");

                // Check if the uploaded file is a PDF
                $file_ext = pathinfo($file_name, PATHINFO_EXTENSION);
                if(strtolower($file_ext) !== 'pdf') {
                    // File is not a PDF, handle error as needed (e.g., show error message)
                    return ['status' => 0, 'message' => 'Please try again!. Uploaded file is not a PDF!'];

                }

                // Generate a unique identifier
                $uniqueId = uniqid();

                // Combine the original filename with the unique ID
                $new_file_name = pathinfo($file_name, PATHINFO_FILENAME) . '_' . $uniqueId . '.' . $file_ext;

                // Navigate up two directories to reach the project root
                $rootPath = dirname(dirname(dirname(__FILE__)));

                //Relative path
                $relative_upload_path = '/student/assets/uploads/documents/work/';

                // Define the target directory
                $target_upload_dir = $rootPath . $relative_upload_path;

                // Define the student's unique directory
                $work_dir = $target_upload_dir . $student_id . '/';

                
                // Create the student's directory if it doesn't exist
                if (!file_exists($work_dir)) {
                    mkdir($work_dir, 0777, true);
                }

                // Define the target file path
                $target_file_path = $work_dir . $new_file_name;


                // Move the uploaded file to the student's directory
                if (!move_uploaded_file($file_tmp, $target_file_path)) {
                    return ['status' => 0, 'message' => 'The file upload was unsuccessful!'];
                }

                $work_doc_status = 1; // 0-N/A|1-Available
    
                //relative file path to insert to database
                $relative_file_path = $relative_upload_path . $student_id . '/' . $new_file_name;

                // Start a transaction
                $this->con->begin_transaction();

                try {
                        
                    $stmt_update_doc = $this->con->prepare("UPDATE work SET work_doc_path = ?, work_doc_status = ?, work_updated_date = ?, work_updated_time = ? WHERE work_id = ?");

                    $stmt_update_doc->bind_param("sissi", $relative_file_path, $work_doc_status, $server_date, $server_time, $work_id);

                    // Execute the statement
                    $stmt_update_doc->execute();

                    // Commit the transaction
                    $this->con->commit();

                    // Check if the update was successful
                    if ($stmt_update_doc->affected_rows > 0) {
                        return ['status' => 1, 'message' => 'Document uploaded successfully!'];
                    } else {
                        return ['status' => 0, 'message' => 'No changes were made or the record does not exist.'];
                    }


                } catch (Throwable $e) {

                    // Rollback the transaction if something failed
                    $this->con->rollback();

                    // Delete the uploaded file if it exists
                    if (isset($target_file_path) && file_exists($target_file_path)) {
                        unlink($target_file_path);
                    }

                    return ['status' => 0, 'message' => 'Data insertion failed: ' . $e->getMessage()];                  
                    
                }


            }else {
                return ['status' => 0, 'message' => '<strong>No file selected.</strong> Please choose a file to upload!'];

            }



        }else{
            return ['status' => 0, 'message' => 'Invalid Request!'];

        }
    }

    public function submitParentData($request){

        $parent_f_name = trim($request['parent_f_name']);
        $parent_f_occupation = trim($request['parent_f_occupation']);
        $parent_f_organization = trim($request['parent_f_organization']);
        $parent_f_phone_1 = trim($request['parent_f_phone_1']);
        $parent_f_phone_2 = trim($request['parent_f_phone_2']);
        $parent_m_name = trim($request['parent_m_name']);
        $parent_m_occupation = trim($request['parent_m_occupation']);
        $parent_m_organization = trim($request['parent_m_organization']);
        $parent_m_phone_1 = trim($request['parent_m_phone_1']);
        $parent_m_phone_2 = trim($request['parent_m_phone_2']);
        $student_id = trim($request['student_id']);

        date_default_timezone_set('Asia/Colombo');
        $server_date = date("Y-m-d");
        $server_time = date("H:i:s");

        if(isset($student_id) && !empty($student_id)){

            $res = $this->checkAlreadyExist($student_id);

            if($res['status'] == 0 && isset($res['available']) && $res['available'] == 0){ // No data available -> then insert new record
                
                // Prepare and bind parameters for the INSERT query
                $stmt_insert_parent = $this->con->prepare("INSERT INTO personal_info (father_name, father_occupation, father_organization, father_phone_1, father_phone_2, mother_name, mother_occupation, mother_organization, mother_phone_1, mother_phone_2, personal_info_created_date, personal_info_created_time, personal_info_updated_date, personal_info_updated_time, student_id) 
                VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                
                $stmt_insert_parent->bind_param("ssssssssssssssi", $parent_f_name, $parent_f_occupation, $parent_f_organization, $parent_f_phone_1, $parent_f_phone_2, $parent_m_name, $parent_m_occupation, $parent_m_organization, $parent_m_phone_1, $parent_m_phone_2, $server_date, $server_time, $server_date, $server_time, $student_id);

                $stmt_insert_parent->execute();

                if($stmt_insert_parent->affected_rows > 0){

                    return ['status' => 1, 'message' => 'Parent information added!'];
                    
                }else{
                    return ['status' => 0, 'message' => 'Parent information not added!']; 
                }

            }else if($res['status'] == 1 && isset($res['available']) && $res['available'] == 1){ // data avilable -> then update form data to db
                
                $personal_info_id = $res['message'][0]['personal_info_id'];

                $stmt_update_parent = $this->con->prepare("UPDATE personal_info SET father_name = ?, father_occupation = ?, father_organization = ?, father_phone_1 = ?, father_phone_2 = ?, mother_name = ?, mother_occupation = ?, mother_organization = ?, mother_phone_1 = ?, mother_phone_2 = ?, personal_info_updated_date = ?, personal_info_updated_time = ? WHERE personal_info_id = ? AND student_id = ?");

                $stmt_update_parent->bind_param("ssssssssssssii", $parent_f_name, $parent_f_occupation, $parent_f_organization, $parent_f_phone_1, $parent_f_phone_2, $parent_m_name, $parent_m_occupation, $parent_m_organization, $parent_m_phone_1, $parent_m_phone_2, $server_date, $server_time, $personal_info_id, $student_id);

                // Execute the statement
                $stmt_update_parent->execute();

                // Check if the update was successful
                if ($stmt_update_parent->affected_rows > 0) {
                    return ['status' => 1, 'message' => 'Parent information updated successfully!'];
                } else {
                    return ['status' => 0, 'message' => 'No changes were made or the record does not exist.'];
                }

            }else if($res['status'] == 0 && !isset($res['available'])){
                
                return ['status' => 0, 'message' => 'Failed to prepare the SQL statement.'];                  

            }

        }else{
            return ['status' => 0, 'message' => 'Invalid Request!'];                  

        }

    }

    private function checkAlreadyExist($student_id){
        
        $stmt_check_parent = $this->con->prepare("SELECT * FROM personal_info p WHERE student_id = ? LIMIT 1");
        
        if($stmt_check_parent){// Check if preparation was successful
            
            // Bind the parameters
            $stmt_check_parent->bind_param("i", $student_id);

            // Execute the statement
            $stmt_check_parent->execute();

            // Get the result
            $result = $stmt_check_parent->get_result();

            $data = [];

            // Process the result
            if ($result->num_rows > 0) {
                // Fetch associative array for each row
                while ($row = $result->fetch_assoc()) {
                    $data[] = $row;
                }
                return ['status' => 1, 'available' => 1, 'message' => $data];
            } else {
                return ['status' => 0, 'available' => 0, 'message' => 'No data available!'];
                // return ['status' => 0, 'available' => 0, 'message' => 'No data available!'];
            }

            // Close the statement
            $stmt_check_parent->close();

        }else{
            
            // Handle errors in preparing the statement
            return ['status' => 0, 'message' => 'Failed to prepare the SQL statement.'];
        }

    }

}

if (isset($_GET['VIEW_PENDING_REGISTRANTS'])) {
    if (isset($_SESSION['log_session']['user_id'])) {
        $sc = new StudentController();
        echo json_encode($sc->viewPendingRegistrants($_GET));
		exit();
    }
}

if (isset($_GET['VIEW_REGISTRANT'])) {
    if (isset($_SESSION['log_session']['user_id'])) {
        $sc = new StudentController();
        echo json_encode($sc->viewRegistrant($_GET));
		exit();
    }
}

if (isset($_POST['CREATE_STUDENT_ACC'])) {
    if (isset($_SESSION['log_session']['user_id'])) {
        $sc = new StudentController();
        echo json_encode($sc->createStudentAccount($_POST));
		exit();
    }
}

if (isset($_GET['VIEW_ALL_STUDENTS_BY_COUNSELOR'])) {
    if (isset($_SESSION['log_session']['user_id'])) {
        $sc = new StudentController();
        echo json_encode($sc->viewStudentsByCounselor($_GET));
		exit();
    }
}

if (isset($_GET['VIEW_STUDENT'])) {
    if (isset($_SESSION['log_session']['user_id'])) {
        $sc = new StudentController();
        echo json_encode($sc->viewStudent($_GET));
		exit();
    }
}

if (isset($_GET['LOAD_EDUCATION_TYPES'])) {
    if (isset($_SESSION['log_session']['user_id'])) {
        $sc = new StudentController();
        echo json_encode($sc->loadEducationTypes($_GET));
		exit();
    }
}

if (isset($_GET['LOAD_QUALIFICATIONS'])) {
    if (isset($_SESSION['log_session']['user_id'])) {
        $sc = new StudentController();
        echo json_encode($sc->loadQualification($_GET));
		exit();
    }
}

if(isset($_POST['SUBMIT_EDUCATION_DATA'])){
    if(isset($_SESSION['log_session']['user_id'])){
        $sc = new StudentController();
        echo json_encode($sc->submitEducationData($_POST, $_FILES));
		exit();
    }
}

if(isset($_GET['LOAD_EDUCATION_INFO_BY_STU_ID'])){
    if(isset($_SESSION['log_session']['user_id'])){
        $sc = new StudentController();
        echo json_encode($sc->access_education_info($_GET));
		exit();
    }
}

if(isset($_POST['SUBMIT_WORK_DATA'])){
    if(isset($_SESSION['log_session']['user_id'])){
        $sc = new StudentController();
        echo json_encode($sc->submitWorkData($_POST, $_FILES));
		exit();
    }
}

if(isset($_POST['SUBMIT_PARENT_DATA'])){
    if(isset($_SESSION['log_session']['user_id'])){
        $sc = new StudentController();
        echo json_encode($sc->submitParentData($_POST));
		exit();
    }
}

if(isset($_POST['SUBMIT_PORTFOLIO_DATA'])){
    if(isset($_SESSION['log_session']['user_id'])){
        $sc = new StudentController();
        echo json_encode($sc->submitPortfolioData($_POST));
		exit();
    }
}

if(isset($_GET['LOAD_PORTFOLIOS_BY_STU_ID'])){
    if(isset($_SESSION['log_session']['user_id'])){
        $sc = new StudentController();
        echo json_encode($sc->access_portfolio_info($_GET));
		exit();
    }
}

if(isset($_POST['UPDATE_EDU_DOCUMENT'])){
    if(isset($_SESSION['log_session']['user_id'])){
        $sc = new StudentController();
        echo json_encode($sc->updateEducationDocument($_POST,$_FILES));
		exit();
    }
}

if(isset($_POST['UPDATE_WORK_DOCUMENT'])){
    if(isset($_SESSION['log_session']['user_id'])){
        $sc = new StudentController();
        echo json_encode($sc->updateWorkDocument($_POST,$_FILES));
		exit();
    }
}




?>