-- JSON RESTRICTION
CREATE OR REPLACE PROCEDURE json_restriction(id_r uuid)
LANGUAGE plpgsql    
AS $$
DECLARE
json_r jsonb='{}'::jsonb;
json_r_values jsonb[];
json_c_values jsonb[];
json_op_values jsonb[];
json_d_values jsonb[];
op varchar(50);
BEGIN
SET session_replication_role = replica;
json_c_values := ARRAY(SELECT
	(CASE WHEN (table1.id_c=3) 
    THEN
	jsonb_build_object('condition_name', table1.name_c, 'upperlimitnorestriction',table1.limitv)
	ELSE 
	jsonb_build_object('condition_name', table1.name_c)
	END)
	FROM ( SELECT condition_expression_type.name as name_c, uas_condition_expression_type.condition_expression_type_restriction_code as id_c, uas_condition_expression_type.upperlimitnorestriction as limitv
	FROM condition_expression_type, uas_condition_expression_type
	WHERE uas_condition_expression_type.uas_zone_version_uas_zone_id=id_r 
	AND condition_expression_type.restriction_code=uas_condition_expression_type.condition_expression_type_restriction_code) as table1
	GROUP BY table1.name_c, table1.id_c, table1.limitv);
	
json_r_values := ARRAY(SELECT
	(CASE WHEN (table2.id_rest=3) 
    THEN
	jsonb_build_object('restriction_type_name', table2.name_r, 'conditions',json_c_values)
	ELSE 
	jsonb_build_object('restriction_type_name', table2.name_r)
	END)
	FROM ( SELECT code_restriction_type.name as name_r, uas_code_restriction_type.code_restriction_type_restriction_code as id_rest
	FROM code_restriction_type, uas_code_restriction_type
	WHERE uas_code_restriction_type.uas_zone_version_uas_zone_id=id_r 
  	AND code_restriction_type.restriction_code=uas_code_restriction_type.code_restriction_type_restriction_code) as table2
	GROUP BY table2.name_r, table2.id_rest);
json_r := jsonb_build_object('type', 'code_restriction_type', 'values', json_r_values);	
UPDATE geofences SET restriction= encode(convert_to(json_r::varchar, 'UTF-8'),'base64')WHERE sequential_id IN (SELECT sequential_id FROM (airspace_volume JOIN uas_zone_version ON airspace_volume.vol_id= uas_zone_version.airspace_volume_vol_id) WHERE uas_zone_version.uas_zone_id=id_r);
SET session_replication_role = DEFAULT;
END;

$$;

--JSON ZONE REASON
CREATE OR REPLACE PROCEDURE json_zone_reason(id_r uuid)
LANGUAGE plpgsql    
AS $$
DECLARE
json_z json='{}'::json;
json_z_values json[];
BEGIN
SET session_replication_role = replica;
json_z_values := ARRAY(SELECT json_build_object('code_zone_type_name', code_zone_reason.name)
FROM code_zone_reason, uas_code_zone_reason
WHERE uas_code_zone_reason.uas_zone_version_uas_zone_id=id_r
AND code_zone_reason.code_zone_reason=uas_code_zone_reason.code_zone_reason_code_zone_reason);

json_z := json_build_object('type', 'code_zone_reason' , 'values', json_z_values);
UPDATE geofences SET code_zone_reason= encode(convert_to(json_z::varchar, 'UTF-8'),'base64') WHERE sequential_id IN (SELECT sequential_id FROM (airspace_volume JOIN uas_zone_version ON airspace_volume.vol_id= uas_zone_version.airspace_volume_vol_id) WHERE uas_zone_version.uas_zone_id=id_r);
SET session_replication_role = DEFAULT;
END;
$$;

--JSON TIME PERIOD
CREATE OR REPLACE PROCEDURE json_time_period(id_r uuid)
LANGUAGE plpgsql    
AS $$
DECLARE
json_values json='{}'::json;
code_type varchar;
code_type_id int;
json_slots json[];
json_days json[];
json_time_periods json[];
time_id uuid;

