-- mapping2
DROP VIEW v_noteitk0;
DROP VIEW v_noteitkretenue;
DROP VIEW v_noteitk ;
DROP VIEW v_notevariete;
DROP VIEW v_notetravailsuperf;
DROP VIEW v_notelabour;
DROP VIEW v_noteetouffculture;
DROP VIEW v_notedatesemis;
DROP VIEW v_notedensite;
DROP VIEW v_notedeshmeca;
DROP VIEW v_noteecart;
DROP VIEW v_notebineuse;
DROP VIEW v_notehoueherse;
DROP VIEW v_anneesinvalides;
DROP VIEW v_coeffdeshmeca;
DROP VIEW v_classfreqlabour;
DROP VIEW v_transdecdeshchim;
DROP VIEW v_transdecdeshmeca;
DROP VIEW v_efftravailsuperf;
DROP VIEW v_classfreqcultureshiver;
DROP VIEW v_nbitkcultureshiver;
DROP VIEW v_nbitklabour;
-- DROP Ovv vues
DROP VIEW ovv.v_scenarios_initiaux;
DROP VIEW ovv.v_scenarios_alternatifs;
DROP VIEW ovv.v_resultat_scenario_all;
DROP VIEW ovv.v_resultat_scenario;
DROP VIEW ovv.v_resultat_detaille_all;
DROP VIEW ovv.v_resultat_detaille;
DROP VIEW ovv.v_refmodalite;
DROP VIEW ovv.v_refcritere;
DROP VIEW ovv.v_ref_note;
DROP VIEW ovv.v_nb_alternatifs;

CREATE SEQUENCE ovv.critere_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE ovv.ass_refparam_refcritere_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE ovv.ref_adventice_idrefadventice_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE ovv.ref_critere_idrefcritere_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE ovv.ref_dependances_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE ovv.ref_modalite_idrefmodalite_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE ovv.ref_modalitecoef_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE ovv.ref_note_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE ovv.ref_parametrage_idrefparametrage_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE ovv.ref_region_id_ref_region_seq INCREMENT BY 1 MINVALUE 1 START 1;

ALTER TABLE ovv.critere ADD id INT NOT NULL DEFAULT nextval('ovv.critere_id_seq');
ALTER TABLE ovv.critere DROP CONSTRAINT critere_pkey;
ALTER TABLE ovv.critere ADD PRIMARY KEY (id);
-- Pourquoi commenter cette colonne SQL ?
-- COMMENT ON COLUMN ovv.critere.valeur IS NULL;
-- Pourquoi commenter cette colonne SQL ?
-- COMMENT ON COLUMN ovv.critere.lock IS NULL;
ALTER TABLE ovv.critere ADD CONSTRAINT FK_DEBFB3DB3EE91213 FOREIGN KEY (idrefcritere) REFERENCES ovv.ref_critere (idrefcritere) NOT DEFERRABLE INITIALLY IMMEDIATE;
CREATE INDEX IDX_DEBFB3DB3EE91213 ON ovv.critere (idrefcritere);

ALTER TABLE ovv.ass_refparam_refcritere ADD id INT NOT NULL DEFAULT nextval('ovv.ass_refparam_refcritere_id_seq');
ALTER TABLE ovv.ass_refparam_refcritere DROP CONSTRAINT ass_refparam_refcritere_pkey;
ALTER TABLE ovv.ass_refparam_refcritere ADD PRIMARY KEY (id);

-- Pourquoi commenter cette colonne SQL ?
--COMMENT ON COLUMN ovv.ref_critere.aide_image IS NULL;

ALTER TABLE ovv.ref_dependances ADD id INT NOT NULL DEFAULT nextval('ovv.ref_dependances_id_seq');
ALTER TABLE ovv.ref_dependances ADD PRIMARY KEY (id);
-- Pourquoi commenter ces colonnes SQL ?
-- COMMENT ON COLUMN ovv.ref_dependances.critere_source IS NULL;
-- COMMENT ON COLUMN ovv.ref_dependances.valeur_source IS NULL;
-- COMMENT ON COLUMN ovv.ref_dependances.critere_cible IS NULL;
-- COMMENT ON COLUMN ovv.ref_dependances.valeur_cible IS NULL;
-- COMMENT ON COLUMN ovv.ref_dependances.bloquant IS NULL;

