#!/usr/bin/env python3
# -*- coding: UTF-8 -*-

import geopandas as gpd
from pycen import con_sicen

drop_v_saisie_ = "DROP VIEW IF EXISTS saisie.v_saisie_observation"
v_saisie_observation = """
CREATE OR REPLACE VIEW saisie.v_saisie_observation
AS WITH auteur AS (
    SELECT p_1.id_personne,
        CASE
            WHEN p_1.prenom ~~ '(%%'::text THEN p_1.nom
            ELSE COALESCE((p_1.nom || ' '::text) || p_1.prenom)
        END AS personne,
    s_1.nom_structure AS structure
    FROM md.personne p_1
        JOIN md.structure s_1 USING (id_structure)
)
SELECT s.id_obs,
    s.date_obs,
    s.date_debut_obs,
    s.date_fin_obs,
    s.date_textuelle,
    s.regne,
    s.nom_vern,
    s.nom_complet,
    s.cd_nom,
    t.cd_ref,
    s.effectif_textuel,
    s.effectif_min,
    s.effectif_max,
        CASE
            WHEN s.regne = 'Plantae'::text THEN s.type_effectif
            ELSE NULL::text
        END AS strate_flore,
        CASE
            WHEN s.regne = 'Plantae'::text THEN s.phenologie
            ELSE NULL::text
        END AS phenologie_flore,
        CASE
            WHEN s.regne = 'Animalia'::text THEN s.type_effectif
            ELSE NULL::text
        END AS age_faune,
        CASE
            WHEN s.regne = 'Animalia'::text THEN s.phenologie
            ELSE NULL::text
        END AS sexe_faune,
    s.id_waypoint,
    s.longitude,
    s.latitude,
    s.localisation,
    md.liste_nom_auteur(s.observateur::character varying) AS observateur,
    a1.personne AS numerisateur,
    a2.personne AS validateur,
    md.liste_nom_structure(s.structure::character varying) AS structure,
    s.remarque_obs,
    s.code_insee,
    s.id_lieu_dit,
    s.diffusable,
    s."precision",
    s.statut_validation,
    e.nom_etude AS etude,
    p.libelle AS protocole,
    s.effectif,
    s.url_photo,
    s.commentaire_photo,
    s.decision_validation,
    s.heure_obs,
    s.determination,
    s.elevation,
    s.geometrie,
    s.phylum,
    s.classe,
    s.ordre,
    s.famille,
    s.nom_valide,
    s.qualification,
    s.reprostatut AS reprostatut_faune,
    s.obs_null,
    s.uuid,
    ld.libelle AS lot_donnee,
    s.id_origine
FROM saisie.saisie_observation s
    LEFT JOIN auteur a1 ON a1.id_personne = s.numerisateur
    LEFT JOIN auteur a2 ON a2.id_personne = s.validateur
    LEFT JOIN md.etude e ON s.id_etude = e.id_etude
    LEFT JOIN md.protocole p ON s.id_protocole = p.id_protocole
    LEFT JOIN md.lot_donnee ld ON s.id_lot = ld.id_lot
    LEFT JOIN inpn.taxref t using (cd_nom)
;
"""

grant_v_saisie = """
ALTER TABLE saisie.v_saisie_observation OWNER TO cgeier;
GRANT ALL ON TABLE saisie.v_saisie_observation TO cgeier;
"""

with con_sicen.begin() as cnx:
    cnx.execute(drop_v_saisie_)
    cnx.execute(v_saisie_observation)
    cnx.execute(grant_v_saisie)


