<?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/authenticate.php';

//dynemic db
require_once $base_path_to_learning_resources . 'includes/specific_db_config.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";



use Aws\S3\S3Client;
use Aws\S3\Exception\S3Exception;

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

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

// Validate API Signature
validateApiSignature($postdata['api_signature']);

// JWT Authentication
$jwttoken = $postdata['jwt_token'];
$jwt_validate = authenticate_jwt($jwttoken);

// Extract user details from JWT
$userid = $jwt_validate->data->id;
$workspace_id = $jwt_validate->data->workspace_id;
$username = $jwt_validate->data->username;
$role = $jwt_validate->data->role;
$institution_id = $jwt_validate->data->institution_id;
$branch_id = $jwt_validate->data->branch_id;

// Validate user access and get specific database connection
$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);
}

require_once $base_path_to_learning . 'functions/common_functions.php';
require_once $base_path_to_learning . 'functions/get_branch_functions.php';
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);

$function_type = trim($postdata['function_type']);

// Check academic session
$academic_id = CheckAcademicSession($conn_rjllearning_specific, $institution_id);
if ($academic_id == false) {
    errorreturn("Access denied. Academic session not active.");
}

$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.");
}

// Handle create_item function
if ($postdata['function_type'] == "create_item_category") {
    
    $faculty_id = $result_faculty['id'];
    $faculty_data = getClassSectionSubjectsAndInChargeRoles($conn_rjllearning, $conn_rjllearning_specific, $institution_id, $userid);
    
    $class_section_subjects  = $faculty_data['class_section_subjects'];
    $class_incharge_roles = $faculty_data['class_incharge_roles'];
    $staff_access_modules = $faculty_data['staff_access_modules'];

    $check_faculty_status = checkFacultyModuleGranted($function_type, $class_section_subjects, $class_incharge_roles, $staff_access_modules, null, null, null, $conn_rjllearning);
    if($check_faculty_status == false){
        errorreturn("Access denied: You do not have the required permissions.");
    }
    // Validate required fields
    if (!isset($postdata['category_name']) || !checkIfIssetAndNotEmpty($postdata['category_name'])) {
        errorreturn("Missing required fields: category_name.");
        return;
    }

    // Assign values and convert to title case
    $category_name = ucwords(strtolower(trim($postdata['category_name'])));
    $under = isset($postdata['under']) && checkIfIssetAndNotEmpty($postdata['under']) 
        ? ucwords(strtolower(trim($postdata['under']))) 
        : null;

    try {
        $under_category_id = null;

        if ($under !== null) {
            if (is_numeric($under)) {
                // If 'under' is an integer, check if it exists using ID
                $sql_under_check = "SELECT id FROM items_category WHERE institution_id = ? AND id = ?";
                $stmt_under_check = $conn_rjllearning_specific->prepare($sql_under_check);
                $stmt_under_check->execute([$institution_id, $under]);

                if ($stmt_under_check->rowCount() > 0) {
                    $existing_under_category = $stmt_under_check->fetch(PDO::FETCH_ASSOC);
                    $under_category_id = $existing_under_category['id'];
                } else {
                    errorreturn("Invalid 'under' category ID: $under. Category does not exist.");
                    return;
                }
            } else {
                // If 'under' is a string, check if it exists using category_name
                $sql_under_check = "SELECT id FROM items_category WHERE institution_id = ? AND category_name = ?";
                $stmt_under_check = $conn_rjllearning_specific->prepare($sql_under_check);
                $stmt_under_check->execute([$institution_id, $under]);

                if ($stmt_under_check->rowCount() > 0) {
                    $existing_under_category = $stmt_under_check->fetch(PDO::FETCH_ASSOC);
                    $under_category_id = $existing_under_category['id'];
                } else {
                    // If category does not exist, insert it first
                    $sql_insert_under = "INSERT INTO items_category 
                        (institution_id, category_name, `under`, date_created, time_created, created_by, last_modified_by, last_modified_at, status) 
                        VALUES (?, ?, NULL, ?, ?, ?, ?, ?, 1)";
                    
                    $stmt_insert_under = $conn_rjllearning_specific->prepare($sql_insert_under);
                    $stmt_insert_under->execute([
                        $institution_id, 
                        $under, 
                        date('Y-m-d'), 
                        date('H:i:s'), 
                        $username, 
                        $username, 
                        date('Y-m-d H:i:s')
                    ]);

                    $under_category_id = $conn_rjllearning_specific->lastInsertId();
                }
            }
        }

        // Check if the category already exists with the correct under category_id
        $sql_check = "SELECT id FROM items_category WHERE institution_id = ? AND category_name = ? AND under = ?";
        $stmt_check = $conn_rjllearning_specific->prepare($sql_check);
        $stmt_check->execute([$institution_id, $category_name, $under_category_id]);

        if ($stmt_check->rowCount() > 0) {
            $existing_category = $stmt_check->fetch(PDO::FETCH_ASSOC);
            $category_id = $existing_category['id'];

            errorreturn([
                'id' => $category_id,
                'successmsg' => "Category '$category_name' already exists, using existing ID."
            ]);
            return;
        }

        // Insert new category if it does not exist
        $sql_insert = "INSERT INTO items_category 
            (institution_id, category_name, `under`, date_created, time_created, created_by, last_modified_by, last_modified_at, status) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, 1)";
        
        $stmt_insert = $conn_rjllearning_specific->prepare($sql_insert);
        $stmt_insert->execute([
            $institution_id, 
            $category_name, 
            $under_category_id, 
            date('Y-m-d'), 
            date('H:i:s'), 
            $username, 
            $username, 
            date('Y-m-d H:i:s')
        ]);

        // Get last inserted ID
        $id = $conn_rjllearning_specific->lastInsertId();

        // Success response
        successreturn([
            'id' => $id,
            'successmsg' => "Category '$category_name' added successfully."
        ]);

    } catch (Exception $e) {
        errorreturn("Error: " . $e->getMessage());
    }
}

