CREATE OR REPLACE FUNCTION update_g_name_feature() RETURNS TRIGGER AS $update_g_name_feature$
	DECLARE
	uas_id uuid;
	
	BEGIN
	UPDATE uas_zone_version SET name= new.name_feature 
	WHERE airspace_volume_vol_id IN (SELECT vol_id FROM airspace_volume WHERE sequential_id=new.sequential_id)
	RETURNING uas_zone_id INTO uas_id;
	raise notice 'uas:%', uas_id;
	
	RETURN NEW;
	END;
$update_g_name_feature$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_g_restriction() RETURNS TRIGGER AS $update_g_restriction$
	DECLARE
	uas_id uuid;
	json_restriction jsonb='{}'::jsonb;
	restriction_id jsonb='{}'::jsonb;
	condition_id jsonb='{}'::jsonb;
	condition_exp_id uuid;
	
	BEGIN
	uas_id:= (SELECT uas_zone_id FROM uas_zone_version WHERE airspace_volume_vol_id IN 
			  (SELECT vol_id FROM airspace_volume WHERE sequential_id= NEW.sequential_id));
			  
    DELETE FROM uas_condition_expression_type WHERE uas_zone_version_uas_zone_id= uas_id;
	DELETE FROM uas_code_restriction_type WHERE uas_zone_version_uas_zone_id= uas_id;
	
	--desplegar json_restriction:
	json_restriction := convert_from(decode(NEW.restriction,'base64'), 'UTF-8');
	
	FOR restriction_id IN SELECT jsonb_array_elements((json_restriction->>'values')::jsonb)
	LOOP
		INSERT INTO uas_code_restriction_type(code_restriction_type_restriction_code,uas_zone_version_uas_zone_id)
		SELECT 
		code_restriction_type.restriction_code,
		uas_id
		FROM code_restriction_type
		WHERE code_restriction_type.name= (restriction_id->> 'restriction_type_name')
		RETURNING uas_restriction_code INTO condition_exp_id;
		raise notice 'type: %', (restriction_id->> 'restriction_type_name');
		
		FOR condition_id IN SELECT jsonb_array_elements(restriction_id -> 'conditions')
		LOOP
			INSERT INTO uas_condition_expression_type(condition_expression_type_restriction_code,upperlimitnorestriction,uas_zone_version_uas_zone_id,uas_code_restriction_type_uas_restriction_code)
			SELECT 
			condition_expression_type.restriction_code,
			(condition_id->> 'upperlimitnorestriction')::int,
			uas_id,
			condition_exp_id
			FROM condition_expression_type
			WHERE condition_expression_type.name= (condition_id->> 'condition_name');
			
		END LOOP;
	END LOOP;
	
	CALL geoawareness_required_check(uas_id);
	CALL remote_id_check(uas_id);
	
	RETURN NEW;
	END;
$update_g_restriction$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_g_time_period() RETURNS TRIGGER AS $update_g_time_period$
	DECLARE
	uas_id uuid;
	json_time_period jsonb='{}'::jsonb;
	time_periods_id jsonb='{}'::jsonb;
	slot_time_id jsonb='{}'::jsonb;
	time_id uuid;
	array_time_id uuid[];
	i int;
	code_type_period_id text;
	
	BEGIN
	uas_id:= (SELECT uas_zone_id FROM uas_zone_version WHERE airspace_volume_vol_id IN 
			  (SELECT vol_id FROM airspace_volume WHERE sequential_id= NEW.sequential_id));
			  
    array_time_id := ARRAY(SELECT time_period_time_period_id FROM uas_time_period WHERE uas_zone_version_uas_zone_id=uas_id);
	raise notice 'time_id:%', array_time_id;
	
	DELETE FROM uas_time_period WHERE uas_zone_version_uas_zone_id=uas_id;
	IF array_length(array_time_id,1)>0 THEN
		FOR i IN 1..array_length(array_time_id,1)
		LOOP
			raise notice 'time_id:%', array_time_id[i];
			DELETE FROM slot_time WHERE time_period_time_period_id=array_time_id[i];
			DELETE FROM time_period WHERE time_period_id=array_time_id[i];
		END LOOP;
	END IF;
	
	-- desplegar json_time_period:
	json_time_period := convert_from(decode(NEW.time_period,'base64'), 'UTF-8');
	code_type_period_id := (json_time_period->>'type');
	raise notice 'time_periods_json:%', (json_time_period->>'values')::jsonb;
	FOR time_periods_id IN SELECT jsonb_array_elements((json_time_period->>'values')::jsonb)
	LOOP
		INSERT INTO time_period(start_date_time,end_date_time,monday,tuesday,wednesday,thursday,friday,saturday,sunday)
		VALUES( 
		(time_periods_id->>'start_date')::timestamp with time zone,
		(time_periods_id->>'end_date')::timestamp with time zone,
		CASE WHEN (time_periods_id->'days') @> ' [{"day_name": "monday"}]' THEN 'True'::boolean
		ELSE 'False'
		END,
		CASE WHEN (time_periods_id->'days') @> ' [{"day_name": "tuesday"}]' THEN 'True'::boolean
		ELSE 'False'
		END,
		CASE WHEN (time_periods_id->'days') @> ' [{"day_name": "wednesday"}]' THEN 'True'::boolean
		ELSE 'False'
		END,
		CASE WHEN (time_periods_id->'days') @> ' [{"day_name": "thursday"}]' THEN 'True'::boolean
		ELSE 'False'
		END,
		CASE WHEN (time_periods_id->'days') @> ' [{"day_name": "friday"}]' THEN 'True'::boolean
		ELSE 'False'
		END,
		CASE WHEN (time_periods_id->'days') @> ' [{"day_name": "saturday"}]' THEN 'True'::boolean
		ELSE 'False'
		END,
		CASE WHEN (time_periods_id->'days') @> ' [{"day_name": "sunday"}]' THEN 'True'::boolean
		ELSE 'False'
		END)
		RETURNING time_period_id INTO time_id;
		
		FOR slot_time_id IN SELECT jsonb_array_elements((time_periods_id->>'slots')::jsonb)
		LOOP
			INSERT INTO slot_time(start_time,end_time,time_period_time_period_id)
			VALUES(
			(slot_time_id->>'start_time')::time with time zone,
			(slot_time_id->>'end_time')::time with time zone,
			time_id
			);
		END LOOP;
		
		INSERT INTO uas_time_period(time_period_time_period_id,uas_zone_version_uas_zone_id)
		VALUES(time_id,uas_id);
		
	END LOOP;
	
	RETURN NEW;
	END;
