github.com/benoitkugler/goacve@v0.0.0-20201217100549-151ce6e55dc8/server/migration/v2_to_v3/main.sql

-- on commence par déplacer les tables existantes
ALTER TABLE
    personnes RENAME TO old_personnes;

ALTER TABLE
    aides RENAME TO old_aides;

ALTER TABLE
    camps RENAME TO old_camps;

ALTER TABLE
    documents RENAME TO old_documents;

ALTER TABLE
    dons RENAME TO old_dons;

ALTER TABLE
    factures RENAME TO old_factures;

ALTER TABLE
    inscriptions RENAME TO old_inscriptions;

ALTER TABLE
    paiements RENAME TO old_paiements;

ALTER TABLE
    participants RENAME TO old_participants;

ALTER TABLE
    structures_aides RENAME TO old_structures_aides;

ALTER TABLE
    users RENAME TO old_users;
	-- DO NOT EDIT - autogenerated by structgen 
		   
	
CREATE TABLE aides (
	id serial PRIMARY KEY,
	id_structureaide integer NOT NULL,
	id_participant integer NOT NULL,
	valeur real NOT NULL,
	valide boolean NOT NULL,
	par_jour boolean NOT NULL,
	nb_jours_max integer NOT NULL
);


CREATE TABLE camps (
	id serial PRIMARY KEY,
	lieu varchar NOT NULL,
	nom varchar NOT NULL,
	prix real NOT NULL,
	nb_places integer NOT NULL,
	password varchar NOT NULL,
	ouvert boolean NOT NULL,
	nb_places_reservees integer NOT NULL,
	numero_js varchar NOT NULL,
	need_equilibre_gf boolean NOT NULL,
	age_min integer NOT NULL,
	age_max integer NOT NULL,
	options jsonb NOT NULL,
	date_debut date NOT NULL,
	date_fin date NOT NULL,
	liste_vetements jsonb NOT NULL,
	schema_paiement varchar NOT NULL CHECK (schema_paiement IN ('acompte', 'total')),
	joomeo_album_id varchar NOT NULL,
	envois jsonb NOT NULL,
	lien_compta varchar NOT NULL,
	option_prix jsonb NOT NULL,
	inscription_simple boolean NOT NULL
);


CREATE TABLE camp_contraintes (
	id_camp integer NOT NULL,
	id_contrainte integer NOT NULL
);


CREATE TABLE contenu_documents (
	id_document integer NOT NULL,
	contenu bytea,
	miniature bytea
);


CREATE TABLE contraintes (
	id serial PRIMARY KEY,
	id_personne integer,
	id_document integer,
	builtin varchar NOT NULL CHECK (builtin IN ('autre', 'bafa', 'bafa_equiv', 'bafd', 'bafd_equiv', 'carte_id', 'carte_vitale', 'cert_med_cuisine', 'haccp', '', 'permis', 'sb', 'scolarite', 'secour', 'test_nautique', 'vaccin')),
	nom varchar NOT NULL,
	description varchar NOT NULL,
	max_docs integer NOT NULL,
	jours_valide integer NOT NULL
);


CREATE TABLE documents (
	id serial PRIMARY KEY,
	taille integer NOT NULL,
	nom_client varchar NOT NULL,
	description varchar NOT NULL,
	date_heure_modif timestamp (0) with time zone
);


CREATE TABLE document_aides (
	id_document integer NOT NULL,
	id_aide integer NOT NULL
);


CREATE TABLE document_camps (
	id_document integer NOT NULL,
	id_camp integer NOT NULL,
	is_lettre boolean NOT NULL
);


CREATE TABLE document_personnes (
	id_document integer NOT NULL,
	id_personne integer NOT NULL,
	id_contrainte integer NOT NULL
);


CREATE TABLE dons (
	id serial PRIMARY KEY,
	id_personne integer,
	valeur real NOT NULL,
	mode_paiement varchar NOT NULL CHECK (mode_paiement IN ('ancv', '', 'cb', 'cheque', 'esp', 'helloasso', 'vir')),
	date_reception date NOT NULL,
	date_merci date NOT NULL,
	personne_merci varchar NOT NULL,
	recu_emis date NOT NULL,
	infos jsonb NOT NULL
);


CREATE TABLE equipiers (
	id serial PRIMARY KEY,
	id_camp integer NOT NULL,
	id_personne integer NOT NULL,
	roles varchar[],
	diplome varchar NOT NULL CHECK (diplome IN ('agreg', 'ass_sociale', '', 'bafa', 'bafa_stag', 'bafd', 'bafd_stag', 'bapaat', 'beatep', 'bjeps', 'cap', 'deug', 'dut', 'educ_spe', 'eje', 'instit', 'mon_educ', 'prof', 'staps', 'zzautre')),
	appro varchar NOT NULL CHECK (appro IN ('', 'autre', 'canoe', 'moto', 'sb', 'voile')),
	presence jsonb NOT NULL,
	invitation_equipier integer NOT NULL,
	charte integer NOT NULL CHECK (charte IN (-1, 1, 0))
);


CREATE TABLE equipier_contraintes (
	id_equipier integer NOT NULL,
	id_contrainte integer NOT NULL,
	optionnel boolean NOT NULL
);


CREATE TABLE factures (
	id serial PRIMARY KEY,
	id_personne integer NOT NULL,
	destinataires_optionnels jsonb,
	key varchar NOT NULL,
	copies_mails varchar[],
	last_connection timestamp (0) with time zone,
	is_confirmed boolean NOT NULL,
	is_validated boolean NOT NULL
);


CREATE TABLE groupes (
	id serial PRIMARY KEY,
	id_camp integer NOT NULL,
	nom varchar NOT NULL,
	plage jsonb NOT NULL,
	couleur varchar NOT NULL,
	isSimple boolean NOT NULL
);


CREATE TABLE groupe_contraintes (
	id_groupe integer NOT NULL,
	id_contrainte integer NOT NULL
);


CREATE TABLE groupe_equipiers (
	id_groupe integer NOT NULL,
	id_equipier integer NOT NULL,
	id_camp integer NOT NULL
);


CREATE TABLE groupe_participants (
	id_participant integer NOT NULL,
	id_groupe integer NOT NULL,
	id_camp integer NOT NULL,
	manuel boolean NOT NULL
);


CREATE TABLE imageuploadeds (
	id_camp integer NOT NULL,
	filename varchar NOT NULL,
	lien varchar NOT NULL,
	content bytea
);


CREATE TABLE inscriptions (
	id serial PRIMARY KEY,
	info varchar NOT NULL,
	date_heure timestamp (0) with time zone,
	copies_mails varchar[],
	responsable jsonb NOT NULL,
	participants jsonb
);


CREATE TABLE lettredirecteurs (
	id_camp integer NOT NULL,
	html varchar NOT NULL,
	use_coord_centre boolean NOT NULL,
	show_adresse_postale boolean NOT NULL,
	color_coord varchar NOT NULL
);


CREATE TABLE messages (
	id serial PRIMARY KEY,
	id_facture integer NOT NULL,
	kind integer NOT NULL CHECK (kind IN (3, 7, 2, 5, 4, 6, 9, 11, 10, 1, 8, 0)),
	created timestamp (0) with time zone,
	modified timestamp (0) with time zone,
	vu boolean NOT NULL
);


