ALTER TABLE `user` ADD `pos_access` INT NOT NULL DEFAULT '0' AFTER `access`;
ALTER TABLE `user` CHANGE `rate` `rate` INT(11) NULL DEFAULT '0', CHANGE `lorry` `lorry` INT(11) NULL DEFAULT '0' COMMENT '0=no 1=yes', CHANGE `coupon_id` `coupon_id` INT(11) NULL;
ALTER TABLE `user` CHANGE `temp_password` `temp_password` VARCHAR(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL;

CREATE TABLE `function_hr` (
  `function_id` int(11) NOT NULL,
  `function` varchar(150) DEFAULT NULL,
  `description` varchar(50) NOT NULL,
  `status_id` smallint(6) DEFAULT 0,
  `display` tinyint(1) DEFAULT 0,
  `sort` int(11) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `function_hr` ADD PRIMARY KEY (`function_id`);
ALTER TABLE `function_hr` MODIFY `function_id` int(11) NOT NULL AUTO_INCREMENT;

CREATE TABLE `permission_hr` (
  `permission_id` int(11) NOT NULL,
  `function_id` int(11) DEFAULT 0,
  `user_id` int(11) DEFAULT 0,
  `writecontent` tinyint(1) DEFAULT 0,
  `readcontent` tinyint(1) DEFAULT 0,
  `editcontent` tinyint(1) DEFAULT 0,
  `deletecontent` tinyint(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `permission_hr` ADD PRIMARY KEY (`permission_id`);

ALTER TABLE `permission_hr` MODIFY `permission_id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `user` ADD `basic_salary` INT NULL AFTER `coupon_id`;

CREATE TABLE `holiday` (
  `holiday_id` int(11) NOT NULL,
  `holiday` text NOT NULL,
  `holiday_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `holiday` ADD PRIMARY KEY (`holiday_id`);
ALTER TABLE `holiday` MODIFY `holiday_id` int(11) NOT NULL AUTO_INCREMENT;

CREATE TABLE `allowance` (
  `allowance_id` int(11) NOT NULL,
  `allowance` text NOT NULL,
  `allowance_type` int(11) NOT NULL,
  `driver_rate` int(11) NOT NULL,
  `asst_driver_rate` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `allowance` ADD PRIMARY KEY (`allowance_id`);
ALTER TABLE `allowance` MODIFY `allowance_id` int(11) NOT NULL AUTO_INCREMENT;


CREATE TABLE `calerhome_calerhome`.`advance` (`advance_id` INT NOT NULL AUTO_INCREMENT , `user_id` INT NOT NULL , `advance_amount` INT NOT NULL , `status` INT NOT NULL COMMENT '1=pending 2=approved 3=rejected' , `date_created` DATE NOT NULL , PRIMARY KEY (`advance_id`)) ENGINE = InnoDB;

CREATE TABLE `calerhome_calerhome`.`annual_leave` (`annual_leave_id` INT NOT NULL AUTO_INCREMENT , `below_2` INT NOT NULL , `between_3to5` INT NOT NULL , `above_5` INT NOT NULL , PRIMARY KEY (`annual_leave_id`)) ENGINE = InnoDB;

CREATE TABLE `calerhome_calerhome`.`leaves` (`leaves_id` INT NOT NULL AUTO_INCREMENT , `user_id` INT NOT NULL , `leaves_type` INT NOT NULL , `leaves_from` DATE NOT NULL , `leaves_to` DATE NOT NULL , `day` DECIMAL(10,1) NOT NULL , `remarks` TEXT NOT NULL , `status` INT NOT NULL , `date_created` DATE NOT NULL , `year` INT NOT NULL , PRIMARY KEY (`leaves_id`)) ENGINE = InnoDB;

CREATE TABLE `calerhome_calerhome`.`trip` (`trip_id` INT NOT NULL AUTO_INCREMENT , `user_id` INT NOT NULL , `allowance` TEXT NOT NULL , `lorry_allowance` INT NULL DEFAULT '0' , `date_created` DATE NOT NULL , PRIMARY KEY (`trip_id`)) ENGINE = InnoDB;

ALTER TABLE `trip` ADD `allowance_rate` INT NOT NULL AFTER `lorry_allowance`, ADD `lorry_allowance_rate` INT NOT NULL AFTER `allowance_rate`;