
-- tables
-- Table: administrative_region_levels
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; 
DROP TABLE IF EXISTS administrative_region_levels CASCADE;
DROP TABLE IF EXISTS administrative_regions CASCADE;
DROP TABLE IF EXISTS data_origin CASCADE;
DROP TABLE IF EXISTS feature_class_codification CASCADE;
DROP TABLE IF EXISTS feature_columns_codification CASCADE;
DROP TABLE IF EXISTS feature_subclass_codification CASCADE;
DROP TABLE IF EXISTS features CASCADE;
DROP TABLE IF EXISTS layers_cartography CASCADE;
DROP TABLE IF EXISTS geometry_codification CASCADE;
DROP TABLE IF EXISTS geofence_basic_layers CASCADE;
DROP TABLE IF EXISTS osm_basic_layers CASCADE;

DROP TABLE IF EXISTS airspace_volume CASCADE;
DROP TABLE IF EXISTS authority CASCADE;
DROP TABLE IF EXISTS code_restriction_type CASCADE;
DROP TABLE IF EXISTS code_uspace_class_type CASCADE;
DROP TABLE IF EXISTS code_vertical_reference CASCADE;
DROP TABLE IF EXISTS code_zone_reason CASCADE;
DROP TABLE IF EXISTS code_zone_type CASCADE;
DROP TABLE IF EXISTS condition_expression_type CASCADE;
DROP TABLE IF EXISTS slot_time CASCADE;
DROP TABLE IF EXISTS flight_warnings CASCADE;
DROP TABLE IF EXISTS layers_volume CASCADE;
DROP TABLE IF EXISTS metadata CASCADE;
DROP TABLE IF EXISTS time_period CASCADE;
DROP TABLE IF EXISTS uas_zone_version CASCADE;
DROP TABLE IF EXISTS volume_limitation CASCADE;
DROP TABLE IF EXISTS volume_limitation_definitions CASCADE;
DROP TABLE IF EXISTS volume_limitation_types CASCADE;
DROP TABLE IF EXISTS warnings_and_limitations CASCADE;
DROP TABLE IF EXISTS uas_code_restriction_type CASCADE;
DROP TABLE IF EXISTS uas_condition_expression_type CASCADE;
DROP TABLE IF EXISTS uas_code_uspace_class_type CASCADE;
DROP TABLE IF EXISTS uas_code_zone_reason CASCADE;
DROP TABLE IF EXISTS uas_time_period CASCADE;
DROP TABLE IF EXISTS code_period_type CASCADE;
DROP TABLE IF EXISTS geofences CASCADE;
DROP TABLE IF EXISTS visual_cartography CASCADE;



CREATE TABLE administrative_region_levels (
    region_level_id uuid  DEFAULT uuid_generate_v4() NOT NULL,
    country_id int  NOT NULL,
    hierarchy_level smallint  NOT NULL,
    name varchar(250)  NOT NULL,
    CONSTRAINT administrative_region_levels_pk PRIMARY KEY (region_level_id)
);
INSERT INTO administrative_region_levels (country_id, hierarchy_level,name)
VALUES
   (34, 0,'País') ,
   (34, 1,'Comunidad Autónoma') ,
   (34, 2,'Provincia') ,
   (34, 3,'Municipio') ;

-- Table: administrative_regions
CREATE TABLE administrative_regions (
    region_id uuid  DEFAULT uuid_generate_v4() NOT NULL,
    eurocae_region varchar(50)  NOT NULL,
    parent_id uuid  NOT NULL,
    name varchar(250)  NOT NULL,
    geometry geometry(MultiPolygon, 4326)  NOT NULL,
    administrative_region_levels_region_level_id uuid  NOT NULL,
    CONSTRAINT administrative_regions_pk PRIMARY KEY (region_id)
);

-- Table: data_origin
CREATE TABLE data_origin (
    origin_id int  NOT NULL,
    name varchar(50)  NOT NULL,
    CONSTRAINT data_origin_pk PRIMARY KEY (origin_id)
);

INSERT INTO data_origin (origin_id, name)
VALUES
   (1,'AIXM') ,
   (2,'OSM') ;;

-- Table: feature_class_codification
CREATE TABLE feature_class_codification (
    class_id uuid  DEFAULT uuid_generate_v4() NOT NULL,
    name varchar(50)  NOT NULL,
    description varchar(500),
    data_origin_origin_id int  NOT NULL,
    CONSTRAINT feature_class_codification_pk PRIMARY KEY (class_id)
);

-- Table: feature_columns_codification
CREATE TABLE feature_columns_codification (
    column_id uuid  DEFAULT uuid_generate_v4() NOT NULL,
    name varchar(50)  NOT NULL,
    name_code varchar(50)  NOT NULL,
    description varchar(500),
    feature_subclass_codification_subclass_id uuid  NOT NULL,
    CONSTRAINT feature_columns_codification_pk PRIMARY KEY (column_id)
);

-- Table: feature_subclass_codification
CREATE TABLE feature_subclass_codification (
    subclass_id uuid  DEFAULT uuid_generate_v4() NOT NULL,
    is_point boolean  NOT NULL,
    is_linestring boolean  NOT NULL,
    is_polygon boolean  NOT NULL,
    name varchar(50)  NOT NULL,
    description varchar(500),
    feature_class_codification_class_id uuid  NOT NULL,
    CONSTRAINT feature_subclass_codification_pk PRIMARY KEY (subclass_id)
);

