
-- Created by Vertabelo (http://vertabelo.com)
-- Last modification date: 2019-11-19 15:21:25.803

-- 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 drawing_style 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 int  NOT NULL,
    parent_id uuid  NOT NULL,
    name varchar(25)  NOT NULL,
    geometry geometry(MultiPolygon, 4326)  NOT NULL,
    administrative_region_levels_region_level_id uuid  NOT NULL,
    layers_layer_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,
    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', 'landuse', '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),

CREATE TABLE drawing_style(
    drawing_style_id int NOT NULL,
    border_rgba varchar(50),
    background_rgba varchar(50),
    border_width int,
    point_type varchar(50),
    width_bitmap int,
    height_bitmap int,
    text_size int,
    text_color varchar(50),
    text_font varchar(50),
    zoom_level int,
    CONSTRAINT drawing_style_pk PRIMARY KEY (drawing_style_id)
);
INSERT INTO drawing_style (drawing_style_id, border_rgba, background_rgba, border_width, text_size, text_color, text_font)
VALUES

    (1, 'rgba(0, 0, 255, 0.1)', 'rgba(0, 179, 199, 0.3)', 2, 12,'rgba(0,0,0,0)', 'ariel' ),
    (2, 'rgba(0, 144, 56, 0.1)', 'rgba(149, 214, 56, 0.3)', 2, 12,'rgba(0,0,0,0)', 'ariel' ),
    (3, 'rgba(239, 170, 32, 0.1)', 'rgba(239, 214, 114, 0.3)', 2, 12,'rgba(0,0,0,0)', 'ariel'),
    (4, 'rgba(226, 18, 22, 0.1)', 'rgba(253, 123, 95, 0.3)', 2, 12,'rgba(0,0,0,0)', 'ariel'),
    (5, 'rgba(33, 41, 30, 0.1)', 'rgba(115, 116, 114, 0.3)', 2, 12,'rgba(0,0,0,0)', 'ariel');;

-- 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: layers_cartography_administrative_regions (table: administrative_regions)
ALTER TABLE administrative_regions ADD CONSTRAINT administrative_regions_layers_cartography
    FOREIGN KEY (layers_layer_id)
    REFERENCES layers_cartography (layer_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 osm_columns_codification_osm_subclass_codification
    FOREIGN KEY (feature_subclass_codification_subclass_id)
    REFERENCES feature_subclass_codification (subclass_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: osm_features_layers (table: features)
ALTER TABLE features ADD CONSTRAINT osm_features_layers
    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 osm_subclass_codification_osm_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 fk_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 fk_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)
;

-- End of file.

