ALTER TABLE `offence_threshold` ADD COLUMN `INFRINGEMENT` INTEGER UNSIGNED DEFAULT 0;
ALTER TABLE `offence_threshold` ADD COLUMN `INFRINGEMENT_CLASS` INTEGER UNSIGNED DEFAULT 0;
ALTER TABLE `offence_threshold` ADD COLUMN `REFERENCE` VARCHAR(32) DEFAULT NULL;

update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-10" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.continuousDrive") and name = "offence.basic") and name = "offence.threshold.minor";
update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-10" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.continuousDrive") and name = "offence.basic") and name = "offence.threshold.serious";
update offence_threshold set infringement = "1500", infringement_class= "5", reference = "R3315-11" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.continuousDrive") and name = "offence.basic") and name = "offence.threshold.verySerious";

update offence_threshold set infringement = "750", infringement_class= "4", reference = "" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.continuousDriveTraveller") and name = "offence.basic") and name = "offence.threshold.minor";
update offence_threshold set infringement = "750", infringement_class= "4", reference = "" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.continuousDriveTraveller") and name = "offence.basic") and name = "offence.threshold.serious";
update offence_threshold set infringement = "1500", infringement_class= "5", reference = "" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.continuousDriveTraveller") and name = "offence.basic") and name = "offence.threshold.verySerious";

update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-10" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.dailyDrive") and name = "offence.dd.extended") and name = "offence.threshold.minor";
update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-10" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.dailyDrive") and name = "offence.dd.extended") and name = "offence.threshold.serious";
update offence_threshold set infringement = "1500", infringement_class= "5", reference = "R3315-11" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.dailyDrive") and name = "offence.dd.extended") and name = "offence.threshold.verySerious";
update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-10" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.dailyDrive") and name = "offence.dd.unextended") and name = "offence.threshold.minor";
update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-10" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.dailyDrive") and name = "offence.dd.unextended") and name = "offence.threshold.serious";
update offence_threshold set infringement = "1500", infringement_class= "5", reference = "R3315-11" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.dailyDrive") and name = "offence.dd.unextended") and name = "offence.threshold.verySerious";

update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-10" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.weeklyDrive") and name = "offence.basic") and name = "offence.threshold.minor";
update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-10" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.weeklyDrive") and name = "offence.basic") and name = "offence.threshold.serious";
update offence_threshold set infringement = "1500", infringement_class= "5", reference = "R3315-11" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.weeklyDrive") and name = "offence.basic") and name = "offence.threshold.verySerious";

update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-10" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.weeklyDrive") and name = "offence.double") and name = "offence.threshold.minor";
update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-10" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.weeklyDrive") and name = "offence.double") and name = "offence.threshold.serious";
update offence_threshold set infringement = "1500", infringement_class= "5", reference = "R3315-11" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.weeklyDrive") and name = "offence.double") and name = "offence.threshold.verySerious";

update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-10" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.dailyRest") and name = "offence.dr.reduced") and name = "offence.threshold.minor";
update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-10" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.dailyRest") and name = "offence.dr.reduced") and name = "offence.threshold.serious";
update offence_threshold set infringement = "1500", infringement_class= "5", reference = "R3315-11" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.dailyRest") and name = "offence.dr.reduced") and name = "offence.threshold.verySerious";

update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-10" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.dailyRest") and name = "offence.dr.unreduced") and name = "offence.threshold.minor";
update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-10" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.dailyRest") and name = "offence.dr.unreduced") and name = "offence.threshold.serious";
update offence_threshold set infringement = "1500", infringement_class= "5", reference = "R3315-11" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.dailyRest") and name = "offence.dr.unreduced") and name = "offence.threshold.verySerious";

update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-10" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.dailyRest") and name = "offence.splitted") and name = "offence.threshold.minor";
update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-10" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.dailyRest") and name = "offence.splitted") and name = "offence.threshold.serious";
update offence_threshold set infringement = "1500", infringement_class= "5", reference = "R3315-11" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.dailyRest") and name = "offence.splitted") and name = "offence.threshold.verySerious";

update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-10" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.dailyRest") and name = "offence.crew") and name = "offence.threshold.minor";
update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-10" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.dailyRest") and name = "offence.crew") and name = "offence.threshold.serious";
update offence_threshold set infringement = "1500", infringement_class= "5", reference = "R3315-11" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.dailyRest") and name = "offence.crew") and name = "offence.threshold.verySerious";

update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-10" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.weeklyRest") and name = "offence.basic") and name = "offence.threshold.minor";
update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-10" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.weeklyRest") and name = "offence.basic") and name = "offence.threshold.serious";
update offence_threshold set infringement = "1500", infringement_class= "5", reference = "R3315-11" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.weeklyRest") and name = "offence.basic") and name = "offence.threshold.verySerious";

update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-10" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.weeklyRest") and name = "offence.reduced") and name = "offence.threshold.minor";
update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-10" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.weeklyRest") and name = "offence.reduced") and name = "offence.threshold.serious";
update offence_threshold set infringement = "1500", infringement_class= "5", reference = "R3315-11" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.weeklyRest") and name = "offence.reduced") and name = "offence.threshold.verySerious";

update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-5" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.dailyWork") and name = "offence.basic") and name = "offence.threshold.minor";
update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-3" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.dailyWork") and name = "offence.reduced") and name = "offence.threshold.minor";

update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-5"  where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.continuousWork") and name = "offence.firstRow") and name = "offence.threshold.minor";
update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-5"  where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.continuousWork") and name = "offence.secondRow") and name = "offence.threshold.minor";

update offence_threshold set infringement = "750", infringement_class= "4", reference = "R3315-5" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.weeklyWork") and name = "offence.basic");

update offence_threshold set infringement = "1500", infringement_class= "5", reference = "Art.1 décret 2010-855" where offence_part_id = (select id from offence_part  where offence_type_id = (select id from offence_type where name = "offence.type.manualInput"));

ALTER TABLE offence ADD COLUMN cost integer unsigned default 0;

update offence set cost = 750 where type in (1,2);
update offence set cost = 1500 where type in (3);

alter table t_frais add column max_distance integer(5) default -1;
alter table t_frais add column from_home tinyint unsigned default 0;
alter table t_frais add column from_agency tinyint unsigned default 0;
alter table t_frais add column from_company tinyint unsigned default 0;

alter table t_conducteur add column latitude float default 0;
alter table t_conducteur add column longitude float default 0;

UPDATE TIMS.CONFIGURATION SET EVAL = "4.5.0" WHERE VALUE = "database_version";