--------------------------------------INSERTION OF NEW FETURE----------------------------------------

Insertion of a new features downloaded from OSM:
Parameters from the geojson:
-properties
-geometry

Parameters define from front/person creating the feature:
-layers_id_feature
-layers_id_geofence
-message

Standar parameters for the osm geofence:
-buffer->defined by the layer
-lower_limit=0
-vertical_reference=1(AGL)
-upper_limit=150
-code_restriction_type=[1]
-code_zone_reason->vec -> defined by the layer
-condition_expression_type->Null -> I am assuming that the restriction is PROHIBITION
-code_uspace_class_type->vec -> defined by the layer
-volume_limitation->vec -> defined by the layer ?? (we have not defined the standar values yet)
-geofence-> true or false -> information provided by the layer
-code_zone_type_type_code=2
-country_id=34
-region=1
-data_capture_prohibition=false
-permanent=True
-start_date
-end_date
-[time_start,time_end,day]->Null -> it is permanent
-[operator_id,drone_id,time]->Null -> I am assuming that ther is not any condition and for this reason any allowed drone

-creation_date_time -> now
-update_date_time -> now
-author -> OSM
-name_authority -> Enaire
-service_authority ->
-contact_name_authority
-site_url_authority
-email_authority->  info@enaire.es
-phone_authority -> 913210211



----INTRODUCTION OF FEATURE------
1.Check if these properties for this osm type are included on the table feature_columns_codification.
If the property is included get the id
(SELECT column_id FROM feature_columns_codification 
WHERE (name = 'property' AND feature_subclass_codification_subclass_id = subclass_id);) 
if not insert the property in the table and get the id.
(INSERT INTO feature_columns_codification (name, feature_subclass_codification_subclass_id,name_code)
VALUES('property_name',subclass_id,property_name_code) RETURNING column_id;)

2.define the center of the geometry:
python lib shapely center=geometry.centroid 
probably exists a funcionality to do the same in gis

3.Insert the data of the feature into features table:
(INSERT INTO features (layers_layer_id,{},geom_center,prop1_value, prop1_code, prop2_value, prop2_code, prop3_value, prop3_code, prop4_value, prop4_code)
VALUES
(layers_id_feature,ST_GeomFromText(ST_AsEWKT(ST_GeomFromText(geometry,4326)::geography)), ST_GeomFromText(ST_AsEWKT(ST_GeomFromText(center,4326)::geography)), 
self.normalize(properties_elements[0]), properties_names[0],self.normalize(properties_elements[1]),properties_names[1],self.normalize(properties_elements[2]),
properties_names[2],self.normalize(properties_elements[3]), properties_names[3],); )

Note: the function self.normalize -> is a function created in order to delete all the accents and other different symbols which could produce and error in the insertion into the DDB.
the varchar inserted in the DDBB cannot have this type of symbols 

REFRESH MV os the layer features:
REFRESH MATERIALIZED VIEW layer_name_feature;

Note: if the feature requiered a geofence keep going with the next section -> information provided by the layer 

----CREATION OF GEOFENCE------
4. Definition of the buffer:
geom_buffer-> (SELECT ST_AsEWKT(ST_Multi(ST_AsEWKT(ST_Buffer(ST_GeomFromText(geometry)::geography, buffer)))); )

5.Insertion of gemetrical parameters into the airspace volume:
INSERT INTO airspace_volume(lower_limit,lower_vertical_reference,
upper_limit,upper_vertical_reference,geom_buffer,layers_layer_id) 
VALUES(lower_limit,vertical_reference,upper_limit,vertical_reference, geom_buffer, layers_id_geofence) RETURNING vol_id;

6.Insertion of time period
INSERT INTO time_period(permanent, start_date_time, end_date_time)
VALUES(permanent, start_date, end_date) RETURNING time_period_id;

7. Insertion of daily period: (in this case Null)
#for each daily_period in vec [time_start,time_end,day]
INSERT INTO daily_period(start_time, end_time, code_week_day_day_number,time_period_time_period_id)
VALUES(time_start,time_end,day,time_period_id);

8. Insert metadata:
INSERT INTO metadata(creation_date_time,update_date_time,author)
VALUES(creation_date_time,update_date_time,author) RETURNING metadata_id;

9. Insert authority:
INSERT INTO authority(name,service,contact_name,site_url,email,phone)
VALUES(name_authority,service_authority,contact_name_authority,site_url_authority,email_authority,phone_authority)
RETURNING authority_oid;

10. Insert definitions of geofence into uas_zone_version:
INSERT INTO uas_zone_version(country_id, name, airspace_volume_vol_id,code_zone_type_type_code,
region,data_capture_prohibition,message,metadata_metada_id,time_period_time_period_id,authority_authority_oid)
VALUES(country_id,name,vol_id,code_zone_type_type_code,region,data_capture_prohibition,message,metadata_id,time_period_id,authority_oid)
RETURNING uas_zone_id