$update_g_time_period$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_g_upper_limit() RETURNS TRIGGER AS $update_g_upper_limit$
	DECLARE
	v_id uuid;
	
	BEGIN
	UPDATE airspace_volume SET upper_limit= new.upper_limit 
	WHERE sequential_id=new.sequential_id
	RETURNING vol_id INTO v_id;
	raise notice 'vol:%', v_id;
	
	RETURN NEW;
	END;
$update_g_upper_limit$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_g_uv_reference() RETURNS TRIGGER AS $update_g_uv_reference$
	DECLARE
	v_id uuid;
	vertical_reference_id int;
	
	BEGIN
	vertical_reference_id := (SELECT vertical_reference_code FROM code_vertical_reference WHERE name= new.upper_vertical_reference);
	
	UPDATE airspace_volume SET upper_vertical_reference= vertical_reference_id
	WHERE sequential_id=new.sequential_id
	RETURNING vol_id INTO v_id;
	raise notice 'vol:%', v_id;
	
	RETURN NEW;
	END;
$update_g_uv_reference$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_g_lv_reference() RETURNS TRIGGER AS $update_g_lv_reference$
	DECLARE
	v_id uuid;
	vertical_reference_id int;
	
	BEGIN
	vertical_reference_id := (SELECT vertical_reference_code FROM code_vertical_reference WHERE name= new.lower_vertical_reference);
	
	UPDATE airspace_volume SET lower_vertical_reference= vertical_reference_id
	WHERE sequential_id=new.sequential_id
	RETURNING vol_id INTO v_id;
	raise notice 'vol:%', v_id;
	
	RETURN NEW;
	END;
$update_g_lv_reference$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_g_volume_limitation() RETURNS TRIGGER AS $update_g_volume_limitation$
	DECLARE
	uas_id uuid;
	json_volume_limitation jsonb='{}'::jsonb;
	volume_limitation_id jsonb='{}'::jsonb;
	
	BEGIN
	uas_id:= (SELECT uas_zone_id FROM uas_zone_version WHERE airspace_volume_vol_id IN 
			  (SELECT vol_id FROM airspace_volume WHERE sequential_id= NEW.sequential_id));
	DELETE FROM volume_limitation WHERE uas_zone_version_uas_zone_id= uas_id;
	
	--desplegar json_volume_limitation:
	json_volume_limitation = convert_from(decode(NEW.volume_limitations,'base64'), 'UTF-8');
	
	FOR volume_limitation_id IN SELECT jsonb_array_elements((json_volume_limitation->>'values')::jsonb)
	LOOP
		INSERT INTO volume_limitation(volume_limitation_definitions_limitation_definition_id,uas_zone_version_uas_zone_id)
		SELECT 
		volume_limitation_definitions.limitation_definition_id,
		uas_id
		FROM volume_limitation_definitions
		WHERE volume_limitation_definitions.name= (volume_limitation_id->> 'volume_limitation_name')::text;
		raise notice 'type: %', (volume_limitation_id->> 'volume_limitation_name')::text;
	END LOOP;
	
	RETURN NEW;
	END;
