--- Database creation script for TIMS V.4.5.0, edited on 2021-10-18
-- pour commencer on met un ; pour le fonctionnement du logiciel

CREATE TABLE `t_activite_conflit_analyse` (
  `CLE_ACTIVITE` int(11) NOT NULL auto_increment,
  `CLE_CHAUFFEUR` int(5) NOT NULL default '0',
  `CLE_JOUR` int(11) NOT NULL default '0',
  `HEURE_DEBUT` int(4) NOT NULL default '0',
  `HEURE_FIN` int(4) NOT NULL default '0',
  `DUREE_EN_MINUTE` int(4) default NULL,
  `FICHIER_IMPORT` varchar(64) default NULL,
  `SOURCE` varchar(64) default NULL,
  `ETAT_CARTE` varchar(64) default NULL,
  `ETAT_CONDUITE` varchar(64) default NULL,
  `LECTEUR` varchar(64) default NULL,
  `ANNEE_ACTIVITE` date NOT NULL default '0000-00-00',
  `CLE_TYPE_ACTIV` int(4) NOT NULL default '0',
  `ARCHIVAGE` tinyint(1) NOT NULL default '0',
  `CLE_DISQUE` int(1) default '0',
  `NOM_SOURCE` varchar(64) default NULL,
  `SUPPRESSION` int(1) default '0',
  `MODIFICATION` tinyint(1) default '0',
  PRIMARY KEY  (`CLE_ACTIVITE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_activite_conflit_base` (
  `CLE_ACTIVITE` int(11) NOT NULL auto_increment,
  `CLE_CHAUFFEUR` int(5) NOT NULL default '0',
  `CLE_JOUR` int(11) NOT NULL default '0',
  `HEURE_DEBUT` int(4) NOT NULL default '0',
  `HEURE_FIN` int(4) NOT NULL default '0',
  `DUREE_EN_MINUTE` int(4) default NULL,
  `FICHIER_IMPORT` varchar(64) default NULL,
  `SOURCE` varchar(64) default NULL,
  `ETAT_CARTE` varchar(64) default NULL,
  `ETAT_CONDUITE` varchar(64) default NULL,
  `LECTEUR` varchar(64) default NULL,
  `ANNEE_ACTIVITE` date NOT NULL default '0000-00-00',
  `CLE_TYPE_ACTIV` int(4) NOT NULL default '0',
  `ARCHIVAGE` tinyint(1) NOT NULL default '0',
  `CLE_DISQUE` int(1) default '0',
  `NOM_SOURCE` varchar(64) default NULL,
  `SUPPRESSION` int(1) default '0',
  `MODIFICATION` tinyint(1) default '0',
  PRIMARY KEY  (`CLE_ACTIVITE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_activite_uev` (
  `CLE_ACTIVITE_UEV` int(11) NOT NULL auto_increment,
  `CLE_VEHICULE` INTEGER NOT NULL,
  `JOUR_TELECHARGE` date NOT NULL default '0000-00-00',
  `HEURE_DEBUT` int(4) NOT NULL default '0',
  `HEURE_FIN` int(4) NOT NULL default '0',
  `ETAT_CARTE` varchar(64) default NULL,
  `ETAT_CONDUITE` varchar(64) default NULL,
  `LECTEUR` varchar(64) default NULL,
  `TYPE_ACTIV` varchar(64) default NULL,
  `DUREE` int(4) NOT NULL default '0',
  PRIMARY KEY  (`CLE_ACTIVITE_UEV`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_agence` (
  `CLE_AGENCE` int(4) NOT NULL auto_increment,
  CLE_PAYS INT(11) DEFAULT 17,
  `DES_AGENCE` varchar(64) NOT NULL default '',
  `CODE_POSTAL` varchar(16) default NULL,
  `VILLE` varchar(64) default NULL,
  TEL2 VARCHAR(32) DEFAULT NULL,
  `FAX` varchar(32) default NULL,
  EMAIL VARCHAR(64) DEFAULT NULL,
  CLE_INTERNE_AGENCE VARCHAR(4),
  `COMMENTAIRE` blob,
  `FICHIER_ASSOCIE` blob,
  `DATE_DEBUT` date default NULL,
  `DATE_FIN` date default NULL,
  `ADRESSE` text,
  `TEL1` varchar(32) default NULL,
  `LOGO` blob,
  `CLE_TYPE_AGENCE` int(11) NOT NULL default '0',
  `CLE_TAILLE_AGENCE` int(11) NOT NULL default '0',
  `ARCHIVAGE` tinyint(1) NOT NULL default '0',
  REGION_ID INTEGER(4) UNSIGNED DEFAULT 0,
  DEFAULT_CALENDAR_ID INTEGER(2) UNSIGNED DEFAULT 0,
  SAT_WORKING TINYINT DEFAULT 0,
  CONSUMPTION_TARGET FLOAT DEFAULT 0,
  LATITUDE FLOAT DEFAULT 0,
  LONGITUDE FLOAT DEFAULT 0,
  PRIMARY KEY  (`CLE_AGENCE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_anomalie_parent` (
  `CLE_ANOMALIE_PARENT` int(11) NOT NULL auto_increment,
  `CODE_ANOMALIE_PARENT` varchar(64) default NULL,
  `TYPE_ANOMALIE_PARENT` varchar(64) default NULL,
  PRIMARY KEY  (`CLE_ANOMALIE_PARENT`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_anomalie_uev` (
  `CLE_ANOMALIE_UEV` int(11) NOT NULL auto_increment,
  `CLE_VEHICULE` INTEGER NOT NULL,
  `DATE_DEBUT` datetime NOT NULL default '0000-00-00 00:00:00',
  `DATE_FIN` datetime NOT NULL default '0000-00-00 00:00:00',
  `TYPE_ANOMALIE` varchar(64) default NULL,
  `RAISON_ANOMALIE` varchar(64) default NULL,
  `NUM_CARTE_COND_DEBUT` varchar(32) default NULL,
  `NUM_CARTE_CONV_DEBUT` varchar(32) default NULL,
  `NUM_CARTE_COND_FIN` varchar(32) default NULL,
  `NUM_CARTE_CONV_FIN` varchar(32) default NULL,
  PRIMARY KEY  (`CLE_ANOMALIE_UEV`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE COMPANY_CARD (
  ID INTEGER(4) UNSIGNED NOT NULL AUTO_INCREMENT,
  AGENCY_ID INTEGER(4) NOT NULL,
  CARD_NUMBER VARCHAR(16),
  MEMBER_STATE INTEGER(3) DEFAULT 17,
  ISSUE_DATE DATE,
  VALIDITY_BEGIN DATE,
  EXPIRY DATE,
  COMPANY_NAME VARCHAR(36),
  COMPANY_ADDRESS VARCHAR(36),
  PREFERRED_LANGUAGE INTEGER(3),
  ARCHIVED TINYINT,
  OWNER VARCHAR(64),
  PRIMARY KEY(ID)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `comment` (
  `CLE` int(11) NOT NULL auto_increment,
  `DESIGNATION` text,
  PRIMARY KEY  (`CLE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_conducteur` (
  `CLE_CHAUFFEUR` int(5) NOT NULL auto_increment,
  `TAUX_HORAIRE` float,
  `DATE_NAISSANCE` date default NULL,
  `CLE_AGENCE` int(4) NOT NULL default '0',
  `CLE_TYPE_PAYE` int(11) NOT NULL default '0',
  `CLE_PROFIL` int(2) NOT NULL default '2',
  NOM_CONDUCTEUR VARCHAR(64),
  PRENOM_CONDUCTEUR VARCHAR(64),
  MATRICULE_CHAUFFEUR VARCHAR(32),
  `NUM_CARTE_CHAUFFEUR` varchar(32) default NULL,
  `MEMBER_STATE` INTEGER(2) DEFAULT 17,
  `REPLACEMENT` TINYINT DEFAULT 0,
  `RENEWAL` TINYINT DEFAULT 0,
  RENEWAL_PENDING TINYINT DEFAULT 0,
  `NUM_CARTE_CHAUFFEUR_14` varchar(32) default NULL,
  `NUM_IMPORT_CHAUFFEUR` varchar(32) default NULL,
  `ADRESSE` text,
  `CODE_POSTAL` varchar(8) default NULL,
  `VILLE` varchar(64) default NULL,
  `TEL1` varchar(16) default NULL,
  `TEL2` varchar(16) default NULL,
  `DATE_ENTREE_SOCIETE` date default NULL,
  `DATE_DEBUT_VALIDITE_CARTE` date default NULL,
  `DATE_EXPIRATION_CARTE` date default NULL,
  `COMMENTAIRE` blob,
  `FICHIER_ASSOCIE` blob,
  `DATE_DERNIERE_VISITE_MEDICALE` date default NULL,
  `TYPE_CONDUCTEUR` varchar(20) default NULL,
  `NUM_SECURITE_SOCIALE` varchar(32) default NULL,
  `ARCHIVAGE` tinyint(1) NOT NULL default '0',
  `CLE_TYPE_CHAUFFEUR` int(2) NOT NULL default '0',
  CALENDAR_ID INTEGER(2) NOT NULL DEFAULT 0,
  `LOGO` blob,
  `DATE_DERNIER_CHANGEMENT` datetime NOT NULL default '1970-01-01 00:00:00',
  `DATE_DERNIER_TELECHARGEMENT` datetime NOT NULL default '1970-01-01 00:00:00',
  `DATE_SORTIE` date NOT NULL default '1970-01-01',
  `DATE_CALCUL_BRUT` date NOT NULL default '1970-01-01',
  `DATE_CALCUL_MODIFIE` date NOT NULL default '1970-01-01',
  `IO_COUNTER` INT(4) DEFAULT 0,
  `TRAVAIL_NUIT` TINYINT DEFAULT 0,
  `FIMO_FCO` DATE DEFAULT "1970-01-01",
  REGION_ID INTEGER(4) UNSIGNED DEFAULT 0,
  TRAGEO_ID INTEGER(8) UNSIGNED DEFAULT 0,
  DALLAS_KEY_ID VARCHAR(16) DEFAULT null,
  NIGHT_DAY TINYINT DEFAULT 0,
  NUMERO_PERMIS VARCHAR(65),
  DATE_VISITE_PERMIS date default NULL,
  QUALIFICATION_CARD_NUMBER VARCHAR(16) DEFAULT NULL,
  ADR DATE DEFAULT NULL,
  CACES DATE DEFAULT NULL,
  LAST_EXPORT DATETIME,
  ANALYTIC_CODE_ID SMALLINT(3) DEFAULT 0,
  CONSUMPTION_TARGET FLOAT DEFAULT 0,
  PAY_PERIOD_TYPE TINYINT(1) DEFAULT 0,
  EXPENSES_ALLOWANCE TINYINT DEFAULT 0,
  B_DELIVERY DATE DEFAULT NULL,
  C_DELIVERY DATE DEFAULT NULL,
  CE_DELIVERY DATE DEFAULT NULL,
  D_DELIVERY DATE DEFAULT NULL,
  DE_DELIVERY DATE DEFAULT NULL,
  TEL3 VARCHAR(16) DEFAULT NULL,
  EMAIL VARCHAR(128) DEFAULT NULL,
  LIEU_NAISSANCE VARCHAR(128) DEFAULT NULL,
  LATITUDE float default 0,
  LONGITUDE float default 0,
  PRIMARY KEY  (`CLE_CHAUFFEUR`),
  KEY `IDX_SOCIETE` (`CLE_AGENCE`),
  KEY `IDX_TYPE_PAYE2` (`CLE_TYPE_PAYE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_conflit` (
  `CLE_CONFLIT` int(11) unsigned NOT NULL auto_increment,
  `CLE_CHAUFFEUR` INTEGER(5) NOT NULL,
  `CLE_USER` INTEGER UNSIGNED DEFAULT 0,
  `DATE_CONFLIT` date default NULL,
  `TRAITE` tinyint(1) unsigned NOT NULL default '0',
  `CLE_TRAITANT` int(5) default NULL,
  `KM_ANALYSE` int(4) NOT NULL default '0',
  `KM_BASE` int(4) NOT NULL default '0',
  `DATE_CREATION` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `DATE_PROCESSION` TIMESTAMP,
  PRIMARY KEY  (`CLE_CONFLIT`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE CUSTOM_REPORT (
  ID INTEGER(4) UNSIGNED NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(128) NOT NULL,
  TYPE TINYINT(1) UNSIGNED DEFAULT 0,
  PRIMARY KEY(`ID`)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE CUSTOM_REPORT_COLUMN (
  ID INTEGER(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  CUSTOM_REPORT_ID INTEGER(4) UNSIGNED NOT NULL,
  NAME VARCHAR(128) NOT NULL,
  VISIBLE_NAME VARCHAR(128) NOT NULL,
  TYPE VARCHAR(128) NOT NULL,
  EXPRESSION VARCHAR(128) DEFAULT NULL,
  WIDTH INTEGER(3) DEFAULT 0,
  POSITION INTEGER(2) DEFAULT 0,
  PRIMARY KEY(`ID`)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE CUSTOM_REPORT_PARAMETER (
  ID INTEGER(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  CUSTOM_REPORT_ID INTEGER(4) UNSIGNED NOT NULL,
  NAME VARCHAR(128) NOT NULL,
  VALUE VARCHAR(128) NOT NULL,
  TYPE VARCHAR(128) DEFAULT NULL,
  PRIMARY KEY(`ID`)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_etalonnage` (
  `CLE_ETALONNAGE` int(5) NOT NULL auto_increment,
  `CLE_VEHICULE` int(5) NOT NULL default '0',
  `TYPE` int(4) default NULL,
  `NOM_MAGASIN` varchar(36) default NULL,
  `ADRESSE_MAGASIN` varchar(36) default NULL,
  `TYPE_CARTE` int(3) default NULL,
  `NUMERO_CARTE` varchar(16) default NULL,
  `EXPIRATION_CARTE` datetime default NULL,
  `IDENTIFICATION_VEHICULE` varchar(17) default NULL,
  `CLE_PAYS` int(3) default NULL,
  `ENREGISTREMENT_VEHICULE` varchar(13) default NULL,
  `W_VEHICULE` int(5) default NULL,
  `K_VEHICULE` int(5) default NULL,
  `L_VEHICULE` int(5) default NULL,
  `TYRE_SIZE` varchar(15) default NULL,
  `AUTHORIZED_SPEED` int(3) default NULL,
  `ANCIEN_KM` int(7) default NULL,
  `NOUVEAU_KM` int(7) default NULL,
  `ANCIENNE_DATE` datetime default NULL,
  `NOUVELLE_DATE` datetime default NULL,
  `PROCHAIN_ETALONNAGE` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`CLE_ETALONNAGE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_event_uev` (
  `CLE_EVENT_UEV` int(11) NOT NULL auto_increment,
  `CLE_VEHICULE` INTEGER NOT NULL,
  `DATE_DEBUT` datetime NOT NULL default '0000-00-00 00:00:00',
  `DATE_FIN` datetime NOT NULL default '0000-00-00 00:00:00',
  `TYPE_EVENT` varchar(64) default NULL,
  `RAISON_EVENT` varchar(64) default NULL,
  `NUM_CARTE_COND_DEBUT` varchar(64) default NULL,
  `NUM_CARTE_CONV_DEBUT` varchar(64) default NULL,
  `NUM_CARTE_COND_FIN` varchar(64) default NULL,
  `NUM_CARTE_CONV_FIN` varchar(64) default NULL,
  `NBR_SIMILAIRE` varchar(64) default NULL,
  PRIMARY KEY  (`CLE_EVENT_UEV`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE  `t_export` (
  CLE_EXPORT int(10) unsigned NOT NULL auto_increment,

  CLE_CHAUFFEUR int(10) unsigned NOT NULL default '0',

  TIMESTAMP_EXPORT timestamp NOT NULL,

  TYPE_FLAG tinyint(3) unsigned NOT NULL default '0',

  PRIMARY KEY  (`CLE_EXPORT`),

  KEY `IDX_EXPORT` USING BTREE (CLE_CHAUFFEUR,TIMESTAMP_EXPORT)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

CREATE TABLE `t_export_update` (
  `CLE_EXPORT_UPDATE` int(10) NOT NULL auto_increment,
  `CLE_CHAUFFEUR` int(10) unsigned NOT NULL default '0',
  `TIMESTAMP_EXPORT` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  USER_ID INTEGER(11),
  AUTO_UPDATE TINYINT(1) DEFAULT 1,
  PRIMARY KEY  (`CLE_EXPORT_UPDATE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_frais` (
  `CLE` int(11) NOT NULL auto_increment,
  `DESIGNATION` varchar(65) NOT NULL default '0',
  `TYPE_DEBUT` int(1) NOT NULL default '0',
  `HEURE_DEBUT` int(4) NOT NULL default '0',
  `TYPE_FIN` int(1) NOT NULL default '0',
  `HEURE_FIN` int(4) NOT NULL default '0',
  `CUMUL_SERVICE` int(4) NOT NULL default '0',
  `CUMUL_AVANT` int(4) NOT NULL default '0',
  `CUMUL_APRES` int(4) NOT NULL default '0',
  `MONTANT_FRANCE` float NOT NULL default '0',
  `MONTANT_ETRANGER` float NOT NULL default '0',
  `ACTIF` tinyint(1) NOT NULL default '0',
  CODE VARCHAR(6),
  QUENCHER VARCHAR(64) DEFAULT NULL,
  ENABLED_DAY VARCHAR(64) DEFAULT NULL,
  BEGIN_AFTER INTEGER(4) DEFAULT -1,
  BEGIN_BEFORE INTEGER(4) DEFAULT -1,
  END_AFTER INTEGER(4) DEFAULT -1,
  END_BEFORE INTEGER(4) DEFAULT -1, 
  HOUR INTEGER(4) DEFAULT 0,
  ARCHIVED TINYINT(1) DEFAULT 0,
  FAST_VIEW TINYINT(4) DEFAULT 0,
  BEGIN_VIEW INTEGER(4) DEFAULT 0,
  END_VIEW INTEGER(4) DEFAULT 0,
  EMPLOYEE_TYPE_ID TINYINT default 0,
  EXPORT TINYINT default 0,
  MAX_DISTANCE integer(5) default -1,
  FROM_HOME tinyint unsigned default 0,
  FROM_AGENCY tinyint unsigned default 0,
  FROM_COMPANY tinyint unsigned default 0,
  PRIMARY KEY  (`CLE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_historique_activite` (
  `CLE_ACTIVITE` int(11) NOT NULL auto_increment,
  `CLE_CHAUFFEUR` int(5) NOT NULL default '0',
  `HEURE_DEBUT` int(4) NOT NULL default '0',
  `HEURE_FIN` int(4) NOT NULL default '0',
  `DUREE_EN_MINUTE` int(4) default NULL,
  `SOURCE` varchar(64) default NULL,
  `ETAT_CARTE` varchar(64) default NULL,
  `ETAT_CONDUITE` varchar(64) default NULL,
  `LECTEUR` varchar(64) default NULL,
  `ANNEE_ACTIVITE` date NOT NULL default '1970-01-01',
  `CLE_TYPE_ACTIV` int(11) NOT NULL default '0',
  `VERSION_MODIFICATION` int(2) NOT NULL default '0',
  PRIMARY KEY  (`CLE_ACTIVITE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_infractions` (
  `CLE` int(11) NOT NULL auto_increment,
  `CLE_CHAUFFEUR` int(5) NOT NULL default '0',
  `DATE` date NOT NULL default '0000-00-00',
  `HEURE_DEPASSEMENT` int(4) NOT NULL default '0',
  `NUM_INFRACTIONS` varchar(8) NOT NULL default '0',
  `SEUIL_CONSTATE` int(5) NOT NULL default '0',
  `BRUTE` tinyint(1) default '0',
  PRIMARY KEY  (`CLE`),
  KEY `T_INFRACTIONSINDEX1` (`CLE_CHAUFFEUR`,`DATE`,`NUM_INFRACTIONS`),
  KEY `T_INFRACTIONSINDEX2` (`DATE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_io_uev` (
  `CLE_IO_UEV` int(11) NOT NULL auto_increment,
  `JOUR_TELECHARGE` datetime NOT NULL default '0000-00-00 00:00:00',
  `CLE_VEHICULE` INTEGER NOT NULL,
  `NOM` varchar(64) default NULL,
  `PRENOM` varchar(64) default NULL,
  `NUM_CARTE` varchar(64) default NULL,
  `DATE_INSERTION` datetime NOT NULL default '0000-00-00 00:00:00',
  `DATE_RETRAIT` datetime NOT NULL default '0000-00-00 00:00:00',
  `KM_INSERTION` varchar(64) default NULL,
  `KM_RETRAIT` varchar(64) default NULL,
  `MANUAL_INPUT` varchar(64) default NULL,
  `LECTEUR` TINYINT(1) DEFAULT 0,
  PRIMARY KEY  (`CLE_IO_UEV`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_jour` (
  `CLE` int(11) NOT NULL auto_increment,
  `DATE_JOUR` date NOT NULL default '0000-00-00',
  `CLE_CHAUFFEUR` int(5) NOT NULL default '0',
  `CONDUITE` int(4) default NULL,
  `TRAVAIL` int(4) default NULL,
  `DISPO` int(4) default NULL,
  `REPOS` int(4) default NULL,
  `DOUBLE_EQUIP` int(4) default NULL,
  `EVENEMENT` int(4) default '0',
  `AUTRE` int(4) default NULL,
  `CUMUL_T_C_D_DE` int(4) default NULL,
  `HEURE_DEBUT` int(4) default NULL,
  `HEURE_FIN` int(4) default NULL,
  `HEURE_NUIT` int(4) default '0',
  `DISTANCE` varchar(64) default NULL,
  `MODIFICATION` tinyint(1) default '0',
  `SUPPRESSION` int(1) default '0',
  `CLE_USER` int(5) default '0',
  `DATE_INSERTION` timestamp NOT NULL default CURRENT_TIMESTAMP,
  ACTIVITIES BLOB,
  SOURCE_NAME VARCHAR(255),
  MANUAL_INPUT TINYINT(1) DEFAULT 0,
  PRIMARY KEY  (`CLE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE T_JOUR_UEV (
  ID int(10) unsigned NOT NULL AUTO_INCREMENT,
  VEHICLE_ID INTEGER(11) NOT NULL,
  USER_ID INTEGER(11) NOT NULL,
  DATE_USE DATE NOT NULL,
  KM INTEGER(9) NOT NULL,
  TOTAL INTEGER(4),
  ACTIVITIES BLOB,
  DAILY_KM INTEGER(3),
  DAY_COMMENT TEXT,
  PRIMARY KEY (ID)

) ENGINE=InnoDB;

CREATE TABLE `t_jour_modifie` (
  `CLE` int(11) NOT NULL auto_increment,
  `DATE_JOUR` date NOT NULL default '1970-01-01',
  `CLE_CHAUFFEUR` int(5) NOT NULL default '0',
  `CLE_USER` int(5) NOT NULL default '0',
  `CLE_AGENCE` int(4) NOT NULL default '0',
  `CONDUITE` int(4) default NULL,
  `TRAVAIL` int(4) default NULL,
  `DISPO` int(4) default NULL,
  `REPOS` int(4) default NULL,
  `DOUBLE_EQUIP` int(4) default NULL,
  `EVENEMENT` int(4) default '0',
  `AUTRE` int(4) default NULL,
  `CUMUL_T_C_D_DE` int(4) default NULL,
  `SEMAINE` int(2) default NULL,
  `ANNEE` int(4) default NULL,
  `NUM_JOUR` int(3) default NULL,
  `JOUR` varchar(64) default NULL,
  `DISTANCE` varchar(64) default NULL,
  `ARCHIVAGE` tinyint(1) NOT NULL default '0',
  `HEURE_DEBUT` int(4) default NULL,
  `HEURE_FIN` int(4) default NULL,
  `HEURE_NUIT` int(4) default '0',
  `MODIFICATION` tinyint(1) default '0',
  `SUPPRESSION` int(1) default '0',
  `DATE_INSERTION` timestamp NOT NULL default CURRENT_TIMESTAMP,
  CURRENT_VALUE TINYINT(1) DEFAULT 1,
  ACTIVITIES BLOB,
  SOURCE_NAME VARCHAR(255),
  MANUAL_INPUT TINYINT(1) DEFAULT 0,
  PRIMARY KEY  (`CLE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_jour_type` (
  `CLE` int(10) unsigned NOT NULL auto_increment,
  `DESIGNATION` varchar(45) NOT NULL default '',
  `SERVICE` int(4) unsigned NOT NULL default '0',
  `PAYE` TINYINT(1) NOT NULL DEFAULT 0,
  `GENERE_HS` TINYINT(1) NOT NULL DEFAULT 0,
  `GENERE_RC` TINYINT DEFAULT 0,  
  `SUPPRESSION` TINYINT(1) NOT NULL DEFAULT 1,
  CODE VARCHAR(6),
  COLOR INTEGER(11) DEFAULT 0,
  FUTURE_SERVICE INTEGER(4) DEFAULT 0,
  WILL_BE_PAID TINYINT DEFAULT 0,
  WILL_GENERATE_RC TINYINT DEFAULT 0,
  WILL_GENERATE_HS TINYINT DEFAULT 0,
  EXPORT TINYINT(1) DEFAULT 0,
  NO_SERVICE TINYINT(1) DEFAULT 0,
  AVG_VALUE TINYINT(1) DEFAULT 0,
  SENORITY INTEGER(4) DEFAULT 0,
  PRIMARY KEY  (`CLE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_lien_jour_commass` (
  `CLE_LIEN_JOUR_COMMASS` int(11) NOT NULL auto_increment,
  `CLE_CHAUFFEUR` int(5) NOT NULL default '0',
  `DATE_JOUR` date NOT NULL default '0000-00-00',
  `COMM_ASS` text,
  `CLE_COMMENT` int(11) default NULL,
  PRIMARY KEY  (`CLE_LIEN_JOUR_COMMASS`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_lien_jour_conducteur` (
  `CLE` int(10) unsigned NOT NULL auto_increment,
  `CLE_CHAUFFEUR` INTEGER(5) NOT NULL default 0,
  `CLE_JOUR_TYPE` INTEGER(5) NOT NULL default 0,
  `CLE_USER` INTEGER UNSIGNED DEFAULT 0,
  `DATE_JOUR` DATE NOT NULL default 0,
  `HEURE_DEBUT` int(4) NOT NULL default '0',
  `HEURE_FIN` int(4) NOT NULL default '0',
  `DUREE` INTEGER(4) NOT NULL default 0,
  `COMPLETE` tinyint(4) NOT NULL default '0',
  TYPE TINYINT DEFAULT 0,
  PRIMARY KEY  (`CLE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_lien_jour_frais` (
  `CLE_LIEN_JOUR_FRAIS` int(11) NOT NULL auto_increment,
  `CLE_CHAUFFEUR` int(5) NOT NULL default '0',
  `CLE_USER` INTEGER(11) NOT NULL default '0',
  `DATE_JOUR` date NOT NULL default '0000-00-00',
  `CLE_FRAIS` int(11) NOT NULL default '0',
  `ETRANGER` tinyint NOT NULL default 0,
  `MONTANT` float NOT NULL default 0,
  BASE FLOAT DEFAULT 1,
  TAUX FLOAT DEFAULT 0,
  FORFAIT TINYINT DEFAULT 0,
  AUTOMATIC TINYINT(1) DEFAULT 0,
  PRIMARY KEY  (`CLE_LIEN_JOUR_FRAIS`),
  KEY `T_LIEN_JOUR_FRAISINDEX1` (`CLE_CHAUFFEUR`,`DATE_JOUR`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE OFFENCE_TYPE (
  ID INTEGER(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(128) NOT NULL,
  CLASS VARCHAR(64) NOT NULL,
  MEMBER_STATE INTEGER(4) NOT NULL,
  COMPANY_TYPE TINYINT(2) NOT NULL,
  ENABLE TINYINT(1) DEFAULT 1,
  PRIMARY KEY(ID)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE OFFENCE_PART (
  ID INTEGER(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  OFFENCE_TYPE_ID INTEGER(2), 
  NAME VARCHAR(128),
  PRIMARY KEY(ID)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE OFFENCE_THRESHOLD (
  ID INTEGER(4) UNSIGNED NOT NULL AUTO_INCREMENT,
  OFFENCE_PART_ID INTEGER(3) NOT NULL,
  THRESHOLD INTEGER(5) NOT NULL,
  NAME VARCHAR(128),
  INFRINGEMENT INTEGER UNSIGNED DEFAULT 0,
  INFRINGEMENT_CLASS INTEGER UNSIGNED DEFAULT 0,
  REFERENCE VARCHAR(32) DEFAULT NULL,
  PRIMARY KEY(ID)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE OFFENCE (
  ID INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  DRIVER_ID INTEGER(5) NOT NULL,
  DAY DATE NOT NULL,
  HOUR INTEGER(4) NOT NULL,
  OFFENCE_PART_ID INTEGER(3) NOT NULL,
  THRESHOLD INTEGER(5) NOT NULL,
  ON_UPDATED TINYINT(1) DEFAULT 0,
  TYPE TINYINT(1) DEFAULT 1,
  COST INTEGER UNSIGNED DEFAULT 0,
  PRIMARY KEY(ID)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_pays` (
  `CLE_PAYS` int(11) NOT NULL auto_increment,
  `CODE_PAYS` int(3) NOT NULL default '0',
  `PREFIXE_PAYS` char(3) NOT NULL default '',
  `DES_PAYS` varchar(64) NOT NULL default '',
  PRIMARY KEY  (`CLE_PAYS`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE driver_vehicle_used (
  CLE int(11) NOT NULL auto_increment,
  CLE_CHAUFFEUR int(11) NOT NULL default '0',
  CLE_VEHICULE int(11) NOT NULL default '0',
  DATE_DEBUT datetime NOT NULL default '0000-00-00 00:00:00',
  DATE_FIN datetime NOT NULL default '0000-00-00 00:00:00',
  KM_DEBUT int(7) NOT NULL default '0',
  KM_FIN int(7) NOT NULL default '0',
  KM_EFFECTUE int(5) NOT NULL default '0',
  SOURCE tinyint(1) unsigned default '0',
  NOM varchar(64) default NULL,
  PRENOM varchar(64) default NULL,
  NUMERO_CARTE varchar(20) default NULL,
  IMMATRICULATION varchar(64) default NULL,
  MANUAL_INPUT TINYINT(1) DEFAULT 0,
  LECTEUR TINYINT(1) DEFAULT 0,
  PRIMARY KEY  (`CLE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE driver_vehicle_used ADD FOREIGN KEY (CLE_CHAUFFEUR) REFERENCES `t_conducteur`(CLE_CHAUFFEUR) ON DELETE CASCADE;

CREATE TABLE `t_raison_anomalie` (
  `CLE_RAISON_ANOMALIE` int(11) NOT NULL auto_increment,
  `CODE_RAISON` varchar(64) default NULL,
  `TYPE_RAISON` varchar(64) default NULL,
  PRIMARY KEY  (`CLE_RAISON_ANOMALIE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_regle_frais` (
  `CLE_REGLE_FRAIS` int(11) NOT NULL auto_increment,
  `BORNE1` int(11) default NULL,
  `BORNE2` int(11) default NULL,
  `CHOIX` int(11) NOT NULL default '0',
  `BORNE3` int(11) default NULL,
  `BORNE4` int(11) default NULL,
  `BORNE5` int(11) default NULL,
  `BORNE6` int(11) default NULL,
  `DESIGNATION` varchar(64) NOT NULL default '',
  `ARCHIVAGE` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`CLE_REGLE_FRAIS`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_scan_image` (
  `CLE` int(11) NOT NULL auto_increment,
  `CLE_USER` INTEGER UNSIGNED DEFAULT 0,
  `DATE_SCAN` date NOT NULL default '0000-00-00',
  `DATE_PROCESSION` DATE,
  `NOM` varchar(64) default NULL,
  `NUMERO` INTEGER NOT NULL,
  `PATH` varchar(255) DEFAULT "",
  `PROCESSED` TINYINT DEFAULT 1,
  PRIMARY KEY  (`CLE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_service` (
  `CLE_SERVICE` int(11) NOT NULL auto_increment,
  `CLE_CHAUFFEUR` int(11) default '0',
  `DATE_DEBUT` datetime NOT NULL default '0000-00-00 00:00:00',
  `DATE_FIN` datetime NOT NULL default '0000-00-00 00:00:00',
  `HEURE_DEBUT` int(4) default '0',
  `HEURE_FIN` int(4) default '0',
  `CONDUITE` int(4) default '0',
  `TRAVAIL` int(4) default '0',
  `DISPO` int(4) default '0',
  `REPOS` int(4) default '0',
  `DOUBLE_EQUIPAGE` int(4) default '0',
  `INCONNU` int(4) default '0',
  `EVENEMENT` int(4) default '0',
  `SERVICE` int(4) default '0',
  `AMPLITUDE` int(4) default '0',
  `DISTANCE` int(4) default '0',
  `HEURE_NUIT` int(4) default '0',
  `FRAIS` FLOAT DEFAULT 0,
  `REPOS_JOURNALIER` INTEGER(6) DEFAULT '0',
  `FIRST_WORK` INTEGER UNSIGNED DEFAULT 0,
  `LAST_WORK` INTEGER UNSIGNED DEFAULT 0,
  `FIRST_WORK_AFTER_REST` INTEGER UNSIGNED DEFAULT 0,
  `SECOND_WORK_AFTER_REST` INTEGER UNSIGNED DEFAULT 0,
  `COUPURE` INTEGER(4) DEFAULT 0,
  ROUND_ID INT(5) UNSIGNED DEFAULT 0,
  THEORIC_SERVICE_POINT INT(5) UNSIGNED DEFAULT 0,
  CALCULATED_SERVICE_POINT INT(5) UNSIGNED DEFAULT 0,
  FAULT TINYINT(1) DEFAULT 0,
  NUIT_PENIBILITE INTEGER(4) DEFAULT 0,
  PRIMARY KEY  (`CLE_SERVICE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_speed_uev` (
  `CLE_SPEED_UEV` int(11) NOT NULL auto_increment,
  `CLE_VEHICULE` INTEGER NOT NULL,
  `DATE_DEBUT` datetime NOT NULL default '0000-00-00 00:00:00',
  `DATE_FIN` datetime NOT NULL default '0000-00-00 00:00:00',
  `TYPE_SPEED` varchar(64) default NULL,
  `RAISON_SPEED` varchar(64) default NULL,
  `NUM_CARTE_COND` varchar(64) default NULL,
  `SPEED_MAX` varchar(64) default NULL,
  `SPEED_MOY` varchar(64) default NULL,
  `NBR_SIMILAIRE` varchar(64) default NULL,
  PRIMARY KEY  (`CLE_SPEED_UEV`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_suppression` (
  `CLE_SUPPRESSION` int(11) NOT NULL auto_increment,
  `CLE_JOUR` INTEGER NOT NULL,
  `DATE_SUPPRESSION` datetime NOT NULL default '1970-01-01 00:00:00',
  `COMMENTAIRE` varchar(255) NOT NULL default 'NON RENSEIGNE',
  `CLE_USER` int(4) NOT NULL default '0',
  PRIMARY KEY  (`CLE_SUPPRESSION`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_taille_agence` (
  `CLE_TAILLE_AGENCE` int(11) NOT NULL auto_increment,
  `DESIGNATION_TAILLE` varchar(64) NOT NULL default '',
  PRIMARY KEY  (`CLE_TAILLE_AGENCE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_tbl_infractions` (
  `CLE` int(11) NOT NULL auto_increment,
  `DESIGNATION` varchar(64) NOT NULL default '',
  `TYPE_REGLE` int(11) NOT NULL default '0',
  `SEUIL` int(11) NOT NULL default '0',
  `AFFICHABLE` tinyint(1) NOT NULL default '0',
  `TYPE_REGLE_E_V` varchar(64) NOT NULL default '',
  `CLE_DESIGNATION` varchar(8) default NULL,
  PRIMARY KEY  (`CLE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_telechargement` (
  `CLE_TELECHARGEMENT` int(10) unsigned NOT NULL auto_increment,
  `CLE_CHAUFFEUR` INTEGER(5) NOT NULL,
  `CLE_USER` INTEGER UNSIGNED DEFAULT 0,
  `DATE_TELECHARGEMENT` datetime default NULL,
  `NOM_FICHIER` varchar(64) default NULL,
  MISSING TINYINT(1) UNSIGNED DEFAULT 0,
  PRIMARY KEY  (`CLE_TELECHARGEMENT`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `t_telechargement_uev` (
  `CLE_TELECHARGEMENT` int(10) unsigned NOT NULL auto_increment,
  `CLE_VEHICULE` INTEGER NOT NULL,
  `CLE_USER` INTEGER UNSIGNED DEFAULT 0,
  `DATE_TELECHARGEMENT` datetime default NULL,
  `NOM_FICHIER` varchar(64) default NULL,
  MISSING TINYINT(1) UNSIGNED DEFAULT 0,
  BEGIN_ACTIVITY DATETIME DEFAULT NULL,
  END_ACTIVITY DATETIME DEFAULT NULL,
  PREVIOUS_DOWNLOAD DATETIME DEFAULT NULL,
  PRIMARY KEY  (`CLE_TELECHARGEMENT`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `T_TOURNEE` (
 `CLE` int(11) NOT NULL auto_increment,
 `DESIGNATION` VARCHAR(64) NOT NULL DEFAULT "NOM CONTRAT",
 `BEGIN_TIME` int(4) unsigned NOT NULL DEFAULT 0,
 `BEGIN_PLACE` VARCHAR(64) NOT NULL DEFAULT "",
 `END_TIME` int(4) unsigned NOT NULL DEFAULT 0,
 `END_PLACE` VARCHAR(64) NOT NULL DEFAULT "",
 `DRIVING` int(4) unsigned NOT NULL DEFAULT 0,
 `WORK` int(4) unsigned NOT NULL DEFAULT 0,
 `AVAILABILITY` int(4) unsigned NOT NULL DEFAULT 0,
 `REST` int(4) unsigned NOT NULL DEFAULT 0,
 `SERVICE_COUNT` int(4) unsigned NOT NULL DEFAULT 0,
 `KM` int(4) unsigned NOT NULL DEFAULT 0,
 `COMMENT` VARCHAR(64) DEFAULT NULL,
 PRIMARY KEY  (`CLE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE T_TYPE_ACTIVITE (
  ID int(11) NOT NULL auto_increment,
  CODE varchar(64) NOT NULL default '',
  DESIGNATION varchar(64) NOT NULL default '',
  PERCENTAGE INTEGER(3),
  PAYER TINYINT default 0,
  COULEUR INTEGER DEFAULT 0, 
  GENERE_HS tinyint(1) default 0,
  GENERE_RC TINYINT DEFAULT 0,
  MODIFIABLE TINYINT DEFAULT 1,
  MODIFICATION_LEVEL TINYINT DEFAULT 0,
  REST TINYINT DEFAULT 0,
  DELETED TINYINT(1) DEFAULT 0,  
  SHOW_PAID TINYINT(1) DEFAULT 0,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_type_anomalie` (
  `CLE_TYPE_ANOMALIE` int(11) NOT NULL auto_increment,
  `ANOMALIE_PARENT` varchar(64) default NULL,
  `CODE_ANOMALIE` varchar(64) default NULL,
  `TYPE_ANOMALIE` varchar(64) default NULL,
  PRIMARY KEY  (`CLE_TYPE_ANOMALIE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_type_paye` (
  `CLE_TYPE_PAYE` int(11) NOT NULL auto_increment,
  `DES_TYPE_PAYE` varchar(64) NOT NULL default '',
  PRIMARY KEY  (`CLE_TYPE_PAYE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_uev` (
  `CLE_UEV` int(11) NOT NULL auto_increment,
  `CLE_VEHICULE` INTEGER NOT NULL,
  `DATE_MIN` datetime NOT NULL default '0000-00-00 00:00:00',
  `DATE_MAX` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`CLE_UEV`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_vehicule` (
  `CLE_VEHICULE` int(11) NOT NULL auto_increment,
  `DESIGNATION_VEHICULE` varchar(64) default NULL,
  `Famille` varchar(64) default NULL,
  `Marque` varchar(64) default NULL,
  `N_IMMATRICULATION` varchar(64) default NULL,
  `IDENTIFICATION` varchar(64) default NULL,
  `NB_PLACES` varchar(64) default NULL,
  `NB_PLACES_CV` varchar(64) default NULL,
  `KM_DERNIER_RELEVE_COMPTEUR` varchar(64) default NULL,
  `DATE_CONTROLE1` date default NULL,
  `DATE_CONTROLE2` date default NULL,
  `DATE_CONTROLE3` date default NULL,
  `DATE_VERIFICATION_EXTINCTEUR` date default NULL,
  `DATE_CONTROLE_MINES` date default NULL,
  `DATE_CONTROLE_TACHY` date default NULL,
  `DATE_DERNIER_RELEVE_COMPTEUR` date default NULL,
  `DERNIER_TELECHARGEMENT` datetime default NULL,
  `REMARQUES` text,
  `DATE_ENTREE` DATE,
  `DATE_SORTIE` DATE,
  `ARCHIVAGE` TINYINT DEFAULT 0,
  `COMMENTAIRE` TEXT,
  `CLE_AGENCE` int(4) NOT NULL default '0',
  DATE_TECHNICAL DATE,
  DATE_CALIBRATION DATE,
  LOCATION_LIMIT DATE DEFAULT NULL,
  GUARANTEE DATE DEFAULT NULL,
  LOCATION_KM INTEGER(6) DEFAULT 0,
  DAILY_KM INTEGER(4) DEFAULT 0,
  LAST_KNOWN_KM INTEGER(7) DEFAULT 0,
  LOCATION_PRICE FLOAT DEFAULT 0,
  OWNER VARCHAR(36) DEFAULT NULL,
  TYPE_ID SMALLINT(3) DEFAULT 0,
  BODY_ID SMALLINT(3) DEFAULT 0,
  AFFECTATION_ID SMALLINT(3) DEFAULT 0,
  ANALYTIC_CODE_ID SMALLINT(3) DEFAULT 0,
  MODEL_ID SMALLINT(3) DEFAULT 0,
  CONSUMPTION_TARGET FLOAT DEFAULT 0,
  VU_BRAND VARCHAR(36) DEFAULT NULL,
  VU_VERSION VARCHAR(4) DEFAULT NULL,
  PRIMARY KEY  (`CLE_VEHICULE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE vehicle_used (
  CLE int(11) NOT NULL auto_increment,
  CLE_CHAUFFEUR int(11) NOT NULL DEFAULT 0,
  CLE_VEHICULE int(11) NOT NULL DEFAULT 0,
  DATE_DEBUT DATETIME NOT NULL,
  DATE_FIN DATETIME NOT NULL,
  KM_DEBUT int(7) NOT NULL DEFAULT 0,
  KM_FIN int(7) NOT NULL DEFAULT 0,
  KM_EFFECTUE int(5) NOT NULL DEFAULT 0,
  MANUAL_INPUT varchar(64) default NULL,
  LECTEUR tinyint(1) default '0',
  SOURCE tinyint(1) default '0',
  NOM varchar(64) default NULL,
  PRENOM varchar(64) default NULL,
  NUMERO_CARTE varchar(20) default NULL,
  IMMATRICULATION varchar(64) default NULL,
  PRIMARY KEY  (`CLE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE VEHICLE_ATTACHMENT (

  ID int(10) unsigned NOT NULL AUTO_INCREMENT,
  
  VEHICLE_ID INTEGER(11) NOT NULL,
    
  DOCUMENT_NAME VARCHAR(64) NOT NULL,
  
  PRIMARY KEY (ID)

) ENGINE=InnoDB;

ALTER TABLE VEHICLE_ATTACHMENT ADD FOREIGN KEY (VEHICLE_ID) REFERENCES T_VEHICULE(CLE_VEHICULE) ON DELETE CASCADE;

CREATE TABLE CALENDAR (
  `ID` INTEGER(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `NAME` VARCHAR(64) DEFAULT NULL,
  `ENABLE` TINYINT DEFAULT 1,
  PRIMARY KEY(`ID`)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE CALENDAR_DAY (
  `ID` INTEGER(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `CALENDAR_ID` INTEGER(2) UNSIGNED NOT NULL,
  `DAY_TYPE_ID` INTEGER(2) UNSIGNED NOT NULL,
  `DAY_DATE` DATE NOT NULL,
  PRIMARY KEY(`ID`)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `close_pay_period` (
  `ID` INTEGER(4) UNSIGNED NOT NULL AUTO_INCREMENT,
  `PAY_PERIOD_ID` INTEGER(4) NOT NULL,
  `AGENCY_ID` INTEGER(11) NOT NULL,
  `USER_ID` INTEGER(4) NOT NULL,
  `CLOTURE` INTEGER(1) NOT NULL,
  `EXPORTED` TINYINT NOT NULL,
  `CLOTURE_DATE` DATE NOT NULL,
  ACTIVE TINYINT(1) DEFAULT 0,
  PRIMARY KEY(`ID`)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE EXTERNAL_DAY (
  ID INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  DRIVER_ID INTEGER(5) DEFAULT 0,
  BEGIN_DATE DATETIME DEFAULT NULL,
  END_DATE DATETIME NOT NULL,
  ACTIVITIES TEXT NOT NULL,
  DRIVING INTEGER(4) DEFAULT 0,
  WORK INTEGER(4) DEFAULT 0,
  AVAILABILITY INTEGER(4) DEFAULT 0,
  REST INTEGER(4) DEFAULT 0,
  CREW INTEGER(4) DEFAULT 0,
  EVENT INTEGER(4) DEFAULT 0,
  OTHER INTEGER(4) DEFAULT 0,
  TOTAL INTEGER(4) DEFAULT 0,
  DISTANCE INTEGER(4) DEFAULT 0,
  BEGIN_TIME INTEGER(4) DEFAULT 0,
  END_TIME INTEGER(4) DEFAULT 0,
  USER_ID INTEGER(11) DEFAULT 0,
  DATE_INSERTION TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(`ID`)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE EXTERNAL_DATA_FAULT (
  `ID` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `DRIVER_ID` INTEGER(5) NOT NULL,
  `DAY` DATE NOT NULL,
  `USER_ID` INTEGER DEFAULT 0,
  PRIMARY KEY(`ID`)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE FAULT (
  `ID` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `DRIVER_ID` INTEGER(11) UNSIGNED NOT NULL,
  `DAY` DATE NOT NULL,
  `TYPE` TINYINT DEFAULT 0,
  `USER_ID` INTEGER DEFAULT 0,
  PRIMARY KEY(`ID`)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE INTERVENTION (
  `ID` SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
  `TYPE_ID` SMALLINT(3) UNSIGNED NOT NULL,
  `VEHICLE_ID` INTEGER(5) NOT NULL,
  `DAY` DATE NOT NULL,
  `PERFORMER` VARCHAR(64) NOT NULL,
  `MO_COST` FLOAT NOT NULL,
  `COST` FLOAT NOT NULL,
  `KM` INTEGER(7) NOT NULL,
  `STATE` TINYINT(1) NOT NULL DEFAULT 0,
  ATTACHMENT VARCHAR(256),
  PRIMARY KEY(`ID`)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE INTERVENTION_TYPE (
  `ID` SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  `CODE` VARCHAR(32) NOT NULL,
  `NAME` VARCHAR(32) NOT NULL,
  `KM_EXPIRATION` INTEGER(4) NOT NULL,
  `PERIOD_EXPIRATION` INTEGER(4) NOT NULL,
  `DEFAULT_COST` FLOAT NOT NULL,
  `MO_COST` FLOAT NOT NULL,
  `ARCHIVED` TINYINT NOT NULL,
  `PRIORITY_ORDER` tinyint default 0,
  PRIMARY KEY(`ID`)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE INTERVENTION ADD FOREIGN KEY (VEHICLE_ID) REFERENCES `T_VEHICULE`(CLE_VEHICULE) ON DELETE CASCADE;
ALTER TABLE INTERVENTION ADD FOREIGN KEY (TYPE_ID) REFERENCES `INTERVENTION_TYPE`(ID) ON DELETE CASCADE;

CREATE TABLE `taux_base` (
  `CLE` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  T115M FLOAT NOT NULL,
  T118M FLOAT NOT NULL,
  T120M FLOAT NOT NULL,
  T128M FLOAT NOT NULL,
  T138M FLOAT NOT NULL,
  T150M FLOAT NOT NULL,
  `DATE_INSERTION` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `DATE_DEBUT` TIMESTAMP NOT NULL,
  PRIMARY KEY(`CLE`)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `tracking` (
 `ID` int(11) NOT NULL auto_increment,
 `USER_ID` int(11) NOT NULL,
 `ACTION_TYPE` int(1) NOT NULL,
 `ACTION_DATE` TIMESTAMP NOT NULL,
 `CONTENT` TEXT NOT NULL,
 TABLE_CHANGED VARCHAR(64) DEFAULT "",
 PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `TYPE_CONTRAT` (
 `CLE` int(11) NOT NULL auto_increment,
 `DESIGNATION` VARCHAR(64) NOT NULL DEFAULT "NOM CONTRAT",
 `HEURE_CONTRAT` INT(3) UNSIGNED NOT NULL DEFAULT 0,
 `HEURE_MAXIMUM` INT(3) UNSIGNED NOT NULL DEFAULT 0,
 `SEUIL_HN` int(4) unsigned NOT NULL DEFAULT 0,
 `SEUIL_HE` int(4) unsigned NOT NULL DEFAULT 0,
 `SEUIL_HS25` int(4) unsigned NOT NULL DEFAULT 0,
 `CALCULATION_TYPE` int(1) unsigned NOT NULL DEFAULT 0,
 `GARANTIE` TINYINT unsigned NOT NULL DEFAULT 0,
 `DAILY_VALUE` INTEGER(4) DEFAULT 0, 
PRIMARY KEY  (`CLE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `pay_period` (
  `ID` INTEGER(4) UNSIGNED NOT NULL AUTO_INCREMENT,
  `NAME` VARCHAR(16) NOT NULL,
  `BEGIN` DATE NOT NULL,
  `END` DATE NOT NULL,
  PRIMARY KEY(`ID`)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `place` (
 `ID` int(11) NOT NULL auto_increment,
 `DRIVER_ID` int(11) NOT NULL,
 `TYPE` INTEGER(1) NOT NULL,
 `DATE_CHANGE` TIMESTAMP NOT NULL,
 `COUNTRY` int(3) NOT NULL,
 `REGION` int(3) NOT NULL,
 `KM` int(7),
 PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `region` (
  `ID` INTEGER(4) UNSIGNED NOT NULL AUTO_INCREMENT,
  `COUNTRY_ID` INTEGER(3) DEFAULT 0,
  `NAME` VARCHAR(128) NOT NULL,
  `CODE` VARCHAR(8),
  PRIMARY KEY(`ID`)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE UEV_SESSION (
  `ID` INTEGER(6) UNSIGNED NOT NULL AUTO_INCREMENT,
  `VEHICLE_ID` INTEGER(5) NOT NULL,
  `OPEN` DATETIME NOT NULL,
  `CLOSE` DATETIME NOT NULL,
  `COMPANY` VARCHAR(36) NOT NULL,
  `COMPANY_ADDRESS` VARCHAR(36) NOT NULL,
  `COMPANY_CARD` VARCHAR(18) NOT NULL,
  PRIMARY KEY(`ID`)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE UEV_SESSION ADD FOREIGN KEY (VEHICLE_ID) REFERENCES `t_vehicule`(CLE_VEHICULE) ON DELETE CASCADE;

CREATE TABLE UEV_EVENT_FAULT (
  `ID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `TYPE_ID` SMALLINT(3) UNSIGNED NOT NULL,
  `PURPOSE_ID` SMALLINT(3) UNSIGNED NOT NULL,
  `VEHICLE_ID` INTEGER(11) NOT NULL,
  `BEGIN` DATETIME NOT NULL,
  `END` DATETIME NOT NULL,
  `DRIVER_CARD_BEGIN` VARCHAR(18) DEFAULT NULL,
  `DRIVER_CARD_END` VARCHAR(18) DEFAULT NULL,
  `CODRIVER_CARD_BEGIN` VARCHAR(18) DEFAULT NULL,
  `CODRIVER_CARD_END` VARCHAR(18) DEFAULT NULL,
  `SIMILAR` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY(`ID`)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE UEV_EVENT_FAULT ADD FOREIGN KEY (VEHICLE_ID) REFERENCES `T_VEHICULE`(CLE_VEHICULE) ON DELETE CASCADE;

CREATE TABLE UEV_OFFENCE (
  `ID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `OFFENCE_TYPE_ID` SMALLINT(3) UNSIGNED NOT NULL,
  `VEHICLE_ID` INTEGER(11) NOT NULL,
  `DRIVER_ID` INTEGER(11) DEFAULT 0,
  `BEGIN` DATETIME NOT NULL,
  `END` DATETIME DEFAULT NULL,
  `LENGTH` INTEGER(11) DEFAULT 0,
  `THRESHOLD` INTEGER(11) DEFAULT 0,
  `GRAVITY` TINYINT DEFAULT 0,
  PRIMARY KEY(`ID`)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE UEV_OFFENCE ADD FOREIGN KEY (VEHICLE_ID) REFERENCES `T_VEHICULE`(CLE_VEHICULE) ON DELETE CASCADE;

CREATE TABLE `waiting` (
  `CLE` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `CLE_USER` INTEGER UNSIGNED DEFAULT 0,
  `NOM` VARCHAR(65) NOT NULL,
  `DATE_SCAN` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `PROCESSED` TINYINT DEFAULT 0,
  PRIMARY KEY(`CLE`)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE CONFIGURATION (
  ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  VALUE text,
  EVAL text,
  ENABLED tinyint(1) NOT NULL default '1',
  EDITABLE TINYINT(1) DEFAULT 1,
  USER_ID INT(3) DEFAULT 0,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE DAILY_VEHICLE_COMMENT (
  ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  VEHICLE_ID INTEGER(3) NOT NULL,
  DAY DATE NOT NULL,
  COMM TEXT NOT NULL,
  PRIMARY KEY(ID)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE EXPENSES_VALIDATION  (
  ID INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  DRIVER_ID INTEGER(5) NOT NULL,
  DAY DATE NOT NULL,
  VALID_DATE TIMESTAMP NOT NULL,
  USER_VALID INTEGER(5) NOT NULL,
  UNVALID_DATE TIMESTAMP NOT NULL,
  USER_UNVALID INTEGER(5) DEFAULT -1,
  PRIMARY KEY(`ID`)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IRREGULARITY (
  CLE_ACTIVITE int(11) NOT NULL auto_increment,
  CLE_CHAUFFEUR int(5) NOT NULL default '0',
  CLE_JOUR int(11) NOT NULL default '0',
  HEURE_DEBUT int(4) NOT NULL default '0',
  HEURE_FIN int(4) NOT NULL default '0',
  DUREE_EN_MINUTE int(4) default NULL,
  FICHIER_IMPORT varchar(64) default NULL,
  SOURCE varchar(64) default NULL,
  ETAT_CARTE varchar(64) default NULL,
  ETAT_CONDUITE varchar(64) default NULL,
  LECTEUR varchar(64) default NULL,
  ANNEE_ACTIVITE date NOT NULL default '0000-00-00',
  CLE_TYPE_ACTIV int(4) NOT NULL default '0',
  ARCHIVAGE tinyint(1) NOT NULL default '0',
  CLE_DISQUE int(1) default '0',
  NOM_SOURCE varchar(64) default NULL,
  SUPPRESSION int(1) default '0',
  MODIFICATION tinyint(1) default '0',
  PRIMARY KEY  (`CLE_ACTIVITE`),
  KEY `T_ACTIVITEINDEX` (`CLE_CHAUFFEUR`,`HEURE_DEBUT`,`HEURE_FIN`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE EMPLOYEE_OBLIGATION (
  ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  TYPE_ID SMALLINT(3) UNSIGNED NOT NULL,
  DRIVER_ID INTEGER(11) NOT NULL,
  DAY DATE NOT NULL,
  PLACE VARCHAR(64) NOT NULL,
  LENGTH SMALLINT(5) DEFAULT 0,
  COST FLOAT DEFAULT 0,
  STATE TINYINT(1) DEFAULT 0,
  ATTACHMENT VARCHAR(256),
  COMM VARCHAR(128) DEFAULT NULL,
  EXPIRATION DATE DEFAULT NULL,
  PRIMARY KEY(ID)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE EMPLOYEE_OBLIGATION_TYPE (
  ID SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  CODE VARCHAR(32) NOT NULL,
  LENGTH SMALLINT(5) DEFAULT 0,
  NAME VARCHAR(32) NOT NULL,
  PERIOD_EXPIRATION INTEGER(4) NOT NULL,
  DEFAULT_COST FLOAT NOT NULL,
  WARNING_THRESHOLD SMALLINT(3) DEFAULT 0,
  ERROR_THRESHOLD SMALLINT(3) DEFAULT 0,
  ARCHIVED TINYINT NOT NULL,
  GROUP_ID TINYINT NOT NULL DEFAULT 0,
  PERMISSION_REQUIRED TINYINT NOT NULL DEFAULT 0,
  MANUAL_EXPIRATION TINYINT DEFAULT 0,
  HABILITATION TINYINT DEFAULT 0,
  PRIMARY KEY(ID)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE EMPLOYEE_OBLIGATION ADD FOREIGN KEY (TYPE_ID) REFERENCES `EMPLOYEE_OBLIGATION_TYPE`(ID) ON DELETE CASCADE;
ALTER TABLE EMPLOYEE_OBLIGATION ADD FOREIGN KEY (DRIVER_ID) REFERENCES `T_CONDUCTEUR`(CLE_CHAUFFEUR) ON DELETE CASCADE;

CREATE TABLE EMPLOYEE_OBLIGATION_TYPE_GROUP (
  ID SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(128) NOT NULL,
  CODE VARCHAR(8) DEFAULT NULL,
  ARCHIVED TINYINT(1) DEFAULT 0,
  PRIMARY KEY(ID)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE VEHICLE_TYPE (
  ID SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  CODE VARCHAR(32) NOT NULL,
  NAME VARCHAR(32) NOT NULL,
  ARCHIVED TINYINT NOT NULL,
  PRIMARY KEY(ID)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE BODY_TYPE (
  ID SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  CODE VARCHAR(32) NOT NULL,
  NAME VARCHAR(32) NOT NULL,
  ARCHIVED TINYINT NOT NULL,
  PRIMARY KEY(ID)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE VEHICLE_AFFECTATION (
  ID SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  CODE VARCHAR(32) NOT NULL,
  NAME VARCHAR(32) NOT NULL,
  ARCHIVED TINYINT NOT NULL,
  PRIMARY KEY(ID)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE VEHICLE_ANALYTIC_CODE (
  ID SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  CODE VARCHAR(32) NOT NULL,
  NAME VARCHAR(32) NOT NULL,
  ARCHIVED TINYINT NOT NULL,
  PRIMARY KEY(ID)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE DRIVER_ANALYTIC_CODE (
  ID SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  CODE VARCHAR(32) NOT NULL,
  NAME VARCHAR(32) NOT NULL,
  ARCHIVED TINYINT NOT NULL,
  PRIMARY KEY(ID)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE ACCIDENT (
  ID INTEGER(6) UNSIGNED NOT NULL AUTO_INCREMENT,
  IDENTIFICATION_NUMBER VARCHAR(32),
  INSURANCE_NUMBER VARCHAR(32) DEFAULT NULL,
  INSURANCE_TYPE VARCHAR(8) DEFAULT NULL,
  INSURANCE_CODE VARCHAR(8) DEFAULT NULL,
  CONVENTION_TYPE VARCHAR(8) DEFAULT NULL,
  AGENCY_ID INTEGER(11) NOT NULL,
  DRIVER_ID INTEGER(11) NOT NULL,
  VEHICLE_ID INTEGER(11) NOT NULL,
  DAY TIMESTAMP NOT NULL,
  PLACE VARCHAR(64) NOT NULL,
  TYPE TINYINT(1) DEFAULT 0,
  INJURY TINYINT(1) DEFAULT 0,
  STEAL TINYINT(1) DEFAULT 0,
  DESCRIPTION VARCHAR(512) NOT NULL,
  BIG_DAMAGES TINYINT(1) DEFAULT 0,
  RESPONSABILITY TINYINT(1) DEFAULT 0,
  GARAGE_ID SMALLINT(4) DEFAULT 0,
  GARAGE_IN TIMESTAMP DEFAULT 0,
  GARAGE_OUT TIMESTAMP DEFAULT 0,
  TOWING TINYINT(1) DEFAULT 0,
  RC_COST FLOAT DEFAULT 0,
  RC_EXCESS FLOAT DEFAULT 0,
  INSURANCE_RECOURSE FLOAT DEFAULT 0,
  COMPANY_RECOURSE FLOAT DEFAULT 0,
  DAMAGE_EXCESS FLOAT DEFAULT 0,
  VAT FLOAT DEFAULT 0,
  DILAPIDATION FLOAT DEFAULT 0,
  SELF_INSURANCE FLOAT DEFAULT 0,
  EXPERT_COST FLOAT DEFAULT 0,
  FIXING_COST FLOAT DEFAULT 0,
  COMPANY_FIXING_COST FLOAT DEFAULT 0,
  LOCATION_DEBT FLOAT DEFAULT 0,
  COMPANY_LOCATION_DEBT FLOAT DEFAULT 0,
  GUARDING FLOAT DEFAULT 0,
  TOTAL_COST FLOAT DEFAULT 0,
  TOTAL_INSURANCE FLOAT DEFAULT 0,
  IN_TRAFFIC TINYINT(1) DEFAULT 1,
  OPENED TINYINT(1) DEFAULT 0,
  PRIMARY KEY(ID)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE REPAIRING (
  ID INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  DAY TIMESTAMP NOT NULL,
  ACCIDENT_ID INTEGER(11) NOT NULL,
  GARAGE_ID SMALLINT(4) NOT NULL,
  PURPOSE VARCHAR(64),
  TOTAL_COST FLOAT DEFAULT 0,
  MO_COST FLOAT DEFAULT 0,
  PRIMARY KEY(ID)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE ACCIDENT_ACTION (
  ID INTEGER(6) UNSIGNED NOT NULL AUTO_INCREMENT,
  USER_ID INTEGER(11) NOT NULL,
  INSERTION_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ACCIDENT_ID INTEGER(6) NOT NULL,
  PURPOSE VARCHAR(64),
  PRIMARY KEY(ID)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE INJURED (
  ID INTEGER(6) UNSIGNED NOT NULL AUTO_INCREMENT,
  ACCIDENT_ID INTEGER(6) NOT NULL,
  EMPLOYEE TINYINT(1) DEFAULT 0,
  FIRST_NAME VARCHAR(64),
  LAST_NAME VARCHAR(64),
  ADDRESS VARCHAR(256),
  CITY VARCHAR(64),
  POSTAL_CODE VARCHAR(5),
  PHONE VARCHAR(16),
  PRIMARY KEY(ID)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE GARAGE (
  ID SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
  AGENCY_ID INTEGER(4),
  NAME VARCHAR(64),
  CODE VARCHAR(8),
  CONTACT VARCHAR(64),
  ADDRESS VARCHAR(256),
  CITY VARCHAR(64),
  POSTAL_CODE VARCHAR(5),
  PHONE VARCHAR(16),
  FAX VARCHAR(16),
  ARCHIVE TINYINT(1) DEFAULT 0,
  PRIMARY KEY(ID)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE AGENCY_TYPE (
  ID SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(32) NOT NULL,
  CODE VARCHAR(32) DEFAULT NULL,
  ARCHIVED TINYINT(1) DEFAULT 0,
  PRIMARY KEY(ID)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE EMPLOYEE_TYPE (
  ID SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(32) NOT NULL,
  CODE VARCHAR(32) DEFAULT NULL,
  ARCHIVED TINYINT(1) DEFAULT 0,
  PRIMARY KEY(ID)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE VEHICLE_MODEL (
  ID SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  CODE VARCHAR(32) NOT NULL,
  NAME VARCHAR(32) NOT NULL,
  ARCHIVED TINYINT NOT NULL,
  PRIMARY KEY(ID)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IMPORT_FIELD (
  ID SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  IMPORT_MODULE_ID SMALLINT(3),
  NAME VARCHAR(32) NOT NULL,
  FIELD_VALUE VARCHAR(128) DEFAULT NULL
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IMPORT_MODULE (
  ID SMALLINT(3) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  NAME VARCHAR(32) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE ROUND_TYPE (
  ID SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ROUND_CODE VARCHAR(16) NOT NULL,
  DESCRIPTION VARCHAR(256) NOT NULL,
  MAX_SERVICE_POINT SMALLINT(3) DEFAULT 0,
  MAX_PARCEL SMALLINT(3) DEFAULT 0,
  BEGIN_TIME SMALLINT(4) DEFAULT 0,
  LOAD_TIME SMALLINT(3) DEFAULT 0,
  TOTAL_CUT_TIME SMALLINT(3) DEFAULT 0,
  RETURN_TIME SMALLINT(4) DEFAULT 0,
  UNLOAD_TIME SMALLINT(3) DEFAULT 0,
  TOTAL_TIME SMALLINT(4) DEFAULT 0,
  AVG_DELIVERY_TIME SMALLINT(3) DEFAULT 0,
  KM SMALLINT(4) DEFAULT 0,
  ARCHIVED TINYINT(1) DEFAULT 0
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE ROUND_AFFECTATION (
  ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ROUND_ID SMALLINT(16) NOT NULL,
  DRIVER_ID INTEGER NOT NULL,
  VEHICLE_ID INTEGER NOT NULL,
  ROUND_DATE DATE NOT NULL,
  SERVICE_POINT SMALLINT(3) DEFAULT 0,
  CLIENT_COUNT SMALLINT(3) DEFAULT 0,
  DELIVERY_PARCEL SMALLINT(3) DEFAULT 0,
  PICKING_PARCEL SMALLINT(3) DEFAULT 0,
  DONE TINYINT(1) DEFAULT 0,
  BEGIN_TIME SMALLINT(4) DEFAULT 0,
  LOAD_TIME SMALLINT(3) DEFAULT 0,
  TOTAL_CUT_TIME SMALLINT(3) DEFAULT 0,
  DELIVERY_TIME SMALLINT(4) DEFAULT 0,
  RETURN_TIME SMALLINT(4) DEFAULT 0,
  UNLOAD_TIME SMALLINT(3) DEFAULT 0,
  TOTAL_TIME SMALLINT(4) DEFAULT 0,
  STOP_SERVICE_POINT SMALLINT(3) DEFAULT 0,
  DELIVERED_PARCEL SMALLINT(3) DEFAULT 0,
  UNDELIVERED_PARCEL SMALLINT(3) DEFAULT 0,
  PICKED_PARCEL SMALLINT(3) DEFAULT 0,
  CLIENT_DELIVERED SMALLINT(3) DEFAULT 0,
  KM SMALLINT(4) DEFAULT 0
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE SPECIFIC_CONDITION (
  ID int(10) unsigned NOT NULL AUTO_INCREMENT,
  DRIVER_ID INTEGER(11) NOT NULL,
  SPECIFIC_TIMESTAMP DATETIME NOT NULL,
  SPECIFIC_TYPE TINYINT(1),
  PRIMARY KEY (ID)
) ENGINE=InnoDB;

CREATE TABLE FUEL_CONSUMPTION (
  ID int(10) unsigned NOT NULL AUTO_INCREMENT,
  AGENCY_ID INTEGER(4) NOT NULL,
  DRIVER_ID INTEGER(5) NOT NULL,
  VEHICLE_ID INTEGER(5) unsigned NOT NULL,
  DATE datetime NOT NULL,
  CONSUMPTION float NOT NULL,
  LITERS float NOT NULL,
  LENGTH INTEGER(10) unsigned NOT NULL,
  KM float NOT NULL,
  STOP_LENGTH INTEGER(4),
  STOP_LENGTH_STRENGTH INTEGER(4),
  STOP_LENGTH_IN_TRAFFIC INTEGER(4),
  LENGTH_BRAKE INTEGER(3),
  REG_DELTA INTEGER(3),
  CO2 INTEGER(6),
  TRAVEL_NOTATION INTEGER(3),
  PRIMARY KEY (ID)

) ENGINE=InnoDB;

ALTER TABLE FUEL_CONSUMPTION ADD FOREIGN KEY (AGENCY_ID) REFERENCES T_AGENCE(CLE_AGENCE) ON DELETE CASCADE;

CREATE TABLE CONSUMPTION_CRITERIA (
  ID INTEGER(3) unsigned NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(128) NOT NULL,
  PRIMARY KEY (ID)
) ENGINE=InnoDB;

CREATE TABLE CONSUMPTION_EVAL (
  ID TINYINT unsigned NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(128) NOT NULL,
  PERCENTAGE TINYINT DEFAULT 0,
  COLOR INTEGER,
  PRIMARY KEY (ID)

) ENGINE=InnoDB; 

CREATE TABLE CONSUMPTION_MARK (
  ID INTEGER(5) unsigned NOT NULL AUTO_INCREMENT,
  DRIVER_ID INTEGER(5) NOT NULL,
  CRITERIA_ID INTEGER(3) unsigned NOT NULL,
  CONSUMPTION_EVAL_ID TINYINT unsigned NOT NULL,
  PRIMARY KEY (ID)
) ENGINE=InnoDB;

CREATE TABLE DRIVER_ATTACHMENT (
  ID int(10) unsigned NOT NULL AUTO_INCREMENT,
  DRIVER_ID INTEGER(11) NOT NULL,
  DOCUMENT_NAME VARCHAR(64) NOT NULL,
  PRIMARY KEY (ID)
) ENGINE=InnoDB;

CREATE TABLE EMPLOYEE_OBLIGATION_ATTACHMENT (
  ID int(10) unsigned NOT NULL AUTO_INCREMENT,
  EMPLOYEE_OBLIGATION_ID INTEGER(6) UNSIGNED NOT NULL,
  DOCUMENT_NAME VARCHAR(64) NOT NULL,
  PRIMARY KEY (ID)
) ENGINE=InnoDB;

CREATE TABLE HARDNESS (
  ID INTEGER(11) unsigned NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(64) NOT NULL,
  CODE VARCHAR(32) NOT NULL,
  CALCULATOR_CLASS VARCHAR(128) DEFAULT NULL,
  WARNING_THRESHOLD INTEGER(4) UNSIGNED DEFAULT 0,
  ATTRIBUTION_THRESHOLD INTEGER(4) UNSIGNED DEFAULT 0,  
  PRIMARY KEY (ID)
) ENGINE=InnoDB;

CREATE TABLE HARDNESS_ATTRIBUTION (
  ID INTEGER(11) unsigned NOT NULL AUTO_INCREMENT,
  DRIVER_ID INTEGER(11) NOT NULL, 
  HARDNESS_ID INTEGER(11) NOT NULL,
  YEAR DATETIME NOT NULL,
  ATTRIBUTION TINYINT(1) NOT NULL,
  USER_ID INTEGER(11) DEFAULT 0,
  CREATION TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ENABLE TINYINT(1) NOT NULL,
  COUNTER INTEGER(4) UNSIGNED DEFAULT 0,
  RECALCULATION TINYINT(1) UNSIGNED DEFAULT 0,
  PRIMARY KEY (ID)
) ENGINE=InnoDB;

CREATE TABLE DRIVER_ERROR (
  ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  DRIVER_ID INTEGER(5) NOT NULL,
  ERROR_BEGIN DATE DEFAULT null,
  ERROR_END DATE DEFAULT null,
  TYPE INTEGER UNSIGNED NOT NULL DEFAULT 0,
  DETAIL VARCHAR(256) NOT NULL,
  PRINTED DATETIME NOT NULL,
  USER_ID INTEGER UNSIGNED NOT NULL DEFAULT 0,
  PROCESSED DATETIME,
  PRIMARY KEY (`ID`)
) ENGINE = InnoDB;

CREATE TABLE VEHICLE_ERROR (
  ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  VEHICLE_ID INTEGER(11) NOT NULL,
  ERROR_BEGIN DATE DEFAULT null,
  ERROR_END DATE DEFAULT null,
  TYPE INTEGER UNSIGNED NOT NULL DEFAULT 0,
  DETAIL VARCHAR(256) NOT NULL,
  PRINTED DATETIME NOT NULL,
  USER_ID INTEGER UNSIGNED NOT NULL DEFAULT 0,
  VEHICLE_FILE_ID INTEGER UNSIGNED NOT NULL DEFAULT 0,
  PROCESSED DATETIME,
  PRIMARY KEY (`ID`)
) ENGINE = InnoDB;

CREATE TABLE EXPENSE_AMOUNT (
  ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  EXPENSE_TYPE_ID INT(11) NOT NULL,
  ENACTED DATE NOT NULL,
  FRENCH_AMOUNT FLOAT NOT NULL,
  FOREIGN_AMOUNT FLOAT NOT NULL,
  PRIMARY KEY (ID)
) ENGINE = InnoDB;
ALTER TABLE EXPENSE_AMOUNT ADD FOREIGN KEY (EXPENSE_TYPE_ID) REFERENCES `t_frais`(CLE) ON DELETE CASCADE;

CREATE TABLE GNSS_PLACE (
  ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  DRIVER_ID INT(11) DEFAULT 0,
  VEHICLE_ID INTEGER DEFAULT 0,
  POINT_TIME DATETIME NOT NULL,
  TYPE TINYINT NOT NULL,
  ACCURACY TINYINT NOT NULL,
  LATITUDE FLOAT DEFAULT 0,
  LONGITUDE FLOAT DEFAULT 0,
  HEADING INTEGER DEFAULT 0,
  COORDINATE VARCHAR(12) DEFAULT NULL,
  ODOMETER_VALUE INTEGER(7),
  PRIMARY KEY (ID)
) ENGINE = InnoDB;

CREATE TABLE IMPORT_ACTIVITY (
  ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  STATUS INTEGER UNSIGNED NOT NULL DEFAULT 0,
  INTEGRATION_DATE DATETIME not null,
  FILENAME VARCHAR(64) NOT NULL,
  USER_ID INTEGER UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`ID`)
) ENGINE = InnoDB;

CREATE TABLE IMPORT_ACTIVITY_WARNING (
  ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  IMPORT_ACTIVITY_ID INTEGER UNSIGNED NOT NULL,
  DRIVER_ID INTEGER UNSIGNED NOT NULL,
  LAST_IMPORT DATETIME NOT NULL,
  LAST_DOWNLOAD DATETIME NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE = InnoDB;

CREATE TABLE IMPORT_ACTIVITY_ANOMALY (
  ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  IMPORT_ACTIVITY_ID INTEGER UNSIGNED NOT NULL,
  ANOMALY_DAY DATE NOT NULL,
  DRIVER_ID INTEGER UNSIGNED NOT NULL,
  INITIAL blob DEFAULT NULL,
  IMPORTED blob DEFAULT NULL,
  OVERRUN INTEGER UNSIGNED NOT NULL,
  PROCESS_TYPE INTEGER UNSIGNED NOT NULL DEFAULT 0,
  IMPORTED_DAY_TYPE INTEGER(10) DEFAULT 0,
  INITIAL_DAY_TYPE INTEGER(10) DEFAULT 0,
  PRIMARY KEY (`ID`)
) ENGINE = InnoDB;

CREATE TABLE  `day_planning` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `DAY_TYPE_ID` int(5) NOT NULL DEFAULT '0',
  `USER_ID` int(10) unsigned DEFAULT '0',
  `DAY` date NOT NULL DEFAULT '0000-00-00',
  `BEGIN_HOUR` int(4) NOT NULL DEFAULT '0',
  `END_HOUR` int(4) NOT NULL DEFAULT '0',
  `LENGTH` int(4) NOT NULL DEFAULT '0',
  `TYPE` tinyint(4) DEFAULT '0',
  `COMM` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB CHARSET=latin1;

CREATE TABLE  `driver_planning` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `DAY_PLANNING_ID` int(10) unsigned NOT NULL DEFAULT '0',
  `DRIVER_ID` int(5) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  CONSTRAINT `dp_day_planning` FOREIGN KEY (`DAY_PLANNING_ID`) REFERENCES `day_planning` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `dp_driver` FOREIGN KEY (`DRIVER_ID`) REFERENCES `t_conducteur` (`CLE_CHAUFFEUR`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE  `vehicle_planning` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `DAY_PLANNING_ID` int(10) unsigned NOT NULL DEFAULT '0',
  `VEHICLE_ID` int(5) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  CONSTRAINT `dp_dayv_planning` FOREIGN KEY (`DAY_PLANNING_ID`) REFERENCES `day_planning` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `dp_vehicle` FOREIGN KEY (`VEHICLE_ID`) REFERENCES `t_vehicule` (`CLE_VEHICULE`) ON DELETE CASCADE
) ENGINE=InnoDB CHARSET=latin1;

ALTER TABLE DRIVER_ERROR ADD FOREIGN KEY (DRIVER_ID) REFERENCES `t_conducteur`(CLE_CHAUFFEUR) ON DELETE CASCADE;
ALTER TABLE VEHICLE_ERROR ADD FOREIGN KEY (VEHICLE_ID) REFERENCES `t_vehicule`(CLE_VEHICULE) ON DELETE CASCADE;

INSERT INTO HARDNESS (NAME, CODE, CALCULATOR_CLASS) VALUES ('Travail de nuit', 'nuit','transpo.system.core.hardness.exposure.NightHardness');
INSERT INTO HARDNESS (NAME, CODE) VALUES ('Travail répititif', 'répétitif');
INSERT INTO HARDNESS (NAME, CODE) VALUES ('Travail d\'équipe en horaire alternants', 'horaire alternants');
INSERT INTO HARDNESS (NAME, CODE) VALUES ('Travail en milieu hyperbare', 'milieu hyperbare');
INSERT INTO HARDNESS (NAME, CODE) VALUES ('Manutention de charges', 'manutention');
INSERT INTO HARDNESS (NAME, CODE) VALUES ('Exposition aux postures pénible', 'postures pénibles');
INSERT INTO HARDNESS (NAME, CODE) VALUES ('Exposition aux vibrations mécaniques', 'vibrations mécaniques');
INSERT INTO HARDNESS (NAME, CODE) VALUES ('Exposition aux agents chimiques dangereux', 'agents chimiques');
INSERT INTO HARDNESS (NAME, CODE) VALUES ('Exposition au bruit', 'bruit');
INSERT INTO HARDNESS (NAME, CODE) VALUES ('Exposition aux températures extrêmes', 'températures extrêmes');

ALTER TABLE DRIVER_ATTACHMENT ADD FOREIGN KEY (DRIVER_ID) REFERENCES T_CONDUCTEUR(CLE_CHAUFFEUR) ON DELETE CASCADE;

ALTER TABLE EMPLOYEE_OBLIGATION_ATTACHMENT ADD FOREIGN KEY (EMPLOYEE_OBLIGATION_ID) REFERENCES EMPLOYEE_OBLIGATION(ID) ON DELETE CASCADE;

ALTER TABLE T_ACTIVITE_CONFLIT_BASE ADD FOREIGN KEY (CLE_CHAUFFEUR) REFERENCES `t_conducteur`(CLE_CHAUFFEUR) ON DELETE CASCADE;
ALTER TABLE T_ACTIVITE_CONFLIT_ANALYSE ADD FOREIGN KEY (CLE_CHAUFFEUR) REFERENCES `t_conducteur`(CLE_CHAUFFEUR) ON DELETE CASCADE;
ALTER TABLE T_TELECHARGEMENT ADD FOREIGN KEY (CLE_CHAUFFEUR) REFERENCES `t_conducteur`(CLE_CHAUFFEUR) ON DELETE CASCADE;
ALTER TABLE T_JOUR ADD FOREIGN KEY (CLE_CHAUFFEUR) REFERENCES `t_conducteur`(CLE_CHAUFFEUR) ON DELETE CASCADE;
ALTER TABLE T_JOUR_MODIFIE ADD FOREIGN KEY (CLE_CHAUFFEUR) REFERENCES `t_conducteur`(CLE_CHAUFFEUR) ON DELETE CASCADE;
ALTER TABLE T_LIEN_JOUR_COMMASS ADD FOREIGN KEY (CLE_CHAUFFEUR) REFERENCES `t_conducteur`(CLE_CHAUFFEUR) ON DELETE CASCADE;
ALTER TABLE T_CONFLIT ADD FOREIGN KEY (CLE_CHAUFFEUR) REFERENCES `t_conducteur`(CLE_CHAUFFEUR) ON DELETE CASCADE;
ALTER TABLE T_LIEN_JOUR_FRAIS ADD FOREIGN KEY (CLE_CHAUFFEUR) REFERENCES `t_conducteur`(CLE_CHAUFFEUR) ON DELETE CASCADE;
ALTER TABLE T_LIEN_JOUR_CONDUCTEUR ADD FOREIGN KEY (CLE_CHAUFFEUR) REFERENCES `t_conducteur`(CLE_CHAUFFEUR) ON DELETE CASCADE;
ALTER TABLE T_HISTORIQUE_ACTIVITE ADD FOREIGN KEY (CLE_CHAUFFEUR) REFERENCES `t_conducteur`(CLE_CHAUFFEUR) ON DELETE CASCADE;
ALTER TABLE T_INFRACTIONS ADD FOREIGN KEY (CLE_CHAUFFEUR) REFERENCES `t_conducteur`(CLE_CHAUFFEUR) ON DELETE CASCADE;
ALTER TABLE T_SERVICE ADD FOREIGN KEY (CLE_CHAUFFEUR) REFERENCES `t_conducteur`(CLE_CHAUFFEUR) ON DELETE CASCADE;
ALTER TABLE place ADD FOREIGN KEY (DRIVER_ID) REFERENCES `t_conducteur`(CLE_CHAUFFEUR) ON DELETE CASCADE;
ALTER TABLE EXTERNAL_DATA_FAULT ADD FOREIGN KEY (DRIVER_ID) REFERENCES `T_CONDUCTEUR`(CLE_CHAUFFEUR) ON DELETE CASCADE;
ALTER TABLE OFFENCE ADD INDEX IDX_DRIVER USING BTREE(DRIVER_ID,DAY);
ALTER TABLE EXPENSES_VALIDATION ADD INDEX IDX_DRIVER USING BTREE(DRIVER_ID);

ALTER TABLE T_SUPPRESSION ADD FOREIGN KEY (CLE_JOUR) REFERENCES `t_jour`(CLE) ON DELETE CASCADE;

ALTER TABLE T_ACTIVITE_UEV ADD FOREIGN KEY (CLE_VEHICULE) REFERENCES `T_VEHICULE`(CLE_VEHICULE) ON DELETE CASCADE;
ALTER TABLE T_ANOMALIE_UEV ADD FOREIGN KEY (CLE_VEHICULE) REFERENCES `T_VEHICULE`(CLE_VEHICULE) ON DELETE CASCADE;
ALTER TABLE T_ETALONNAGE ADD FOREIGN KEY (CLE_VEHICULE) REFERENCES `T_VEHICULE`(CLE_VEHICULE) ON DELETE CASCADE;
ALTER TABLE T_EVENT_UEV ADD FOREIGN KEY (CLE_VEHICULE) REFERENCES `T_VEHICULE`(CLE_VEHICULE) ON DELETE CASCADE;
ALTER TABLE T_IO_UEV ADD FOREIGN KEY (CLE_VEHICULE) REFERENCES `T_VEHICULE`(CLE_VEHICULE) ON DELETE CASCADE;
ALTER TABLE T_SPEED_UEV ADD FOREIGN KEY (CLE_VEHICULE) REFERENCES `T_VEHICULE`(CLE_VEHICULE) ON DELETE CASCADE;
ALTER TABLE T_TELECHARGEMENT_UEV ADD FOREIGN KEY (CLE_VEHICULE) REFERENCES `T_VEHICULE`(CLE_VEHICULE) ON DELETE CASCADE;
ALTER TABLE T_UEV ADD FOREIGN KEY (CLE_VEHICULE) REFERENCES `T_VEHICULE`(CLE_VEHICULE) ON DELETE CASCADE;
ALTER TABLE CUSTOM_REPORT_COLUMN ADD FOREIGN KEY (CUSTOM_REPORT_ID) REFERENCES `CUSTOM_REPORT`(ID) ON DELETE CASCADE;
ALTER TABLE DAILY_VEHICLE_COMMENT ADD FOREIGN KEY (VEHICLE_ID) REFERENCES `T_VEHICULE`(CLE_VEHICULE) ON DELETE CASCADE;

ALTER TABLE GARAGE ADD FOREIGN KEY (AGENCY_ID) REFERENCES T_AGENCE(CLE_AGENCE) ON DELETE CASCADE;

INSERT INTO T_PAYS (CLE_PAYS,DES_PAYS,CODE_PAYS,PREFIXE_PAYS) VALUES (1,'Inconnu',0,'UNK'),(2,'Autriche',1,'A__'),(3,'Albanie',2,'AL_'),(4,'Andorre',3,'AND'),(5,'Arménie',4,'ARM'),(6,'Azerbaidjan',5,'AZ_'),(7,'Belgique',6,'B__'),(8,'Bulgarie',7,'BG_'),(9,'Bosnie Herzégovine',8,'BIH'),(10,'Bélarus',9,'BY_'),(11,'Suisse',10,'CH_'),(12,'Chypre',11,'CY_'),(13,'République tchèque',12,'CZ_'),(14,'Allemagne',13,'D__'),(15,'Danemark',14,'DK_'),(16,'Espagne',15,'E__'),(17,'Estonie',16,'EST'),(18,'France',17,'F__'),(19,'Finlande',18,'FIN'),(20,'Liechtenstein',19,'FL_'),(21,'Iles féroé',20,'FR_'),(22,'Royaume uni',21,'UK_'),(23,'Géorgie',22,'GE_'),(24,'Grèce',23,'GR_'),(25,'Hongrie',24,'H__'),(26,'Croatie',25,'HR_'),(27,'Italie',26,'I__'),(28,'Irlande',27,'IRL'),(29,'Islande',28,'IS_'),(30,'Kazakhstan',29,'KZ_'),(31,'Luxembourg',30,'L__'),(32,'Lituanie',31,'LT_'),(33,'Lettonie',32,'LV_'),(34,'Malte',33,'M__'),(35,'Monaco',34,'MC_'),(36,'République de moldova',35,'MD_'),(37,'Macédoine',36,'MK_'),(38,'Norvège',37,'N__'),(39,'Pays-bas',38,'NL_'),(40,'Portugal',39,'P__'),(41,'Pologne',40,'PL_'),(42,'Roumanie',41,'RO_'),(43,'Saint-marin',42,'RSM'),(44,'Russie',43,'RUS'),(45,'Suède',44,'S_'),(46,'Slovaquie',45,'SK_'),(47,'Slovenie',46,'SLO'),(48,'Turkménistan',47,'TM_'),(49,'Turquie',48,'TR_'),(50,'Ukraine',49,'UA_'),(51,'Cité du vatican',50,'V__'),(52,'Yougoslavie',51,'YU_'),(53,'Communauté Européenne',253,'EC_'),(54,'Reste de l\'europe',254,'EUR'),(55,'Reste du monde',255,'WLD');

INSERT INTO T_TBL_INFRACTIONS VALUES (1,'Conduite continue max (CC)',1,270,0,'E','CCM');
INSERT INTO T_TBL_INFRACTIONS VALUES (2,'Interruption minimale CC',1,45,0,'E','CCIM');
INSERT INTO T_TBL_INFRACTIONS VALUES (3,'Periode minimale 1',1,15,0,'E','');
INSERT INTO T_TBL_INFRACTIONS VALUES (4,'Periode minimale 2',1,30,0,'E','');
INSERT INTO T_TBL_INFRACTIONS VALUES (5,'Conduite journaliere maximale (CJ)',2,540,0,'E','CJM');
INSERT INTO T_TBL_INFRACTIONS VALUES (6,'CJ toléré',2,600,0,'E','');
INSERT INTO T_TBL_INFRACTIONS VALUES (7,'Jours CJT',2,2,0,'E','');
INSERT INTO T_TBL_INFRACTIONS VALUES (8,'Conduite hebdomadaire max (CH)',3,2880,0,'E','CHM');
INSERT INTO T_TBL_INFRACTIONS VALUES (9,'Conduite sur 2 semaines max (C2S)',3,5400,0,'E','CBHM');
INSERT INTO T_TBL_INFRACTIONS VALUES (10,'Repos journalier min (RJ)',4,660,0,'E','RJM');
INSERT INTO T_TBL_INFRACTIONS VALUES (11,'RJ toléré',4,540,0,'E','');
INSERT INTO T_TBL_INFRACTIONS VALUES (12,'Jours RJT',4,3,0,'E','');
INSERT INTO T_TBL_INFRACTIONS VALUES (13,'Repos journalier DE min (RJDE)',5,480,0,'E','RJDE');
INSERT INTO T_TBL_INFRACTIONS VALUES (14,'Periode journalier dbl. equ',5,1800,0,'E','');
INSERT INTO T_TBL_INFRACTIONS VALUES (15,'Repos journalier fractionné',6,720,0,'E','RJF');
INSERT INTO T_TBL_INFRACTIONS VALUES (16,'Repos consécutif min',6,540,0,'E','');
INSERT INTO T_TBL_INFRACTIONS VALUES (17,'Fractionnements min',6,180,0,'E','');
INSERT INTO T_TBL_INFRACTIONS VALUES (18,'Repos hebdomadaire min (RH)',7,2700,0,'E','RHM');
INSERT INTO T_TBL_INFRACTIONS VALUES (19,'2 repos hebdomadaire réduit',7,2160,0,'E','RHT');
INSERT INTO T_TBL_INFRACTIONS VALUES (20,'RHTHD',7,1440,0,'E','');
INSERT INTO T_TBL_INFRACTIONS VALUES (21,'TE hebdomadaire max (TEH) grands routiers',8,3360,0,'F','TEHMGR');
INSERT INTO T_TBL_INFRACTIONS VALUES (22,'TE hebdomadaire max (TEH) zones courtes',8,3120,0,'F','TEHMZC');
INSERT INTO T_TBL_INFRACTIONS VALUES (23,'TE hebdomadaire max (TEH) messagerie',8,2880,0,'F','TEHMME');
INSERT INTO T_TBL_INFRACTIONS VALUES (24,'TE journalier max (TEJ)',9,600,0,'F','TEJM');
INSERT INTO T_TBL_INFRACTIONS VALUES (25,'TEJ toléré',9,720,0,'F','TEJT');
INSERT INTO T_TBL_INFRACTIONS VALUES (26,'Jours TEJ',9,2,0,'F','TEJTM');
INSERT INTO T_TBL_INFRACTIONS VALUES (27,'TE continue 6h',13,360,0,'F','TEL1');
INSERT INTO T_TBL_INFRACTIONS VALUES (28,'Repos inférieur à 30 min',13,30,0,'F','REL1');
INSERT INTO T_TBL_INFRACTIONS VALUES (29,'TE continue 9h',13,540,0,'F','TEL2');
INSERT INTO T_TBL_INFRACTIONS VALUES (30,'Repos inférieur à 45 min',13,45,0,'F','REL2');
INSERT INTO T_TBL_INFRACTIONS VALUES (31,'Periode minimale TEC',13,15,0,'F','');

INSERT INTO T_TYPE_ACTIVITE (ID, CODE, DESIGNATION, PERCENTAGE, PAYER, COULEUR, GENERE_HS, GENERE_RC, MODIFIABLE, REST) VALUES (1, 'C', 'CONDUITE', 100, 1, '-16776961', 1, 1, 1, 0);
INSERT INTO T_TYPE_ACTIVITE (ID, CODE, DESIGNATION, PERCENTAGE, PAYER, COULEUR, GENERE_HS, GENERE_RC, MODIFIABLE, REST) VALUES (2, 'T', 'TRAVAIL', 100, 1, '-52429', 1, 1, 1, 0);
INSERT INTO T_TYPE_ACTIVITE (ID, CODE, DESIGNATION, PERCENTAGE, PAYER, COULEUR, GENERE_HS, GENERE_RC, MODIFIABLE, REST) VALUES (3, 'D', 'DISPO', 100, 1, '-16724992', 1, 1, 1, 0);
INSERT INTO T_TYPE_ACTIVITE (ID, CODE, DESIGNATION, PERCENTAGE, PAYER, COULEUR, GENERE_HS, GENERE_RC, MODIFIABLE, REST) VALUES (4, 'R', 'REPOS', 0, 1, '16711935', 0, 0, 1, 1);
INSERT INTO T_TYPE_ACTIVITE (ID, CODE, DESIGNATION, PERCENTAGE, PAYER, COULEUR, GENERE_HS, GENERE_RC, MODIFIABLE, REST) VALUES (5, 'DE', 'DOUBLE EQUIPAGE', 100, 1, '8388736', 1, 1, 1, 0);
INSERT INTO T_TYPE_ACTIVITE (ID, CODE, DESIGNATION, PERCENTAGE, PAYER, COULEUR, GENERE_HS, GENERE_RC, MODIFIABLE, REST) VALUES (6, 'AA', 'AUTRE ACTIVITE', 0, 1, '-6711040', 0, 0, 1, 0);
INSERT INTO T_TYPE_ACTIVITE (ID, CODE, DESIGNATION, PERCENTAGE, PAYER, COULEUR, GENERE_HS, GENERE_RC, MODIFIABLE, REST) VALUES (7, 'INC', 'INCONNU', 0, 0, '16514560', 0, 0, 1, 1);

INSERT INTO T_FRAIS (CLE, DESIGNATION, CODE, MONTANT_FRANCE, MONTANT_ETRANGER, ACTIF, QUENCHER, BEGIN_AFTER, BEGIN_BEFORE, END_AFTER, END_BEFORE, CUMUL_SERVICE, CUMUL_AVANT, CUMUL_APRES) 
VALUES (1, 'REPAS MIDI', 'RM', 13.32, 15.1, 0, 0, -1, 705, 855, -1, 0, -1, -1);
INSERT INTO T_FRAIS (CLE, DESIGNATION, CODE, MONTANT_FRANCE, MONTANT_ETRANGER, ACTIF, QUENCHER, BEGIN_AFTER, BEGIN_BEFORE, END_AFTER, END_BEFORE, CUMUL_SERVICE, CUMUL_AVANT, CUMUL_APRES) 
VALUES (2, 'REPAS SOIR', 'RS', 13.32, 15.1, 0, 5, -1, 1125, 1275, -1, 0, -1, -1);
INSERT INTO T_FRAIS (CLE, DESIGNATION, CODE, MONTANT_FRANCE, MONTANT_ETRANGER, ACTIF, QUENCHER, BEGIN_AFTER, BEGIN_BEFORE, END_AFTER, END_BEFORE, CUMUL_SERVICE, CUMUL_AVANT, CUMUL_APRES) 
VALUES (3, 'CASSE CROUTE', 'CC', 7.22, 8.52, 0, 0, 0, 300, -1, -1, 0, -1, -1);
INSERT INTO T_FRAIS (CLE, DESIGNATION, CODE, MONTANT_FRANCE, MONTANT_ETRANGER, ACTIF, QUENCHER, BEGIN_AFTER, BEGIN_BEFORE, END_AFTER, END_BEFORE, CUMUL_SERVICE, CUMUL_AVANT, CUMUL_APRES) 
VALUES (4, 'REPAS UNIQUE', 'RU', 8.2, 9.67, 0, 4, -1, -1, -1, -1, 240, 420, 1320);
INSERT INTO T_FRAIS (CLE, DESIGNATION, CODE, MONTANT_FRANCE, MONTANT_ETRANGER, ACTIF, QUENCHER, BEGIN_AFTER, BEGIN_BEFORE, END_AFTER, END_BEFORE, CUMUL_SERVICE, CUMUL_AVANT, CUMUL_APRES) 
VALUES (5, 'DECOUCHER', 'GD1', 29.28, 29.28, 0, 0, -1, -1, -1, -1, 0, -1, -1);

UPDATE T_FRAIS SET FAST_VIEW=1, BEGIN_VIEW = 120,END_VIEW = 240, HOUR = 180 WHERE CODE="RU";
UPDATE T_FRAIS SET FAST_VIEW=1, BEGIN_VIEW = 240,END_VIEW = 300, HOUR = 270 WHERE CODE="CC";
UPDATE T_FRAIS SET FAST_VIEW=1, BEGIN_VIEW = 705,END_VIEW = 855, HOUR = 780 WHERE CODE="RM";
UPDATE T_FRAIS SET FAST_VIEW=1, BEGIN_VIEW = 1125,END_VIEW = 1275, HOUR = 1200 WHERE CODE="RS";
UPDATE T_FRAIS SET FAST_VIEW=1, BEGIN_VIEW = 1320,END_VIEW = 120, HOUR = 20 WHERE CODE="RHD";

INSERT INTO EXPENSE_AMOUNT (EXPENSE_TYPE_ID, ENACTED, FOREIGN_AMOUNT, FRENCH_AMOUNT) select cle, "2010-01-01", MONTANT_ETRANGER, MONTANT_FRANCE from t_frais;

INSERT INTO TAUX_BASE values (1, 8.37, 8.37, 8.37, 8.49, 8.51, 8.8, '2008-04-11 00:00:00', '2008-06-18 15:38:15');
INSERT INTO TAUX_BASE values (2, 10.10, 10.10, 10.10, 10.13, 10.15, 10.39, '2019-06-01 00:00:00', '2019-06-01 00:00:00');

INSERT INTO TYPE_CONTRAT values (1, "DEFAUT", 9120, 9120, 9120, 11160, 11160, 2, 0,420);

INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ("fco_expiration","1826",1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ("medical_visit_driving_expiration","1826",1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ("medical_visit_driving_60_expiration","730",1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ("medical_visit_expiration","730",1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ("medical_visit_night_expiration","182",1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ("vehicle_technical_expiration","365",1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ("fco_warning","30",1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ("fco_error","5",1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ("medical_visit_warning","30",1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ("medical_visit_error","5",1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ("medical_visit_driving_warning","30",1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ("medical_visit_driving_error","2",1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ("vehicle_technical_warning","30",1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ("vehicle_technical_error","5",1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ("card_expiration_warning","90",1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ("card_expiration_error","30",1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ("calibration_warning","30",1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ("calibration_error","5",1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ("contract_day_length_reference","1",1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ("company.type","1",1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ('adr_expiration','1826',1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ('adr_warning','90',1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ('adr_error','30',1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ('caces_expiration','1826',1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ('caces_warning','30',1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ('caces_error','5',1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ('show_country','0',1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ('show_region','0',1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED) VALUES ('price-fixed_default','1',1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED, EDITABLE) VALUES ("export_default_path","",1,1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED, EDITABLE) VALUES ("automatic.tools.detection","0",1,1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED, EDITABLE) VALUES ("driver.orange.threshold","15",1,1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED, EDITABLE) VALUES ("driver.red.threshold","25",1,1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED, EDITABLE) VALUES ("vehicle.orange.threshold","45",1,1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED, EDITABLE) VALUES ("vehicle.red.threshold","70",1,1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED, EDITABLE) VALUES ("scan.speed","0",1,1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED, EDITABLE) VALUES ("attestation.agency","0",1,1);
INSERT INTO CONFIGURATION (VALUE, EVAL, ENABLED, EDITABLE) VALUES ('cardless.threshold','3',1,1);

INSERT INTO OFFENCE_TYPE (NAME, CLASS, MEMBER_STATE, COMPANY_TYPE) VALUES ("offence.type.continuousDrive", "transport.offence.ContinuousDrive", 0, 1);

INSERT INTO OFFENCE_PART (OFFENCE_TYPE_ID, NAME) SELECT ID, "offence.basic" FROM OFFENCE_TYPE WHERE NAME = "offence.type.continuousDrive";
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 270, "offence.threshold.minor", "750", "4", "R3315-10"  FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 300, "offence.threshold.serious", "750", "4", "R3315-10"  FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 360, "offence.threshold.verySerious", "1500", "5", "R3315-11"  FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME) SELECT MAX(ID), 45, "offence.interruption" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME) SELECT MAX(ID), 15, "offence.threshold.firstInterruption" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME) SELECT MAX(ID), 30, "offence.threshold.secondInterruption" FROM OFFENCE_PART;


INSERT INTO OFFENCE_TYPE (NAME, CLASS, MEMBER_STATE, COMPANY_TYPE) VALUES ("offence.type.continuousDriveTraveller", "transport.offence.ContinuousDriveTraveller", 0, 2);

INSERT INTO OFFENCE_PART (OFFENCE_TYPE_ID, NAME) SELECT ID, "offence.basic" FROM OFFENCE_TYPE WHERE NAME = "offence.type.continuousDriveTraveller";
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 270, "offence.threshold.minor", "750", "4", "R3315-10"  FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 300, "offence.threshold.serious", "750", "4", "R3315-10"  FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 360, "offence.threshold.verySerious", "1500", "5", "R3315-11" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME) SELECT MAX(ID), 45, "offence.interruption" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME) SELECT MAX(ID), 15, "offence.threshold.firstInterruption" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME) SELECT MAX(ID), 30, "offence.threshold.secondInterruption" FROM OFFENCE_PART;

INSERT INTO OFFENCE_PART (OFFENCE_TYPE_ID, NAME) SELECT ID, "offence.reduced" FROM OFFENCE_TYPE WHERE NAME = "offence.type.continuousDriveTraveller";
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 240, "offence.threshold.minor", "750", "4", "" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 300, "offence.threshold.serious", "750", "4", "" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 360, "offence.threshold.verySerious", "1500", "5", "" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME) SELECT MAX(ID), 1260, "offence.threshold.begin" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME) SELECT MAX(ID), 360, "offence.threshold.end" FROM OFFENCE_PART;


INSERT INTO OFFENCE_TYPE (NAME, CLASS, MEMBER_STATE, COMPANY_TYPE) VALUES ("offence.type.dailyDrive", "transport.offence.DailyDrive", 0, 0);

INSERT INTO OFFENCE_PART (OFFENCE_TYPE_ID, NAME) SELECT ID, "offence.dd.unextended" FROM OFFENCE_TYPE WHERE NAME = "offence.type.dailyDrive";
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 540, "offence.threshold.minor", "750", "4", "R3315-10" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 600, "offence.threshold.serious", "750", "4", "R3315-10" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 660, "offence.threshold.verySerious", "1500", "5", "R3315-11" FROM OFFENCE_PART;

INSERT INTO OFFENCE_PART (OFFENCE_TYPE_ID, NAME) SELECT ID, "offence.dd.extended" FROM OFFENCE_TYPE WHERE NAME = "offence.type.dailyDrive";
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 600, "offence.threshold.minor", "750", "4", "R3315-10" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 660, "offence.threshold.serious", "750", "4", "R3315-10"  FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 720, "offence.threshold.verySerious", "1500", "5", "R3315-11" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME) SELECT MAX(ID), 2, "offence.threshold.dayCount" FROM OFFENCE_PART;


INSERT INTO OFFENCE_TYPE (NAME, CLASS, MEMBER_STATE, COMPANY_TYPE) VALUES ("offence.type.weeklyDrive", "transport.offence.WeeklyDrive", 0, 0);

INSERT INTO OFFENCE_PART (OFFENCE_TYPE_ID, NAME) SELECT ID, "offence.basic" FROM OFFENCE_TYPE WHERE NAME = "offence.type.weeklyDrive";
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 3360, "offence.threshold.minor", "750", "4", "R3315-10" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 3600, "offence.threshold.serious", "750", "4", "R3315-10" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 4200, "offence.threshold.verySerious", "1500", "5", "R3315-11" FROM OFFENCE_PART;

INSERT INTO OFFENCE_PART (OFFENCE_TYPE_ID, NAME) SELECT ID, "offence.double" FROM OFFENCE_TYPE WHERE NAME = "offence.type.weeklyDrive";
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 5400, "offence.threshold.minor", "750", "4", "R3315-10" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 6000, "offence.threshold.serious", "750", "4", "R3315-10" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 6750, "offence.threshold.verySerious", "1500", "5", "R3315-11" FROM OFFENCE_PART;


INSERT INTO OFFENCE_TYPE (NAME, CLASS, MEMBER_STATE, COMPANY_TYPE) VALUES ("offence.type.dailyRest", "transport.offence.DailyRest", 0, 0);

INSERT INTO OFFENCE_PART (OFFENCE_TYPE_ID, NAME) SELECT ID, "offence.dr.unreduced" FROM OFFENCE_TYPE WHERE NAME = "offence.type.dailyRest";
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 660, "offence.threshold.minor", "750", "4", "R3315-10" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 600, "offence.threshold.serious", "750", "4", "R3315-10" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 510, "offence.threshold.verySerious", "1500", "5", "R3315-11" FROM OFFENCE_PART;

INSERT INTO OFFENCE_PART (OFFENCE_TYPE_ID, NAME) SELECT ID, "offence.dr.reduced" FROM OFFENCE_TYPE WHERE NAME = "offence.type.dailyRest";
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 540, "offence.threshold.minor", "750", "4", "R3315-10" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 480, "offence.threshold.serious", "750", "4", "R3315-10" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 420, "offence.threshold.verySerious", "1500", "5", "R3315-11" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME) SELECT MAX(ID), 3, "offence.threshold.dayCount" FROM OFFENCE_PART;

INSERT INTO OFFENCE_PART (OFFENCE_TYPE_ID, NAME) SELECT ID, "offence.crew" FROM OFFENCE_TYPE WHERE NAME = "offence.type.dailyRest";
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 540, "offence.threshold.minor", "750", "4", "R3315-10" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 480, "offence.threshold.serious", "750", "4", "R3315-10" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 420, "offence.threshold.verySerious", "1500", "4", "R3315-11" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME) SELECT MAX(ID), 1800, "offence.threshold.period" FROM OFFENCE_PART;

INSERT INTO OFFENCE_PART (OFFENCE_TYPE_ID, NAME) SELECT ID, "offence.splitted" FROM OFFENCE_TYPE WHERE NAME = "offence.type.dailyRest";
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME) SELECT MAX(ID), 720, "offence.threshold" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 540, "offence.threshold.minor", "750", "4", "R3315-10" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 480, "offence.threshold.serious", "750", "4", "R3315-10" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 420, "offence.threshold.verySerious", "1500", "4", "R3315-11" FROM OFFENCE_PART;


INSERT INTO OFFENCE_TYPE (NAME, CLASS, MEMBER_STATE, COMPANY_TYPE) VALUES ("offence.type.weeklyRest", "transport.offence.WeeklyRest", 0, 0);

INSERT INTO OFFENCE_PART (OFFENCE_TYPE_ID, NAME) SELECT ID, "offence.basic" FROM OFFENCE_TYPE WHERE NAME = "offence.type.weeklyRest";
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 2700, "offence.threshold.minor", "750", "4", "R3315-10" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 2520, "offence.threshold.serious", "750", "4", "R3315-10" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 2160, "offence.threshold.verySerious", "1500", "4", "R3315-11" FROM OFFENCE_PART;

INSERT INTO OFFENCE_PART (OFFENCE_TYPE_ID, NAME) SELECT ID, "offence.reduced" FROM OFFENCE_TYPE WHERE NAME = "offence.type.weeklyRest";
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 1440, "offence.threshold.minor", "750", "4", "R3315-10" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 1320, "offence.threshold.serious", "750", "4", "R3315-10" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 1200, "offence.threshold.verySerious", "1500", "4", "R3315-11" FROM OFFENCE_PART;


INSERT INTO OFFENCE_TYPE (NAME, CLASS, MEMBER_STATE, COMPANY_TYPE) VALUES ("offence.type.dailyWork", "transport.offence.DailyWork", 17, 0);

INSERT INTO OFFENCE_PART (OFFENCE_TYPE_ID, NAME) SELECT ID, "offence.basic" FROM OFFENCE_TYPE WHERE NAME = "offence.type.dailyWork";
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 720, "offence.threshold.minor", "750", "4", "R3315-10" FROM OFFENCE_PART;

INSERT INTO OFFENCE_PART (OFFENCE_TYPE_ID, NAME) SELECT ID, "offence.reduced" FROM OFFENCE_TYPE WHERE NAME = "offence.type.dailyWork";
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 600, "offence.threshold.minor", "750", "4", "R3315-10" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME) SELECT MAX(ID), 0, "offence.threshold.begin" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME) SELECT MAX(ID), 300, "offence.threshold.end" FROM OFFENCE_PART;

INSERT INTO OFFENCE_TYPE (NAME, CLASS, MEMBER_STATE, COMPANY_TYPE) VALUES ("offence.type.continuousWork", "transport.offence.ContinuousWork", 17, 0);

INSERT INTO OFFENCE_PART (OFFENCE_TYPE_ID, NAME) SELECT ID, "offence.firstRow" FROM OFFENCE_TYPE WHERE NAME = "offence.type.continuousWork";
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 360, "offence.threshold.minor", "750", "4", "R3315-10" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME) SELECT MAX(ID), 30, "offence.interruption" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME) SELECT MAX(ID), 15, "offence.threshold.firstInterruption" FROM OFFENCE_PART;

INSERT INTO OFFENCE_PART (OFFENCE_TYPE_ID, NAME) SELECT ID, "offence.secondRow" FROM OFFENCE_TYPE WHERE NAME = "offence.type.continuousWork";
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 540, "offence.threshold.minor", "750", "4", "R3315-10" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME) SELECT MAX(ID), 45, "offence.interruption" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME) SELECT MAX(ID), 15, "offence.threshold.firstInterruption" FROM OFFENCE_PART;

INSERT INTO OFFENCE_TYPE (NAME, CLASS, MEMBER_STATE, COMPANY_TYPE) VALUES ("offence.type.weeklyWork", "transport.offence.WeeklyWork", 17, 0);

INSERT INTO OFFENCE_PART (OFFENCE_TYPE_ID, NAME) SELECT ID, "offence.basic" FROM OFFENCE_TYPE WHERE NAME = "offence.type.weeklyWork";
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 3360, "offence.threshold.GR", "750", "4", "R3315-10" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 3120, "offence.threshold.ZC", "750", "4", "R3315-10" FROM OFFENCE_PART;
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 2880, "offence.threshold.ME", "750", "4", "R3315-10" FROM OFFENCE_PART;

INSERT INTO OFFENCE_TYPE (NAME, CLASS, MEMBER_STATE, COMPANY_TYPE) VALUES ("offence.type.manualInput", "transport.offence.DailyManualInput", 0, 0);

INSERT INTO OFFENCE_PART (OFFENCE_TYPE_ID, NAME) SELECT ID, "offence.basic" FROM OFFENCE_TYPE WHERE NAME = "offence.type.manualInput";
INSERT INTO OFFENCE_THRESHOLD (OFFENCE_PART_ID , THRESHOLD, NAME, INFRINGEMENT, INFRINGEMENT_CLASS, REFERENCE) SELECT MAX(ID), 1, "offence.threshold", "750", "4", "Art.1 décret 2010-855" FROM OFFENCE_PART;

INSERT INTO INTERVENTION_TYPE VALUES ('1', 'ETA', 'ETALONNAGE', 0, 730, 0, 0, 0, 1); 

INSERT INTO IMPORT_MODULE (NAME) values ('VEHICLE_SHEET');

INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select 'test.csv', ID, 'FILENAME' FROM IMPORT_MODULE WHERE NAME='VEHICLE_SHEET');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select 'dd/MM/yyyy', ID, 'DATE_FORMAT' FROM IMPORT_MODULE WHERE NAME='VEHICLE_SHEET');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '8', ID, 'IDX_AGENCY' FROM IMPORT_MODULE WHERE NAME='VEHICLE_SHEET');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1',  ID, 'IDX_AGENCY_CODE' FROM IMPORT_MODULE WHERE NAME='VEHICLE_SHEET');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '0', ID, 'IDX_IMMATRICULATION' FROM IMPORT_MODULE WHERE NAME='VEHICLE_SHEET');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '2', ID, 'IDX_DESIGNATION' FROM IMPORT_MODULE WHERE NAME='VEHICLE_SHEET');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_IDENTIFICATION' FROM IMPORT_MODULE WHERE NAME='VEHICLE_SHEET');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '5', ID, 'IDX_FAMILLE' FROM IMPORT_MODULE WHERE NAME='VEHICLE_SHEET');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '1', ID, 'IDX_MARQUE' FROM IMPORT_MODULE WHERE NAME='VEHICLE_SHEET');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_COMMENTAIRE' FROM IMPORT_MODULE WHERE NAME='VEHICLE_SHEET');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '3', ID, 'IDX_DATE_ENTREE' FROM IMPORT_MODULE WHERE NAME='VEHICLE_SHEET');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_DATE_SORTIE' FROM IMPORT_MODULE WHERE NAME='VEHICLE_SHEET');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_ARCHIVAGE' FROM IMPORT_MODULE WHERE NAME='VEHICLE_SHEET');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '4', ID, 'IDX_LOCATION_LIMIT' FROM IMPORT_MODULE WHERE NAME='VEHICLE_SHEET');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '4', ID, 'IDX_GUARANTEE' FROM IMPORT_MODULE WHERE NAME='VEHICLE_SHEET');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_LOCATION_KM' FROM IMPORT_MODULE WHERE NAME='VEHICLE_SHEET');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_LAST_KNOWN_KM' FROM IMPORT_MODULE WHERE NAME='VEHICLE_SHEET');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '7', ID, 'IDX_OWNER' FROM IMPORT_MODULE WHERE NAME='VEHICLE_SHEET');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_LOCATION_PRICE' FROM IMPORT_MODULE WHERE NAME='VEHICLE_SHEET');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_TYPE' FROM IMPORT_MODULE WHERE NAME='VEHICLE_SHEET');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_BODY' FROM IMPORT_MODULE WHERE NAME='VEHICLE_SHEET');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_MODEL' FROM IMPORT_MODULE WHERE NAME='VEHICLE_SHEET');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_AFFECTATION' FROM IMPORT_MODULE WHERE NAME='VEHICLE_SHEET');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_ANALYTIC_CODE' FROM IMPORT_MODULE WHERE NAME='VEHICLE_SHEET');

INSERT INTO IMPORT_MODULE (NAME) values ('ACCIDENT');

INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select 'test.csv', ID, 'FILENAME' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select 'dd/MM/yyyy', ID, 'DATE_FORMAT' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');

INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '1', ID, 'FORCE_VEHICLE_CREATION' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '1', ID, 'FORCE_DRIVER_CREATION' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '1', ID, 'FORCE_GARAGE_CREATION' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');

INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_AGENCY_NAME' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1',  ID, 'IDX_AGENCY_CODE' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_IMMATRICULATION' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_VEHICLE_ID' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_VEHICLE_CODE' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_DRIVER_ID' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_DRIVER_CODE' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_DRIVER_FIRST_NAME' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_DRIVER_LAST_NAME' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_NUMBER' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_DATE' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_PLACE' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_DESCRIPTION' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_RESPONSABILITY' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_TYPE' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_STEAL' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_BIG_DAMAGES' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_TOWING' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_OPENED' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_GARAGE_ID' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_GARAGE_CODE' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_GARAGE_NAME' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_GARAGE_ENTRY' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_GARAGE_OUT' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_RC_COST' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_RC_EXCESS' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_INSURANCE_RECOURSE' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_COMPANY_RECOURSE' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_DAMAGE_EXCESS' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_VAT' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_DILAPIDATION' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_SELF_INSURANCE' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_EXPERT_COST' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_FIXING_COST' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_COMPANY_FIXING_COST' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_LOCATION_DEBT' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_COMPANY_LOCATION_DEBT' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_GUARDING' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
-- valeur de transcription
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '0;0', ID, 'TRN_RESPONSABILITY_0' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '50;1', ID, 'TRN_RESPONSABILITY_1' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '100;2', ID, 'TRN_RESPONSABILITY_2' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select 'CLOS;0', ID, 'TRN_OPEN_0' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select 'OUVERT;1', ID, 'TRN_OPEN_1' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select 'avec tiers identifié;0', ID, 'TRN_TYPE_0' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select 'avec tiers non identifié;1', ID, 'TRN_TYPE_1' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select 'sans tiers identifié;2', ID, 'TRN_TYPE_2' FROM IMPORT_MODULE WHERE NAME='ACCIDENT');

INSERT INTO IMPORT_MODULE (NAME) values ('ROUND_AFFECTATION');

INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select 'round.csv', ID, 'FILENAME' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select 'dd/MM/yyyy', ID, 'DATE_FORMAT' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_ROUND_ID' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_DRIVER_ID' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_DRIVER_FIRST_NAME' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_DRIVER_LAST_NAME' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_VEHICLE_ID' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_IMMATRICULATION' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_DATE' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_SERVICE_POINT' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_CLIENT_COUNT' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_DELIVERY_PARCEL' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_PICKING_PARCEL' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_DONE' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_BEGIN_TIME' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_LOAD_TIME' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_TOTAL_CUT_TIME' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_DELIVERY_TIME' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_RETURN_TIME' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_UNLOAD_TIME' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_TOTAL_TIME' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_STOP_SERVICE_POINT' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_DELIVERED_PARCEL' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_UNDELIVERED_PARCEL' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_PICKED_PARCEL' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_CLIENT_DELIVERED' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'IDX_KM' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');
INSERT INTO IMPORT_FIELD (FIELD_VALUE, IMPORT_MODULE_ID, NAME) (select '-1', ID, 'CREATE' FROM IMPORT_MODULE WHERE NAME='ROUND_AFFECTATION');