CREATE  TABLE IF NOT EXISTS `logiware`.`un_location` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `un_loc_code` VARCHAR(10) NULL DEFAULT NULL ,
  `un_loc_name` VARCHAR(100) NULL DEFAULT NULL ,
  `countrycode` INT(11) NULL DEFAULT NULL ,
  `statecode` INT(11) NULL DEFAULT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `countrycode` (`countrycode` ASC) ,
  INDEX `statecode` (`statecode` ASC) ,
  INDEX `un_loc_code` (`un_loc_code` ASC) ,
  INDEX `un_loc_name` (`un_loc_name` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 54719
DEFAULT CHARACTER SET = latin1
COMMENT = 'UN Location Codes'
;

CREATE  TABLE IF NOT EXISTS `logiware`.`user_details` (
  `user_id` INT(11) NOT NULL AUTO_INCREMENT ,
  `first_name` VARCHAR(20) NULL DEFAULT NULL ,
  `last_name` VARCHAR(20) NULL DEFAULT NULL ,
  `telephone` VARCHAR(20) NULL DEFAULT NULL ,
  `address1` VARCHAR(50) NULL DEFAULT NULL ,
  `address2` VARCHAR(50) NULL DEFAULT NULL ,
  `country` INT(50) NULL DEFAULT NULL ,
  `state` VARCHAR(50) NULL DEFAULT NULL ,
  `city` INT(40) NULL DEFAULT NULL ,
  `zipcode` VARCHAR(20) NULL DEFAULT NULL ,
  `login_name` VARCHAR(100) NULL DEFAULT NULL ,
  `role` INT(11) NULL DEFAULT NULL ,
  `password` VARCHAR(20) NULL DEFAULT NULL ,
  `status` VARCHAR(20) NULL DEFAULT NULL ,
  `usercreateddate` DATETIME NULL DEFAULT NULL ,
  `email` VARCHAR(50) NULL DEFAULT NULL ,
  `terminal_id` VARCHAR(50) NULL DEFAULT NULL ,
  `extension` VARCHAR(50) NULL DEFAULT NULL ,
  `fax` VARCHAR(50) NULL DEFAULT NULL ,
  `officecitylocation` VARCHAR(5) NULL DEFAULT NULL ,
  `city1` VARCHAR(20) NULL DEFAULT NULL ,
  `ach_approver` TINYINT(1) NOT NULL DEFAULT '0' ,
  `import_terminal_id` VARCHAR(50) NULL DEFAULT NULL ,
  PRIMARY KEY (`user_id`) ,
  INDEX `login_name` (`login_name` ASC) ,
  INDEX `password` (`password` ASC) ,
  INDEX `status_idx` (`status` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 631
DEFAULT CHARACTER SET = latin1
;


CREATE  TABLE IF NOT EXISTS `logiware`.`lcl_relay_plan` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `poo_id` INT(11) NOT NULL ,
  `fd_id` INT(11) NOT NULL ,
  `seg_no` INT(11) NOT NULL ,
  `seg_start_id` INT(11) NOT NULL ,
  `seg_end_id` INT(11) NOT NULL ,
  `trans_mode` ENUM( 'A', 'R', 'T', 'V', '*' ) NOT NULL ,
  `active` TINYINT(1) NOT NULL DEFAULT TRUE ,
  `co_dbd` INT(11) UNSIGNED NULL COMMENT 'Days before Dept' ,
  `co_dow` INT(11) UNSIGNED NULL DEFAULT NULL COMMENT 'Day of Week' ,
  `co_tod` TIME NULL DEFAULT NULL ,
  `tt_published` INT(11) UNSIGNED NOT NULL ,
  `tt_avg_30` INT(11) UNSIGNED NULL DEFAULT NULL ,
  `tt_avg_60` INT(11) UNSIGNED NULL DEFAULT NULL ,
  `tt_avg_90` INT(11) UNSIGNED NULL DEFAULT NULL ,
  `remarks` TEXT NULL DEFAULT NULL ,
  `entered_datetime` DATETIME NOT NULL ,
  `entered_by_user_id` INT(11) NOT NULL ,
  `modified_datetime` DATETIME NOT NULL ,
  `modified_by_user_id` INT(11) NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `lcl_relay_plan_fk1` (`poo_id` ASC) ,
  INDEX `lcl_relay_plan_fk2` (`fd_id` ASC) ,
  INDEX `lcl_relay_plan_fk3` (`seg_start_id` ASC) ,
  INDEX `lcl_relay_plan_fk4` (`seg_end_id` ASC) ,
  INDEX `lcl_relay_plan_fk5` (`entered_by_user_id` ASC) ,
  INDEX `lcl_relay_plan_fk6` (`modified_by_user_id` ASC) ,
  INDEX `lcl_relay_plan_uidx1` (`poo_id` ASC, `fd_id` ASC, `seg_no` ASC, `seg_start_id` ASC, `seg_end_id` ASC, `trans_mode` ASC) ,
  CONSTRAINT `lcl_relay_plan_fk1`
    FOREIGN KEY (`poo_id` )
    REFERENCES `logiware`.`un_location` (`id` )
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `lcl_relay_plan_fk2`
    FOREIGN KEY (`fd_id` )
    REFERENCES `logiware`.`un_location` (`id` )
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `lcl_relay_plan_fk3`
    FOREIGN KEY (`seg_start_id` )
    REFERENCES `logiware`.`un_location` (`id` )
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `lcl_relay_plan_fk4`
    FOREIGN KEY (`seg_end_id` )
    REFERENCES `logiware`.`un_location` (`id` )
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `lcl_relay_plan_fk5`
    FOREIGN KEY (`entered_by_user_id` )
    REFERENCES `logiware`.`user_details` (`user_id` )
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `lcl_relay_plan_fk6`
    FOREIGN KEY (`modified_by_user_id` )
    REFERENCES `logiware`.`user_details` (`user_id` )
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'LCL Relay Plan Table'
;


DELIMITER $$
USE `logiware`$$
CREATE FUNCTION `UnLocationGetIDByCode`( pLocCode VARCHAR(10) ) RETURNS INT(11)
READS SQL DATA
MAIN: BEGIN

	DECLARE mKey INT(11) DEFAULT NULL;

	IF ISNULL( pLocCode ) = FALSE AND TRIM( pLocCode ) <> "" THEN
		SELECT id INTO mKey FROM un_location WHERE un_loc_code = pLocCode LIMIT 1;
	END IF;

	RETURN mKey;

END MAIN $$
DELIMITER ;