-- A passer AVANT le ssql

BEGIN;

-- interdiction (formelle) de creer ou alterer une table ici

delete from statut;
insert into statut (uid, nom) values (1, 'valide');
insert into statut (uid, nom) values (2, 'invalide');
-- change begin uid of object so that the join with object for champs.lst yealds to status value like statut inherits object
insert into object (codecrea) values ('vital_generation.sql');
insert into object (codecrea) values ('vital_generation.sql');

delete from profil where codecrea='vital_generation.sql';
insert into profil (nom, code, codecrea, ref_statut) VALUES ('Administrateur', 'ADM', 'vital_generation.sql', (select uid from statut where nom='valide'));
insert into profil (nom, code, codecrea, ref_statut) VALUES ('Utilisateur', 'USR', 'vital_generation.sql', (select uid from statut where nom='valide'));
insert into profil (nom, code, codecrea, ref_statut) VALUES ('Client', 'CLI', 'vital_generation.sql', (select uid from statut where nom='valide'));
insert into profil (nom, code, codecrea, ref_statut) VALUES ('Comptable', 'CPT', 'vital_generation.sql', (select uid from statut where nom='valide'));

delete from profil_societe where codecrea='vital_generation.sql';
insert into profil_societe (nom, code, codecrea, ref_statut) values ('client','CLI','vital_generation.sql', (select uid from statut where nom='valide'));
insert into profil_societe (nom, code, codecrea, ref_statut) values ('fournisseur','FOU','vital_generation.sql', (select uid from statut where nom='valide'));
insert into profil_societe (nom, code, codecrea, ref_statut) values ('mes_societes','MES','vital_generation.sql', (select uid from statut where nom='valide'));
insert into profil_societe (nom, code, codecrea, ref_statut) values ('autre', 'AUT', 'vital_generation.sql', (select uid from statut where nom='valide'));
insert into profil_societe (nom, code, codecrea, ref_statut) VALUES ('prospect', 'PRO', 'vital_generation.sql', (select uid from statut where nom='valide'));

delete from personne_physique where codecrea='vital_generation.sql';
insert into personne_physique (nom,prenom,code,codecrea,ref_statut) values ('admin','admin','#','vital_generation.sql', (select uid from statut where nom='valide'));

delete from userid where codecrea='vital_generation.sql';
insert into userid (login,pwd,ref_personne_physique,ref_statut, codecrea) select 'admin','secret',(select uid from personne_physique where nom='admin'),(SELECT uid from statut where nom='valide'), 'vital_generation.sql';

insert into profils (ref_profil, ref_userid, ref_statut) values ((select uid FROM profil where code='ADM'), (select uid FROM userid WHERE login='admin' AND codecrea='vital_generation.sql'), (SELECT uid from statut where nom='valide'));
insert into profils (ref_profil, ref_userid, ref_statut) values ((select uid FROM profil where code='USR'), (select uid FROM userid WHERE login='admin' AND codecrea='vital_generation.sql'), (SELECT uid from statut where nom='valide'));

-- données diverses
delete from tva where codecrea='vital_generation.sql';
insert into tva (ref_statut,nom,taux, codecrea) values ((select uid from statut where nom='valide'),'exonéré','0', 'vital_generation.sql');
insert into tva (ref_statut,nom,taux, codecrea) values ((select uid from statut where nom='valide'),'taux général 2003','19.6', 'vital_generation.sql');

delete from devise where codecrea='vital_generation.sql';
insert into devise (ref_statut,nom,taux_euro,symbole, codecrea) values ((select uid from statut where nom='valide'),'Euro','1','€', 'vital_generation.sql');
insert into devise (ref_statut,nom,taux_euro,symbole, codecrea) values ((select uid from statut where nom='valide'),'Franc','0.15244902','Frcs', 'vital_generation.sql');

delete from type_periode where codecrea='vital_generation.sql';
insert into type_periode (ref_statut, nom, interval_period, codecrea) VALUES ((select uid from statut where nom='valide'),'Toutes les heures','1 hour', 'vital_generation.sql');
insert into type_periode (ref_statut, nom, interval_period, codecrea) VALUES ((select uid from statut where nom='valide'),'Tous les jours','1 day', 'vital_generation.sql');
insert into type_periode (ref_statut, nom, interval_period, codecrea) VALUES ((select uid from statut where nom='valide'),'Toutes les semaines','1 week', 'vital_generation.sql');
insert into type_periode (ref_statut, nom, interval_period, codecrea) VALUES ((select uid from statut where nom='valide'),'Tous les mois','1 month', 'vital_generation.sql');