ALTER TABLE ovv.ref_modalitecoef ADD id INT NOT NULL DEFAULT nextval('ovv.ref_modalitecoef_id_seq');
ALTER TABLE ovv.ref_modalitecoef ADD PRIMARY KEY (id);

ALTER TABLE ovv.ref_note ADD id INT NOT NULL DEFAULT nextval('ovv.ref_note_id_seq');
ALTER TABLE ovv.ref_note ADD PRIMARY KEY (id);
-- Pourquoi commenter cette colonne SQL ?
--COMMENT ON COLUMN ovv.ref_region.description IS NULL;

ALTER TABLE ovv.scenario ALTER idscenario DROP DEFAULT;
ALTER TABLE ovv.scenario ADD CONSTRAINT FK_DD8716F93D8E604F FOREIGN KEY (parent) REFERENCES ovv.scenario (idscenario) NOT DEFERRABLE INITIALLY IMMEDIATE;
CREATE INDEX IDX_DD8716F93D8E604F ON ovv.scenario (parent);

DROP TABLE ovv.tlogacces;
ALTER TABLE ovv.critere ALTER id DROP DEFAULT;
ALTER TABLE ovv.ass_refparam_refcritere ALTER id DROP DEFAULT;
ALTER TABLE ovv.ref_dependances ALTER id DROP DEFAULT;
ALTER TABLE ovv.ref_modalitecoef ALTER id DROP DEFAULT;
ALTER TABLE ovv.ref_note ALTER id DROP DEFAULT;

-- vues
CREATE VIEW v_anneesinvalides AS SELECT t_itk.iditk, t_scenario.idscenario, t_scenario.dureesuccession, t_itk.anneeculturale AS numordre FROM (t_scenario t_scenario LEFT JOIN t_itk ON ((t_itk.idscenario = t_scenario.idscenario))) WHERE (t_itk.anneeculturale > t_scenario.dureesuccession);

CREATE OR REPLACE VIEW v_nbitkcultureshiver AS
SELECT t_itk.idscenario, sum(
        CASE
            WHEN t_itk.decadesemis >= 22 OR (t_itk.decadesemis > 0 AND t_itk.decadesemis <= 3) THEN 1
            ELSE 0
        END) AS nbitkcultureshiver, count(*) AS nbitk
   FROM t_itk
   
  GROUP BY t_itk.idscenario;

CREATE VIEW v_classfreqcultureshiver AS SELECT v_nbitkcultureshiver.idscenario, CASE WHEN (((v_nbitkcultureshiver.nbitkcultureshiver)::double precision / (v_nbitkcultureshiver.nbitk)::double precision) = (1)::double precision) THEN 1 WHEN (((v_nbitkcultureshiver.nbitkcultureshiver)::double precision / (v_nbitkcultureshiver.nbitk)::double precision) >= (0.6)::double precision) THEN 2 WHEN (((v_nbitkcultureshiver.nbitkcultureshiver)::double precision / (v_nbitkcultureshiver.nbitk)::double precision) >= (0.3)::double precision) THEN 3 WHEN (((v_nbitkcultureshiver.nbitkcultureshiver)::double precision / (v_nbitkcultureshiver.nbitk)::double precision) > (0)::double precision) THEN 4 ELSE 5 END AS classfreqcultureshiver FROM v_nbitkcultureshiver;

CREATE VIEW v_nbitklabour AS SELECT t_itk.idscenario, sum(CASE WHEN (t_itk.decadelabour > 0) THEN 1 ELSE 0 END) AS nbitklabour, count(*) AS nbitk FROM t_itk GROUP BY t_itk.idscenario;

