/**
 * @author AMR
 * @date 09/08/21
 * @version 1.0.0
 * @project SIEau
 * @table public.intervenants
 */

# --- !Ups

drop VIEW if exists  public.gestion_reseau;
drop VIEW public.ln_intervenant;
ALTER TABLE intervenants ALTER COLUMN commentaires TYPE text USING commentaires::text;
CREATE OR REPLACE VIEW public.ln_intervenant
AS SELECT intervenants.codeintervenant AS code_interv,
    '1' AS origine_interv,
    intervenants.nom,
    intervenants.statut,
    '' AS activite,
    intervenants.datecreation AS date_creation,
    intervenants.datemiseajour AS date_maj,
    intervenants.auteur,
    intervenants.mnemonique AS mnemo,
    intervenants.boitelettre AS bal,
    intervenants.complementadresse AS immeuble,
    intervenants.rue,
    intervenants.lieudit AS lieu_dit,
    intervenants.departement AS code_postal,
    intervenants.ville,
    '' AS region,
    intervenants.commentaires AS commentaire
   FROM intervenants;

CREATE OR REPLACE VIEW public.gestion_reseau
AS SELECT pz_gestionnaire.code_bss,
    ln_intervenant.nom AS gestionnaire,
    ln_reseau.libelle AS reseau
   FROM ln_reseau
     JOIN (ln_intervenant
	     JOIN (pz_gestionnaire
	     	JOIN pz_reseau ON pz_gestionnaire.code_bss::text = pz_reseau.code_bss::text )
     	ON ln_intervenant.origine_interv::text = pz_gestionnaire.origine_interv::text AND ln_intervenant.code_interv = pz_gestionnaire.code_interv)
     ON ln_reseau.code_reseau = pz_reseau.code_reseau
  GROUP BY pz_gestionnaire.code_bss, ln_intervenant.nom, ln_reseau.libelle;

# --- !Downs

drop VIEW if exists  public.gestion_reseau;
drop VIEW public.ln_intervenant;
ALTER TABLE intervenants ALTER COLUMN commentaires TYPE VARCHAR(255) USING commentaires::VARCHAR;
CREATE OR REPLACE VIEW public.ln_intervenant
AS SELECT intervenants.codeintervenant AS code_interv,
    '1' AS origine_interv,
    intervenants.nom,
    intervenants.statut,
    '' AS activite,
    intervenants.datecreation AS date_creation,
    intervenants.datemiseajour AS date_maj,
    intervenants.auteur,
    intervenants.mnemonique AS mnemo,
    intervenants.boitelettre AS bal,
    intervenants.complementadresse AS immeuble,
    intervenants.rue,
    intervenants.lieudit AS lieu_dit,
    intervenants.departement AS code_postal,
    intervenants.ville,
    '' AS region,
    intervenants.commentaires AS commentaire
   FROM intervenants;

CREATE OR REPLACE VIEW public.gestion_reseau
AS SELECT pz_gestionnaire.code_bss,
    ln_intervenant.nom AS gestionnaire,
    ln_reseau.libelle AS reseau
   FROM ln_reseau
     JOIN (ln_intervenant
	     JOIN (pz_gestionnaire
	     	JOIN pz_reseau ON pz_gestionnaire.code_bss::text = pz_reseau.code_bss::text )
     	ON ln_intervenant.origine_interv::text = pz_gestionnaire.origine_interv::text AND ln_intervenant.code_interv = pz_gestionnaire.code_interv)
     ON ln_reseau.code_reseau = pz_reseau.code_reseau
  GROUP BY pz_gestionnaire.code_bss, ln_intervenant.nom, ln_reseau.libelle;