CREATE TABLE message_attestations (
	id_message integer NOT NULL,
	distribution integer NOT NULL CHECK (distribution IN (0, 1, 2)),
	guard_kind integer NOT NULL CHECK (guard_kind IN (3, 7, 2, 5, 4, 6, 9, 11, 10, 1, 8, 0))
);


CREATE TABLE message_documents (
	id_message integer NOT NULL,
	id_camp integer NOT NULL,
	guardKind integer NOT NULL CHECK (guardKind IN (3, 7, 2, 5, 4, 6, 9, 11, 10, 1, 8, 0))
);


CREATE TABLE message_messages (
	id_message integer NOT NULL,
	contenu varchar NOT NULL,
	guard_kind integer NOT NULL CHECK (guard_kind IN (3, 7, 2, 5, 4, 6, 9, 11, 10, 1, 8, 0))
);


CREATE TABLE message_placeliberes (
	id_message integer NOT NULL,
	id_participant integer NOT NULL,
	guardKind integer NOT NULL CHECK (guardKind IN (3, 7, 2, 5, 4, 6, 9, 11, 10, 1, 8, 0))
);


CREATE TABLE message_sondages (
	id_message integer NOT NULL,
	id_camp integer NOT NULL,
	guardKind integer NOT NULL CHECK (guardKind IN (3, 7, 2, 5, 4, 6, 9, 11, 10, 1, 8, 0)),
	isSimple boolean NOT NULL
);


CREATE TABLE paiements (
	id serial PRIMARY KEY,
	id_facture integer NOT NULL,
	is_acompte boolean NOT NULL,
	is_remboursement boolean NOT NULL,
	in_bordereau timestamp (0) with time zone,
	label_payeur varchar NOT NULL,
	nom_banque varchar NOT NULL,
	mode_paiement varchar NOT NULL CHECK (mode_paiement IN ('ancv', '', 'cb', 'cheque', 'esp', 'helloasso', 'vir')),
	numero varchar NOT NULL,
	valeur real NOT NULL,
	is_invalide boolean NOT NULL,
	date_reglement timestamp (0) with time zone,
	details varchar NOT NULL
);


CREATE TABLE participants (
	id serial PRIMARY KEY,
	id_camp integer NOT NULL,
	id_personne integer NOT NULL,
	id_facture integer,
	liste_attente jsonb NOT NULL,
	remises jsonb NOT NULL,
	option_prix jsonb NOT NULL,
	options jsonb NOT NULL,
	date_heure timestamp (0) with time zone,
	isSimple boolean NOT NULL
);


CREATE TABLE participant_equipiers (
	id_participant integer NOT NULL,
	id_equipier integer NOT NULL,
	id_groupe integer NOT NULL
);


CREATE TABLE participantsimples (
	id serial PRIMARY KEY,
	id_personne integer NOT NULL,
	id_camp integer NOT NULL,
	date_heure timestamp (0) with time zone,
	info varchar NOT NULL,
	isSimple boolean NOT NULL
);


CREATE TABLE personnes (
	id serial PRIMARY KEY,
	nom varchar NOT NULL,
	nom_jeune_fille varchar NOT NULL,
	prenom varchar NOT NULL,
	date_naissance date NOT NULL,
	ville_naissance varchar NOT NULL,
	departement_naissance varchar NOT NULL,
	sexe varchar NOT NULL CHECK (sexe IN ('', 'F', 'M')),
	tels varchar[],
	mail varchar NOT NULL,
	adresse varchar NOT NULL,
	code_postal varchar NOT NULL,
	ville varchar NOT NULL,
	pays varchar NOT NULL,
	securite_sociale varchar NOT NULL,
	profession varchar NOT NULL,
	etudiant boolean NOT NULL,
	fonctionnaire boolean NOT NULL,
	version_papier boolean NOT NULL,
	pub_hiver boolean NOT NULL,
	pub_ete boolean NOT NULL,
	echo_rocher boolean NOT NULL,
	rang_membre_asso varchar NOT NULL CHECK (rang_membre_asso IN ('3', '2', '1', '')),
	quotient_familial integer NOT NULL,
	cotisation integer[],
	eonews boolean NOT NULL,
	contact_groupe jsonb NOT NULL,
	fiche_sanitaire jsonb NOT NULL,
	is_temporaire boolean NOT NULL
);


CREATE TABLE sondages (
	id serial PRIMARY KEY,
	id_camp integer NOT NULL,
	id_facture integer NOT NULL,
	modified timestamp (0) with time zone,
	infos_avant_sejour integer NOT NULL CHECK (infos_avant_sejour IN (1, 2, 3, 4, 0)),
	infos_pendant_sejour integer NOT NULL CHECK (infos_pendant_sejour IN (1, 2, 3, 4, 0)),
	hebergement integer NOT NULL CHECK (hebergement IN (1, 2, 3, 4, 0)),
	activites integer NOT NULL CHECK (activites IN (1, 2, 3, 4, 0)),
	theme integer NOT NULL CHECK (theme IN (1, 2, 3, 4, 0)),
	nourriture integer NOT NULL CHECK (nourriture IN (1, 2, 3, 4, 0)),
	hygiene integer NOT NULL CHECK (hygiene IN (1, 2, 3, 4, 0)),
	ambiance integer NOT NULL CHECK (ambiance IN (1, 2, 3, 4, 0)),
	ressenti integer NOT NULL CHECK (ressenti IN (1, 2, 3, 4, 0)),
	message_enfant varchar NOT NULL,
	message_responsable varchar NOT NULL
);


CREATE TABLE structureaides (
	id serial PRIMARY KEY,
	nom varchar NOT NULL,
	immatriculation varchar NOT NULL,
	adresse varchar NOT NULL,
	code_postal varchar NOT NULL,
	ville varchar NOT NULL,
	telephone varchar NOT NULL,
	info varchar NOT NULL
);


CREATE TABLE users (
	id serial PRIMARY KEY,
	label varchar NOT NULL,
	mdp varchar NOT NULL,
	is_admin boolean NOT NULL,
	modules jsonb NOT NULL
);