-- Table: features
CREATE TABLE features (
    feature_id uuid  DEFAULT uuid_generate_v4() NOT NULL,
    geom_point geometry(POINT,4326)  ,
    geom_linestring geometry(LINESTRING,4326)  ,
    geom_polygon geometry(Polygon,4326)  ,
    geom_center geometry(Point,4326),
    prop1_value varchar(250),
    prop2_value varchar(250),
    prop3_value varchar(250),
    prop4_value varchar(250),
    prop1_code uuid,
    prop2_code uuid,
    prop3_code uuid,
    prop4_code uuid,
    layers_layer_id uuid NOT NULL,
    sequential_id int,
    CONSTRAINT features_pk PRIMARY KEY (feature_id)
);
-- Table: layers
CREATE TABLE layers_cartography (
    layer_id uuid  DEFAULT uuid_generate_v4() NOT NULL,
    name varchar(50)  ,
    name_code varchar(50)  ,
    description varchar(2000) ,
    geometry_codification_geometry_type int,
    CONSTRAINT layers_pk PRIMARY KEY (layer_id)
);

CREATE TABLE geometry_codification (
    geometry_id int  NOT NULL,
    name varchar(50)  NOT NULL,
    CONSTRAINT geometry_codification_pk PRIMARY KEY (geometry_id)
);

INSERT INTO geometry_codification (geometry_id, name)
VALUES
   (1,'Point') , (2,'Linestring') ,(3,'Polygon') ,(4,'Multipoint') ,(5,'Multilinestring') ,
   (6,'Multipolygon') ,(7,'Geometrycollection') ,(8,'Pointm') ,(9,'Linestringm') ,
   (10,'Polygonm') ,(11,'Multipointm') ,(12,'Multilinestring') ,(13,'Multipolygonm') ,
   (14,'Geometrycollectionm') ,(15,'Geometry') ;;

CREATE TABLE geofence_basic_layers(
    feature_id int NOT NULL,
    name varchar(50) NOT NULL,
    geometry_codification_geometry_type int NOT NULL,
    geofence boolean  NOT NULL,
    buffer int NOT NULL,
    prop1 varchar(100),
    prop2 varchar(100),
    prop3 varchar(100),
    prop4 varchar(100),
    description_layer varchar(100),
    code_uspace_class_type_u_space_code int, 
    code_zone_reason_code_zone_reason int,
    CONSTRAINT geofence_basic_layers_pk PRIMARY KEY (feature_id)
    
);

INSERT INTO geofence_basic_layers (feature_id, name, geometry_codification_geometry_type, geofence, buffer,prop1, prop2, prop3,prop4, description_layer, code_uspace_class_type_u_space_code, code_zone_reason_code_zone_reason)
VALUES
    (1, 'protected_zone', 3, '1', 0,'name','contact','website','source',  'Basic layer for pretected zones',2,5),
    (2, 'natural_zone', 3, '1', 0,'name','contact','website','source', 'Basic layer for natural zones',2,5), 
    (3, 'military', 3, '1', 30,'name','contact','website','access','Basic layer for military zones',1,2), 
    (4, 'hospitals', 3, '1', 20, 'name','contact','website', 'phone','Basic layer for hospitals',1,2),
    (5, 'schools', 3, '1', 20,'name', 'contact','website','level', 'Basic layer for schools',1,2), 
    (6, 'townhall', 3, '1', 20, 'name', 'contact','website','townhall:type', 'Basic layer for townhall',1,2),
    (7, 'aerodromes', 3, '1', 8000, 'name','contact','website', 'icao', 'Basic layer for aerodromes',1,1), 
    (8, 'rail_station', 3,'1', 50, 'name','contact','website', 'operator', 'Basic layer for rail station',2,2), 
    (9, 'roads', 2, '0', 0, 'name','contact','website', 'ref', 'Basic layer for roads',NULL,NULL), 
    (10, 'secundary_roads', 2, '0', 0,'name','contact','website', 'ref', 'Basic layer for secundary roads',NULL,NULL), 
    (11, 'funicular', 2,'0', 0, 'name','contact','website', 'operator', 'Basic layer for funicular',NULL,NULL), 
    (12, 'gondola', 2, '0' ,0,'name','contact','website', 'operator', 'Basic layer for gondola',NULL,NULL),
    (13, 'power_plant', 3, '1', 150, 'name','contact','website', 'operator', 'Basic layer for power plant',1,2), 
    (14, 'rail', 2,'0' ,0, 'name','contact','website','service', 'Basic layer for rail',NULL,NULL), 
    (15, 'power_line', 2, '0', 0, 'name','contact','website', 'operator',  'Basic layer for power lines',NULL,NULL),
    (16, 'power_tower', 1, '0', 0, 'name','contact','website', 'operator',  'Basic layer for power towers',NULL,NULL), 
    (17, 'generator', 3, '1', 20, 'name','contact','website', 'operator', 'Basic layer for generator',1,2);;

CREATE TABLE osm_basic_layers(
    osm_id int NOT NULL,
    class_osm varchar(50) NOT NULL,
    subclass_osm varchar(50) NOT NULL,
    document_name varchar(100) NOT NULL,
    geofence_basic_layers_feature_id int NOT NULL,
    CONSTRAINT osm_basic_layers_pk PRIMARY KEY (osm_id)

);
INSERT INTO osm_basic_layers (osm_id, class_osm, subclass_osm, document_name, geofence_basic_layers_feature_id)
VALUES
   (1,'boundary', 'national_park','boundary_national_park',1) , (2,'boundary','protected_area','boundary_protected_area',1) ,(3,'leisure','nature_reserve','leisure_reserve',2) ,
   (4,'military','all','military',3), 
   (7,'amenity','hospital','amenity_hospital',4) ,
   (17, 'amenity', 'school','amenity_school',5),
   (18, 'amenity', 'university', 'amenity_university',5), (19, 'amenity', 'townhall','amenity_townhall', 6), (20, 'aeroway','aerodrome','aeroway_aerodrome',7),
   (21, 'aeroway', 'heliport', 'aeroway_heliport',7), (22, 'railway', 'station','railway_station',8),
   (25, 'highway', 'motorway','highway_motorway', 9), (26, 'highway', 'secondary','highway_secondary',10), (27, 'railway', 'funicular','railway_funicular',11), 
   (29, 'aerialway', 'gondola','aerialway_gondola', 12), (30, 'power', 'plant','power_plant', 13),
   (31, 'railway', 'rail', 'railway_rail',14), (32, 'power', 'line','power_line',15),
   (33, 'power', 'tower','power_tower', 16), 
   (36, 'power', 'generator','power_generator', 17) ;;

--EXTRA:(5,'building','bunker','building_bunker',3), (6,'landuse','military', 'landuse_military',3),(8,'building','hospital','building_hospital',4) , (9,'building','school','building_school',5) ,
-- (10,'building', 'university','building_university',5) ,(11,'building', 'conservatory', 'building_conservatory',5) ,(12,'amenity', 'college', 'amenity_college',5),(13,'amenity', 'driving_school','driving_shool',5),(14,'amenity', 'kidergarten','amenity_kidergarten',5) ,(15,'amenity', 'language_school','amenity_language_school',5),
-- (16, 'amenity', 'music_school', 'amenity_music_school',5),  (23, 'railway','halt','railway_halt',8),(24, 'building','train_station','building_train_station',8),
-- (28, 'aerialway', 'cable_car','aerialway_cable_car',12), (34, 'power', 'transformer','power_transformer', 16), (35, 'power', 'terminal', 'power_terminal', 16),

-- SCHEMA RESTRICTIONS:
-- Table: airspace_volume
CREATE TABLE airspace_volume (
    vol_id uuid DEFAULT uuid_generate_v4()  NOT NULL,
    lower_limit int,
    lower_vertical_reference int  NOT NULL,
    upper_limit int,
    upper_vertical_reference int  NOT NULL,
    geom_origin uuid ,
    geom_buffer geometry(MULTIPOLYGON,4326)  NOT NULL,
    layers_layer_id uuid  NOT NULL,
    active boolean NOT NULL,
    geom_center geometry(GeometryCollection,4326),
    geom_radius int,
    sequential_id int,
    CONSTRAINT airspace_volume_pk PRIMARY KEY (vol_id)
);

CREATE INDEX airspace_volume_geom_origin_index ON airspace_volume(geom_origin);

-- Table: authority
CREATE TABLE authority (
    authority_oid uuid DEFAULT uuid_generate_v4() NOT NULL,
    name varchar(250)  NOT NULL,
    service varchar(250),
    contact_name varchar(250),
    site_url varchar(250),
    email varchar(250),
    phone varchar(250),
    CONSTRAINT authority_pk PRIMARY KEY (authority_oid)
);

-- Table: code_restriction_type
CREATE TABLE code_restriction_type (
    restriction_code int  NOT NULL,
    name varchar(50)  NOT NULL,
    CONSTRAINT code_restriction_type_pk PRIMARY KEY (restriction_code)
);

INSERT INTO code_restriction_type (restriction_code, name)
VALUES
   (1,'PROHIBITED') ,
   (2,'REQ_AUTHORISATION') ,
   (3,'CONDITIONAL') ,
   (4,'NO_RESTRICTION') ;;

CREATE TABLE uas_code_restriction_type(
    uas_restriction_code uuid DEFAULT uuid_generate_v4() not null,
    code_restriction_type_restriction_code int NOT NULL,
    uas_zone_version_uas_zone_id uuid NOT NULL,
    CONSTRAINT uas_code_restriction_type_pk PRIMARY KEY (uas_restriction_code)
);

-- Table: code_uspace_class_type
CREATE TABLE code_uspace_class_type (
    u_space_code int  NOT NULL,
    name varchar(50)  NOT NULL,
    description varchar(550)  NOT NULL,
    CONSTRAINT code_uspace_class_type_pk PRIMARY KEY (u_space_code)
);

INSERT INTO code_uspace_class_type (u_space_code, name, description)
VALUES
   (1,'NDZ', 'No Drone Zone') ,
   (2,'LDZ', 'Limited Drone Zone') ,
   (3,'EDZu', 'Exclusive Drone Zone (unplanned)') ,
   (4,'EDZp', 'Exclusive Drone Zone (planned)') , 
   (5,'EDZm', 'Exclusive Drone Zone for passenger carrying operations') ,
   (6,'X', 'Class X (according to the CORUS conops)') ,
   (7,'Y', 'Class Y (according to the CORUS conops)') ,
   (8,'Z', 'Class Z (according to the CORUS conops)');;

CREATE TABLE uas_code_uspace_class_type(
    uas_u_space_code uuid DEFAULT uuid_generate_v4() not null,
    code_uspace_class_type_u_space_code int NOT NULL,
    uas_zone_version_uas_zone_id uuid NOT NULL,
    CONSTRAINT uas_code_uspace_class_type_pk PRIMARY KEY (uas_u_space_code)
);

-- Table: code_vertical_reference
CREATE TABLE code_vertical_reference (
    vertical_reference_code int  NOT NULL,
    name varchar(50)  NOT NULL,
    description varchar(550),
    CONSTRAINT code_vertical_reference_pk PRIMARY KEY (vertical_reference_code)
);

