WITH nb_mois_bonne as
(select distinct 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 ci.v_liste_menage inner join ci.selection_mois_generique using (no_menage)
where no_selection=meilleure_saison_peche group by no_site,no_menage)

,nb_mois_mauvaise as
(select distinct 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 ci.v_liste_menage inner join ci.selection_mois_generique using (no_menage)
where no_selection=moins_bonne_saison_peche group by no_site,no_menage
)

,bonne_saison as
(select distinct 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 ci.v_liste_menage inner join nb_mois_bonne using(no_site,no_menage)
left join nb_mois_mauvaise using (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 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 ci.v_liste_menage inner join nb_mois_mauvaise using(no_site,no_menage)
inner join nb_mois_bonne using(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 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 ci.v_liste_menage inner join nb_mois_bonne using(no_site,no_menage) inner join nb_mois_mauvaise using(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 no_site, no_menage FROM mauvaise_saison UNION SELECT DISTINCT no_site, no_menage FROM bonne_saison)

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

,req_moy_b_tot as
(select avg(captures_bonne) as moy_b_tot from bonne_saison)

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

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

,req_moy_m_tot as
(select avg(captures_mauvaise) as moy_m_tot from mauvaise_saison)

,req_moy_m_tot_meth2 as
(select avg(captures_mauvaise_meth2) as moy_m_tot_meth2 from mauvaise_saison)

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

,req_moy_moy_tot as
(select avg(captures_moyenne) as moy_moy_tot from moyenne_saison)

--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 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 (no_site)left join bonne_saison using(no_site, no_menage), req_moy_b_tot
)

,mauvaise_saison_c as
(select distinct 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 (no_site) left join mauvaise_saison using(no_site, no_menage),req_moy_m_tot
)

,mauvaise_saison_meth2_c as
(select distinct 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 (no_site) left join mauvaise_saison using
(no_site, no_menage),req_moy_m_tot_meth2
)

,moyenne_saison_c as
(select distinct 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 (no_site) left join moyenne_saison using(no_site, no_menage),
req_moy_moy_tot order by captures_moyenne_c desc
)



,total as
(select distinct 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 (no_site,no_menage) inner join mauvaise_saison_meth2_c using (no_site, no_menage)inner join
moyenne_saison_c using (no_site,no_menage)
group by no_site,no_menage)

,calcul_txb as
(
WITH t1 AS (
         SELECT DISTINCT v_liste_menage.no_site, count(DISTINCT v_liste_menage.no_menage) AS nb_menage_tot
           FROM ci.liste_site
      JOIN ci.v_liste_menage USING (no_site)
      where (nb_homme_peche+nb_femme_peche)>0
     GROUP BY v_liste_menage.no_site
        )
 SELECT DISTINCT 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 (no_site)

  GROUP BY no_site, t1.nb_menage_tot
  )

select distinct ci.pre_enquete_village.region_niv1 as ABSCISSE,'méthode 1' as GROUPE,sum(capture_c*txa*txc*txb)/1000 as TOTAL
from ci.pre_enquete_village
inner join ci.liste_site using(no_enquete) inner join ci.calcul_txa using(strate)
inner join ci.calcul_txc using(strate) inner join ci.v_liste_menage using (no_site)
inner join total using (no_site,no_menage) inner join calcul_txb using(no_site)
group by ci.pre_enquete_village.region_niv1
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 ci.pre_enquete_village.region_niv1 as ABSCISSE,'méthode 2' as GROUPE,sum(capture_meth2_c*txa*txb*txc)/1000 as TOTAL
from ci.pre_enquete_village
inner join ci.liste_site using(no_enquete) inner join ci.calcul_txa using(strate)
inner join ci.calcul_txc using(strate) inner join ci.v_liste_menage using (no_site)
inner join total using (no_site,no_menage) inner join calcul_txb using(no_site)
group by ci.pre_enquete_village.region_niv1
union

select distinct ci.pre_enquete_village.region_niv1 as ABSCISSE,'méthode 3' as GROUPE,sum(capture_meth3_c*txa*txb*txc)/1000 as TOTAL
from ci.pre_enquete_village
inner join ci.liste_site using(no_enquete) inner join ci.calcul_txa using(strate)
inner join ci.calcul_txc using(strate)
inner join ci.v_liste_menage using (no_site)
inner join total using (no_site,no_menage) inner join calcul_txb using(no_site)
group by ci.pre_enquete_village.region_niv1
order by ABSCISSE,GROUPE