<?php
ini_set('display_errors', 1);

require_once '../../resources/base_path_include.php';

require_once $base_path_to_learning_resources  . "includes/connect.php"; // use require for mandatory files

require_once $base_path_to_learning_resources . 'lib/function.php';
require_once $base_path_to_learning_resources . 'lib/validate_api_signature.php';

//valdation file
require_once $base_path_to_learning_resources . 'lib/validate_function.php';
require_once $base_path_to_learning . '/user_management/check_username_availability.php';
require_once $base_path_to_learning . 'user_management/authenticate.php';
require_once $base_path_to_learning . 'functions/common_functions.php';
require_once $base_path_to_learning . "faculty_api/faculty_function/common_faculty_function.php";
require_once $base_path_to_learning . "principal_api/principal_function/principal_function.php";

//db access function
require_once $base_path_to_learning_resources . 'includes/specific_db_config.php';

$postdata = json_decode(file_get_contents("php://input"), true);
//print_r($postdata);
checkIfNoPhpContentsReceive();

if (
    !isset($postdata['api_signature']) or !checkIfIssetAndNotEmpty($postdata['api_signature']) or
    !isset($postdata['function_type']) or !checkIfIssetAndNotEmpty($postdata['function_type'])  or
    !isset($postdata['jwt_token']) or  !checkIfIssetAndNotEmpty($postdata['jwt_token'])
) {
    errorreturn("All * fields are required.");
}


validateApiSignature($postdata['api_signature']);

$jwttoken = $postdata['jwt_token'];
$jwt_validate = authenticate_jwt($jwttoken);
//then workspace_id is iin decodede data of jwt use from there
$userid = $jwt_validate->data->id;
$workspace_id = $jwt_validate->data->workspace_id;
$username = $jwt_validate->data->username;
$role = $jwt_validate->data->role;


$specific_db_name = validateUserAccess($conn_rjllearning, $userid, $role, $workspace_id,["teacher"], null);
if (!$specific_db_name) {
    errorreturn('Access denied.');
} else {
    $conn_rjllearning_specific = dynamicDbConnection($specific_db_name);
}
    $function_type = trim($postdata['function_type']);
    
    $Faculty_Codes = $conn_rjllearning_specific->prepare("SELECT id FROM faculty_info WHERE userid = ? AND institution_id = ? AND branch_id = ?");
    $Faculty_Codes->execute(array($userid, $institution_id, $branch_id));
    // Fetch a single row instead of all
    $result_faculty = $Faculty_Codes->fetch(PDO::FETCH_ASSOC);
    // Check if the faculty exists
    if ($result_faculty === false) {
        errorreturn("Faculty Not Found.");
    }
    $faculty_id = $result_faculty['id'];
    $teacher_data = getClassSectionSubjectsAndInChargeRoles($conn_rjllearning, $conn_rjllearning_specific, $institution_id, $userid);
    $class_section_subjects  = $teacher_data['class_section_subjects'];
    $class_incharge_roles = $teacher_data['class_incharge_roles'];
    $staff_access_modules = $teacher_data['staff_access_modules'];

    checkFacultyModuleGranted($function_type, $class_section_subjects, $class_incharge_roles, $staff_access_modules, $class_id, $section_id, null, $conn_rjllearning);
    
    require_once $base_path_to_learning . 'user_management/user_versioning.php';

$version = isset($_POST['version']) ? $_POST['version'] : (isset($postdata['version']) ? $postdata['version'] : null);
$updated_time = isset($_POST['updated_time']) ? $_POST['updated_time'] : (isset($postdata['updated_time']) ? $postdata['updated_time'] : null);

$response = user_data_refresh($conn_rjllearning, $conn_rjllearning_specific, $version, $updated_time, $userid, $institution_id);
    
