<?php
/*
**Enter category data in datatable
*/
if (! function_exists('get_new_cats')) {
	function get_new_cats()
	{
		if (!current_user_can('manage_custom_data')) {
			wp_die(__('You do not have permission to manage this data.', 'passwords-manager'));
		}

		global $wpdb, $posts;
		$prefix = $wpdb->prefix;

		if (isset($_POST) && !empty($_POST)) {
			// Extract variables from $_POST safely
			$security_nonce = isset($_POST['security_nonce']) ? sanitize_text_field($_POST['security_nonce']) : '';
			$search = isset($_POST['search']) ? $_POST['search'] : [];
			$start = isset($_POST['start']) ? $_POST['start'] : 0;
			$length = isset($_POST['length']) ? $_POST['length'] : 10;
			$order = isset($_POST['order']) ? $_POST['order'] : [];

			if (wp_verify_nonce($security_nonce, 'security_nonce') && is_user_logged_in() && is_admin()) {
				// Count the categories and passwords
				$tr_cate = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM {$prefix}pms_category"));
				$tr_pass = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM {$prefix}pms_passwords"));

				// Sanitize and validate inputs
				$serachString = isset($search['value']) ? sanitize_text_field($search['value']) : '';
				$startLimit = filter_var($start, FILTER_VALIDATE_INT);
				$lengthLimit = filter_var($length, FILTER_VALIDATE_INT);
				$rowCount = $startLimit;

				// Initialize the search query condition
				$searchQuery = '';
				if (!empty($serachString)) {
					// Use prepared statements for search to avoid SQL injection
					$searchQuery = $wpdb->prepare(' WHERE category LIKE %s', '%' . $wpdb->esc_like($serachString) . '%');
				} else {
					$searchQuery = ' WHERE 1 ';
				}

				// Check if categories and passwords exist
				if ($tr_cate > 0 && $tr_pass > 0) {

					// Construct the join query
					$query3 = "SELECT {$prefix}pms_category.category, {$prefix}pms_category.category_color, {$prefix}pms_category.id, 
								COUNT({$prefix}pms_passwords.category_id) AS Total 
								FROM {$prefix}pms_category 
								LEFT JOIN {$prefix}pms_passwords ON {$prefix}pms_category.id = {$prefix}pms_passwords.category_id 
								{$searchQuery}
								GROUP BY {$prefix}pms_category.id 
								ORDER BY {$prefix}pms_category.id";

					// Sanitize order direction to avoid malicious input
					if (isset($order[0]['dir']) && in_array($order[0]['dir'], ['ASC', 'DESC'])) {
						$query3 .= ' ' . sanitize_text_field($order[0]['dir']);
					}

					// Apply limits (pagination)
					if (isset($lengthLimit) && ($lengthLimit != -1) && isset($startLimit)) {
						$query3 .= ' LIMIT ' . $startLimit . ', ' . $lengthLimit;
					} else {
						$query3 .= ' LIMIT 0, 10';
					}

					// Execute the query
					$trs3 = $wpdb->get_results($query3);

					// Prepare the response data
					$data = array();
					$array = json_decode(json_encode($trs3), True);

					// Row count initialization
					$rowCount = $startLimit;
					foreach ($array as $row) {
						$rowCount++;
						$id = filter_var($row['id'], FILTER_VALIDATE_INT);
						$sub_array = array();
						$sub_array[] = filter_var($rowCount, FILTER_VALIDATE_INT);
						$sub_array[] = '<span class="colorcode1 badge" style="background-color:' . esc_attr($row['category_color']) . '">' . ucfirst(esc_html($row['category'])) . '</span>';
						$sub_array[] = filter_var($row['Total'], FILTER_VALIDATE_INT);
						$sub_array[] = '<div class="act_box">
											<a href="javascript:void(0);" name="upcate" id="' . $id . '" class="upcate"><span class="dashicons dashicons-edit text-warning"></span></a>&nbsp;
											<a href="#" name="delete" class="delete" id="' . $id . '"><span class="dashicons dashicons-trash text-danger"></span></a>
										  </div>';
						$data[] = $sub_array;
					}

					// Prepare and output the response
					$output = array(
						"recordsTotal"   => filter_var($tr_cate, FILTER_VALIDATE_INT),
						"recordsFiltered" => filter_var($tr_cate, FILTER_VALIDATE_INT),
						"data"            => $data
					);

					echo json_encode($output);
					die;
				} else {

					// Prepare the base query
					$query = "SELECT * FROM {$prefix}pms_category";

					// Check if a search string is provided and modify the query accordingly
					if (isset($serachString) && !empty($serachString)) {
						$query .= $wpdb->prepare(" WHERE category LIKE %s", '%' . $serachString . '%');
					} else {
						$query .= " WHERE 1";
					}

					// Check if length and start limits are set for pagination
					if (isset($lengthLimit) && ($lengthLimit != -1) && isset($startLimit)) {
						$query .= $wpdb->prepare(" LIMIT %d, %d", $startLimit, $lengthLimit);
					} else {
						$query .= " LIMIT 0, 10";
					}

					// Execute the query
					$qrs = $wpdb->get_results($query);

					// Initialize the data array
					$data = array();
					$rowCount = 0; // Initialize row count

					// Convert results into an array for processing
					$array = json_decode(json_encode($qrs), true);

					// Loop through the results and build the output data
					foreach ($array as $row) {
						$rowCount++;
						$id = filter_var($row['id'], FILTER_VALIDATE_INT);

						// Prepare the sub-array for each row
						$sub_array = array();
						$sub_array[] = filter_var($rowCount, FILTER_VALIDATE_INT); // Row number
						$sub_array[] = '<span class="colorcode1 badge" style="background-color:' . esc_attr($row['category_color']) . '">' . ucfirst(esc_html($row['category'])) . '</span>';
						$sub_array[] = 0; // This field seems to be hardcoded as '0'
						$sub_array[] = '<div class="act_box">
                    <a href="#" name="upcate" id="' . esc_html($id) . '" class="upcate"><span class="dashicons dashicons-edit text-warning"></span></a>&nbsp;
                    <a href="#" name="delete" class="delete" id="' . esc_html($id) . '"><span class="dashicons dashicons-trash text-danger"></span></a>
                    </div>';

						// Append the sub-array to the data
						$data[] = $sub_array;
					}

					// Prepare the output in the expected format
					$output = array(
						"recordsTotal"    => filter_var($tr_cate, FILTER_VALIDATE_INT),
						"recordsFiltered" => filter_var($tr_cate, FILTER_VALIDATE_INT),
						"data"            => $data
					);

					// Output the results as a JSON response
					echo json_encode($output);
					die;
				}
			}
		}
	}
}