INSERT INTO code_vertical_reference (vertical_reference_code, name, description)
VALUES
   (1,'AGL', 'Height above ground/surface level') ,
   (2,'AMSL', 'Altitude above Mean Sea Level') ,
   (3,'WGS84', 'Altitude above the reference WGS-84 Ellipsoid') ;;


-- Table: code_zone_reason
CREATE TABLE code_zone_reason (
    code_zone_reason int  NOT NULL,
    name varchar(50)  NOT NULL,
    description varchar(550)  NOT NULL,
    CONSTRAINT code_zone_reason_pk PRIMARY KEY (code_zone_reason)
);

INSERT INTO code_zone_reason (code_zone_reason, name, description)
VALUES
   (1,'Air Traffic', 'Due to the presence of air traffic') ,
   (2,'Sensitive', 'Due to the presence of a sensitive site, in the vicinity of which the presence of drones could be considered a potential threat') ,
   (3,'Privacy', 'Due to the presence of a site for which the presence of drones could rise privacy concerns'),
   (4,'Population', 'Due to the presence of a significantly populated area') ,
   (5,'Nature', 'Due to the presence of a wildelife/nature sanctuary or another area with sensitive nature/fauna') ,
   (6,'Noise', 'Due to noise abatement regulations') ,
   (7,'Foreign Territory', 'Indicates a "fake" Zone that is created automatically and which corresponds to the territory of a neighboring country. This zone is "restricted" by default and it is just an indication that the database is missing the information about the neighboring country') ,
   (8,'Other', 'Due to another reason, which may be specified in the format "OTHER:..specific reason..." (maximum 30 characters)');;

CREATE TABLE uas_code_zone_reason(
    uas_code_zone_reason uuid DEFAULT uuid_generate_v4() not null,
    code_zone_reason_code_zone_reason int NOT NULL,
    uas_zone_version_uas_zone_id uuid NOT NULL,
    CONSTRAINT uas_code_zone_reason_pk PRIMARY KEY (uas_code_zone_reason)
);


-- Table: code_zone_type
CREATE TABLE code_zone_type (
    type_code int  NOT NULL,
    name varchar(50)  NOT NULL,
    description varchar(550)  NOT NULL,
    CONSTRAINT code_zone_type_pk PRIMARY KEY (type_code)
);

INSERT INTO code_zone_type (type_code, name, description)
VALUES
   (1,'COMMON', 'The Zone is provided with its default definition') ,
   (2,'CUSTOMIZED', 'The Zone is provided with a customised definition, for a particular operator'),
   (3,'MODIFIED','The zone is provided by a modification from a customised definition') ;;

-- Table: condition_expression_type
CREATE TABLE condition_expression_type (
    restriction_code int  NOT NULL,
    name varchar(50)  NOT NULL,
    description varchar(550)  NOT NULL,
    CONSTRAINT condition_expression_type_pk PRIMARY KEY (restriction_code)
);

INSERT INTO condition_expression_type(restriction_code,name,description)
VALUES
    (1,'Geoawareness equipment Required', 'Geoawareness equipment Required'),
    (2, 'Remote ID equipment requiered', 'Remote ID equipment requiered'),
    (3, 'Manual authorization required from XXX', 'Manual authorization required from XXX (un valor en metros concreto)'),
    (4, 'Allowed Operators/drones', 'Allowed Operators/drones');;

CREATE TABLE uas_condition_expression_type(
    uas_restriction_code uuid DEFAULT uuid_generate_v4() NOT NULL,
    condition_expression_type_restriction_code int NOT NULL,
    upperlimitnorestriction int,
    uas_code_restriction_type_uas_restriction_code uuid NOT NULL,
    uas_zone_version_uas_zone_id uuid NOT NULL,
    CONSTRAINT uas_condition_expression_type_pk PRIMARY KEY(uas_restriction_code)
);




-- Table: slot_time
CREATE TABLE slot_time (
    slot_id uuid DEFAULT uuid_generate_v4(),
    start_time time with time zone,
    end_time time with time zone,
    time_period_time_period_id uuid  NOT NULL,
    CONSTRAINT slot_pk PRIMARY KEY (slot_id)
);

-- Table: flight_warnings
CREATE TABLE flight_warnings (
    warning_id int  NOT NULL,
    name_code varchar(25)  NOT NULL,
    name varchar(2000)  NOT NULL,
    description varchar(2000)  NOT NULL,
    CONSTRAINT flight_warnings_pk PRIMARY KEY (warning_id)
);

