-- ***********************************************************************
--
--      QGIS Package for the CityGML 3D City Database (for PostgreSQL)
--
--
--                        Copyright 2023
--
-- Delft University of Technology, The Netherlands
-- 3D Geoinformation Group
-- https://3d.bk.tudelft.nl/
-- 
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
-- 
--     http://www.apache.org/licenses/LICENSE-2.0
--     
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
-- Author: Giorgio Agugiaro
-- Delft University of Technology, The Netherlands
-- 3D Geoinformation Group
-- https://3d.bk.tudelft.nl/gagugiaro/
--
-- ***********************************************************************
--
-- This script creates the qgis_pkg_usrgroup_* role associated to the
-- current database, and the template tables.
--
-- ***********************************************************************

-- Add user group and template table(s)
DO $MAINBODY$
DECLARE
srid 			integer := (SELECT srid FROM citydb.database_srs LIMIT 1);
sql_statement	varchar;
grp_name 		varchar;

BEGIN
grp_name := (SELECT qgis_pkg.create_qgis_pkg_usrgroup());
RAISE NOTICE 'Created group "%"',grp_name;


------------------------------------------------------------------
-- TABLE QGIS_PKG.EXTENTS_TEMPLATE
------------------------------------------------------------------
-- Written as dynamic SQL because we need to pass the SRID value
-- for the geometry in the envelope column.
sql_statement := concat('
DROP TABLE IF EXISTS qgis_pkg.extents_template  CASCADE;
CREATE TABLE         qgis_pkg.extents_template (
id				int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
cdb_schema		varchar NOT NULL,
bbox_type		varchar,
label			varchar,
creation_date	timestamptz(3),
envelope		geometry(Polygon,',srid,'),
CONSTRAINT		extents_bbox_type_check CHECK (bbox_type IN (''db_schema'', ''m_view'', ''qgis'')),
CONSTRAINT		extents_schema_bbox_key UNIQUE (cdb_schema, bbox_type)
);
COMMENT ON TABLE qgis_pkg.extents_template IS ''Extents (as bounding box) of data in the cdb_schema(s) and associated layers'';
');
EXECUTE sql_statement;

END $MAINBODY$;


------------------------------------------------------------------
-- TABLE QGIS_PKG.LAYER_METADATA_TEMPLATE
------------------------------------------------------------------
DROP TABLE IF EXISTS qgis_pkg.layer_metadata_template CASCADE;
CREATE TABLE         qgis_pkg.layer_metadata_template (
id				bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
cdb_schema		varchar,
ade_prefix		varchar, -- NULL for standard CityGML, otherwise the prefix used by the selected ADE.
layer_type		varchar, -- Value in (VectorLayer, DetailView, VectorLayerNoGeom, DetailViewNoGeom)
feature_type	varchar, -- CityGML package/module name
root_class		varchar, -- The CityGML top class in the respective CityGML module (Building, Road, ...)
class			varchar,
lod				varchar(4), -- value in ('lod0', 'lod1', 'lod2', 'lod3', 'lod4', 'lodx' for no lod)
layer_name		varchar UNIQUE, -- contains the layer name
gv_name			varchar,
av_name			varchar, -- contains the suffix of the detail view (column to be renamed at a later point)
n_features		integer,
creation_date	timestamptz(3),
refresh_date	timestamptz(3),
qml_form		varchar, -- name of the qml file containing QGIS Field and Forms configurations
qml_symb		varchar, -- name of the qml file containing QGIS 2D symbology configuration
qml_3d			varchar,  -- name of the qml file containing QGIS 3D symbology configuration
-- possibly other columns with other qml files
enum_cols		varchar[][], -- array containing the class and column names that are linked to enumerations in the GUI form
codelist_cols	varchar[][]  -- array containing class and column names that may be linked to codelists in the GUI form
);
COMMENT ON TABLE qgis_pkg.layer_metadata_template IS 'List of layers and their metadata';

CREATE INDEX lmeta_cdb_schema_idx ON qgis_pkg.layer_metadata_template (cdb_schema);
CREATE INDEX lmeta_ade_pref_idx   ON qgis_pkg.layer_metadata_template (ade_prefix);
CREATE INDEX lmeta_l_type_idx     ON qgis_pkg.layer_metadata_template (layer_type);
CREATE INDEX lmeta_f_type_idx     ON qgis_pkg.layer_metadata_template (feature_type);
CREATE INDEX lmeta_rclass_idx     ON qgis_pkg.layer_metadata_template (root_class);
CREATE INDEX lmeta_class_idx      ON qgis_pkg.layer_metadata_template (class);
CREATE INDEX lmeta_lod_idx        ON qgis_pkg.layer_metadata_template (lod);
CREATE INDEX lmeta_l_name_idx     ON qgis_pkg.layer_metadata_template (layer_name);
CREATE INDEX lmeta_gv_name_idx    ON qgis_pkg.layer_metadata_template (gv_name);
CREATE INDEX lmeta_nf_idx         ON qgis_pkg.layer_metadata_template (n_features);
CREATE INDEX lmeta_rd_idx         ON qgis_pkg.layer_metadata_template (refresh_date);


------------------------------------------------------------------
-- TABLE QGIS_PKG.FEATURE_TYPE_TO_FEATURE
------------------------------------------------------------------
DROP TABLE IF EXISTS qgis_pkg.feature_type_to_toplevel_feature CASCADE;
CREATE TABLE         qgis_pkg.feature_type_to_toplevel_feature(
id 					integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
feature_type		varchar,
ade_prefix 			varchar DEFAULT NULL,
toplevel_feature	varchar,
is_supported		boolean,
description 		text
);
COMMENT ON TABLE qgis_pkg.feature_type_to_toplevel_feature IS 'Mapping between Feature Types and Top-class Features';

CREATE INDEX ft2tcf_feature_type_idx      ON qgis_pkg.feature_type_to_toplevel_feature (feature_type);
CREATE INDEX ft2tcf_ade_prefix_idx        ON qgis_pkg.feature_type_to_toplevel_feature (ade_prefix);
CREATE INDEX ft2tcf_toplevel_feature_idx ON qgis_pkg.feature_type_to_toplevel_feature (toplevel_feature);
CREATE INDEX ft2tcf_is_supported_idx      ON qgis_pkg.feature_type_to_toplevel_feature (is_supported);


------------------------------------------------------------------
-- TABLE QGIS_PKG.ENUM_LOOKUP_CONFIG
------------------------------------------------------------------
DROP TABLE IF EXISTS qgis_pkg.enum_lookup_config CASCADE;
CREATE TABLE         qgis_pkg.enum_lookup_config(
id 					integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
ade_prefix 			varchar DEFAULT NULL,
source_class 		varchar,
source_table        varchar,
source_column 		varchar,
target_table 		varchar NOT NULL DEFAULT 'v_codelist', 
key_column			varchar NOT NULL DEFAULT 'value',
value_column		varchar NOT NULL DEFAULT 'description',
filter_expression 	varchar,
num_columns 		integer DEFAULT 1,
allow_multi 		boolean DEFAULT FALSE,
allow_null 			boolean DEFAULT TRUE,
order_by_value 		boolean DEFAULT TRUE,
use_completer 		boolean DEFAULT FALSE,
description 		text
);
COMMENT ON TABLE qgis_pkg.enum_lookup_config IS 'Settings to set up look-up tables of enumerations in QGIS forms';

CREATE INDEX enluc_ade_prefix_idx   ON qgis_pkg.enum_lookup_config (ade_prefix);
CREATE INDEX enluc_source_class_idx ON qgis_pkg.enum_lookup_config (source_class);


------------------------------------------------------------------
-- TABLE QGIS_PKG.CODELIST_LOOKUP_CONFIG_TEMPLATE
------------------------------------------------------------------
-- NOTE From PostgreSQL 12 we can use generated columns (stored) to autocompute some fields (e.g. filter expression and number of columns)
DROP TABLE IF EXISTS qgis_pkg.codelist_lookup_config_template CASCADE;
CREATE TABLE         qgis_pkg.codelist_lookup_config_template(
id 					integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name                varchar NOT NULL, -- a simple name to identify the group of codelists to load/use.
ade_prefix          varchar DEFAULT NULL,
source_class 		varchar NOT NULL, -- Building, etc...
source_table        varchar NOT NULL,
source_column 		varchar NOT NULL,
target_table 		varchar NOT NULL DEFAULT 'v_codelist', 
key_column			varchar NOT NULL DEFAULT 'value',
value_column		varchar NOT NULL DEFAULT 'description',
filter_expression	varchar NOT NULL,
num_columns 		integer NOT NULL DEFAULT 1,     -- number of columns entries are distributed in the form
allow_multi 		boolean NOT NULL DEFAULT FALSE, -- allows selection of multiple entries (for 0:* cardinality)
allow_null 			boolean NOT NULL DEFAULT TRUE,
order_by_value 		boolean NOT NULL DEFAULT TRUE,
use_completer 		boolean NOT NULL DEFAULT FALSE,
description 		text
);
COMMENT ON TABLE qgis_pkg.codelist_lookup_config_template IS 'Settings to set up look-up tables of codelists in QGIS forms';

CREATE INDEX clluc_name_idx        ON qgis_pkg.codelist_lookup_config_template (name);
CREATE INDEX clluc_ade_prefix_idx ON qgis_pkg.codelist_lookup_config_template (ade_prefix);


------------------------------------------------------------------
-- TABLE qgis_pkg.settings
------------------------------------------------------------------
/* Data types
1 string
2 integer
3 real
4 boolean
5 date
*/
DROP TABLE IF EXISTS qgis_pkg.settings CASCADE;
CREATE TABLE         qgis_pkg.settings (
id				int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
dialog_name		varchar NOT NULL,
name			varchar NOT NULL,
data_type		integer NOT NULL,
data_value		varchar,
description		varchar,
latest_update	timestamptz(0),
CONSTRAINT settings_dialog_name_name_key UNIQUE (dialog_name, name)
);
COMMENT ON TABLE qgis_pkg.settings IS 'List of settings';

CREATE INDEX set_dlg_name_idx   ON qgis_pkg.settings (dialog_name);
CREATE INDEX set_name_idx       ON qgis_pkg.settings (name);
CREATE INDEX set_data_type_idx  ON qgis_pkg.settings (data_type);

------------------------------------------------------------------
-- TABLE qgis_pkg.usr_schema
------------------------------------------------------------------
DROP TABLE IF EXISTS qgis_pkg.usr_schema CASCADE;
CREATE TABLE         qgis_pkg.usr_schema (
id				int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
usr_name		varchar,
usr_schema		varchar,
creation_date	timestamptz(0)
);
COMMENT ON TABLE qgis_pkg.usr_schema IS 'List of schemas for qgis users';

CREATE INDEX usr_schema_usr_name_idx   ON qgis_pkg.usr_schema (usr_name);
CREATE INDEX usr_schema_usr_schema_idx ON qgis_pkg.usr_schema (usr_schema);


------------------------------------------------------------------
-- TABLE qgis_pkg.enumeration_template
------------------------------------------------------------------
DROP TABLE IF EXISTS qgis_pkg.enumeration_template CASCADE;
CREATE TABLE         qgis_pkg.enumeration_template (
id 			int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
data_model	varchar,
name		varchar,
name_space	varchar,
description	text,
CONSTRAINT e_enum_template_unique UNIQUE (data_model, name, name_space)
);
COMMENT ON TABLE qgis_pkg.enumeration_template IS 'Contains enumeration metadata';

CREATE INDEX e_data_model_idx  ON qgis_pkg.enumeration_template (data_model);
CREATE INDEX e_name_idx        ON qgis_pkg.enumeration_template (name);


------------------------------------------------------------------
-- TABLE qgis_pkg.enumeration_value_template
------------------------------------------------------------------
DROP TABLE IF EXISTS qgis_pkg.enumeration_value_template CASCADE;
CREATE TABLE         qgis_pkg.enumeration_value_template (
id 			int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
enum_id		integer,
value		varchar,
description	text,
CONSTRAINT ev_value_template_unique UNIQUE (enum_id, value)
);
COMMENT ON TABLE qgis_pkg.enumeration_value_template IS 'Contains enumeration values';

CREATE INDEX ev_enum_id_idx  ON qgis_pkg.enumeration_value_template (enum_id);
CREATE INDEX ev_value_idx    ON qgis_pkg.enumeration_value_template (value);


------------------------------------------------------------------
-- VIEW qgis_pkg.v_enumeration_value_template
------------------------------------------------------------------
DROP VIEW IF EXISTS qgis_pkg.v_enumeration_value_template CASCADE;
CREATE VIEW         qgis_pkg.v_enumeration_value_template AS
SELECT
	ev.id,
	e.data_model,
	e.name,
	ev.value,
	ev.description,
	e.name_space
FROM
	qgis_pkg.enumeration_value_template AS ev
	INNER JOIN qgis_pkg.enumeration_template AS e ON (ev.enum_id = e.id);


------------------------------------------------------------------
-- TABLE qgis_pkg.codelist_template
------------------------------------------------------------------
DROP TABLE IF EXISTS qgis_pkg.codelist_template CASCADE;
CREATE TABLE         qgis_pkg.codelist_template (
id 			int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
data_model	varchar,
name		varchar,
name_space	varchar,
description	text,
CONSTRAINT cl_template_unique UNIQUE (data_model, name, name_space)
);
COMMENT ON TABLE qgis_pkg.codelist_template IS 'Contains codelist metadata';

CREATE INDEX cl_data_model_idx  ON qgis_pkg.codelist_template (data_model);
CREATE INDEX cl_name_idx        ON qgis_pkg.codelist_template (name);


------------------------------------------------------------------
-- TABLE qgis_pkg.codelist_value_template
------------------------------------------------------------------
DROP TABLE IF EXISTS qgis_pkg.codelist_value_template CASCADE;
CREATE TABLE         qgis_pkg.codelist_value_template (
id 			int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
code_id		integer,
value		varchar,
description	text,
CONSTRAINT clv_template_unique UNIQUE (code_id, value)
);
COMMENT ON TABLE qgis_pkg.codelist_value_template IS 'Contains codelist values';

CREATE INDEX clv_code_id_idx  ON qgis_pkg.codelist_value_template (code_id);
CREATE INDEX clv_value_idx    ON qgis_pkg.codelist_value_template (value);


------------------------------------------------------------------
-- VIEW qgis_pkg.v_codelist_value_template
------------------------------------------------------------------
DROP VIEW IF EXISTS qgis_pkg.v_codelist_value_template CASCADE;
CREATE VIEW         qgis_pkg.v_codelist_value_template AS
SELECT
	cv.id,
	c.data_model,
	c.name,
	cv.value,
	cv.description,
	c.name_space
FROM
	qgis_pkg.codelist_value_template AS cv
	INNER JOIN qgis_pkg.codelist_template AS c ON (cv.code_id = c.id);


--**************************
DO $$
BEGIN
RAISE NOTICE E'\n\nDone\n\n';
END $$;
--**************************