/*
**add new category
*/
if (! function_exists('post_new_cats')) {

	function post_new_cats()
	{
		if (!current_user_can('manage_custom_data')) {
			wp_die(__('You do not have permission to manage this data.', 'passwords-manager'));
		}

		global $wpdb;
		$prefix = $wpdb->prefix;

		// Check if POST request is not empty
		if (isset($_POST) && !empty($_POST)) {

			// Safely extract POST variables
			$btn_action  = isset($_POST['btn_action']) ? sanitize_text_field($_POST['btn_action']) : '';
			$security_nonce = isset($_POST['security_nonce']) ? sanitize_text_field($_POST['security_nonce']) : '';
			$category = isset($_POST['category']) ? sanitize_text_field($_POST['category']) : '';
			$category_id = isset($_POST['category_id']) ? absint($_POST['category_id']) : 0;
			$category_color = isset($_POST['category_color']) ? sanitize_hex_color($_POST['category_color']) : '';

			// Ensure nonce verification, user is logged in, and admin role
			if (wp_verify_nonce($security_nonce, 'security_nonce') && is_user_logged_in() && is_admin()) {

				// Handling "Add" action
				if ($btn_action === 'Add') {
					$cat_name = strtolower($category);

					// Check for special characters in category name
					if (preg_match('/[\'^!"|~£$%&*()}{@#~?><>,|=_+¬-]/', $cat_name) || empty($cat_name)) {
						$resp['ecode'] = "special character";
						echo json_encode($resp);
						die;
					} else {
						// Check if category already exists
						$query_cate = $wpdb->get_var($wpdb->prepare("SELECT category FROM {$prefix}pms_category WHERE category = %s", $cat_name));

						if ($query_cate) {
							$resp['al_exist'] = $cat_name;
						} else {
							// Insert new category
							$result = $wpdb->insert(
								$prefix . "pms_category",
								array(
									'category' => $cat_name,
									'category_color' => $category_color
								),
								array('%s', '%s')
							);

							// Check the result
							$resp['resp'] = $result ? "Success" : "Error";
						}
					}
					echo json_encode($resp);
					die;
				}

				// Handling "Edit" action
				elseif ($btn_action === 'Edit') {
					$cat_name = strtolower($category);

					// Check for special characters in category name
					if (preg_match('/[\'^!"|~£$%&*()}{@#~?><>,|=_+¬-]/', $cat_name) || empty($cat_name)) {
						$resp['ecode'] = "special character";
						echo json_encode($resp);
						die;
					} else {
						// Check if the category exists
						$query_cate = $wpdb->get_var($wpdb->prepare("SELECT category FROM {$prefix}pms_category WHERE category = %s", $cat_name));

						if ($query_cate && $cat_name !== $query_cate) {
							// Update the category
							$result = $wpdb->update(
								$prefix . "pms_category",
								array(
									'category' => $cat_name,
									'category_color' => $category_color
								),
								array('id' => $category_id),
								array('%s', '%s'),
								array('%d')
							);

							// Check the result
							$resp['resp'] = $result !== false ? "Success" : "Error";
						} else {
							// If the name is the same, only update the color
							$result = $wpdb->update(
								$prefix . "pms_category",
								array('category_color' => $category_color),
								array('id' => $category_id),
								array('%s'),
								array('%d')
							);

							$resp['al_exist'] = "Exists";
						}
					}
					echo json_encode($resp);
					die;
				}

				// Handling "Delete" action
				elseif ($btn_action === 'Delete') {
					if ($category_id > 0) {
						// Delete the category
						$qry_dlt_cate = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM {$prefix}pms_passwords WHERE category_id = %d", $category_id));

						if ($qry_dlt_cate > 0) {
							// Delete passwords associated with the category first
							$wpdb->delete($prefix . "pms_passwords", array('category_id' => $category_id), array('%d'));
						}

						// Now delete the category
						$result = $wpdb->delete($prefix . "pms_category", array('id' => $category_id), array('%d'));

						// Check the result
						if ($result !== false) {
							echo json_encode(array('resp' => 'Success'));
						} else {
							echo json_encode(array('resp' => 'Error'));
						}
					} else {
						echo json_encode(array('resp' => 'Invalid category ID'));
					}
					die;
				}
			}
		}
	}
}

