<?php
$host = "localhost"; 
$username = "root";
$password = "";
$dbname = "WSAS";

$con = mysqli_connect($host, $username, $password, $dbname);

if (!$con) {
    die("Connection failed: " . mysqli_connect_error());
}

if ($_SERVER["REQUEST_METHOD"] == "POST") {

    $f_name = $_POST['f_name'];
    $m_name = $_POST['m_name'];
    $suffix = $_POST['suffix'];
    $l_name = $_POST['l_name'];
    $sex = $_POST['sex'];
    $course_id = $_POST['course_id'];
    $e_status = $_POST['e_status'];
    $email = $_POST['email'];
    $pword = $_POST['pword'];

    // Get active school year
    $sy_query = mysqli_query($con, "SELECT sy_id, sy_begin FROM school_year WHERE is_active = 1");
    $sy = mysqli_fetch_assoc($sy_query);
    $sy_id = $sy['sy_id'];
    $sy_begin = $sy['sy_begin'];

    // Generate student ID
    $last = mysqli_query($con, "SELECT student_id_num FROM adminstudent WHERE student_id_num LIKE '$sy_begin-%' ORDER BY student_id DESC LIMIT 1");

    if(mysqli_num_rows($last) > 0){
        $row = mysqli_fetch_assoc($last);
        $last_id = $row['student_id_num'];
        $num = explode("-", $last_id)[1];
        $new_num = $num + 1;
    } else {
        $new_num = 1;
    }

    $student_id_num = $sy_begin . "-" . str_pad($new_num, 3, "0", STR_PAD_LEFT);

    // Insert student
    $sql = "INSERT INTO adminstudent (student_id_num, f_name, m_name, suffix, l_name, sex, course_id, e_status, email, pword, sy_id)
        VALUES ('$student_id_num','$f_name','$m_name','$suffix','$l_name','$sex','$course_id','$e_status','$email','$pword','$sy_id')";
        
    if (mysqli_query($con, $sql)) {

        $student_id = mysqli_insert_id($con);

        $status_type = $_POST['status_type'] ?? '';
        $e_status = $_POST['e_status'];

        $is_alumni = $_POST['is_alumni'] ?? 'No';

        if($is_alumni == "Yes"){

            $fee_query = mysqli_query($con, "
                SELECT fee_amount 
                FROM adminfee 
                WHERE fee_name = 'SHS Alumni TF'
                LIMIT 1
            ");

            $fee_data = mysqli_fetch_assoc($fee_query);
            $alumni_tf = $fee_data['fee_amount'] ?? 0;

            mysqli_query($con, "
                INSERT INTO alumni (student_id, alumni_tf)
                VALUES ('$student_id', '$alumni_tf')
            ");
        }

        if($e_status == "Regular"){

            $year_level = $_POST['year_level'];

            $sy_query = mysqli_query($con, "SELECT semester FROM school_year WHERE is_active = 1 LIMIT 1");
            $sy = mysqli_fetch_assoc($sy_query);

            $current_sem = strtolower($sy['semester']);
            $semester = (strpos($current_sem, '1') !== false) ? 1 : 2;

            $subjects = mysqli_query($con, "
                SELECT subject_id, year_level, semester
                FROM course_subject
                WHERE course_id = '$course_id'
            ");

            while ($row = mysqli_fetch_assoc($subjects)) {

                $subject_id = $row['subject_id'];
                $sub_year = $row['year_level'];
                $sub_sem = (strpos(strtolower($row['semester']), '1') !== false) ? 1 : 2;

                $status = "not_taken";

                if($sub_year < $year_level){
                    $status = "completed";
                }

                if($sub_year == $year_level){
                    if($semester == 2 && $sub_sem == 1){
                        $status = "completed";
                    }
                }

                mysqli_query($con, "
                    INSERT INTO student_curriculum (student_id, subject_id, sc_status)
                    VALUES ('$student_id', '$subject_id', '$status')
                ");
            }

            header("Location: ../adminpanel/adminstudents.php?success=1");
            exit();
        }

        if($e_status == "Irregular"){

            // Redirect directly to subject evaluation (curriculum page)
            header("Location: ../view/evaluate_subject.php?student_id=" . $student_id);
            exit();
        }

        

        header("Location: ../adminpanel/adminstudents.php?success=1");
        exit();

    } else {
        echo "Error: " . mysqli_error($con);
    }

}

mysqli_close($con);
?>