CREATE VIEW v_classfreqlabour AS SELECT v_nbitklabour.idscenario, CASE WHEN (((v_nbitklabour.nbitklabour)::double precision / (v_nbitklabour.nbitk)::double precision) = (1)::double precision) THEN 1 WHEN (((v_nbitklabour.nbitklabour)::double precision / (v_nbitklabour.nbitk)::double precision) >= (0.6)::double precision) THEN 2 WHEN (((v_nbitklabour.nbitklabour)::double precision / (v_nbitklabour.nbitk)::double precision) >= (0.3)::double precision) THEN 3 WHEN (((v_nbitklabour.nbitklabour)::double precision / (v_nbitklabour.nbitk)::double precision) > (0)::double precision) THEN 4 ELSE 5 END AS classfreqlabour FROM v_nbitklabour;

CREATE VIEW v_transdecdeshchim AS SELECT t_itk.iditk, t_itkdeshchim.iditkdeshchim, t_itkdeshchim.decadedeshchim, CASE WHEN (t_itkdeshchim.decadedeshchim < t_itk.decadesemis) THEN (t_itkdeshchim.decadedeshchim + 36) ELSE t_itkdeshchim.decadedeshchim END AS decadedeshchimtrans FROM (t_itk JOIN t_itkdeshchim ON ((t_itk.iditk = t_itkdeshchim.iditk)));

CREATE VIEW v_transdecdeshmeca AS SELECT t_itk.iditk, t_itk.idculture, t_itkdeshmeca.iditkdeshmeca, t_itkdeshmeca.idoutildeshmeca, t_itkdeshmeca.decadedeshmeca, CASE WHEN (t_itkdeshmeca.decadedeshmeca < t_itk.decadesemis) THEN (t_itkdeshmeca.decadedeshmeca + 36) ELSE t_itkdeshmeca.decadedeshmeca END AS decadedeshmecatrans FROM (t_itk JOIN t_itkdeshmeca ON ((t_itk.iditk = t_itkdeshmeca.iditk)));

CREATE VIEW v_coeffdeshmeca AS SELECT d.iditkdeshmeca, CASE WHEN (((d.decadeoperprec >= 25) AND (d.decadeoperprec <= 29)) AND (d.delaiopeprec <= 4)) THEN 1 WHEN (((d.decadeoperprec >= 30) OR (d.decadeoperprec <= 3)) AND (d.delaiopeprec <= 12)) THEN 1 WHEN (((d.decadeoperprec >= 4) AND (d.decadeoperprec <= 14)) AND (d.delaiopeprec <= 4)) THEN 1 WHEN (((d.decadeoperprec >= 15) AND (d.decadeoperprec <= 24)) AND (d.delaiopeprec <= 3)) THEN 1 ELSE 0 END AS coeffdeshmeca FROM (SELECT c.iditkdeshmeca, CASE WHEN ((c.decadedeshmecatrans - c.delaiopeprec) > 36) THEN ((c.decadedeshmecatrans - c.delaiopeprec) - 36) ELSE (c.decadedeshmecatrans - c.delaiopeprec) END AS decadeoperprec, c.delaiopeprec FROM (SELECT b.iditkdeshmeca, b.decadedeshmecatrans, LEAST((b.decadedeshmecatrans - b.decadesemis), (b.decadedeshmecatrans - b.decdeshmecaprec), (b.decadedeshmecatrans - b.decdeshchimprec)) AS delaiopeprec FROM (SELECT (SELECT max(v_transdecdeshmeca.decadedeshmecatrans) AS max FROM v_transdecdeshmeca WHERE ((v_transdecdeshmeca.decadedeshmecatrans < a.decadedeshmecatrans) AND (v_transdecdeshmeca.iditk = a.iditk))) AS decdeshmecaprec, (SELECT max(v_transdecdeshchim.decadedeshchimtrans) AS max FROM v_transdecdeshchim WHERE ((v_transdecdeshchim.decadedeshchimtrans < a.decadedeshmecatrans) AND (v_transdecdeshchim.iditk = a.iditk))) AS decdeshchimprec, i.decadesemis, a.decadedeshmecatrans, a.iditkdeshmeca FROM (t_itk i JOIN v_transdecdeshmeca a ON ((i.iditk = a.iditk))) WHERE ((a.idoutildeshmeca = 'e'::bpchar) OR (a.idoutildeshmeca = 'r'::bpchar))) b) c) d;

