DELETE FROM agent WHERE email = 'soizic.malin@pgquadogeo.fr';

-- ****************************************************************************
-- contraindre la saisie grâce à un check simple
-- ****************************************************************************

SELECT email, nom, prenom, genre FROM agent;

ALTER TABLE agent ADD CONSTRAINT agent_genre CHECK (genre IN ('M', 'F'));

-- En échec : INSERT INTO agent (email, id_poste, nom, prenom, genre, date_naissance) values ('soizic.malin@pgquadogeo.fr', 2607, 'MALIN', 'SOIZIC', 'O', '1981-12-07');
-- ERREUR:  la nouvelle ligne de la relation « agent » viole la contrainte de vérification « agent_genre »

-- ****************************************************************************
-- contraindre la saisie grâce à un check avec fonction
-- ****************************************************************************

ALTER TABLE agent ADD CONSTRAINT agent_date_naissance
CHECK (age(now(), date_naissance) between '7 years'::interval AND '77 years'::interval);

-- En échec : INSERT INTO agent (email, id_poste, nom, prenom, genre, date_naissance) values ('soizic.malin@pgquadogeo.fr', 2607, 'MALIN', 'SOIZIC', 'F', '1881-12-07');
-- ERREUR:  la nouvelle ligne de la relation « agent » viole la contrainte de vérification « agent_date_naissance »

-- ****************************************************************************
-- contraindre la saisie grâce à un check inter-champs
-- ****************************************************************************

-- En échec : ALTER TABLE commune ADD CONSTRAINT coherence_cog_cog_departement CHECK (substring(cog FOR 2) = cog_departement);
-- ERREUR:  la contrainte de vérification « coherence_cog_cog_departement » de la relation « commune » est violée par une ligne

SELECT * FROM commune WHERE cog = '30228';

UPDATE commune SET cog_departement = '30' WHERE cog = '30228';

ALTER TABLE commune ADD CONSTRAINT coherence_cog_cog_departement CHECK (substring(cog FOR 2) = cog_departement);

-- En échec : INSERT INTO commune (cog, cog_departement, code_postal, nom) values ('34145', '30', '34400', 'Lunel');
-- ERREUR:  la nouvelle ligne de la relation « commune » viole la contrainte de vérification « coherence_cog_cog_departement »

-- ****************************************************************************
-- contraindre la saisie en utilisant un domaine
-- ****************************************************************************

SELECT email, nom, prenom, genre FROM agent;