if ($postdata['function_type'] == "update_item_category") {
    
    // Validate required fields
    if (!isset($postdata['category_id']) || !is_numeric($postdata['category_id'])) {
        errorreturn("Missing or invalid category ID.");
        return;
    }

    if (!isset($postdata['category_name']) || !checkIfIssetAndNotEmpty($postdata['category_name'])) {
        errorreturn("Missing required field: category_name.");
        return;
    }

    // Assign values and convert to title case
    $category_id = intval($postdata['category_id']);
    $category_name = ucwords(strtolower(trim($postdata['category_name'])));
    $under = isset($postdata['under']) && checkIfIssetAndNotEmpty($postdata['under']) 
        ? ucwords(strtolower(trim($postdata['under']))) 
        : null;

    try {
        $under_category_id = null;

        if ($under !== null) {
            if (is_numeric($under)) {
                // If 'under' is an integer, check if it exists using ID
                $sql_under_check = "SELECT id FROM items_category WHERE institution_id = ? AND id = ?";
                $stmt_under_check = $conn_rjllearning_specific->prepare($sql_under_check);
                $stmt_under_check->execute([$institution_id, $under]);

                if ($stmt_under_check->rowCount() > 0) {
                    $existing_under_category = $stmt_under_check->fetch(PDO::FETCH_ASSOC);
                    $under_category_id = $existing_under_category['id'];
                } else {
                    errorreturn("Invalid 'under' category ID: $under. Category does not exist.");
                    return;
                }
            } else {
                // If 'under' is a string, check if it exists using category_name
                $sql_under_check = "SELECT id FROM items_category WHERE institution_id = ? AND category_name = ?";
                $stmt_under_check = $conn_rjllearning_specific->prepare($sql_under_check);
                $stmt_under_check->execute([$institution_id, $under]);

                if ($stmt_under_check->rowCount() > 0) {
                    $existing_under_category = $stmt_under_check->fetch(PDO::FETCH_ASSOC);
                    $under_category_id = $existing_under_category['id'];
                } else {
                    // If category does not exist, insert it first
                    $sql_insert_under = "INSERT INTO items_category 
                        (institution_id, category_name, `under`, date_created, time_created, created_by, last_modified_by, last_modified_at, status) 
                        VALUES (?, ?, NULL, ?, ?, ?, ?, ?, 1)";
                    
                    $stmt_insert_under = $conn_rjllearning_specific->prepare($sql_insert_under);
                    $stmt_insert_under->execute([
                        $institution_id, 
                        $under, 
                        date('Y-m-d'), 
                        date('H:i:s'), 
                        $username, 
                        $username, 
                        date('Y-m-d H:i:s')
                    ]);

                    $under_category_id = $conn_rjllearning_specific->lastInsertId();
                }
            }
        }

        // Check if the category exists
        $sql_check = "SELECT id FROM items_category WHERE institution_id = ? AND id = ?";
        $stmt_check = $conn_rjllearning_specific->prepare($sql_check);
        $stmt_check->execute([$institution_id, $category_id]);

        if ($stmt_check->rowCount() == 0) {
            errorreturn("Category ID '$category_id' does not exist.");
            return;
        }

        // Update the category
        $sql_update = "UPDATE items_category SET 
            category_name = ?, 
            `under` = ?, 
            last_modified_by = ?, 
            last_modified_at = ? 
            WHERE id = ? AND institution_id = ?";
        
        $stmt_update = $conn_rjllearning_specific->prepare($sql_update);
        $stmt_update->execute([
            $category_name, 
            $under_category_id, 
            $username, 
            date('Y-m-d H:i:s'), 
            $category_id, 
            $institution_id
        ]);

        // Success response
        successreturn([
            'id' => $category_id,
            'successmsg' => "Category '$category_name' updated successfully."
        ]);

    } catch (Exception $e) {
        errorreturn("Error: " . $e->getMessage());
    }
}

