<?php
	include_once __DIR__ . '/../config/dbconn.php';
	date_default_timezone_set('Asia/Manila');

	// Get JSON input
	$json = file_get_contents('php://input');
	$data = json_decode($json);
	
	// Get post data
	$trans = $data->trans;

	if ($trans === "getDonationEvents") { 
		$location = isset($data->location) ? $data->location : '';
			
		// Prepare the SQL query
		$sql = "SELECT * 
				FROM events 
				WHERE status <> 'Done' ";
		if($location!=''){
			$sql .= " AND location='$location' ";
		}
		$sql .= " ORDER BY created_at DESC";

		// Execute the query
		$runQuery = mysqli_query($con, $sql);
		$count_rows = mysqli_num_rows($runQuery);

		// If rows are found, return them as JSON
		if ($count_rows > 0) {
			$results = [];
			while ($record = mysqli_fetch_assoc($runQuery)) {
				$results[] = $record;
			}
			// Creating the response object
			$response = (object)null;
			$response->results = $results;
			$response->title = 'Successful';
			$response->description = 'Filter successfully!';
			$response->response_code = "0";
		} else {
			$response = (object)null;
			$response->title = 'Failed';
			$response->description = 'No record found!';
			$response->response_code = '2';
		}
		
	} else if($trans === "getDonationRequest") { 
		
		// Extract fields from the decoded JSON
		$location = isset($data->location) ? $data->location : '';
			
		// Prepare the SQL query
		$sql = "SELECT *,
					(SELECT CONCAT(t.fname,' ',t.lname) FROM member t WHERE t.doc_number = member_id LIMIT 1) AS member_name,
					(SELECT t.email FROM member t WHERE t.doc_number = member_id LIMIT 1) AS member_email,
					(SELECT t.phone FROM member t WHERE t.doc_number = member_id LIMIT 1) AS member_phone,
					(SELECT CONCAT(t.location_st,' ',t.location_brgy,', ',t.location_city,', ',t.location_prov) FROM member t WHERE t.doc_number = member_id LIMIT 1) AS member_address,
					(SELECT t.title FROM material_items t WHERE t.id = item_id LIMIT 1) AS item_desc
				FROM request 
				WHERE status = 'Approved' ";
		if($location!=''){
			$sql .= " AND location='$location' ";
		}
		$sql .= " ORDER BY created_at DESC";

		// Execute the query
		$runQuery = mysqli_query($con, $sql);
		$count_rows = mysqli_num_rows($runQuery);

		// If rows are found, return them as JSON
		if ($count_rows > 0) {
			$results = [];
			while ($record = mysqli_fetch_assoc($runQuery)) {
				$results[] = $record;
			}
			// Creating the response object
            $response = new stdClass();
			$response->results = $results;
			$response->title = 'Successful';
			$response->description = 'Filter successfully!';
			$response->response_code = "0";
		} else {
            $response = new stdClass();
			$response->title = 'Failed';
			$response->description = 'No data found!';
			$response->response_code = '2';
		}
			
	} else if ($trans == "addDonationClaimed") {
		// Extract fields from the decoded JSON 
		$reference 		= $data->reference;
		$member_id 		= $data->member_id;
		$origin			= $data->origin;
		$origin_id		= $data->origin_id;
		$donation_type	= $data->donation_type;
		$item			= $data->item;
		$item_quantity	= $data->item_quantity;
		$amount 		= $data->amount;
		$location 		= $data->location;
		$claimed_date 	= date('Y-m-d H:i:s', strtotime($data->claimed_date));
		$released_by 	= $data->released_by;
		
		$flag = false;
		$sql = "";
		$record = "";

		if($origin=="Event"){
			$sql = "SELECT *
					FROM events 
					WHERE id = '$origin_id'
					LIMIT 1 
				"; 
			// Execute the query
			$runQuery = mysqli_query($con, $sql);
			$count_rows = mysqli_num_rows($runQuery);

			// If rows are found, return them as JSON
			if ($count_rows > 0) {
				$record = mysqli_fetch_assoc($runQuery); 
				
				if($record["status"] == "Done") {
					$flag = true;
				} else if($record["donation_type"]=="Material"){
					if($record["item_quantity"] <= $record["item_claimed"]) {
						$flag = true;
					}
				} else {  
					if($record["total_amount"] <= $record["amount_claimed"]) {
						$flag = true;
					}
				}
			} 
		} else {   
			$sql = "SELECT *
					FROM request 
					WHERE id = '$origin_id'
					LIMIT 1 
				"; 
			// Execute the query
			$runQuery = mysqli_query($con, $sql);
			$count_rows = mysqli_num_rows($runQuery);

			// If rows are found, return them as JSON
			if ($count_rows > 0) {
				$record = mysqli_fetch_assoc($runQuery); 
				
				if($record["status"] == "Claimed") {
					$flag = true;
				}
			}
		}
		   
		if (!$flag) {
			// Default status for new request
			if($donation_type=="Material"){
				$amount = 0;
			} else {  
				$item = '';
				$item_quantity = 0; 
			}

			// Prepare SQL query for inserting donation request
			$sql = "INSERT INTO member_claimed 
					( 
						reference, member_id, origin, origin_id, donation_type, item, item_quantity, amount, location,  claimed_date, created_at
					) VALUES (
						'$reference', '$origin', '$origin_id', '$donation_type', '$item','$item_quantity','$amount','$location','$claimed_date', Now()
					) 
				";

			// Execute the query
			if (mysqli_query($con, $sql)) {
				if($origin=="Event"){
					$new_item_quantity = 0;
					$new_amount = 0;
					$new_status = "Started";
					if($donation_type=="Material"){
						$new_amount = 0;
						$new_item_quantity	= $record["item_claimed"] + $item_quantity;
						if($new_item_quantity>=$record["item_quantity"]){
							$new_status = "Done";
						}
					} else {  
						$new_item = '';
						$new_item_quantity = 0; 
						$new_amount	= $record["amount_claimed"] + $amount;
						if($new_amount>=$record["total_amount"]){
							$new_status = "Done";
						}
					} 
					
					// Prepare SQL query for updating  requests
					$sql = "UPDATE events 
							SET 
								status = '$new_status', 
								item_claimed = '$new_item_quantity', 
								amount_claimed = '$new_amount'
							WHERE id = '$origin_id' 
						";
					mysqli_query($con, $sql);
				} else {  
					$sql = "UPDATE request 
							SET 
								status = 'Claimed', 
								claimed_date = '$claimed_date', 
								claimed_by = '$member_id', 
								released_date = '$claimed_date', 
								released_by = '$released_by'
							WHERE id = '$origin_id'
						";
					mysqli_query($con, $sql);
				}

				$response = new stdClass();
				$response->title = 'Request Successful';
				$response->description = 'Your request has been processed successfully!';
				$response->response_code = '0';
			} else {
				$response = new stdClass();
				$response->title = 'Request Failed';
				$response->description = 'We encountered an error while processing your request. Please try again!';
				$response->response_code = '3';
			} 
			
		} else {
			$response = new stdClass();
			$response->title = 'Request Failed';
			$response->description = 'We encountered an error while processing your request. Please try again!';
			$response->response_code = '3';
		} 

	} else if($trans === "getClaimedDetails") {  
		// Extract fields from the decoded JSON
		$reference 		= $data->reference;
		$member_id 		= $data->member_id;
		$origin			= $data->origin;
		$origin_id		= $data->origin_id;
			
		// Prepare the SQL query
		$sql = "SELECT *,
					(SELECT CONCAT(t.fname,' ',t.lname) FROM member t WHERE t.doc_number = member_id LIMIT 1) AS member_name,
					(SELECT t.email FROM member t WHERE t.doc_number = member_id LIMIT 1) AS member_email,
					(SELECT t.phone FROM member t WHERE t.doc_number = member_id LIMIT 1) AS member_phone,
					(SELECT CONCAT(t.location_st,' ',t.location_brgy,', ',t.location_city,', ',t.location_prov) FROM member t WHERE t.doc_number = member_id LIMIT 1) AS member_address,
			";
		if($origin=='1'){
			$sql .= " (SELECT t.title FROM events t WHERE t.id = origin_id LIMIT 1) AS event_title, ";
			$sql .= " (SELECT t.name FROM events t WHERE t.id = origin_id LIMIT 1) AS donor_name, ";
			$sql .= " (SELECT t.address FROM events t WHERE t.id = origin_id LIMIT 1) AS donor_address, ";
			$sql .= " (SELECT t.purpose FROM events t WHERE t.id = origin_id LIMIT 1) AS purpose ";
		} else {
			$sql .= " ('') AS event_title, ";
			$sql .= " ('') AS donor_name, ";
			$sql .= " ('') AS donor_address, ";
			$sql .= " (SELECT t.purpose FROM request t WHERE t.id = origin_id LIMIT 1) AS purpose ";
		}
		$sql .= "FROM member_claimed 
				 WHERE reference = '$reference' ";
		$sql .= "ORDER BY created_at DESC LIMIT 1";

		// Execute the query
		$runQuery = mysqli_query($con, $sql);
		$count_rows = mysqli_num_rows($runQuery);

		// If rows are found, return them as JSON
		if ($count_rows > 0) {
			$results = mysqli_fetch_assoc($runQuery);
			// Creating the response object
			$response = new stdClass();
			$response->results = $results;
			$response->title = 'Successful';
			$response->description = 'Filter successfully!';
			$response->response_code = "0";
		} else {
			$response = new stdClass();
			$response->title = 'Failed';
			$response->description = 'No data found!';
			$response->response_code = '2';
		}

	} else {
		// If invalid trans value
		$response = new stdClass();
		$response->title = 'Critical Error!';
		$response->description = 'Invalid transaction.';
		$response->response_code = '1';
	}

	// Send the response as JSON
	header('Content-Type: application/json');
	echo json_encode($response);
