CREATE TABLE line_elements (
	ID INT,  
	PRIMARY KEY (ID)
);

CREATE TABLE gis_groups (
	ID INT, 
	NAME VARCHAR(255), 
	PRIMARY KEY (ID)
);

CREATE TABLE maps (
	ID INT NOT NULL, 
	NAME VARCHAR(255), 
	X_MIN REAL,
	Y_MIN REAL,
	X_MAX REAL,
	Y_MAX REAL,
	PRIMARY KEY (ID)
);

CREATE TABLE layers (
	ID INT NOT NULL, 
	NAME VARCHAR(255), 
	map_id INT, 
	PRIMARY KEY (ID)
);

CREATE TABLE users2groups (
	group_id INT NOT NULL, 
	user_id INT NOT NULL, 
	PRIMARY KEY (group_id, user_id)
);

CREATE TABLE config (
	ID INT NOT NULL, 
	NAME VARCHAR(255), 
	VALUE VARCHAR(255), 
	PRIMARY KEY (ID)
);

CREATE TABLE gis_users (
	ID INT NOT NULL, 
	NAME VARCHAR(255), 
	PRIMARY KEY (ID)
);

CREATE TABLE dot_elements (
	ID INT NOT NULL, 
	PRIMARY KEY (ID)
);

CREATE TABLE polygon_elements (
	ID INT NOT NULL, 
	PRIMARY KEY (ID)
);

CREATE TABLE layer_elements (
	ID INT NOT NULL, 
	DTYPE VARCHAR(31), 
	NAME VARCHAR(255), 
	layer_id INT, 
	PRIMARY KEY (ID)
);

ALTER TABLE line_elements ADD CONSTRAINT FK_line_elements_ID FOREIGN KEY (ID) REFERENCES layer_elements (ID);

ALTER TABLE layers ADD CONSTRAINT FK_layers_map_id FOREIGN KEY (map_id) REFERENCES maps (ID);

ALTER TABLE users2groups ADD CONSTRAINT FK_users2groups_user_id FOREIGN KEY (user_id) REFERENCES gis_groups (ID);

ALTER TABLE users2groups ADD CONSTRAINT FK_users2groups_group_id FOREIGN KEY (group_id) REFERENCES gis_users (ID);

ALTER TABLE dot_elements ADD CONSTRAINT FK_dot_elements_ID FOREIGN KEY (ID) REFERENCES layer_elements (ID);

ALTER TABLE polygon_elements ADD CONSTRAINT FK_polygon_elements_ID FOREIGN KEY (ID) REFERENCES layer_elements (ID);

ALTER TABLE layer_elements ADD CONSTRAINT FK_layer_elements_layer_id FOREIGN KEY (layer_id) REFERENCES layers (ID);

CREATE SEQUENCE SEQ_GEN_SEQUENCE INCREMENT BY 5 START WITH 50;

-- Create GIS fields

select AddGeometryColumn('line_elements', 'the_geom', 4326, 'LINESTRING', 2);
select AddGeometryColumn('dot_elements', 'the_geom', 4326, 'POINT', 2);
select AddGeometryColumn('polygon_elements', 'the_geom', 4326, 'POLYGON', 2);

create index line_elements_gist_idx on line_elements using gist(the_geom);
create index dot_elements_gist_idx on dot_elements using gist(the_geom);
create index polygon_elements_gist_idx on polygon_elements using gist(the_geom);

-- core functions;

create or replace function count_extent(map_id maps.id%TYPE ) returns RECORD as $$
DECLARE
        retval RECORD;
BEGIN
        select max(x_max) as xmax, max(y_max) as ymax, min(x_min) as xmin, min(y_min) as ymin from (
                select st_xmax(st_box2d(st_extent(the_geom))) as x_max,
                           st_xmin(st_box2d(st_extent(the_geom))) as x_min,
                           st_ymax(st_box2d(st_extent(the_geom))) as y_max,
                           st_ymin(st_box2d(st_extent(the_geom))) as y_min
                        from layer_elements le, line_elements g, layers l where
                                l.map_id=map_id and g.id=le.id and le.layer_id=l.id
                union all
                select st_xmax(st_box2d(st_extent(the_geom))) as x_max,
                       st_xmin(st_box2d(st_extent(the_geom))) as x_min,
                       st_ymax(st_box2d(st_extent(the_geom))) as y_max,
                       st_ymin(st_box2d(st_extent(the_geom))) as y_min
                        from layer_elements le, dot_elements g, layers l where
                                l.map_id=map_id and g.id=le.id and le.layer_id=l.id
                union all
                select st_xmax(st_box2d(st_extent(the_geom))) as x_max,
                           st_xmin(st_box2d(st_extent(the_geom))) as x_min,
                           st_ymax(st_box2d(st_extent(the_geom))) as y_max,
                           st_ymin(st_box2d(st_extent(the_geom))) as y_min
                        from layer_elements le, polygon_elements g, layers l where
                                l.map_id=map_id and g.id=le.id and le.layer_id=l.id
        ) as sub into retval;
	update maps set x_min=retval.xmin, x_max=retval.xmax, y_min=retval.ymin, y_max=retval.ymax where id=map_id;
        return retval;