11.Insertion of restrictions into the different tables uas_ to suport relation n to n:
#for each volume_limitation in the vector:
INSERT INTO volume_limitation(volume_limitation_definitions_limitation_definition_id, uas_zone_version_uas_zone_id)
VALUES(volume_limitation, uas_zone_id);

#for each value in the code_uspace_class_type:
INSERT INTO uas_uspace_class_type(code_uspace_class_type_u_space_code,uas_zone_version_uas_zone_id)
VALUES(code_uspace_class_type, uas_zone_id);

#for each value in the vector code_zone_reason:
INSERT INTO uas_code_zone_reason(code_zone_reason_code_zone_reason,uas_zone_version_uas_zone_id)
VALUE(code_zone_reason,uas_zone_id)

#for each value in the vector code_restriction_type:
INSERT INTO uas_code_restriction_type(code_restriction_type_restriction_code,uas_version_uas_zone_id)
VALUES(code_restriction_type,uas_zone_id) RETURNING uas_restriction_code;
#if uas_code_restriction_type==3:
#for each value in vector condition_expression_type 
#if condition_expression_type==3: 
INSERT INTO uas_condition_expression_type(condition_expression_type_restriction_code, uas_code_restrcition_type_uas_retriction_code, uas_zone_version_uas_zone_id,upperlimitnorestriction)
VALUES(condition_expression_type,uas_code_restriction_code,uas_zone_id,upperlimitnorestriction) RETURNING uas_restriction_code;
#else:
INSERT INTO uas_condition_expression_type(condition_expression_type_restriction_code, uas_code_restrcition_type_uas_retriction_code, uas_zone_version_uas_zone_id)
VALUES(condition_expression_type,uas_code_restriction_code,uas_zone_id) RETURNING uas_restriction_code;
#if condition_expression_type==5: 
#for each value in vector [operator_id,drone_id]:
INSERT INTO list_allowed_operators_drones(operator_id,drone_id,uas_restriction_code,uas_zone_id)


12.Creation of jsons and insertion on uas_zone_version:
CALL json_restriction(uas_zone_id)
CALL json_time_period(uas_zone_id)
CALL json_uspace(uas_zone_id)
CALL json_volume_limitation(uas_zone_id)
CALL json_zone_reason(uas_zone_id)

13. Refresh the view in which the feature is include:
REFRESH MATERIALIZED VIEW layer_name;

the view query if the layer do not exist is the following one:
CREATE MATERIALIZED VIEW layer_name AS
SELECT 
layers_volume.name layer_name,
airspace_volume.vol_id volume_id,
airspace_volume.geom_buffer geom_buffer,
uas_zone_version.name name_feature,
airspace_volume.lower_limit lower_limit, 
airspace_volume.upper_limit upper_limit, 
code_vertical_reference.name vertical_reference,
features.prop2_value contact,
features.prop3_value url,
features.prop4_value oid,
uas_zone_version.json_restriction_type restriction,
uas_zone_version.json_zone_reason code_zone_reason,
uas_zone_version.json_uspace_class_type code_uspace_class_type,
uas_zone_version.json_volume_limitation volume_limitations,
code_zone_type.name code_zone_type,
uas_zone_version.json_time_period time_period,
uas_zone_version.message message,
uas_zone_version.data_capture_prohibition data_capture_prohibition,
metadata.author author_metadata,
authority.name authority_name
FROM 
airspace_volume,
features,
layers_volume,
uas_zone_version,
code_zone_type,
authority,
metadata,
code_vertical_reference
WHERE 
airspace_volume.vol_id= uas_zone_version.airspace_volume_vol_id AND
airspace_volume.layers_layer_id = layers_volume.layer_id AND
code_zone_type.type_code = uas_zone_version.code_zone_type_type_code AND
authority.authority_oid = uas_zone_version.authority_authority_oid AND
metadata.metada_id = uas_zone_version.metadata_metada_id AND
code_vertical_reference.vertical_reference_code = airspace_volume.upper_vertical_reference AND
layers_volume.layer_id='{}' AND
CASE
WHEN airspace_volume.geom_origin is NULL THEN
features.feature_id='3ad9db77-6e92-45fb-a9d0-d520759a9712'
ELSE 
features.feature_id=airspace_volume.geom_origin
END;


NOTE: if we want to download a completed layer and not only a features of a layer the process would be a loop of this previous process for each one of the features downloaded and
 creating the layer before the loop and creting the view after the loop

-------------------------------------------UPDATE FEATURE---------------------------------------

The idea is to update the rows of the tables affected. 
1. Get the airspace_volume id.
2. Update the row affected