SIGALERTA_GEOSERVER_ALERT_REFINED

DROP INDEX "SIGALERTA"."IDX_GIST_GEOSERVER_ALERT_REFINED" FORCE;
DROP TABLE "SIGALERTA"."GEOSERVER_ALERT_REFINED";
ALTER INDEX "SIGALERTA"."IDX_ALERT_REFINED_GEOMETRY_ID" REBUILD ONLINE PARALLEL 4;

CREATE TABLE GEOSERVER_ALERT_REFINED
  AS
    SELECT
        A.alert_id,
        A.source AS alert_source,
        A.detection_date AS alert_detection_date,
        A.insertion_date AS alert_insertion_date,
        A.chargeable AS alert_chargeable,
        A.satellite_name AS alert_satellite_name,
        A.deleted_at AS Alert_deleted_at,
        A.deleted_by_user_id AS alert_deleted_by_user_id,
        A.user_id AS alert_geometry_user_id,
        A.alert_geometry_id,
        A.detection_class_id AS alert_geometry_detection_class_id,
        A.area AS alert_geometry_area,
        A.bioma_id,
        A.assentamento_id,
        A.municipio_id,
        A.uc_id,
        A.ucs_jurisdicao,
        --A.asv_id,
        A.validation_bool,
        A.validated_class_id,
        A.status_id,
        A.geoembargo_id_geo,
        A.geo_car_source,
        A.ti_id,
        A.reserva_legal_id,
        A.area_uso_restrito_id,
        A.vegetacao_preterita_id,
        A.vegetacao_preterita_categories,
        '0' AS refined,
        CASE
            WHEN A.asv_id IS NULL THEN A.peso_total
            WHEN A.asv_id IS NOT NULL THEN 0
        END AS final_weight,
        CASE
            WHEN A.geoembargo_id_geo IS NULL THEN 'Não'
            WHEN A.geoembargo_id_geo IS NOT NULL THEN 'Sim'
        END AS embargo_bool,
        CASE
            WHEN A.uc_id IS NULL THEN 'Não'
            WHEN A.uc_id IS NOT NULL THEN 'Sim'
        END AS uc_bool,
        CASE
            WHEN A.assentamento_id IS NULL THEN 'Não'
            WHEN A.assentamento_id IS NOT NULL THEN 'Sim'
        END AS assentamento_bool,
        CASE
            WHEN A.asv_id IS NULL THEN 'Não'
            WHEN A.asv_id IS NOT NULL THEN 'Sim'
        END AS asv_bool,
        CASE
            WHEN A.ti_id IS NULL THEN 'Não'
            WHEN A.ti_id IS NOT NULL THEN 'Sim'
        END AS ti_bool,
    (SELECT SDO_UTIL.rectify_geometry(geom,0.05) FROM ALERT_REFINED_GEOMETRY WHERE id = A.alert_geometry_id) AS geom