END
$$ language plpgsql;

create or replace function recount_extents_ui() returns trigger as $$
DECLARE
	mid maps.id%TYPE;
BEGIN
	select l.map_id into mid from layer_elements as e, layers as l 
		where e.layer_id=l.id and e.id=NEW.id;
	perform count_extent(mid);
	return NEW;
END
$$ language plpgsql;

create or replace function recount_extents_d() returns trigger as $$
DECLARE
        mid maps.id%TYPE;
BEGIN
        select l.map_id into mid from layer_elements as e, layers as l
                where e.layer_id=l.id and e.id=OLD.id;
        perform count_extent(mid);
        return OLD;
END
$$ language plpgsql;

create or replace function create_required_views(lid layers.id%TYPE) returns int as $$
DECLARE
        view_name VARCHAR(128);
        mid maps.id%TYPE;
        t_val int;
        query TEXT;
BEGIN
        select map_id from layers as l where l.id=lid into mid;
        view_name := 'map' || mid || '_l' || lid;

        select populate_layer(lid, view_name || '_l', 'LINESTRING', 'line_elements') into t_val;
        select populate_layer(lid, view_name || '_p', 'POLYGON', 'polygon_elements') into t_val;
        select populate_layer(lid, view_name || '_s', 'POINT', 'dot_elements') into t_val;
        return 0;
END
$$ language plpgsql volatile;

create or replace function tr_create_required_views() returns trigger as $$
DECLARE
	lid layers.id%TYPE;
BEGIN
	lid=NEW.id;
	perform create_required_views(lid);
	return NEW;
END
$$ language plpgsql;

create or replace function populate_layer(lid layers.id%TYPE, view_name varchar, spatial_type varchar, le varchar) returns int as $$
DECLARE
        query text;
BEGIN
        query := 'create or replace view ' || view_name || ' as
                select e.id as id, l.name as layer_name, g.the_geom as the_geom, e.name as name, l.id as layer_id 
                        from layers as l, layer_elements as e, ' || le || ' as g 
                        where g.id=e.id and e.layer_id=' || lid;
        execute query;

        query := 'create or replace rule '|| view_name || 'i ' || '
                as on insert to '|| view_name || '
                do instead (
                        insert into layer_elements (layer_id, id, name) values('|| lid ||', NEW.id, NEW.name);
                        insert into ' || le ||' (id, the_geom) values(NEW.id, NEW.the_geom);
                )';
        execute query;
                
        query := 'create or replace rule ' || view_name || 'u ' || '
                as on update to ' || view_name || '
                do instead (
                        update ' || le || ' set the_geom=NEW.the_geom where id=OLD.id;
                        update layer_elements set name=NEW.name where id=OLD.id;
                )';
        execute query;

        query := 'create or replace rule ' || view_name || 'd ' || '
                as on delete to ' || view_name || '
                do instead (
                        delete from ' || le || ' where id=OLD.id;
                        delete from layer_elements where id=OLD.id;
                )';
        execute query;
        
        insert into geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, type)
               values ('', 'public', view_name, 'the_geom', 2, 4326, spatial_type);
        
        return 0;
END 
$$ language plpgsql volatile;

-- Trigger section

create trigger recalculate_extents_di after insert or update on dot_elements for each row
        execute procedure recount_extents_ui();

create trigger recalculate_extens_li after insert or update on line_elements for each row
	execute procedure recount_extents_ui();

create trigger recalculate_extents_pi after insert or update on polygon_elements for each row
	execute procedure recount_extents_ui();

create trigger recalculate_extents_dd after delete on dot_elements for each row
	execute procedure recount_extents_d();

create trigger recalculate_extents_ld after delete on line_elements for each row
	execute procedure recount_extents_d();

create trigger recalculate_extents_pd after delete on polygon_elements for each row
	execute procedure recount_extents_d();

create trigger layer_added_triger after insert on layers for each row
	execute procedure tr_create_required_views();