BEGIN
SET session_replication_role = replica;
code_type := (SELECT  code_period_type.name
FROM uas_zone_version, code_period_type
WHERE uas_zone_version.uas_zone_id=id_r AND
code_period_type.period_type_id=uas_zone_version.code_period_type_period_type_id			 );

code_type_id := (SELECT uas_zone_version.code_period_type_period_type_id
FROM uas_zone_version
WHERE uas_zone_version.uas_zone_id=id_r);

IF code_type_id!= 1 THEN
	FOR time_id IN (SELECT uas_time_period.time_period_time_period_id
	FROM uas_time_period
	WHERE uas_time_period.uas_zone_version_uas_zone_id=id_r)
	LOOP
		json_slots := ARRAY(SELECT json_build_object('start_time', slot_time.start_time, 'end_time', slot_time.end_time)
			FROM slot_time
			WHERE slot_time.time_period_time_period_id=time_id);
		
		json_days := ARRAY(SELECT json_build_object( 'day_name', D.day_name)
						FROM(
						   select time_period_id,day_name,
							case day_name
								when 'monday' then monday
								when 'tuesday' then tuesday
								when 'wednesday' then wednesday
								when 'thursday' then thursday
								when 'friday' then friday
								when 'saturday' then saturday
								when 'sunday' then sunday
							end as day_bool
							from time_period
							Cross Join (values('monday'),('tuesday'),('wednesday'),('thursday'),('friday'),('saturday'),('sunday')) AS Subjct(day_name)
							where time_period.time_period_id=time_id)as D
						where D.day_bool=true);
		
		json_time_periods := json_time_periods || ARRAY(SELECT json_build_object('start_date',time_period.start_date_time, 'end_date', time_period.end_date_time, 'days', json_days, 'slots', json_slots) 
							FROM time_period
							WHERE time_period_id= time_id);

		
	END LOOP;
	
	json_values :=  json_build_object('type',code_type,'time_periods', json_time_periods);
	
END IF;

IF code_type_id=1 THEN
	json_values :=  json_build_object('type',code_type);
END IF;

UPDATE geofences SET time_period=encode(convert_to(json_values::varchar, 'UTF-8'),'base64') WHERE sequential_id IN (SELECT sequential_id FROM (airspace_volume JOIN uas_zone_version ON airspace_volume.vol_id= uas_zone_version.airspace_volume_vol_id) WHERE uas_zone_version.uas_zone_id=id_r);
SET session_replication_role = DEFAULT;
END;


$$;

--JSON VOLUME LIMITATION
CREATE OR REPLACE PROCEDURE json_volume_limitation(id_r uuid)
LANGUAGE plpgsql    
AS $$
DECLARE
json_v json='{}'::json;
json_v_values json[];
BEGIN
SET session_replication_role = replica;
json_v_values := ARRAY(SELECT json_build_object('volume_limitation_name', volume_limitation_definitions.name)
FROM volume_limitation_definitions, volume_limitation
WHERE volume_limitation.uas_zone_version_uas_zone_id=id_r
AND volume_limitation_definitions.limitation_definition_id=volume_limitation.volume_limitation_definitions_limitation_definition_id);
json_v := json_build_object('type', 'volume_limitation', 'values', json_v_values);
UPDATE geofences SET volume_limitations=encode(convert_to(json_v::varchar, 'UTF-8'),'base64') WHERE sequential_id IN (SELECT sequential_id FROM (airspace_volume JOIN uas_zone_version ON airspace_volume.vol_id= uas_zone_version.airspace_volume_vol_id) WHERE uas_zone_version.uas_zone_id=id_r);
SET session_replication_role = DEFAULT;
END;

$$;

