<?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';
require_once $base_path_to_learning . 'functions/common_functions.php';
//db access function
require_once $base_path_to_learning_resources . 'includes/specific_db_config.php';

//aws image store
require_once $base_path_to_learning_resources . 'lib/bucket_functions/bucket_functions.php';
require_once $base_path_to_learning . "vendor/autoload.php";

require_once $base_path_to_learning_resources . 'includes/aws_bucket_config.php';

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

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

$specific_db_name = validateUserAccess($conn_rjllearning, $userid, $role, $workspace_id,["principal"], null);
if (!$specific_db_name) {
    errorreturn('Access denied.');
} else {
    $conn_rjllearning_specific = dynamicDbConnection($specific_db_name);
}

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 getvendor($conn_rjllearning_specific, $institution_id, $search_string, $branch_id, $transaction_type_group, $from_date, $to_date, $transport_data, $limit, $offset)
{
  if ($transport_data == 1) {
    $sql_transport = "SELECT id FROM unmarked_transaction_type_group WHERE institution_id = ? AND LOWER(group_name) LIKE ? LIMIT 1";
    $stmt_tr = $conn_rjllearning_specific->prepare($sql_transport);
    $stmt_tr->execute([$institution_id, '%transport%']);
    $transport_category_id = $stmt_tr->fetch();
    $vehicle_category_id = $transport_category_id ? $transport_category_id['id'] : null;
  } else {
    $vehicle_category_id = null;
  }

//   $vehicle_category_id = $transport_category_id ? $transport_category_id['id'] : null;
//   errorreturn($vehicle_category_id);

  $sql_count = "SELECT  count(*) as total_rows FROM unmarked_transaction where institution_id =?";
  $array_params = [$institution_id];
  if ($branch_id == 'all') {
    $sql_count .= "";
  } else if ($branch_id !=  null  && $branch_id != 'all') {
    $sql_count .= " AND branch_id = ? ";
    $array_params[] = $branch_id;
  }
  if (!empty($search_string)) { // Check if the search string is provided
    $sql_count .= " AND vendor_name LIKE ? ";
    $array_params[] = "%" . $search_string . "%"; // Add wildcard for partial matching
  }

  if (!empty($vehicle_category_id)) { // Check if the search string is provided
    $sql_count .= " AND category_id = ? ";
    $array_params[] = $vehicle_category_id; // Add wildcard for partial matching
  }

  if ($from_date != null && $to_date != null) {
    $sql_count .= " AND date_created BETWEEN ? AND ?";
    $array_params[] = $from_date;
    $array_params[] = $to_date;
  }
  if (!empty($transaction_type_group) && in_array($transaction_type_group, ['expense', 'receipt'])) {
    $sql_count .= " AND transaction_type_group = ? ";
    $array_params[] = $transaction_type_group;
  }

  $stmt = $conn_rjllearning_specific->prepare($sql_count);
  $stmt->execute($array_params);
  $result_total = $stmt->fetchAll();
  $totalrows = $result_total[0]['total_rows'];

  if ($limit == 'all') {

    $sql_session = "SELECT * FROM   unmarked_transaction  where institution_id =? ";
    $array_params = [$institution_id];
    if ($branch_id == 'all') {
      $sql_session .= "";
    } else if ($branch_id !=  null  && $branch_id != 'all') {
      $sql_session .= " AND branch_id = ? ";
      $array_params[] = $branch_id;
    }


    if (!empty($search_string)) { // Check if the search string is provided
      $sql_session .= " AND vendor_name LIKE ? ";
      $array_params[] = "%" . $search_string . "%"; // Add wildcard for partial matching
    }

    if (!empty($vehicle_category_id)) { // Check if the search string is provided
      $sql_session .= " AND category_id = ? ";
      $array_params[] = $vehicle_category_id; // Add wildcard for partial matching
    }
    if ($from_date != null && $to_date != null) {
      $sql_session .= " AND date_created BETWEEN ? AND ?";
      $array_params[] = $from_date;
      $array_params[] = $to_date;
    }
    if (!empty($transaction_type_group) && in_array($transaction_type_group, ['expense', 'receipt'])) {
      $sql_session .= " AND transaction_type_group = ? ";
      $array_params[] = $transaction_type_group;
    }
    $sql_session .=  "order by date_created desc, time_created desc";
  } else {
    $sql_session = "SELECT * FROM  unmarked_transaction WHERE institution_id = ?";
    $array_params = [$institution_id];

    if ($branch_id == 'all') {
      $sql_session .= "";
    } else if ($branch_id !=  null  && $branch_id != 'all') {
      $sql_session .= " AND branch_id = ? ";
      $array_params[] = $branch_id;
    }
    if ($from_date != null && $to_date != null) {
      $sql_session .= " AND date_created BETWEEN ? AND ?";
      $array_params[] = $from_date;
      $array_params[] = $to_date;
    }

    if (!empty($search_string)) { // Check if the search string is provided
      $sql_session .= " AND vendor_name LIKE ? ";
      $array_params[] = "%" . $search_string . "%"; // Add wildcard for partial matching
    }

    if (!empty($vehicle_category_id)) { // Check if the search string is provided
      $sql_session .= " AND category_id = ? ";
      $array_params[] = $vehicle_category_id; // Add wildcard for partial matching
    }
    if (!empty($transaction_type_group) && in_array($transaction_type_group, ['expense', 'receipt'])) {
      $sql_session .= " AND transaction_type_group = ? ";
      $array_params[] = $transaction_type_group;
    }
    $sql_session .= " order by date_created desc, time_created desc ";
    $sql_session .= "LIMIT " . $offset . " ," . $limit . "";
  }
  $stmt = $conn_rjllearning_specific->prepare($sql_session);
  $stmt->execute($array_params);
  $session_rows = $stmt->fetchAll();



  $array_return = [];

  foreach ($session_rows as $rows) {

    $transaction_type = null; // Default value
    if (!empty($rows['inventory_transaction_id'])) {
      $sql = "SELECT transaction_type FROM inventory_transactions WHERE id = ?";
      $stmt = $conn_rjllearning_specific->prepare($sql);
      $stmt->execute([$rows['inventory_transaction_id']]);
      $transaction_type_info = $stmt->fetch(PDO::FETCH_ASSOC);

      if ($transaction_type_info) {
        $transaction_type = $transaction_type_info['transaction_type'];
      }
    }

    if ($rows['attachment'] != null and !is_array($rows['attachment'])) {
      $data_attachment = json_decode($rows['attachment'], true);
      $filePath = $data_attachment['filepath'];
      $originalName = $data_attachment['original_name'];
      $aws_attachment = displayFileFromS3($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $filePath);
      $final_array_attachment   = array("filepath" => $aws_attachment, "original_name" => $originalName);
    } else {
      $final_array_attachment =  null;
    }

    $category_id = $rows['category_id']; // Vendor ID to use for fetching details

    // Validate the vendor ID
    if (empty($category_id)) {
      errorreturn("Category ID is required.");
    }

    // Query to fetch name and contact_number from vendor table
    $fetch_vendor_sql = "SELECT id,group_name FROM unmarked_transaction_type_group WHERE id = ?";

    // Prepare and execute the query
    $stmt = $conn_rjllearning_specific->prepare($fetch_vendor_sql);
    $stmt->execute([$category_id]);

    // Fetch the result
    $vendor_info = $stmt->fetch(PDO::FETCH_ASSOC);

    // Check if vendor info was found
    if ($vendor_info) {
      // Successfully fetched vendor info
      $group_id = $vendor_info['id'];
      $group_name = $vendor_info['group_name'];
    } else {
      $group_id = null;
      $group_name = null;
    }
    // $expense_id = json_decode($rows['expense_id'], true);
    $branch_info = getBranchInfo($conn_rjllearning_specific, $institution_id, $rows['branch_id']);
    $array_return[] = array(
      'expense_id' => $rows['id'],
      'institution_id' => $rows['institution_id'],
      'vendor_name' => $rows['vendor_name'],
      'branch_id' => $branch_info,
      'category_id' => $vendor_info,
      // 'expense_id' => $rows['expense_id'],
      'amount' => $rows['amount'],
      'date' => $rows['date'],
      'transaction_reference_id' => $rows['transaction_reference_id'],
      'payment_bool' => $rows['payment_bool'],
      'payment_method' => $rows['payment_method'],
      'paid_on' => $rows['paid_on'],
      'attachment' => $final_array_attachment,
      'remarks' => $rows['remarks'],
      'initiated_by' => $rows['initiated_by'],
      'last_edit' => $rows['last_edit'],
      'last_edit_at' => $rows['last_edit_at'],
      'recorded_in_finware' => $rows['recorded_in_finware'],
      'transaction_type_group' => $rows['transaction_type_group'],
      'transaction_type' => $transaction_type,
      'approved_by' => $rows['approved_by'],
      'created_by' => $rows['created_by'],
      'time_created' => $rows['time_created'],
      'date_created' => $rows['date_created']
    );
  }

  if (count($session_rows) < 50) {

    return array('total_rows' => $totalrows, 'vehicle_expense_info' => $array_return, 'maxreach' => 1);
  } else {
    return array('total_rows' => $totalrows, 'vehicle_expense_info' => $array_return, 'maxreach' => 0);
  }
}


