-- ---------------------------------------- --
-- Add column 'hireable' to product table   --
-- ---------------------------------------- --

ALTER TABLE `product` ADD `hireable` INT(10) NOT NULL AFTER `name`;
ALTER TABLE `product` CHANGE `hireable` `hireable` INT(10) NOT NULL DEFAULT '0';

-- ------------------------------------------ --
-- Add hire module trigger in systemsettings  --
-- ------------------------------------------ --

INSERT INTO `systemsettings` (`type`, `value`, `description`, `active`, `created`, `createdById`, `updated`, `updatedById`) VALUES
    ('enable_hire_module', '0', 'Enable the hire module function (0 = disable or 1 = enable)', 1, NOW(), 10, NOW(), 10),
    ('hire_no_prefix', 'HIRE', 'Prefix for Hire Order No', 1, NOW(), 10, NOW(), 10);

-- -------------------------------------------------------- --
-- Add columns isForHire, dateFrom, dateTo, period in order --
-- -------------------------------------------------------- --

ALTER TABLE `order`  ADD `hireId` INT(10) NULL DEFAULT '0' AFTER `customerId`, ADD `dateFrom` DATE NOT NULL DEFAULT '0001-01-01' AFTER `hireId`, ADD `dateTo` DATE NOT NULL DEFAULT '0001-01-01' AFTER `dateFrom`, ADD `period` INT(10) NOT NULL DEFAULT '0' AFTER `dateTo`;
CREATE INDEX `hireId` ON `order`(`hireId`) USING BTREE ;
CREATE INDEX `dateFrom` ON `order`(`dateFrom`) USING BTREE ;
CREATE INDEX `dateTo` ON `order`(`dateTo`) USING BTREE ;
CREATE INDEX `period` ON `order`(`period`) USING BTREE ;

-- ----------------------------- --
-- Edit  productpricetype table  --
-- ----------------------------- --

ALTER TABLE `productpricetype` ADD `isForHire` TINYINT(1) NOT NULL DEFAULT '0' AFTER `needTime`;
ALTER TABLE `productpricetype` ADD INDEX(`isForHire`);
INSERT INTO `productpricetype` (`id`, `name`, `description`, `needTime`, `isForHire`, `active`, `created`, `createdById`, `updated`, `updatedById`) VALUES
    (12, 'Hire Price', 'Hire price per day', '0', '1', '1', NOW(), '10', CURRENT_TIMESTAMP, '10'),
    (13, 'Hire Price Per Day (Weekly Rate)', 'Hire price per day (weekly rate)', '0', '1', '1', NOW(), '10', CURRENT_TIMESTAMP, '10'),
    (14, 'Hire Price Per Day (Monthly Rate)', 'Hire Price Per Day (Monthly Rate)', '0', '1', '1', NOW(), '10', CURRENT_TIMESTAMP, '10');

-- ------------------------------- --
-- Add  hireId to shippment table  --
-- ------------------------------- --

ALTER TABLE `shippment` ADD `hireId` INT(10) NULL DEFAULT NULL AFTER `rmaId`;
ALTER TABLE `shippment` ADD INDEX(`hireId`);