INSERT INTO flight_warnings (warning_id,name_code, name, description)
VALUES
    (1,'eu_war_all_01', 'It is forbidden to fly above 120m', 'Valid for all the operations') ,
    (2,'eu_war_all_02', 'It is forbidden to operate the UA while operating a moving vehicle', 'Valid for all the operations') ,
    (3,'eu_war_all_03', 'It is forbidden to operate more than one UA at a time', 'Valid for all the operations') ,
    (4,'eu_war_all_04', 'The dissemination of people and private places images requires authorisation of the involved people/owners', 'Valid for all the operations') ,
    (5,'eu_war_all_05', 'Remember to check the weather conditions prior the operation planning', 'Valid for all the operations') ,
    (6,'eu_war_all_06', 'It is forbidden to Fly during the night', 'Valid for all the operations') ,
    (21,'eu_war_open_01', 'It is forbidden to fly above assemblies of people', 'Valid for Open operations') ,
    (22,'eu_war_open_02', 'It is forbidden to fly UA with MTOM>25kg', 'Valid for Open operations') ,
    (23,'eu_war_open_03', 'It is forbidden any autonomous operation', 'Valid for Open operations') ,
    (24,'eu_war_open_04', 'It is forbidden to carry dangerous goods and drop any material', 'Valid for Open operations') ,
    (25,'eu_war_open_05', 'It is forbidden any operation in BVLOS without an UA observer or flying in follow-me mode', 'Valid for Open operations') ,
    (26,'eu_war_open_06', 'The minimum age for remote pilot without supervision is 16 years old unless operating an UAS class C0 classified as toy in A1 subcategory', 'Valid for Open operations') ,
    (41,'eu_war_esp_01', 'The minimum age for remote pilot without supervision is 16 years old', 'Valid for Specific operations') ,
    (42,'eu_war_esp_02', 'The competent authority may require the certification of the UAS t adequately mitigate the risk of the operation', 'Valid for Specific operations') ,
    (43,'eu_war_esp_03', 'An operational authorisation issued by competent authority with a risk assessment attached is needed, unless it applies any of the following: PDRA, LUC, declaration STS or club/association authorisation', 'Valid for Specific operations') ,
    (61,'eu_war_cert_01', 'Certified operations require manual authorisation', 'Valid for Certified operations') ,
    (62,'eu_war_cert_02', 'The UA operated in this area must be registered and airworthiness certified pursuant Commission Regulations (EU) No 748/2012, (EU) No 2015/640 and (EU) No 1321/2014', 'Valid for Certified operations') ;;


-- Table: layers
CREATE TABLE layers_volume (
    layer_id uuid  DEFAULT uuid_generate_v4() NOT NULL,
    name varchar(50)  NOT NULL,
    name_code varchar(50) ,
    description varchar(2000) ,
    geometry_codification_geometry_type int,
    CONSTRAINT layers_v_pk PRIMARY KEY (layer_id)
);

-- Table: metadata
CREATE TABLE metadata (
    metada_id uuid DEFAULT uuid_generate_v4() NOT NULL,
    creation_date_time timestamp with time zone,
    update_date_time timestamp with time zone,
    author varchar(250)  NOT NULL,
    CONSTRAINT metadata_pk PRIMARY KEY (metada_id)
);

-- Table: time_period
CREATE TABLE time_period (
    time_period_id uuid DEFAULT uuid_generate_v4()  NOT NULL,
    start_date_time timestamp with time zone,
    end_date_time timestamp with time zone,
    monday boolean,
    tuesday boolean,
    wednesday boolean,
    thursday boolean,
    friday boolean,
    saturday boolean,
    sunday boolean,
    CONSTRAINT time_period_pk PRIMARY KEY (time_period_id)
);

--Table: uas_time_period
CREATE TABLE uas_time_period (
    uas_time_period_id uuid DEFAULT uuid_generate_v4()  NOT NULL,
    uas_zone_version_uas_zone_id uuid NOT NULL,
    time_period_time_period_id uuid NOT NULL,
    CONSTRAINT uas_time_period_pk PRIMARY KEY (uas_time_period_id)
);

-- Table: uas_zone_version
CREATE TABLE uas_zone_version (
    uas_zone_id uuid DEFAULT uuid_generate_v4() NOT NULL,
    identifier varchar(10),
    country_id varchar(3)  NOT NULL,
    name varchar(1000)  NOT NULL,
    code_zone_type_type_code int,
    region int,
    data_capture_prohibition boolean,
    message varchar(1000),
    metadata_metada_id uuid,
    authority_authorityfrom_oid uuid,
	authority_notificationto_oid uuid,
    code_period_type_period_type_id int,
    airspace_volume_vol_id uuid  NOT NULL  UNIQUE,
    intervalBefore varchar(11), 
    CONSTRAINT uas_zone_version_pk PRIMARY KEY (uas_zone_id)
);

CREATE INDEX uas_zone_version_airspace_volume_vol_id_index ON uas_zone_version(airspace_volume_vol_id);

--Table: code_period_type
CREATE TABLE code_period_type(
    period_type_id int NOT NULL,
    name varchar(100)  NOT NULL,
    CONSTRAINT code_period_type_pk PRIMARY KEY (period_type_id) 
);

INSERT INTO code_period_type(period_type_id,name)
VALUES
    (1,'permanent'),
    (2,'every day'),
    (3,'personalize');;

-- Table: volume_limitation
CREATE TABLE volume_limitation (
    limitation_id uuid DEFAULT uuid_generate_v4()  NOT NULL,
    limitation_value int ,
    volume_limitation_definitions_limitation_definition_id int  NOT NULL,
    uas_zone_version_uas_zone_id uuid  NOT NULL,
    CONSTRAINT volume_limitation_pk PRIMARY KEY (limitation_id)
);

-- Table: volume_limitation_definitions
CREATE TABLE volume_limitation_definitions (
    limitation_definition_id int  NOT NULL,
    name_code varchar(100)  NOT NULL,
    name varchar(100)  NOT NULL,
    description varchar(2000)  NOT NULL,
    volume_limitation_types_limitation_type_id int  NOT NULL,
    CONSTRAINT volume_limitation_definitions_pk PRIMARY KEY (limitation_definition_id)
);