-- JSON USPACE
CREATE OR REPLACE PROCEDURE json_uspace(id_r uuid)
LANGUAGE plpgsql    
AS $$
DECLARE
json_u json='{}'::json;
json_u_values json[];
BEGIN
SET session_replication_role = replica;
json_u_values := ARRAY(SELECT json_build_object('uspace_class_type_name', code_uspace_class_type.name)
FROM code_uspace_class_type, uas_code_uspace_class_type
WHERE uas_code_uspace_class_type.uas_zone_version_uas_zone_id=id_r
AND code_uspace_class_type.u_space_code=uas_code_uspace_class_type.code_uspace_class_type_u_space_code);

json_u := json_build_object('type', 'code_uspace_class_type', 'values', json_u_values);
UPDATE geofences SET code_uspace_class_type= encode(convert_to(json_u::varchar, 'UTF-8'),'base64') WHERE sequential_id IN (SELECT sequential_id FROM (airspace_volume JOIN uas_zone_version ON airspace_volume.vol_id= uas_zone_version.airspace_volume_vol_id) WHERE uas_zone_version.uas_zone_id=id_r);
SET session_replication_role = DEFAULT;
END;
$$;

--CREATE A ROW ON GEOFENCES TABLE AFTER A CREATION OF A GEOFENCE IN EUROCAE SCHEMA
CREATE OR REPLACE PROCEDURE create_fgeofence(geofence_id uuid)
LANGUAGE plpgsql    
AS $$
DECLARE
uas_id uuid;
seq_id int;
BEGIN
SET session_replication_role = replica; 

WITH ins1 AS (
	INSERT INTO geofences(layer_name,geom_buffer,name_feature,
	lower_limit,upper_limit, lower_vertical_reference, upper_vertical_reference,geom_center,geom_radius,active,contact,url,oid,country_id,
	region,code_zone_type,message,data_capture_prohibition,author_metadata, creation_datetime, update_datetime, intervalBefore,identifier)
	(SELECT 
	layers_volume.name layer_name,
	airspace_volume.geom_buffer geom_buffer,
	uas_zone_version.name name_feature,
	airspace_volume.lower_limit lower_limit,  
	airspace_volume.upper_limit upper_limit,
	lower_ref.name lower_vertical_reference,
	upper_ref.name upper_vertical_reference,
	airspace_volume.geom_center geom_center,
	airspace_volume.geom_radius geom_radius,
	airspace_volume.active active,
	features.prop2_value contact,
	features.prop3_value url,
	features.prop4_value oid,
	uas_zone_version.country_id country_id,
	uas_zone_version.region region,
	code_zone_type.name code_zone_type,
	uas_zone_version.message message,
	uas_zone_version.data_capture_prohibition data_capture_prohibition,
	metadata.author author_metadata,
	metadata.creation_date_time creation_datetime,
	metadata.update_date_time update_datetime,
	uas_zone_version.intervalBefore intervalBefore,
	uas_zone_version.identifier identifier
	FROM 
	(((((((airspace_volume
	JOIN layers_volume ON airspace_volume.layers_layer_id = layers_volume.layer_id)
	JOIN uas_zone_version ON airspace_volume.vol_id= uas_zone_version.airspace_volume_vol_id) 
	JOIN code_zone_type ON code_zone_type.type_code = uas_zone_version.code_zone_type_type_code) 
	JOIN metadata ON metadata.metada_id = uas_zone_version.metadata_metada_id)
	JOIN code_vertical_reference AS upper_ref ON upper_ref.vertical_reference_code = airspace_volume.upper_vertical_reference)
	JOIN code_vertical_reference AS lower_ref ON lower_ref.vertical_reference_code = airspace_volume.lower_vertical_reference)
	LEFT JOIN features ON airspace_volume.geom_origin = features.feature_id )
	WHERE airspace_volume.vol_id=geofence_id)
	RETURNING sequential_id AS s_id
)

UPDATE airspace_volume SET sequential_id = (SELECT s_id FROM ins1) WHERE vol_id = geofence_id;
uas_id= (SELECT uas_zone_id FROM uas_zone_version WHERE uas_zone_version.airspace_volume_vol_id=geofence_id);
seq_id= (SELECT sequential_id FROM airspace_volume WHERE vol_id=geofence_id);
CALL json_restriction(uas_id);
CALL json_time_period(uas_id);
CALL json_uspace(uas_id);
CALL json_volume_limitation(uas_id);
CALL json_zone_reason(uas_id);
CALL json_authorityfrom(uas_id);
CALL json_notificationto(uas_id);