ALTER TABLE aides ADD FOREIGN KEY(id_structureaide) REFERENCES structureaides ;
ALTER TABLE aides ADD FOREIGN KEY(id_participant) REFERENCES participants ON DELETE CASCADE;
ALTER TABLE camp_contraintes ADD FOREIGN KEY(id_camp) REFERENCES camps ON DELETE CASCADE;
ALTER TABLE camp_contraintes ADD FOREIGN KEY(id_contrainte) REFERENCES contraintes ;
ALTER TABLE contenu_documents ADD FOREIGN KEY(id_document) REFERENCES documents ON DELETE CASCADE;
ALTER TABLE contraintes ADD FOREIGN KEY(id_personne) REFERENCES personnes ON DELETE CASCADE;
ALTER TABLE contraintes ADD FOREIGN KEY(id_document) REFERENCES documents ;
ALTER TABLE document_aides ADD FOREIGN KEY(id_document) REFERENCES documents ON DELETE CASCADE;
ALTER TABLE document_aides ADD FOREIGN KEY(id_aide) REFERENCES aides ;
ALTER TABLE document_camps ADD FOREIGN KEY(id_document) REFERENCES documents ON DELETE CASCADE;
ALTER TABLE document_camps ADD FOREIGN KEY(id_camp) REFERENCES camps ;
ALTER TABLE document_personnes ADD FOREIGN KEY(id_document) REFERENCES documents ON DELETE CASCADE;
ALTER TABLE document_personnes ADD FOREIGN KEY(id_personne) REFERENCES personnes ;
ALTER TABLE document_personnes ADD FOREIGN KEY(id_contrainte) REFERENCES contraintes ;
ALTER TABLE dons ADD FOREIGN KEY(id_personne) REFERENCES personnes ON DELETE CASCADE;
ALTER TABLE equipiers ADD FOREIGN KEY(id_camp) REFERENCES camps ;
ALTER TABLE equipiers ADD FOREIGN KEY(id_personne) REFERENCES personnes ON DELETE CASCADE;
ALTER TABLE equipier_contraintes ADD FOREIGN KEY(id_equipier) REFERENCES equipiers ON DELETE CASCADE;
ALTER TABLE equipier_contraintes ADD FOREIGN KEY(id_contrainte) REFERENCES contraintes ;
ALTER TABLE factures ADD FOREIGN KEY(id_personne) REFERENCES personnes ;
ALTER TABLE groupes ADD FOREIGN KEY(id_camp) REFERENCES camps ON DELETE CASCADE;
ALTER TABLE groupe_contraintes ADD FOREIGN KEY(id_groupe) REFERENCES groupes ON DELETE CASCADE;
ALTER TABLE groupe_contraintes ADD FOREIGN KEY(id_contrainte) REFERENCES contraintes ;
ALTER TABLE groupe_equipiers ADD FOREIGN KEY(id_groupe) REFERENCES groupes ON DELETE CASCADE;
ALTER TABLE groupe_equipiers ADD FOREIGN KEY(id_equipier) REFERENCES equipiers ON DELETE CASCADE;
ALTER TABLE groupe_equipiers ADD FOREIGN KEY(id_camp) REFERENCES camps ON DELETE CASCADE;
ALTER TABLE groupe_participants ADD FOREIGN KEY(id_participant) REFERENCES participants ON DELETE CASCADE;
ALTER TABLE groupe_participants ADD FOREIGN KEY(id_groupe) REFERENCES groupes ON DELETE CASCADE;
ALTER TABLE groupe_participants ADD FOREIGN KEY(id_camp) REFERENCES camps ;
ALTER TABLE imageuploadeds ADD FOREIGN KEY(id_camp) REFERENCES camps ON DELETE CASCADE;
ALTER TABLE lettredirecteurs ADD FOREIGN KEY(id_camp) REFERENCES camps ON DELETE CASCADE;
ALTER TABLE messages ADD FOREIGN KEY(id_facture) REFERENCES factures ON DELETE CASCADE;
ALTER TABLE message_attestations ADD FOREIGN KEY(id_message) REFERENCES messages ON DELETE CASCADE;
ALTER TABLE message_documents ADD FOREIGN KEY(id_message) REFERENCES messages ON DELETE CASCADE;
ALTER TABLE message_documents ADD FOREIGN KEY(id_camp) REFERENCES camps ;
ALTER TABLE message_messages ADD FOREIGN KEY(id_message) REFERENCES messages ON DELETE CASCADE;
ALTER TABLE message_placeliberes ADD FOREIGN KEY(id_message) REFERENCES messages ON DELETE CASCADE;
ALTER TABLE message_placeliberes ADD FOREIGN KEY(id_participant) REFERENCES participants ;
ALTER TABLE message_sondages ADD FOREIGN KEY(id_message) REFERENCES messages ON DELETE CASCADE;
ALTER TABLE message_sondages ADD FOREIGN KEY(id_camp) REFERENCES camps ;
ALTER TABLE paiements ADD FOREIGN KEY(id_facture) REFERENCES factures ;
ALTER TABLE participants ADD FOREIGN KEY(id_camp) REFERENCES camps ;
ALTER TABLE participants ADD FOREIGN KEY(id_personne) REFERENCES personnes ON DELETE CASCADE;
ALTER TABLE participants ADD FOREIGN KEY(id_facture) REFERENCES factures ON DELETE SET NULL;
ALTER TABLE participant_equipiers ADD FOREIGN KEY(id_participant) REFERENCES participants ON DELETE CASCADE;
ALTER TABLE participant_equipiers ADD FOREIGN KEY(id_equipier) REFERENCES equipiers ON DELETE CASCADE;
ALTER TABLE participant_equipiers ADD FOREIGN KEY(id_groupe) REFERENCES groupes ON DELETE CASCADE;
ALTER TABLE participantsimples ADD FOREIGN KEY(id_personne) REFERENCES personnes ON DELETE CASCADE;
ALTER TABLE participantsimples ADD FOREIGN KEY(id_camp) REFERENCES camps ;
ALTER TABLE sondages ADD FOREIGN KEY(id_camp) REFERENCES camps ON DELETE CASCADE;
ALTER TABLE sondages ADD FOREIGN KEY(id_facture) REFERENCES factures ON DELETE CASCADE;
ALTER TABLE camps ADD UNIQUE(id,inscription_simple);
ALTER TABLE lettredirecteurs ADD UNIQUE(id_camp);
ALTER TABLE imageuploadeds  ADD UNIQUE(id_camp, lien);
ALTER TABLE groupes ADD UNIQUE(id_camp, nom);
ALTER TABLE groupes ADD UNIQUE(id, id_camp);
ALTER TABLE groupes ALTER COLUMN isSimple SET DEFAULT false;
ALTER TABLE groupes ADD CHECK(isSimple = false);
ALTER TABLE groupes ADD FOREIGN KEY (id_camp, isSimple) REFERENCES camps(id,inscription_simple);
ALTER TABLE sondages ADD UNIQUE(id_camp, id_facture);
ALTER TABLE participants ADD UNIQUE(id_personne, id_camp);
ALTER TABLE participants ADD UNIQUE(id, id_camp);
ALTER TABLE participants ALTER COLUMN isSimple SET DEFAULT false;
ALTER TABLE participants ADD CHECK(isSimple = false);
ALTER TABLE participants ADD FOREIGN KEY (id_camp, isSimple) REFERENCES camps(id,inscription_simple);
ALTER TABLE groupe_participants ADD UNIQUE (id_participant);
ALTER TABLE groupe_participants ADD UNIQUE (id_participant, id_groupe);
ALTER TABLE groupe_participants ADD FOREIGN KEY (id_participant, id_camp) REFERENCES participants(id,id_camp);
ALTER TABLE groupe_participants ADD FOREIGN KEY (id_groupe, id_camp) REFERENCES groupes(id,id_camp);
ALTER TABLE equipiers ADD UNIQUE(id_personne, id_camp);
ALTER TABLE equipiers ADD UNIQUE(id, id_camp);
ALTER TABLE groupe_equipiers ADD UNIQUE(id_groupe, id_equipier);
ALTER TABLE groupe_equipiers ADD FOREIGN KEY (id_equipier, id_camp) REFERENCES equipiers(id,id_camp);
ALTER TABLE groupe_equipiers ADD FOREIGN KEY (id_groupe, id_camp) REFERENCES groupes(id,id_camp);
ALTER TABLE participant_equipiers ADD UNIQUE (id_participant);
ALTER TABLE participant_equipiers ADD FOREIGN KEY (id_participant, id_groupe) REFERENCES groupe_participants(id_participant, id_groupe);
ALTER TABLE participant_equipiers ADD FOREIGN KEY (id_equipier, id_groupe) REFERENCES groupe_equipiers(id_equipier, id_groupe) ON DELETE CASCADE;
ALTER TABLE participantsimples ADD UNIQUE(id_personne, id_camp);
ALTER TABLE participantsimples ALTER COLUMN isSimple SET DEFAULT TRUE;
ALTER TABLE participantsimples ADD CHECK(isSimple = TRUE);
ALTER TABLE participantsimples ADD FOREIGN KEY (id_camp, isSimple) REFERENCES camps(id,inscription_simple);
ALTER TABLE contraintes ADD UNIQUE(nom, builtin, id_personne);
ALTER TABLE contraintes ADD CHECK(builtin <> '' OR id_personne IS NOT NULL);
ALTER TABLE equipier_contraintes ADD UNIQUE(id_equipier, id_contrainte);
ALTER TABLE camp_contraintes ADD UNIQUE(id_camp, id_contrainte);
ALTER TABLE groupe_contraintes ADD UNIQUE(id_groupe, id_contrainte);
ALTER TABLE contenu_documents ADD UNIQUE(id_document);
ALTER TABLE document_camps 	ADD UNIQUE(id_document);
ALTER TABLE document_personnes ADD UNIQUE(id_document);
ALTER TABLE document_aides ADD UNIQUE(id_document);
ALTER TABLE messages ADD UNIQUE(id, kind);
CREATE FUNCTION m_responsable() RETURNS int LANGUAGE sql IMMUTABLE PARALLEL SAFE AS 'SELECT 1';
CREATE FUNCTION m_centre() RETURNS int LANGUAGE sql IMMUTABLE PARALLEL SAFE AS 'SELECT 2';
CREATE FUNCTION m_accuse_reception() RETURNS int LANGUAGE sql IMMUTABLE PARALLEL SAFE AS 'SELECT 3';
CREATE FUNCTION m_facture() RETURNS int LANGUAGE sql IMMUTABLE PARALLEL SAFE AS 'SELECT 4';
CREATE FUNCTION m_documents() RETURNS int LANGUAGE sql IMMUTABLE PARALLEL SAFE AS 'SELECT 5';
CREATE FUNCTION m_facture_acquittee() RETURNS int LANGUAGE sql IMMUTABLE PARALLEL SAFE AS 'SELECT 6';
CREATE FUNCTION m_attestation_presence() RETURNS int LANGUAGE sql IMMUTABLE PARALLEL SAFE AS 'SELECT 7';
CREATE FUNCTION m_sondage() RETURNS int LANGUAGE sql IMMUTABLE PARALLEL SAFE AS 'SELECT 8';
CREATE FUNCTION m_inscription() RETURNS int LANGUAGE sql IMMUTABLE PARALLEL SAFE AS 'SELECT 9';
CREATE FUNCTION m_place_liberee() RETURNS int LANGUAGE sql IMMUTABLE PARALLEL SAFE AS 'SELECT 10';
ALTER TABLE message_documents ADD UNIQUE(id_message);
ALTER TABLE message_documents ADD CHECK(guardKind = 5);
ALTER TABLE message_documents ALTER COLUMN guardKind SET DEFAULT 5;
ALTER TABLE message_documents ADD FOREIGN KEY (id_message, guardKind) REFERENCES messages(id,kind);
ALTER TABLE message_sondages ADD UNIQUE(id_message);
ALTER TABLE message_sondages ADD CHECK(guardKind = 8);
ALTER TABLE message_sondages ALTER COLUMN guardKind SET DEFAULT 8;
ALTER TABLE message_sondages ADD FOREIGN KEY (id_message, guardKind) REFERENCES messages(id,kind);
ALTER TABLE message_sondages ALTER COLUMN isSimple SET DEFAULT FALSE;
ALTER TABLE message_sondages ADD CHECK(isSimple = FALSE);
ALTER TABLE message_sondages ADD FOREIGN KEY (id_camp, isSimple) REFERENCES camps(id,inscription_simple);
ALTER TABLE message_placeliberes ADD UNIQUE(id_message);
ALTER TABLE message_placeliberes ADD CHECK(guardKind = 10);
ALTER TABLE message_placeliberes ALTER COLUMN guardKind SET DEFAULT 10;
ALTER TABLE message_placeliberes ADD FOREIGN KEY (id_message, guardKind) REFERENCES messages(id,kind);
ALTER TABLE message_attestations ADD UNIQUE(id_message);
ALTER TABLE message_attestations ADD CHECK(guard_kind = 6 OR guard_kind = 7);
ALTER TABLE message_attestations ADD FOREIGN KEY (id_message, guard_kind) REFERENCES messages(id,kind);
CREATE FUNCTION d_mail() RETURNS int LANGUAGE sql IMMUTABLE PARALLEL SAFE AS 'SELECT 1';
ALTER TABLE message_messages ADD UNIQUE(id_message);
ALTER TABLE message_messages ADD CHECK(guard_kind = 1 OR guard_kind = 2);
ALTER TABLE message_messages ADD FOREIGN KEY (id_message, guard_kind) REFERENCES messages(id,kind);
-- Commandes à lancer pour setup la base de données
--
-- Contraintes de document 'built-in'
INSERT INTO
    contraintes (
        builtin,
        nom,
        max_docs,
        description,
        jours_valide
    )