INSERT INTO volume_limitation_definitions (limitation_definition_id,name_code, name, description,volume_limitation_types_limitation_type_id )
VALUES
   (1,'cert_type_0', 'Certification Type 0', 'European Drone Certification Type 0',1) ,
   (2,'cert_type_1', 'Certification Type 1', 'European Drone Certification Type 1',1) ,
   (3,'cert_type_2', 'Certification Type 2', 'European Drone Certification Type 2',1) ,
   (4,'cert_type_3', 'Certification Type 3', 'European Drone Certification Type 3',1) ,
   (5,'cert_type_4', 'Certification Type 4', 'European Drone Certification Type 4',1) ,
   (6,'opt_type_open', 'Operation_type Open', 'European operation type Open',1) ,
   (7,'opt_type_specific', 'Operation_type Specific', 'European operation type Specific',1) ,
   (8,'opt_type_certified', 'Operation_type Certified', 'European operation type Certified',1) ,
   (50,'laanc', 'LAANC Type', 'Low Altitude Authorization and Notification Capability',2) ;;

-- Table: volume_limitation_types
CREATE TABLE volume_limitation_types (
    limitation_type_id int  NOT NULL,
    name_code varchar(50)  NOT NULL,
    CONSTRAINT volume_limitation_types_pk PRIMARY KEY (limitation_type_id)
);

INSERT INTO volume_limitation_types (limitation_type_id, name_code)
VALUES
   (1,'boolean_limitation') ,
   (2,'laanc_limitation');;

-- Table: warnings_and_limitations
CREATE TABLE warnings_and_limitations (
    wl_id int  NOT NULL,
    flight_warnings_id_warning int  NOT NULL,
    volume_limitation_definitions_limitation_definition_id int  NOT NULL,
    CONSTRAINT warnings_and_limitations_pk PRIMARY KEY (wl_id)
);

INSERT INTO warnings_and_limitations (wl_id,volume_limitation_definitions_limitation_definition_id, flight_warnings_id_warning)
VALUES
   (1,6,1) ,(2,6,2) ,(3,6,3) ,(4,6,4) ,(5,6,5) ,(6,6,6) ,  -- warning for all (open) 
   (7,7,1) ,(8,7,2) ,(9,7,3) ,(10,7,4) ,(11,7,5) ,(12,7,6) ,  -- warning for all (specific)
   (13,8,1) ,(14,8,2) ,(15,8,3) ,(16,8,4) ,(17,8,5) ,(18,8,6) ,  -- warning for all (certified)
   (19,6,21) ,(20,6,22) ,(21,6,23) ,(22,6,24) ,(23,6,25) ,(24,6,26) ,  -- warning just for open 
   (25,7,41) ,(26,7,42) ,(27,7,43) ,  -- warning just for specific 
   (28,8,61) ,(29,8,62);  -- warning just for certified;


--FUSION TABLES:
CREATE TABLE geofences(
sequential_id SERIAL UNIQUE,
identifier varchar(10),
layer_name varchar(50) NOT NULL,
geom_buffer geometry(MULTIPOLYGON,4326)  NOT NULL,
name_feature varchar(1000),
lower_limit int,
upper_limit int,
lower_vertical_reference varchar(25)  NOT NULL,
upper_vertical_reference varchar(25)  NOT NULL,
geom_center geometry(GeometryCollection,4326),
geom_radius int,
active boolean NOT NULL default 'TRUE',
contact varchar(250),
url varchar(250),
oid varchar(250),
country_id varchar(3),
region int  ,
restriction varchar,
code_zone_reason varchar,
code_uspace_class_type varchar,
volume_limitations varchar,
code_zone_type varchar,
time_period varchar,
message varchar(1000),
data_capture_prohibition boolean,
author_metadata varchar(250),
creation_datetime  timestamp with time zone,
update_datetime  timestamp with time zone,
authorityfrom varchar,
notificationto varchar,
intervalBefore varchar(11), 
remote_id boolean NOT NULL DEFAULT 'FALSE',
geoawareness_required boolean NOT NULL DEFAULT 'FALSE',
CONSTRAINT geofences_sequential_id_pk PRIMARY KEY (sequential_id)
);

CREATE INDEX geofences_sequential_id_index ON geofences(sequential_id);

CREATE TABLE visual_cartography(
sequential_id SERIAL UNIQUE,
geom_point geometry(POINT,4326)  ,
geom_linestring geometry(LINESTRING,4326)  ,
geom_polygon geometry(Polygon,4326)  ,
geom_center geometry(Point,4326),
prop1_value varchar(250),
prop2_value varchar(250),
prop3_value varchar(250),
prop4_value varchar(250),
prop1_code varchar(50),
prop2_code varchar(50),
prop3_code varchar(50),
prop4_code varchar(50),
layer_name varchar(50),
CONSTRAINT visual_cartography_sequential_id_pk PRIMARY KEY (sequential_id)
);

CREATE INDEX visual_cartography_sequential_id_index ON visual_cartography(sequential_id);
--CREATE INDEX visual_cartography_feature_id_index ON visual_cartography(feature_id);

