-- -----------------------------------------------------
-- Table `item_request_header`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `item_request_header` (
  `id` VARCHAR(25) NOT NULL ,
  `location_id` INT NOT NULL ,
  `user_id` INT NOT NULL ,
  `department_id` INT NOT NULL ,
  `date_time` DATETIME NOT NULL ,
  `cancelled` TINYINT(1)  NULL ,
  `created_by` INT NULL ,
  `created_date` DATETIME NULL ,
  `last_edited_by` INT NULL ,
  `last_edited_date` DATETIME NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_Request_location1` (`location_id` ASC) ,
  INDEX `fk_Request_user1` (`user_id` ASC) ,
  INDEX `fk_Request_department1` (`department_id` ASC) ,
  CONSTRAINT `fk_Request_location1`
    FOREIGN KEY (`location_id` )
    REFERENCES `location` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Request_user1`
    FOREIGN KEY (`user_id` )
    REFERENCES `user` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Request_department1`
    FOREIGN KEY (`department_id` )
    REFERENCES `department` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `item`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `item` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(50) NOT NULL ,
  `description` VARCHAR(255) NULL ,
  `status` ENUM('Active','Inactive') NOT NULL ,
  `created_by` INT NULL ,
  `created_date` DATETIME NULL ,
  `last_edited_by` INT NULL ,
  `last_edited_date` DATETIME NULL ,
  `location_id` INT NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `name_UNIQUE` (`name` ASC) ,
  INDEX `fk_item_location1` (`location_id` ASC) ,
  CONSTRAINT `fk_item_location1`
    FOREIGN KEY (`location_id` )
    REFERENCES `location` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `item_request_detail`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `item_request_detail` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `item_id` INT NOT NULL ,
  `item_request_header_id` VARCHAR(25) NOT NULL ,
  `quantity` TINYINT NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_item_request_detail_item1` (`item_id` ASC) ,
  INDEX `fk_item_request_detail_item_request_header1` (`item_request_header_id` ASC) ,
  CONSTRAINT `fk_item_request_detail_item1`
    FOREIGN KEY (`item_id` )
    REFERENCES `item` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_item_request_detail_item_request_header1`
    FOREIGN KEY (`item_request_header_id` )
    REFERENCES `item_request_header` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


CREATE VIEW v_report_item_summary_for_department AS
	SELECT 
	item.id AS id, department.name  AS 'department', department.id as  department_id,item_request_header.date_time AS 'date_time',
	item.name as item_name, quantity, location.code as location_code
	,location.id as location_id
		FROM `item_request_header` 
			inner JOIN  location on item_request_header.location_id = location.id
                        inner JOIN  department on item_request_header.department_id = department.id
			
                        inner JOIN item_request_detail on item_request_detail.item_request_header_id = item_request_header.id
			inner JOIN item on item_request_detail.item_id = item.id
			Where item_request_header.cancelled = 0 ;