VALUES
    ('bafa', 'BAFA', 1, '', 0),
    ('bafd', 'BAFD', 1, '', 0),
    (
        'carte_id',
        'Carte d''identité/Passeport',
        2,
        '',
        0
    ),
    ('permis', 'Permis de conduire', 2, '', 0),
    ('sb', 'Surveillant de baignade', 1, '', 0),
    ('secour', 'Secourisme (PSC1 - AFPS)', 1, '', 0),
    ('bafd_equiv', 'Equivalent BAFD', 1, '', 0),
    ('bafa_equiv', 'Equivalent BAFA', 1, '', 0),
    ('carte_vitale', 'Carte Vitale', 2, '', 0),
    (
        'haccp',
        'Cuisine (HACCP)',
        1,
        'Certificat concernant les normes HACCP, délivré sur place après une formation.',
        0
    ),
    ('scolarite', 'Certificat de scolarité', 1, '', 0),
    ('autre', 'Autre', 1, '', 0);

INSERT INTO
    contraintes (
        builtin,
        nom,
        max_docs,
        description,
        jours_valide
    )
VALUES
    ('vaccin', 'Vaccin', 5, '', 0);

-- le certificat de non contre indication est valable 3 mois
INSERT INTO
    contraintes (
        builtin,
        nom,
        max_docs,
        description,
        jours_valide
    )