CREATE VIEW v_efftravailsuperf AS SELECT t_itktravsuperf.iditk, sum(t_notetravailsuperf.notetravailsuperf) AS efftravailsuperf, t_notetravailsuperf.idadventice FROM (t_itktravsuperf JOIN t_refnotetravailsuperf t_notetravailsuperf ON ((t_itktravsuperf.decadetravsuperf = t_notetravailsuperf.numdecade))) GROUP BY t_itktravsuperf.iditk, t_notetravailsuperf.idadventice;

CREATE VIEW v_notebineuse AS SELECT v_transdecdeshmeca.iditk, v_transdecdeshmeca.iditkdeshmeca, t_notetravailsuperf.idadventice, CASE WHEN (v_transdecdeshmeca.decadedeshmecatrans > t_reflimutildeshmeca.decademax) THEN (0)::double precision ELSE t_notetravailsuperf.notetravailsuperf END AS notebineuse FROM (t_reflimutildeshmeca JOIN (v_transdecdeshmeca JOIN t_refnotetravailsuperf t_notetravailsuperf ON ((v_transdecdeshmeca.decadedeshmeca = t_notetravailsuperf.numdecade))) ON (((t_reflimutildeshmeca.idoutildeshmeca = v_transdecdeshmeca.idoutildeshmeca) AND (t_reflimutildeshmeca.idculture = v_transdecdeshmeca.idculture)))) WHERE (v_transdecdeshmeca.idoutildeshmeca = 'b'::bpchar);

CREATE OR REPLACE VIEW public.v_notedatesemis AS 
SELECT t_itk.iditk,
    t_notedatesemis.notedatesemis,
    t_notedatesemis.idadventice
   FROM t_itk 
   JOIN t_adventiceetudie _adv ON t_itk.idscenario = _adv.idscenario 
   LEFT JOIN t_refnotedatesemis t_notedatesemis ON t_itk.decadesemis = t_notedatesemis.numdecade;

CREATE VIEW v_notedensite AS SELECT t_itk.iditk, CASE WHEN (t_itk.densitesemis > t_refcultures.seuildensiteelevee) THEN t_refcultures.notedensiteelevee ELSE (0)::double precision END AS notedensite FROM (t_refculture t_refcultures JOIN t_itk ON ((t_refcultures.idculture = t_itk.idculture)));

CREATE VIEW v_notehoueherse AS SELECT v_transdecdeshmeca.iditk, v_transdecdeshmeca.iditkdeshmeca, t_notetravailsuperf.idadventice, CASE WHEN (v_transdecdeshmeca.decadedeshmecatrans > t_reflimutildeshmeca.decademax) THEN (0)::double precision ELSE (t_notetravailsuperf.notetravailsuperf * (v_coeffdeshmeca.coeffdeshmeca)::double precision) END AS notehoueherse FROM (((v_transdecdeshmeca JOIN v_coeffdeshmeca ON ((v_transdecdeshmeca.iditkdeshmeca = v_coeffdeshmeca.iditkdeshmeca))) JOIN t_reflimutildeshmeca ON (((v_transdecdeshmeca.idculture = t_reflimutildeshmeca.idculture) AND (v_transdecdeshmeca.idoutildeshmeca = t_reflimutildeshmeca.idoutildeshmeca)))) JOIN t_refnotetravailsuperf t_notetravailsuperf ON ((v_transdecdeshmeca.decadedeshmeca = t_notetravailsuperf.numdecade)));

