-- ****************************************************************************
-- Correction table agent
-- ****************************************************************************

-- RAPPEL : Une relation est en 1NF si elle possède au moins une clé.

-- On souhaite utiliser l'adresse e-mail mais la création de la contrainte de clé primaire échoue car on peut voir dans les données qu'il y a un doublon.
-- En échec : ALTER TABLE agent ADD CONSTRAINT pk_agent PRIMARY KEY (email);

-- On va vider/re-remplir la table.
-- Les actions sont réalisées dans une transaction pour ne pas perdre les données initiales en cas d'échec.
BEGIN;

-- On crée une copie temporaire sans doublon de la table.
CREATE TEMPORARY TABLE copie_agent ON COMMIT DROP AS SELECT DISTINCT * FROM agent;

-- On vide la table originale.
DELETE FROM agent;

-- On réinsère les données depuis la copie sans doublon.
INSERT INTO agent SELECT * FROM copie_agent;

-- On peut alors créer la clé primaire.
ALTER TABLE agent ADD CONSTRAINT pk_agent PRIMARY KEY (email);

COMMIT;

-- ****************************************************************************
-- Correction table adresse
-- ****************************************************************************

-- RAPPEL : Une relation est en 1NF si tous ses attributs sont atomiques.

-- Le champ libelle n'est pas atomique, on va le séparer en 3 champs numero, repetition et voie.
ALTER TABLE adresse ADD COLUMN IF NOT EXISTS numero integer;
ALTER TABLE adresse ADD COLUMN IF NOT EXISTS repetition varchar;
ALTER TABLE adresse ADD COLUMN IF NOT EXISTS voie varchar;

-- Par exepression régulière, on isole le numéro présent au début de libelle.
UPDATE adresse SET numero = substring(libelle FROM '^\d+')::integer;

-- Par expression régulière, on isole la répétition qui se trouve à la fin de l'id.
UPDATE adresse SET repetition = substring(id FROM '[a-zA-Z]+$');

-- La voie correspond à la sous-chaine du libellé sans le numéro et la répétition.
UPDATE adresse SET voie = trim(substring(libelle FROM length(concat_ws(' ', numero, repetition)) + 1));

-- A l'issue du remplissage des colonnes numero, repetition et voie on peut supprimer le champ non-atomique libelle.
ALTER TABLE adresse DROP COLUMN IF EXISTS libelle;

-- ****************************************************************************
-- Correction table departement
-- ****************************************************************************

-- RAPPEL : Une relation est en 3NF si elle est en 2NF et si tout attribut n'appartenant pas à une clé ne dépend pas d'un autre attribut n'appartenant pas à une clé.

-- Il y a dans la table departement le cog_region et le nom_region or cog_region > nom_region, il convient de créer une nouvelle entité region.
CREATE TABLE IF NOT EXISTS region AS SELECT DISTINCT cog_region AS cog, nom_region AS nom FROM departement;

-- La création de la clé primaire échoue car dans les données initiales deux noms (redondance) étaient associés à un même Code Officiel Géographique de région (incohérence).
-- En échec : ALTER TABLE region ADD CONSTRAINT pk_region PRIMARY KEY (cog);

-- On supprime la ligne en erreur qui ressemble à une faute de frappe : Ocitanie au lieu de Occitanie.
DELETE FROM region WHERE nom = 'Ocitanie';

-- On peut alors créer la clé primaire.
ALTER TABLE region ADD CONSTRAINT pk_region PRIMARY KEY (cog);

-- On crée la clé étrangère de sorte que l'entité departement référence l'entité region.
ALTER TABLE departement ADD CONSTRAINT fk_departement_region FOREIGN KEY (cog_region) REFERENCES region (cog);

-- Enfin, on supprime de la table departement la colonne qui donne le nom_region.
ALTER TABLE departement DROP COLUMN IF EXISTS nom_region;

-- ****************************************************************************
-- Correction tables commune et adresse
-- ****************************************************************************

-- RAPPEL : Une relation est en 2NF si elle est en 1NF et si tout attribut qui n'est pas dans une clé ne dépend pas d'une partie seulement d'une clé.
-- RAPPEL : Une relation est en BCNF si elle est en 3NF et si tout attribut qui n'appartient pas à une clé n'est pas source d'une dépendance fonctionnelle vers une partie d'une clé.

-- /!\ : On considère qu'une commune ne peut avoir qu'un seul code postal alors que dans le "vrai" monde certaines en ont plusieurs.

-- On peut remarquer une erreur dans les données, la commune de Fons en Ardèche a été renseignée avec le COG de la commune de Fons dans le Gard.
SELECT cog, count(*) FROM commune GROUP BY cog ORDER BY 2 DESC;
SELECT * FROM commune WHERE cog = '30112';

-- Préalablement, on corrige le COG en erreur.
UPDATE commune SET cog = '07091' WHERE code_postal = '07200' AND nom = 'Fons';

-- Il va falloir changer la clé primaire de la table commune et par conséquent changer la clé étrangère de la table adresse.
-- Les opérations sont réalisées dans une transaction pour assurer le cohérence de l'ensemble (tout fonctionne ou rien n'est fait).
BEGIN;

-- On ajoute la colonne cog_commune qui sera la nouvelle clé étrangère.
ALTER TABLE adresse ADD COLUMN IF NOT EXISTS cog_commune varchar(5);

-- On remplit la nouvelle colonne en récupérant le cog_commune depuis la table commune.
UPDATE adresse a
SET cog_commune = c.cog
FROM commune c
WHERE a.code_postal_commune = c.code_postal
AND a.nom_commune = c.nom;

-- On supprime l'ancienne clé étrangère de la table adresse.
ALTER TABLE adresse DROP CONSTRAINT fk_adresse_commune;

-- On supprime l'ancienne clé primaire de la table commune.
ALTER TABLE commune DROP CONSTRAINT pk_commune;

-- On crée la nouvelle clé primaire de la table commune sur la colonne cog.
ALTER TABLE commune ADD CONSTRAINT pk_commune PRIMARY KEY (cog);

-- On recrée la clé étrangère d'adresse vers commune mais cette fois sur la colonne cog_commune.
ALTER TABLE adresse ADD CONSTRAINT fk_adresse_commune FOREIGN KEY (cog_commune) REFERENCES commune (cog);

-- On supprime de la table adresse les anciennes colonnes qui étaient utilisées par la précédente clé étrangère.
ALTER TABLE adresse DROP COLUMN IF EXISTS code_postal_commune;
ALTER TABLE adresse DROP COLUMN IF EXISTS nom_commune;

COMMIT;