-- foreign keys
-- Reference: administrative_regions_administrative_region_levels (table: administrative_regions)
ALTER TABLE administrative_regions ADD CONSTRAINT administrative_regions_administrative_region_levels
    FOREIGN KEY (administrative_region_levels_region_level_id)
    REFERENCES administrative_region_levels (region_level_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: codification_for_property1 (table: features)
ALTER TABLE features ADD CONSTRAINT codification_for_property1
    FOREIGN KEY (prop1_code)
    REFERENCES feature_columns_codification (column_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

ALTER TABLE layers_cartography ADD CONSTRAINT layers_cartography_geometry_codification
    FOREIGN KEY (geometry_codification_geometry_type)
    REFERENCES geometry_codification (geometry_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: codification_for_property2 (table: features)
ALTER TABLE features ADD CONSTRAINT codification_for_property2
    FOREIGN KEY (prop2_code)
    REFERENCES feature_columns_codification (column_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: codification_for_property3 (table: features)
ALTER TABLE features ADD CONSTRAINT codification_for_property3
    FOREIGN KEY (prop3_code)
    REFERENCES feature_columns_codification (column_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: codification_for_property4 (table: features)
ALTER TABLE features ADD CONSTRAINT codification_for_property4
    FOREIGN KEY (prop4_code)
    REFERENCES feature_columns_codification (column_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: feature_class_codification_data_origin (table: feature_class_codification)
ALTER TABLE feature_class_codification ADD CONSTRAINT feature_class_codification_data_origin
    FOREIGN KEY (data_origin_origin_id)
    REFERENCES data_origin (origin_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: osm_columns_codification_osm_subclass_codification (table: feature_columns_codification)
ALTER TABLE feature_columns_codification ADD CONSTRAINT feature_columns_codification_feature_subclass_codification
    FOREIGN KEY (feature_subclass_codification_subclass_id)
    REFERENCES feature_subclass_codification (subclass_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: feature_layers_cartography (table: features)
ALTER TABLE features ADD CONSTRAINT feature_layers_cartography 
    FOREIGN KEY (layers_layer_id)
    REFERENCES layers_cartography (layer_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: osm_subclass_codification_osm_class_codification (table: feature_subclass_codification)
ALTER TABLE feature_subclass_codification ADD CONSTRAINT feature_subclass_codification_feature_class_codification
    FOREIGN KEY (feature_class_codification_class_id)
    REFERENCES feature_class_codification (class_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

ALTER TABLE geofence_basic_layers ADD CONSTRAINT geofence_basic_layers_geometry_codification
    FOREIGN KEY (geometry_codification_geometry_type)
    REFERENCES geometry_codification (geometry_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

ALTER TABLE osm_basic_layers ADD CONSTRAINT osm_basic_layers_geofence_basic_layers
    FOREIGN KEY (geofence_basic_layers_feature_id)
    REFERENCES geofence_basic_layers (feature_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;


ALTER TABLE geofence_basic_layers ADD CONSTRAINT geofence_basic_layers_code_zone_reason_code_zone_reason 
    FOREIGN KEY (code_zone_reason_code_zone_reason) 
    REFERENCES code_zone_reason(code_zone_reason)
;

ALTER TABLE geofence_basic_layers ADD CONSTRAINT geofence_basic_layers_code_uspace_class_type_u_space_code
    FOREIGN KEY (code_uspace_class_type_u_space_code) 
    REFERENCES code_uspace_class_type(u_space_code)
;

ALTER TABLE airspace_volume ADD CONSTRAINT airspace_volume_features
    FOREIGN KEY (geom_origin) 
    REFERENCES features(feature_id)
;





-- Reference: airspace_volume_code_vertical_reference (table: airspace_volume)
ALTER TABLE airspace_volume ADD CONSTRAINT airspace_volume_lower_code_vertical_reference
    FOREIGN KEY (lower_vertical_reference)
    REFERENCES code_vertical_reference (vertical_reference_code)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: airspace_volume_code_vertical_reference (table: airspace_volume)
ALTER TABLE airspace_volume ADD CONSTRAINT airspace_volume_upper_code_vertical_reference
    FOREIGN KEY (upper_vertical_reference)
    REFERENCES code_vertical_reference (vertical_reference_code)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;


-- Reference: airspace_volume_layers (table: airspace_volume)
ALTER TABLE airspace_volume ADD CONSTRAINT airspace_volume_layers_volume
    FOREIGN KEY (layers_layer_id)
    REFERENCES layers_volume (layer_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;


-- Reference: slot_time_time_period (table: slot_time)
ALTER TABLE slot_time ADD CONSTRAINT slot_time_time_period
    FOREIGN KEY (time_period_time_period_id)
    REFERENCES time_period (time_period_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

--Reference:  uas_time_period_time_period(table: uas_time_period)
ALTER TABLE uas_time_period ADD CONSTRAINT uas_time_period_time_period
    FOREIGN KEY (time_period_time_period_id)
    REFERENCES time_period (time_period_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

--Reference:  uas_time_period_uas_zone_version(table: uas_time_period)
ALTER TABLE uas_time_period ADD CONSTRAINT uas_time_period_uas_zone_version
    FOREIGN KEY (uas_zone_version_uas_zone_id)
    REFERENCES uas_zone_version (uas_zone_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: layers_geometry_codification (table: layers)
ALTER TABLE layers_volume ADD CONSTRAINT layers_volume_geometry_codification
    FOREIGN KEY (geometry_codification_geometry_type)
    REFERENCES geometry_codification (geometry_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: uas_zone_version_airspace_volume (table: uas_zone_version)
ALTER TABLE uas_zone_version ADD CONSTRAINT uas_zone_version_airspace_volume
    FOREIGN KEY (airspace_volume_vol_id)
    REFERENCES airspace_volume (vol_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: uas_zone_version_authority (table: uas_zone_version)
ALTER TABLE uas_zone_version ADD CONSTRAINT uas_zone_version_authorityfrom
    FOREIGN KEY (authority_authorityfrom_oid)
    REFERENCES authority (authority_oid)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: uas_zone_version_authority (table: uas_zone_version)
ALTER TABLE uas_zone_version ADD CONSTRAINT uas_zone_version_notificationto
    FOREIGN KEY (authority_notificationto_oid)
    REFERENCES authority (authority_oid)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;


-- Reference: uas_zone_version_code_zone_type (table: uas_zone_version)
ALTER TABLE uas_zone_version ADD CONSTRAINT uas_zone_version_code_zone_type
    FOREIGN KEY (code_zone_type_type_code)
    REFERENCES code_zone_type (type_code)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;


-- Reference: uas_zone_version_metadata (table: uas_zone_version)
ALTER TABLE uas_zone_version ADD CONSTRAINT uas_zone_version_metadata
    FOREIGN KEY (metadata_metada_id)
    REFERENCES metadata (metada_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

--Reference: uas_zone_verrsion_code_period_type (table:uas_zone_version)
ALTER TABLE uas_zone_version ADD CONSTRAINT uas_zone_version_code_period_type
    FOREIGN KEY (code_period_type_period_type_id)
    REFERENCES code_period_type (period_type_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

--Reference:  uas_code_restriction_type_code_restriction_type(table: uas_code_restriction_type)
ALTER TABLE uas_code_restriction_type ADD CONSTRAINT uas_code_restriction_type_code_restriction_type
    FOREIGN KEY (code_restriction_type_restriction_code)
    REFERENCES code_restriction_type (restriction_code)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

--Reference:  uas_code_restriction_type_uas_zone_version(table: uas_code_restriction_type)
ALTER TABLE uas_code_restriction_type ADD CONSTRAINT uas_code_restriction_type_uas_zone_version
    FOREIGN KEY (uas_zone_version_uas_zone_id)
    REFERENCES uas_zone_version (uas_zone_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

--Reference:  uas_condition_expression_type_condition_expression_type(table: uas_condition_expression_type)
ALTER TABLE uas_condition_expression_type ADD CONSTRAINT uas_condition_expression_type_condition_expression_type
    FOREIGN KEY (condition_expression_type_restriction_code)
    REFERENCES condition_expression_type (restriction_code)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

--Reference:  uas_condition_expression_type_uas_code_restriction_type(table: uas_condition_expression_type)
ALTER TABLE uas_condition_expression_type ADD CONSTRAINT uas_condition_expression_type_uas_code_restriction_type
    FOREIGN KEY (uas_code_restriction_type_uas_restriction_code)
    REFERENCES uas_code_restriction_type (uas_restriction_code)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

ALTER TABLE uas_condition_expression_type ADD CONSTRAINT uas_condition_expression_type_uas_zone_version
    FOREIGN KEY (uas_zone_version_uas_zone_id)
    REFERENCES uas_zone_version (uas_zone_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

--Reference:  uas_code_uspace_class_type_code_uspace_class_type(table: uas_code_uspace_class_type)
ALTER TABLE uas_code_uspace_class_type ADD CONSTRAINT uas_code_uspace_class_type_code_uspace_class_type
    FOREIGN KEY (code_uspace_class_type_u_space_code)
    REFERENCES code_uspace_class_type (u_space_code)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

--Reference:  uas_code_uspace_class_type_uas_zone_version(table: uas_code_uspace_class_type)
ALTER TABLE uas_code_uspace_class_type ADD CONSTRAINT uas_code_uspace_class_type_uas_zone_version
    FOREIGN KEY (uas_zone_version_uas_zone_id)
    REFERENCES uas_zone_version (uas_zone_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

--Reference:  uas_code_zone_reason_code_zone_reason(table: uas_code_zone_reason)
ALTER TABLE uas_code_zone_reason ADD CONSTRAINT uas_code_zone_reason_code_zone_reason
    FOREIGN KEY (code_zone_reason_code_zone_reason)
    REFERENCES code_zone_reason (code_zone_reason)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

--Reference:  uas_code_zone_reason_uas_zone_version(table: uas_code_zone_reason)
ALTER TABLE uas_code_zone_reason ADD CONSTRAINT uas_code_zone_reason_uas_zone_version
    FOREIGN KEY (uas_zone_version_uas_zone_id)
    REFERENCES uas_zone_version (uas_zone_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;


-- Reference: volume_limitation_definitions_volume_limitation_types (table: volume_limitation_definitions)
ALTER TABLE volume_limitation_definitions ADD CONSTRAINT volume_limitation_definitions_volume_limitation_types
    FOREIGN KEY (volume_limitation_types_limitation_type_id)
    REFERENCES volume_limitation_types (limitation_type_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: volume_limitation_uas_zone_version (table: volume_limitation)
ALTER TABLE volume_limitation ADD CONSTRAINT volume_limitation_uas_zone_version
    FOREIGN KEY (uas_zone_version_uas_zone_id)
    REFERENCES uas_zone_version (uas_zone_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: volume_limitation_volume_limitation_definitions (table: volume_limitation)
ALTER TABLE volume_limitation ADD CONSTRAINT volume_limitation_volume_limitation_definitions
    FOREIGN KEY (volume_limitation_definitions_limitation_definition_id)
    REFERENCES volume_limitation_definitions (limitation_definition_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: warnings_and_limitations_flight_warnings (table: warnings_and_limitations)
ALTER TABLE warnings_and_limitations ADD CONSTRAINT warnings_and_limitations_flight_warnings
    FOREIGN KEY (flight_warnings_id_warning)
    REFERENCES flight_warnings (warning_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: warnings_and_limitations_volume_limitation_definitions (table: warnings_and_limitations)
ALTER TABLE warnings_and_limitations ADD CONSTRAINT warnings_and_limitations_volume_limitation_definitions
    FOREIGN KEY (volume_limitation_definitions_limitation_definition_id)
    REFERENCES volume_limitation_definitions (limitation_definition_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;




