-- ///////////////////////////////////////////////////////////
-- // PROJECTOR                                             //
-- //-------------------------------------------------------//
-- // Version : 5.1.0                                       //
-- // Date : 2015-07-30                                     //
-- ///////////////////////////////////////////////////////////

CREATE TABLE `${prefix}paymentdelay` (
  `id` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100),
  `days` int(3) unsigned DEFAULT NULL,
  `endOfMonth` int(1) DEFAULT 0,
  `sortOrder` int(3) DEFAULT 0,
  `idle` int(1) DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci ;

INSERT INTO `${prefix}paymentdelay` (`id`, `name`, `days`, `endOfMonth`, `sortOrder`, `idle`) VALUES
(1, '15 days', 15, 0, 10, 0),
(2, '15 days end of month', 15, 1, 20, 0),
(3, '30 days', 30, 0, 30, 0),
(4, '30 days end of month', 30, 1, 40, 0),
(5, '45 days', 45, 0, 50, 0),
(6, '45 days end of month', 45, 1, 60, 0),
(7, '60 days', 60, 0, 70, 0),
(8, 'on order', 0, 0, 80, 0);

CREATE TABLE `${prefix}paymentmode` (
  `id` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100),
  `sortOrder` int(3) DEFAULT 0,
  `idle` int(1) DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci ;

INSERT INTO `${prefix}paymentmode` (`id`, `name`, `sortOrder`, `idle`) VALUES
(1, 'bank transfer', 10, 0),
(2, 'cheque', 20, 0),
(3, 'credit card', 30, 0),
(4, 'virtual payment terminal', 40, 0),
(5, 'paypal', 50, 0);

CREATE TABLE `${prefix}payment` (
  `id` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100),
  `idBill` int(12) unsigned DEFAULT NULL,
  `paymentDate` date,
  `idPaymentMode` int(12) unsigned DEFAULT NULL,
  `idle` int(1) DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci ;

CREATE INDEX paymentBill ON `${prefix}payment` (idBill);

ALTER TABLE `${prefix}client` ADD `numTax` varchar(100) DEFAULT NULL,
ADD `idPaymentDelay` int(12) unsigned DEFAULT NULL;

ALTER TABLE `${prefix}client` CHANGE `designation` `designation`  varchar (100),
CHANGE `street` `street`  varchar (100),
CHANGE `complement` `complement`  varchar (100),
CHANGE `city` `city`  varchar (100),
CHANGE `state` `state`  varchar (100),
CHANGE `country` `country`  varchar (100);

CREATE TABLE `${prefix}measureunit` (
  `id` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100),
  `pluralName` varchar(100),
  `sortOrder` int(3) DEFAULT 0,
  `idle` int(1) DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci ;

INSERT INTO `${prefix}measureunit` (`id`, `name`, `pluralName`, `sortOrder`, `idle`) VALUES
(1, 'piece', 'pieces', 10, 0),
(2, 'lot', 'lots', 20, 0),
(3, 'day', 'days', 30, 0),
(4, 'month', 'months', 40, 0);

ALTER TABLE `${prefix}billline` ADD `idMeasureUnit` int(12) unsigned DEFAULT NULL,
ADD `extra` int(1) UNSIGNED DEFAULT 0;

ALTER TABLE `${prefix}bill` ADD `idPaymentDelay` int(12) unsigned DEFAULT NULL,
ADD `paymentDueDate` date DEFAULT NULL,
ADD `idDeliveryMode` int(12) unsigned DEFAULT NULL,
ADD `idResource` int(12) unsigned DEFAULT NULL,
ADD `idUser` int(12) unsigned DEFAULT NULL,
ADD `creationDate` date,
ADD `paymentsCount` int(3) default 0;

UPDATE `${prefix}bill` b set `idUser` = (select idUser from `${prefix}history` h where h.refType='Bill' and h.refId=b.id order by operationDate LIMIT 1); 

