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

	-- DO NOT EDIT - autogenerated by structgen 
		   
	
CREATE OR REPLACE FUNCTION f_delfunc (OUT func_dropped int
)
AS $func$
DECLARE
    _sql text;
BEGIN
    SELECT
        count(*)::int,
        'DROP FUNCTION ' || string_agg(oid::regprocedure::text, '; DROP FUNCTION ')
    FROM
        pg_proc
    WHERE
        starts_with (proname, 'structgen_validate_json')
        AND pg_function_is_visible(oid) INTO func_dropped,
        _sql;
    -- only returned if trailing DROPs succeed
    IF func_dropped > 0 THEN
        -- only if function(s) found
        EXECUTE _sql;
    END IF;
END
$func$
LANGUAGE plpgsql;

SELECT
    f_delfunc ();

DROP FUNCTION f_delfunc;

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 OR REPLACE FUNCTION structgen_validate_json_boolean (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		RETURN jsonb_typeof(data) = 'boolean';
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_string (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		RETURN jsonb_typeof(data) = 'string';
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_number (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		RETURN jsonb_typeof(data) = 'number';
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_2721319832 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('rendez_vous', 'prix')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_string(data->'rendez_vous')
AND structgen_validate_json_number(data->'prix')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_1080302296 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('actif', 'commentaire', 'aller', 'retour')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_boolean(data->'actif')
AND structgen_validate_json_string(data->'commentaire')
AND structgen_validate_json_struct_2721319832(data->'aller')
AND structgen_validate_json_struct_2721319832(data->'retour')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_2617578105 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('actif', 'prix_acve', 'prix_loueur')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_boolean(data->'actif')
AND structgen_validate_json_number(data->'prix_acve')
AND structgen_validate_json_number(data->'prix_loueur')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_2942046293 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('bus', 'materiel_ski')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_struct_1080302296(data->'bus')
AND structgen_validate_json_struct_2617578105(data->'materiel_ski')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_118887585 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('quantite', 'description', 'obligatoire')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_number(data->'quantite')
AND structgen_validate_json_string(data->'description')
AND structgen_validate_json_boolean(data->'obligatoire')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_array_struct_118887585 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) = 'null' THEN RETURN TRUE; END IF;
		RETURN jsonb_typeof(data) = 'array'
			AND (SELECT bool_and( structgen_validate_json_struct_118887585(value) )  FROM jsonb_array_elements(data)) 
			;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_1755779154 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('liste', 'complement')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_array_struct_118887585(data->'liste')
AND structgen_validate_json_string(data->'complement')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_690496530 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('__locked__', 'lettre_directeur', 'liste_vetements', 'liste_participants')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_boolean(data->'__locked__')
AND structgen_validate_json_boolean(data->'lettre_directeur')
AND structgen_validate_json_boolean(data->'liste_vetements')
AND structgen_validate_json_boolean(data->'liste_participants')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_1 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			TRUE
		) FROM jsonb_each(data))  
		
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_1788348674 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('from', 'to')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_struct_1(data->'from')
AND structgen_validate_json_struct_1(data->'to')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_2148407589 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('plage_1', 'plage_2', 'prix_1', 'prix_2')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_struct_1788348674(data->'plage_1')
AND structgen_validate_json_struct_1788348674(data->'plage_2')
AND structgen_validate_json_number(data->'prix_1')
AND structgen_validate_json_number(data->'prix_2')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_3786019866 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('id', 'prix', 'statut', 'description')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_number(data->'id')
AND structgen_validate_json_number(data->'prix')
AND structgen_validate_json_string(data->'statut')
AND structgen_validate_json_string(data->'description')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_array_struct_3786019866 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) = 'null' THEN RETURN TRUE; END IF;
		RETURN jsonb_typeof(data) = 'array'
			AND (SELECT bool_and( structgen_validate_json_struct_3786019866(value) )  FROM jsonb_array_elements(data)) 
			;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_array_number (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) = 'null' THEN RETURN TRUE; END IF;
		RETURN jsonb_typeof(data) = 'array'
			AND (SELECT bool_and( structgen_validate_json_number(value) )  FROM jsonb_array_elements(data)) 
			;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_array_4_number (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		
		RETURN jsonb_typeof(data) = 'array'
			AND (SELECT bool_and( structgen_validate_json_number(value) )  FROM jsonb_array_elements(data)) 
			AND jsonb_array_length(data) = 4;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_1454451391 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('active', 'semaine', 'statut', 'jour', 'quotient_familial')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_string(data->'active')
AND structgen_validate_json_struct_2148407589(data->'semaine')
AND structgen_validate_json_array_struct_3786019866(data->'statut')
AND structgen_validate_json_array_number(data->'jour')
AND structgen_validate_json_array_4_number(data->'quotient_familial')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

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 CONSTRAINT options_structgen_validate_json_struct_2942046293 CHECK (structgen_validate_json_struct_2942046293(options)),
	date_debut date  NOT NULL,
	date_fin date  NOT NULL,
	liste_vetements jsonb  NOT NULL CONSTRAINT liste_vetements_structgen_validate_json_struct_1755779154 CHECK (structgen_validate_json_struct_1755779154(liste_vetements)),
	schema_paiement varchar  CHECK (schema_paiement IN ('acompte', 'total')) NOT NULL,
	joomeo_album_id varchar  NOT NULL,
	envois jsonb  NOT NULL CONSTRAINT envois_structgen_validate_json_struct_690496530 CHECK (structgen_validate_json_struct_690496530(envois)),
	lien_compta varchar  NOT NULL,
	option_prix jsonb  NOT NULL CONSTRAINT option_prix_structgen_validate_json_struct_1454451391 CHECK (structgen_validate_json_struct_1454451391(option_prix)),
	inscription_simple boolean  NOT NULL,
	infos varchar  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  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')) NOT NULL,
	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 OR REPLACE FUNCTION structgen_validate_json_struct_4190070452 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('id', 'date', 'amount', 'type', 'mean', 'payer_first_name', 'payer_last_name', 'payer_address', 'payer_zip_code', 'payer_city', 'payer_country', 'payer_email', 'payer_society', 'payer_is_society', 'url_receipt')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_string(data->'id')
AND structgen_validate_json_string(data->'date')
AND structgen_validate_json_number(data->'amount')
AND structgen_validate_json_string(data->'type')
AND structgen_validate_json_string(data->'mean')
AND structgen_validate_json_string(data->'payer_first_name')
AND structgen_validate_json_string(data->'payer_last_name')
AND structgen_validate_json_string(data->'payer_address')
AND structgen_validate_json_string(data->'payer_zip_code')
AND structgen_validate_json_string(data->'payer_city')
AND structgen_validate_json_string(data->'payer_country')
AND structgen_validate_json_string(data->'payer_email')
AND structgen_validate_json_string(data->'payer_society')
AND structgen_validate_json_boolean(data->'payer_is_society')
AND structgen_validate_json_string(data->'url_receipt')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_2194933903 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('HelloAsso')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_struct_4190070452(data->'HelloAsso')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

CREATE TABLE dons (
	id serial PRIMARY KEY,
	valeur real  NOT NULL,
	mode_paiement varchar  CHECK (mode_paiement IN ('ancv', '', 'cb', 'cheque', 'esp', 'helloasso', 'vir')) NOT NULL,
	date_reception date  NOT NULL,
	recu_emis date  NOT NULL,
	infos jsonb  NOT NULL CONSTRAINT infos_structgen_validate_json_struct_2194933903 CHECK (structgen_validate_json_struct_2194933903(infos)),
	remercie boolean  NOT NULL,
	numero varchar  NOT NULL,
	affectation varchar  NOT NULL
);

CREATE TABLE don_donateurs (
	id_don integer  NOT NULL,
	id_personne integer ,
	id_organisme integer 
);

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_3706195042 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('Plage', 'active')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_struct_1788348674(data->'Plage')
AND structgen_validate_json_boolean(data->'active')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

CREATE TABLE equipiers (
	id serial PRIMARY KEY,
	id_camp integer  NOT NULL,
	id_personne integer  NOT NULL,
	roles varchar[] ,
	diplome varchar  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')) NOT NULL,
	appro varchar  CHECK (appro IN ('', 'autre', 'canoe', 'moto', 'sb', 'voile')) NOT NULL,
	presence jsonb  NOT NULL CONSTRAINT presence_structgen_validate_json_struct_3706195042 CHECK (structgen_validate_json_struct_3706195042(presence)),
	invitation_equipier integer  NOT NULL,
	charte integer  CHECK (charte IN (-1, 1, 0)) NOT NULL
);

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

	CREATE OR REPLACE FUNCTION structgen_validate_json_Sexe (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		RETURN jsonb_typeof(data) = 'string' AND data#>>'{}' IN ('', 'F', 'M');
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_2287278961 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('nom_prenom', 'sexe', 'adresse', 'code_postal', 'ville')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_string(data->'nom_prenom')
AND structgen_validate_json_Sexe(data->'sexe')
AND structgen_validate_json_string(data->'adresse')
AND structgen_validate_json_string(data->'code_postal')
AND structgen_validate_json_string(data->'ville')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_array_struct_2287278961 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) = 'null' THEN RETURN TRUE; END IF;
		RETURN jsonb_typeof(data) = 'array'
			AND (SELECT bool_and( structgen_validate_json_struct_2287278961(value) )  FROM jsonb_array_elements(data)) 
			;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

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

CREATE TABLE groupes (
	id serial PRIMARY KEY,
	id_camp integer  NOT NULL,
	nom varchar  NOT NULL,
	plage jsonb  NOT NULL CONSTRAINT plage_structgen_validate_json_struct_1788348674 CHECK (structgen_validate_json_struct_1788348674(plage)),
	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 OR REPLACE FUNCTION structgen_validate_json_struct_3918138841 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('valid', 'id', 'crypted')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_boolean(data->'valid')
AND structgen_validate_json_number(data->'id')
AND structgen_validate_json_string(data->'crypted')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_array_string (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) = 'null' THEN RETURN TRUE; END IF;
		RETURN jsonb_typeof(data) = 'array'
			AND (SELECT bool_and( structgen_validate_json_string(value) )  FROM jsonb_array_elements(data)) 
			;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_3524541810 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('lienid', 'nom', 'prenom', 'sexe', 'mail', 'adresse', 'code_postal', 'ville', 'tels', 'date_naissance', 'pays')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_struct_3918138841(data->'lienid')
AND structgen_validate_json_string(data->'nom')
AND structgen_validate_json_string(data->'prenom')
AND structgen_validate_json_Sexe(data->'sexe')
AND structgen_validate_json_string(data->'mail')
AND structgen_validate_json_string(data->'adresse')
AND structgen_validate_json_string(data->'code_postal')
AND structgen_validate_json_string(data->'ville')
AND structgen_validate_json_array_string(data->'tels')
AND structgen_validate_json_struct_1(data->'date_naissance')
AND structgen_validate_json_string(data->'pays')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_Bus (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		RETURN jsonb_typeof(data) = 'string' AND data#>>'{}' IN ('aller', 'aller_retour', '', 'retour');
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_344989053 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('need', 'mode', 'casque', 'poids', 'taille', 'pointure')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_string(data->'need')
AND structgen_validate_json_string(data->'mode')
AND structgen_validate_json_boolean(data->'casque')
AND structgen_validate_json_number(data->'poids')
AND structgen_validate_json_number(data->'taille')
AND structgen_validate_json_number(data->'pointure')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_3994291279 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('bus', 'materiel_ski')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_Bus(data->'bus')
AND structgen_validate_json_struct_344989053(data->'materiel_ski')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_Semaine (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		RETURN jsonb_typeof(data) = 'string' AND data#>>'{}' IN ('', '1', '2');
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_2131172154 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('semaine', 'statut', 'quotient_familial', 'jour')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_Semaine(data->'semaine')
AND structgen_validate_json_number(data->'statut')
AND structgen_validate_json_number(data->'quotient_familial')
AND structgen_validate_json_array_number(data->'jour')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_3572839532 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('lienid', 'nom', 'prenom', 'date_naissance', 'sexe', 'id_camp', 'options', 'options_prix')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_struct_3918138841(data->'lienid')
AND structgen_validate_json_string(data->'nom')
AND structgen_validate_json_string(data->'prenom')
AND structgen_validate_json_struct_1(data->'date_naissance')
AND structgen_validate_json_Sexe(data->'sexe')
AND structgen_validate_json_number(data->'id_camp')
AND structgen_validate_json_struct_3994291279(data->'options')
AND structgen_validate_json_struct_2131172154(data->'options_prix')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_array_struct_3572839532 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) = 'null' THEN RETURN TRUE; END IF;
		RETURN jsonb_typeof(data) = 'array'
			AND (SELECT bool_and( structgen_validate_json_struct_3572839532(value) )  FROM jsonb_array_elements(data)) 
			;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

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 CONSTRAINT responsable_structgen_validate_json_struct_3524541810 CHECK (structgen_validate_json_struct_3524541810(responsable)),
	participants jsonb  CONSTRAINT participants_structgen_validate_json_array_struct_3572839532 CHECK (structgen_validate_json_array_struct_3572839532(participants)),
	partage_adresses_ok boolean  NOT NULL
);

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  CHECK (kind IN (3, 7, 2, 5, 4, 6, 9, 11, 10, 1, 8, 0)) NOT NULL,
	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  CHECK (distribution IN (0, 1, 2)) NOT NULL,
	guard_kind integer  CHECK (guard_kind IN (3, 7, 2, 5, 4, 6, 9, 11, 10, 1, 8, 0)) NOT NULL
);

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

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

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

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

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_514333156 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('mail', 'adresse', 'code_postal', 'ville', 'pays')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_string(data->'mail')
AND structgen_validate_json_string(data->'adresse')
AND structgen_validate_json_string(data->'code_postal')
AND structgen_validate_json_string(data->'ville')
AND structgen_validate_json_string(data->'pays')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_2953253573 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('pub_ete', 'pub_hiver', 'echo_rocher')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_number(data->'pub_ete')
AND structgen_validate_json_number(data->'pub_hiver')
AND structgen_validate_json_number(data->'echo_rocher')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

CREATE TABLE organismes (
	id serial PRIMARY KEY,
	nom varchar  NOT NULL,
	contact_propre boolean  NOT NULL,
	contact jsonb  NOT NULL CONSTRAINT contact_structgen_validate_json_struct_514333156 CHECK (structgen_validate_json_struct_514333156(contact)),
	id_contact integer ,
	id_contact_don integer ,
	exemplaires jsonb  NOT NULL CONSTRAINT exemplaires_structgen_validate_json_struct_2953253573 CHECK (structgen_validate_json_struct_2953253573(exemplaires))
);

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  CHECK (mode_paiement IN ('ancv', '', 'cb', 'cheque', 'esp', 'helloasso', 'vir')) NOT NULL,
	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 OR REPLACE FUNCTION structgen_validate_json_StatutAttente (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		RETURN jsonb_typeof(data) = '' AND data::int IN (1, 2, 0, 3);
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_3523939619 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('statut', 'raison')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_StatutAttente(data->'statut')
AND structgen_validate_json_string(data->'raison')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_4230945022 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('reduc_equipiers', 'reduc_enfants', 'reduc_speciale')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_number(data->'reduc_equipiers')
AND structgen_validate_json_number(data->'reduc_enfants')
AND structgen_validate_json_number(data->'reduc_speciale')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

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 CONSTRAINT liste_attente_structgen_validate_json_struct_3523939619 CHECK (structgen_validate_json_struct_3523939619(liste_attente)),
	remises jsonb  NOT NULL CONSTRAINT remises_structgen_validate_json_struct_4230945022 CHECK (structgen_validate_json_struct_4230945022(remises)),
	option_prix jsonb  NOT NULL CONSTRAINT option_prix_structgen_validate_json_struct_2131172154 CHECK (structgen_validate_json_struct_2131172154(option_prix)),
	options jsonb  NOT NULL CONSTRAINT options_structgen_validate_json_struct_3994291279 CHECK (structgen_validate_json_struct_3994291279(options)),
	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 OR REPLACE FUNCTION structgen_validate_json_struct_1355495657 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('rubeole', 'varicelle', 'angine', 'oreillons', 'scarlatine', 'coqueluche', 'otite', 'rougeole', 'rhumatisme')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_boolean(data->'rubeole')
AND structgen_validate_json_boolean(data->'varicelle')
AND structgen_validate_json_boolean(data->'angine')
AND structgen_validate_json_boolean(data->'oreillons')
AND structgen_validate_json_boolean(data->'scarlatine')
AND structgen_validate_json_boolean(data->'coqueluche')
AND structgen_validate_json_boolean(data->'otite')
AND structgen_validate_json_boolean(data->'rougeole')
AND structgen_validate_json_boolean(data->'rhumatisme')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_1537811643 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('asthme', 'alimentaires', 'medicamenteuses', 'autres', 'conduite_a_tenir')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_boolean(data->'asthme')
AND structgen_validate_json_boolean(data->'alimentaires')
AND structgen_validate_json_boolean(data->'medicamenteuses')
AND structgen_validate_json_string(data->'autres')
AND structgen_validate_json_string(data->'conduite_a_tenir')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_1240729534 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('nom', 'tel')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_string(data->'nom')
AND structgen_validate_json_string(data->'tel')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

	CREATE OR REPLACE FUNCTION structgen_validate_json_struct_4084551223 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('traitement_medical', 'maladies', 'allergies', 'difficultes_sante', 'recommandations', 'handicap', 'tel', 'medecin', 'last_modif', 'mails')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_boolean(data->'traitement_medical')
AND structgen_validate_json_struct_1355495657(data->'maladies')
AND structgen_validate_json_struct_1537811643(data->'allergies')
AND structgen_validate_json_string(data->'difficultes_sante')
AND structgen_validate_json_string(data->'recommandations')
AND structgen_validate_json_boolean(data->'handicap')
AND structgen_validate_json_string(data->'tel')
AND structgen_validate_json_struct_1240729534(data->'medecin')
AND structgen_validate_json_struct_1(data->'last_modif')
AND structgen_validate_json_array_string(data->'mails')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

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  CHECK (sexe IN ('', 'F', 'M')) NOT NULL,
	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  CHECK (rang_membre_asso IN ('3', '2', '1', '')) NOT NULL,
	quotient_familial integer  NOT NULL,
	cotisation integer[] ,
	eonews boolean  NOT NULL,
	fiche_sanitaire jsonb  NOT NULL CONSTRAINT fiche_sanitaire_structgen_validate_json_struct_4084551223 CHECK (structgen_validate_json_struct_4084551223(fiche_sanitaire)),
	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  CHECK (infos_avant_sejour IN (1, 2, 3, 4, 0)) NOT NULL,
	infos_pendant_sejour integer  CHECK (infos_pendant_sejour IN (1, 2, 3, 4, 0)) NOT NULL,
	hebergement integer  CHECK (hebergement IN (1, 2, 3, 4, 0)) NOT NULL,
	activites integer  CHECK (activites IN (1, 2, 3, 4, 0)) NOT NULL,
	theme integer  CHECK (theme IN (1, 2, 3, 4, 0)) NOT NULL,
	nourriture integer  CHECK (nourriture IN (1, 2, 3, 4, 0)) NOT NULL,
	hygiene integer  CHECK (hygiene IN (1, 2, 3, 4, 0)) NOT NULL,
	ambiance integer  CHECK (ambiance IN (1, 2, 3, 4, 0)) NOT NULL,
	ressenti integer  CHECK (ressenti IN (1, 2, 3, 4, 0)) NOT NULL,
	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 OR REPLACE FUNCTION structgen_validate_json_struct_2301900765 (data jsonb)
		RETURNS boolean
		AS $f$
	BEGIN
		IF jsonb_typeof(data) != 'object' THEN 
			RETURN FALSE;
		END IF;
		RETURN (SELECT bool_and( 
			key IN ('personnes', 'camps', 'inscriptions', 'suivi_camps', 'suivi_dossiers', 'paiements', 'aides', 'equipiers', 'dons')
		) FROM jsonb_each(data))  
		AND structgen_validate_json_number(data->'personnes')
AND structgen_validate_json_number(data->'camps')
AND structgen_validate_json_number(data->'inscriptions')
AND structgen_validate_json_number(data->'suivi_camps')
AND structgen_validate_json_number(data->'suivi_dossiers')
AND structgen_validate_json_number(data->'paiements')
AND structgen_validate_json_number(data->'aides')
AND structgen_validate_json_number(data->'equipiers')
AND structgen_validate_json_number(data->'dons')
		;
	END;
	$f$
	LANGUAGE 'plpgsql'
	IMMUTABLE;

CREATE TABLE users (
	id serial PRIMARY KEY,
	label varchar  NOT NULL,
	mdp varchar  NOT NULL,
	is_admin boolean  NOT NULL,
	modules jsonb  NOT NULL CONSTRAINT modules_structgen_validate_json_struct_2301900765 CHECK (structgen_validate_json_struct_2301900765(modules))
);
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 don_donateurs ADD FOREIGN KEY(id_don) REFERENCES dons ON DELETE CASCADE;
ALTER TABLE don_donateurs ADD FOREIGN KEY(id_personne) REFERENCES personnes ;
ALTER TABLE don_donateurs ADD FOREIGN KEY(id_organisme) REFERENCES organismes ;
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 organismes ADD FOREIGN KEY(id_contact) REFERENCES personnes ;
ALTER TABLE organismes ADD FOREIGN KEY(id_contact_don) REFERENCES personnes ;
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 organismes ADD CHECK(contact_propre <> (id_contact IS NOT NULL));
ALTER TABLE don_donateurs ADD CHECK(id_personne <> null OR id_organisme <> null);
ALTER TABLE don_donateurs ADD CHECK(id_personne = null OR id_organisme = null);
ALTER TABLE don_donateurs ADD UNIQUE(id_don);
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);