FROM
(
    SELECT
        A.id AS alert_id,
        A.source,
        A.detection_date,
        A.insertion_date,
        A.chargeable,
        A.satellite_name,
        A.deleted_at,
        A.user_id,
        A.deleted_by_user_id,
        A.alert_geometry_id,
        A.detection_class_id,
        A.validation_bool,
        A.VALIDATED_CLASS_ID,
        A.status_id,
        A.area,
        LISTAGG(DISTINCT A.MODIFICATION_DATE,',') AS modification_date,
        (SELECT ROUND((A.TOTAL_WEIGHT * 100) / (SELECT MAX(TOTAL_WEIGHT) FROM ALERT_REFINED_GEOMETRY)) FROM ALERT_REFINED_GEOMETRY WHERE A.alert_geometry_id = ID) AS peso_total,
        (SELECT LISTAGG(CONCAT('-', CONCAT(x.ID_GEO, '-')) ,',') FROM (SELECT DISTINCT ID_GEO FROM ALERT_REFINED_GEOMETRY_HAS_GEO_ASSENTAMENTO c WHERE A.alert_geometry_id = c.ID_ALERT) x) AS assentamento_id,
        (SELECT LISTAGG(CONCAT('-', CONCAT(x.ID_GEO, '-')) ,',') FROM (SELECT DISTINCT ID_GEO FROM ALERT_REFINED_GEOMETRY_HAS_GEO_MUNICIPIO c WHERE A.alert_geometry_id = c.ID_ALERT) x) AS municipio_id,
        (SELECT LISTAGG(CONCAT('-', CONCAT(x.ID_GEO, '-')) ,',') FROM (SELECT DISTINCT ID_GEO FROM ALERT_REFINED_GEOMETRY_HAS_GEO_UC c WHERE A.alert_geometry_id = c.ID_ALERT) x) AS uc_id,
        (SELECT LISTAGG(CONCAT('-', CONCAT(x.JURISDICAO, '-')),',') FROM (SELECT DISTINCT JURISDICAO FROM ALERT_REFINED_GEOMETRY_HAS_GEO_UC c INNER JOIN GEO_UC ON c.ID_GEO = GEO_UC.ID WHERE A.alert_geometry_id = c.ID_ALERT) x) AS ucs_jurisdicao,
        (SELECT LISTAGG(CONCAT('-', CONCAT(x.ID_GEO, '-')) ,',') FROM (SELECT DISTINCT ID_GEO FROM ALERT_REFINED_GEOMETRY_HAS_GEO_TI c WHERE A.alert_geometry_id = c.ID_ALERT) x) AS ti_id,
        (SELECT DISTINCT ID_GEO FROM ALERT_REFINED_GEOMETRY_HAS_GEO_ASV c WHERE A.alert_geometry_id = c.ID_ALERT FETCH FIRST 1 ROWS ONLY) AS asv_id,
        --(SELECT LISTAGG(CONCAT('-', CONCAT(x.ID_GEO, '-')) ,',') FROM (SELECT DISTINCT ID_GEO FROM ALERT_REFINED_GEOMETRY_HAS_GEO_ASV c WHERE A.alert_geometry_id = c.ID_ALERT) x) as asv_id,
        (SELECT LISTAGG(CONCAT('-', CONCAT(x.ID_GEO, '-')) ,',') FROM (SELECT DISTINCT ID_GEO FROM ALERT_REFINED_GEOMETRY_HAS_GEO_EMBARGO c WHERE A.alert_geometry_id = c.ID_ALERT) x) AS geoembargo_id_geo,
        (SELECT LISTAGG(CONCAT('-', CONCAT(x.ID_GEO, '-')),',') FROM (SELECT DISTINCT ID_GEO FROM ALERT_REFINED_GEOMETRY_HAS_GEO_BIOMA c WHERE A.alert_geometry_id = c.ID_ALERT) x) AS bioma_id,
        (SELECT LISTAGG(CONCAT('-', CONCAT(x.ID_GEO, '-')),',') FROM (SELECT DISTINCT ID_GEO FROM ALERT_REFINED_GEOMETRY_HAS_GEO_RESERVA_LEGAL c WHERE A.alert_geometry_id = c.ID_ALERT) x) AS reserva_legal_id,
        (SELECT LISTAGG(CONCAT('-', CONCAT(x.ID_GEO, '-')),',') FROM (SELECT DISTINCT ID_GEO FROM ALERT_REFINED_GEOMETRY_HAS_GEO_AREAS_USO_RESTRITO c WHERE A.alert_geometry_id = c.ID_ALERT) x) AS area_uso_restrito_id,
        (SELECT LISTAGG(CONCAT('-', CONCAT(x.ID_GEO, '-')),',') FROM (SELECT DISTINCT ID_GEO FROM ALERT_REFINED_GEOMETRY_HAS_GEO_VEGETACAO_PRETERITA c WHERE A.alert_geometry_id = c.ID_ALERT) x) AS vegetacao_preterita_id,
        (SELECT LISTAGG(CONCAT('-', CONCAT(x.CATEGORY, '-')),',') FROM (SELECT DISTINCT gvegp.CATEGORY FROM ALERT_REFINED_GEOMETRY_HAS_GEO_VEGETACAO_PRETERITA c INNER JOIN GEO_VEGETACAO_PRETERITA gvegp ON gvegp.ID = c.ID_GEO WHERE A.alert_geometry_id = c.ID_ALERT) x) AS vegetacao_preterita_categories,
        (SELECT LISTAGG(CONCAT('-', CONCAT(x.source, '-')) ,',') FROM (SELECT DISTINCT source FROM ALERT_REFINED_GEOMETRY_HAS_GEO_CAR c INNER JOIN GEO_CAR ON GEO_CAR.ID = C.ID_GEO WHERE A.alert_geometry_id = c.ID_ALERT) x) AS geo_car_source
    FROM (
        SELECT
            a.id,
            a.source,
            a.detection_date,
            a.insertion_date,
            a.satellite_type,
            a.satellite_name,
            a.chargeable,
            a.deleted_at,
            a.deleted_by_user_id,
            b.validation_bool,
            b.status_id,
            b.user_id,
            b.VALIDATED_CLASS_ID,
            b.ID AS alert_geometry_id,
            b.DETECTION_CLASS_ID AS detection_class_id,
            SDO_GEOM.SDO_AREA(SDO_CS.TRANSFORM(b.GEOM,1000202),0.001,'unit=HECTARE') AS area,
            b.MODIFICATION_DATE  AS modification_date
        FROM ALERT a
            INNER JOIN ALERT_REFINED_GEOMETRY b ON a.id = b.alert_id
        WHERE
            a.DELETED_AT IS NULL AND B.ID IN (SELECT MAX(ALERT_REFINED_GEOMETRY.ID) FROM ALERT INNER JOIN ALERT_REFINED_GEOMETRY ON ALERT.ID = ALERT_REFINED_GEOMETRY.alert_ID WHERE ALERT.ID = A.ID)
        ) A
        WHERE
         A.area >= 1
        GROUP BY
        A.id,
        A.source,
        A.user_id,
        A.VALIDATED_CLASS_ID,
        A.satellite_name,
        A.detection_date,
        A.insertion_date,
        A.chargeable,
        A.deleted_at,
        A.deleted_by_user_id,
        A.alert_geometry_id,
        A.detection_class_id,
        A.validation_bool,
        A.status_id,
        A.area
) A;

  ALTER TABLE GEOSERVER_ALERT_REFINED
ADD CONSTRAINT GEOSERVER_ALERT_REFINED_PK PRIMARY KEY (alert_id);


  INSERT INTO user_sdo_geom_metadata
    (TABLE_NAME,
     COLUMN_NAME,
     DIMINFO,
     SRID)
  VALUES (
  'GEOSERVER_ALERT_REFINED',
  'GEOM',
  SDO_DIM_ARRAY(   -- 20X20 grid
    SDO_DIM_ELEMENT('X', -90, 90, 0.005),
    SDO_DIM_ELEMENT('Y', -180, 180, 0.005)
     ),
  4674   -- SRID
);

CREATE INDEX IDX_GIST_GEOSERVER_ALERT_REFINED ON "SIGALERTA"."GEOSERVER_ALERT_REFINED"("GEOM") INDEXTYPE IS MDSYS.SPATIAL_INDEX