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

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

require_once $base_path_to_learning_resources . "includes/connect.php";
require_once $base_path_to_learning_resources . 'lib/function.php';
require_once $base_path_to_learning_resources . 'lib/validate_api_signature.php';

// Validation 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_resources . 'includes/specific_db_config.php';
require_once $base_path_to_learning . 'functions/common_functions.php';


// Fetch and decode input data
$postdata = json_decode(file_get_contents("php://input"), true);
checkIfNoPhpContentsReceive();

// Check required fields
if (
     !isset($postdata['group_name']) || !checkIfIssetAndNotEmpty($postdata['group_name']) ||
    !isset($postdata['parent_ledgerhead']) || !checkIfIssetAndNotEmpty($postdata['parent_ledgerhead']) ||
    !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 required fields must be provided.");
}

validateApiSignature($postdata['api_signature']);

$jwttoken = $postdata['jwt_token'];
$jwt_validate = authenticate_jwt($jwttoken);
$userid = $jwt_validate->data->id;
$workspace_id = $jwt_validate->data->workspace_id;
$username = $jwt_validate->data->username;
$role = $jwt_validate->data->role;
$branch_id = $jwt_validate->data->branch_id;
$institution_id = $jwt_validate->data->institution_id;


$group_name = ucwords($postdata['group_name']); 
$parent_ledgerhead = $postdata['parent_ledgerhead'];


// Validate user access
$specific_db_name = validateUserAccess($conn_rjllearning, $userid, $role, $workspace_id,["principal"], null);
if (!$specific_db_name) {
    errorreturn('Access denied.');
}

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

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

// Create vendor logic
if ($postdata['function_type'] == 'create_expense_group') {
    // Validate required input
    if (empty($group_name)) {
        errorreturn("Group name is required.");
    }
    
    if (!preg_match('/^[a-zA-Z0-9\s]+$/', $group_name)) {
        errorreturn("Group name can only contain letters, numbers and spaces.");
    }
    
    $group_name = ucwords(strtolower($group_name));

    if (empty($parent_ledgerhead)) {
        errorreturn("Parent ledgerhead is required.");
    }

    try {
        // Check if group_name already exists in the ledgerheads table
        $res = $conn_rjllearning->prepare("SELECT ledgerhead_name FROM ledgerheads WHERE ledgerhead_name = ?");
        $res->execute([$group_name]);
        if ($res->rowCount() > 0) {
            errorreturn("Group name cannot be equal to any existing ledgerheads or group names.");
        }

        // Check if group_name already exists in the unmarked_transaction_type_group table
        $res = $conn_rjllearning_specific->prepare(
            "SELECT group_name FROM unmarked_transaction_type_group 
             WHERE group_name = ? AND institution_id = ?"
        );
        $res->execute([$group_name, $institution_id]);
        if ($res->rowCount() > 0) {
            errorreturn("Group name cannot be equal to any existing ledgerheads or group names.");
        }

        // Initialize variables for hierarchy levels
        $main_ledgerhead = null; // Cannot be null, will validate later
        $parent_lvl1 = null; // Can be null
        $parent_lvl2 = null;
        $parent_lvl3 = null;
        $parent_lvl4 = null;
        $parent_lvl5 = null;

        // Fetch main_ledgerhead and parent levels based on parent_ledgerhead
        $stmt = $conn_rjllearning->prepare("SELECT ledgerhead_name FROM ledgerheads WHERE ledgerhead_name = ?");
        $stmt->execute([$parent_ledgerhead]);
        $result1 = $stmt->fetch();

        if ($result1) {
            $main_ledgerhead = $parent_ledgerhead;
        } else {
            $stmt = $conn_rjllearning_specific->prepare(
                "SELECT group_name, main_ledgerhead, parent_lvl1, parent_lvl2, parent_lvl3, parent_lvl4, parent_lvl5 
                 FROM unmarked_transaction_type_group 
                 WHERE group_name = ? AND institution_id = ?"
            );
            $stmt->execute([$parent_ledgerhead, $institution_id]);
            $result2 = $stmt->fetch();

            if ($result2) {
                if (!empty($result2['parent_lvl5'])) {
                    errorreturn("Maximum subgroup level reached for this ledger group.");
                }

                $main_ledgerhead = $result2['main_ledgerhead'];
                $parent_lvl1 = $parent_ledgerhead;
                $parent_lvl2 = $result2['parent_lvl1'];
                $parent_lvl3 = $result2['parent_lvl2'];
                $parent_lvl4 = $result2['parent_lvl3'];
                $parent_lvl5 = $result2['parent_lvl4'];
            } else {
                errorreturn("Parent ledgerhead not found in the system.");
            }
        }

        // Ensure main_ledgerhead is not null
        if (!$main_ledgerhead) {
            errorreturn("Invalid parent ledgerhead or main ledgerhead could not be determined.");
        }

        // Insert the new group into unmarked_transaction_type_group
        $sql = "INSERT INTO unmarked_transaction_type_group (
            institution_id, group_name, main_ledgerhead, created_by, date_created, time_created, status, 
            parent_lvl1, parent_lvl2, parent_lvl3, parent_lvl4, parent_lvl5
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        $stmt = $conn_rjllearning_specific->prepare($sql);
        $stmt->execute([
            $institution_id,
            $group_name,
            $main_ledgerhead,
            $username,
            date('Y-m-d'),
            date('H:i:s'),
            1,
            $parent_lvl1,
            $parent_lvl2,
            $parent_lvl3,
            $parent_lvl4,
            $parent_lvl5
        ]);

        $expense_id = $conn_rjllearning_specific->lastInsertId();
        successreturn(['id' => $expense_id, 'successmsg' => "Ledger group successfully added."]);
    } catch (Exception $e) {
        errorreturn("Error occurred while adding the expense group: " . $e->getMessage());
    }
}

