-- MySQL Script generated by MySQL Workbench
-- Mon Apr 25 10:09:26 2022
-- Model: New Model    Version: 1.0


-- -----------------------------------------------------
-- Schema mse
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mse` DEFAULT CHARACTER SET utf8 ;
-- -----------------------------------------------------
-- Schema maintainance
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema maintainance
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `maintainance` ;
USE `mse` ;

-- -----------------------------------------------------
-- Table `mse`.`mse`
-- -----------------------------------------------------
CREATE TABLE `mse` (
  `mse_id` VARCHAR(10) NOT NULL,
  `mse_description` VARCHAR(2000) NULL,
  `mse_createddate` DATETIME NULL,
  `mse_createdby` VARCHAR(250) NULL,
  PRIMARY KEY (`mse_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mse`.`introduction`
-- -----------------------------------------------------
CREATE TABLE `introduction` (
  `introduction_id` VARCHAR(10) NOT NULL,
  `intro_fl` VARCHAR(250) NULL, NULL COMMENT '10a - mse_title \n 20b - mse_intro',
  `intro_description` VARCHAR(2000) NULL,
  `intro_createdby` VARCHAR(250) NULL,
  `intro_createddate` DATETIME NULL,
  `mse_id` VARCHAR(10) NULL,
   PRIMARY KEY (`introduction_id`),
    FOREIGN KEY (`mse_id`)
    REFERENCES `mse`.`mse` (`mse_id`)
   )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mse`.`bg_threats`
-- -----------------------------------------------------
CREATE TABLE `bg_threats` (
  `bg_threats_id` VARCHAR(10) NOT NULL,
  `bg_threats_descriptions` VARCHAR(2000) NULL,
  `bg_threats_fl` VARCHAR(20) NULL COMMENT 'bg0101- international & external threats\nbg0102 - nature of threat\nbg0103 -source of threat\nbg0104 - perception of threat\nbg0105 - vulnerabilities',
  `bg_threats_createdby` VARCHAR(250) NULL,
  `bg_threats_createddate` DATETIME NULL,
  `mse_id` VARCHAR(10) NULL,
  PRIMARY KEY (`bg_threats_id`),
    FOREIGN KEY (`mse_id`)
    REFERENCES `mse`.`mse` (`mse_id`)
)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maintainance`.`country`
-- -----------------------------------------------------
CREATE TABLE `country` (
  `country_id` VARCHAR(10) NOT NULL,
  `country_description` VARCHAR(250) NULL,
  `country_createdby` VARCHAR(250) NULL,
  `country_createddate` DATETIME NULL,
  PRIMARY KEY (`country_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maintainance`.`operation_type`
-- -----------------------------------------------------
CREATE TABLE `operation_type` (
  `operation_type_id` VARCHAR(10) NOT NULL,
  `operation_type_name` VARCHAR(250) NULL,
  `operation_type_createdby` VARCHAR(250) NULL,
  `operation_type_createddate` DATETIME NULL,
  PRIMARY KEY (`operation_type_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maintainance`.`timezone`
-- -----------------------------------------------------
CREATE TABLE `timezone` (
  `timezone_id` VARCHAR(10) NOT NULL,
  `timezone_name` VARCHAR(45) NULL,
  `timezone_createdby` VARCHAR(250) NULL,
  `timezone_createddate` DATETIME NULL,
  PRIMARY KEY (`timezone_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maintainance`.`planning_type`
-- -----------------------------------------------------
CREATE TABLE `planning_type` (
  `planning_type_id` VARCHAR(10) NOT NULL,
  `planning_type_description` VARCHAR(250) NULL,
  `planning_type_createdby` VARCHAR(250) NULL,
  `planning_type_createddate` DATETIME NULL,
  PRIMARY KEY (`planning_type_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maintainance`.`organization`
-- -----------------------------------------------------
CREATE TABLE `organization` (
  `organization_id` VARCHAR(10) NOT NULL,
  `organization_description` VARCHAR(250) NULL,
  `organization_createdby` VARCHAR(250) NULL,
  `organization_createddate` DATETIME NULL,
  PRIMARY KEY (`organization_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maintainance`.`user`
-- -----------------------------------------------------
CREATE TABLE `user` (
  `user_id` VARCHAR(10) NOT NULL,
  `user_name` VARCHAR(250) NULL,
  `user_military_id` VARCHAR(250) NULL,
  PRIMARY KEY (`user_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mse`.`create_mission`
-- -----------------------------------------------------
CREATE TABLE `create_mission` (
  `create_mission_id` VARCHAR(10) NOT NULL,
  `mse_id` VARCHAR(10) NULL,
  `country_id` VARCHAR(10) NULL,
  `operation_type_id` VARCHAR(10) NULL,
  `timezone_id` VARCHAR(10) NULL,
  `user_id` VARCHAR(10) NULL,
  `organization_id` VARCHAR(10) NULL,
  `planning_type_id` VARCHAR(10) NULL,
  `op_name` VARCHAR(250) NULL,
  `op_description` VARCHAR(2000) NULL,
  `create_mission_createdby` VARCHAR(250) NULL,
  `create_mission_createddate` DATETIME NULL,
  PRIMARY KEY (`create_mission_id`),
    FOREIGN KEY (`mse_id`)
    REFERENCES `mse`.`mse` (`mse_id`)
    FOREIGN KEY (`country_id`)
    REFERENCES `maintainance`.`country` (`country_id`)
    FOREIGN KEY (`operation_type_id`)
    REFERENCES `maintainance`.`operation_type` (`operation_type_id`)
    FOREIGN KEY (`timezone_id`)
    REFERENCES `maintainance`.`timezone` (`timezone_id`)
    FOREIGN KEY (`planning_type_id`)
    REFERENCES `maintainance`.`planning_type` (`planning_type_id`)
    FOREIGN KEY (`organization_id`)
    REFERENCES `maintainance`.`organization` (`organization_id`)
    FOREIGN KEY (`user_id`)
    REFERENCES `maintainance`.`user` (`user_id`)
)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mse`.`bg_dependent`
-- -----------------------------------------------------
CREATE TABLE `bg_dependent` (
  `bg_dependent_id` VARCHAR(10) NOT NULL,
  `bg_dependent_description` VARCHAR(2000) NULL,
  `bg_dependent_fl` VARCHAR(20) NULL COMMENT 'bg0201 - alliances\nbg0202 - dependencies & interdependencies',
  `bg_dependent_createdby` VARCHAR(250) NULL,
  `bg_dependent_createddate` DATETIME NULL,
  `mse_id` VARCHAR(10) NULL,
  PRIMARY KEY (`bg_dependent_id`),
    FOREIGN KEY (`mse_id`)
    REFERENCES `mse`.`mse` (`mse_id`)
)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mse`.`strategic_analysis`
-- -----------------------------------------------------
CREATE TABLE`strategic_analysis` (
  `strategic_analysis_id` VARCHAR(10) NOT NULL,
  `strategic_analysis_description` VARCHAR(2000) NULL,
  `strategic_analysis_fl` VARCHAR(20) NULL COMMENT 'a1 - strategic environment\na2 - whole of gov response\na3 - strategic defence interests\na4 - strategic defence objective\na5 - risk consideration and opportunities\n',
  `strategic_analysis_createdby` VARCHAR(250) NULL,
  `strategic_analysis_createddate` DATETIME NULL,
  `mse_id` VARCHAR(10) NULL,
  PRIMARY KEY (`strategic_analysis_id`),
    FOREIGN KEY (`mse_id`)
    REFERENCES `mse`.`mse` (`mse_id`)
)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mse`.`CDFGuidance`
-- -----------------------------------------------------
CREATE TABLE `CDFGuidance` (
  `CDFGuidance_id` VARCHAR(10) NOT NULL,
  `CDFGuidance_description` VARCHAR(2000) NULL,
  `CDFGuidance_fl` VARCHAR(20) NULL COMMENT 'b1 - mission\nb2 - intent_purpose\nb3 - intent_method\nb4 - intent_endstate\nb5 - limitation_restriction\nb6 - limitation_constraint\nb7 - limitation_info',
  `CDFGuidance_createdby` VARCHAR(250) NULL,
  `CDFGuidance_createddate` DATETIME NULL,
  `mse_id` VARCHAR(10) NULL,
  PRIMARY KEY (`CDFGuidance_id`),
    FOREIGN KEY (`mse_id`)
    REFERENCES `mse`.`mse` (`mse_id`)
)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mse`.`option`
-- -----------------------------------------------------
CREATE TABLE `option` (
  `option_id` VARCHAR(10) NOT NULL,
  `option_description` VARCHAR(2500) NULL,
  `option_createdby` VARCHAR(250) NULL,
  `option_createddate` DATETIME NULL,
  `mse_id` VARCHAR(10) NULL,
  `option_fl` VARCHAR(20) NULL COMMENT 'c1 - problem_statement\nc2 - planning_assumption\nc3 - planning_coa\nc4 - transition_strategies\nc5-consultation',
  PRIMARY KEY (`option_id`),
    FOREIGN KEY (`mse_id`)
    REFERENCES `mse`.`mse` (`mse_id`)
 )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mse`.`conclusion`
