WITH nb_mois_bonne as
(select distinct pays,no_site,no_menage,
sum(janvier::int+fevrier::int+mars::int+avril::int+mai::int+juin::int+juillet::int+aout::int+septembre::int+octobre::int+novembre::int+decembre::int)
 as nb_mois_bonne_saison
from total.v_liste_menage inner join total.selection_mois_generique using (pays,no_menage)
where no_selection=meilleure_saison_peche group by no_site,no_menage,total.v_liste_menage.pays)

,nb_mois_mauvaise as
(select distinct pays,no_site,no_menage,
sum(janvier::int+fevrier::int+mars::int+avril::int+mai::int+juin::int+juillet::int+aout::int+septembre::int+octobre::int+novembre::int+decembre::int)
as nb_mois_mauvaise_saison
from total.v_liste_menage inner join total.selection_mois_generique using (pays,no_menage)
where no_selection=moins_bonne_saison_peche group by no_site,no_menage,total.v_liste_menage.pays
)

,bonne_saison as
(select distinct pays,no_site,no_menage,exp((ln(min_qte_pechee_meil_saison)+ln(max_qte_pechee_meil_saison))/2.0)*(nb_sortie_meilleure_saison/7.0)*
nb_mois_bonne_saison *30 as captures_bonne from total.v_liste_menage inner join nb_mois_bonne using(pays,no_site,no_menage)
left join nb_mois_mauvaise using (pays,no_site, no_menage) where date_enquete_m is not null
and min_qte_pechee_meil_saison>0
and max_qte_pechee_meil_saison>0
and nb_sortie_meilleure_saison<=7
and nb_sortie_meilleure_saison>0
and min_qte_pechee_meil_saison<100
and max_qte_pechee_meil_saison<200
and (nb_mois_bonne_saison+nb_mois_mauvaise_saison)between 1 and 12
)

,mauvaise_saison as
(select distinct pays,no_site,no_menage,
exp((ln(min_pue_moins_bonnel_saison)+ln(max_pue_semaine_moins_bonnel_saison))/2.0)*(nb_sortie_moins_bonne_saison/7.0)*
(nb_mois_mauvaise_saison)*30 as captures_mauvaise,
exp((ln(min_pue_moins_bonnel_saison)+ln(max_pue_semaine_moins_bonnel_saison))/2.00)*(nb_sortie_moins_bonne_saison/7.0)*(12-nb_mois_bonne_saison)*30
as captures_mauvaise_meth2
from total.v_liste_menage inner join nb_mois_mauvaise using(pays,no_site,no_menage)
inner join nb_mois_bonne using(pays,no_site,no_menage)
where date_enquete_m is not null
and min_pue_moins_bonnel_saison>0 and min_pue_moins_bonnel_saison<50
and max_pue_semaine_moins_bonnel_saison>0 and max_pue_semaine_moins_bonnel_saison<100
and nb_sortie_moins_bonne_saison<=7 and nb_sortie_moins_bonne_saison>0
and (nb_mois_bonne_saison+nb_mois_mauvaise_saison) between 1 and 12
)


,moyenne_saison as
(select distinct pays,no_site,no_menage,
(exp((ln(min_qte_pechee_meil_saison)+ln(max_qte_pechee_meil_saison))/2.00)+exp((ln(min_pue_moins_bonnel_saison)+ln(max_pue_semaine_moins_bonnel_saison))
/2.00))/2.0*((nb_sortie_meilleure_saison/7.00)+(nb_sortie_moins_bonne_saison/7.00))/2.00*(12-(nb_mois_bonne_saison+nb_mois_mauvaise_saison))*30
 as captures_moyenne
from total.v_liste_menage inner join nb_mois_bonne using(pays,no_site,no_menage) inner join nb_mois_mauvaise using(pays,no_site,no_menage)
where date_enquete_m is not null
and min_qte_pechee_meil_saison>0 and min_qte_pechee_meil_saison<100
and max_qte_pechee_meil_saison>0 and max_qte_pechee_meil_saison<200
and min_pue_moins_bonnel_saison>0 and min_pue_moins_bonnel_saison<50
and nb_sortie_meilleure_saison<=7 and nb_sortie_moins_bonne_saison<=7
and nb_sortie_meilleure_saison>0 and  nb_sortie_moins_bonne_saison>0
and max_pue_semaine_moins_bonnel_saison>0 and max_pue_semaine_moins_bonnel_saison<100
and(nb_mois_bonne_saison+nb_mois_mauvaise_saison) between 1 and 12
)
,union_saison as
(SELECT DISTINCT pays,no_site, no_menage FROM mauvaise_saison
UNION SELECT DISTINCT pays,no_site, no_menage FROM bonne_saison)

,req_moy_b as
(select distinct pays,no_site, avg(captures_bonne) as moy_b from bonne_saison group by pays,no_site)

,req_moy_b_tot as
(select pays,avg(captures_bonne) as moy_b_tot from bonne_saison group by pays)

,req_moy_m as
(select distinct pays,no_site, avg(captures_mauvaise) as moy_m from mauvaise_saison group by pays,no_site)

,req_moy_m_meth2 as
(select distinct pays,no_site, avg(captures_mauvaise_meth2) as moy_m_meth2 from mauvaise_saison group by pays,no_site)

