DROP TABLE IF EXISTS profile_corps;
DROP TABLE IF EXISTS profile_corps_enum;
DROP TABLE IF EXISTS profile_corps_rank_enum;

CREATE TABLE profile_corps (
  pid INT(11) NOT NULL,
  original_corpsid INT(4) UNSIGNED NOT NULL DEFAULT 1,
  current_corpsid INT(4) UNSIGNED NOT NULL DEFAULT 1,
  rankid INT(4) UNSIGNED NOT NULL DEFAULT 1,
  corps_pub ENUM('private', 'ax', 'public') NOT NULL DEFAULT 'private',
  PRIMARY KEY(pid)
) ENGINE=InnoDB, CHARSET=utf8;


CREATE TABLE profile_corps_enum (
  id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL DEFAULT '',
  abbreviation CHAR(5) NOT NULL DEFAULT '',
  still_exists TINYINT(1) UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY(id),
  UNIQUE KEY(name),
  UNIQUE KEY(abbreviation)
) ENGINE=InnoDB, CHARSET=utf8;


CREATE TABLE profile_corps_rank_enum (
  id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL DEFAULT '',
  abbreviation CHAR(5) NOT NULL DEFAULT '',
  PRIMARY KEY(id),
  UNIQUE KEY(name),
  UNIQUE KEY(abbreviation)
) ENGINE=InnoDB, CHARSET=utf8;

