-- Function: calculate_distance_imu_mobile()

-- DROP FUNCTION calculate_distance_imu_mobile();

CREATE OR REPLACE FUNCTION calculate_distance_imu_mobile()
  RETURNS trigger AS
$BODY$
DECLARE	
	databaseid_val 		mobile_connected_dev.hash%TYPE;
	
	x_pos_last		mobile_pos_step.x_pos_step%TYPE;
	y_pos_last		mobile_pos_step.y_pos_step%TYPE;
	x_pos_new_step		mobile_pos_step.x_pos_step%TYPE;
	y_pos_new_step		mobile_pos_step.y_pos_step%TYPE;
	x_pos_new_int		mobile_pos_step.x_pos_step%TYPE;
	y_pos_new_int		mobile_pos_step.y_pos_step%TYPE;
	timestamp_last		mobile_pos_step.timestamp%TYPE;
	
	timestamp_val		accelerometer_data.timestamp%TYPE;
	y_acc_val		accelerometer_data.value_y%TYPE;
	
	ts_last			accelerometer_data.timestamp%TYPE;
	ts_before		accelerometer_data.timestamp%TYPE;

	y_min			accelerometer_data.value_y%TYPE;	
	z_min			accelerometer_data.value_z%TYPE;

	
	y_treshold		real := -1;
	z_treshold		real := 3;
	
	stepcount_y		double precision :=0;
	stepcount_z 		double precision :=0;
	step_length 		double precision := 0.75;

	stepcountsum 		double precision :=0;
	sumAcc_y_0		accelerometer_data.value_y%TYPE:=0;
	sumAcc_y_1		accelerometer_data.value_y%TYPE:=0;
	sumAcc_y		accelerometer_data.value_y%TYPE:=0;
	acc_val			accelerometer_data.value_y%TYPE;
	sumAcc			double precision :=0;
	
	
	azimuth_val		orientation_data.azimuth%TYPE;

	map_rotation		double precision := 45;

	iterace 		real:=1;
	withoutWifiPos		real:=0;



	source_address_val	mobile_rssi_current_copy.source_address%TYPE;
	destination_address_val	mobile_rssi_current_copy.destination_address%TYPE;
	second_source_address	mobile_rssi_current_copy.destination_address%TYPE; 
	mobile_rssi		mobile_rssi_current_copy.rssi_avg%TYPE;

	mobile_anthena		mobile_rssi_current_copy.rssi_avg%TYPE:= -5;
	mobile_anthena_1	double precision:= 1.1574;

	conn_rssi 		ap_rssi_current_copy.rssi_avg%TYPE;
	conn_eta		ap_rssi_current_copy.eta%TYPE;
	conn_dist		ap_rssi_current_copy.distance_real%TYPE;
	
	rssi_0			ap_rssi_current_copy.rssi_avg%TYPE 	:= -70.8333333333333;
	eta_0			ap_rssi_current_copy.eta%TYPE 		:= 6.55334973335266;
	dist_0			ap_rssi_current_copy.distance_real%TYPE := 12.2733497619629;

	distance_real_val	mobile_rssi_current_copy.distance_real%TYPE;
	
	distance_0		mobile_rssi_current_copy.distance_calc_0%TYPE;
	distance_1		mobile_rssi_current_copy.distance_calc_1%TYPE;

	err_0			mobile_rssi_current_copy.distance_error_0%TYPE;
	err_1			mobile_rssi_current_copy.distance_error_1%TYPE;

	timestamp_global 	mobile_pos_step.timestamp%TYPE;

				
BEGIN
-- ZISKEJ DATABAZOVE ID ZE SOURCE MAC ADRESY
EXECUTE 'SELECT hash FROM mobile_connected_dev WHERE device_address_mac = $$'
	||NEW.source_address
	||'$$'
	INTO databaseid_val;

IF databaseid_val IS NULL
THEN 
	RETURN NULL;
END IF;

--ZISKEJ POSLEDNI ZNAMOU POLOHU 
EXECUTE 'SELECT x_pos_step, x_pos_step, timestamp FROM mobile_pos_step WHERE databaseid = $$'
	||databaseid_val
	||'$$ ORDER BY timestamp DESC LIMIT 1' 
	INTO x_pos_last, y_pos_last, timestamp_last;


timestamp_global =  (select extract('epoch' from current_timestamp)*1000);
IF timestamp_global - timestamp_last < 5000
THEN 
	
	RETURN NULL;
END IF;

IF 	x_pos_last IS NULL OR
	y_pos_last IS NULL OR
	timestamp_last IS NULL
THEN 
	withoutWifiPos = 1;
END IF;

	ts_last = timestamp_last;
	ts_before = timestamp_last;
	
--PROJDI ZAZNAMY ZRYCHLENI MEZI POSLEDNI ZNAMOU POZICI A SOUCASNYM CASEM A ZJISTI JESTLI BYL POHYB 	
FOR 	timestamp_val, y_acc_val  IN SELECT timestamp, value_y FROM accelerometer_data 
	WHERE timestamp >= timestamp_last AND timestamp < timestamp_global
	LOOP
	IF iterace = 1
		THEN
		ts_before = timestamp_val;
		iterace = 2;
	END IF;

	
	IF (timestamp_val - ts_last) > 500
	THEN 
		EXECUTE 'SELECT MIN(value_y), MAX(value_z) FROM accelerometer_data WHERE timestamp >= $$'
			|| ts_last
			|| '$$ AND timestamp < $$'
			|| timestamp_val
			|| '$$'
			INTO y_min, z_min;


		--KROKOVA VARIANTA
		IF y_min < y_treshold
		THEN
			stepcount_y = stepcount_y + 1;
		END IF;

		IF z_min > z_treshold
		THEN
			stepcount_z = stepcount_z + 1;
		END IF;

		ts_last = timestamp_val;
	END IF;
--INTEGRACNI VARIANTA

	IF 	y_acc_val > 0.06 OR y_acc_val < -0.06
	THEN
		ts_before = timestamp_val;
	END IF;
    
	IF 	y_acc_val > 0.02
	THEN
		sumAcc_y_0 = sumAcc_y_0 + (y_acc_val/2) * (timestamp_val-ts_before);
		ts_before = timestamp_val;
	END IF;
     
	IF  	y_acc_val < 0.02
	THEN
		sumAcc_y_1 = sumAcc_y_1 + (y_acc_val/2) * (timestamp_val-ts_before);
		ts_before = timestamp_val;
	END IF;

	END LOOP;

	
stepcountsum = (stepcount_y+stepcount_z)/2*step_length;