/*
**Fetch category detail in database
*/
if (!function_exists('edit_cats')) {
	function edit_cats()
	{
		if (!current_user_can('manage_custom_data')) {
			wp_die(__('You do not have permission to manage this data.', 'passwords-manager'));
		}

		global $wpdb;
		$prefix = $wpdb->prefix;

		if (isset($_POST) && !empty($_POST)) {
			$category_id = isset($_POST['category_id']) ? absint($_POST['category_id']) : 0;
			$security_nonce = isset($_POST['security_nonce']) ? sanitize_text_field($_POST['security_nonce']) : '';

			if ($category_id && wp_verify_nonce($security_nonce, 'security_nonce') && is_user_logged_in() && is_admin()) {
				// Use the correct query with "=" for exact match
				$fetch_cate = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$prefix}pms_category WHERE id = %d", $category_id));

				if ($fetch_cate) {
					$output['category_name'] = esc_html(ucwords($fetch_cate->category));
					$output['category_color'] = esc_html($fetch_cate->category_color);
					echo json_encode($output);
				} else {
					// Handle case where no category is found
					echo json_encode(['error' => 'Category not found']);
				}
			}
			die;
		}
	}
}

/*
**Add actions category detail
*/
add_action('wp_ajax_get_new_cats', 'get_new_cats');
add_action('wp_ajax_post_new_cats', 'post_new_cats');
add_action('wp_ajax_edit_cats', 'edit_cats');