CREATE OR REPLACE VIEW public.v_notedeshmeca AS 
SELECT t_itk.iditk,
    _adv.idadventice,
    COALESCE(sum(a.note),0) AS notedeshmeca
   FROM t_itk 
   JOIN t_adventiceetudie _adv ON t_itk.idscenario = _adv.idscenario 
   LEFT JOIN ( SELECT v_notebineuse.iditk,
            v_notebineuse.iditkdeshmeca,
            v_notebineuse.idadventice,
            v_notebineuse.notebineuse AS note
           FROM v_notebineuse
        UNION
         SELECT v_notehoueherse.iditk,
            v_notehoueherse.iditkdeshmeca,
            v_notehoueherse.idadventice,
            v_notehoueherse.notehoueherse
           FROM v_notehoueherse) a ON t_itk.iditk = a.iditk AND _adv.idadventice = a.idadventice 
  GROUP BY t_itk.iditk, _adv.idadventice;

CREATE VIEW v_noteecart AS SELECT t_itk.iditk, CASE WHEN ((t_itk.ecartinterrangs < t_refcultures.seuilecartreduiteffmin) AND (t_itk.ecartinterrangs > t_refcultures.seuilecartreduiteffmax)) THEN ((t_refcultures.seuilecartreduiteffmin - t_itk.ecartinterrangs) * (t_refcultures.noteecartreduitmax / (t_refcultures.seuilecartreduiteffmin - t_refcultures.seuilecartreduiteffmax))) WHEN (t_itk.ecartinterrangs <= t_refcultures.seuilecartreduiteffmax) THEN t_refcultures.noteecartreduitmax ELSE (0)::double precision END AS noteecart FROM (t_refculture t_refcultures JOIN t_itk ON ((t_refcultures.idculture = t_itk.idculture)));

CREATE OR REPLACE VIEW v_noteetouffculture AS 
SELECT _itk.iditk, _etouff.noteetouffement::double precision AS noteetouffculture 
FROM t_itk _itk
LEFT JOIN t_refetouffementculture _etouff ON _itk.idetouffement = _etouff.idetouffement;

CREATE OR REPLACE VIEW public.v_notelabour AS 
SELECT t_itk.iditk,
    COALESCE(t_notelabour.notelabour,0) AS notelabour,
    _adv.idadventice
   FROM t_itk 
   JOIN t_adventiceetudie _adv ON t_itk.idscenario = _adv.idscenario 
     JOIN t_refnotelabour t_notelabour ON t_itk.decadelabour = t_notelabour.numdecade AND _adv.idadventice = t_notelabour.idadventice 
     JOIN v_classfreqcultureshiver ON v_classfreqcultureshiver.classfreqcultureshiver = t_notelabour.classefreqculthiver AND t_itk.idscenario = v_classfreqcultureshiver.idscenario
     JOIN v_classfreqlabour ON v_classfreqlabour.classfreqlabour = t_notelabour.classefreqlabour AND t_itk.idscenario = v_classfreqlabour.idscenario;

CREATE OR REPLACE VIEW public.v_notetravailsuperf AS 
SELECT t_itk.iditk,
    COALESCE(sum(t_notetravailsuperf.notetravailsuperf), 0) AS notetravailsuperf,
    _adv.idadventice
   FROM t_itk 
   JOIN t_adventiceetudie _adv ON t_itk.idscenario = _adv.idscenario 
   LEFT JOIN t_itktravsuperf ON t_itk.iditk = t_itktravsuperf.iditk 
     LEFT JOIN t_refnotetravailsuperf t_notetravailsuperf ON t_itktravsuperf.decadetravsuperf = t_notetravailsuperf.numdecade AND _adv.idadventice = t_notetravailsuperf.idadventice 
  GROUP BY t_itk.iditk, _adv.idadventice;

CREATE VIEW v_notevariete AS SELECT t_itk.iditk, CASE WHEN ((t_refcultures.possvarieteadapt = true) AND (t_itk.idvariete IS NOT NULL)) THEN t_varietes.noteetouff ELSE 0 END AS notevariete FROM ((t_itk LEFT JOIN t_refvariete t_varietes ON ((t_itk.idvariete = t_varietes.idvariete))) LEFT JOIN t_refculture t_refcultures ON ((t_itk.idculture = t_refcultures.idculture))) ORDER BY t_itk.iditk;