sumAcc_y_0 = sumAcc_y_0/2;
sumAcc_y_1 = sumAcc_y_1/2*(-1);


sumAcc = (sumAcc_y_0 + sumAcc_y_1)/2;

IF withoutWifiPos = 1
THEN 
	stepcountsum = 0;
	sumAcc = 0;
END IF;

--BYL ZJISTEN POHYB 
IF stepcountsum > 0 --OR sumAcc > 0 
THEN
	EXECUTE 'SELECT avg(azimuth) FROM orientation_data WHERE databaseid = $$'
	||databaseid_val
	||'$$ AND timestamp >= $$'
	|| timestamp_last
	|| '$$ AND timestamp < $$'
	|| timestamp_global
	|| '$$'
	INTO azimuth_val;

	x_pos_new_step = sin(radians(azimuth_val-map_rotation))* stepcountsum + x_pos_last;
	y_pos_new_step = cos(radians(azimuth_val-map_rotation))* stepcountsum + y_pos_last;
	
	x_pos_new_int = sin(radians(azimuth_val-map_rotation))* sumAcc + x_pos_last;
	y_pos_new_int = cos(radians(azimuth_val-map_rotation))* sumAcc + y_pos_last;
	

	INSERT INTO mobile_pos_step(timestamp, databaseid, x_pos_step,
		y_pos_step, x_pos_int, y_pos_int, type) VALUES (
		timestamp_global, 
		databaseid_val, 
		x_pos_new_step,
		y_pos_new_step,
		x_pos_new_int,
		y_pos_new_int,
		$$IMU$$);