$update_g_volume_limitation$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION insert_newg() RETURNS TRIGGER AS $insert_newg$
	DECLARE
	json_zone_reason jsonb='{}'::jsonb;
	zone_reason_id jsonb='{}'::jsonb;
	json_uspace jsonb='{}'::jsonb;
	uspace_id jsonb='{}'::jsonb;
	json_volume_limitation jsonb='{}'::jsonb;
	volume_limitation_id jsonb='{}'::jsonb;
	json_restriction jsonb='{}'::jsonb;
	restriction_id jsonb='{}'::jsonb;
	condition_id jsonb='{}'::jsonb;
	json_time_period jsonb='{}'::jsonb;
	time_periods_id jsonb='{}'::jsonb;
	slot_time_id jsonb='{}'::jsonb;
	json_authority jsonb='{}'::jsonb;
	json_notification jsonb='{}'::jsonb;
	uas_id uuid;
	m_id uuid;
	a_id uuid;
	v_id uuid;
	n_id uuid;
	condition_exp_id uuid;
	time_id uuid;
	code_type_period_id text;
	check_id uuid;
	
	BEGIN

	check_id:= (SELECT vol_id FROM airspace_volume WHERE sequential_id=NEW.sequential_id);

	IF check_id is null THEN

		--check geometry:
		raise notice 'geom: %', NEW.geom_buffer;
		raise notice 'geom_center: %', NEW.geom_center;
		raise notice 'geom_radius: %', NEw.geom_radius;

		--code_period_id-> sacar de json
		json_time_period := convert_from(decode(NEW.time_period,'base64'), 'UTF-8');
		code_type_period_id := (json_time_period->>'type');
		
		
		--AIRSPACE_VOLUME
		INSERT INTO airspace_volume(lower_limit,lower_vertical_reference,
		upper_limit,upper_vertical_reference,geom_buffer,layers_layer_id,active,geom_center,
		geom_radius,sequential_id)
		SELECT 
		NEW.lower_limit,
		lower_v.vertical_reference_code,
		NEW.upper_limit,
		upper_v.vertical_reference_code,
		NEW.geom_buffer,
		layers_volume.layer_id,
		NEW.active,
		NEW.geom_center,
		NEW.geom_radius,
		NEW.sequential_id
		FROM
		((layers_volume
		JOIN code_vertical_reference AS lower_v ON lower_v.name=NEW.lower_vertical_reference)
		JOIN code_vertical_reference AS upper_v ON upper_v.name=NEW.upper_vertical_reference)
		WHERE 
		layers_volume.name=NEW.layer_name
		RETURNING vol_id INTO v_id;

		--METADATA:
		INSERT INTO metadata(author,creation_date_time, update_date_time)
		SELECT 
		NEW.author_metadata,
		CURRENT_DATE,
		CURRENT_DATE
		RETURNING metada_id INTO m_id;

		-- AUTHORITY:
		json_authority := convert_from(decode(NEW.authorityfrom,'base64'), 'UTF-8');
		INSERT INTO authority(name,service,contact_name,site_url,email,phone)
		VALUES((json_authority->>'name'),(json_authority->>'service'),(json_authority->>'contact_name'),(json_authority->>'site_utl'),
		(json_authority->>'email'),(json_authority->>'phone'))
		RETURNING authority_oid INTO a_id;

		-- AUTHORITY:
		json_notification := convert_from(decode(NEW.notificationto,'base64'), 'UTF-8');
		INSERT INTO authority(name,service,contact_name,site_url,email,phone)
		VALUES((json_notification->>'name'),(json_notification->>'service'),(json_notification->>'contact_name'),(json_notification->>'site_utl'),
		(json_notification->>'email'),(json_notification->>'phone'))
		RETURNING authority_oid INTO n_id;

		--UAS_ZONE_VERSION:
		insert into uas_zone_version(country_id, name,code_zone_type_type_code,region,
									data_capture_prohibition, message,metadata_metada_id,
									authority_authorityfrom_oid, authority_notificationto_oid, airspace_volume_vol_id,intervalbefore,code_period_type_period_type_id)
		SELECT
		NEW.country_id,
		NEW.name_feature,
		code_zone_type.type_code,
		NEW.region,
		NEW.data_capture_prohibition,
		NEW.message,
		m_id,
		a_id,
		n_id,
		v_id,
		NEW.intervalbefore,
		CASE WHEN code_type_period_id = 'personalize' THEN 2
		WHEN code_type_period_id ='permanent' THEN 1
		END
		FROM
		code_zone_type WHERE NEW.code_zone_type=code_zone_type.name
		RETURNING uas_zone_id INTO uas_id;

		raise notice 'v_id: %', v_id;
		raise notice 'uas_id: %', uas_id;
		
		--desplegar json_zone_reason:
		json_zone_reason = convert_from(decode(NEW.code_zone_reason,'base64'), 'UTF-8');
		
		FOR zone_reason_id IN SELECT jsonb_array_elements((json_zone_reason->>'values')::jsonb)
		LOOP
			INSERT INTO uas_code_zone_reason(code_zone_reason_code_zone_reason,uas_zone_version_uas_zone_id)
			SELECT 
			code_zone_reason.code_zone_reason,
			uas_id
			FROM code_zone_reason 
			WHERE code_zone_reason.name = (zone_reason_id ->> 'code_zone_type_name')::text;
			raise notice 'type: %', (zone_reason_id ->> 'code_zone_type_name')::text;
		END LOOP;
		
		--desplegar json_uspace:
		json_uspace = convert_from(decode(NEW.code_uspace_class_type,'base64'), 'UTF-8');
		
		FOR uspace_id IN  SELECT jsonb_array_elements((json_uspace->>'values')::jsonb)
		LOOP
			INSERT INTO uas_code_uspace_class_type(code_uspace_class_type_u_space_code,uas_zone_version_uas_zone_id)
			SELECT 
			code_uspace_class_type.u_space_code,
			uas_id
			FROM code_uspace_class_type
			WHERE code_uspace_class_type.name=(uspace_id ->> 'uspace_class_type_name')::text;
			raise notice 'type: %', (uspace_id ->> 'uspace_class_type_name')::text;
		END LOOP;
		
		--desplegar json_volume_limitation:
		json_volume_limitation = convert_from(decode(NEW.volume_limitations,'base64'), 'UTF-8');
		
		FOR volume_limitation_id IN SELECT jsonb_array_elements((json_volume_limitation->>'values')::jsonb)
		LOOP
			INSERT INTO volume_limitation(volume_limitation_definitions_limitation_definition_id,uas_zone_version_uas_zone_id)
			SELECT 
			volume_limitation_definitions.limitation_definition_id,
			uas_id
			FROM volume_limitation_definitions
			WHERE volume_limitation_definitions.name= (volume_limitation_id->> 'volume_limitation_name')::text;
			raise notice 'type: %', (volume_limitation_id->> 'volume_limitation_name')::text;
		END LOOP;
		
		--desplegar json_restriction:
		json_restriction := convert_from(decode(NEW.restriction,'base64'), 'UTF-8');
		
		FOR restriction_id IN SELECT jsonb_array_elements((json_restriction->>'values')::jsonb)
		LOOP
			INSERT INTO uas_code_restriction_type(code_restriction_type_restriction_code,uas_zone_version_uas_zone_id)
			SELECT 
			code_restriction_type.restriction_code,
			uas_id
			FROM code_restriction_type
			WHERE code_restriction_type.name= (restriction_id->> 'restriction_type_name')
			RETURNING uas_restriction_code INTO condition_exp_id;
			raise notice 'type: %', (restriction_id->> 'restriction_type_name');
			
			FOR condition_id IN SELECT jsonb_array_elements(restriction_id -> 'conditions')
			LOOP
				raise notice 'upperlimit: %', (condition_id->> 'upperlimitnorestriction');
				raise notice 'condition: %', (condition_id->> 'condition_name');
				INSERT INTO uas_condition_expression_type(condition_expression_type_restriction_code,upperlimitnorestriction,uas_zone_version_uas_zone_id,uas_code_restriction_type_uas_restriction_code)
				SELECT 
				condition_expression_type.restriction_code,
				(condition_id->> 'upperlimitnorestriction')::int,
				uas_id,
				condition_exp_id
				FROM condition_expression_type
				WHERE condition_expression_type.name= (condition_id->> 'condition_name');
				
			END LOOP;
		END LOOP;
		
		-- desplegar json_time_period:
		
		FOR time_periods_id IN SELECT jsonb_array_elements((json_time_period->>'time_periods')::jsonb)
		LOOP
			INSERT INTO time_period(start_date_time,end_date_time,monday,tuesday,wednesday,thursday,friday,saturday,sunday)
			VALUES( 
			TO_TIMESTAMP((time_periods_id->>'start_date'), 'MM/DD/YYYY HH24:MI:SS'),
			TO_TIMESTAMP((time_periods_id->>'end_date'), 'MM/DD/YYYY HH24:MI:SS'),
			CASE WHEN (time_periods_id->'days') @> ' [{"day_name": "monday"}]' THEN 'True'::boolean
			ELSE 'False'
			END,
			CASE WHEN (time_periods_id->'days') @> ' [{"day_name": "tuesday"}]' THEN 'True'::boolean
			ELSE 'False'
			END,
			CASE WHEN (time_periods_id->'days') @> ' [{"day_name": "wednesday"}]' THEN 'True'::boolean
			ELSE 'False'
			END,
			CASE WHEN (time_periods_id->'days') @> ' [{"day_name": "thursday"}]' THEN 'True'::boolean
			ELSE 'False'
			END,
			CASE WHEN (time_periods_id->'days') @> ' [{"day_name": "friday"}]' THEN 'True'::boolean
			ELSE 'False'
			END,
			CASE WHEN (time_periods_id->'days') @> ' [{"day_name": "saturday"}]' THEN 'True'::boolean
			ELSE 'False'
			END,
			CASE WHEN (time_periods_id->'days') @> ' [{"day_name": "sunday"}]' THEN 'True'::boolean
			ELSE 'False'
			END)
			RETURNING time_period_id INTO time_id;
			
			FOR slot_time_id IN SELECT jsonb_array_elements((time_periods_id->>'slots')::jsonb)
			LOOP
				INSERT INTO slot_time(start_time,end_time,time_period_time_period_id)
				VALUES(
				(slot_time_id->>'start_time')::time,
				(slot_time_id->>'end_time')::time,
				time_id
				);
			END LOOP;
			
			INSERT INTO uas_time_period(time_period_time_period_id,uas_zone_version_uas_zone_id)
			VALUES(time_id,uas_id);
			
		END LOOP;
		
		CALL geoawareness_required_check(uas_id);
		CALL remote_id_check(uas_id);
		CALL create_identifier(NEW.sequential_id);

	END IF;
	
	RETURN NEW;
	END;