CREATE OR REPLACE VIEW public.v_noteitk AS 
SELECT a.iditk,
    a.idadventice,
    COALESCE(v_notetravailsuperf.notetravailsuperf, 0::double precision) + COALESCE(v_notelabour.notelabour, 0::double precision) + COALESCE(v_notedeshmeca.notedeshmeca, 0::double precision) + COALESCE(v_notedatesemis.notedatesemis, 0::double precision) + COALESCE(b.sommeetouff, 0::double precision) AS noteitk
   FROM ( SELECT t_itk.iditk,
            _adv.idadventice
           FROM t_itk 
		JOIN t_adventiceetudie _adv ON t_itk.idscenario = _adv.idscenario) a
     LEFT JOIN v_notedeshmeca ON a.idadventice = v_notedeshmeca.idadventice AND a.iditk = v_notedeshmeca.iditk
     LEFT JOIN v_notelabour ON a.idadventice = v_notelabour.idadventice AND a.iditk = v_notelabour.iditk
     LEFT JOIN v_notetravailsuperf ON a.idadventice = v_notetravailsuperf.idadventice AND a.iditk = v_notetravailsuperf.iditk
     LEFT JOIN v_notedatesemis ON a.iditk = v_notedatesemis.iditk AND a.idadventice = v_notedatesemis.idadventice
     LEFT JOIN ( SELECT t_itk.iditk,
            v_notevariete.notevariete::double precision + v_notedensite.notedensite + v_noteecart.noteecart + v_noteetouffculture.noteetouffculture AS sommeetouff
           FROM t_itk
             JOIN v_notedensite ON t_itk.iditk = v_notedensite.iditk
             JOIN v_noteecart ON t_itk.iditk = v_noteecart.iditk
             JOIN v_noteetouffculture ON t_itk.iditk = v_noteetouffculture.iditk
             JOIN v_notevariete ON t_itk.iditk = v_notevariete.iditk) b ON a.iditk = b.iditk;

CREATE VIEW v_noteitk0 AS SELECT v_noteitk.iditk, v_noteitk.idadventice, CASE WHEN (v_noteitk.noteitk < (0)::double precision) THEN (0)::double precision ELSE v_noteitk.noteitk END AS noteitk0 FROM v_noteitk;

CREATE VIEW v_noteitkretenue AS SELECT v_noteitk.iditk, v_noteitk.idadventice, CASE WHEN (v_noteitk.noteitk < (0)::double precision) THEN (0)::double precision ELSE (v_noteitk.noteitk * t_refadventice.coeffnote) END AS noteitkretenue FROM (v_noteitk JOIN t_refadventice t_refadventice ON ((v_noteitk.idadventice = t_refadventice.idadventice)));

--Vues OVV
CREATE OR REPLACE VIEW ovv.v_nb_alternatifs AS SELECT scenario.idscenario,  COALESCE(sc.nb_alternatifs, 0::bigint) AS nb_alternatifs FROM ovv.scenario  LEFT JOIN ( SELECT scenario_1.parent AS idscenario,  count(scenario_1.idscenario) AS nb_alternatifs FROM ovv.scenario scenario_1  WHERE scenario_1.parent IS NOT NULL  GROUP BY scenario_1.parent) sc ON sc.idscenario = scenario.idscenario WHERE scenario.parent IS NULL ORDER BY scenario.idscenario;

CREATE OR REPLACE VIEW ovv.v_ref_note AS SELECT sc1.idparametrage, CASE WHEN sc1.idparametrage IS DISTINCT FROM sc1.prev_idparametrage THEN 0::double precision ELSE sc1.score_min  END AS score_min, sc1.score_max, sc1.note FROM ( SELECT sc2.idparametrage, lag(sc2.score_max) OVER (ORDER BY sc2.idparametrage, sc2.score_max) AS score_min, lag(sc2.idparametrage) OVER (ORDER BY sc2.idparametrage, sc2.score_max) AS prev_idparametrage, sc2.score_max,  sc2.note FROM ovv.ref_note sc2 ORDER BY sc2.idparametrage, sc2.score_max) sc1;