if ($postdata['function_type'] == "fetch_vehicle_expense") {
   
    $check_academic_confirmation = CheckAcademicSession($conn_rjllearning_specific, $institution_id);
    if ($check_academic_confirmation != true) {
        errorreturn("Access denied. Academic session not active.");
    }
   
    if (isset($postdata['transaction_type_group']) and !empty($postdata['transaction_type_group'])) {
        $transaction_type_group = trim($postdata['transaction_type_group']);
    } else {
        $transaction_type_group = null;
    }


    if (isset($postdata['search_string']) and !empty($postdata['search_string'])) {
        $search_string = trim($postdata['search_string']);
    } else {
        $search_string = null;
    }
     if (isset($postdata['from_date']) and !empty($postdata['from_date'])) {
        $from_date = trim($postdata['from_date']);
    } else {
        $from_date = null;
    }
    if (isset($postdata['to_date']) and !empty($postdata['to_date'])) {
        $to_date = trim($postdata['to_date']);
    } else {
        $to_date = null;
    }
        if (isset($postdata['transport_data']) and !empty($postdata['transport_data'])) {
        $transport_data = trim($postdata['transport_data']);
    } else {
        $transport_data =0;
    }
    
    if ($from_date > $to_date) {
    errorreturn('Invalid Date format');
    }
    $limit = $postdata['limit'];

    $page = (isset($postdata['page']) and isNumeric($postdata['page'])) ? (int)$postdata['page'] : 1;

    if ($limit != 'all') {
        $offset = ($page - 1) * $limit;
        $success['data'] = getvendor($conn_rjllearning_specific, $institution_id, $search_string, $branch_id,$transaction_type_group,$from_date,$to_date, $transport_data, $limit, $offset);
        successreturn($success);
    } else {
        $success['data'] = getvendor($conn_rjllearning_specific, $institution_id, $search_string, $branch_id,$transaction_type_group,$from_date,$to_date,$transport_data,  $limit, null);
        successreturn($success);
    }
}