-- -----------------------------------------------------
CREATE TABLE `conclusion` (
  `conclusion_id` VARCHAR(10) NOT NULL,
  `conclusion_description` VARCHAR(2000) NULL,
  `conclusion_createdby` VARCHAR(250) NULL,
  `conclusion_createddate` DATETIME NULL,
  `mse_id` VARCHAR(10) NULL,
  PRIMARY KEY (`conclusion_id`),
    FOREIGN KEY (`mse_id`)
    REFERENCES `mse`.`mse` (`mse_id`)
   )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maintainance`.`add_information`
-- -----------------------------------------------------
CREATE TABLE `add_information` (
  `addinfo_id` VARCHAR(10) NOT NULL,
  `addinfo_description` VARCHAR(2000) NULL,
  `addinfo_title` VARCHAR(2000) NULL,
  `addinfo_createdby` VARCHAR(250) NULL,
  `addinfo_createddate` DATETIME NULL,
  `add_information_location` VARCHAR(250) NULL,
  PRIMARY KEY (`addinfo_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mse`.`add_info`
-- -----------------------------------------------------
CREATE TABLE `add_info` (
  `add_info_id` VARCHAR(10) NOT NULL,
  `addinfo_id` VARCHAR(10) NULL,
  `mse_id` VARCHAR(10) NULL,
  PRIMARY KEY (`add_info_id`),
    FOREIGN KEY (`addinfo_id`)
    REFERENCES `maintainance`.`add_information` (`addinfo_id`)
    FOREIGN KEY (`mse_id`)
    REFERENCES `mse`.`mse` (`mse_id`)
ENGINE = InnoDB;