else if ($postdata['function_type'] == "alter_group") {
    if (!isset($postdata['ledger_group_id']) || !checkIfIssetAndNotEmpty($postdata['ledger_group_id'])) {
        errorreturn('Error in updating the group.');
    } else {
        $id = $postdata['ledger_group_id'];
    }

    $group_name = trim($postdata['group_name']);
    $parent_ledgerhead = trim($postdata['parent_ledgerhead']);
    $main_ledgerhead = null; // cannot be null
    $parent_lvl1 = null; // can be null
    $parent_lvl2 = null;
    $parent_lvl3 = null;
    $parent_lvl4 = null;
    $parent_lvl5 = null;

    // Check if the group exists
    $sql_count = "SELECT id, group_name, main_ledgerhead FROM unmarked_transaction_type_group WHERE id = ? AND institution_id = ? ";
    $stmt_count = $conn_rjllearning_specific->prepare($sql_count);
    $stmt_count->execute([$id, $institution_id]);
    $result_count = $stmt_count->fetch();
    
    if (count($result_count) < 1) {
        errorreturn("Group Info not exists.");
    }

    $existing_group_name = $result_count['group_name'];
    $existing_main_ledgerhead = $result_count['main_ledgerhead'];

// With this null-safe version:
if (
    strtolower($existing_group_name ?? '') === "transport" &&
    strtolower($existing_main_ledgerhead ?? '') === "direct expenses"
) {
    errorreturn("Alteration is not allowed for the 'Transport' group under 'Direct Expenses'.");
}

// Similarly, update other strtolower() calls:
if (
    strtolower($existing_group_name ?? '') === "student_registration" &&
    strtolower($existing_main_ledgerhead ?? '') === "direct income"
) {
    errorreturn("Alteration is not allowed for the 'Student_Registration' group under 'Direct Income'.");
}

// And further down:
if (
    (strtolower($existing_group_name ?? '') === "transport" &&
    strtolower($group_name ?? '') !== "transport") ||
    (strtolower($existing_group_name ?? '') === "student_registration" &&
    strtolower($group_name ?? '') !== "student_registration")
) {
    errorreturn("The group name cannot be changed for 'Transport' or 'Student_Registration'.");
}

    
    // Check if the group name already exists
    $res = $conn_rjllearning_specific->prepare("SELECT * FROM unmarked_transaction_type_group WHERE id != ? AND institution_id = ? AND group_name = ?");
    $res->execute([$id, $institution_id, $group_name]);
    $result1 = $res->fetchAll();
    if (count($result1) >= 1) {
        errorreturn("Group Name already exists");
    }

    // Get the existing group name
    $sql1 = "SELECT group_name FROM unmarked_transaction_type_group WHERE id = ? AND institution_id = ?";
    $stmt = $conn_rjllearning_specific->prepare($sql1);
    $stmt->execute([$id, $institution_id]);
    $result1 = $stmt->fetchAll();
    $existing_group_name = $result1[0]['group_name'];

    // Determine the main ledger head
    $sql1 = "SELECT ledgerhead_name FROM ledgerheads WHERE ledgerhead_name = ?";
    $stmt = $conn_rjllearning->prepare($sql1);
    $stmt->execute([$parent_ledgerhead]);
    $result1 = $stmt->fetchAll();

    if (count($result1) == 1) {
        $main_ledgerhead = $parent_ledgerhead;
    } else {
        $sql1 = "SELECT group_name, main_ledgerhead, parent_lvl1, parent_lvl2, parent_lvl3, parent_lvl4, parent_lvl5 
                 FROM unmarked_transaction_type_group 
                 WHERE group_name = ? AND institution_id = ?";
        $stmt = $conn_rjllearning_specific->prepare($sql1);
        $stmt->execute([$parent_ledgerhead, $institution_id]);
        $result2 = $stmt->fetchAll();
        foreach ($result2 as $row1) {
            $main_ledgerhead = $row1['main_ledgerhead'];
            $parent_lvl1 = $parent_ledgerhead;
            $parent_lvl2 = $row1['parent_lvl1'];
            $parent_lvl3 = $row1['parent_lvl2'];
            $parent_lvl4 = $row1['parent_lvl3'];
            $parent_lvl5 = $row1['parent_lvl4'];
        }
    }

    // Update the group
    $sql = "UPDATE unmarked_transaction_type_group 
            SET group_name = ?, main_ledgerhead = ?, parent_lvl1 = ?, parent_lvl2 = ?, parent_lvl3 = ?, parent_lvl4 = ?, parent_lvl5 = ? 
            WHERE id = ? AND institution_id = ?";
    $stmt = $conn_rjllearning_specific->prepare($sql);
    $stmt->execute([$group_name, $main_ledgerhead, $parent_lvl1, $parent_lvl2, $parent_lvl3, $parent_lvl4, $parent_lvl5, $id, $institution_id]);

    // // Update related ledgers
    // $sql = "UPDATE act_ledgers 
    //         SET ledger_parent_group = ?, ledger_main_ledgerhead = ? 
    //         WHERE ledger_parent_group = ? AND institution_id = ? AND branch_id = ?";
    // $stmt = $conn_rjllearning_specific->prepare($sql);
    // $stmt->execute([$group_name, $main_ledgerhead, $existing_group_name, $institution_id, $branch_id]);

    // Update parent level groups
    if ($existing_group_name != $group_name) {
        for ($i = 1; $i <= 5; $i++) {
            $field = 'parent_lvl' . $i;
            $sql = "UPDATE unmarked_transaction_type_group 
                    SET $field = ? 
                    WHERE institution_id = ? AND $field = ?";
            $stmt = $conn_rjllearning_specific->prepare($sql);
            $stmt->execute([$group_name, $institution_id, $existing_group_name]);
        }
    }

    successreturn("Updated group info Successfully");
}