INSERT IGNORE INTO  profile_corps_rank_enum (abbreviation, name)
            VALUES  (" ", "Aucun"),
                    ("A", "Amiral"),
                    ("A2", "Amiral 2e Son"),
                    ("ADC", "Adm.Civil"),
                    ("ADC2", "Adm.Civil retr."),
                    ("ADC3", "Adm.Civil cg."),
                    ("ADC4", "Adm.Civil dém."),
                    ("ADC5", "Adm.Civil dét."),
                    ("ADC6", "Adm.Civil dis."),
                    ("ADC7", "Adm.Civil (H.C)"),
                    ("ADC8", "Adm.Civil h.cl."),
                    ("ADHC", "Adm. h.cl.INSEE"),
                    ("ADHC2", "Adm.h.cl.INSEE retr."),
                    ("ADHC3", "Adm.h.cl.INSEE cg."),
                    ("ADHC4", "Adm.h.cl.INSEE dém."),
                    ("ADHC5", "Adm.h.cl.INSEE dét."),
                    ("ADHC6", "Adm.h.cl.INSEE dis."),
                    ("ADHC7", "Adm.h.cl.INSEE (H.C.)"),
                    ("ADIN", "Adm.INSEE"),
                    ("ADIN2", "Adm.INSEE retr."),
                    ("ADIN3", "Adm.INSEE cg"),
                    ("ADIN4", "Adm.INSEE dém."),
                    ("ADIN5", "Adm.INSEE dét."),
                    ("ADIN6", "Adm.INSEE dis."),
                    ("ADIN7", "Adm.INSEE (H.C.)"),
                    ("CA", "Contre-Amiral"),
                    ("CA2", "Contre-Amiral 2e Son"),
                    ("CAA", "Contrôleur des Armées"),
                    ("CB", "Chef de bataillon"),
                    ("CB2", "Chef de bataillon retr."),
                    ("CB8", "Chef de bataillon rés."),
                    ("CBH", "Chef de bataillon"),
                    ("CC", "Capitaine de Corvette"),
                    ("CC2", "Capitaine de Corvette retr."),
                    ("CC8", "Capitaine de Corvette rés."),
                    ("CCA", "Commiss. Contr. Ass."),
                    ("CCA2", "Commiss. Contr. Ass. retr."),
                    ("CCA3", "Commiss. Contr. Ass. cg."),
                    ("CCA4", "Commiss. Contr. Ass. dém."),
                    ("CCA5", "Commiss. Contr. Ass. dét."),
                    ("CCA6", "Commiss. Contr. Ass. dis."),
                    ("CCA7", "Commiss. Contr. Ass. (HC)"),
                    ("CCCA", "Commiss. Contr. Ch. Ass."),
                    ("CCCA2", "Commiss. Contr. Ch. Ass. retr."),
                    ("CCCA3", "Commiss. Contr. Ch. Ass. cg."),
                    ("CCCA4", "Commiss. Contr. Ch. Ass. dém."),
                    ("CCCA5", "Commiss. Contr. Ch. Ass. dét."),
                    ("CCCA6", "Commiss. Contr. Ch. Ass. dis."),
                    ("CCCA7", "Commiss. Contr. Ch. Ass. (HC)"),
                    ("CCGA", "Commiss. Contr. Gén. Ass."),
                    ("CCGA2", "Commiss. Contr. Gén. Ass.retr."),
                    ("CCGA3", "Commiss. Contr. Gén. Ass. cg."),
                    ("CCGA4", "Commiss. Contr. Gén. Ass. dém."),
                    ("CCGA5", "Commiss. Contr. Gén. Ass. dét."),
                    ("CCGA6", "Commiss. Contr. Gén. Ass. dis."),
                    ("CCGA7", "Commiss. Contr. Gén. Ass. (HC)"),
                    ("CCH", "Capitaine de Corvette hon."),
                    ("Cdt", "Commandant"),
                    ("Cdt2", "Commandant retr."),
                    ("Cdt5", "Commandant dém."),
                    ("Cdt8", "Commandant de rés."),
                    ("CE", "Chef d'Escadron"),
                    ("CE2", "Chef d'Escadron retr."),
                    ("CE3", "Chef d'Escadrons"),
                    ("CE4", "Chef d'Escadrons"),
                    ("CE5", "Chef d'Escadrons de rés."),
                    ("CF", "Capitaine de Frégate"),
                    ("CF2", "Capitaine de Frégate"),
                    ("CF9", "Capitaine de Frégate (CR)"),
                    ("CFC1", "Ing.ch.SNCF"),
                    ("CFC11", "Ing.ch.h.cl.SNCF"),
                    ("CFC2", "Ing.ch.retr.SNCF"),
                    ("CFC3", "Ing.ch.hon.SNCF"),
                    ("CFC31", "Ing.ch.h.cl.SNCF"),
                    ("CFC4", "Ing.ch.H.C.hon.SNCF"),
                    ("CFC5", "Ing.ch.h.cl.hon.SNCF"),
                    ("CFC6", "Ing.ch.h.cl.retr.SNCF"),
                    ("CFD1", "Direct.Gén.SNCF"),
                    ("CFD2", "Direct.Gén.hon.SNCF"),
                    ("CFD3", "Direct.SNCF"),
                    ("CFD4", "Direct.hon.SNCF"),
                    ("CFD5", "Direct.retr.SNCF"),
                    ("CFD6", "DGA hon.SNCF"),
                    ("CFG1", "Ing.gén.SNCF"),
                    ("CFG2", "Ing.gén.retr.SNCF"),
                    ("CFG3", "Ing.gén.hon.SNCF"),
                    ("CFH", "Capitaine de Frégate hon."),
                    ("CFP1", "Ing.pp.SNCF"),
                    ("CFP2", "Ing.pp.retr.SNCF"),
                    ("CFP3", "Ing.pp.hon.SNCF"),
                    ("CFP4", "Ing.pp.H.C.hon.SNCF"),
                    ("CFP5", "Ing.pp.h.cl.hon.SNCF"),
                    ("CFP6", "Ing.pp.h.cl.SNCF"),
                    ("CGA", "Contrôleur Général des Armées"),
                    ("CGA2", "Contrôl.Gén.des Armées 2e Son"),
                    ("Cne", "Capitaine"),
                    ("Cne2", "Capitaine retr."),
                    ("Cne5", "Capitaine dém."),
                    ("Cne6", "Capitaine dis."),
                    ("Cne8", "Capitaine de rés."),
                    ("Col", "Colonel"),
                    ("Col2", "Colonel retr."),
                    ("Col3", "Colonel Brth retr."),
                    ("Col4", "Colonel (Air) retr."),
                    ("Col5", "Colonel hon.(Air)"),
                    ("Colh", "Colonel hon."),
                    ("COM1C", "Commiss.1e cl.Marine"),
                    ("COM2", "Commiss.Marine retr."),
                    ("COM2C", "Commiss.2e cl.Marine"),
                    ("COM4", "Commiss.Marine dém."),
                    ("COM8", "Commiss.Marine rés."),
                    ("COMC", "Commiss.ch.Marine"),
                    ("COMC1", "Commiss.ch.1e cl.Marine"),
                    ("COMC2", "Commiss.ch.Marine retr."),
                    ("COMC8", "Commiss.ch.Marine rés."),
                    ("COMD", "Commiss.Cdt"),
                    ("COMG", "Comm.Gén.Mar.2e Son"),
                    ("COMG1", "Comm.Gén.1e cl.Mar.2e Son"),
                    ("COMG2", "Comm.Gén.2e cl.Mar.2e Son"),
                    ("COMGP", "Commiss.gén.aux prix hon."),
                    ("COMP", "Commiss.pp.Marine"),
                    ("COMP2", "Commiss.pp Marine retr."),
                    ("COMP8", "Commiss.pp.Marine rés."),
                    ("CONE", "Contrôl.Gén Eco.et Financ."),
                    ("CONE2", "Contrôl.Gén Eco.et Financ.ret"),
                    ("CONE3", "Contrôl.Gén Eco.et Financ.cg."),
                    ("CONE4", "Contrôl.Gén Eco.et Financ.dém."),
                    ("CONE5", "Contrôl.Gén Eco.et Financ.dét."),
                    ("CONE6", "Contrôl.Gén Eco.et Financ. dis"),
                    ("CONE7", "Contrôl.Gén Eco.et Financ.H.C."),
                    ("CONEH", "Contrôl.Gén Eco.et Financ.hon."),
                    ("CV", "Capitaine de Vaisseau"),
                    ("CV2", "Capitaine de Vaisseau retr."),
                    ("CV9", "Capitaine de Vaisseau (CR)"),
                    ("CVH", "Capitaine de Vaisseau hon."),
                    ("DRT", "Direct.rég.Téléc."),
                    ("DRT2", "Direct.rég.Téléc. retr."),
                    ("DRT3", "Direct.rég.Téléc. cg."),
                    ("DRT4", "Direct.rég.Téléc. dém."),
                    ("DRT5", "Direct.rég.Téléc. dét."),
                    ("DRT6", "Direct.rég.Téléc. dis."),
                    ("DRT7", "Direct.rég.Téléc. (H.C.)"),
                    ("DRT8", "Direct.rég.Téléc. hon."),
                    ("EF2", "Ing.EDF retr."),
                    ("EF3", "Ing.EDF cg."),
                    ("EF4", "Ing.EDF dém."),
                    ("EF5", "Ing.EDF dét."),
                    ("EF6", "Ing.EDF dis."),
                    ("EFC2", "Ing.ch.EDF retr."),
                    ("EFC3", "Ing.ch.EDF cg."),
                    ("EFC4", "Ing.ch.EDF dém."),
                    ("EFC5", "Ing.ch.EDF dét."),
                    ("EFC6", "Ing.ch.EDF dis."),
                    ("EFC7", "Ing.ch.EDF (HC)"),
                    ("EFCO1", "Contrôl.gén.hon.EDF"),
                    ("EFD", "Direct. EDF"),
                    ("EFD2", "Direct.hon.EDF"),
                    ("EFDG", "Direct.Gén. EDF"),
                    ("EFDG2", "Direct.Gén.hon.EDF"),
                    ("EFG2", "Ing.gén.EDF retr."),
                    ("EFGH", "Insp.gén.hon.EDF"),
                    ("EFP2", "Ing.pp.EDF retr."),
                    ("EFP3", "Ing.pp.EDF cg."),
                    ("EFP4", "Ing.pp.EDF dém."),
                    ("EFP5", "Ing.pp.EDF dét."),
                    ("EFP6", "Ing.pp.EDF dis."),
                    ("EFP7", "Ing.pp.EDF (HC)"),
                    ("EFS", "Insp.gén.EDF"),
                    ("EFS2", "Insp.gén.EDF retr."),
                    ("EFS3", "Insp.gén.hon. EDF"),
                    ("ESC", "Ing.ch.des Essences"),
                    ("ESC10", "Ing.ch. 1e cl.des Essence CR"),
                    ("ESC2", "Ing.ch.des Essences retr."),
                    ("ESC3", "Ing.ch.des Essences cg."),
                    ("ESC4", "Ing.ch.des Essences dém."),
                    ("ESC5", "Ing.ch.des Essences dét."),
                    ("ESC6", "Ing.ch.des Essences dis."),
                    ("ESC7", "Ing.ch.des Essences (H.C.)"),
                    ("ESC9", "Ing.ch.des Essences CR"),
                    ("ESG", "Ing.gén.des Essences"),
                    ("ESG2", "Ing.gén.des Essences retr."),
                    ("ESG3", "Ing.gén.des Essences cg."),
                    ("ESG4", "Ing.gén.des Essences dém."),
                    ("ESG5", "Ing.gén.des Essences dét."),
                    ("ESG6", "Ing.gén.des Essences dis."),
                    ("ESG7", "Ing.gén.des Essences (H.C.)"),
                    ("ESI", "Ing.des Essences"),
                    ("ESI2", "Ing.des Eseences retr."),
                    ("ESI3", "Ing.des Essences cg."),
                    ("ESI4", "Ing.des Essences dém."),
                    ("ESI5", "Ing.des Essences dét."),
                    ("ESI6", "Ing.des Essences dis."),
                    ("ESI7", "Ing.des Essences (H.C.)"),
                    ("EV", "Enseigne de Vaisseau"),
                    ("GA", "Général d'Armée"),
                    ("GA2", "Général d'Armée 2e Son"),
                    ("GAE", "Général d'Armée Aér."),
                    ("GAE2", "Général d'Armée Aér.2e Son"),
                    ("GB", "Général de Brigade"),
                    ("GB2", "Général de Brigade 2e Son"),
                    ("GBAE", "Général de Brigade Aér."),
                    ("GBAE2", "Général de Brig.Aér.2e Son"),
                    ("GCA", "Général de C.A."),
                    ("GCA2", "Général de C.A. 2e Son"),
                    ("GCAE", "Général de C.A.Aér."),
                    ("GCAE2", "Général de C.A.Aér. 2e Son"),
                    ("GD", "Général de Division"),
                    ("GD2", "Général de Division 2e Son"),
                    ("GDAE", "Général de Division Aér."),
                    ("GDAE2", "Général de Div.Aér. 2e Son"),
                    ("GDFC2", "Contrôleur gén.GDF retr."),
                    ("GDFD3", "Direct.hon.GDF"),
                    ("GDFI", "Insp.gén.hon.GDF"),
                    ("GM2", "Ing. Arm. (GM) retr."),
                    ("GM3", "Ing. Arm. (GM) cg."),
                    ("GM4", "Ing. Arm. (GM) dém."),
                    ("GM7", "Ing. Arm. (GM) H.C"),
                    ("GM8", "Ing. Arm. (GM) rés."),
                    ("GM9", "Ing. Arm. (GM) CR"),
                    ("GMC", "Ing.ch.Arm.(GM)"),
                    ("GMC1", "Ing.ch.Arm (GM)"),
                    ("GMC2", "Ing.ch.Arm.(GM) retr."),
                    ("GMC3", "Ing.ch.Arm.(GM) cg."),
                    ("GMC4", "Ing.ch.Arm.(GM) dém."),
                    ("GMC7", "Ing.ch.Arm.(GM) H.C"),
                    ("GMC8", "Ing.ch.Arm.(GM) rés."),
                    ("GMC9", "Ing.ch.Arm.(GM) CR"),
                    ("GMG", "Ing.Gén.Arm.(GM)"),
                    ("GMG02", "Ing.Gén.Arm.(GM) 2e Son"),
                    ("GMG1", "Ing.Gén.1e cl.Arm.(GM) 2e Son"),
                    ("GMG11", "Ing.Gén.1e cl.Arm.(GM)"),
                    ("GMG2", "Ing.Gén.2e cl.Arm.(GM) 2e Son"),
                    ("GMG22", "Ing.Gén.2e cl.Arm.(GM)"),
                    ("GMP", "Ing.pp.Arm.(GM)"),
                    ("GMP2", "Ing.pp.Arm.(GM) retr."),
                    ("GMP3", "Ing.pp.Arm.(GM) cg."),
                    ("GMP4", "Ing.pp.Arm.(GM) dém."),
                    ("GMP7", "Ing.pp.Arm.(GM) H.C"),
                    ("GMP8", "Ing.pp.Arm.(GM) rés."),
                    ("GMP9", "Ing.pp.Arm.(GM) CR"),
                    ("GMT12", "Ing.Gén.1e cl. Mat. 2e Son"),
                    ("GMT2", "Ing.Gén.du Mat. 2e Son"),
                    ("GMT22", "Ing.Gén.2e cl. Mat. 2e Son"),
                    ("HG02", "Ing.Gén.Arm.(H) 2e Son"),
                    ("IA", "Ing.Arm."),
                    ("IA2", "Ing.Arm. retr."),
                    ("IA3", "Ing.Arm. cg."),
                    ("IA4", "Ing.Arm. dém."),
                    ("IA5", "Ing.Arm. dét."),
                    ("IA6", "Ing.Arm. dis."),
                    ("IA7", "Ing.Arm. (H.C.)"),
                    ("IA8", "Ing.Arm. rés."),
                    ("IA9", "Ing.Arm. (CR)"),
                    ("IAC8", "Ing.ch.Arm. rés."),
                    ("IAv", "Ing.Av.C."),
                    ("IAv2", "Ing.Av.C. retr."),
                    ("IAV3", "Ing.Av.C. cg."),
                    ("IAv4", "Ing.Av.C. dém."),
                    ("IAv5", "Ing.Av.C. dét."),
                    ("IAv6", "Ing.Av.C. dis."),
                    ("IAv7", "Ing.Av.C. (H.C.)"),
                    ("ICA", "Ing.ch.Arm."),
                    ("ICA2", "Ing.ch.Arm.retr."),
                    ("ICA3", "Ing.ch.Arm. cg."),
                    ("ICA4", "Ing.ch.Arm. dém."),
                    ("ICA5", "Ing.ch.Arm. dét."),
                    ("ICA6", "Ing.ch.Arm. dis"),
                    ("ICA7", "Ing.ch.Arm. (H.C.)"),
                    ("ICA8", "Ing.ch.Arm.rés."),
                    ("ICA9", "Ing.ch.Arm. (CR)"),
                    ("ICAv", "Ing.ch.Av.C."),
                    ("ICAv2", "Ing.ch.Av.C. retr."),
                    ("ICAv3", "Ing.ch.Av.C. cg."),
                    ("ICAv4", "Ing.ch.Av.C. dém."),
                    ("ICAv5", "Ing.ch.Av.C. dét."),
                    ("ICAv6", "Ing.ch.Av.C. dis."),
                    ("ICAv7", "Ing.ch.Av.C. (H.C.)"),
                    ("ICG", "Ing.ch.GREF"),
                    ("ICG2", "Ing.ch.GREF retr."),
                    ("ICG3", "Ing.ch.GREF cg."),
                    ("ICG4", "Ing.ch.GREF dém."),
                    ("ICG5", "Ing.ch.GREF dét."),
                    ("ICG6", "Ing.ch.GREF dis."),
                    ("ICG7", "Ing.ch.GREF (H.C.)"),
                    ("ICGO", "Ing.ch.Géog."),
                    ("ICGO2", "Ing.ch.Géog. retr."),
                    ("ICGO3", "Ing.ch.Géog. cg"),
                    ("ICGO4", "Ing.ch.Géog. dém."),
                    ("ICGO5", "Ing.ch.Géog. dét."),
                    ("ICGO6", "Ing.ch.Géog. dis."),
                    ("ICGO7", "Ing.ch.Géog. (H.C.)"),
                    ("ICIM", "Ing.ch.I.M."),
                    ("ICIM2", "Ing.ch.I.M. retr."),
                    ("ICIM3", "Ing.ch.I.M. cg."),
                    ("ICIM4", "Ing.ch.I.M. dém."),
                    ("ICIM5", "Ing.ch.I.M. dét."),
                    ("ICIM6", "Ing.ch.I.M. dis."),
                    ("ICIM7", "Ing.ch.I.M. (H.C.)"),
                    ("ICM", "Ing.ch.Mines"),
                    ("ICM2", "Ing.ch.Mines retr."),
                    ("ICM3", "Ing.ch.Mines cg."),
                    ("ICM4", "Ing.ch.Mines dém."),
                    ("ICM5", "Ing.ch.Mines dét."),
                    ("ICM6", "Ing.ch.Mines dis."),
                    ("ICM7", "Ing.ch.Mines (H.C.)"),
                    ("ICME", "Ing.ch.M.E."),
                    ("ICME2", "Ing.ch.M.E. retr."),
                    ("ICME3", "Ing.ch.M.E. cg."),
                    ("ICME4", "Ing.ch.M.E. dém."),
                    ("ICME5", "Ing.ch.M.E. dét."),
                    ("ICME6", "Ing.ch.M.E. dis."),
                    ("ICME7", "Ing.ch.M.E (H.C.)"),
                    ("ICMO", "Ing.ch.Météo."),
                    ("ICMO2", "Ing.ch.Météo.retr."),
                    ("ICMO3", "Ing.ch.Météo.cg"),
                    ("ICMO4", "Ing.ch.Météo.dém."),
                    ("ICMO5", "Ing.ch.Météo.dét."),
                    ("ICMO6", "Ing.ch.Météo.dis"),
                    ("ICMO7", "Ing.ch.Météo. (H.C.)"),
                    ("ICMT2", "Ing.ch.Mat. retr."),
                    ("ICMT3", "Ing.ch.1e cl. Mat hon."),
                    ("ICMT4", "Ing.ch.2e cl. Mat. hon."),
                    ("ICPC", "Ing.ch.P.C."),
                    ("ICPC2", "Ing.ch.P.C. retr."),
                    ("ICPC3", "Ing.ch.P.C. cg."),
                    ("ICPC4", "Ing.ch.P.C. dém."),
                    ("ICPC5", "Ing.ch.P.C. dét."),
                    ("ICPC6", "Ing.ch.P.C. dis."),
                    ("ICPC7", "Ing.ch.P.C. (H.C.)"),
                    ("ICT", "Ing.ch.Téléc."),
                    ("ICT2", "Ing.ch.Téléc. retr."),
                    ("ICT3", "Ing.ch.Téléc. cg."),
                    ("ICT4", "Ing.ch.Téléc. dém."),
                    ("ICT5", "Ing.ch.Téléc. dét."),
                    ("ICT6", "Ing.ch.Téléc. dis."),
                    ("ICT7", "Ing.ch.Téléc. (H.C.)"),
                    ("ICTH", "Ing.ch.Téléc. hon."),
                    ("IFi", "Insp.des Finances"),
                    ("IFi2", "Insp.des Finances retr."),
                    ("IFi3", "Insp.des Finances cg."),
                    ("IFi4", "Insp.des Finances dém."),
                    ("IFi5", "Insp.des Finances dét."),
                    ("IFi6", "Insp.des Finances dis."),
                    ("IFi7", "Insp.des Finances (H.C.)"),
                    ("IG", "Ing.GREF"),
                    ("IG11", "Ing.Gén.1e cl.Arm."),
                    ("IG12", "Ing.Gén.1e cl.Arm. 2e Son"),
                    ("IG13", "Ing.Gén.1e cl.Arm. cg."),
                    ("IG15", "Ing.Gén.1e cl.Arm. dét."),
                    ("IG16", "Ing.Gén.1e cl.Arm. dis."),
                    ("IG17", "Ing.Gén.1e cl.Arm. (H.C.)"),
                    ("IG19", "Ing.Gén.1e cl.Arm. (CR)"),
                    ("IG2", "Ing.GREF retr."),
                    ("IG21", "Ing.Gén.2e cl.Arm."),
                    ("IG22", "Ing.Gén.2e cl.Arm. 2e Son"),
                    ("IG23", "Ing.Gén.2e cl.Arm. cg."),
                    ("IG25", "Ing.Gén.2e cl.Arm. dét."),
                    ("IG26", "Ing.Gén.2e cl.Arm. dis."),
                    ("IG27", "Ing.Gén.2e cl.Arm. (H.C.)"),
                    ("IG29", "Ing.Gén.2e cl.Arm. (CR)"),
                    ("IG3", "Ing.GREF cg."),
                    ("IG4", "Ing.GREF dém."),
                    ("IG5", "Ing.GREF dét."),
                    ("IG6", "Ing.GREF dis."),
                    ("IG7", "Ing.GREF (H.C.)"),
                    ("IGA", "Ing.gén.Arm."),
                    ("IGA2", "Ing.gén.Arm. 2e Son"),
                    ("IGA3", "Ing.gén.Arm. cg."),
                    ("IGA5", "Ing.gén.Arm. dét."),
                    ("IGA6", "Ing.gén.Arm. dis."),
                    ("IGA7", "Ing.gén.Arm. (H.C.)"),
                    ("IGA8", "Ing.gén.Arm.(C.R.)"),
                    ("IGAv", "Ing.gén.Av.C."),
                    ("IGAv2", "Ing.gén.Av.C. retr."),
                    ("IGAv3", "Ing.gén.Av.C. cg."),
                    ("IGAv4", "Ing.gén.Av.C. dém."),
                    ("IGAv5", "Ing.gén.Av.C. dét."),
                    ("IGAv6", "Ing.gén.Av.C. dis"),
                    ("IGAv7", "Ing.gén.Av.C. (H.C.)"),
                    ("IGE", "Ing.gén.de cl.except.Arm."),
                    ("IGE2", "Ing.gén.de cl.except.Arm.2eSon"),
                    ("IGFi", "Insp.gén.des Finances"),
                    ("IGFi2", "Insp.gén.des Finances retr."),
                    ("IGFi3", "Insp.gén.des Finances cg."),
                    ("IGFi4", "Insp.gén.des Finances dém."),
                    ("IGFi5", "Insp.gén.des Finances dét."),
                    ("IGFi6", "Insp.gén.des Finances dis."),
                    ("IGFi7", "Insp.gén.des Finances (H.C.)"),
                    ("IGG", "Ing.gén.GREF"),
                    ("IGG2", "Ing.gén.GREF retr."),
                    ("IGG3", "Ing.gén.GREF cg."),
                    ("IGG4", "Ing.gén.GREF dém."),
                    ("IGG5", "Ing.gén.GREF dét."),
                    ("IGG6", "Ing.gén.GREF dis."),
                    ("IGG7", "Ing.gén.GREF (H.C.)"),
                    ("IGGO", "Ing.gén.Géog."),
                    ("IGGO2", "Ing.gén.Géog. retr."),
                    ("IGGO3", "Ing.gén.Géog. cg."),
                    ("IGGO4", "Ing.gén.Géog. dém."),
                    ("IGGO5", "Ing.gén.Géog. dét."),
                    ("IGGO6", "Ing.gén.Géog. dis."),
                    ("IGGO7", "Ing.gén.Géog. (H.C.)"),
                    ("IGH", "Ing.gén.hors cl. Arm."),
                    ("IGH2", "Ing.gén.hors cl.Arm.2e Son"),
                    ("IGIM", "Ing.gén.I.M."),
                    ("IGIM2", "Ing.gén.I.M. retr."),
                    ("IGIN", "Insp.gén.INSEE"),
                    ("IGIN2", "Insp.gén.INSEE retr."),
                    ("IGIN3", "Insp.gén.INSEE cg."),
                    ("IGIN4", "Insp.gén.INSEE dém."),
                    ("IGIN5", "Insp.gén.INSEE dét."),
                    ("IGIN6", "Insp.gén.INSEE dis."),
                    ("IGIN7", "Insp.gén.INSEE (H.C.)"),
                    ("IGIN8", "Insp.gén.INSEE hon."),
                    ("IGM", "Ing.gén.Mines"),
                    ("IGM2", "Ing.gén.Mines retr."),
                    ("IGM3", "Ing.gén.Mines cg."),
                    ("IGM4", "Ing.gén.Mines dém."),
                    ("IGM5", "Ing.gén.Mines dét."),
                    ("IGM6", "Ing.gén.Mines dis."),
                    ("IGM7", "Ing.gén.Mines (H.C.)"),
                    ("IGME", "Insp.gén.M.E."),
                    ("IGME2", "Insp.gén.M.E. retr."),
                    ("IGME3", "Insp.gén.M.E. cg"),
                    ("IGME4", "Insp.gén.M.E. dém."),
                    ("IGME5", "Insp.gén.M.E. dét."),
                    ("IGME6", "Insp.gén.M.E. dis."),
                    ("IGME7", "Insp.gén.M.E.(H.C)."),
                    ("IGMO", "Ing.gén.Météo."),
                    ("IGMO2", "Ing.gén.Météo. retr."),
                    ("IGMO3", "Ing.gén.Météo. cg."),
                    ("IGMO4", "Ing.gén.Météo. dém."),
                    ("IGMO5", "Ing.gén.Météo. dét."),
                    ("IGMO6", "Ing.gén.Météo. dis."),
                    ("IGMO7", "Ing.gén.Météo. (H.C.)"),
                    ("IGO", "Ing.Géog."),
                    ("IGO2", "Ing.Géog. retr."),
                    ("IGO3", "Ing.Géog. cg."),
                    ("IGO4", "Ing.Géog. dém."),
                    ("IGO5", "Ing.Géog. dét."),
                    ("IGO6", "Ing.Géog. dis."),
                    ("IGO7", "Ing.Géog. (H.C.)"),
                    ("IGPC", "Ing.gén.P.C."),
                    ("IGPC2", "Ing.gén.P.C. hon."),
                    ("IGPC3", "Ing.gén.P.C. cg."),
                    ("IGPC4", "Ing.gén.P.C. dém."),
                    ("IGPC5", "Ing.gén.P.C. dét."),
                    ("IGPC6", "Ing.gén.P.C. dis."),
                    ("IGPC7", "Ing.gén.P.C. (H.C.)"),
                    ("IGT", "Ing.gén.Téléc."),
                    ("IGT12", "Ing.gén.1e cl.Téléc. retr."),
                    ("IGT2", "Ing.gén.Téléc. retr."),
                    ("IGT22", "Ing.gén.2e cl.Téléc. retr."),
                    ("IGT3", "Ing.gén.Téléc. cg."),
                    ("IGT4", "Ing.gén.Téléc. dém."),
                    ("IGT5", "Ing.gén.Téléc. dét."),
                    ("IGT6", "Ing.gén.Téléc. dis."),
                    ("IGT7", "Ing.gén.Téléc.(H.C.)"),
                    ("IGTH", "Ing.gén.Téléc. hon."),
                    ("IIM", "Ing.I.M."),
                    ("IIM2", "Ing.I.M. retr."),
                    ("IIM3", "Ing.I.M. cg."),
                    ("IIM4", "Ing.I.M. dém."),
                    ("IIM5", "Ing.I.M. dét."),
                    ("IIM6", "Ing.I.M. dis."),
                    ("IIM7", "Ing.I.M. (H.C.)"),
                    ("IM", "Ing.Mines"),
                    ("IM2", "Ing.Mines retr."),
                    ("IM3", "Ing.Mines cg."),
                    ("IM4", "Ing.Mines dém."),
                    ("IM5", "Ing.Mines dét."),
                    ("IM6", "Ing.Mines dis."),
                    ("IM7", "Ing.Mines (H.C.)"),
                    ("IME", "Ing.M.E."),
                    ("IME2", "Ing.M.E. retr."),
                    ("IME3", "Ing.M.E. cg."),
                    ("IME4", "Ing.M.E. dém."),
                    ("IME5", "Ing.M.E. dét."),
                    ("IME6", "Ing.M.E. dis."),
                    ("IME7", "Ing.M.E (H.C.)"),
                    ("IMO", "Ing.Météo."),
                    ("IMO2", "Ing.Météo. retr."),
                    ("IMO3", "Ing.Météo. cg."),
                    ("IMO4", "Ing.Météo.dém."),
                    ("IMO5", "Ing.Météo. dét."),
                    ("IMO6", "Ing.Météo. dis."),
                    ("IMO7", "Ing.Météo. (H.C.)"),
                    ("ININ", "Insp.INSEE"),
                    ("ININ2", "Insp.INSEE retr."),
                    ("ININ3", "Insp.INSEE cg."),
                    ("ININ4", "Insp.INSEE dém."),
                    ("ININ5", "Insp.INSEE dét."),
                    ("ININ6", "Insp.INSEE dis."),
                    ("ININ7", "Insp.INSEE (H.C.)"),
                    ("INT12", "Intent.Milit.1e cl. retr."),
                    ("INT22", "Intent.Milit.2e cl. retr."),
                    ("INTG", "Intent.Gén.2e Son"),
                    ("INTG1", "Intent.Gén.1ere cl.2e Son"),
                    ("INTG2", "Intent.Gén.2e cl.2e Son"),
                    ("IPA", "Ing.pp.Arm."),
                    ("IPA2", "Ing.pp.Arm.retr."),
                    ("IPA3", "Ing.pp.Arm. cg."),
                    ("IPA4", "Ing.pp.Arm. dém."),
                    ("IPA5", "Ing.pp.Arm. dét."),
                    ("IPA6", "Ing.pp.Arm. dis."),
                    ("IPA7", "Ing.pp.Arm. (H.C.)"),
                    ("IPA8", "Ing.pp.Arm. rés."),
                    ("IPA9", "Ing.pp.Arm. (CR)"),
                    ("IPC", "Ing.P.C."),
                    ("IPC2", "Ing.P.C. retr."),
                    ("IPC3", "Ing.P.C. cg."),
                    ("IPC4", "Ing.P.C. dém."),
                    ("IPC5", "Ing.P.C. dét."),
                    ("IPC6", "Ing.P.C. dis."),
                    ("IPC7", "Ing.P.C. (H.C.)"),
                    ("IPG", "Ing.pp.GREF"),
                    ("IPG2", "Ing.pp.GREF retr."),
                    ("IPG3", "Ing.pp.GREF cg."),
                    ("IPG4", "Ing.pp.GREF dém."),
                    ("IPG5", "Ing.pp.GREF dét."),
                    ("IPG6", "Ing.pp.GREF dis."),
                    ("IPG7", "Ing.pp.GREF (H.C.)"),
                    ("ISG2", "Insp.pp.des E.et F.OM.retr."),
                    ("IT", "Ing.Téléc."),
                    ("IT2", "Ing.Téléc. retr."),
                    ("IT3", "Ing.Téléc. cg."),
                    ("IT4", "Ing.Téléc. dém."),
                    ("IT5", "Ing.Téléc. dét."),
                    ("IT6", "Ing.Téléc. dis."),
                    ("IT7", "Ing.Téléc. (H.C.)"),
                    ("L.Co", "Lieut.-Colonel"),
                    ("L.Co2", "Lieut.-Colonel retr."),
                    ("L.Co5", "Lieut.-Colonel dém."),
                    ("L.Co8", "Lieut.-Colonel rés."),
                    ("L.Coh", "Lieut.-Colonel hon."),
                    ("Lt", "Lieutenant"),
                    ("Lt6", "Lieutenant dis."),
                    ("Lt8", "Lieutenant rés."),
                    ("LV", "Lieutenant de Vaisseau"),
                    ("NAéG2", "Ing.gén.N.Aé.retr"),
                    ("VA", "Vice-Amiral"),
                    ("VA2", "Vice-Amiral 2e Son"),
                    ("VAE", "Vice-Amiral d'Escadre"),
                    ("VAE2", "Vice-Amiral d'Escadre 2e Son");