CALL geoawareness_required_check(uas_id);
CALL remote_id_check(uas_id);
CALL create_identifier(seq_id);

SET session_replication_role = DEFAULT; 
END;


$$;

CREATE OR REPLACE PROCEDURE update_fgeofence(geofence_id uuid)
LANGUAGE plpgsql    
AS $$
DECLARE
uas_id uuid;
s_id int;
BEGIN
SET session_replication_role = replica; 
UPDATE geofences 
SET
layer_name= subquery.layer_name,
geom_buffer=subquery.geom_buffer,
name_feature=subquery.name_feature,
lower_limit=subquery.lower_limit,
upper_limit=subquery.upper_limit,
lower_vertical_reference=subquery.lower_vertical_reference,
upper_vertical_reference=subquery.upper_vertical_reference,
geom_center=subquery.geom_center,
geom_radius=subquery.geom_radius,
active = subquery.active,
contact=subquery.contact,
url=subquery.url,
oid=subquery.oid,
country_id=subquery.country_id,
region=subquery.region,
code_zone_type=subquery.code_zone_type,
message=subquery.message,
data_capture_prohibition=subquery.data_capture_prohibition,
author_metadata=subquery.author_metadata,
creation_datetime = subquery.creation_datetime,
update_datetime = subquery.update_datetime,
intervalBefore = subquery.intervalBefore,
identifier = subquery.identifier
FROM (SELECT 
layers_volume.name layer_name,
airspace_volume.geom_buffer geom_buffer,
uas_zone_version.name name_feature,
airspace_volume.lower_limit lower_limit,  
airspace_volume.upper_limit upper_limit,
lower_ref.name lower_vertical_reference,
upper_ref.name upper_vertical_reference,
airspace_volume.geom_center geom_center,
airspace_volume.geom_radius geom_radius,
airspace_volume.active active,
features.prop2_value contact,
features.prop3_value url,
features.prop4_value oid,
uas_zone_version.country_id country_id,
uas_zone_version.region region,
code_zone_type.name code_zone_type,
uas_zone_version.message message,
uas_zone_version.data_capture_prohibition data_capture_prohibition,
metadata.author author_metadata,
metadata.creation_date_time creation_datetime,
metadata.update_date_time update_datetime,
uas_zone_version.intervalBefore intervalBefore,
airspace_volume.sequential_id sequential_id,
uas_zone_version.identifier identifier
FROM 
(((((((airspace_volume
JOIN layers_volume ON airspace_volume.layers_layer_id = layers_volume.layer_id)
JOIN uas_zone_version ON airspace_volume.vol_id= uas_zone_version.airspace_volume_vol_id) 
JOIN code_zone_type ON code_zone_type.type_code = uas_zone_version.code_zone_type_type_code) 
JOIN metadata ON metadata.metada_id = uas_zone_version.metadata_metada_id)
JOIN code_vertical_reference AS upper_ref ON upper_ref.vertical_reference_code = airspace_volume.upper_vertical_reference)
JOIN code_vertical_reference AS lower_ref ON lower_ref.vertical_reference_code = airspace_volume.lower_vertical_reference)
LEFT JOIN features ON airspace_volume.geom_origin = features.feature_id )
WHERE airspace_volume.vol_id=geofence_id) AS subquery
WHERE geofences.sequential_id = subquery.sequential_id
RETURNING geofences.sequential_id INTO s_id;

UPDATE airspace_volume SET sequential_id = s_id WHERE vol_id = geofence_id;
uas_id= (SELECT uas_zone_id FROM uas_zone_version WHERE uas_zone_version.airspace_volume_vol_id=geofence_id);

CALL json_restriction(uas_id);
CALL json_time_period(uas_id);
CALL json_uspace(uas_id);
CALL json_volume_limitation(uas_id);
CALL json_zone_reason(uas_id);
CALL json_authorityfrom(uas_id);
CALL json_notificationto(uas_id);

CALL geoawareness_required_check(uas_id);
CALL remote_id_check(uas_id);

SET session_replication_role = DEFAULT; 
END;


$$;

CREATE OR REPLACE PROCEDURE delete_fgeofence(v_id uuid)
LANGUAGE plpgsql    
AS $$
DECLARE
s_id int;

BEGIN
s_id:= (SELECT sequential_id FROM airspace_volume WHERE vol_id=v_id);
DELETE FROM geofences WHERE sequential_id=s_id;
END;

$$;




CREATE OR REPLACE PROCEDURE create_fcartography(f_id uuid)
LANGUAGE plpgsql    
AS $$
	DECLARE
	s_id int;
	BEGIN
	SET session_replication_role = replica; 
	INSERT INTO visual_cartography(geom_point,geom_linestring,
	geom_polygon,geom_center,prop1_value,prop2_value,prop3_value,
	prop4_value,prop1_code,prop2_code,prop3_code,
	prop4_code,layer_name)
	SELECT 
	geom_point,
	geom_linestring,
	geom_polygon,
	geom_center,
	prop1_value,
	prop2_value,
	prop3_value,
	prop4_value,
	column1.name,
	column2.name,
	column3.name,
	column4.name,
	layer.name
	FROM
	(((((features
	JOIN layers_cartography AS layer ON layer.layer_id=layers_layer_id)
	JOIN feature_columns_codification AS column1 ON column1.column_id=prop1_code)
	JOIN feature_columns_codification AS column2 ON column2.column_id=prop2_code)
	JOIN feature_columns_codification AS column3 ON column3.column_id=prop3_code)
	JOIN feature_columns_codification AS column4 ON column4.column_id=prop4_code)
	WHERE 
	feature_id=f_id
	RETURNING sequential_id INTO s_id;
	

	UPDATE features SET sequential_id = s_id WHERE feature_id = f_id;
	SET session_replication_role = DEFAULT;
	END;
$$;

CREATE OR REPLACE PROCEDURE update_metadata(uas_id uuid)
LANGUAGE plpgsql    
AS $$
	DECLARE

	BEGIN

	UPDATE metadata SET update_date_time = CURRENT_DATE 
	WHERE metada_id IN 
	(SELECT metadata_metada_id FROM uas_zone_version WHERE uas_zone_id=uas_id);
	
	END;
$$;

CREATE OR REPLACE PROCEDURE json_authorityfrom(uas_id uuid)
LANGUAGE plpgsql    
AS $$
	DECLARE
	json_a json='{}'::json;
	json_a_values json[];
	BEGIN
	SET session_replication_role = replica;
	json_a := (SELECT json_build_object('type', 'authorityfrom','name', authority.name, 'service', authority.service,
	'contact_name', authority.contact_name, 'site_url', authority.site_url, 'email', authority.email, 'phone', authority.phone)
	FROM (uas_zone_version
	JOIN authority ON authority.authority_oid=uas_zone_version.authority_authorityfrom_oid)
	WHERE uas_zone_version.uas_zone_id=uas_id);

	UPDATE geofences SET authorityfrom= encode(convert_to(json_a::varchar, 'UTF-8'),'base64') WHERE sequential_id IN (SELECT sequential_id FROM (airspace_volume JOIN uas_zone_version ON airspace_volume.vol_id= uas_zone_version.airspace_volume_vol_id) WHERE uas_zone_version.uas_zone_id=uas_id);
	SET session_replication_role = DEFAULT;
	END;
$$;