VALUES
    (
        'cert_med_cuisine',
        'Certificat médical Cuisine',
        1,
        'Certificat de non contre-indication à la cuisine de collectivité, à demander à son médecin traitant.',
        90
    );

-- un administrateur doit uploader le test vierge
INSERT INTO
    contraintes (
        builtin,
        nom,
        max_docs,
        description,
        jours_valide
    )
VALUES
    ('test_nautique', 'Test nautique', 1, '', 0);-- script de transition depuis une base de données v2 vers une base de données v3
-- seul changement : on abandonne la liste des enfants

UPDATE
    old_personnes
SET
    rang_membre_asso = ''
WHERE
    rang_membre_asso = '0';

INSERT INTO personnes (id, nom, prenom, sexe, mail, adresse, code_postal, ville, tels, version_papier, pub_hiver, pub_ete, echo_rocher, rang_membre_asso, quotient_familial, pays, ville_naissance, departement_naissance, nom_jeune_fille, date_naissance, profession, etudiant, fonctionnaire, securite_sociale, cotisation, eonews, contact_groupe, fiche_sanitaire, is_temporaire)
SELECT
    id,
    coalesce(nom, ''),
    coalesce(prenom, ''),
    coalesce(sexe, ''),
    coalesce(mail, ''),
    coalesce(adresse, ''),
    coalesce(code_postal, ''),
    coalesce(ville, ''),
    coalesce(tels, '{}'),
    coalesce(version_papier, FALSE),
    coalesce(pub_hiver, FALSE),
    coalesce(pub_ete, FALSE),
    coalesce(echo_rocher, FALSE),
    coalesce(rang_membre_asso, ''),
    coalesce(quotient_familial, 0),
    coalesce(pays, ''),
    coalesce(ville_naissance, ''),
    coalesce(departement_naissance, ''),
    coalesce(nom_jeune_fille, ''),
    coalesce(date_naissance, '0001-01-01'),
    coalesce(profession, ''),
    coalesce(etudiant, FALSE),
    coalesce(fonctionnaire, FALSE),
    coalesce(securite_sociale, ''),
    coalesce(cotisation, '{}'),
    coalesce(eonews, FALSE),
    coalesce(contact_groupe, '{}'),
    coalesce(fiche_sanitaire, '{}'),
    coalesce(is_temporaire, FALSE)
FROM
    old_personnes;

-- on supprime les factures sans participants
DELETE FROM old_paiements
WHERE id_facture IN (
        SELECT
            old_factures.id
        FROM
            old_factures
        WHERE (
            SELECT
                count(*)
            FROM
                old_participants
            WHERE
                old_factures.id = old_participants.id_facture) = 0);

DELETE FROM old_factures
WHERE (
        SELECT
            count(*)
        FROM
            old_participants
        WHERE
            old_factures.id = old_participants.id_facture) = 0;

-- on enregistre une connection fictive
-- pour "marquer" comme vu

INSERT INTO factures (id, id_personne, destinataires_optionnels, KEY, copies_mails, last_connection, is_confirmed, is_validated)
SELECT
    id,
    id_responsable,
    CASE WHEN alt_dest ->> 'nom_prenom' <> '' THEN
        jsonb_build_array(alt_dest - '__actif__' - 'mail')
    ELSE
        '[]'
    END,
    KEY,
    coalesce(copies_mails, '{}'),
    now(),
    TRUE,
    TRUE
FROM
    old_factures;

ALTER TABLE old_camps RENAME COLUMN id_js TO numero_js;

-- cleanup des groupes liés aux séjours simples
UPDATE
    old_camps
SET
    groupes = NULL
WHERE
    old_camps.inscription_simple = TRUE;

UPDATE
    old_camps
SET
    schema_paiement = 'total'
WHERE
    schema_paiement = ''
    OR schema_paiement IS NULL;

INSERT INTO camps (id, lieu, nom, prix, nb_places, PASSWORD, ouvert, nb_places_reservees, numero_js, need_equilibre_gf, age_min, age_max, options, date_debut, date_fin, liste_vetements, schema_paiement, joomeo_album_id, envois, lien_compta, option_prix, inscription_simple)
SELECT
    id,
    lieu,
    nom,
    prix,
    nb_places,
    PASSWORD,
    ouvert,
    nb_places_reservees,
    coalesce(numero_js, ''),
    need_equilibre_gf,
    coalesce(age_min, 0),
    coalesce(age_max, 0),
    options,
    date_debut,
    date_fin,
    coalesce(liste_vetements, '{}'),
    coalesce(schema_paiement, ''),
    coalesce(joomeo_album_id, ''),
    envois,
    coalesce(lien_compta, ''),
    option_prix,
    coalesce(inscription_simple, FALSE)
FROM
    old_camps;

-- on sépare le contenu html de la lettre directeur
-- et on utilise toujours les coordonnées du directeur
-- pour les camps importés

INSERT INTO lettredirecteurs (id_camp, html, use_coord_centre, show_adresse_postale, color_coord)
SELECT
    id,
    coalesce(lettre_directeur ->> 'html', ''),
    FALSE,
    FALSE,
    ''
FROM
    old_camps
WHERE
    coalesce(lettre_directeur ->> 'html', '') <> '';

-- on a besoin de lier les anciens aux ids des nouveaux
CREATE TABLE tmp_groupes (
    id serial,
    id_camp integer,
    old_id varchar,
    nom varchar NOT NULL,
    plage jsonb NOT NULL,
    need_aisance_aquatique boolean NOT NULL
);

-- on transforme les groupes en gardant la correspondances des ids
INSERT INTO tmp_groupes (id_camp, old_id, nom, plage, need_aisance_aquatique)
SELECT
    old_camps.id,
    grs.key,
    grs.value ->> 'nom',
    jsonb_build_object('from', grs.value -> 'date_debut', 'to', grs.value -> 'date_fin'),
    coalesce(grs.value ->> 'need_aisance_aquatique', 'false') = 'true'
FROM
    old_camps
    JOIN LATERAL jsonb_each(old_camps.groupes) AS grs ON TRUE
WHERE ((coalesce(old_camps.inscription_simple, FALSE) = FALSE)
    AND (old_camps.groupes IS NOT NULL)
    AND (jsonb_typeof(old_camps.groupes) <> 'null')
    AND (grs.value ->> 'nom' <> 'null'));

-- On peut maintenant transférer les groupes vers la table définitive ...
INSERT INTO groupes (id, id_camp, nom, plage, couleur)
SELECT
    id,
    id_camp,
    nom,
    plage,
    ''
FROM
    tmp_groupes;

-- ... et ajouter les contraintes 'aisance_aquatique'
INSERT INTO groupe_contraintes (id_groupe, id_contrainte)
SELECT
    id,
    (
        SELECT
            id
        FROM
            contraintes
        WHERE
            builtin = 'test_nautique')
FROM
    tmp_groupes
WHERE
    need_aisance_aquatique;

-- On copie les demandes de test nautique des séjours
INSERT INTO camp_contraintes (id_camp, id_contrainte)
SELECT
    id,
    (
        SELECT
            id
        FROM
            contraintes
        WHERE
            builtin = 'test_nautique')