INSERT IGNORE INTO  profile_corps_enum (abbreviation, name, still_exists)
            VALUES  ("D", "Aucun (anc. démissionnaire)", 1),
                    ("Ad.C", "Adm. des Colonies", 0),
                    ("Agr.C", "Agriculture aux Colonies", 0),
                    ("Z", "Ancien élève étranger", 0),
                    ("Aé", "Arm.Aéronautique", 0),
                    ("ABC", "Arme blindée", 0),
                    ("Arm", "Ingénieurs de l'Armement", 1),
                    ("A", "Artillerie", 0),
                    ("AC", "Artillerie coloniale", 0),
                    ("AM", "Artillerie de Marine", 0),
                    ("AN", "Artillerie Navale", 0),
                    ("Av.C", "Aviation Civile", 0),
                    ("B", "Bourse d'études", 0),
                    ("Cav", "Cavalerie", 0),
                    ("Cha", "Chars de Combat", 0),
                    ("C.A", "Commissaire de l'Air", 0),
                    ("C.M", "Commissariat de la Marine", 0),
                    ("C.Ap", "Contrôle des Assurances", 1),
                    ("EF.Ma", "E.& F. du Maroc", 0),
                    ("E.F", "Eaux et Forêts", 0),
                    ("EFC", "Eaux et Forêts des Colonies", 0),
                    ("EFOM", "Eaux et Forêts Outre-Mer", 0),
                    ("ENA", "Ecole Nation.d'Administration", 0),
                    ("-", "Elève à l'Ecole", 0),
                    ("W", "Elève catégorie particulière", 0),
                    ("w", "Elève étranger naturalisé", 0),
                    (" ", "Elève non sorti de l'école", 0),
                    ("FA", "Fabrication d'Armement", 0),
                    ("Gend", "Gendarmerie", 0),
                    ("G", "Génie", 0),
                    ("GM", "Génie Maritime", 0),
                    ("GR", "Génie Rural", 0),
                    ("GREF", "Génie Rural Eaux et Forêts", 1),
                    ("IG", "Géographe", 0),
                    ("Géo.C", "Géologie des Colonies", 0),
                    ("H", "Hydrographes", 0),
                    ("I", "Infanterie", 0),
                    ("I.C", "Infanterie Coloniale", 0),
                    ("I.Ma", "Infanterie de Marine", 0),
                    ("St", "Institut nat.de la statistique", 0),
                    ("IM", "Instruments de mesure", 0),
                    ("ME", "Manufactures Etat Tabacs", 0),
                    ("Mat", "Matériel Armée de Terre", 0),
                    ("Mét", "Météorologie nationale", 0),
                    ("M", "Mines", 1),
                    ("MC", "Mines coloniales", 0),
                    ("MOM", "Mines Outre-Mer", 0),
                    ("N.Aé", "Navigation Aérienne", 0),
                    ("Air", "Offic.de l'Air", 0),
                    ("ORTF", "Office Radio-Télévision franç.", 0),
                    ("BaAir", "Officier des Bases de l'Air", 0),
                    ("Mar", "Officiers de marine", 0),
                    ("OMAir", "Officiers mécaniciens Air", 0),
                    ("PC", "Ponts et Chaussées", 1),
                    ("PCC", "Ponts et Chaussées Colonies", 0),
                    ("PCOM", "Ponts et Chaussées Outre-Mer", 0),
                    ("PTC", "Postes et Télécom.Colonies", 0),
                    ("PTOM", "Postes et Télécom.Outre-Mer", 0),
                    ("P", "Poudres", 0),
                    ("Rad", "Radiodiffusion française", 0),
                    ("Rech", "Recherche", 0),
                    ("R", "Réformé en fin d'études", 0),
                    ("Bât.", "Service des Bâtiments", 0),
                    ("C.F", "SNCF", 0),
                    ("Sp", "Sports", 0),
                    ("Téléc", "Télécommunications", 1),
                    ("Té.A", "Télécommunications d'Armement", 0),
                    ("Tr", "Train", 0),
                    ("Tra", "Transmissions", 0),
                    ("Tra.C", "Transmissions Coloniales", 0),
                    ("TPC", "Travaux publics des Colonies", 0),
                    ("TP Ic", "Travaux publics en Indochine", 0),
                    ("TPOM", "Travaux publics Outre-Mer", 0),
                    ("TdM", "Troupes de Marine", 0),
                    ("INSEE", "Administrateurs de l'INSEE", 1),
                    ("Off", "Officiers des Armées", 1);

ALTER TABLE  watch_profile
     MODIFY  field ENUM('nom', 'freetext', 'mobile', 'nationality1', 'nationality2', 'nationality3',
                        'nick', 'networking', 'edus', 'addresses', 'section', 'binets', 'medals',
                        'cv', 'jobs', 'photo', 'corps');

--  vim:set syntax=mysql:
