-- ddh=deltadamheight
--dnh = deltanaturalheight
-- calcul pour wso_id=3
--te taux d'étagement

select max(cs_height)-min(cs_height) as ddh * from ccm21.catchments c
join ccm21.riversegments r on r.wso1_id=c.wso1_id
where wso3_id=294040;

select  max(elev_mean)-min(elev_mean) as dnh from ccm21.catchments c
join ccm21.riversegments r on r.wso1_id=c.wso1_id
where wso3_id=294040;


drop table if exists ccm21.bretagne_test;
create table ccm21.bretagne_test as (
        select te.*,c0.the_geom from ccm21.catchments_bretagne c0 join (
                select wso3_id,max(cs_height)-min(cs_height) as ddh,
                 max(elev_mean)-min(elev_mean) as dnh,
                 CASE WHEN (max(elev_mean)-min(elev_mean))=0 then NULL
                 ELSE  (max(cs_height)-min(cs_height)) / (max(elev_mean)-min(elev_mean)) 
                 END as te 
                 from ccm21.catchments_bretagne c
                join ccm21.riversegments_bretagne r on r.wso1_id=c.wso1_id
                group by wso3_id) as te
        on te.wso3_id=c0.wso3_id);
alter table ccm21.bretagne_test add column id serial;
 alter table ccm21.bretagne_test add CONSTRAINT bretagne_test_pkey PRIMARY KEY (id);
 alter table ccm21.bretagne_test add CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2);
 alter table ccm21.bretagne_test add CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL);
 alter table ccm21.bretagne_test add CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035);

 drop table if exists ccm21.bretagne_test_wso_2;
create table ccm21.bretagne_test_wso_2 as (
        select te.*,c0.the_geom from ccm21.catchments_bretagne c0 join (
                select wso2_id,max(cs_height)-min(cs_height) as ddh,
                 max(elev_mean)-min(elev_mean) as dnh,
                 CASE WHEN (max(elev_mean)-min(elev_mean))=0 then NULL
                 ELSE  (max(cs_height)-min(cs_height)) / (max(elev_mean)-min(elev_mean)) 
                 END as te 
                 from ccm21.catchments_bretagne c
                join ccm21.riversegments_bretagne r on r.wso1_id=c.wso1_id
                group by wso2_id) as te
        on te.wso2_id=c0.wso2_id);
alter table ccm21.bretagne_test_wso_2 add column id serial;
 alter table ccm21.bretagne_test_wso_2 add CONSTRAINT bretagne_testwso2_pkey PRIMARY KEY (id);
 alter table ccm21.bretagne_test_wso_2 add CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2);
 alter table ccm21.bretagne_test_wso_2 add CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL);
 alter table ccm21.bretagne_test_wso_2 add CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035);

drop table if exists ccm21.taux_etagement;
create table ccm21.taux_etagement as (
        select te.*,c0.the_geom from ccm21.catchments c0 join (
                select wso3_id,max(cs_height)-min(cs_height) as ddh,
                 max(elev_mean)-min(elev_mean) as dnh,
                 CASE WHEN (max(elev_mean)-min(elev_mean))=0 then NULL
                 ELSE  (max(cs_height)-min(cs_height)) / (max(elev_mean)-min(elev_mean)) 
                 END as te 
                 from ccm21.catchments c
                join (select * from ccm21.riversegments  WHERE wso_id IN (SELECT wso_id FROM europe.wso WHERE area='France'))r
  on r.wso1_id=c.wso1_id
                group by wso3_id) as te
        on te.wso3_id=c0.wso3_id);
alter table ccm21.taux_etagement add column id serial;
 alter table ccm21.taux_etagement add CONSTRAINT taux_etagement_pkey PRIMARY KEY (id);
 alter table ccm21.taux_etagement add CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2);
 alter table ccm21.taux_etagement add CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL);
 alter table ccm21.taux_etagement add CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035);