ALTER TABLE `${prefix}quotation` ADD `idPaymentDelay` int(12) unsigned DEFAULT NULL,
ADD `tax` decimal(5,2) DEFAULT NULL,
ADD `fullAmount` decimal(12,2) DEFAULT NULL,
ADD `idDeliveryMode` int(12) unsigned DEFAULT NULL;
ALTER TABLE `${prefix}quotation` CHANGE `initialWork` `untaxedAmount` DECIMAL(11,2) UNSIGNED;

ALTER TABLE `${prefix}command` ADD `idPaymentDelay` int(12) unsigned DEFAULT NULL,
ADD `tax` decimal(5,2) DEFAULT NULL,
ADD `fullAmount` decimal(12,2) DEFAULT NULL,
ADD `addFullAmount` decimal(12,2) DEFAULT NULL,
ADD `totalFullAmount` decimal(12,2) DEFAULT NULL,
ADD `idDeliveryMode` int(12) unsigned DEFAULT NULL;
ALTER TABLE `${prefix}command` CHANGE `initialAmount` `untaxedAmount` DECIMAL(11,2);
ALTER TABLE `${prefix}command` CHANGE `addAmount` `addUntaxedAmount` DECIMAL(11,2);
ALTER TABLE `${prefix}command` CHANGE `validatedAmount` `totalUntaxedAmount` DECIMAL(11,2);

CREATE TABLE `${prefix}deliverymode` (
  `id` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100),
  `sortOrder` int(3) DEFAULT 0,
  `idle` int(1) DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci ;

INSERT INTO `${prefix}deliverymode` (`id`, `name`, `sortOrder`, `idle`) VALUES
(1, 'email', 10, 0),
(2, 'postal mail', 20, 0),
(3, 'hand delivered', 30, 0),
(4, 'digital deposit', 40, 0);

INSERT INTO `${prefix}menu` (`id`, `name`, `idMenu`, `type`, `sortOrder`, `level`, `idle`, `menuClass`) VALUES
(137, 'menuPaymentDelay', 36, 'object', 785, 'ReadWriteList', 0, 'ListOfValues '),
(138, 'menuPaymentMode', 36, 'object', 786, 'ReadWriteList', 0, 'ListOfValues '),
(139, 'menuDeliveryMode', 36, 'object', 787, 'ReadWriteList', 0, 'ListOfValues '),
(140, 'menuMeasureUnit', 36, 'object', 788, 'ReadWriteList', 0, 'ListOfValues ');
INSERT INTO `${prefix}habilitation` (`idProfile`, `idMenu`, `allowAccess`) VALUES
(1, 137, 1),
(2, 137, 0),
(3, 137, 0),
(4, 137, 0),
(5, 137, 0),
(6, 137, 0),
(7, 137, 0),
(1, 138, 1),
(2, 138, 0),
(3, 138, 0),
(4, 138, 0),
(5, 138, 0),
(6, 138, 0),
(7, 138, 0),
(1, 139, 1),
(2, 139, 0),
(3, 139, 0),
(4, 139, 0),
(5, 139, 0),
(6, 139, 0),
(7, 139, 0),
(1, 140, 1),
(2, 140, 0),
(3, 140, 0),
(4, 140, 0),
(5, 140, 0),
(6, 140, 0),
(7, 140, 0);

ALTER TABLE `${prefix}decision` ADD `done` int(1) unsigned DEFAULT 0;
UPDATE `${prefix}decision` set done=1 where idStatus in (select id from `${prefix}status` where setDoneStatus=1);