delete from mode_paiement where codecrea='vital_generation.sql';
insert into mode_paiement (ref_statut,nom,codecrea) values ((select uid from statut where nom='valide'),'chèque','vital_generation.sql');
insert into mode_paiement (ref_statut,nom,codecrea) values ((select uid from statut where nom='valide'),'virement','vital_generation.sql');
insert into mode_paiement (ref_statut,nom,codecrea) values ((select uid from statut where nom='valide'),'espèces','vital_generation.sql');
insert into mode_paiement (ref_statut,nom,codecrea) values ((select uid from statut where nom='valide'),'CB','vital_generation.sql');

delete from type_evenement_custom where codecrea='vital_generation.sql';
insert into type_evenement_custom (ref_statut, nom, codecrea) values ((select uid from statut where nom='valide'), 'vacances', 'vital_generation.sql');
insert into type_evenement_custom (ref_statut, nom, codecrea) values ((select uid from statut where nom='valide'), 'RTT', 'vital_generation.sql');
insert into type_evenement_custom (ref_statut, nom, codecrea) values ((select uid from statut where nom='valide'), 'R&D', 'vital_generation.sql');

delete from civilite where codecrea='vital_generation.sql';
insert into civilite (ref_statut, nom, codecrea) values ((select uid from statut where nom='valide'), 'Monsieur', 'vital_generation.sql');
insert into civilite (ref_statut, nom, codecrea) values ((select uid from statut where nom='valide'), 'Madame', 'vital_generation.sql');
insert into civilite (ref_statut, nom, codecrea) values ((select uid from statut where nom='valide'), 'Mademoiselle', 'vital_generation.sql');
insert into civilite (ref_statut, nom, codecrea) values ((select uid from statut where nom='valide'), 'Madame, Monsieur', 'vital_generation.sql');

delete from formule where codecrea='vital_generation.sql';
insert into formule (ref_statut, nom, codecrea) values ((select uid from statut where nom='valide'), 'Cordialement,', 'vital_generation.sql');
insert into formule (ref_statut, nom, codecrea) values ((select uid from statut where nom='valide'), 'Sincèrement,', 'vital_generation.sql');
insert into formule (ref_statut, nom, codecrea) values ((select uid from statut where nom='valide'), 'En vous remerciant de votre compréhension, je vous prie de recevoir, monsieur, mes salutations respectueuses.', 'vital_generation.sql');
insert into formule (ref_statut, nom, codecrea) values ((select uid from statut where nom='valide'), 'Dans cette attente, nous vous prions de bien vouloir agréer, Monsieur, l\'expression de nos sentiments distingués.', 'vital_generation.sql');
insert into formule (ref_statut, nom, codecrea) values ((select uid from statut where nom='valide'), 'En vous souhaitant bonne reception, nous vous prions de bien vouloir agréer, Monsieur, l\'expression de nos sentiments distingués.', 'vital_generation.sql');
insert into formule (ref_statut, nom, codecrea) values ((select uid from statut where nom='valide'), 'En vous remerciant de votre compréhension, je vous prie de recevoir, mademoiselle, mes salutations respectueuses.', 'vital_generation.sql');
insert into formule (ref_statut, nom, codecrea) values ((select uid from statut where nom='valide'), 'En vous remerciant de votre compréhension, je vous prie de recevoir, madame, monsieur, mes salutations respectueuses.', 'vital_generation.sql');
insert into formule (ref_statut, nom, codecrea) values ((select uid from statut where nom='valide'), 'En vous souhaitant bonne reception, nous vous prions de bien vouloir agréer, Madame, Monsieur, l\'expression de nos sentiments distingués.', 'vital_generation.sql');
insert into formule (ref_statut, nom, codecrea) values ((select uid from statut where nom='valide'), 'En vous remerciant de votre compréhension, je vous prie de recevoir, madame, mes salutations respectueuses.', 'vital_generation.sql');

delete from type_achat where codecrea='vital_generation.sql';
insert into type_achat (ref_statut, nom, codecrea) values ((select uid from statut where nom='valide'), 'prestation', 'vital_generation.sql');
insert into type_achat (ref_statut, nom, codecrea) values ((select uid from statut where nom='valide'), 'loyer/charge', 'vital_generation.sql');
insert into type_achat (ref_statut, nom, codecrea) values ((select uid from statut where nom='valide'), 'matériel/fournitures', 'vital_generation.sql');

delete from type_charge where codecrea='vital_generation.sql';
insert into type_charge (ref_statut, nom, codecrea) values ((select uid from statut where nom='valide'), 'tva', 'vital_generation.sql');
insert into type_charge (ref_statut, nom, codecrea) values ((select uid from statut where nom='valide'), 'impôts', 'vital_generation.sql');

END;