$insert_newg$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION geometryinsert() RETURNS TRIGGER AS $geometryinsert$
	DECLARE
	
	
	BEGIN
	IF NEW.geom_buffer is null THEN
		NEW.geom_buffer := CASE WHEN NEW.geom_radius>0 THEN ST_GeomFromText(ST_AsEWKT(ST_Multi(ST_AsEWKT(ST_Buffer(ST_CollectionExtract(NEW.geom_center,1)::geography, NEW.geom_radius)))))
			WHEN NEW.geom_radius<0 THEN ST_GeomFromText(ST_AsEWKT(ST_Multi(ST_AsEWKT(ST_Buffer(ST_CollectionExtract(NEW.geom_center,2)::geography, abs(NEW.geom_radius))))))
		END;
	ELSE
		--AIRSPACE_VOLUME
		NEW.geom_center := ST_GeomFromText(CONCAT('SRID=4326;GEOMETRYCOLLECTION(',ST_AsText(ST_Centroid(New.geom_buffer)),')'));
		NEW.geom_radius := 0;

	END IF;

	
	RETURN NEW;
	END;
$geometryinsert$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION delete_g() RETURNS TRIGGER AS $delete_g$
	DECLARE
	uas_id uuid;
	m_id uuid;
	a_id uuid;
	n_id uuid;
	v_id uuid;
	array_time_id uuid[];
	time_id uuid;
	i int;
	
	BEGIN
	v_id:= (SELECT vol_id FROM airspace_volume WHERE sequential_id=OLD.sequential_id);
	raise notice 'vol_id: %', v_id;
	
	m_id:=(SELECT metadata_metada_id FROM uas_zone_version WHERE airspace_volume_vol_id=v_id);
	raise notice 'm_id: %', m_id;
	a_id:=(SELECT authority_authorityfrom_oid FROM uas_zone_version WHERE airspace_volume_vol_id=v_id);
	raise notice 'a_id: %', a_id;
	n_id:=(SELECT authority_notificationto_oid FROM uas_zone_version WHERE airspace_volume_vol_id=v_id);
	raise notice 'n_id: %', n_id;
	uas_id:=(SELECT uas_zone_id FROM uas_zone_version WHERE airspace_volume_vol_id=v_id);
	raise notice 'uas_id: %', uas_id;
		
	DELETE FROM uas_condition_expression_type WHERE uas_zone_version_uas_zone_id=uas_id;
	DELETE FROM uas_code_restriction_type WHERE uas_zone_version_uas_zone_id=uas_id;
	DELETE FROM uas_code_uspace_class_type WHERE uas_zone_version_uas_zone_id=uas_id;
	DELETE FROM uas_code_zone_reason WHERE uas_zone_version_uas_zone_id=uas_id;
	DELETE FROM volume_limitation WHERE uas_zone_version_uas_zone_id=uas_id;

	array_time_id := ARRAY(SELECT time_period_time_period_id FROM uas_time_period WHERE uas_zone_version_uas_zone_id=uas_id);
	raise notice 'time_id:%', array_time_id;
	
	DELETE FROM uas_time_period WHERE uas_zone_version_uas_zone_id=uas_id;
	IF array_length(array_time_id,1)>0 THEN
		FOR i IN 1..array_length(array_time_id,1)
		LOOP
			raise notice 'time_id:%', array_time_id[i];
			DELETE FROM slot_time WHERE time_period_time_period_id=array_time_id[i];
			DELETE FROM time_period WHERE time_period_id=array_time_id[i];
		END LOOP;
	END IF;
	
	
	DELETE FROM uas_zone_version WHERE uas_zone_id= uas_id;
	
	DELETE FROM metadata WHERE metada_id=m_id;
	DELETE FROM airspace_volume WHERE vol_id=v_id;
	DELETE FROM authority WHERE authority_oid=a_id;
	DELETE FROM authority WHERE authority_oid=n_id;
	
	RETURN OLD;
	END;