CREATE TABLE `${prefix}favorite` (
  `id` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `idUser` int(12) unsigned DEFAULT NULL,
  `scope` varchar(100) DEFAULT NULL,
  `idReport` int(12) unsigned DEFAULT NULL,
  `idMenu` int(12) unsigned DEFAULT NULL,
  `sortOrder` int(3) unsigned DEFAULT NULL,
  `idle` int(1) unsigned DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=innoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci ;

CREATE INDEX favoriteUser ON `${prefix}favorite` (idUser);

CREATE TABLE `${prefix}favoriteparameter` (
  `id` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `idUser` int(12) unsigned DEFAULT NULL,
  `idReport` int(12) unsigned DEFAULT NULL,
  `idFavorite` int(12) unsigned DEFAULT NULL,
  `parameterName` varchar(100) DEFAULT NULL,
  `parameterValue` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=innoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci ;

CREATE INDEX favoriteParameterUser ON `${prefix}favoriteparameter` (idUser);
CREATE INDEX favoriteParameterReport ON `${prefix}favoriteparameter` (idReport);
CREATE INDEX favoriteParameterToday ON `${prefix}favoriteparameter` (idFavorite);

UPDATE `${prefix}menu` SET idle=0 where `name` in ('menuPayment', 'menuPaymentType'); 
DELETE FROM `${prefix}habilitation` WHERE idMenu in (78, 83);
INSERT INTO `${prefix}habilitation` (`idProfile`, `idMenu`, `allowAccess`) VALUES
(1, 78, 1),
(2, 78, 1),
(3, 78, 1),
(4, 78, 0),
(5, 78, 0),
(6, 78, 0),
(7, 78, 0),
(1, 83, 1),
(2, 83, 0),
(3, 83, 0),
(4, 83, 0),
(5, 83, 0),
(6, 83, 0),
(7, 83, 0);

ALTER TABLE `${prefix}payment` ADD `idPaymentType` int(12) unsigned DEFAULT NULL,
ADD `paymentAmount`  DECIMAL(11,2) UNSIGNED,
ADD `paymentFeeAmount`  DECIMAL(11,2) UNSIGNED,
ADD `paymentCreditAmount` DECIMAL(11,2) UNSIGNED,
ADD `description` mediumtext,
ADD `idUser` int(12) unsigned DEFAULT NULL,
ADD `creationDate` date,
ADD `referenceBill` varchar(100) DEFAULT NULL,
ADD `idClient` int(12) unsigned DEFAULT NULL,
ADD `idRecipient` int(12) unsigned DEFAULT NULL;

DELETE FROM `${prefix}type` WHERE `scope`='Payment' and `name`='event payment';
UPDATE `${prefix}type` SET sortOrder=10 WHERE `scope`='Payment' and `name`='final payment';

ALTER TABLE `${prefix}term` ADD `idUser` int(12) unsigned DEFAULT NULL,
ADD `creationDate` date;

ALTER TABLE `${prefix}activityprice` ADD `idUser` int(12) unsigned DEFAULT NULL,
ADD `creationDate` date;

ALTER TABLE `${prefix}quotation` ADD `idLikelihood` int(12) unsigned DEFAULT NULL,
ADD `plannedWork` decimal(12,2) DEFAULT 0;

ALTER TABLE `${prefix}bill` ADD `commandAmountPct` int(3) unsigned DEFAULT 100,
ADD `sendDate` date;

ALTER TABLE `${prefix}command` ADD `receptionDate` date;

INSERT INTO `${prefix}importable` (`name`, `idle`) VALUES ('Work', '0');

ALTER TABLE `${prefix}likelihood` ADD `valuePct` int(3) unsigned DEFAULT 0;
UPDATE `${prefix}likelihood` SET `valuePct`='10' WHERE name like '%10%';
UPDATE `${prefix}likelihood` SET `valuePct`='50' WHERE name like '%50%';
UPDATE `${prefix}likelihood` SET `valuePct`='90' WHERE name like '%90%';

ALTER TABLE `${prefix}risk` ADD `impactCost` DECIMAL(11,2) UNSIGNED DEFAULT 0,
ADD `projectReserveAmount` DECIMAL(11,2) UNSIGNED DEFAULT 0;

ALTER TABLE `${prefix}opportunity` ADD `impactCost` DECIMAL(11,2) UNSIGNED DEFAULT 0,
ADD `projectReserveAmount` DECIMAL(11,2) UNSIGNED DEFAULT 0;

ALTER TABLE `${prefix}planningelement` ADD `reserveAmount` DECIMAL(11,2) UNSIGNED DEFAULT 0;

ALTER TABLE `${prefix}payment` ADD `billAmount` DECIMAL(11,2) UNSIGNED;

UPDATE `${prefix}accessright` SET idAccessProfile=1 WHERE idAccessProfile=2 and idProfile in (6,7);