FROM
    old_camps
WHERE
    need_aisance_aquatique;

-- copie des inscriptions (sans la case autorisation_photos)
INSERT INTO inscriptions (info, date_heure, copies_mails, responsable, participants)
SELECT
    info,
    date_heure,
    copies_mails,
    -- on agrège les champs du responsable légal
    jsonb_build_object('lienid', lienid, 'nom', nom, 'prenom', prenom, 'sexe', sexe, 'mail', mail, 'adresse', adresse, 'code_postal', code_postal, 'ville', ville, 'tels', tels, 'date_naissance', to_char(date_naissance::date, 'YYYY-MM-DD'), 'pays', pays),
    -- on nettoie les vieux champs
    (
        SELECT
            jsonb_agg(value - 'date_heure' - 'info' - 'autorisation_photos')
        FROM jsonb_array_elements(participants))
FROM
    old_inscriptions
WHERE
    date_heure::date > '0001-01-01';

-- copie des inscrits et attente
INSERT INTO participants (id, id_camp, id_personne, id_facture, liste_attente, remises, option_prix, options, date_heure)
SELECT
    id,
    id_camp,
    id_personne,
    id_facture,
    jsonb_build_object('statut', CASE WHEN ROLE = '_attente' THEN
            1
        WHEN ROLE = '_attente_reponse' THEN
            2
        ELSE
            0
        END, 'raison', raison_attente),
    coalesce(remises, '{}'),
    coalesce(option_prix, '{}'),
    coalesce(options, '{}'),
    date_heure
FROM
    old_participants
WHERE (old_participants.role IN ('_campeur', '_attente', '_attente_reponse'))
    AND (
        SELECT
            coalesce(inscription_simple, FALSE)
        FROM
            old_camps
        WHERE
            old_camps.id = old_participants.id_camp) = FALSE;

-- modification de l'option JOUR
CREATE OR REPLACE FUNCTION compute_jours (jours jsonb, date_debut timestamp)
    RETURNS integer[]
    AS $$
DECLARE
    plage_from timestamp;
    plage_to timestamp;
    index_from int;
    index_to int;
BEGIN
    IF jours IS NULL THEN
        RETURN '{}';
    END IF;
    IF coalesce(jours ->> 'from', '') = '' OR coalesce(jours ->> 'to', '') = '' THEN
        RETURN '{}';
    END IF;
    plage_from := to_date(jours ->> 'from', 'YYYY-MM-DD"T"HH24:MI:SS"Z"');
    plage_to := to_date(jours ->> 'to', 'YYYY-MM-DD"T"HH24:MI:SS"Z"');
    IF coalesce(plage_from, '0001-01-01T00:00:00Z') = '0001-01-01T00:00:00Z' OR coalesce(plage_to, '0001-01-01T00:00:00Z') = '0001-01-01T00:00:00Z' THEN
        RETURN '{}';
    END IF;
    index_from := extract(DAYS FROM plage_from - date_debut);
    index_to := extract(DAYS FROM plage_to - date_debut);
    RETURN ARRAY (
        SELECT
            a.n
        FROM
            generate_series(index_from, index_to) AS a (n));
END;
$$
LANGUAGE plpgsql;

UPDATE
    participants
SET
    option_prix = jsonb_set(participants.option_prix, '{jour}', to_jsonb (compute_jours (participants.option_prix -> 'jour', camps.date_debut)))
FROM
    camps
WHERE
    participants.id_camp = camps.id;

DROP FUNCTION compute_jours (jours jsonb, date_debut timestamp);

-- copie des groupes
INSERT INTO groupe_participants (id_participant, id_groupe, id_camp, manuel)
SELECT
    id,
    -- on trouve le groupe, possiblement null
    (
        SELECT
            id FROM tmp_groupes
        WHERE
            tmp_groupes.id_camp = old_participants.id_camp
            AND tmp_groupes.old_id = old_participants.id_groupe) AS idG,
id_camp,
FALSE -- automatique par défaut
FROM
    old_participants
WHERE
    old_participants.id = ANY (
        SELECT
            id
        FROM
            participants)
    AND (
        SELECT
            id
        FROM
            tmp_groupes
        WHERE
            tmp_groupes.id_camp = old_participants.id_camp
            AND tmp_groupes.old_id = old_participants.id_groupe) IS NOT NULL;

-- copie des participants simples :
INSERT INTO participantsimples (id_personne, id_camp, date_heure, info)
SELECT
    id_personne,
    id_camp,
    date_heure,
    coalesce(info, '')
FROM
    old_participants
WHERE (
    SELECT
        inscription_simple
    FROM
        camps
    WHERE
        camps.id = old_participants.id_camp) = TRUE
    AND old_participants.role IN ('_campeur', '_attente', '_attente_reponse');

-- copie des équipiers
UPDATE
    old_participants
SET
    appro = ''
WHERE
    appro = 'aucun'
    OR appro = 'null';

UPDATE
    old_participants
SET
    diplome = ''
WHERE
    diplome = 'null';

INSERT INTO equipiers (id, id_camp, id_personne, roles, diplome, appro, presence, invitation_equipier, charte)
SELECT
    id,
    id_camp,
    id_personne,
    ARRAY[ROLE],
    coalesce(diplome, ''),
    coalesce(appro, ''),
    coalesce(presence, '{}'),
    coalesce(invitation_equipier, 0),
    0
FROM
    old_participants
WHERE
    NOT ROLE IN ('_campeur', '_attente', '_attente_reponse');

INSERT INTO structureaides (id, nom, immatriculation, adresse, code_postal, ville, telephone, info)
SELECT
    id,
    coalesce(nom, ''),
    coalesce(immatriculation, ''),
    coalesce(adresse, ''),
    coalesce(code_postal, ''),
    coalesce(ville, ''),
    coalesce(telephone, ''),
    coalesce(info, '')
FROM
    old_structures_aides;

-- on ignore les aides sur les camps simples et les équipiers
INSERT INTO aides (id, id_structureaide, id_participant, valeur, valide, par_jour, nb_jours_max)
SELECT
    old_aides.id,
    old_aides.id_structure,
    old_aides.id_participant,
    old_aides.valeur,
    coalesce(old_aides.valide, TRUE),
    coalesce(old_aides.par_jour, FALSE),
    coalesce(old_aides.nb_jours_max, 0)
FROM
    old_aides
    JOIN old_participants ON old_aides.id_participant = old_participants.id
    JOIN old_camps ON old_participants.id_camp = old_camps.id
WHERE (coalesce(old_camps.inscription_simple, FALSE) = FALSE)
    AND old_participants.role IN ('_campeur', '_attente', '_attente_reponse');

-- on nettoie les documents sans contenu
DELETE FROM old_documents
WHERE content IS NULL;

-- on copie les meta données ...
INSERT INTO documents (id, taille, nom_client, description, date_heure_modif)
SELECT
    id,
    taille,
    nom_client,
    description,
    date_heure_modif
FROM
    old_documents;

-- ... puis le contenu
INSERT INTO contenu_documents (id_document, contenu, miniature)
SELECT
    id,
    content,
    miniature
FROM
    old_documents;