ELSE
	
	FOR 	source_address_val, destination_address_val, mobile_rssi, distance_real_val 
	IN SELECT source_address, destination_address, rssi_avg, distance_real FROM mobile_rssi_current_copy
	LOOP			
		-- Vyber druhy nejlepsi APcko z mobile table
		EXECUTE	'SELECT mobile_rssi_current_copy.destination_address 
			FROM mobile_rssi_current_copy WHERE 
			source_address = $$'
			|| source_address_val 
			||'$$ AND destination_address != $$'
			||destination_address_val
			||'$$ ORDER BY mobile_rssi_current_copy.rssi_avg DESC LIMIT 1'
			INTO second_source_address;
		
		-- RSSI,DISTANCE,ETA FROM nejlepsi linka  A-B, B-A '-- ,mobile_rssi_current_copy.rssi_avg

		EXECUTE 'SELECT avg(ap_rssi_current_copy.rssi_avg), avg(ap_rssi_current_copy.distance_real), avg(ap_rssi_current_copy.eta)
			FROM ap_rssi_current_copy INNER JOIN mobile_rssi_current_copy ON 
			ap_rssi_current_copy.source_address = mobile_rssi_current_copy.destination_address WHERE
			mobile_rssi_current_copy.source_address = $$'
			|| source_address_val 
			||'$$ AND (ap_rssi_current_copy.destination_address = $$'
			||destination_address_val
			||'$$ OR ap_rssi_current_copy.source_address = $$'
			||destination_address_val
			||'$$ ) AND (ap_rssi_current_copy.destination_address = $$'
			|| second_source_address
			||'$$ OR ap_rssi_current_copy.source_address = $$'
			|| second_source_address
			||'$$)'
			INTO conn_rssi, conn_dist, conn_eta;
		
		
		IF  	conn_rssi IS NULL 
			OR conn_dist IS NULL 
			OR conn_eta IS NULL 
			OR mobile_rssi IS NULL
		THEN 
			UPDATE mobile_rssi_current_copy 
			SET 
			distance_calc_IMU_0 = distance_0,
			distance_calc_IMU_1 = distance_1,
			distance_error_IMU_0 = err_0,
			distance_error_IMU_1 = err_1
			WHERE source_address = source_address_val 
			AND destination_address = destination_address_val;

		ELSE
			--distance_0 = dist_0 * (10^((rssi_0 - mobile_rssi) / (10 * eta_0)));
			--distance_0 = dist_0 * (10^((rssi_0 - mobile_rssi - mobile_anthena) / (10 * eta_0)));
			distance_0 = dist_0 * (10^((rssi_0 - (mobile_rssi*mobile_anthena_1)) / (10 * eta_0)));
			
			--distance_1 = conn_dist * (10^((conn_rssi - mobile_rssi / (10 * conn_eta)));
			--distance_1 = conn_dist * (10^((conn_rssi - mobile_rssi - mobile_anthena) / (10 * conn_eta)));
			distance_1 = conn_dist * (10^((conn_rssi - (mobile_rssi*mobile_anthena_1)) / (10 * conn_eta)));

		IF 	distance_real_val IS NOT NULL
		THEN
			err_0 = ABS(distance_real_val - distance_0);
			err_1 = ABS(distance_real_val - distance_1);
			
			EXECUTE 'UPDATE mobile_rssi_current_copy 
			SET distance_calc_IMU_0 = $$'
			|| distance_0
			||'$$, distance_error_IMU_0 = $$'
			|| err_0
			||'$$, distance_calc_IMU_1 = $$'
			|| distance_1
			||'$$, distance_error_IMU_1 = $$'
			|| err_1
			||'$$ WHERE source_address = $$'
			|| source_address_val 
			||'$$ AND destination_address = $$'
			|| destination_address_val
			||'$$';
		ELSE
		
			UPDATE mobile_rssi_current_copy 
			SET 
			distance_calc_IMU_0 = distance_0,
			distance_calc_IMU_1 = distance_1,
			distance_error_IMU_0 = err_0,
			distance_error_IMU_1 = err_1
			WHERE source_address = source_address_val 
			AND destination_address = destination_address_val;			
		END IF;	
		
		distance_0 = NULL;
		distance_1 = NULL;
		END IF;
	END LOOP;
	
END IF;



RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION calculate_distance_imu_mobile()
  OWNER TO postgres;
  
  
  
-- Function: calculate_distance_mobile()

-- DROP FUNCTION calculate_distance_mobile();

CREATE OR REPLACE FUNCTION calculate_distance_mobile()
  RETURNS trigger AS
$BODY$
DECLARE	
	source_address_val	mobile_rssi_current_copy.source_address%TYPE;
	destination_address_val	mobile_rssi_current_copy.destination_address%TYPE;
	second_source_address	mobile_rssi_current_copy.destination_address%TYPE; 
	timestamp_val 		mobile_rssi_current_copy.timestamp%TYPE;
	mobile_rssi		mobile_rssi_current_copy.rssi_avg%TYPE;

	mobile_anthena		mobile_rssi_current_copy.rssi_avg%TYPE:= -5;
	mobile_anthena_1	double precision:= 1.1574;

	conn_rssi 		ap_rssi_current_copy.rssi_avg%TYPE;
	conn_eta		ap_rssi_current_copy.eta%TYPE;
	conn_dist		ap_rssi_current_copy.distance_real%TYPE;
	
	rssi_0			ap_rssi_current_copy.rssi_avg%TYPE 	:= -70.8333333333333;
	eta_0			ap_rssi_current_copy.eta%TYPE 		:= 6.55334973335266;
	dist_0			ap_rssi_current_copy.distance_real%TYPE := 12.2733497619629;

	distance_real_val	mobile_rssi_current_copy.distance_real%TYPE;
	
	distance_0		mobile_rssi_current_copy.distance_calc_0%TYPE;
	distance_1		mobile_rssi_current_copy.distance_calc_1%TYPE;

	err_0			mobile_rssi_current_copy.distance_error_0%TYPE;
	err_1			mobile_rssi_current_copy.distance_error_1%TYPE;
				
BEGIN
FOR 	timestamp_val, source_address_val, destination_address_val, mobile_rssi, distance_real_val 
	IN SELECT timestamp, source_address, destination_address, rssi_avg, distance_real FROM mobile_rssi_current_copy
	LOOP			
		-- Vyber druhy nejlepsi APcko z mobile table
		EXECUTE	'SELECT mobile_rssi_current_copy.destination_address 
			FROM mobile_rssi_current_copy WHERE 
			source_address = $$'
			|| source_address_val 
			||'$$ AND destination_address != $$'
			||destination_address_val
			||'$$ ORDER BY mobile_rssi_current_copy.rssi_avg DESC LIMIT 1'
			INTO second_source_address;
		
		-- RSSI,DISTANCE,ETA FROM CLOSEST LINK A-B, B-A '-- ,mobile_rssi_current_copy.rssi_avg

		EXECUTE 'SELECT avg(ap_rssi_current_copy.rssi_avg), avg(ap_rssi_current_copy.distance_real), avg(ap_rssi_current_copy.eta)
			FROM ap_rssi_current_copy INNER JOIN mobile_rssi_current_copy ON 
			ap_rssi_current_copy.source_address = mobile_rssi_current_copy.destination_address WHERE
			mobile_rssi_current_copy.source_address = $$'
			|| source_address_val 
			||'$$ AND (ap_rssi_current_copy.destination_address = $$'
			||destination_address_val
			||'$$ OR ap_rssi_current_copy.source_address = $$'
			||destination_address_val
			||'$$ ) AND (ap_rssi_current_copy.destination_address = $$'
			|| second_source_address
			||'$$ OR ap_rssi_current_copy.source_address = $$'
			|| second_source_address
			||'$$)'
			INTO conn_rssi, conn_dist, conn_eta;
		
		
		IF  	conn_rssi IS NULL 
			OR conn_dist IS NULL 
			OR conn_eta IS NULL 
			OR mobile_rssi IS NULL
		THEN 
			
			UPDATE mobile_rssi_current_copy 
			SET 	distance_calc_0 = NULL,
				distance_calc_1 = NULL,
				distance_error_0 = NULL,
				distance_error_1 = NULL
			WHERE source_address = source_address_val
			AND destination_address = destination_address_val;

		ELSE
			--distance_0 = dist_0 * (10^((rssi_0 - mobile_rssi) / (10 * eta_0)));
			distance_0 = dist_0 * (10^((rssi_0 - mobile_rssi - mobile_anthena) / (10 * eta_0)));
			--distance_0 = dist_0 * (10^((rssi_0 - (mobile_rssi*mobile_anthena_1)) / (10 * eta_0)));

			--distance_1 = conn_dist * (10^((conn_rssi - mobile_rssi) / (10 * conn_eta)));
			distance_1 = conn_dist * (10^((conn_rssi - mobile_rssi - mobile_anthena) / (10 * conn_eta)));
			--distance_1 = conn_dist * (10^((conn_rssi - (mobile_rssi*mobile_anthena_1)) / (10 * conn_eta)));

			err_0 = ABS(distance_real_val - distance_0);
			err_1 = ABS(distance_real_val - distance_1);
			
			UPDATE mobile_rssi_current_copy 
			SET 	distance_calc_0  = distance_0,
				distance_calc_1  = distance_1,
				distance_error_0 = err_0,
				distance_error_1 = err_1
			WHERE source_address = source_address_val
			AND destination_address = destination_address_val;

			INSERT INTO mobile_measure(timestamp,source_address,destination_address,route_rssi,
			route_eta,route_distance,mobile_rssi,distance_calc_0,distance_calc_1,distance_error_0,distance_error_1) 
			VALUES (
			timestamp_val,
			source_address_val, 
			destination_address_val, 
			conn_rssi,
			conn_eta,
			conn_dist,
			mobile_rssi,
			distance_0,
			err_0,
			distance_1,
			err_1);
		
			distance_0 = NULL;
			distance_1 = NULL;
			err_0 = NULL;
			err_1 = NULL;
 		END IF;
	END LOOP;
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION calculate_distance_mobile()
  OWNER TO postgres;



-- Function: calculate_pos_0_mobile()

-- DROP FUNCTION calculate_pos_0_mobile();

CREATE OR REPLACE FUNCTION calculate_pos_0_mobile()
  RETURNS trigger AS
$BODY$
DECLARE	
	source_address_val	mobile_rssi_current_copy.source_address%TYPE;
	destination_address_val	mobile_rssi_current_copy.destination_address%TYPE;
	second_source_address	mobile_rssi_current_copy.destination_address%TYPE; 
	timestamp_val 		mobile_rssi_current_copy.timestamp%TYPE;
	timestamp_global	mobile_rssi_current_copy.timestamp%TYPE;
	mobile_rssi		mobile_rssi_current_copy.rssi_avg%TYPE;

	x			ap_init.x_poz%TYPE;
	y			ap_init.y_poz%TYPE;
	z			ap_init.z_poz%TYPE;
	
	d1			mobile_rssi_current_copy.distance_calc_0%TYPE;
	d2			mobile_rssi_current_copy.distance_calc_0%TYPE;
	d3			mobile_rssi_current_copy.distance_calc_0%TYPE;

	x1			ap_init.x_poz%TYPE;
	x2			ap_init.x_poz%TYPE;
	x3			ap_init.x_poz%TYPE;

	y1			ap_init.x_poz%TYPE;
	y2			ap_init.x_poz%TYPE;
	y3			ap_init.x_poz%TYPE;

	z1			ap_init.x_poz%TYPE;
	z2			ap_init.x_poz%TYPE;
	z3			ap_init.x_poz%TYPE;

	iterace			real;

	m11			double precision;
	m12			double precision;
	m21			double precision;
	m22			double precision;

	n11			double precision;
	n12			double precision;
	n21			double precision;
	n22			double precision;

	detM			double precision;
	detN			double precision;
				
BEGIN
iterace = 0;
FOR source_address_val IN SELECT device_address_mac FROM mobile_connected_dev
	LOOP
	EXECUTE 'SELECT timestamp FROM MOBILE_POS_0 WHERE mac_address = $$'
				|| source_address_val
				|| '$$ ORDER BY timestamp desc limit 1' 
				INTO 
				timestamp_val;

	timestamp_global =  (select extract('epoch' from current_timestamp)*1000);
	IF timestamp_global - timestamp_val < 100
	THEN 
	
		CONTINUE;
	END IF;

		FOR destination_address_val IN SELECT destination_address FROM mobile_rssi_current_copy ORDER BY rssi_avg DESC
		LOOP
			

			IF iterace = 0
			THEN 	
				EXECUTE'SELECT x_poz, y_poz, z_poz FROM ap_init
				WHERE mac_address = $$'
				|| destination_address_val
				|| '$$' 
				INTO x1, y1, z1;

				EXECUTE'SELECT distance_calc_0 
					FROM mobile_rssi_current_copy 
					WHERE destination_address = $$'
					|| destination_address_val
					||'$$ AND source_address = $$'
					|| source_address_val
					|| '$$' 
				INTO d1;
			
			ELSIF iterace = 1
			THEN
				EXECUTE'SELECT x_poz, y_poz, z_poz FROM ap_init
				WHERE mac_address = $$'
				|| destination_address_val
				|| '$$' 
				INTO x2, y2, z2;
	
				EXECUTE'SELECT distance_calc_0 
					FROM mobile_rssi_current_copy 
					WHERE destination_address = $$'
					|| destination_address_val
					||'$$ AND source_address = $$'
					|| source_address_val
					|| '$$' 
				INTO d2;
			
			ELSIF iterace = 2
			THEN
				EXECUTE'SELECT x_poz, y_poz, z_poz FROM ap_init
				WHERE mac_address = $$'
				|| destination_address_val
				|| '$$' 
				INTO x3, y3, z3;


				EXECUTE'SELECT distance_calc_0
					FROM mobile_rssi_current_copy 
					WHERE destination_address = $$'
					|| destination_address_val
					||'$$ AND source_address = $$'
					|| source_address_val
					|| '$$' 
				INTO d3;

			ELSE 
				CONTINUE;

			END IF;
			iterace = iterace +1;
			END LOOP;


	IF 	x1 IS NULL OR 
		x2 IS NULL OR 
		x3 IS NULL OR
		y1 IS NULL OR 
		y2 IS NULL OR 
		y3 IS NULL OR
		z1 IS NULL OR 
		z2 IS NULL OR 
		z3 IS NULL OR
		d1 IS NULL OR 
		d2 IS NULL OR 
		d3 IS NULL
	THEN
		RETURN NULL;
	END IF;

	--DO THE MATH CALCULATE MATRIX 

	m11 = (d1^2-d2^2)-(x1^2-x2^2)-(y1^2-y2^2); 
	m12 = 2*(y2-y1);
	
	m21 = (d1^2-d3^2)-(x1^2-x3^2)-(y1^2-y3^2);
	m22 =  2*(y3-y1);

	detM = (m11*m22)-(m12*m21);
	
	n11 = 2*(x2-x1);
	n12 = 2*(y2-y1);

	n21 = 2*(x3-x1);
	n22 = 2*(y3-y1); 

	detN = (n11*n22)-(n12*n21);

	IF detN = 0 
	THEN 
		RETURN NULL;
	END IF;
	
	x = detM/detN;

	--Y VALUE 
	m11 = 2*(x2-x1);
	m12 = (d1^2-d2^2)-(x1^2-x2^2)-(y1^2-y2^2);

	m21 = 2*(x3-x1);
	m22 = (d1^2-d3^2)-(x1^2-x3^2)-(y1^2-y3^2);

	detM = (m11*m22)-(m12*m21);

	n11 = 2*(x2-x1);
	n12 = 2*(y2-y1);

	n21 = 2*(x3-x1);
	n22 = 2*(y3-y1);

	detN = (n11*n22)-(n12*n21);

	IF detN = 0 
	THEN 
		RETURN NULL;
	
	END IF;
	
	y = detM/detN;
	
	EXECUTE 'UPDATE mobile_connected_dev
		SET x_0 = $$'
		|| x
		||'$$, timestamp = $$'
		|| timestamp_global	
		||'$$, y_0 = $$'
		|| y
		||'$$ WHERE device_address_mac = $$'
		|| source_address_val 
		||'$$';

	EXECUTE 'INSERT INTO MOBILE_POS_0(mac_address,timestamp, x_pos, y_pos) VALUES($$'
		|| source_address_val
		||'$$,$$'
		|| timestamp_global
		||'$$,$$'
		|| x
		||'$$,$$'
		|| y 
		||'$$)';

	
	END LOOP;
RETURN NEW;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION calculate_pos_0_mobile()
  OWNER TO postgres;






-- Function: calculate_pos_1_mobile()

-- DROP FUNCTION calculate_pos_1_mobile();

CREATE OR REPLACE FUNCTION calculate_pos_1_mobile()
  RETURNS trigger AS
$BODY$
DECLARE	
	source_address_val	mobile_rssi_current_copy.source_address%TYPE;
	destination_address_val	mobile_rssi_current_copy.destination_address%TYPE;
	second_source_address	mobile_rssi_current_copy.destination_address%TYPE; 
	timestamp_val 		mobile_rssi_current_copy.timestamp%TYPE;
	mobile_rssi		mobile_rssi_current_copy.rssi_avg%TYPE;
	timestamp_global	mobile_rssi_current_copy.timestamp%TYPE;

	x			ap_init.x_poz%TYPE;
	y			ap_init.y_poz%TYPE;
	z			ap_init.z_poz%TYPE;
	
	d1			mobile_rssi_current_copy.distance_calc_0%TYPE;
	d2			mobile_rssi_current_copy.distance_calc_0%TYPE;
	d3			mobile_rssi_current_copy.distance_calc_0%TYPE;

	x1			ap_init.x_poz%TYPE;
	x2			ap_init.x_poz%TYPE;
	x3			ap_init.x_poz%TYPE;

	y1			ap_init.x_poz%TYPE;
	y2			ap_init.x_poz%TYPE;
	y3			ap_init.x_poz%TYPE;

	z1			ap_init.x_poz%TYPE;
	z2			ap_init.x_poz%TYPE;
	z3			ap_init.x_poz%TYPE;

	iterace			real;

	m11			double precision;
	m12			double precision;
	m21			double precision;
	m22			double precision;

	n11			double precision;
	n12			double precision;
	n21			double precision;
	n22			double precision;

	detM			double precision;
	detN			double precision;
				
BEGIN
iterace = 0;

FOR source_address_val IN SELECT device_address_mac FROM mobile_connected_dev
	LOOP
		EXECUTE 'SELECT timestamp FROM mobile_pos_1 WHERE mac_address = $$'
			|| source_address_val
			|| '$$ ORDER BY timestamp desc limit 1' 
			INTO 
			timestamp_val;

		timestamp_global =  (select extract('epoch' from current_timestamp)*1000);
		IF timestamp_global - timestamp_val < 100
		THEN 
			CONTINUE;
		END IF;
		
		FOR destination_address_val IN SELECT destination_address FROM mobile_rssi_current_copy ORDER BY rssi_avg DESC
		LOOP
			IF iterace = 0
			THEN 	
				EXECUTE'SELECT x_poz, y_poz, z_poz FROM ap_init
				WHERE mac_address = $$'
				|| destination_address_val
				|| '$$' 
				INTO x1, y1, z1;

				EXECUTE'SELECT distance_calc_1 
					FROM mobile_rssi_current_copy 
					WHERE destination_address = $$'
					|| destination_address_val
					||'$$ AND source_address = $$'
					|| source_address_val
					|| '$$' 
				INTO d1;
			
			ELSIF iterace = 1
			THEN
				EXECUTE'SELECT x_poz, y_poz, z_poz FROM ap_init
				WHERE mac_address = $$'
				|| destination_address_val
				|| '$$' 
				INTO x2, y2, z2;
	
				EXECUTE'SELECT distance_calc_1 
					FROM mobile_rssi_current_copy 
					WHERE destination_address = $$'
					|| destination_address_val
					||'$$ AND source_address = $$'
					|| source_address_val
					|| '$$' 
				INTO d2;
			
			ELSIF iterace = 2
			THEN
				EXECUTE'SELECT x_poz, y_poz, z_poz FROM ap_init
				WHERE mac_address = $$'
				|| destination_address_val
				|| '$$' 
				INTO x3, y3, z3;


				EXECUTE'SELECT distance_calc_1 
					FROM mobile_rssi_current_copy 
					WHERE destination_address = $$'
					|| destination_address_val
					||'$$ AND source_address = $$'
					|| source_address_val
					|| '$$' 
				INTO d3;

			ELSE 
				CONTINUE;

			END IF;
			iterace = iterace +1;
			END LOOP;


	IF 	x1 IS NULL OR 
		x2 IS NULL OR 
		x3 IS NULL OR
		y1 IS NULL OR 
		y2 IS NULL OR 
		y3 IS NULL OR
		z1 IS NULL OR 
		z2 IS NULL OR 
		z3 IS NULL OR
		d1 IS NULL OR 
		d2 IS NULL OR 
		d3 IS NULL
	THEN
		RETURN NULL;
	END IF;

	--DO THE MATH CALCULATE MATRIX 

	m11 = (d1^2-d2^2)-(x1^2-x2^2)-(y1^2-y2^2); 
	m12 = 2*(y2-y1);
	
	m21 = (d1^2-d3^2)-(x1^2-x3^2)-(y1^2-y3^2);
	m22 =  2*(y3-y1);

	detM = (m11*m22)-(m12*m21);
	
	n11 = 2*(x2-x1);
	n12 = 2*(y2-y1);

	n21 = 2*(x3-x1);
	n22 = 2*(y3-y1); 

	detN = (n11*n22)-(n12*n21);

	IF detN = 0 
	THEN 
		RETURN NULL;
	END IF;
	
	x = detM/detN;

	--Y VALUE 
	m11 = 2*(x2-x1);
	m12 = (d1^2-d2^2)-(x1^2-x2^2)-(y1^2-y2^2);

	m21 = 2*(x3-x1);
	m22 = (d1^2-d3^2)-(x1^2-x3^2)-(y1^2-y3^2);

	detM = (m11*m22)-(m12*m21);

	n11 = 2*(x2-x1);
	n12 = 2*(y2-y1);

	n21 = 2*(x3-x1);
	n22 = 2*(y3-y1);

	detN = (n11*n22)-(n12*n21);

	IF detN = 0 
	THEN 
		RETURN NULL;
	
	END IF;
	
	y = detM/detN;
	
	EXECUTE 'UPDATE mobile_connected_dev
		SET x_1 = $$'
		|| x
		||'$$, timestamp = $$'
		|| timestamp_global
		||'$$, y_1 = $$'
		|| y
		||'$$ WHERE device_address_mac = $$'
		|| source_address_val 
		||'$$';

	EXECUTE 'INSERT INTO mobile_pos_1(mac_address,timestamp, x_pos, y_pos) VALUES($$'
		|| source_address_val
		||'$$,$$'
		|| timestamp_global
		||'$$,$$'
		|| x
		||'$$,$$'
		|| y 
		||'$$)';

	
	END LOOP;
RETURN NEW;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION calculate_pos_1_mobile()
  OWNER TO postgres;





-- Function: calculate_pos_imu_1_mobile()

-- DROP FUNCTION calculate_pos_imu_1_mobile();

CREATE OR REPLACE FUNCTION calculate_pos_imu_1_mobile()
  RETURNS trigger AS
$BODY$
DECLARE	
	source_address_val	mobile_rssi_current_copy.source_address%TYPE;
	destination_address_val	mobile_rssi_current_copy.destination_address%TYPE;
	second_source_address	mobile_rssi_current_copy.destination_address%TYPE; 
	timestamp_val 		mobile_rssi_current_copy.timestamp%TYPE;
	mobile_rssi		mobile_rssi_current_copy.rssi_avg%TYPE;
	timestamp_last_val 	mobile_rssi_current_copy.timestamp%TYPE;
	
	x			ap_init.x_poz%TYPE;
	y			ap_init.y_poz%TYPE;
	z			ap_init.z_poz%TYPE;
	
	d1			mobile_rssi_current_copy.distance_calc_0%TYPE;
	d2			mobile_rssi_current_copy.distance_calc_0%TYPE;
	d3			mobile_rssi_current_copy.distance_calc_0%TYPE;

	x1			ap_init.x_poz%TYPE;
	x2			ap_init.x_poz%TYPE;
	x3			ap_init.x_poz%TYPE;

	y1			ap_init.x_poz%TYPE;
	y2			ap_init.x_poz%TYPE;
	y3			ap_init.x_poz%TYPE;

	z1			ap_init.x_poz%TYPE;
	z2			ap_init.x_poz%TYPE;
	z3			ap_init.x_poz%TYPE;

	iterace			real;

	m11			double precision;
	m12			double precision;
	m21			double precision;
	m22			double precision;

	n11			double precision;
	n12			double precision;
	n21			double precision;
	n22			double precision;

	detM			double precision;
	detN			double precision;
	
	databaseid_val		mobile_connected_dev.hash%TYPE;
				
BEGIN
iterace = 0;
timestamp_val =  (select extract('epoch' from current_timestamp)*1000);

EXECUTE 'SELECT timestamp FROM mobile_pos_step ORDER BY timestamp desc limit 1' INTO 
		timestamp_last_val;

		IF  timestamp_last_val = timestamp_val
		THEN 
			RETURN NULL;
		END IF;

FOR source_address_val IN SELECT device_address_mac FROM mobile_connected_dev
	LOOP		
		EXECUTE 'SELECT hash FROM mobile_connected_dev WHERE device_address_mac = $$'
		||source_address_val
		||'$$'
		INTO databaseid_val;
		FOR destination_address_val IN SELECT destination_address FROM mobile_rssi_current_copy ORDER BY rssi_avg DESC
		LOOP	
			IF iterace = 0
			THEN 	
				EXECUTE'SELECT x_poz, y_poz, z_poz FROM ap_init
				WHERE mac_address = $$'
				|| destination_address_val
				|| '$$' 
				INTO x1, y1, z1;

				EXECUTE'SELECT distance_calc_imu_1 
					FROM mobile_rssi_current_copy 
					WHERE destination_address = $$'
					|| destination_address_val
					||'$$ AND source_address = $$'
					|| source_address_val
					|| '$$' 
				INTO d1;
			
			ELSIF iterace = 1
			THEN
				EXECUTE'SELECT x_poz, y_poz, z_poz FROM ap_init
				WHERE mac_address = $$'
				|| destination_address_val
				|| '$$' 
				INTO x2, y2, z2;
	
				EXECUTE'SELECT distance_calc_imu_1 
					FROM mobile_rssi_current_copy 
					WHERE destination_address = $$'
					|| destination_address_val
					||'$$ AND source_address = $$'
					|| source_address_val
					|| '$$' 
				INTO d2;
			
			ELSIF iterace = 2
			THEN
				EXECUTE'SELECT x_poz, y_poz, z_poz FROM ap_init
				WHERE mac_address = $$'
				|| destination_address_val
				|| '$$' 
				INTO x3, y3, z3;


				EXECUTE'SELECT distance_calc_imu_1 
					FROM mobile_rssi_current_copy 
					WHERE destination_address = $$'
					|| destination_address_val
					||'$$ AND source_address = $$'
					|| source_address_val
					|| '$$' 
				INTO d3;

			ELSE 
				CONTINUE;

			END IF;
			iterace = iterace +1;
			END LOOP;	

	IF 	x1 IS NULL OR 
		x2 IS NULL OR 
		x3 IS NULL OR
		y1 IS NULL OR 
		y2 IS NULL OR 
		y3 IS NULL OR
		z1 IS NULL OR 
		z2 IS NULL OR 
		z3 IS NULL OR
		d1 IS NULL OR 
		d2 IS NULL OR 
		d3 IS NULL
	THEN
		RETURN NULL;
	END IF;

	--DO THE MATH CALCULATE MATRIX 

	m11 = (d1^2-d2^2)-(x1^2-x2^2)-(y1^2-y2^2); 
	m12 = 2*(y2-y1);
	
	m21 = (d1^2-d3^2)-(x1^2-x3^2)-(y1^2-y3^2);
	m22 =  2*(y3-y1);

	detM = (m11*m22)-(m12*m21);
	
	n11 = 2*(x2-x1);
	n12 = 2*(y2-y1);

	n21 = 2*(x3-x1);
	n22 = 2*(y3-y1); 

	detN = (n11*n22)-(n12*n21);

	IF detN = 0 
	THEN 
		RETURN NULL;
	END IF;
	
	x = detM/detN;

	--Y VALUE 
	m11 = 2*(x2-x1);
	m12 = (d1^2-d2^2)-(x1^2-x2^2)-(y1^2-y2^2);

	m21 = 2*(x3-x1);
	m22 = (d1^2-d3^2)-(x1^2-x3^2)-(y1^2-y3^2);

	detM = (m11*m22)-(m12*m21);

	n11 = 2*(x2-x1);
	n12 = 2*(y2-y1);

	n21 = 2*(x3-x1);
	n22 = 2*(y3-y1);

	detN = (n11*n22)-(n12*n21);

	IF detN = 0 
	THEN 
		RETURN NULL;
	
	END IF;
	
	y = detM/detN;
	
--	EXECUTE 'UPDATE mobile_connected_dev
--		SET x_1 = $$'
--		|| x
--		||'$$, y_1 = $$'
--		|| y
--		||'$$ WHERE device_address_mac = $$'
--		|| source_address_val 
--		||'$$';
	
	INSERT INTO mobile_pos_step(timestamp, databaseid, x_pos_step,
		y_pos_step, x_pos_int, y_pos_int, type) VALUES (
		timestamp_val, 
		databaseid_val, 
		x,
		y,
		x,
		y,
		$$Wireless$$);
	
	END LOOP;
RETURN NEW;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION calculate_pos_imu_1_mobile()
  OWNER TO postgres;




-- Function: calculate_real_distance()

-- DROP FUNCTION calculate_real_distance();

CREATE OR REPLACE FUNCTION calculate_real_distance()
  RETURNS trigger AS
$BODY$
DECLARE
	--zdrojova adresa
	source_x	ap_init.x_poz%TYPE;
	source_y 	ap_init.y_poz%TYPE;
	source_z 	ap_init.z_poz%TYPE;

	--Cilova adresa
	destination_x 	ap_init.x_poz%TYPE;
	destination_y	ap_init.y_poz%TYPE;
	destination_z 	ap_init.z_poz%TYPE;

	id		ap_init.id%TYPE;
	ip_address	ap_init.ip_address%TYPE;
	source 		ap_init.mac_address%TYPE;
	destination 	ap_init.mac_address%TYPE;

	distance_real 	ap_rssi_current_copy.distance_real%TYPE = 0;
	timestamp_val 	ap_rssi_current_copy.timestamp%TYPE;

BEGIN
	timestamp_val =  (select extract('epoch' from current_timestamp)*1000);
	FOR 	id, source, ip_address, source_x, source_y, source_z 
		IN SELECT * FROM ap_init
		LOOP
			FOR 	id, destination, ip_address, destination_x, destination_y, destination_z 
				IN SELECT * FROM ap_init
				LOOP	
					IF source = destination
					THEN
						CONTINUE;
					ELSE
						IF	source_x 	IS NOT NULL AND 
							source_y 	IS NOT NULL AND 
							source_z 	IS NOT NULL AND 
							destination_x 	IS NOT NULL AND 
							destination_y 	IS NOT NULL AND 
							destination_z 	IS NOT NULL  
						
						THEN
							distance_real = SQRT(
							(source_x - destination_x)^2 + 
							(source_y - destination_y)^2 +
							(source_z - destination_z)^2);
						ELSE 
							distance_real = null;
						END IF;	

						PERFORM ap_rssi_current_copy.id FROM ap_rssi_current_copy 
						WHERE source_address = source
						AND destination_address = destination;
						
						IF FOUND THEN
							IF NEW.mac_address = source OR NEW.mac_address = destination
							THEN
								IF distance_real IS NOT NULL
								THEN	
									EXECUTE 'UPDATE ap_rssi_current_copy SET 
									distance_real = $$'
									|| distance_real
									|| '$$,timestamp =$$'
									|| timestamp_val
									|| '$$ WHERE source_address = $$' 
									|| source 
									|| '$$ AND destination_address = $$'
									|| destination 
									|| '$$';
								ELSE
									UPDATE ap_rssi_current_copy SET 
									distance_real = NULL, timestamp = timestamp_val 
									WHERE source_address = source AND
									destination_address = destination;

								END IF;
							END IF;
						
						ELSE
							IF distance_real IS NOT NULL
							THEN
								EXECUTE 'INSERT INTO ap_rssi_current_copy
								(timestamp,source_address,destination_address,distance_real) VALUES ($$'
								|| timestamp_val
								|| '$$,$$' 
								|| source 
								|| '$$,$$' 
								|| destination 
								|| '$$,$$' 
								|| distance_real
								|| '$$)';
							ELSE
								INSERT INTO ap_rssi_current_copy
								(timestamp,source_address,destination_address,distance_real) 
								VALUES(timestamp_val,source,destination,null);
							END IF;
						END IF;
					END IF;
				END LOOP;	
		END LOOP;
RETURN NEW;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION calculate_real_distance()
  OWNER TO postgres;

-- Function: calculate_rssi_eta_mobile()

-- DROP FUNCTION calculate_rssi_eta_mobile();

CREATE OR REPLACE FUNCTION calculate_rssi_eta_mobile()
  RETURNS trigger AS
$BODY$
DECLARE	
	source_address_val	mobile_rssi_current_copy.source_address%TYPE;
	destination_address_val	mobile_rssi_current_copy.destination_address%TYPE;
	rssi_med_val 		mobile_rssi_current_copy.rssi_avg%TYPE;
	timestamp_val 		mobile_rssi_current_copy.timestamp%TYPE;
	
	rssi_array 		double precision[];
	

BEGIN
PERFORM mobile_rssi_current_copy.id 
	FROM mobile_rssi_current_copy 
	WHERE source_address = NEW.source_address
	AND destination_address= NEW.destination_address;

IF FOUND THEN 
	source_address_val = NEW.source_address;
	destination_address_val = NEW.destination_address;
	timestamp_val =  (select extract('epoch' from current_timestamp)*1000);

	EXECUTE 'SELECT median(rssi) AS median_value FROM mobile_rssi_data_copy 
		WHERE  source_address = $$'
		|| source_address_val
		||'$$ AND destination_address = $$'
		|| destination_address_val
		||'$$ AND timestamp > (select extract(epoch from current_timestamp)*1000 - 10000)'
		INTO rssi_med_val;

	IF  rssi_med_val IS NULL 
	THEN 
		RETURN NULL;
	END IF;

	EXECUTE 'UPDATE mobile_rssi_current_copy SET 
		rssi_avg = $$'
		|| rssi_med_val
		|| '$$,timestamp = $$'
		|| timestamp_val
		|| '$$ WHERE source_address = $$' 
		|| source_address_val
		|| '$$ AND destination_address = $$'
		|| destination_address_val
		|| '$$';

	END IF;
RETURN NEW;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION calculate_rssi_eta_mobile()
  OWNER TO postgres;






-- Function: update_ap_current()

-- DROP FUNCTION update_ap_current();

CREATE OR REPLACE FUNCTION update_ap_current()
  RETURNS trigger AS
$BODY$
DECLARE	
	id 			ap_rssi_current_copy.id%TYPE;
	source_address_val	ap_rssi_current_copy.source_address%TYPE;
	destination_address_val	ap_rssi_current_copy.destination_address%TYPE;
	distance_real_val	ap_rssi_current_copy.distance_real%TYPE;
	rssi_med_val 		ap_rssi_current_copy.rssi_avg%TYPE;
	eta_val			ap_rssi_current_copy.eta%TYPE;
	timestamp_val 		ap_rssi_current_copy.timestamp%TYPE;

	antena_gain		ap_init.antena_gain%TYPE;
	tx_power		ap_init.tx_power%TYPE;

	count_val		real;
BEGIN
--Update pouze inicializovanych jednotek 
PERFORM ap_rssi_current_copy.id 	
	FROM ap_rssi_current_copy 
	WHERE source_address = NEW.source_address
	AND destination_address= NEW.destination_address;
	
	IF FOUND THEN	
			source_address_val = NEW.source_address;
			destination_address_val = NEW.destination_address;
			
			EXECUTE 'SELECT distance_real
			FROM ap_rssi_current_copy WHERE source_address = $$' 
			|| source_address_val
			|| '$$ AND destination_address = $$'
			|| destination_address_val 
			|| '$$'
			INTO distance_real_val;

			IF  distance_real_val IS NULL 
			THEN 
				UPDATE ap_rssi_current_copy SET 
				rssi_avg 	= rssi_med_val, 
				eta 		= eta_val
				WHERE source_address = NEW.source_address
				AND destination_address = NEW.destination_address;
				
			END IF;
			
			timestamp_val =  (select extract('epoch' from current_timestamp)*1000);
			
			--EXECUTE 'SELECT avg(rssi) FROM ap_rssi_data_copy 
			EXECUTE 'SELECT median(rssi) AS median_value FROM ap_rssi_data_copy 
				WHERE  source_address = $$'
				||NEW.source_address 
				||'$$ AND destination_address = $$'
				|| NEW.destination_address
				||'$$ AND timestamp > (select extract(epoch from current_timestamp)*1000 - 60000)'
				INTO rssi_med_val;

			-- IF NULL RETURN NULL
			IF  rssi_med_val IS NULL 
			THEN 
				UPDATE ap_rssi_current_copy SET 
				rssi_avg 	= rssi_med_val, 
				eta 		= eta_val
				WHERE source_address = NEW.source_address
				AND destination_address = NEW.destination_address;

				RETURN NULL;
			END IF;

			--GET ANTENA GAIN AND TX POWER
			EXECUTE 'SELECT antena_gain, tx_power FROM ap_init WHERE mac_address = $$' 
			|| NEW.source_address 
			|| '$$'
			INTO antena_gain, tx_power;
		
			IF 	antena_gain 		IS NOT NULL AND 
				tx_power 		IS NOT NULL 
			THEN 
				
				--eta = ((rssi_med_val/10)-LOG(tx_power+antena_gain))/((-1)*LOG(distance_real_val));
				eta_val = LOG(10^((rssi_med_val-(tx_power+antena_gain))/10))/LOG(1/distance_real_val);
				
				
				-- UPDATE hodnotu s novym RSSI
				EXECUTE 'UPDATE ap_rssi_current_copy SET 
					rssi_avg = $$'
					|| rssi_med_val
					|| '$$,eta = $$'
					|| eta_val
					|| '$$,timestamp = $$'
					|| timestamp_val
					|| '$$ WHERE source_address = $$' 
					|| NEW.source_address 
					|| '$$ AND destination_address = $$'
					|| NEW.destination_address 
					|| '$$';
			ELSE 
				UPDATE ap_rssi_current_copy SET 
				rssi_avg 	= rssi_med_val, 
				eta 		= eta_val
				WHERE source_address = NEW.source_address
				AND destination_address = NEW.destination_address;
			END IF;		
	END IF;
RETURN NEW;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION update_ap_current()
  OWNER TO postgres;



-- Function: update_mobile_rssi_table()

-- DROP FUNCTION update_mobile_rssi_table();

CREATE OR REPLACE FUNCTION update_mobile_rssi_table()
  RETURNS trigger AS
$BODY$
DECLARE
	source 		mobile_rssi_current_copy.source_address%TYPE;
	destination 	ap_init.mac_address%TYPE;
	timestamp 	mobile_rssi_current_copy.timestamp%TYPE;

	source_x	mobile_connected_dev.x_real%TYPE;
	source_y 	mobile_connected_dev.y_real%TYPE;
	source_z 	mobile_connected_dev.z_real%TYPE;

	destination_x 	ap_init.x_poz%TYPE;
	destination_y	ap_init.y_poz%TYPE;
	destination_z 	ap_init.z_poz%TYPE;

	distance_real  mobile_rssi_current_copy.distance_real%TYPE;


BEGIN
	timestamp =  (select extract('epoch' from current_timestamp)*1000);
	source = NEW.device_address_mac;

	source_x = NEW.x_real;
	source_y = NEW.y_real;
	source_z = NEW.z_real;

	FOR 	destination,destination_x,destination_y,destination_z 
		IN SELECT mac_address,x_poz,y_poz,z_poz FROM ap_init
	LOOP	
		IF	source_x 	IS NOT NULL AND 
			source_y 	IS NOT NULL AND 
			source_z 	IS NOT NULL AND 
			destination_x 	IS NOT NULL AND 
			destination_y 	IS NOT NULL AND 
			destination_z 	IS NOT NULL  
			
			THEN
				distance_real = SQRT(
				(source_x - destination_x)^2 + 
				(source_y - destination_y)^2 +
				(source_z - destination_z)^2);
			ELSE 
				distance_real = null;
			END IF;
	
		PERFORM 	mobile_rssi_current_copy.id 
				FROM mobile_rssi_current_copy 
				WHERE source_address = source
				AND destination_address = destination;	
		IF NOT FOUND THEN
			IF distance_real IS NOT NULL
			THEN
				EXECUTE 'INSERT INTO mobile_rssi_current_copy
					(timestamp,distance_real,source_address
					destination_address)
					VALUES ($$'
					|| timestamp
					|| '$$,$$' 
					|| distance_real
					|| '$$,$$'
					|| source
					|| '$$,$$'	
					|| destination
					|| '$$)';
			ELSE
				EXECUTE 'INSERT INTO mobile_rssi_current_copy
					(timestamp,source_address,
					destination_address)
					VALUES ($$'
					|| timestamp
					|| '$$,$$'
					|| source
					|| '$$,$$'	
					|| destination
					|| '$$)';
			END IF;
				
		ELSE
			IF distance_real IS NOT NULL 
			THEN 
				EXECUTE 'UPDATE mobile_rssi_current_copy SET 
					distance_real = '
					|| distance_real
					|| ',timestamp =$$'
					|| timestamp
					|| '$$ WHERE source_address = $$' 
					|| source 
					|| '$$ AND destination_address = $$'
					|| destination 
					|| '$$'; 
			END IF;
			
		END IF;
	END LOOP;
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION update_mobile_rssi_table()
  OWNER TO postgres;





-- Function: final_median(anyarray)

-- DROP FUNCTION final_median(anyarray);

CREATE OR REPLACE FUNCTION final_median(anyarray)
  RETURNS double precision AS
$BODY$ 
DECLARE
  cnt INTEGER;
BEGIN
  cnt := (SELECT COUNT(*) FROM unnest($1) val WHERE val IS NOT NULL);
  IF (CEIL(cnt/ 2.0) - 1) > 0
  THEN 
	RETURN (SELECT avg(tmp.val)::float8 
            FROM (SELECT val FROM unnest($1) val
                    WHERE val IS NOT NULL 
                    ORDER BY 1 
                    LIMIT 2 - MOD(cnt, 2) 
                    OFFSET CEIL(cnt/ 2.0) - 1
                  ) AS tmp
         );
  ELSE
	RETURN NULL;
  END IF;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION final_median(anyarray)
  OWNER TO postgres;
