-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema DW_Incontinence
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema DW_Incontinence
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `DW_Incontinence` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
USE `DW_Incontinence` ;

-- -----------------------------------------------------
-- Table `DW_Incontinence`.`tp_additional_surgery`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`tp_additional_surgery` (
  `additional_surgery_type_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `description` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`additional_surgery_type_id`),
  UNIQUE INDEX `additional_surgery_type_id_UNIQUE` (`additional_surgery_type_id` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`tp_concomitant_surgery`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`tp_concomitant_surgery` (
  `concomitant_surgery_type_id` INT NOT NULL AUTO_INCREMENT,
  `description` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`concomitant_surgery_type_id`),
  UNIQUE INDEX `concomitant_surgery_id_UNIQUE` (`concomitant_surgery_type_id` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`concomitant_surgery`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`concomitant_surgery` (
  `concomitant_surgery_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `concomitant_surgery_date` DATETIME NULL DEFAULT NULL,
  `concomitant_surgery_type_id` INT NULL DEFAULT NULL,
  PRIMARY KEY (`concomitant_surgery_id`),
  UNIQUE INDEX `concomitant_surgery_id_UNIQUE` (`concomitant_surgery_id` ASC) VISIBLE,
  INDEX `concomitant_surgery_type_idx` (`concomitant_surgery_type_id` ASC) VISIBLE,
  CONSTRAINT `concomitant_surgery_type`
    FOREIGN KEY (`concomitant_surgery_type_id`)
    REFERENCES `DW_Incontinence`.`tp_concomitant_surgery` (`concomitant_surgery_type_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`tp_incontinence`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`tp_incontinence` (
  `incontinence_type_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `description` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`incontinence_type_id`),
  UNIQUE INDEX `incontinence_type_id_UNIQUE` (`incontinence_type_id` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`tp_pop_grade`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`tp_pop_grade` (
  `pop_grade_id` INT NOT NULL AUTO_INCREMENT,
  `description` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`pop_grade_id`),
  UNIQUE INDEX `pop_grade_id_UNIQUE` (`pop_grade_id` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`tp_SUI_grade`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`tp_SUI_grade` (
  `SUI_grade_id` INT NOT NULL AUTO_INCREMENT,
  `description` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`SUI_grade_id`),
  UNIQUE INDEX `SUI_grade_id_UNIQUE` (`SUI_grade_id` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`patient`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`patient` (
  `patient_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL DEFAULT NULL,
  `surname` VARCHAR(45) NULL DEFAULT NULL,
  `birthday` DATETIME NOT NULL,
  `tax_code` VARCHAR(20) NULL DEFAULT NULL,
  `birthplace` VARCHAR(45) NULL DEFAULT NULL,
  `weight` DECIMAL(10,2) UNSIGNED NOT NULL,
  `height` DECIMAL(10,2) NULL DEFAULT NULL,
  `bmi` INT UNSIGNED NOT NULL,
  `registration_data` DATETIME NULL DEFAULT NULL,
  `fl_pop` BINARY(1) NOT NULL,
  `fl_incontinence` BINARY(1) NOT NULL,
  `pop_grade_id` INT NULL DEFAULT NULL,
  `sui_grade_id` INT NULL DEFAULT NULL,
  `incontinence_type_id` INT UNSIGNED NULL,
  PRIMARY KEY (`patient_id`),
  UNIQUE INDEX `pazienti_id_UNIQUE` (`patient_id` ASC) VISIBLE,
  INDEX `pop_grade_idx` (`pop_grade_id` ASC) VISIBLE,
  INDEX `sui_grade_idx` (`sui_grade_id` ASC) VISIBLE,
  INDEX `fk_patient_tp_incontinence1_idx` (`incontinence_type_id` ASC) VISIBLE,
  CONSTRAINT `fk_patient_tp_incontinence1`
    FOREIGN KEY (`incontinence_type_id`)
    REFERENCES `DW_Incontinence`.`tp_incontinence` (`incontinence_type_id`),
  CONSTRAINT `pop_grade`
    FOREIGN KEY (`pop_grade_id`)
    REFERENCES `DW_Incontinence`.`tp_pop_grade` (`pop_grade_id`),
  CONSTRAINT `sui_grade`
    FOREIGN KEY (`sui_grade_id`)
    REFERENCES `DW_Incontinence`.`tp_SUI_grade` (`SUI_grade_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`intra_surgery_complication`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`intra_surgery_complication` (
  `intra_surgery_complication_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `fl_bowl_injury` BINARY(1) NULL DEFAULT NULL,
  `fl_hematuria` BINARY(1) NULL DEFAULT NULL,
  `fl_bladder_injury` BINARY(1) NULL DEFAULT NULL,
  `fl_hematoma` BINARY(1) NULL DEFAULT NULL,
  `fl_urethral_perforations` BINARY(1) NULL DEFAULT NULL,
  `fl_vaginal_perforations` BINARY(1) NULL DEFAULT NULL,
  `fl_rectal_perforation` BINARY(1) NULL DEFAULT NULL,
  `estimated_blod_loss` INT NULL DEFAULT NULL,
  `other_complication` VARCHAR(2000) NULL DEFAULT NULL,
  PRIMARY KEY (`intra_surgery_complication_id`),
  UNIQUE INDEX `intra_surgery_complication_id_UNIQUE` (`intra_surgery_complication_id` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`tp_surgery`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`tp_surgery` (
  `surgery_type_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `description` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`surgery_type_id`),
  UNIQUE INDEX `surgery_type_id_UNIQUE` (`surgery_type_id` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`surgery_diary`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`surgery_diary` (
  `surgery_diary_id` INT NOT NULL,
  `patient_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `intra_surgery_complication_id` INT UNSIGNED NULL DEFAULT NULL,
  `concomitant_surgery_id` INT UNSIGNED NULL DEFAULT NULL,
  `surgery_type_id` INT UNSIGNED NOT NULL,
  `surgery_diary_data` DATETIME NULL DEFAULT NULL,
  `surgery_diary_duration` INT NULL DEFAULT NULL,
  `surgery_diary_anesthesia` VARCHAR(100) NULL DEFAULT NULL,
  `device_type` VARCHAR(100) NULL DEFAULT NULL,
  `discharge_data` DATETIME NULL DEFAULT NULL,
  `note` VARCHAR(2000) NULL DEFAULT NULL,
  PRIMARY KEY (`surgery_diary_id`),
  UNIQUE INDEX `patient_id_UNIQUE` (`patient_id` ASC) VISIBLE,
  UNIQUE INDEX `surgery_diary_id_UNIQUE` (`surgery_diary_id` ASC) VISIBLE,
  UNIQUE INDEX `intra_surgery_complication_id_UNIQUE` (`intra_surgery_complication_id` ASC) VISIBLE,
  UNIQUE INDEX `concomitant_surgery_id_UNIQUE` (`concomitant_surgery_id` ASC) VISIBLE,
  INDEX `patient_surgery_idx` (`patient_id` ASC) VISIBLE,
  INDEX `surgery_complication_idx` (`intra_surgery_complication_id` ASC) VISIBLE,
  INDEX `fk_surgery_diary_tp_surgery1_idx` (`surgery_type_id` ASC) VISIBLE,
  CONSTRAINT `concomitant_surgery`
    FOREIGN KEY (`concomitant_surgery_id`)
    REFERENCES `DW_Incontinence`.`concomitant_surgery` (`concomitant_surgery_id`),
  CONSTRAINT `patient_surgery`
    FOREIGN KEY (`patient_id`)
    REFERENCES `DW_Incontinence`.`patient` (`patient_id`),
  CONSTRAINT `surgery_complication`
    FOREIGN KEY (`intra_surgery_complication_id`)
    REFERENCES `DW_Incontinence`.`intra_surgery_complication` (`intra_surgery_complication_id`),
  CONSTRAINT `fk_surgery_diary_tp_surgery1`
    FOREIGN KEY (`surgery_type_id`)
    REFERENCES `DW_Incontinence`.`tp_surgery` (`surgery_type_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`additional_surgery_diary`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`additional_surgery_diary` (
  `additional_surgery_diary_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `additional_surgery_type_id` INT UNSIGNED NOT NULL,
  `surgery_diary_id` INT NOT NULL,
  `additional_surgery_diary_data` DATETIME NULL DEFAULT NULL,
  `norte` VARCHAR(2000) NULL DEFAULT NULL,
  PRIMARY KEY (`additional_surgery_diary_id`),
  UNIQUE INDEX `additional_surgery_type_id_UNIQUE` (`additional_surgery_type_id` ASC) VISIBLE,
  UNIQUE INDEX `additional_surgery_diary_id_UNIQUE` (`additional_surgery_diary_id` ASC) VISIBLE,
  INDEX `additional:surgery_type_idx` (`additional_surgery_type_id` ASC) VISIBLE,
  INDEX `fk_additional_surgery_diary_surgery_diary1_idx` (`surgery_diary_id` ASC) VISIBLE,
  CONSTRAINT `additional:surgery_type`
    FOREIGN KEY (`additional_surgery_type_id`)
    REFERENCES `DW_Incontinence`.`tp_additional_surgery` (`additional_surgery_type_id`),
  CONSTRAINT `fk_additional_surgery_diary_surgery_diary1`
    FOREIGN KEY (`surgery_diary_id`)
    REFERENCES `DW_Incontinence`.`surgery_diary` (`surgery_diary_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`tp_neurological_diseases`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`tp_neurological_diseases` (
  `neurological_diseases_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `description` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`neurological_diseases_id`),
  UNIQUE INDEX `neurological_diseases_id_UNIQUE` (`neurological_diseases_id` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`tp_bowel_function`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`tp_bowel_function` (
  `bowel_function_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `description` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`bowel_function_id`),
  UNIQUE INDEX `bowel_function_id_UNIQUE` (`bowel_function_id` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`comorbidities`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`comorbidities` (
  `comorbidities_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` INT UNSIGNED NOT NULL,
  `fl_smoke` BINARY(1) NULL DEFAULT NULL,
  `fl_lung_disease` BINARY(1) NULL DEFAULT NULL,
  `fl_ashma` BINARY(1) NULL DEFAULT NULL,
  `fl_estrogen_supplementation` BINARY(1) NULL DEFAULT NULL,
  `fl_HRT` BINARY(1) NULL DEFAULT NULL,
  `fl_further_children` BINARY(1) NULL DEFAULT NULL,
  `menopause_date` DATETIME NULL DEFAULT NULL,
  `note` VARCHAR(2000) NULL DEFAULT NULL,
  `neurological_diseases_id` INT UNSIGNED NULL DEFAULT NULL,
  `bowel_function_id` INT UNSIGNED NULL,
  PRIMARY KEY (`comorbidities_id`),
  UNIQUE INDEX `patient_id_UNIQUE` (`patient_id` ASC) VISIBLE,
  UNIQUE INDEX `medical_history_id_UNIQUE` (`comorbidities_id` ASC) VISIBLE,
  INDEX `patient_medical_history_idx` (`patient_id` ASC) VISIBLE,
  INDEX `fk_comorbidities_tp_neurological diseases1_idx` (`neurological_diseases_id` ASC) VISIBLE,
  INDEX `fk_comorbidities_tp_bowel_function1_idx` (`bowel_function_id` ASC) VISIBLE,
  CONSTRAINT `fk_comorbidities_tp_neurological diseases1`
    FOREIGN KEY (`neurological_diseases_id`)
    REFERENCES `DW_Incontinence`.`tp_neurological_diseases` (`neurological_diseases_id`),
  CONSTRAINT `patient_medical_history`
    FOREIGN KEY (`patient_id`)
    REFERENCES `DW_Incontinence`.`patient` (`patient_id`),
  CONSTRAINT `fk_comorbidities_tp_bowel_function1`
    FOREIGN KEY (`bowel_function_id`)
    REFERENCES `DW_Incontinence`.`tp_bowel_function` (`bowel_function_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`tp_delivery`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`tp_delivery` (
  `delivery_type_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `description` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`delivery_type_id`),
  UNIQUE INDEX `delivery_type_id_UNIQUE` (`delivery_type_id` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`delivery_diary`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`delivery_diary` (
  `delivery_diary_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` INT UNSIGNED NOT NULL,
  `delivery_date` DATETIME NULL DEFAULT NULL,
  `newborn_weight` INT NULL DEFAULT NULL,
  `note` VARCHAR(2000) NULL DEFAULT NULL,
  `delivery_type_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`delivery_diary_id`),
  UNIQUE INDEX `delivery_diary_id_UNIQUE` (`delivery_diary_id` ASC) VISIBLE,
  UNIQUE INDEX `patient_id_UNIQUE` (`patient_id` ASC) VISIBLE,
  INDEX `patient_id_idx` (`patient_id` ASC) VISIBLE,
  INDEX `fk_delivery_diary_tp_delivery1_idx` (`delivery_type_id` ASC) VISIBLE,
  CONSTRAINT `fk_delivery_diary_tp_delivery1`
    FOREIGN KEY (`delivery_type_id`)
    REFERENCES `DW_Incontinence`.`tp_delivery` (`delivery_type_id`),
  CONSTRAINT `patient_delivery`
    FOREIGN KEY (`patient_id`)
    REFERENCES `DW_Incontinence`.`patient` (`patient_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`tp_immediate_complication`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`tp_immediate_complication` (
  `immediate_complication_type_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `description` VARCHAR(45) NULL DEFAULT NULL,
  `fl_dangerous` BINARY(1) NULL DEFAULT NULL,
  PRIMARY KEY (`immediate_complication_type_id`),
  UNIQUE INDEX `immediate_complication_type_id_UNIQUE` (`immediate_complication_type_id` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`immediate_post_surgery_complication`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`immediate_post_surgery_complication` (
  `immediate_complication_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `surgery_diary_id` INT NOT NULL,
  `complication_data` DATETIME NULL DEFAULT NULL,
  `fl_pre_post_discharge` BINARY(1) NULL DEFAULT NULL,
  `note` VARCHAR(2000) NULL DEFAULT NULL,
  `immediate_complication_type_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`immediate_complication_id`),
  UNIQUE INDEX `immediate_complication_id_UNIQUE` (`immediate_complication_id` ASC) VISIBLE,
  INDEX `fk_immediate_post_surgery_complication_surgery_diary1_idx` (`surgery_diary_id` ASC) VISIBLE,
  INDEX `fk_immediate_post_surgery_complication_tp_immediate_complic_idx` (`immediate_complication_type_id` ASC) VISIBLE,
  CONSTRAINT `fk_immediate_post_surgery_complication_surgery_diary1`
    FOREIGN KEY (`surgery_diary_id`)
    REFERENCES `DW_Incontinence`.`surgery_diary` (`surgery_diary_id`),
  CONSTRAINT `fk_immediate_post_surgery_complication_tp_immediate_complicat1`
    FOREIGN KEY (`immediate_complication_type_id`)
    REFERENCES `DW_Incontinence`.`tp_immediate_complication` (`immediate_complication_type_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`urodynamic_variables`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`urodynamic_variables` (
  `urodynamic_variables_id` INT NOT NULL AUTO_INCREMENT,
  `Valsalva_leak point_pressure` INT NULL DEFAULT NULL,
  `maximum_flow_rate` INT NULL DEFAULT NULL,
  `detrusor_pressure_at_maximal_flow` INT NULL DEFAULT NULL,
  `bladder_compliance` INT NULL DEFAULT NULL,
  `maximum_cystometric_capacity` INT NULL DEFAULT NULL,
  `maximum_urethral_closing_pressur` INT NULL DEFAULT NULL,
  `detrusor_hypocontractility` BINARY(1) NULL DEFAULT NULL,
  `detrusor_overactivity` BINARY(1) NULL DEFAULT NULL,
  `detrusor_instability_score` INT NULL DEFAULT NULL,
  PRIMARY KEY (`urodynamic_variables_id`),
  UNIQUE INDEX `visit_measure_id_UNIQUE` (`urodynamic_variables_id` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`visit_measure`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`visit_measure` (
  `visit_measure_id` INT NOT NULL AUTO_INCREMENT,
  `mesure_date` DATETIME NULL DEFAULT NULL,
  `cough_test` INT NULL DEFAULT NULL,
  `pad_test` INT NULL DEFAULT NULL,
  `postvoid residual urine` INT NULL DEFAULT NULL,
  `fl_stress/urge_UI_symptoms` BINARY(1) NULL DEFAULT NULL,
  `pop_grade_id` INT NULL DEFAULT NULL,
  `sui_grade_id` INT NULL DEFAULT NULL,
  `urodynamic_variables_id` INT NULL DEFAULT NULL,
  PRIMARY KEY (`visit_measure_id`),
  UNIQUE INDEX `visit_measure_id_UNIQUE` (`visit_measure_id` ASC) VISIBLE,
  INDEX `pop_grade_id_idx` (`pop_grade_id` ASC) VISIBLE,
  INDEX `sui_grade_id_idx` (`sui_grade_id` ASC) VISIBLE,
  INDEX `urodynamic_variables_idx` (`urodynamic_variables_id` ASC) VISIBLE,
  CONSTRAINT `pop_grade_id`
    FOREIGN KEY (`pop_grade_id`)
    REFERENCES `DW_Incontinence`.`tp_pop_grade` (`pop_grade_id`),
  CONSTRAINT `sui_grade_id`
    FOREIGN KEY (`sui_grade_id`)
    REFERENCES `DW_Incontinence`.`tp_SUI_grade` (`SUI_grade_id`),
  CONSTRAINT `urodynamic_variables`
    FOREIGN KEY (`urodynamic_variables_id`)
    REFERENCES `DW_Incontinence`.`urodynamic_variables` (`urodynamic_variables_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`pdf_document`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`pdf_document` (
  `pdf_document_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `visit_measure_id` INT NOT NULL,
  `pdf_document_file` VARCHAR(45) NULL DEFAULT NULL,
  `description` VARCHAR(2000) NULL DEFAULT NULL,
  PRIMARY KEY (`pdf_document_id`),
  UNIQUE INDEX `pdf_document_id_UNIQUE` (`pdf_document_id` ASC) VISIBLE,
  INDEX `fk_pdf_document_visit_measure1_idx` (`visit_measure_id` ASC) VISIBLE,
  CONSTRAINT `fk_pdf_document_visit_measure1`
    FOREIGN KEY (`visit_measure_id`)
    REFERENCES `DW_Incontinence`.`visit_measure` (`visit_measure_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`tp_visit`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`tp_visit` (
  `visit_type_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `description` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`visit_type_id`),
  UNIQUE INDEX `visit_type_id_UNIQUE` (`visit_type_id` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`visit_diary`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`visit_diary` (
  `visit_diary_id` INT UNSIGNED NOT NULL,
  `visit_type_id` INT UNSIGNED NOT NULL,
  `patient_id` INT UNSIGNED NOT NULL,
  `visit_measure_id` INT NULL DEFAULT NULL,
  `visit_data` DATETIME NULL DEFAULT NULL,
  `note` VARCHAR(2000) NULL DEFAULT NULL,
  PRIMARY KEY (`visit_diary_id`),
  UNIQUE INDEX `delivery_diary_id_UNIQUE` (`visit_diary_id` ASC) VISIBLE,
  UNIQUE INDEX `visit_measure_id_UNIQUE` (`visit_measure_id` ASC) VISIBLE,
  INDEX `patient_delivery_idx` (`visit_measure_id` ASC) VISIBLE,
  INDEX `fk_visit_diary_tp_visit1_idx` (`visit_type_id` ASC) VISIBLE,
  INDEX `fk_visit_diary_patient1_idx` (`patient_id` ASC) VISIBLE,
  CONSTRAINT `fk_visit_diary_patient1`
    FOREIGN KEY (`patient_id`)
    REFERENCES `DW_Incontinence`.`patient` (`patient_id`),
  CONSTRAINT `fk_visit_diary_tp_visit1`
    FOREIGN KEY (`visit_type_id`)
    REFERENCES `DW_Incontinence`.`tp_visit` (`visit_type_id`),
  CONSTRAINT `measure`
    FOREIGN KEY (`visit_measure_id`)
    REFERENCES `DW_Incontinence`.`visit_measure` (`visit_measure_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`tp_post_surgery_complication`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`tp_post_surgery_complication` (
  `complication_type_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `description` VARCHAR(45) NULL DEFAULT NULL,
  `fl_dangerous` BINARY(1) NULL DEFAULT NULL,
  PRIMARY KEY (`complication_type_id`),
  UNIQUE INDEX `complication_type_id_UNIQUE` (`complication_type_id` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`post_surgery_complication`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`post_surgery_complication` (
  `surgery_complication_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `visit_diary_id` INT UNSIGNED NOT NULL,
  `complication_type_id` INT UNSIGNED NOT NULL,
  `additional_surgery_diary_id` INT UNSIGNED NULL,
  `complication_date` DATETIME NULL DEFAULT NULL,
  `note` VARCHAR(2000) NULL DEFAULT NULL,
  PRIMARY KEY (`surgery_complication_id`),
  UNIQUE INDEX `surgery_complication_id_UNIQUE` (`surgery_complication_id` ASC) VISIBLE,
  UNIQUE INDEX `additional_surgery_diary_id_UNIQUE` (`additional_surgery_diary_id` ASC) VISIBLE,
  INDEX `additional_surgery_idx` (`additional_surgery_diary_id` ASC) VISIBLE,
  INDEX `fk_post_surgery_complication_visit_diary1_idx` (`visit_diary_id` ASC) VISIBLE,
  INDEX `fk_post_surgery_complication_tp_post_surgery_complication1_idx` (`complication_type_id` ASC) VISIBLE,
  CONSTRAINT `additional_surgery`
    FOREIGN KEY (`additional_surgery_diary_id`)
    REFERENCES `DW_Incontinence`.`additional_surgery_diary` (`additional_surgery_diary_id`),
  CONSTRAINT `fk_post_surgery_complication_visit_diary1`
    FOREIGN KEY (`visit_diary_id`)
    REFERENCES `DW_Incontinence`.`visit_diary` (`visit_diary_id`),
  CONSTRAINT `fk_post_surgery_complication_tp_post_surgery_complication1`
    FOREIGN KEY (`complication_type_id`)
    REFERENCES `DW_Incontinence`.`tp_post_surgery_complication` (`complication_type_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`tp_post_surgery_pain`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`tp_post_surgery_pain` (
  `pain_type_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `description` VARCHAR(45) NULL DEFAULT NULL,
  `fl_dangerous` BINARY(1) NULL DEFAULT NULL,
  PRIMARY KEY (`pain_type_id`),
  UNIQUE INDEX `pain_type_id_UNIQUE` (`pain_type_id` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`post_surgery_pain`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`post_surgery_pain` (
  `post_surgery_pain_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `visit_diary_id` INT UNSIGNED NOT NULL,
  `pain_type_id` INT UNSIGNED NOT NULL,
  `pain_date` DATETIME NOT NULL,
  PRIMARY KEY (`post_surgery_pain_id`),
  UNIQUE INDEX `post_surgery_pain_id_UNIQUE` (`post_surgery_pain_id` ASC) VISIBLE,
  INDEX `fk_post_surgery_pain_visit_diary1_idx` (`visit_diary_id` ASC) VISIBLE,
  INDEX `fk_post_surgery_pain_tp_post_surgery_pain1_idx` (`pain_type_id` ASC) VISIBLE,
  CONSTRAINT `fk_post_surgery_pain_tp_post_surgery_pain1`
    FOREIGN KEY (`pain_type_id`)
    REFERENCES `DW_Incontinence`.`tp_post_surgery_pain` (`pain_type_id`),
  CONSTRAINT `fk_post_surgery_pain_visit_diary1`
    FOREIGN KEY (`visit_diary_id`)
    REFERENCES `DW_Incontinence`.`visit_diary` (`visit_diary_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`tp_previous_surgery`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`tp_previous_surgery` (
  `previous_surgery_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `description` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`previous_surgery_id`),
  UNIQUE INDEX `previous_surgery_type_id_UNIQUE` (`previous_surgery_id` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`previous_surgery_diary`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`previous_surgery_diary` (
  `previous_surgery_diary_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` INT UNSIGNED NOT NULL,
  `previous_surgery_date` DATETIME NULL DEFAULT NULL,
  `note` VARCHAR(2000) NULL DEFAULT NULL,
  `previous_surgery_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`previous_surgery_diary_id`),
  UNIQUE INDEX `operazioni_id_UNIQUE` (`previous_surgery_diary_id` ASC) VISIBLE,
  UNIQUE INDEX `patient_id_UNIQUE` (`patient_id` ASC) VISIBLE,
  INDEX `fk_pazienti_idx` (`patient_id` ASC) VISIBLE,
  INDEX `fk_previous_surgery_diary_tp_previous_surgery1_idx` (`previous_surgery_id` ASC) VISIBLE,
  CONSTRAINT `fk_previous_surgery_diary_tp_previous_surgery1`
    FOREIGN KEY (`previous_surgery_id`)
    REFERENCES `DW_Incontinence`.`tp_previous_surgery` (`previous_surgery_id`),
  CONSTRAINT `patient_previous_surgery`
    FOREIGN KEY (`patient_id`)
    REFERENCES `DW_Incontinence`.`patient` (`patient_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`tp_questionary`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`tp_questionary` (
  `questionary_type_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `description` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`questionary_type_id`),
  UNIQUE INDEX `questionary_type_id_UNIQUE` (`questionary_type_id` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `DW_Incontinence`.`questionary_diary`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DW_Incontinence`.`questionary_diary` (
  `questionary_diary_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `visit_diary_id` INT UNSIGNED NOT NULL,
  `questionary_type_id` INT UNSIGNED NOT NULL,
  `questionary_data` DATETIME NULL DEFAULT NULL,
  `questionary_result` INT NULL DEFAULT NULL,
  `note` VARCHAR(2000) NULL DEFAULT NULL,
  PRIMARY KEY (`questionary_diary_id`),
  UNIQUE INDEX `delivery_diary_id_UNIQUE` (`questionary_diary_id` ASC) VISIBLE,
  INDEX `fk_questionary_diary_visit_diary1_idx` (`visit_diary_id` ASC) VISIBLE,
  INDEX `fk_questionary_diary_tp_questionary1_idx` (`questionary_type_id` ASC) VISIBLE,
  CONSTRAINT `fk_questionary_diary_tp_questionary1`
    FOREIGN KEY (`questionary_type_id`)
    REFERENCES `DW_Incontinence`.`tp_questionary` (`questionary_type_id`),
  CONSTRAINT `fk_questionary_diary_visit_diary1`
    FOREIGN KEY (`visit_diary_id`)
    REFERENCES `DW_Incontinence`.`visit_diary` (`visit_diary_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