CREATE OR REPLACE VIEW ovv.v_refcritere AS SELECT ref_critere.idrefcritere, ref_critere.type, ref_critere.nom, ref_critere.aide, ref_critere.aide_image,scenario.idscenario FROM ovv.scenario  LEFT JOIN ovv.ass_refparam_refcritere ON scenario.idrefparametrage = ass_refparam_refcritere.idrefparametrage LEFT JOIN ovv.ref_critere ON ass_refparam_refcritere.idrefcritere = ref_critere.idrefcritere ORDER BY ass_refparam_refcritere.ordre;

CREATE OR REPLACE VIEW ovv.v_refmodalite AS SELECT ref_critere.idrefcritere, ref_modalite.idrefmodalite, ref_modalite.nom, ref_modalite.ordre FROM ovv.ref_modalite LEFT JOIN ovv.ref_critere ON ref_modalite.idrefcritere = ref_critere.idrefcritere ORDER BY ref_critere.idrefcritere, ref_modalite.ordre;

CREATE OR REPLACE VIEW ovv.v_resultat_detaille AS SELECT v_refcritere.idscenario, v_refcritere.idrefcritere, v_refcritere.nom AS nom_critere, critere.idrefmodalite, ref_modalite.nom AS nom_modalite, critere.valeur, ass_refparam_refcritere.poids, CASE WHEN v_refcritere.type::text = 'select'::text AND ref_modalite.valeur IS NOT NULL THEN ref_modalite.valeur * ass_refparam_refcritere.poids WHEN v_refcritere.type::text = 'bool'::text THEN critere.valeur * ass_refparam_refcritere.poids ELSE NULL::double precision  END AS score, v_refcritere.type, critere.valeur AS valeursaisie, critere.lock, scenario.parent FROM ovv.v_refcritere LEFT JOIN ovv.scenario ON scenario.idscenario = v_refcritere.idscenario LEFT JOIN ovv.critere ON v_refcritere.idrefcritere = critere.idrefcritere AND v_refcritere.idscenario = critere.idscenario LEFT JOIN ovv.ref_modalite ON ref_modalite.idrefmodalite = critere.idrefmodalite AND ref_modalite.idrefcritere = v_refcritere.idrefcritere LEFT JOIN ovv.ass_refparam_refcritere ON ass_refparam_refcritere.idrefparametrage = scenario.idrefparametrage AND ass_refparam_refcritere.idrefcritere = critere.idrefcritere ORDER BY v_refcritere.idscenario, v_refcritere.idrefcritere;

CREATE OR REPLACE VIEW ovv.v_resultat_detaille_all AS SELECT sc.idscenario, sc.idrefcritere, sc.nom_critere, sc.idrefmodalite, sc.nom_modalite, sc.valeur, sc.poids, sc.score, sc.type, sc.valeursaisie, sc.lock, sc.parent, scparent.nom_modalite AS nom_modalite_parent, CASE  WHEN sc.score > scparent.score THEN 1 WHEN sc.score < scparent.score THEN (-1)  WHEN sc.score = scparent.score THEN 0  ELSE NULL::integer  END AS evolution_score,  scparent.valeur AS valeur_parent  FROM ovv.v_resultat_detaille sc  LEFT JOIN ovv.v_resultat_detaille scparent ON scparent.idscenario = sc.parent AND scparent.idrefcritere = sc.idrefcritere;

CREATE OR REPLACE VIEW ovv.v_resultat_scenario AS SELECT sc_score.idscenario, sc_score.score,  v_ref_note.note, sc_score.parent FROM ( SELECT v_resultat_detaille.idscenario, v_resultat_detaille.parent, bool_or(v_resultat_detaille.score IS NULL) AS contient_null, CASE  WHEN bool_or(v_resultat_detaille.score IS NULL) THEN NULL::double precision ELSE sum(v_resultat_detaille.score)  END AS score FROM ovv.v_resultat_detaille  GROUP BY v_resultat_detaille.idscenario, v_resultat_detaille.parent) sc_score  LEFT JOIN ovv.v_ref_note ON sc_score.score >= v_ref_note.score_min AND sc_score.score < v_ref_note.score_max;