if ($postdata['function_type'] == "create_session") {

    if (
    !isset($postdata['institution_id']) or !checkIfIssetAndNotEmpty($postdata['institution_id']) or
    !isset($postdata['startdate']) or !checkIfIssetAndNotEmpty($postdata['startdate']) or
    !isset($postdata['enddate']) or !checkIfIssetAndNotEmpty($postdata['enddate'])
    ) {
        errorreturn("All * fields are required.");
    }

    $name = (isset($postdata['name']) and !empty($postdata['name'])) ? ucwords(strtolower(trim($postdata['name']))) : null;
    $startdate = trim(date('Y-m-d', strtotime($postdata['startdate'])));
    $enddate = trim(date('Y-m-d', strtotime($postdata['enddate'])));
    $institution_id = trim($postdata['institution_id']);
  

    //Book Opening Date Y-m-d
    if (!CheckStartDate($startdate)) {
        errorreturn('Invalid Date Format');
    }

    //Book Opening Date Y-m-d
    if (!CheckStartDate($enddate)) {
        errorreturn('Invalid Date Format');
    }

    if ($startdate >= $enddate) {
        errorreturn('Invalid Date Range');
    }

    if (!CheckStartDate($startdate)) {
        errorreturn('Invalid Date Format');
    }

    $res = $conn_rjllearning_specific->prepare("SELECT id,institution_id FROM academic_session WHERE institution_id= ? and name= ? and active_academic_session = ?");
    $res->execute(array($institution_id, $name, 1));
    $result1 = $res->fetchAll();
    if (count($result1) >= 1) {
        errorreturn("The academic sesssion already exists");
    }
    
    $current_year = date('Y');

    // Ensure start date is within the current year
    if (date('Y', strtotime($startdate)) != $current_year) {
        errorreturn("The start date must be within the current year.");
    }




    // Check if there is a previous active session and validate date range
    $resPreviousSession = $conn_rjllearning_specific->prepare("
        SELECT enddate 
        FROM academic_session 
        WHERE institution_id = ? 
        AND active_academic_session = 1
        ORDER BY enddate DESC
        LIMIT 1
    ");
    $resPreviousSession->execute([$institution_id]);
    $previousSession = $resPreviousSession->fetch(PDO::FETCH_ASSOC);

    if ($previousSession) {
        // Check if the previous session's end date is after the new session's start date
        if ($previousSession['enddate'] > $startdate) {
            errorreturn("The new session's start date must be after the last session's end date.");
        }
    }



    try {

        // Check if any active session exists
        $sql_check_active = "SELECT COUNT(*) AS active_sessions FROM academic_session WHERE institution_id = ? ";
        $stmt_check_active = $conn_rjllearning_specific->prepare($sql_check_active);
        $stmt_check_active->execute(array($institution_id));
        $row_active_sessions = $stmt_check_active->fetch(PDO::FETCH_ASSOC);

        if ($row_active_sessions['active_sessions'] == 0) {
            // No active sessions, so this is the first session
            $active_academic_session = '1'; // Set as active
        } else {
            // Active session(s) already exist, so this is not the first session
            $active_academic_session = '0'; // Set as inactive
        }

        // Prepare the query to check overlapping sessions
        $sqlSession = "
        SELECT id
            FROM academic_session 
        WHERE 
        institution_id = ? 
        AND (
            (? BETWEEN startdate AND enddate) OR 
            (? BETWEEN startdate AND enddate) OR 
            (startdate BETWEEN ? AND ?) OR 
            (enddate BETWEEN ? AND ?) OR 
            (CURDATE() BETWEEN startdate AND enddate)
        )";
        $resSession = $conn_rjllearning_specific->prepare($sqlSession);
        // Bind parameters for placeholders
        $resSession->execute([
            $institution_id, // Institution ID
            $startdate,      // New session start date
            $enddate,        // New session end date
            $startdate,      // New session start date
            $enddate,        // New session end date
            $startdate,      // New session start date
            $enddate         // New session end date
        ]);
        // Fetch results
        $result = $resSession->fetchAll();
        // Check if any overlapping session exists
        if (count($result) > 0) {
            // Overlapping session exists
            // errorreturn("The session dates overlap with an existing academic session.");
            cacheAcademicStudentsData($conn_rjllearning_specific, $institution_id, $result[0]['id'], $startdate, $enddate);
        } 
        
        
        {
            $sql = "INSERT INTO academic_session(institution_id,name,startdate,enddate,added_by,date_created,time_created,active_academic_session)
                                 VALUES(?,?,?,?,?,?,?,?)";
            $stmt =  $conn_rjllearning_specific->prepare($sql);
            $stmt->execute(array($institution_id, $name, $startdate, $enddate, $username, date('Y-m-d'), time(), 1));
        }
    } catch (Exception $e) {
        echo "Error : " . $e->getMessage();
    }
    
    $id = $conn_rjllearning_specific->lastInsertId();
    
    $sql = "UPDATE academic_session SET active_academic_session = 0 WHERE id != ? and institution_id = ?";
    $stmt = $conn_rjllearning_specific->prepare($sql);
    $stmt->execute([$id,$institution_id]);
    


    successreturn(array('id' => $id, 'successmsg' => "Academic Session Added Successfully."));
    
} else if ($postdata['function_type'] == "alter_session") {
        if (
            !isset($postdata['institution_id']) || !checkIfIssetAndNotEmpty($postdata['institution_id']) ||
            !isset($postdata['session_id']) || !checkIfIssetAndNotEmpty($postdata['session_id']) ||
            !isset($postdata['enddate']) || !checkIfIssetAndNotEmpty($postdata['enddate'])
        ) {
            errorreturn("Institution ID, Session ID, and End Date are required.");
        }
    
        $name = (isset($postdata['name']) && !empty($postdata['name'])) ? trim($postdata['name']) : null;
        $enddate = date('Y-m-d', strtotime(trim($postdata['enddate'])));
        $session_id = trim($postdata['session_id']);
        $institution_id = trim($postdata['institution_id']);
        $academic_id = $session_id; // Assuming academic_id matches session_id for validation purposes.
    
        // Validate Date Format for End Date
        if (!CheckStartDate($enddate)) {
            errorreturn('Invalid End Date Format');
        }
        
        // Query to validate end date against the assignments table
        $sql_assignments = "SELECT MIN(last_date) AS minimum_date FROM assignments WHERE institution_id = ? AND academic_id = ?";
        $stmt_assignments = $conn_rjllearning_specific->prepare($sql_assignments);
        $stmt_assignments->execute([$institution_id, $academic_id]);
        $row_assignments = $stmt_assignments->fetch();
        $assignments_enddate = $row_assignments['minimum_date'];
        
        if ($enddate < $assignments_enddate) {
            errorreturn("End Date cannot be earlier than assignments' end date: " . $assignments_enddate);
        }
        
        // Query to validate end date against the homework table
        $sql_homework = "SELECT MIN(date_created) AS minimum_date FROM homework WHERE institution_id = ? AND academic_id = ?";
        $stmt_homework = $conn_rjllearning_specific->prepare($sql_homework);
        $stmt_homework->execute([$institution_id, $academic_id]);
        $row_homework = $stmt_homework->fetch();
        $homework_enddate = $row_homework['minimum_date'];
        
        if ($enddate < $homework_enddate) {
            errorreturn("End Date cannot be earlier than homework's end date: " . $homework_enddate);
        }
        
        // Query to validate end date against the announcement table
        $sql_announcement = "SELECT MIN(date_created) AS minimum_date FROM announcement WHERE institution_id = ? AND academic_id = ?";
        $stmt_announcement = $conn_rjllearning_specific->prepare($sql_announcement);
        $stmt_announcement->execute([$institution_id, $academic_id]);
        $row_announcement = $stmt_announcement->fetch();
        $announcement_enddate = $row_announcement['minimum_date'];
        
        if ($enddate < $announcement_enddate) {
            errorreturn("End Date cannot be earlier than announcement's end date: " . $announcement_enddate);
        }
        
        // Query to validate end date against the student_attendance_daywise table
        $sql_student_attendance = "SELECT MIN(DATE) AS minimum_date FROM student_attendance_daywise WHERE institution_id = ? AND academic_id = ?";
        $stmt_student_attendance = $conn_rjllearning_specific->prepare($sql_student_attendance);
        $stmt_student_attendance->execute([$institution_id, $academic_id]);
        $row_student_attendance = $stmt_student_attendance->fetch();
        $attendance_enddate = $row_student_attendance['minimum_date'];
        
        if ($enddate < $attendance_enddate) {
            errorreturn("End Date cannot be earlier than student attendance's end date: " . $attendance_enddate);
        }
        
        // Query for book_issues
        $sql_book_issues = "SELECT MIN(due_date) AS minimum_date FROM book_issues WHERE institution_id = ? AND academic_id = ?";
        $stmt_book_issues = $conn_rjllearning_specific->prepare($sql_book_issues);
        $stmt_book_issues->execute([$institution_id, $academic_id]);
        $row_book_issues = $stmt_book_issues->fetch();
        $book_issues_enddate = $row_book_issues['minimum_date'];
        
        if ($enddate < $book_issues_enddate) {
            errorreturn("End Date cannot be earlier than book issue's due date: " . $book_issues_enddate);
        }
        
        // Query to validate end date against the apply_leaves table
        $sql_apply_leaves = "SELECT MIN(end_date) AS minimum_date FROM apply_leaves WHERE institution_id = ?";
        $stmt_apply_leaves = $conn_rjllearning_specific->prepare($sql_apply_leaves);
        $stmt_apply_leaves->execute([$institution_id]);
        $row_apply_leaves = $stmt_apply_leaves->fetch();
        $apply_leaves_enddate = $row_apply_leaves['minimum_date'];
        
        if ($enddate < $apply_leaves_enddate) {
            errorreturn("End Date cannot be earlier than apply leaves' end date: " . $apply_leaves_enddate);
        }
        
        // Query to validate end date against the visitors_log table
        $sql_visitors_log = "SELECT MIN(date) AS minimum_date FROM visitors_log WHERE institution_id = ? AND academic_id = ?";
        $stmt_visitors_log = $conn_rjllearning_specific->prepare($sql_visitors_log);
        $stmt_visitors_log->execute([$institution_id, $academic_id]);
        $row_visitors_log = $stmt_visitors_log->fetch();
        $visitors_log_enddate = $row_visitors_log['minimum_date'];
        
        if ($enddate < $visitors_log_enddate) {
            errorreturn("End Date cannot be earlier than visitors log date: " . $visitors_log_enddate);
        }
        
        // Query to validate end date against the gate_pass table
        $sql_gate_pass = "SELECT MIN(req_date) AS minimum_date FROM gate_pass WHERE institution_id = ? AND academic_id = ?";
        $stmt_gate_pass = $conn_rjllearning_specific->prepare($sql_gate_pass);
        $stmt_gate_pass->execute([$institution_id, $academic_id]);
        $row_gate_pass = $stmt_gate_pass->fetch();
        $gate_pass_enddate = $row_gate_pass['minimum_date'];
        
        if ($enddate < $gate_pass_enddate) {
            errorreturn("End Date cannot be earlier than gate pass date: " . $gate_pass_enddate);
        }
        
        // Query to validate end date against the marksheet table
        $sql_marksheet = "SELECT MIN(date_created) AS minimum_date FROM marksheet WHERE institution_id = ? AND academic_id = ?";
        $stmt_marksheet = $conn_rjllearning_specific->prepare($sql_marksheet);
        $stmt_marksheet->execute([$institution_id, $academic_id]);
        $row_marksheet = $stmt_marksheet->fetch();
        $marksheet_enddate = $row_marksheet['minimum_date'];
        
        if ($enddate < $marksheet_enddate) {
            errorreturn("End Date cannot be earlier than marksheet date: " . $marksheet_enddate);
        }

        // Check for Overlapping Sessions
        $sqlSession = "
            SELECT id 
            FROM academic_session 
            WHERE institution_id = ? 
            AND id != ? 
            AND (
                ? BETWEEN startdate AND enddate OR 
                ? BETWEEN startdate AND enddate
            )
        ";
        $resSession = $conn_rjllearning_specific->prepare($sqlSession);
        $resSession->execute([$institution_id, $session_id, $enddate, $enddate]);
        if ($resSession->rowCount() > 0) {
            errorreturn("The session end date overlaps with an existing academic session.");
        }
    
        // Check for Duplicate Name
        if ($name) {
            $sql_name = "
                SELECT id 
                FROM academic_session 
                WHERE id != ? AND institution_id = ? AND name = ?
            ";
            $stmt_name = $conn_rjllearning_specific->prepare($sql_name);
            $stmt_name->execute([$session_id, $institution_id, $name]);
            if ($stmt_name->rowCount() > 0) {
                errorreturn("An academic session with the same name already exists.");
            }
        }
    
        // Update Academic Session
        $sql_update = "UPDATE academic_session SET ";
        $params = [];
        if ($name) {
            $sql_update .= "name = ?, ";
            $params[] = $name;
        }
        $sql_update .= "enddate = ? WHERE id = ? AND institution_id = ?";
        $params[] = $enddate;
        $params[] = $session_id;
        $params[] = $institution_id;
    
        $stmt_update = $conn_rjllearning_specific->prepare($sql_update);
        $stmt_update->execute($params);
    
        successreturn("Academic session updated successfully.");
}
