CREATE TABLE IF NOT EXISTS ref_etapeacv
(
    code    varchar(255) NOT NULL,
    libelle varchar(255) NULL,
    CONSTRAINT ref_etapeacv_pkey PRIMARY KEY (code)
);

CREATE TABLE IF NOT EXISTS ref_critere
(
    nom_critere varchar(255) NOT NULL,
    description varchar(255) NULL,
    unite       varchar(255) NULL,
    CONSTRAINT ref_critere_pkey PRIMARY KEY (nom_critere)
);

CREATE TABLE IF NOT EXISTS ref_hypothese
(
    code     varchar(255) NOT NULL,
    "source" varchar(255) NULL,
    valeur   varchar(255) NULL,
    description   varchar(255) NULL,
    CONSTRAINT ref_hypothese_pkey PRIMARY KEY (code)
);

CREATE TABLE IF NOT EXISTS ref_type_equipement
(
    "type"                    varchar(255) NOT NULL,
    commentaire               varchar(255) NULL,
    duree_vie_defaut          float8       NULL,
    serveur                   bool         NOT NULL,
    "source"                  varchar(255) NULL,
    ref_equipement_par_defaut varchar(255) NULL,
    CONSTRAINT ref_type_equipement_pkey PRIMARY KEY (type)
);

CREATE TABLE IF NOT EXISTS ref_type_item
(
    "type"                    varchar(255) NOT NULL,
    categorie                 varchar(255) NULL,
    commentaire               varchar(255) NULL,
    duree_vie_defaut          float8       NULL,
    ref_conso_moyenne         varchar(255) NULL,
    serveur                   bool         NOT NULL,
    "source"                  varchar(255) NULL,
    ref_item_par_defaut       varchar(255) NULL,
    CONSTRAINT ref_type_item_pkey PRIMARY KEY (type)
);

CREATE TABLE IF NOT EXISTS ref_impact_messagerie
(
    constante_coefficient_directeur float8       NULL,
    constante_ordonnee_origine      float8       NULL,
    "source"                        varchar(255) NULL,
    nom_critere                     varchar(255) NOT NULL,
    CONSTRAINT ref_impact_messagerie_pkey PRIMARY KEY (nom_critere)
);

CREATE TABLE IF NOT EXISTS ref_correspondance_ref_eqp
(
    modele_equipement_source varchar(255) NOT NULL,
    ref_equipement_cible     varchar(255) NULL,
    CONSTRAINT ref_correspondance_ref_eqp_pkey PRIMARY KEY (modele_equipement_source)
);

CREATE TABLE IF NOT EXISTS ref_impactequipement
(
    refequipement      varchar(255) NOT NULL,
    conso_elec_moyenne float8       NULL,
    "source"           varchar(255) NULL,
    "type"             varchar(255) NULL,
    valeur             float8       NULL,
    etapeacv           varchar(255) NOT NULL,
    nomcritere         varchar(255) NOT NULL,
    description        varchar(255) NULL,
    CONSTRAINT ref_impactequipement_pkey PRIMARY KEY (nomcritere, etapeacv, refequipement)
);

CREATE TABLE IF NOT EXISTS ref_impactreseau
(
    refreseau        varchar(255) NOT NULL,
    consoelecmoyenne float8       NULL,
    "source"         varchar(255) NULL,
    valeur           float8       NULL,
    etapeacv         varchar(255) NOT NULL,
    nomcritere       varchar(255) NOT NULL,
    CONSTRAINT ref_impactreseau_pkey PRIMARY KEY (nomcritere, etapeacv, refreseau)
);

CREATE TABLE IF NOT EXISTS ref_mixelec
(
    pays              varchar(255) NOT NULL,
    raccourcisanglais varchar(255) NULL,
    "source"          varchar(255) NULL,
    valeur            float8       NULL,
    nomcritere        varchar(255) NOT NULL,
    CONSTRAINT ref_mixelec_pkey PRIMARY KEY (nomcritere, pays)
);
    CREATE TABLE IF NOT EXISTS ref_facteurcaracterisation
(
     nom      varchar(255) NOT NULL,
     etapeacv           varchar(255) NOT NULL,
     nomcritere         varchar(255) NOT NULL,
     description        varchar(255) NULL,
     niveau             varchar(255) NULL,
     tiers              varchar(255) NULL,
     categorie          varchar(255) NULL,
     conso_elec_moyenne float8       NULL,
     localisation       varchar(255) NULL,
     valeur             float8       NULL,
     unite              varchar(255) NULL,
     source             varchar(255) NULL,
     CONSTRAINT ref_facteurcaracterisation_pkey PRIMARY KEY (nom, etapeacv, nomcritere)
);
-- suppression des contraintes de clés étrangères
ALTER TABLE ref_impactequipement DROP CONSTRAINT IF EXISTS fk5iuiwnk7rymtob1fku71uuj52;
ALTER TABLE ref_impactequipement DROP CONSTRAINT IF EXISTS fksfjum8kagn7q6vsv5uqn6kimx;
ALTER TABLE ref_impactreseau DROP CONSTRAINT IF EXISTS fk31ykp7xtj41win3ptqlr3us9s;
ALTER TABLE ref_impactreseau DROP CONSTRAINT IF EXISTS fkb8tkreu8c8s8pqqnft6vr4pnf;
ALTER TABLE ref_mixelec DROP CONSTRAINT IF EXISTS fkdncd4m2je6fbno7pkn850u1fs;
ALTER TABLE ref_impact_messagerie DROP CONSTRAINT IF EXISTS fkohnlpwfp0ebk7dswmfbe5l3k0;