CREATE OR REPLACE PROCEDURE json_notificationto(uas_id uuid)
LANGUAGE plpgsql    
AS $$
	DECLARE
	json_a json='{}'::json;
	json_a_values json[];
	BEGIN
	SET session_replication_role = replica;
	json_a := (SELECT json_build_object('type', 'notificationto','name', authority.name, 'service', authority.service,
	'contact_name', authority.contact_name, 'site_url', authority.site_url, 'email', authority.email, 'phone', authority.phone)
	FROM (uas_zone_version
	JOIN authority ON authority.authority_oid=uas_zone_version.authority_notificationto_oid)
	WHERE uas_zone_version.uas_zone_id=uas_id);

	UPDATE geofences SET notificationto= encode(convert_to(json_a::varchar, 'UTF-8'),'base64') WHERE sequential_id IN (SELECT sequential_id FROM (airspace_volume JOIN uas_zone_version ON airspace_volume.vol_id= uas_zone_version.airspace_volume_vol_id) WHERE uas_zone_version.uas_zone_id=uas_id);
	SET session_replication_role = DEFAULT;
	END;
$$;

CREATE OR REPLACE PROCEDURE create_identifier(sid integer)
LANGUAGE plpgsql    
AS $$
	DECLARE
	identifier_string varchar(10);
	seq_number varchar;
	seq_numberf varchar;
	n integer;
	counter integer := 0 ;
	BEGIN
	seq_number := CAST(sid as varchar);
	n = 7 - length(seq_number);
	seq_numberf := '';
	loop 
			exit when counter = n ; 
			counter := counter + 1 ; 
			seq_numberf := seq_numberf || CAST('0' as varchar);
	end loop; 
	seq_numberf := 'ESP' || seq_numberf || seq_number;
	raise notice 'length: %', length(seq_number);

	raise notice 'value: %', seq_number;

	raise notice 'value: %', seq_numberf;

	UPDATE geofences SET identifier = seq_numberf WHERE sequential_id=sid;

	END;
$$;

CREATE OR REPLACE PROCEDURE geoawareness_required_check(uas_id uuid)
LANGUAGE plpgsql    
AS $$
	BEGIN
	SET session_replication_role = replica;
	UPDATE geofences SET geoawareness_required='True' WHERE sequential_id IN 
	(SELECT airspace_volume.sequential_id
	FROM 
	((uas_zone_version JOIN uas_condition_expression_type 
	ON uas_condition_expression_type.uas_zone_version_uas_zone_id=uas_zone_version.uas_zone_id)
	JOIN airspace_volume ON airspace_volume.vol_id=uas_zone_version.airspace_volume_vol_id)
	WHERE
	uas_condition_expression_type.condition_expression_type_restriction_code=1 AND
	uas_zone_version.uas_zone_id=uas_id);
	SET session_replication_role = DEFAULT;
	END;
$$;

CREATE OR REPLACE PROCEDURE remote_id_check(uas_id uuid)
LANGUAGE plpgsql    
AS $$
	BEGIN
	SET session_replication_role = replica;
	UPDATE geofences SET remote_id='True' WHERE sequential_id IN 
	(SELECT airspace_volume.sequential_id
	FROM 
	((uas_zone_version JOIN uas_condition_expression_type 
	ON uas_condition_expression_type.uas_zone_version_uas_zone_id=uas_zone_version.uas_zone_id)
	JOIN airspace_volume ON airspace_volume.vol_id=uas_zone_version.airspace_volume_vol_id)
	WHERE
	uas_condition_expression_type.condition_expression_type_restriction_code=2 AND
	uas_zone_version.uas_zone_id=uas_id);
	SET session_replication_role = DEFAULT;
	END;
$$;
CREATE OR REPLACE PROCEDURE remote_id_check(seqid integer)
LANGUAGE plpgsql    
AS $$
	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= seqid));
			  
    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 := (SELECT convert_from(decode(time_period,'base64'), 'UTF-8') FROM geofences WHERE sequential_id=seqid);
	code_type_period_id := (json_time_period->>'type');
	raise notice 'time_periods_json:%', (json_time_period->>'time_periods')::jsonb;
	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( 
		(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;
		raise notice 'time_periods_ids:%', 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;
	END;
$$;