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

-- 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);