CREATE DOMAIN email AS varchar
CONSTRAINT format_email CHECK (VALUE ~ '(?:[a-z0-9!#$%&\''*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&''*+/=?^_`{|}~-]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9]))\.){3}(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9])|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])');
-- ' <-- pour contourner le problème de coloration syntaxique sur Notepad++
ALTER TABLE agent ALTER COLUMN email SET DATA TYPE email;

-- En échec : INSERT INTO agent (email, id_poste, nom, prenom, genre, date_naissance) values ('soizic.malin-at-pgquadogeo.fr', 2607, 'MALIN', 'SOIZIC', 'O', '1981-12-07');
-- ERREUR:  la valeur pour le domaine email viole la contrainte de vérification « format_email »

-- ****************************************************************************
-- contraindre la saisie en utilisant un type enum
-- ****************************************************************************

SELECT email, type_emploi FROM agent;

-- On crée un type énumération type_emploi.
CREATE TYPE type_emploi AS ENUM ('t', 'c');

-- On crée une fonction de conversion varchar vers type_emploi.
CREATE FUNCTION varchar_2_type_emploi(v varchar) RETURNS type_emploi AS
$$
BEGIN
    IF v IN ('titulaire', 'titu', 't') THEN RETURN 't'::type_emploi;
    ELSIF v IN ('contractuel', 'cont', 'c') THEN RETURN 'c'::type_emploi;
    ELSE RAISE EXCEPTION 'valeur en entrée invalide pour le type enum type_emploi : (%)', v;
    END IF;
END
$$ LANGUAGE plpgsql;

-- On ajoute la possibilité de "caster" automatique un varchar en type_emploi.
CREATE CAST (varchar AS type_emploi) WITH FUNCTION varchar_2_type_emploi(varchar) AS IMPLICIT;

-- On modifie le type de la colonne type_emploi, cette modification s'appuie sur le "cast" implicite.
ALTER TABLE agent ALTER COLUMN type_emploi SET DATA TYPE type_emploi;

-- En échec : INSERT INTO agent (email, id_poste, nom, prenom, genre, date_naissance, type_emploi) values ('soizic.malin@pgquadogeo.fr', 2607, 'MALIN', 'SOIZIC', 'F', '1981-12-07', 'non titulaire'::varchar);
-- ERREUR:  valeur en entrée invalide pour le type enum type_emploi : (non titulaire)

-- ****************************************************************************
-- contraindre la saisie par décomposition
-- ****************************************************************************

SELECT * FROM poste;

-- On crée une table emploi avec un auto-incrément en tant que clé primaire.
CREATE TABLE emploi (
    id serial NOT NULL,
    nom varchar NOT NULL,
    
    CONSTRAINT pk_emploi PRIMARY KEY (id)
);

-- On insère les emplois à l'appui des données de la table poste.
INSERT INTO emploi (nom) SELECT DISTINCT nom FROM poste;

SELECT * FROM emploi;

-- On ajoute une colonne id_emploi à la table poste.
ALTER TABLE poste ADD COLUMN IF NOT EXISTS id_emploi integer;

-- On fait en sorte que la colonne ajoutée référence l'id de la table emploi.
ALTER TABLE poste ADD CONSTRAINT fk_poste_emploi FOREIGN KEY (id_emploi) REFERENCES emploi (id);

-- On met à jour les valeurs id_emploi de la table poste.
UPDATE poste p
SET id_emploi = e.id
FROM emploi e
WHERE p.nom = e.nom;

ALTER TABLE poste ALTER COLUMN id_emploi SET NOT NULL;

-- On termine par la suppression de la colonne nom.
ALTER TABLE poste DROP COLUMN IF EXISTS nom;

SELECT * FROM poste;
SELECT * FROM emploi;

-- On remarque un "faux" doublon dans les emplois, on réaffecte les postes concernés et on supprime ce doublon.
UPDATE poste SET id_emploi = 6 WHERE id_emploi = 5;
DELETE FROM emploi WHERE id = 5;

-- ****************************************************************************
-- contraindre la saisie en utilisant un trigger
-- ****************************************************************************

SELECT * FROM adresse;

-- On crée une fonction chargée de vérifier pour une adresse que sa géométrie et son COG de commune sont cohérents.
CREATE OR REPLACE FUNCTION verifier_coherence_geom_commune() RETURNS TRIGGER AS $$
DECLARE
    commune_trouvee RECORD;
BEGIN
    SELECT c.cog, c.nom INTO commune_trouvee FROM commune c WHERE ST_Intersects(NEW.geom, c.geom) LIMIT 1;
    
    IF (commune_trouvee IS NULL) THEN
        RAISE EXCEPTION 'La localisation de l''adresse % est en dehors de la zone d''étude.', NEW.id;
    ELSIF (commune_trouvee.cog <> NEW.cog_commune) THEN
        RAISE EXCEPTION 'La localisation de l''adresse % n''intersecte pas avec la commune cible, pensiez-vous plutôt à la commune % - % ?', NEW.id, commune_trouvee.cog, commune_trouvee.nom;
    ELSE
        RETURN NEW;
    END IF;
END;
$$ language plpgsql;

-- On définit les critères de déclenchement et d'exécution de la fonction de vérification.
CREATE TRIGGER coherence_geom_commune
BEFORE INSERT OR UPDATE ON adresse
FOR EACH ROW EXECUTE PROCEDURE verifier_coherence_geom_commune();

/*
En échec : 

INSERT INTO adresse (id, cog_commune, numero, voie, geom) VALUES (
    '30189_XXXXX',
    '30189',
    1,
    'Rue Dumbo',
    ST_Transform(ST_SetSRID(ST_MakePoint(2.294464, 48.858353), 4326), 2154)
); -- ERREUR:  La localisation de l'adresse 30189_XXXXX est en dehors de la zone d'étude.

INSERT INTO adresse (id, cog_commune, numero, voie, geom) VALUES (
    '30189_XXXXX',
    '30189',
    1,
    'Rue Dumbo',
    ST_Transform(ST_SetSRID(ST_MakePoint(4.407412883917744, 43.65775475593013), 4326), 2154)
); -- ERREUR:  La localisation de l'adresse 30189_XXXXX n'intersecte pas avec la commune cible, pensiez-vous plutôt à la commune 30258 - Saint-Gilles ?
*/

-- ****************************************************************************
-- contraindre la saisie en utilisant une vue
-- ****************************************************************************

CREATE VIEW vue_adresse AS
SELECT
    numero,
    repetition,
    voie,
    geom
FROM adresse;

CREATE RULE insert_vue_adresse AS ON INSERT TO vue_adresse DO INSTEAD
INSERT INTO adresse (
    id,
    numero,
    repetition,
    voie,
    cog_commune,
    geom
)
SELECT
    concat_ws('_', c.cog, 'XXXX', lpad(new.numero::varchar, 5, '0'), new.repetition),
    new.numero,
    new.repetition,
    initcap(new.voie),
    c.cog,
    new.geom
FROM commune c
WHERE ST_Intersects(c.Geom, new.geom)
ORDER BY c.cog
LIMIT 1;

SELECT * FROM adresse WHERE voie ilike '%schumann%';

INSERT INTO vue_adresse (numero, voie, geom) VALUES (
    418,
    'rue MAURICE schumann',
    ST_Transform(ST_SetSRID(ST_MakePoint(4.3721401579921615, 43.813190367808296), 4326), 2154)
);

SELECT * FROM adresse WHERE voie ilike '%schumann%';