use `akura`;

--
-- Function for the Count max leaves for the selected staff leave type 
--
DELIMITER $$

CREATE FUNCTION `count_max_leaves`(maxNoOfDays int, monthId int) RETURNS int(11)
    DETERMINISTIC
BEGIN 
DECLARE maxDays int;
SET monthId = monthId - 1;
SET maxDays = (maxNoOfDays/12)*(12-monthId);
RETURN maxDays;
END$$
DELIMITER ;

--
-- Table structure for table `STAFF_LEAVE_STATUS`
--
CREATE  TABLE `STAFF_LEAVE_STATUS` (

  `STAFF_LEAVE_STATUS_ID` INT(11) NOT NULL AUTO_INCREMENT ,

  `DESCRIPTION` VARCHAR(45) NOT NULL ,

  `MODIFIED_TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,

  PRIMARY KEY (`STAFF_LEAVE_STATUS_ID`) )
ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;

--
-- Insert values for the `STAFF_LEAVE_STATUS`
--
INSERT INTO `STAFF_LEAVE_STATUS` (`STAFF_LEAVE_STATUS_ID`, `DESCRIPTION`) VALUES (1, 'Approved');
INSERT INTO `STAFF_LEAVE_STATUS` (`STAFF_LEAVE_STATUS_ID`, `DESCRIPTION`) VALUES (2, 'Rejected');
INSERT INTO `STAFF_LEAVE_STATUS` (`STAFF_LEAVE_STATUS_ID`, `DESCRIPTION`) VALUES (3, 'In Progress');

--
-- Table structure for table `STAFF` 
--
ALTER TABLE `STAFF_LEAVE` 
ADD COLUMN `STAFF_LEAVE_STATUS_ID` INT(11) NULL , 
ADD COLUMN `APPLIED_DATE` DATE NULL , 
ADD COLUMN `USER_LOGIN_ID` INT(11) NULL , 
ADD COLUMN `APPROVED_REJECTED_DATE` DATE NULL , 
ADD COLUMN `DATE_TYPE` VARCHAR(45) NULL , 
ADD COLUMN `COMMENT` VARCHAR(150) NULL ,
CHANGE COLUMN `NO_OF_DAYS` `NO_OF_DAYS` FLOAT NOT NULL ,
ADD CONSTRAINT `fk_STAFF_LEAVE_STATUS1`

  FOREIGN KEY (`STAFF_LEAVE_STATUS_ID` )

  REFERENCES `STAFF_LEAVE_STATUS` (`STAFF_LEAVE_STATUS_ID` )

  ON DELETE RESTRICT

  ON UPDATE CASCADE , 
ADD CONSTRAINT `fk_STAFF_LEAVE_USER_LOGIN1`

  FOREIGN KEY (`USER_LOGIN_ID` )

  REFERENCES `USER_LOGIN` (`USER_LOGIN_ID` )

  ON DELETE RESTRICT

  ON UPDATE CASCADE ;