drop_vm_synthese = "DROP MATERIALIZED VIEW IF EXISTS saisie.vm_synthese_observations CASCADE;"
vm_synthese_observations = """
-- saisie.vm_synthese_observations source

CREATE MATERIALIZED VIEW saisie.vm_synthese_observations
TABLESPACE pg_default
AS WITH 
    observateurs AS (
        SELECT personne.id_personne,
            (personne.nom || ' '::text) || personne.prenom AS nom_complet,
            personne.role,
            personne.id_structure
        FROM md.personne
        ), 
    structures AS (
        SELECT structure.id_structure,
            structure.nom_structure,
            structure.diffusable
        FROM md.structure
        )
SELECT DISTINCT s.id_obs,
    e.nom_etude AS etude,
    p.libelle AS protocole,
    ld.libelle AS lot_donnee,
    s.date_obs,
    s.date_debut_obs,
    s.date_fin_obs,
    s.heure_obs,
    CASE
        WHEN tx.regne IS NOT NULL THEN tx.regne
        ELSE ta.regne
    END AS regne,
    --tx.phylum,
    CASE
        WHEN tx.phylum IS NOT NULL THEN tx.phylum
        ELSE ta.phylum
    END AS phylum,
    --tx.classe,
    CASE
        WHEN tx.classe IS NOT NULL THEN tx.classe
        ELSE ta.classe
    END AS classe,
    --tx.ordre,
    CASE
        WHEN tx.ordre IS NOT NULL THEN tx.ordre
        ELSE ta.ordre
    END AS ordre,
    tx.famille,
    CASE
        WHEN tx.group1_inpn IS NOT NULL THEN tx.group1_inpn
        ELSE ta.group1_inpn
    END AS group1_inpn,
    CASE
        WHEN tx.group2_inpn IS NOT NULL THEN tx.group2_inpn
        ELSE ta.group2_inpn
    END AS group2_inpn,
    CASE
        WHEN tx.lb_nom IS NOT NULL THEN tx.lb_nom
        ELSE ta.lb_nom
    END AS nom_latin,
    CASE
        WHEN tx.nom_vern IS NOT NULL THEN tx.nom_vern
        ELSE ta.lb_nom
    END AS nom_vern,
    CASE
        WHEN tx.nom_complet IS NOT NULL THEN tx.nom_complet
        ELSE ta.nom_complet
    END AS nom_complet,
    CASE
        WHEN tx.cd_nom IS NOT NULL THEN tx.cd_nom
        ELSE ta.cd_nom
    END AS cd_nom,
    CASE
        WHEN tx.cd_ref IS NOT NULL THEN tx.cd_ref
        ELSE ta.cd_ref
    END AS cd_ref,
    --s.cd_nom,
    --t.cd_ref,
    CASE
        WHEN s.obs_null IS TRUE THEN 'oui'::text
        ELSE 'non'::text
    END::character(3) AS absence_observation,
    s.effectif,
    s.effectif_min,
    s.effectif_max,
    s.effectif_textuel,
        CASE
            WHEN s.regne ~~ 'Plantae'::text THEN s.type_effectif
            ELSE NULL::text
        END::saisie.enum_type_effectif AS strate_flore,
        CASE
            WHEN s.regne ~~ 'Plantae'::text THEN s.phenologie
            ELSE NULL::text
        END::saisie.enum_phenologie AS phenologie_flore,
        CASE
            WHEN s.regne ~~ 'Animalia'::text THEN s.type_effectif
            ELSE NULL::text
        END::saisie.enum_age AS age_faune,
        CASE
            WHEN s.regne ~~ 'Animalia'::text THEN s.phenologie
            ELSE NULL::text
        END::saisie.enum_sexe AS sexe_faune,
    s.reprostatut AS reprostatut_faune,
    s.determination,
    s.remarque_obs AS rmq_observation,
    md.liste_nom_auteur(s.observateur::character varying) AS observateurs,
    md.liste_nom_auteur_structure(s.observateur::character varying) AS observateurs_v2,
    num.nom_complet AS numerisateur,
    md.liste_nom_structure(s.structure::character varying) AS structures,
        CASE
            WHEN s.diffusable IS TRUE THEN 'oui'::text
            ELSE 'non'::text
        END::character(3) AS diffusable,
    s.statut_validation,
    val.nom_complet AS validateur,
    s.decision_validation,
    s.code_insee AS insee_commune,
    c.nom AS nom_commune,
    l.nom AS lieu_dit,
    s.elevation AS altitude_z,
    s.longitude AS longitude_x,
    s.latitude AS latitude_y,
    s."precision",
    s.localisation AS rmq_localisation,
    s.id_origine,
    s.geometrie::geometry(Point,2154) AS geom
    FROM saisie.saisie_observation s
        LEFT JOIN inpn.taxref t ON s.cd_nom = t.cd_nom
        LEFT JOIN inpn.taxons_isere_absents_taxref ta ON s.cd_nom = ta.cd_nom::text
        LEFT JOIN ref_inpn_taxref.taxref_v11 tx ON s.cd_nom = tx.cd_nom::text
        JOIN md.etude e ON s.id_etude = e.id_etude
        LEFT JOIN md.protocole p ON s.id_protocole = p.id_protocole
        LEFT JOIN md.lot_donnee ld ON s.id_lot = ld.id_lot
        LEFT JOIN observateurs num ON s.numerisateur = num.id_personne
        LEFT JOIN observateurs val ON s.validateur = val.id_personne
        LEFT JOIN ign_bd_topo.commune c ON s.code_insee = c.code_insee
        LEFT JOIN ign_bd_topo.lieu_dit l ON s.id_lieu_dit = l.id
WITH DATA;

-- View indexes:
CREATE INDEX idx_vm_synthese_observations_date_debut_obs ON saisie.vm_synthese_observations USING btree (date_debut_obs);
CREATE INDEX idx_vm_synthese_observations_date_fin_obs ON saisie.vm_synthese_observations USING btree (date_fin_obs);
CREATE INDEX idx_vm_synthese_observations_date_obs ON saisie.vm_synthese_observations USING btree (date_obs);
CREATE INDEX idx_vm_synthese_observations_group1_inpn ON saisie.vm_synthese_observations USING btree (group1_inpn);
CREATE INDEX idx_vm_synthese_observations_group2_inpn ON saisie.vm_synthese_observations USING btree (group2_inpn);
CREATE UNIQUE INDEX idx_vm_synthese_observations_id_obs ON saisie.vm_synthese_observations USING btree (id_obs);
CREATE INDEX idx_vm_synthese_observations_nom_commune ON saisie.vm_synthese_observations USING btree (nom_commune);
CREATE INDEX idx_vm_synthese_observations_nom_latin ON saisie.vm_synthese_observations USING btree (nom_latin);
CREATE INDEX idx_vm_synthese_observations_observateurs ON saisie.vm_synthese_observations USING btree (observateurs);
CREATE INDEX idx_vm_synthese_observations_structures ON saisie.vm_synthese_observations USING btree (structures);
CREATE INDEX sidx_vm_synthese_observations_geom ON saisie.vm_synthese_observations USING gist (geom);


COMMENT ON MATERIALIZED VIEW saisie.vm_synthese_observations IS 'Vue matérialisée de synthèse des données d''observations issuent de la table "saisie.saise_observation" et reformattée pour faciliter son utilisation dans QGIS. La vue est rafraîchie toutes les heures avec un script PSQL executer en SHELL via une tâche planifiée CRON côté serveur. Cette vue matérialisée peut également être rafraîchi manuellement avec la commande : REFRESH MATERIALIZED VIEW CONCURRENTLY';
"""

grant_vm_synthese = """
-- Permissions

ALTER TABLE saisie.vm_synthese_observations OWNER TO admin_ra;
GRANT ALL ON TABLE saisie.vm_synthese_observations TO admin_ra;
GRANT SELECT ON TABLE saisie.vm_synthese_observations TO grp_consult;
GRANT ALL ON TABLE saisie.vm_synthese_observations TO grp_admin;
GRANT SELECT ON TABLE saisie.vm_synthese_observations TO sicen2_gr_consult;
"""
with con_sicen.begin() as cnx:
    cnx.execute(drop_vm_synthese)
    cnx.execute(vm_synthese_observations)
    cnx.execute(grant_vm_synthese)