$delete_g$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_g_code_zone_type() RETURNS TRIGGER AS $update_g_code_zone_type$
	DECLARE
	uas_id uuid;
	code_id int;
	
	BEGIN
	code_id:=(SELECT type_code FROM code_zone_type WHERE name=new.code_zone_type);
	
	UPDATE uas_zone_version SET code_zone_type_type_code= code_id
	WHERE airspace_volume_vol_id IN (SELECT vol_id FROM airspace_volume WHERE sequential_id=new.sequential_id)
	RETURNING uas_zone_id INTO uas_id;
	raise notice 'uas_id:%', uas_id;
	
	RETURN NEW;
	END;
$update_g_code_zone_type$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_g_data_capture_prohibition() RETURNS TRIGGER AS $update_g_data_capture_prohibition$
	DECLARE
	uas_id uuid;
	
	BEGIN
	
	UPDATE uas_zone_version SET data_capture_prohibition= new.data_capture_prohibition
	WHERE airspace_volume_vol_id IN (SELECT vol_id FROM airspace_volume WHERE sequential_id=new.sequential_id)
	RETURNING uas_zone_id INTO uas_id;
	raise notice 'uas:%', uas_id;
	
	RETURN NEW;
	END;
$update_g_data_capture_prohibition$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_g_active() RETURNS TRIGGER AS $update_g_active$
	DECLARE
	uas_id uuid;
	
	BEGIN
	
	UPDATE airspace_volume SET active = NEW.active
	WHERE airspace_volume_vol_id IN (SELECT vol_id FROM airspace_volume WHERE sequential_id=new.sequential_id)
	RETURNING uas_zone_id INTO uas_id;
	raise notice 'uas:%', uas_id;
	
	RETURN NEW;
	END;
