<?php
session_start();
include 'includes/database.php';
include 'includes/functions.php';
$conn= new Functions();

if(!empty($_POST['old_student_name'])
    && !empty($_POST['sex'])
    && !empty($_POST['reg_no'])){
    $old_name = preg_replace('/[^\w\s]+/', '', $_POST['old_student_name']);
    $old_name = trim(preg_replace('/\s+/', ' ', $old_name));

    $new_name = preg_replace('/[^\w\s]+/', '', $_POST['new_student_name']);
    $new_name = trim(preg_replace('/\s+/', ' ', $new_name));
    $sex = $_POST['sex'];
    $reg_no = $_POST['reg_no'];
    $dob = $_POST['dob'];
    $country = $_POST['country'];
    $state = $_POST['state'];
    $email = $_POST['email'];
    $dob = $_POST['dob'];
    $gender = $_POST['sex'];
    $religion = $_POST['religion'];
    $phone = $_POST['phone'];
    $address = $_POST['address'];
    $username = preg_replace('/[^\w\s]+/', '', $_POST['username']);
    $username = trim(preg_replace('/\s+/', ' ', $username));
    $height = $_POST['height'];
    $weight = $_POST['weight'];
    $section = $_POST['section'];
    $class = $_POST['class'];

    $sql = "SELECT * FROM student WHERE name =:name AND username !=:username AND name !=''";
    $conn->query($sql);
    $conn->bind(":name", $new_name);
    $conn->bind(":username", $username);
    if($conn->rowCount() > 0){
        echo "<p class='alert alert-danger alert-dismissible fade show' role='alert'>
         <i class='fas fa-ban'></i> Name already exists for another student.
                  <button type=\"button\" class=\"close\" data-dismiss=\"alert\" aria-label=\"Close\">
                             <span aria-hidden=\"true\">×</span>
                  </button>
           </p>";
        return false;
    }


    //check if new name is entered
    if(!empty($new_name)){
        //check if new name and old name are the same
        if($old_name == $new_name){
            //throw an error and return false.
            echo "<script>toastr['error']('Old and new student name are the same')</script>";
            return false;
        }else{
            //check if new name does not belong to another student
            $sql = "SELECT * FROM student WHERE name =:name AND username !=:username AND name !=''";
            $conn->query($sql);
            $conn->bind(":name", $new_name);
            $conn->bind(":username", $username);
            if($conn->rowCount() > 0){
                echo "<script>toastr['error']('Name already exist for another student.')</script>";
                return false;
            }
            /*
             * go ahead and update :
             * 1. student table
             * 2. result table,
             * 3. psychomotor table,
             * 4. cognitive table and
             * 5. attendance table with new name
             * 6. student class position
             * 7. subject position
             * 8. pins
             */

            //1. update student table
            $sql = "UPDATE student SET name =:name, sex=:sex,  username=:username, country=:country,
            dob=:dob, religion=:religion, email=:email, phone=:phone, address=:address, state=:state, registerNO=:register_no, height =:height, weight =:weight, classesID =:class, sectionid =:section WHERE username =:username";
            $conn->query($sql);
            $conn->bind(":name", $new_name);
            $conn->bind(":sex", $sex);
            $conn->bind(":username", $username);
            $conn->bind(":country", $country);
            $conn->bind(":dob", $dob);
            $conn->bind(":religion", $religion);
            $conn->bind(":email", $email);
            $conn->bind(":phone", $phone);
            $conn->bind(":address", $address);
            $conn->bind(":state", $state);
            $conn->bind(":register_no", $reg_no);
            $conn->bind(":height", $height);
            $conn->bind(":weight", $weight);
            $conn->bind(":class", $class);
            $conn->bind(":section", $section);
            $conn->execute();

            //2. update student name in results table if it exists
            $sql = "SELECT student_name FROM student_results WHERE student_name =:old_student_name";
            $conn->query($sql);
            $conn->bind(":old_student_name", $old_name);
            if($conn->rowCount() > 0){
                //update old student name with new student name
                $sql = "UPDATE student_results SET student_name =:new_name 
                        WHERE student_name =:old_name";
                $conn->query($sql);
                $conn->bind(":new_name", $new_name);
                $conn->bind(":old_name", $old_name);
                $conn->execute();
            }

            //3. update student name in psychomotor if it exists
            $sql = "SELECT student_name FROM students_psychomotor WHERE student_name =:old_student_name";
            $conn->query($sql);
            $conn->bind(":old_student_name", $old_name);
            if($conn->rowCount() > 0){
                //update old student name with new student name
                $sql = "UPDATE students_psychomotor SET student_name =:new_name 
                        WHERE student_name =:old_name";
                $conn->query($sql);
                $conn->bind(":new_name", $new_name);
                $conn->bind(":old_name", $old_name);
                $conn->execute();
            }

            //4. update student name in cognitive if it exists

            $sql = "SELECT student_name FROM students_cognitive_area WHERE student_name =:old_student_name";
            $conn->query($sql);
            $conn->bind(":old_student_name", $old_name);
            if($conn->rowCount() > 0){
                //update old student name with new student name
                $sql = "UPDATE students_cognitive_area SET student_name =:new_name 
                        WHERE student_name =:old_name";
                $conn->query($sql);
                $conn->bind(":new_name", $new_name);
                $conn->bind(":old_name", $old_name);
                $conn->execute();
            }

            //5. update student name in attendance if it exists

            $sql = "SELECT student_name FROM attendance WHERE student_name =:old_student_name";
            $conn->query($sql);
            $conn->bind(":old_student_name", $old_name);
            if($conn->rowCount() > 0){
                //update old student name with new student name
                $sql = "UPDATE attendance SET student_name =:new_name 
                        WHERE student_name =:old_name";
                $conn->query($sql);
                $conn->bind(":new_name", $new_name);
                $conn->bind(":old_name", $old_name);
                $conn->execute();
            }

            //6. update name in student class position if it exists

            $sql = "SELECT name FROM student_class_positions WHERE name =:old_student_name";
            $conn->query($sql);
            $conn->bind(":old_student_name", $old_name);
            if($conn->rowCount() > 0){
                //update old student name with new student name
                $sql = "UPDATE student_class_positions SET name =:new_name 
                        WHERE name =:old_name";
                $conn->query($sql);
                $conn->bind(":new_name", $new_name);
                $conn->bind(":old_name", $old_name);
                $conn->execute();
            }

            //7. update name in subject position if it exists

            $sql = "SELECT name FROM subject_positions WHERE name =:old_student_name";
            $conn->query($sql);
            $conn->bind(":old_student_name", $old_name);
            if($conn->rowCount() > 0){
                //update old student name with new student name
                $sql = "UPDATE subject_positions SET name =:new_name
                        WHERE name =:old_name";
                $conn->query($sql);
                $conn->bind(":new_name", $new_name);
                $conn->bind(":old_name", $old_name);
                $conn->execute();
            }

            //8. update name in pins if it exists

            $sql = "SELECT used_by FROM pins WHERE used_by =:old_student_name";
            $conn->query($sql);
            $conn->bind(":old_student_name", $old_name);
            if($conn->rowCount() > 0){
                //update old student name with new student name
                $sql = "UPDATE pins SET used_by =:new_name 
                        WHERE used_by =:old_name";
                $conn->query($sql);
                $conn->bind(":new_name", $new_name);
                $conn->bind(":old_name", $old_name);
                $conn->execute();
            }

            //all relevant tables update
            echo "<script>toastr['success']('Student Name updated successfully.')</script>
                  <meta http-equiv='refresh' content='3; student'>";

        }
    }else{
        //name wasn't changed, we check if class or section was changed
        if(!empty($class) AND !empty($section)){
            //update class and section
            $sql = "UPDATE student SET classesID =:class, sectionid =:section 
                    WHERE username =:username";
            $conn->query($sql);
            $conn->bind(":class", $class);
            $conn->bind(":section", $section);
            $conn->bind(":username", $username);
            try {
                $conn->execute();
                echo "<script>toastr['success']('Student details updated successfully.')
                    </script> <meta http-equiv='refresh' content='3; student'>";
            }catch (PDOException $err){
                $error = $err->getMessage();
                echo "<script>toastr['error']('An error occurred while updating student details: $error')</script>";
            }
        }else{
            echo "<script>toastr['error']('No changes were detected...')</script>";
        }

    }

}else{
    echo "<script>toastr['error']('Fields marked asterisk(*) are required.')</script>";
}
