<?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_resources . 'external_requests/finware_curl/groups_ledgers/curl_function_branch_students.php';
require_once $base_path_to_learning_resources .  'external_requests/finware_curl/book/curl_academic_session_with_branch.php';
require_once $base_path_to_learning_resources .  'external_requests/finware_curl/book/curl_alter_academic_session_with_branch.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, ["superadmin", "admin"], null);
if (!$specific_db_name) {
    errorreturn('Access denied.');
} else {
    $conn_rjllearning_specific = dynamicDbConnection($specific_db_name);
}

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');

    // 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 {
        $conn_rjllearning_specific->exec("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");
         // Start transaction
        $conn_rjllearning_specific->beginTransaction();

        // Get branch IDs for the institution
        $sql_get_branch = "SELECT id FROM branch WHERE institution_id = ?";
        $stmt_get_branch = $conn_rjllearning_specific->prepare($sql_get_branch);
        $stmt_get_branch->execute([$institution_id]);
        $row_branches = $stmt_get_branch->fetchAll();
      
        $branches = [];
        if (!empty($row_branches)) {
            foreach ($row_branches as $data) {
                $learning_branch_id = $data['id'];
                $branch_details = getBranchDetails($conn_rjllearning_specific, $learning_branch_id, $institution_id);
               
               // Stop cURL if any required field is empty or null
                if (empty($branch_details['rejoice_solutions_company_id']) || 
                    empty($branch_details['rejoice_solutions_branch_id']) || 
                    empty($branch_details['rejoice_finware_book_id'])) {
                     continue; // Skip to the next branch without adding this one
                }
                $branches[] = $branch_details;
            }
        }
      
        // 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));
       
            $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]);
            
           if (!empty($branches)){
   
              // Prepare the API request
            $finalArray = [
                'workspace_id' => $workspace_id,
                'branch' => $branches,
                'academic_session_name' => $name,
                'active_academic_session' => 1,
                'created_by' => $username
            ];

            // Call API function
            $response = CurlAcademicSessionWithBranch($finalArray);   
            }
            // Commit transaction if everything is successful
            $conn_rjllearning_specific->commit();
            successreturn(array('id' => $id, 'successmsg' => "Academic Session Added Successfully."));
        }
    } catch (Exception $e) {
        // Rollback on error
        $conn_rjllearning_specific->rollBack();
        echo "Error : " . $e->getMessage();
    }
    
} 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.
    
    $sql_academic_session = "SELECT id, name FROM academic_session WHERE id = ? AND institution_id = ?";
    $stmt_academic_session = $conn_rjllearning_specific->prepare($sql_academic_session);
    $stmt_academic_session->execute([$academic_id, $institution_id]);
    $row_academic_session = $stmt_academic_session->fetchAll();
    
    if (count($row_academic_session) !== 1) {
        errorreturn('The specified academic session name was not found.');
    }
    
    $existing_academic_name = $row_academic_session[0]['name'];


    // Validate Date Format for End Date
    if (!CheckStartDate($enddate)) {
        errorreturn('Invalid End Date Format');
    }

    $print_enddate = explode("-", $enddate);
    $print_enddate = $print_enddate[2] . '-' . $print_enddate[1] . '-' . $print_enddate[0];

    $sql_assignments = "SELECT count(*) as count from assignments where institution_id = ? AND last_date > ?";
    $stmt_assignments = $conn_rjllearning_specific->prepare($sql_assignments);
    $stmt_assignments->execute(array($institution_id, $enddate));
    $row_assignments = $stmt_assignments->fetch();
    if ($row_assignments and $row_assignments['count'] > 0) {
        errorreturn('Assignments with last_date greater than ' . $print_enddate . ' exist. Kindly choose an appropriate end date for academic session');
    }

    // // 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 count(*) as count FROM homework WHERE institution_id = ? AND date_created > ?";
        $stmt_homework = $conn_rjllearning_specific->prepare($sql_homework);
        $stmt_homework->execute([$institution_id, $enddate]);
        $row_homework = $stmt_homework->fetch();
        if ($row_homework && $row_homework['count'] > 0) {
            errorreturn('Homework with last_date greater than ' . $print_enddate . ' exists. Kindly choose an appropriate end date for academic session.');
        }
        
        // Query to validate end date against the announcement table
        $sql_announcement = "SELECT count(*) as count FROM announcement WHERE institution_id = ? AND date_created > ?";
        $stmt_announcement = $conn_rjllearning_specific->prepare($sql_announcement);
        $stmt_announcement->execute([$institution_id, $enddate]);
        $row_announcement = $stmt_announcement->fetch();
        if ($row_announcement && $row_announcement['count'] > 0) {
            errorreturn('Announcement with last_date greater than ' . $print_enddate . ' exists. Kindly choose an appropriate end date for academic session.');
        }
        
        // Query to validate end date against the student_attendance_daywise table
        $sql_student_attendance = "SELECT count(*) as count FROM student_attendance_daywise WHERE institution_id = ? AND DATE > ?";
        $stmt_student_attendance = $conn_rjllearning_specific->prepare($sql_student_attendance);
        $stmt_student_attendance->execute([$institution_id, $enddate]);
        $row_student_attendance = $stmt_student_attendance->fetch();
        if ($row_student_attendance && $row_student_attendance['count'] > 0) {
            errorreturn('Student attendance with last_date greater than ' . $print_enddate . ' exists. Kindly choose an appropriate end date for academic session.');
        }
        
        // Query for book_issues
        $sql_book_issues = "SELECT count(*) as count FROM book_issues WHERE institution_id = ? AND due_date > ?";
        $stmt_book_issues = $conn_rjllearning_specific->prepare($sql_book_issues);
        $stmt_book_issues->execute([$institution_id, $enddate]);
        $row_book_issues = $stmt_book_issues->fetch();
        if ($row_book_issues && $row_book_issues['count'] > 0) {
            errorreturn('Book issues with last_date greater than ' . $print_enddate . ' exist. Kindly choose an appropriate end date for academic session.');
        }
        
        // Query to validate end date against the apply_leaves table
        $sql_apply_leaves = "SELECT count(*) as count FROM apply_leaves WHERE institution_id = ? AND end_date > ?";
        $stmt_apply_leaves = $conn_rjllearning_specific->prepare($sql_apply_leaves);
        $stmt_apply_leaves->execute([$institution_id, $enddate]);
        $row_apply_leaves = $stmt_apply_leaves->fetch();
        if ($row_apply_leaves && $row_apply_leaves['count'] > 0) {
            errorreturn('Apply leaves with last_date greater than ' . $print_enddate . ' exist. Kindly choose an appropriate end date for academic session.');
        }
        
        // Query to validate end date against the visitors_log table
        $sql_visitors_log = "SELECT count(*) as count FROM visitors_log WHERE institution_id = ? AND date > ?";
        $stmt_visitors_log = $conn_rjllearning_specific->prepare($sql_visitors_log);
        $stmt_visitors_log->execute([$institution_id, $enddate]);
        $row_visitors_log = $stmt_visitors_log->fetch();
        if ($row_visitors_log && $row_visitors_log['count'] > 0) {
            errorreturn('Visitors log with last_date greater than ' . $print_enddate . ' exists. Kindly choose an appropriate end date for academic session.');
        }
        
        // Query to validate end date against the gate_pass table
        $sql_gate_pass = "SELECT count(*) as count FROM gate_pass WHERE institution_id = ? AND req_date > ?";
        $stmt_gate_pass = $conn_rjllearning_specific->prepare($sql_gate_pass);
        $stmt_gate_pass->execute([$institution_id, $enddate]);
        $row_gate_pass = $stmt_gate_pass->fetch();
        if ($row_gate_pass && $row_gate_pass['count'] > 0) {
            errorreturn('Gate pass with last_date greater than ' . $print_enddate . ' exists. Kindly choose an appropriate end date for academic session.');
        }
        
        // Query to validate end date against the marksheet table
        $sql_marksheet = "SELECT count(*) as count FROM marksheet WHERE institution_id = ? AND date_created > ?";
        $stmt_marksheet = $conn_rjllearning_specific->prepare($sql_marksheet);
        $stmt_marksheet->execute([$institution_id, $enddate]);
        $row_marksheet = $stmt_marksheet->fetch();
        if ($row_marksheet && $row_marksheet['count'] > 0) {
            errorreturn('Marksheet with last_date greater than ' . $print_enddate . ' exists. Kindly choose an appropriate end date for academic session.');
        }
     try {
        $conn_rjllearning_specific->exec("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");
         // Start transaction
        $conn_rjllearning_specific->beginTransaction();
    // Get branch IDs for the institution
        $sql_get_branch = "SELECT id FROM branch WHERE institution_id = ?";
        $stmt_get_branch = $conn_rjllearning_specific->prepare($sql_get_branch);
        $stmt_get_branch->execute([$institution_id]);
        $row_branches = $stmt_get_branch->fetchAll();
      
        $branches = [];
        if (!empty($row_branches)) {
            foreach ($row_branches as $data) {
                $learning_branch_id = $data['id'];
                $branch_details = getBranchDetails($conn_rjllearning_specific, $learning_branch_id, $institution_id);
               
               // Stop cURL if any required field is empty or null
                if (empty($branch_details['rejoice_solutions_company_id']) || 
                    empty($branch_details['rejoice_solutions_branch_id']) || 
                    empty($branch_details['rejoice_finware_book_id'])) {
                     continue; // Skip to the next branch without adding this one
                }
                $branches[] = $branch_details;
            }
        }


    // 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);
    
     
    if (!empty($branches)){
        // echo 'here';
        // Prepare the API request
        $finalArray = [
            'workspace_id' => $workspace_id,
            'branch' => $branches,
            'academic_session_name' => $name,
            'existing_academic_session_name' =>$existing_academic_name,
            'active_academic_session' => 1,
            'created_by' => $username
        ];
        
        // Call API function
        $response = CurlAlterAcademicSessionWithBranch($finalArray);   
    }
    
    $conn_rjllearning_specific->commit();
    successreturn("Academic session updated successfully.");
    } catch (Exception $e) {
        // Rollback on error
        $conn_rjllearning_specific->rollBack();
        echo "Error : " . $e->getMessage();
    }
}