$update_g_active$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_g_geom_buffer() RETURNS TRIGGER AS $update_g_geom_buffer$
	DECLARE
	v_id uuid;
	
	BEGIN
	
	UPDATE airspace_volume SET geom_buffer= new.geom_buffer WHERE sequential_id=new.sequential_id 
	RETURNING vol_id INTO v_id;
	raise notice 'vol:%', v_id;
	
	RETURN NEW;
	END;
$update_g_geom_buffer$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION geometryinsert() RETURNS TRIGGER AS $geometryinsert$
	DECLARE
	uas_id uuid;
	m_id uuid;
	a_id uuid;
	n_id uuid;
	v_id uuid;
	array_time_id uuid[];
	time_id uuid;
	i int;
	
	BEGIN
	IF NEW.geom_radius is not null THEN
		NEW.geom_buffer := CASE WHEN NEW.geom_radius>0 THEN ST_GeomFromText(ST_AsEWKT(ST_Multi(ST_AsEWKT(ST_Buffer(ST_CollectionExtract(NEW.geom_center,1)::geography, NEW.geom_radius)))))
			WHEN NEW.geom_radius<0 THEN ST_GeomFromText(ST_AsEWKT(ST_Multi(ST_AsEWKT(ST_Buffer(ST_CollectionExtract(NEW.geom_center,2)::geography, abs(NEW.geom_radius))))))
		END;
	ELSE
		--AIRSPACE_VOLUME
		NEW.geom_center := ST_GeomFromText(CONCAT('SRID=4326;GEOMETRYCOLLECTION(',ST_AsText(ST_Centroid(New.geom_buffer)),')'));
		NEW.geom_radius := 0;

	END IF;

	
	RETURN NEW;
	END;
$geometryinsert$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_g_geom_rc() RETURNS TRIGGER AS $update_g_geom_rc$
	DECLARE
	v_id uuid;
	
	BEGIN
	IF NEW.geom_radius is not null THEN
		NEW.geom_buffer := CASE WHEN NEW.geom_radius>0 THEN ST_GeomFromText(ST_AsEWKT(ST_Multi(ST_AsEWKT(ST_Buffer(ST_CollectionExtract(NEW.geom_center,1)::geography, NEW.geom_radius)))))
			WHEN NEW.geom_radius<0 THEN ST_GeomFromText(ST_AsEWKT(ST_Multi(ST_AsEWKT(ST_Buffer(ST_CollectionExtract(NEW.geom_center,2)::geography, abs(NEW.geom_radius))))))
		END;
	ELSE
		--AIRSPACE_VOLUME
		NEW.geom_center := ST_GeomFromText(CONCAT('SRID=4326;GEOMETRYCOLLECTION(',ST_AsText(ST_Centroid(New.geom_buffer)),')'));
		NEW.geom_radius := 0;
	END IF;
	
	UPDATE airspace_volume SET geom_buffer= new.geom_buffer, geom_center= new.geom_center, geom_radius= new.geom_radius
	WHERE sequential_id=new.sequential_id
	RETURNING vol_id INTO v_id;
	raise notice 'vol:%', v_id;
	
	RETURN NEW;
	END;

$update_g_geom_rc$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_g() RETURNS TRIGGER AS $update_g$
	
	BEGIN
	

	UPDATE metadata SET update_date_time = CURRENT_DATE 
	WHERE metada_id IN 
	(SELECT metadata_metada_id FROM uas_zone_version WHERE airspace_volume_vol_id IN 
	(SELECT vol_id FROM airspace_volume WHERE sequential_id = NEW.sequential_id));
	
	RETURN NEW;
	END;
