-- ///////////////////////////////////////////////////////////
-- // PROJECTOR                                             //
-- //-------------------------------------------------------//
-- // Version : 6.2.0                                       //
-- // Date : 2017-03-10                                    //
-- ///////////////////////////////////////////////////////////

ALTER TABLE `${prefix}type` ADD `priority` int(3) unsigned DEFAULT NULL;

CREATE TABLE `${prefix}subscription` (
  `id` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `idAffectable` int(12) unsigned DEFAULT NULL,
  `refType` varchar(100) DEFAULT NULL,
  `refId` int(12) unsigned DEFAULT NULL,
  `creationDateTime` datetime DEFAULT NULL,
  `idUser` int(12) unsigned DEFAULT NULL,
  `comment` varchar(4000) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;
CREATE INDEX `subscriptionAffectable` ON `${prefix}subscription` (`idAffectable`);
CREATE INDEX `subscriptionReference` ON `${prefix}subscription` (`refType`,`refId`);

INSERT INTO `${prefix}checklistable` (`id`, `name`, `idle`) VALUES 
(17, 'ProductVersion', '0'),
(18, 'ComponentVersion', '0'),
(19, 'Product', '0'),
(20, 'Component', '0'),
(21, 'Incoming', '0'), 
(22, 'Deliverable', '0');

INSERT INTO `${prefix}linkable` (`id`, `name`, `idle`, `idDefaultLinkable`) VALUES 
(23, 'Bill', '0', '18');

INSERT INTO `${prefix}linkable` (`id`, `name`, `idle`, `idDefaultLinkable`) VALUES 
(24, 'Term', '0', '23');

INSERT INTO `${prefix}habilitationother` (idProfile,scope,rightAccess) VALUES 
(1,'subscription','4'),
(3,'subscription','3');

ALTER TABLE `${prefix}accessscope` ADD `isSpecific` int(1) unsigned DEFAULT 1;
ALTER TABLE `${prefix}accessscope` ADD `nameSpecific` varchar(100) DEFAULT NULL;
UPDATE `${prefix}accessscope` set `isSpecific`=0 WHERE accessCode='RES';
UPDATE `${prefix}accessscope` set `nameSpecific`=replace(`name`,'accessScope','accessScopeSpecific') WHERE `isSpecific`=1;
UPDATE `${prefix}habilitationother` set `rightAccess`='2' WHERE `rightAccess`='5' and `scope` in ('imputation','workValid','diary');

ALTER TABLE `${prefix}statusmail` ADD `mailToSubscribers` int(1) unsigned DEFAULT 0;

ALTER TABLE `${prefix}indicatordefinition` ADD `mailToSubscribers` int(1) unsigned DEFAULT 0,
ADD `alertToSubscribers` int(1) unsigned DEFAULT 0;

CREATE TABLE `${prefix}catalog` (
  `id` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `idCatalogType` int(12) unsigned DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `description` varchar(400) DEFAULT NULL,
  `detail` varchar(400)  DEFAULT NULL,
  `nomenclature` varchar(100)  DEFAULT NULL,
  `specification` mediumtext  DEFAULT NULL,
  `unitCost` decimal(10,2)  DEFAULT NULL,
  `idMeasureUnit` int(12) unsigned DEFAULT NULL,
  `idProduct` int(12) unsigned DEFAULT NULL,
  `idProductVersion` int(12) unsigned DEFAULT NULL,
  `idle` int(1) unsigned DEFAULT '0',
  `quantity` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;

ALTER TABLE `${prefix}billline`
ADD idCatalog int(12) unsigned DEFAULT NULL;

INSERT INTO `${prefix}menu` (`id`, `name`, `idMenu`, `type`, `sortOrder`, `level`, `idle`, `menuClass`) VALUES 
(174, 'menuCatalog', 152, 'object', 286, 'Project', 0, 'Financial'),
(175, 'menuCatalogType', 79, 'object', 858, 'ReadWriteType', 0, 'Type');

INSERT INTO ${prefix}habilitation (idProfile, idMenu, allowAccess) VALUES 
(1, 174, 1),
(2, 174, 1),
(1, 175, 1),
(2, 175, 0),
(3, 175, 0),
(4, 175, 0),
(5, 175, 0),
(6, 175, 0),
(7, 175, 0);

INSERT INTO `${prefix}type` (`scope`, `name`, `idle`) VALUES 
('Catalog', 'Product',0),
('Catalog', 'Service',0);

ALTER TABLE `${prefix}quotation`
ADD `idRecipient` int(12) unsigned DEFAULT NULL;

UPDATE `${prefix}menu` SET `idle`=1 WHERE id=173;

CREATE TABLE `${prefix}extrareadonlyfield` (
  `id` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `scope` varchar(100),
  `idType` int(12) unsigned DEFAULT NULL,
  `field` varchar(100),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci ;

CREATE TABLE `${prefix}extrarequiredfield` (
  `id` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `scope` varchar(100),
  `idType` int(12) unsigned DEFAULT NULL,
  `field` varchar(100),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci ;

INSERT INTO `${prefix}mailable` (`id`, `name`, `idle`) VALUES 
(27,'Incoming', '0'), 
(28,'Deliverable', '0');

INSERT INTO `${prefix}reportparameter` (`idReport`, `name`, `paramType`, `sortOrder`, `defaultValue`) VALUES
(62, 'showIdle', 'boolean', 70, '0');

--- ADD BY Marc TABARY - 2017-03-03 - SET VALUE OF XXX, YYY, ZZZ IN 'alertOverXXXwarningOverYYYokUnderYYY'
ALTER TABLE `${prefix}organization` ADD COLUMN `alertOverPct` INT(3) unsigned NOT NULL DEFAULT '0';
ALTER TABLE `${prefix}organization` ADD COLUMN `warningOverPct` INT(3) unsigned NOT NULL DEFAULT '0';
ALTER TABLE `${prefix}organization` ADD COLUMN `okUnderPct` INT(3) unsigned NOT NULL DEFAULT '0';
--- END ADD BY Marc TABARY - 2017-03-03 - SET VALUE OF XXX, YYY, ZZZ IN 'alertOverXXXwarningOverYYYokUnderYYY'

--- ADD BY Marc TABARY - 2017-03-08 - PERIODIC YEAR BUDGET ELEMENT
ALTER TABLE `${prefix}organization` ADD COLUMN `idleDateTime` DATETIME DEFAULT NULL;
ALTER TABLE `${prefix}budgetelement` ADD COLUMN `idleDateTime` DATETIME DEFAULT NULL;
--- END ADD BY Marc TABARY - 2017-03-08 - PERIODIC YEAR BUDGET ELEMENT

-- ADD BY Marc TABARY - 2017-03-21 - IMPORT ORGANIZATION AND BUDGET ELEMENT
INSERT INTO `${prefix}importable` ( `name`,`idle`) VALUES ('Organization',0);
-- END ADD BY Marc TABARY - 2017-03-21 - IMPORT ORGANIZATION AND BUDGET ELEMENT

--- ADD BY Marc TABARY - 2017-02-21 - ORGANIZATION & RESOURCE VISIBILITY
INSERT INTO `${prefix}list` (`id`, `list`, `name`, `code`, `sortOrder`, `idle`) VALUES
(1001, 'orgaSubOrga', 'displayAll', 'all', 10, 0),
(1002, 'orgaSubOrga', 'displaySubOrga', 'subOrga', 20, 0),
(1003, 'orgaSubOrga', 'displayOrga', 'orga', 30, 0),
(104,'teamOrga','displaySubOrga','subOrga',20,0);

 UPDATE `${prefix}list` SET `sortOrder`=30 WHERE `id`=102;
 UPDATE `${prefix}list` SET `sortOrder`=40 WHERE `id`=103;
--- END ADD BY Marc TABARY - 2017-02-21 - ORGANIZATION & RESOURCE VISIBILITY

ALTER TABLE `${prefix}assignment` ADD `optional` int(1) unsigned DEFAULT '0';

ALTER TABLE `${prefix}project` CHANGE `creationDate` `creationDate` date;
ALTER TABLE `${prefix}callfortender` CHANGE `creationDate` `creationDate` date;
ALTER TABLE `${prefix}tender` CHANGE `creationDate` `creationDate` date;
ALTER TABLE `${prefix}organization` CHANGE `creationDate` `creationDate` date;

ALTER TABLE `${prefix}planningelement` CHANGE `totalAssignedCost` `totalAssignedCost` DECIMAL(12,2);
ALTER TABLE `${prefix}planningelement` CHANGE `totalPlannedCost` `totalPlannedCost` DECIMAL(12,2);
ALTER TABLE `${prefix}planningelement` CHANGE `totalRealCost` `totalRealCost` DECIMAL(12,2);
ALTER TABLE `${prefix}planningelement` CHANGE `totalLeftCost` `totalLeftCost` DECIMAL(12,2);
ALTER TABLE `${prefix}planningelement` CHANGE `totalValidatedCost` `totalValidatedCost` DECIMAL(12,2);
ALTER TABLE `${prefix}planningelement` CHANGE `reserveAmount` `reserveAmount` DECIMAL(12,2);

ALTER TABLE `${prefix}budgetelement` CHANGE `totalAssignedCost` `totalAssignedCost` DECIMAL(12,2);
ALTER TABLE `${prefix}budgetelement` CHANGE `totalPlannedCost` `totalPlannedCost` DECIMAL(12,2);
ALTER TABLE `${prefix}budgetelement` CHANGE `totalRealCost` `totalRealCost` DECIMAL(12,2);
ALTER TABLE `${prefix}budgetelement` CHANGE `totalLeftCost` `totalLeftCost` DECIMAL(12,2);
ALTER TABLE `${prefix}budgetelement` CHANGE `totalValidatedCost` `totalValidatedCost` DECIMAL(12,2);
ALTER TABLE `${prefix}budgetelement` CHANGE `reserveAmount` `reserveAmount` DECIMAL(12,2);