-- on sépare suivant les targets
INSERT INTO document_aides (id_document, id_aide)
SELECT
    id,
    id_target
FROM
    old_documents
WHERE
    target = 'Ai';

INSERT INTO document_camps (id_document, id_camp, is_lettre)
SELECT
    id,
    id_target,
    target = 'Le'
FROM
    old_documents
WHERE
    target = 'Le'
    OR target = 'Pj';

INSERT INTO document_personnes (id_document, id_personne, id_contrainte)
SELECT
    id,
    id_target,
    (
        SELECT
            id
        FROM
            contraintes
        WHERE
            builtin = categorie)
FROM
    old_documents
WHERE
    target = 'Pe';

-- on nettoie les description "undefined" qui viennent du frontend
UPDATE
    documents
SET
    description = ''
WHERE
    description = 'undefined';

INSERT INTO dons (id, id_personne, valeur, mode_paiement, date_reception, date_merci, personne_merci, recu_emis, infos)
SELECT
    id,
    id_donateur,
    valeur,
    coalesce(mode_paiement, ''),
    coalesce(date_reception, '0001-01-01'),
    coalesce(date_merci, '0001-01-01'),
    coalesce(personne_merci, ''),
    coalesce(recu_emis, '0001-01-01'),
    coalesce(infos, '{}')
FROM
    old_dons;

INSERT INTO paiements (id, id_facture, is_acompte, is_remboursement, in_bordereau, label_payeur, nom_banque, mode_paiement, numero, valeur, is_invalide, date_reglement, details)
SELECT
    id,
    id_facture,
    coalesce(is_acompte, FALSE),
    coalesce(is_remboursement, FALSE),
    coalesce(in_bordereau, '0001-01-01'),
    coalesce(label_payeur, ''),
    coalesce(nom_banque, ''),
    coalesce(mode_paiement, ''),
    coalesce(numero, ''),
    coalesce(valeur, 0),
    coalesce(is_invalide, FALSE),
    coalesce(date_reglement, '0001-01-01'),
    CASE WHEN date_encaissement > '0001-01-01' THEN
        'encaissement: ' || to_char(date_encaissement, 'DD/MM/YYYY')
    ELSE
        ''
    END
FROM
    old_paiements;

-- transformation du champ info
-- (1) MResponsable
-- (2) MCentre
-- (3) MAccuseReception
-- (4) MFacture
-- (5) MDocuments
-- (6) MFactureAquittee
-- (7) MAttestationPresence
-- (8) MSondage
-- (9) MInscription
-- (10) MPlaceLiberee

INSERT INTO messages (id_facture, kind, created, modified, vu)
SELECT
    id,
    m_responsable (),
    CASE WHEN ( SELECT DISTINCT
            (date_heure)::date
        FROM
            old_participants
        WHERE
            id_facture = old_factures.id
        ORDER BY
            date_heure
        LIMIT 1) > '0001-01-01' THEN
( SELECT DISTINCT
        date_heure
    FROM
        old_participants
    WHERE
        id_facture = old_factures.id
    ORDER BY
        date_heure
    LIMIT 1)
ELSE
    (
        SELECT
            date_debut
        FROM
            old_camps
        WHERE
            old_camps.id = (
                SELECT
                    id_camp
                FROM
                    old_participants
                WHERE
                    old_participants.id_facture = old_factures.id
                LIMIT 1))
    END,
    NULL,
    TRUE
FROM
    old_factures
WHERE
    coalesce(info, '') <> '';

-- comme il n'y qu'une info par facture, on se sert de la facture
-- pour retrouver le message

INSERT INTO message_messages (id_message, contenu, guard_kind)
SELECT
    messages.id,
    old_factures.info,
    m_responsable ()
FROM
    messages
    JOIN old_factures ON old_factures.id = messages.id_facture
WHERE
    messages.kind = m_responsable ();

-- champ etat.accuse reception
INSERT INTO messages (id_facture, kind, created, modified, vu)
SELECT
    id,
    m_accuse_reception (),
    (etat ->> 'accuse_reception')::timestamp WITH time zone AS ti,
    NULL,
    TRUE
FROM
    old_factures
WHERE
    coalesce(etat ->> 'accuse_reception', '0001-01-01T00:00:00Z') <> '0001-01-01T00:00:00Z'
ORDER BY
    ti;

-- champ etat.facture
INSERT INTO messages (id_facture, kind, created, modified, vu)
SELECT
    id,
    m_facture (),
    (etat ->> 'facture')::timestamp AS ti,
    NULL,
    TRUE
FROM
    old_factures
WHERE
    coalesce(etat ->> 'facture', '0001-01-01T00:00:00Z') <> '0001-01-01T00:00:00Z'
ORDER BY
    ti;

-- champ etat.facture_acquittee
INSERT INTO messages (id_facture, kind, created, modified, vu)
SELECT
    id,
    m_facture_acquittee (),
    (etat ->> 'facture_acquittee')::timestamp AS ti,
    NULL,
    TRUE
FROM
    old_factures
WHERE
    coalesce(etat ->> 'facture_acquittee', '0001-01-01T00:00:00Z') <> '0001-01-01T00:00:00Z'
ORDER BY
    ti;

-- champ etat.attestation_presence
INSERT INTO messages (id_facture, kind, created, modified, vu)
SELECT
    id,
    m_attestation_presence (),
    (etat ->> 'attestation_presence')::timestamp AS ti,
    NULL,
    TRUE
FROM
    old_factures
WHERE
    coalesce(etat ->> 'attestation_presence', '0001-01-01T00:00:00Z') <> '0001-01-01T00:00:00Z'
ORDER BY
    ti;

-- toutes les attestations ont été envoyées par mail
INSERT INTO message_attestations (id_message, distribution, guard_kind)
SELECT
    id,
    d_mail (),
    kind
FROM
    messages
WHERE
    kind = m_attestation_presence ()
    OR kind = m_facture_acquittee ();

-- table temporaire
CREATE TABLE tmp_messages_camps (
    id_message integer,
    -- target to fill
    id_facture integer,
    id_camp integer,
    created timestamp
);

INSERT INTO tmp_messages_camps (id_facture, id_camp, created)
SELECT
    old_factures.id,
    docs.key::int,
    (docs.value::text)::timestamp
FROM
    old_factures
    JOIN LATERAL jsonb_each(old_factures.etat -> 'documents') AS docs ON TRUE
WHERE
    coalesce(old_factures.etat -> 'documents', 'null') <> 'null'
    AND coalesce(docs.value, 'null') <> 'null'
    AND ((docs.value)::text)::date <> '0001-01-01';

-- on pré-calcule les ids messages et on les sauvegarde dans la table temporaire
UPDATE
    tmp_messages_camps
SET
    id_message = nextval(pg_get_serial_sequence('messages', 'id'));

-- pour éviter que tous les anciens messages camps soit dans un groupe
-- on ajoute une petite variation dans le temps

INSERT INTO messages (id, id_facture, kind, created, modified, vu)
SELECT
    id_message,
    id_facture,
    m_documents (),
    created + id_camp * interval '1 second',
    NULL,
    TRUE
FROM
    tmp_messages_camps;