$update_g$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_g_authorityfrom() RETURNS TRIGGER AS $update_g_authorityfrom$
	DECLARE
	a_id uuid;
	json_authority jsonb='{}'::jsonb;
	
	BEGIN

	json_authority := convert_from(decode(NEW.authorityfrom,'base64'), 'UTF-8');

	UPDATE authority
	SET
	name=(json_authority->>'name'), 
	service=(json_authority->>'service'), 
	contact_name=(json_authority->>'contact_name'), 
	site_url=(json_authority->>'site_url'),
	email=(json_authority->>'email'), 
	phone=(json_authority->>'phone')
	WHERE authority_oid IN
	(SELECT uas_zone_version.authority_authorityfrom_oid 
	FROM (airspace_volume 
	JOIN  uas_zone_version ON uas_zone_version.airspace_volume_vol_id = airspace_volume.vol_id)
	WHERE airspace_volume.sequential_id=NEW.sequential_id);
	
	RETURN NEW;
	END;
$update_g_authorityfrom$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_g_notificationto() RETURNS TRIGGER AS $update_g_notificationto$
	DECLARE
	a_id uuid;
	json_authority jsonb='{}'::jsonb;
	
	BEGIN

	json_authority := convert_from(decode(NEW.notificationto,'base64'), 'UTF-8');

	UPDATE authority
	SET
	name=(json_authority->>'name'), 
	service=(json_authority->>'service'), 
	contact_name=(json_authority->>'contact_name'), 
	site_url=(json_authority->>'site_url'),
	email=(json_authority->>'email'), 
	phone=(json_authority->>'phone')
	WHERE authority_oid IN
	(SELECT uas_zone_version.authority_notificationto_oid 
	FROM (airspace_volume 
	JOIN  uas_zone_version ON uas_zone_version.airspace_volume_vol_id = airspace_volume.vol_id)
	WHERE airspace_volume.sequential_id=NEW.sequential_id);
	
	RETURN NEW;
	END;
$update_g_notificationto$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_g_code_uspace() RETURNS TRIGGER AS $update_g_code_uspace$
	DECLARE
	uas_id uuid;
	json_uspace jsonb='{}'::jsonb;
	uspace_id jsonb='{}'::jsonb;
	
	BEGIN
	uas_id:= (SELECT uas_zone_id FROM uas_zone_version WHERE airspace_volume_vol_id IN 
			  (SELECT vol_id FROM airspace_volume WHERE sequential_id= NEW.sequential_id));
	DELETE FROM uas_code_uspace_class_type WHERE uas_zone_version_uas_zone_id= uas_id;
	json_uspace = convert_from(decode(NEW.code_uspace_class_type,'base64'), 'UTF-8');
	
	FOR uspace_id IN  SELECT jsonb_array_elements((json_uspace->>'values')::jsonb)
	LOOP
		INSERT INTO uas_code_uspace_class_type(code_uspace_class_type_u_space_code,uas_zone_version_uas_zone_id)
		SELECT 
		code_uspace_class_type.u_space_code,
		uas_id
		FROM code_uspace_class_type
		WHERE code_uspace_class_type.name=(uspace_id ->> 'uspace_class_type_name')::text;
		raise notice 'type: %', (uspace_id ->> 'uspace_class_type_name')::text;
	END LOOP;
	
	RETURN NEW;
	END;
$update_g_code_uspace$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_g_code_zone_reason() RETURNS TRIGGER AS $update_g_code_zone_reason$
	DECLARE
	uas_id uuid;
	json_zone_reason jsonb='{}'::jsonb;
	zone_reason_id jsonb='{}'::jsonb;
	
	BEGIN
	uas_id:= (SELECT uas_zone_id FROM uas_zone_version WHERE airspace_volume_vol_id IN 
			  (SELECT vol_id FROM airspace_volume WHERE sequential_id= NEW.sequential_id));
	DELETE FROM uas_code_zone_reason WHERE uas_zone_version_uas_zone_id= uas_id;
	--desplegar json_zone_reason:
	json_zone_reason = convert_from(decode(NEW.code_zone_reason,'base64'), 'UTF-8');
	
	FOR zone_reason_id IN SELECT jsonb_array_elements((json_zone_reason->>'values')::jsonb)
	LOOP
		INSERT INTO uas_code_zone_reason(code_zone_reason_code_zone_reason,uas_zone_version_uas_zone_id)
		SELECT 
		code_zone_reason.code_zone_reason,
		uas_id
		FROM code_zone_reason 
		WHERE code_zone_reason.name = (zone_reason_id ->> 'code_zone_type_name')::text;
		raise notice 'type: %', (zone_reason_id ->> 'code_zone_type_name')::text;
	END LOOP;
	
	RETURN NEW;
	END;
$update_g_code_zone_reason$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_g_layer_name() RETURNS TRIGGER AS $update_g_layer_name$
	DECLARE
	l_id uuid;
	
	BEGIN
	
	l_id:= (SELECT layer_id FROM layers_volume WHERE name=NEW.layer_name);
	UPDATE airspace_volume SET layers_layer_id= l_id WHERE sequential_id=new.sequential_id;
	raise notice 'vol:%', l_id;
	
	RETURN NEW;
	END;
$update_g_layer_name$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_g_lower_limit() RETURNS TRIGGER AS $update_g_lower_limit$
	DECLARE
	v_id uuid;
	
	BEGIN
	UPDATE airspace_volume SET lower_limit= new.lower_limit 
	WHERE sequential_id=new.sequential_id
	RETURNING vol_id INTO v_id;
	raise notice 'vol:%', v_id;
	
	RETURN NEW;
	END;