-- ------------------- --
-- Create  hire table  --
-- ------------------- --
DROP TABLE IF EXISTS `hire`;
CREATE TABLE `hire` (
`id`  int(10) NOT NULL AUTO_INCREMENT ,
`hireNo` VARCHAR(100) NULL ,
`hireStart` DATE NOT NULL DEFAULT '0001-01-01' ,
`hireEnd` DATE NOT NULL DEFAULT '0001-01-01' ,
`invoiceType` VARCHAR(100) NULL DEFAULT NULL ,
`invoiceCycleInDays` INT(10) NOT NULL DEFAULT '0' ,
`customerId` INT(10) NOT NULL DEFAULT '0' ,
`type` VARCHAR(100) NULL DEFAULT NULL ,
`hireStatusId` VARCHAR(100) NULL DEFAULT NULL ,
`billingAddrId` INT(10) NOT NULL DEFAULT '0' ,
`shippingAddrId` INT(10) NOT NULL DEFAULT '0' ,
`shippmentId` INT(10) NULL DEFAULT '0' ,
`isPickup` TINYINT(1) NOT NULL DEFAULT '0' ,
`active`  tinyint(1) NOT NULL DEFAULT 1 ,
`created`  datetime NOT NULL DEFAULT '0001-01-01 00:00:00' ,
`createdById`  int(10) NOT NULL DEFAULT 0 ,
`updated`  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
`updatedById`  int(10) NOT NULL DEFAULT 0 ,
PRIMARY KEY (`id`),
INDEX `active` (`active`) USING BTREE ,
INDEX `created` (`created`) USING BTREE ,
INDEX `createdById` (`createdById`) USING BTREE ,
INDEX `updated` (`updated`) USING BTREE ,
INDEX `updatedById` (`updatedById`) USING BTREE ,
INDEX `hireNo` (`hireNo`) USING BTREE ,
INDEX `hireStart` (`hireStart`) USING BTREE,
INDEX `hireEnd` (`hireEnd`) USING BTREE,
INDEX `invoiceType` (`invoiceType`) USING BTREE,
INDEX `invoiceCycleInDays` (`invoiceCycleInDays`) USING BTREE,
INDEX `customerId` (`customerId`) USING BTREE,
INDEX `type` (`type`) USING BTREE,
INDEX `hireStatusId` (`hireStatusId`) USING BTREE,
INDEX `billingAddrId` (`billingAddrId`) USING BTREE,
INDEX `shippingAddrId` (`shippingAddrId`) USING BTREE,
INDEX `shippmentId` (`shippmentId`) USING BTREE,
INDEX `isPickup` (`isPickup`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=Compact;

-- ---------------------------- --
-- Create  hireCheckList table  --
-- ---------------------------- --
DROP TABLE IF EXISTS `hirechecklist`;
CREATE TABLE `hirechecklist` (
`id`  int(10) NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(100) NOT NULL ,
`description` VARCHAR(100) NOT NULL ,
`active`  tinyint(1) NOT NULL DEFAULT 1 ,
`created`  datetime NOT NULL DEFAULT '0001-01-01 00:00:00' ,
`createdById`  int(10) NOT NULL DEFAULT 0 ,
`updated`  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
`updatedById`  int(10) NOT NULL DEFAULT 0 ,
PRIMARY KEY (`id`),
INDEX `active` (`active`) USING BTREE ,
INDEX `created` (`created`) USING BTREE ,
INDEX `createdById` (`createdById`) USING BTREE ,
INDEX `updated` (`updated`) USING BTREE ,
INDEX `updatedById` (`updatedById`) USING BTREE ,
INDEX `name` (`name`) USING BTREE ,
INDEX `description` (`description`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=Compact;

-- ----------------------- --
-- Create  hireItem table  --
-- ----------------------- --
DROP TABLE IF EXISTS `hireitem`;
CREATE TABLE `hireitem` (
`id`  int(10) NOT NULL AUTO_INCREMENT ,
`hireId` INT(10) NOT NULL ,
`productId` INT(10) NOT NULL ,
`unitPrice` DOUBLE(14,4) NOT NULL DEFAULT '0.0000',
`itemDescription` VARCHAR(100) NULL ,
`qtyOrdered` INT(10) NOT NULL DEFAULT '0',
`totalPrice` DOUBLE(14,4) NOT NULL DEFAULT '0.0000',
`active`  tinyint(1) NOT NULL DEFAULT 1 ,
`created`  datetime NOT NULL DEFAULT '0001-01-01 00:00:00' ,
`createdById`  int(10) NOT NULL DEFAULT 0 ,
`updated`  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
`updatedById`  int(10) NOT NULL DEFAULT 0 ,
PRIMARY KEY (`id`),
INDEX `active` (`active`) USING BTREE ,
INDEX `created` (`created`) USING BTREE ,
INDEX `createdById` (`createdById`) USING BTREE ,
INDEX `updated` (`updated`) USING BTREE ,
INDEX `updatedById` (`updatedById`) USING BTREE ,
INDEX `hireId` (`hireId`) USING BTREE ,
INDEX `productId` (`productId`) USING BTREE ,
INDEX `unitPrice` (`unitPrice`) USING BTREE ,
INDEX `itemDescription` (`itemDescription`) USING BTREE ,
INDEX `qtyOrdered` (`qtyOrdered`) USING BTREE ,
INDEX `totalPrice` (`totalPrice`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=Compact;

-- ----------------------- --
-- Create  hireItemInstance table  --
-- ----------------------- --
DROP TABLE IF EXISTS `hireiteminstance`;
CREATE TABLE `hireiteminstance` (
`id`  int(10) NOT NULL AUTO_INCREMENT ,
`hireItemId` INT(10) NOT NULL ,
`partInstanceId` INT(10) NULL ,
`productId` INT(10) NOT NULL ,
`isReserved` TINYINT(1) NOT NULL DEFAULT '0' ,
`isOnHire` TINYINT(1) NOT NULL DEFAULT '0' ,
`isReturned` TINYINT(1) NOT NULL DEFAULT '0' ,
`partInstanceForHireStatusId` INT(10) NOT NULL ,
`unitPrice` DOUBLE(14,4) NOT NULL DEFAULT '0.0000',
`active`  tinyint(1) NOT NULL DEFAULT 1 ,
`created`  datetime NOT NULL DEFAULT '0001-01-01 00:00:00' ,
`createdById`  int(10) NOT NULL DEFAULT 0 ,
`updated`  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
`updatedById`  int(10) NOT NULL DEFAULT 0 ,
PRIMARY KEY (`id`),
INDEX `active` (`active`) USING BTREE ,
INDEX `created` (`created`) USING BTREE ,
INDEX `createdById` (`createdById`) USING BTREE ,
INDEX `updated` (`updated`) USING BTREE ,
INDEX `updatedById` (`updatedById`) USING BTREE ,
INDEX `hireItemId` (`hireItemId`) USING BTREE ,
INDEX `productId` (`productId`) USING BTREE ,
INDEX `partInstanceId` (`partInstanceId`) USING BTREE ,
INDEX `isReserved` (`isReserved`) USING BTREE ,
INDEX `isOnHire` (`isOnHire`) USING BTREE ,
INDEX `isReturned` (`isReturned`) USING BTREE ,
INDEX `partInstanceForHireStatusId` (`partInstanceForHireStatusId`) USING BTREE ,
INDEX `unitPrice` (`unitPrice`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=Compact;

-- ----------------------- --
-- Create  taskItem table  --
-- ----------------------- --
DROP TABLE IF EXISTS `taskitem`;
CREATE TABLE `taskitem` ( `id` INT(10) NOT NULL AUTO_INCREMENT ,  `taskId` INT(10) NOT NULL ,  `partInstanceId` INT(10) NOT NULL ,  `active` TINYINT(1) NOT NULL DEFAULT '1' ,  `created` DATETIME NOT NULL DEFAULT '0001-01-01' ,  `createdById` INT(10) NOT NULL DEFAULT '10' ,  `updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,  `updatedById` INT NOT NULL DEFAULT '10' ,    PRIMARY KEY  (`id`)) ENGINE = InnoDB;
ALTER TABLE `taskitem`
ADD INDEX( `taskId`),
ADD INDEX( `partInstanceId`),
ADD INDEX( `createdById`),
ADD INDEX( `updatedById`);

-- ----------------------- --
-- Create  hireFile table  --
-- ----------------------- --

DROP TABLE IF EXISTS `hirefile`;
CREATE TABLE `hirefile` ( `id` INT(10) NOT NULL AUTO_INCREMENT ,  `hireId` INT(10) NOT NULL ,  `fileName` VARCHAR(100) NOT NULL ,  `fileAssetId` VARCHAR(100) NOT NULL ,  `active` TINYINT(1) NOT NULL DEFAULT '1' ,  `created` DATETIME NOT NULL DEFAULT '0001-01-01 00:00:00' ,  `createdById` INT(10) NOT NULL DEFAULT '10' ,  `updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,  `updatedById` INT(10) NOT NULL DEFAULT '10' ,    PRIMARY KEY  (`id`)) ENGINE = InnoDB;
ALTER TABLE `hirefile`
ADD INDEX( `hireId`),
ADD INDEX( `fileName`),
ADD INDEX( `fileAssetId`),
ADD INDEX( `createdById`),
ADD INDEX( `updatedById`);

-- ----------------------------------- --
-- Create  hirePickupDriverInfo table  --
-- ----------------------------------- --
DROP TABLE IF EXISTS `hirepickupdriverinfo`;
CREATE TABLE `hirepickupdriverinfo` ( `id` INT(10) NOT NULL AUTO_INCREMENT ,  `hireId` INT(10) NOT NULL ,  `customerId` INT(10) NOT NULL ,  `driverName` VARCHAR(100) NOT NULL ,  `licenseNo` VARCHAR(100) NOT NULL ,  `expiryDate` DATETIME NOT NULL DEFAULT '0001-01-01 00:00:00' ,  `active` TINYINT(1) NOT NULL DEFAULT '1' ,  `created` DATETIME NOT NULL DEFAULT '0001-01-01' ,  `createdById` INT(10) NOT NULL DEFAULT '10' ,  `updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,  `updatedById` INT(10) NOT NULL DEFAULT '10' ,    PRIMARY KEY  (`id`)) ENGINE = InnoDB;
ALTER TABLE `hirepickupdriverinfo`
ADD INDEX( `hireId`),
ADD INDEX( `customerId`),
ADD INDEX( `driverName`),
ADD INDEX( `licenseNo`),
ADD INDEX( `expiryDate`),
ADD INDEX( `createdById`),
ADD INDEX( `updatedById`);

-- ------------------------- --
-- Create  hireStatus table  --
-- ------------------------- --

DROP TABLE IF EXISTS `hirestatus`;
CREATE TABLE `hirestatus`(
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL DEFAULT '',
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
  `createdById` int(10) unsigned NOT NULL DEFAULT '0',
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updatedById` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into `hirestatus` (name, created, createdById, updated, updatedById) VALUES ('New', '2019-10-11 00:00:00', 10, '2019-10-11 00:00:00', 10);
insert into `hirestatus` (name, created, createdById, updated, updatedById) VALUES ('On Hire', '2019-10-11 00:00:00', 10, '2019-10-11 00:00:00', 10);
insert into `hirestatus` (name, created, createdById, updated, updatedById) VALUES ('Returned', '2019-10-11 00:00:00', 10, '2019-10-11 00:00:00', 10);
insert into `hirestatus` (name, created, createdById, updated, updatedById) VALUES ('Cancelled', '2019-10-11 00:00:00', 10, '2019-10-11 00:00:00', 10);