CREATE OR REPLACE VIEW ovv.v_resultat_scenario_all AS  SELECT sc.idscenario, sc.score, sc.note, sc.parent,  scparent.note AS note_parent, CASE  WHEN sc.note > scparent.note THEN 1  WHEN sc.note < scparent.note THEN (-1)  WHEN sc.note = scparent.note THEN 0 ELSE NULL::integer  END AS evolution_note FROM ovv.v_resultat_scenario sc LEFT JOIN ovv.v_resultat_scenario scparent ON scparent.idscenario = sc.parent;

CREATE OR REPLACE VIEW ovv.v_scenarios_alternatifs AS  SELECT scenario.idscenario, CASE  WHEN scenario.parent IS NULL THEN scenario.idscenario ELSE scenario.parent  END AS scenario_initial,  scenario.nom, scenario.parent, scenario.nomexploitation, scenario.nomparcelle,scenario.typesol, scenario.idrefparametrage, scenario.idutilisateur, ref_adventice.nom AS nomadventice,  ref_region.nom AS nomregion, v_resultat_scenario.note FROM ovv.scenario  LEFT JOIN ovv.ref_parametrage ON scenario.idrefparametrage = ref_parametrage.idrefparametrage LEFT JOIN ovv.ref_region ON ref_region.id_ref_region = ref_parametrage.idrefregion LEFT JOIN ovv.ref_adventice ON ref_adventice.idrefadventice = ref_parametrage.idrefadventice LEFT JOIN ovv.v_resultat_scenario ON v_resultat_scenario.idscenario = scenario.idscenario ORDER BY scenario.idscenario;

CREATE OR REPLACE VIEW ovv.v_scenarios_initiaux AS SELECT scenario.idscenario, scenario.nom, scenario.parent,  scenario.nomexploitation,  scenario.nomparcelle, scenario.typesol, scenario.idrefparametrage, scenario.idutilisateur, ref_adventice.nom AS nomadventice, ref_region.nom AS nomregion, v_nb_alternatifs.nb_alternatifs, v_resultat_scenario.note FROM ovv.scenario LEFT JOIN ovv.ref_parametrage ON scenario.idrefparametrage = ref_parametrage.idrefparametrage LEFT JOIN ovv.ref_region ON ref_region.id_ref_region = ref_parametrage.idrefregion  LEFT JOIN ovv.ref_adventice ON ref_adventice.idrefadventice = ref_parametrage.idrefadventice  LEFT JOIN ovv.v_nb_alternatifs ON v_nb_alternatifs.idscenario = scenario.idscenario LEFT JOIN ovv.v_resultat_scenario ON v_resultat_scenario.idscenario = scenario.idscenario WHERE scenario.parent IS NULL ORDER BY scenario.idscenario;

-- tâche 1571: Interculture : prise en compte de l'efficacité dans les vues de calculs
CREATE OR REPLACE VIEW public.v_notetravailsuperf AS
 SELECT t_itk.iditk,
    COALESCE(sum(t_notetravailsuperf.notetravailsuperf / 0.5 * t_itktravsuperf.coeffefficacitetravsuperf), 0::double precision) AS notetravailsuperf,
    _adv.idadventice
   FROM t_itk
     JOIN t_adventiceetudie _adv ON t_itk.idscenario = _adv.idscenario
     LEFT JOIN t_itktravsuperf ON t_itk.iditk = t_itktravsuperf.iditk
     LEFT JOIN t_refnotetravailsuperf t_notetravailsuperf ON t_itktravsuperf.decadetravsuperf = t_notetravailsuperf.numdecade AND _adv.idadventice = t_notetravailsuperf.idadventice
  GROUP BY t_itk.iditk, _adv.idadventice;