$update_g_lower_limit$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_g_message() RETURNS TRIGGER AS $update_g_message$
	DECLARE
	uas_id uuid;
	
	BEGIN
	
	UPDATE uas_zone_version SET message= new.message
	WHERE airspace_volume_vol_id IN (SELECT vol_id FROM airspace_volume WHERE sequential_id=new.sequential_id)
	RETURNING uas_zone_id INTO uas_id;
	raise notice 'uas:%', uas_id;
	
	RETURN NEW;
	END;
$update_g_message$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_g_metadata() RETURNS TRIGGER AS $update_g_metadata$
	DECLARE
	m_id uuid;
	
	BEGIN
	
	UPDATE metadata SET author= new.author_metadata, creation_date_time = new.creation_datetime
	WHERE metada_id IN (SELECT metadata_metada_id FROM uas_zone_version
	WHERE airspace_volume_vol_id IN (SELECT vol_id FROM airspace_volume WHERE sequential_id=new.sequential_id))
	RETURNING metada_id INTO m_id;
	raise notice 'metadata_id:%', m_id;
	
	RETURN NEW;
	END;
$update_g_metadata$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_g_identifier() RETURNS TRIGGER AS $update_g_identifier$
	DECLARE
	uas_id uuid;
	
	BEGIN
	
	UPDATE uas_zone_version SET identifier= new.identifier
	WHERE airspace_volume_vol_id IN (SELECT vol_id FROM airspace_volume WHERE sequential_id=new.sequential_id)
	RETURNING uas_zone_id INTO uas_id;
	raise notice 'uas_zone_id:%', uas_id;
	
	RETURN NEW;
	END;
$update_g_identifier$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_g_intervalbefore() RETURNS TRIGGER AS $update_g_intervalbefore$
	DECLARE
	uas_id uuid;
	
	BEGIN
	
	UPDATE uas_zone_version SET intervalbefore= new.intervalbefore
	WHERE airspace_volume_vol_id IN (SELECT vol_id FROM airspace_volume WHERE sequential_id=new.sequential_id)
	RETURNING uas_zone_id INTO uas_id;
	raise notice 'uas_zone_id:%', uas_id;
	
	RETURN NEW;
	END;
$update_g_intervalbefore$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_g_region() RETURNS TRIGGER AS $update_g_region$
	DECLARE
	uas_id uuid;
	
	BEGIN
	
	UPDATE uas_zone_version SET region= new.region
	WHERE airspace_volume_vol_id IN (SELECT vol_id FROM airspace_volume WHERE sequential_id=new.sequential_id)
	RETURNING uas_zone_id INTO uas_id;
	raise notice 'uas_zone_id:%', uas_id;
	
	RETURN NEW;
	END;
$update_g_region$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_g_country() RETURNS TRIGGER AS $update_g_country$
	DECLARE
	uas_id uuid;
	
	BEGIN
	
	UPDATE uas_zone_version SET country_id= new.country_id
	WHERE airspace_volume_vol_id IN (SELECT vol_id FROM airspace_volume WHERE sequential_id=new.sequential_id)
	RETURNING uas_zone_id INTO uas_id;
	raise notice 'uas_zone_id:%', uas_id;
	
	RETURN NEW;
	END;
$update_g_country$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_e_metadata() RETURNS TRIGGER AS $update_e_metadata$
	DECLARE
	uas_id uuid;
	
	BEGIN
	SET session_replication_role = replica; 
	uas_id:= (SELECT uas_zone_id FROm uas_zone_version 
	where metadata_metada_id=NEW.metada_id);

	UPDATE geofences
	SET 
	update_datetime = NEW.update_date_time
	WHERE
	sequential_id IN
	(SELECT airspace_volume.sequential_id 
	FROM ((metadata
	JOIN uas_zone_version ON uas_zone_version.metadata_metada_id=metadata.metada_id)
	JOIN airspace_volume ON airspace_volume.vol_id= uas_zone_version.airspace_volume_vol_id)
	WHERE metadata.metada_id=NEW.metada_id);
	SET session_replication_role = DEFAULT; 
	
	RETURN NEW;
	END;
$update_e_metadata$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_g_featureprop() RETURNS TRIGGER AS $update_g_featureprop$
	DECLARE
	origin_id uuid;
	
	BEGIN
	origin_id :=(SELECT geom_origin FROM airspace_volume WHERE sequential_id=NEW.sequential_id);

	IF origin_id is null THEN
		RETURN OLD;
	ELSE 
		UPDATE features SET prop2_value=NEW.contact, prop3_value=NEW.url, prop4_value= NEW.oid WHERE feature_id= origin_id;
		RETURN NEW;
	END IF; 

	END;
$update_g_featureprop$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_g_checkboxs() RETURNS TRIGGER AS $update_g_checkboxs$
	DECLARE
	origin_id uuid;
	
	BEGIN
	
	RETURN OLD;
	
	END;
$update_g_checkboxs$ LANGUAGE plpgsql;