INSERT INTO message_documents (id_message, id_camp, guardKind)
SELECT
    id_message,
    id_camp,
    m_documents ()
FROM
    tmp_messages_camps;

-- cleanup
DROP TABLE tmp_messages_camps;

-- type 'id'
INSERT INTO users (label, mdp, is_admin, modules)
SELECT
    label,
    mdp,
    is_admin,
    modules
FROM
    old_users;

-- convertion des dates stockées en JSON comme des time
CREATE FUNCTION time_to_date (text)
    RETURNS jsonb
    AS $$
    SELECT
        to_jsonb (to_date($1, 'YYYY-MM-DD"T"HH24:MI:SS"Z"')::date)
$$
LANGUAGE SQL;

UPDATE
    camps
SET
    option_prix = jsonb_set(option_prix, '{semaine, plage_1, from}', time_to_date (option_prix -> 'semaine' -> 'plage_1' ->> 'from'))
WHERE
    option_prix -> 'semaine' -> 'plage_1' -> 'from' IS NOT NULL;

UPDATE
    camps
SET
    option_prix = jsonb_set(option_prix, '{semaine, plage_1, to}', time_to_date (option_prix -> 'semaine' -> 'plage_1' ->> 'to'))
WHERE
    option_prix -> 'semaine' -> 'plage_1' -> 'to' IS NOT NULL;

UPDATE
    camps
SET
    option_prix = jsonb_set(option_prix, '{semaine, plage_2, from}', time_to_date (option_prix -> 'semaine' -> 'plage_2' ->> 'from'))
WHERE
    option_prix -> 'semaine' -> 'plage_2' -> 'from' IS NOT NULL;

UPDATE
    camps
SET
    option_prix = jsonb_set(option_prix, '{semaine, plage_2, to}', time_to_date (option_prix -> 'semaine' -> 'plage_2' ->> 'to'))
WHERE
    option_prix -> 'semaine' -> 'plage_2' -> 'to' IS NOT NULL;

UPDATE
    equipiers
SET
    presence = jsonb_set(presence, '{from}', time_to_date (presence ->> 'from'))
WHERE
    presence -> 'from' IS NOT NULL;

UPDATE
    equipiers
SET
    presence = jsonb_set(presence, '{to}', time_to_date (presence ->> 'to'))
WHERE
    presence -> 'to' IS NOT NULL;

UPDATE
    groupes
SET
    plage = jsonb_set(plage, '{from}', time_to_date (plage ->> 'from'))
WHERE
    plage -> 'from' IS NOT NULL;

UPDATE
    groupes
SET
    plage = jsonb_set(plage, '{to}', time_to_date (plage ->> 'to'))
WHERE
    plage -> 'to' IS NOT NULL;

UPDATE
    inscriptions
SET
    responsable = jsonb_set(responsable, '{date_naissance}', time_to_date (responsable ->> 'date_naissance'))
WHERE
    responsable -> 'date_naissance' IS NOT NULL;

UPDATE
    inscriptions
SET
    participants = (
        SELECT
            jsonb_agg(jsonb_set(value, '{date_naissance}', time_to_date (value ->> 'date_naissance')))
        FROM
            jsonb_array_elements(participants));

DROP FUNCTION time_to_date (text);

-- on peut supprimer les anciennes tables
DROP TABLE old_personnes CASCADE;

DROP TABLE old_aides CASCADE;

DROP TABLE old_camps CASCADE;

DROP TABLE old_documents CASCADE;

DROP TABLE old_dons CASCADE;

DROP TABLE old_factures CASCADE;

DROP TABLE old_inscriptions CASCADE;

DROP TABLE old_paiements CASCADE;

DROP TABLE old_participants CASCADE;

DROP TABLE old_structures_aides CASCADE;

DROP TABLE old_users CASCADE;

-- ajuste les serials
SELECT
    setval(
        pg_get_serial_sequence('aides', 'id'),
        coalesce(max(id), 0) + 1,
        FALSE
    )
FROM
    aides;

SELECT
    setval(
        pg_get_serial_sequence('camps', 'id'),
        coalesce(max(id), 0) + 1,
        FALSE
    )
FROM
    camps;

SELECT
    setval(
        pg_get_serial_sequence('contraintes', 'id'),
        coalesce(max(id), 0) + 1,
        FALSE
    )
FROM
    contraintes;

SELECT
    setval(
        pg_get_serial_sequence('documents', 'id'),
        coalesce(max(id), 0) + 1,
        FALSE
    )
FROM
    documents;

SELECT
    setval(
        pg_get_serial_sequence('dons', 'id'),
        coalesce(max(id), 0) + 1,
        FALSE
    )
FROM
    dons;

SELECT
    setval(
        pg_get_serial_sequence('equipiers', 'id'),
        coalesce(max(id), 0) + 1,
        FALSE
    )
FROM
    equipiers;

SELECT
    setval(
        pg_get_serial_sequence('factures', 'id'),
        coalesce(max(id), 0) + 1,
        FALSE
    )
FROM
    factures;

SELECT
    setval(
        pg_get_serial_sequence('groupes', 'id'),
        coalesce(max(id), 0) + 1,
        FALSE
    )
FROM
    groupes;

SELECT
    setval(
        pg_get_serial_sequence('messages', 'id'),
        coalesce(max(id), 0) + 1,
        FALSE
    )
FROM
    messages;

SELECT
    setval(
        pg_get_serial_sequence('paiements', 'id'),
        coalesce(max(id), 0) + 1,
        FALSE
    )
FROM
    paiements;

SELECT
    setval(
        pg_get_serial_sequence('participants', 'id'),
        coalesce(max(id), 0) + 1,
        FALSE
    )
FROM
    participants;

SELECT
    setval(
        pg_get_serial_sequence('participantsimples', 'id'),
        coalesce(max(id), 0) + 1,
        FALSE
    )
FROM
    participantsimples;

SELECT
    setval(
        pg_get_serial_sequence('personnes', 'id'),
        coalesce(max(id), 0) + 1,
        FALSE
    )
FROM
    personnes;

SELECT
    setval(
        pg_get_serial_sequence('sondages', 'id'),
        coalesce(max(id), 0) + 1,
        FALSE
    )
FROM
    sondages;

SELECT
    setval(
        pg_get_serial_sequence('structureaides', 'id'),
        coalesce(max(id), 0) + 1,
        FALSE
    )
FROM
    structureaides;

SELECT
    setval(
        pg_get_serial_sequence('users', 'id'),
        coalesce(max(id), 0) + 1,
        FALSE
    )
FROM
    users;

DROP FUNCTION IF EXISTS m_responsable();

DROP FUNCTION IF EXISTS m_centre();

DROP FUNCTION IF EXISTS m_accuse_reception();

DROP FUNCTION IF EXISTS m_facture();

DROP FUNCTION IF EXISTS m_documents();

DROP FUNCTION IF EXISTS m_facture_aquittee();

DROP FUNCTION IF EXISTS m_attestation_presence();

DROP FUNCTION IF EXISTS m_sondage();

DROP FUNCTION IF EXISTS m_inscription();

DROP FUNCTION IF EXISTS m_place_liberee();

DROP FUNCTION IF EXISTS d_mail();