create schema spatial_query;
create table spatial_query.squery(
queryid TEXT NOT NULL, 
userid  TEXT NOT NULL, 
crs TEXT NOT NULL, 
squery TEXT NOT NULL, 
timeconstraint  TEXT NOT NULL,
status TEXT,
creationdate TIMESTAMP WITH TIME ZONE,
lastupdatedon TIMESTAMP WITH TIME ZONE,
expirationdate TIMESTAMP WITH TIME ZONE,
statusreport TEXT,

constraint squery_pk PRIMARY KEY (queryid,userid)
);

create or replace function public.espas_update_geovertextent() returns trigger 
as $$
    BEGIN
         IF(TG_OP = 'UPDATE' ) THEN
            UPDATE geovertextent SET geometryextent = ST_GeomFromGML(replace(replace(old.wktextgeometryextent,'&lt;','<'),'&gt;','>')), geometryextent_geo = ST_GeomFromGML(replace(replace(old.gmlgeometryextent1,'&lt;','<'),'&gt;','>')), geometryextent_gse = ST_GeomFromGML(replace(replace(old.gmlgeometryextent2,'&lt;','<'),'&gt;','>')) WHERE id=old.id;
            return new;
        ELSIF (TG_OP = 'INSERT') THEN
            UPDATE geovertextent SET geometryextent = ST_GeomFromGML(replace(replace(new.wktextgeometryextent,'&lt;','<'),'&gt;','>')), geometryextent_geo = ST_GeomFromGML(replace(replace(new.gmlgeometryextent1,'&lt;','<'),'&gt;','>')), geometryextent_gse = ST_GeomFromGML(replace(replace(new.gmlgeometryextent2,'&lt;','<'),'&gt;','>')) WHERE id=new.id;
            return new;
        ELSE
            return null;
         END IF;   
    END
$$ LANGUAGE plpgsql;

drop function spatial_query.cleanupexpiredquery;

create or replace function spatial_query.cleanupexpiredquery(_queryid text) returns void as $$
DECLARE
BEGIN 
   update spatial_query.squery set status='EXPIRED', statusreport ='Query expiration time has surpassed. All query results and details have been removed from the system.' where queryid=_queryid;
   execute 'drop table if exists spatial_query.'|| _queryid; 
   execute 'drop table if exists spatial_query.'|| _queryid || '_result'; 
END 
$$ LANGUAGE plpgsql;