,req_moy_m_tot as
(select pays,avg(captures_mauvaise) as moy_m_tot from mauvaise_saison group by pays)

,req_moy_m_tot_meth2 as
(select pays,avg(captures_mauvaise_meth2) as moy_m_tot_meth2 from mauvaise_saison group by pays)

,req_moy_moy as
(select distinct pays,no_site, avg(captures_moyenne) as moy_moy from moyenne_saison group by pays,no_site)

,req_moy_moy_tot as
(select pays,avg(captures_moyenne) as moy_moy_tot from moyenne_saison group by pays)
--select moy_moy_tot, moy_b_tot, moy_m_tot from req_moy_moy_tot, req_moy_m_tot, req_moy_b_tot

,bonne_saison_c as
(select distinct pays,no_site, no_menage, moy_b_tot, moy_b, CASE WHEN captures_bonne is null then (case when moy_b is null then moy_b_tot else moy_b end) else captures_bonne end
as captures_bonne_c FROM union_saison left join req_moy_b using (pays,no_site)
left join bonne_saison using(pays,no_site, no_menage) inner join  req_moy_b_tot using(pays)
)
,mauvaise_saison_c as
(select distinct pays,no_site, no_menage, case when captures_mauvaise is null then (case when moy_m is null then moy_m_tot else moy_m end) else captures_mauvaise
end as captures_mauvaise_c FROM union_saison left join req_moy_m using (pays,no_site)
 left join mauvaise_saison using(pays,no_site, no_menage) inner join req_moy_m_tot using(pays)
)

,mauvaise_saison_meth2_c as
(select distinct pays,no_site, no_menage, case when captures_mauvaise_meth2 is null then (case when moy_m_meth2 is null then moy_m_tot_meth2 else moy_m_meth2 end)
else captures_mauvaise_meth2 end as captures_mauvaise_meth2_c FROM
 union_saison left join req_moy_m_meth2 using (pays,no_site) left join mauvaise_saison using
(pays,no_site, no_menage) inner join req_moy_m_tot_meth2 using(pays)
)

,moyenne_saison_c as
(select distinct pays,no_site, no_menage, captures_moyenne, case when captures_moyenne is null then (case when moy_moy is null then moy_moy_tot else moy_moy end) else
captures_moyenne end as captures_moyenne_c FROM union_saison
 left join req_moy_moy using (pays,no_site) left join moyenne_saison
  using(pays,no_site, no_menage)
  inner join req_moy_moy_tot using(pays) order by captures_moyenne_c desc
)



,total as
(select distinct pays,no_site,no_menage,sum(captures_bonne_c+captures_mauvaise_c) as capture_c,
 sum(captures_bonne_c+captures_mauvaise_meth2_c) as capture_meth2_c,
 sum(captures_bonne_c+captures_mauvaise_c+captures_moyenne_c) as capture_meth3_c
from bonne_saison_c inner join mauvaise_saison_c using (pays,no_site,no_menage)
inner join mauvaise_saison_meth2_c using (pays,no_site, no_menage)inner join
moyenne_saison_c using (pays,no_site,no_menage)
group by pays,no_site,no_menage)
,calcul_txb as
(
WITH t1 AS (
         SELECT DISTINCT v_liste_menage.pays,v_liste_menage.no_site, count(DISTINCT v_liste_menage.no_menage) AS nb_menage_tot
           FROM total.liste_site
      JOIN total.v_liste_menage USING (no_site)
      where (nb_homme_peche+nb_femme_peche)>0
     GROUP BY v_liste_menage.pays,v_liste_menage.no_site
        )
 SELECT DISTINCT pays,t1.no_site, t1.nb_menage_tot::double precision / count(DISTINCT union_saison.no_menage)::double precision AS txb
   FROM t1
   JOIN union_saison  USING (pays,no_site)

  GROUP BY pays,no_site, t1.nb_menage_tot
  )
,calcul_tx_strate as
(
select distinct pays,strate,no_site,txa,txc from
total.liste_site inner join total.calcul_txa using(pays,strate)
 inner join total.calcul_txc using(pays,strate)
)
,final as
(select distinct pays as ABSCISSE,'méthode 1' as GROUPE,sum(capture_c*txa*txc*txb)/1000 as TOTAL
from total inner join calcul_tx_strate using(pays,no_site) inner join calcul_txb using(pays,no_site)
group by pays
union
-- La méthode 2 consiste à appliquer les mauvaises PUE à tous les mois qui ne sont pas dans la bonne saison (12-nb_mois_bonne saison)
select distinct pays as ABSCISSE,'méthode 2' as GROUPE,sum(capture_meth2_c*txa*txb*txc)/1000 as TOTAL
from total inner join calcul_tx_strate using(pays,no_site) inner join calcul_txb using(pays,no_site)
group by pays
union

select distinct pays as ABSCISSE,'méthode 3' as GROUPE,sum(capture_meth3_c*txa*txb*txc)/1000 as TOTAL
from total inner join calcul_tx_strate using(pays,no_site) inner join calcul_txb using(pays,no_site)
group by pays
)
select * from final order by TOTAL desc,GROUPE