-- MySQL Script generated by MySQL Workbench
-- Fri Sep 16 18:26:34 2022
-- Model: New Model    Version: 1.0
-- 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 medical_cemter_db
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `medical_cemter_db` ;

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

-- -----------------------------------------------------
-- Table `medical_cemter_db`.`patients`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `medical_cemter_db`.`patients` ;

CREATE TABLE IF NOT EXISTS `medical_cemter_db`.`patients` (
  `id` VARCHAR(255) NOT NULL,
  `identification` VARCHAR(255) NULL DEFAULT NULL,
  `firstname` VARCHAR(45) NOT NULL,
  `lastname` VARCHAR(45) NOT NULL,
  `age` VARCHAR(45) NOT NULL,
  `gender` ENUM('F', 'M') NOT NULL,
  `email` VARCHAR(45) NULL DEFAULT NULL,
  `phone` VARCHAR(45) NULL DEFAULT NULL,
  `birthday` DATETIME NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `medical_cemter_db`.`appointments`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `medical_cemter_db`.`appointments` ;

CREATE TABLE IF NOT EXISTS `medical_cemter_db`.`appointments` (
  `id` VARCHAR(255) NOT NULL,
  `patients_id` VARCHAR(255) NOT NULL,
  `code` VARCHAR(255) NOT NULL,
  `date` TIMESTAMP NOT NULL,
  `active` BIGINT NULL DEFAULT NULL,
  `especiality` ENUM('NED', 'PED') NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `code_UNIQUE` (`code` ASC) VISIBLE,
  INDEX `fk_appointments_patients_idx` (`patients_id` ASC) VISIBLE,
  CONSTRAINT `fk_appointments_patients`
    FOREIGN KEY (`patients_id`)
    REFERENCES `medical_cemter_db`.`patients` (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `medical_cemter_db`.`history`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `medical_cemter_db`.`history` ;

CREATE TABLE IF NOT EXISTS `medical_cemter_db`.`history` (
  `id` VARCHAR(255) NOT NULL,
  `patients_id` VARCHAR(255) NOT NULL,
  `title` VARCHAR(255) NULL DEFAULT NULL,
  `summary` TEXT NOT NULL,
  `especiality` ENUM('NED', 'PED') NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_history_patients1_idx` (`patients_id` ASC) VISIBLE,
  CONSTRAINT `fk_history_patients1`
    FOREIGN KEY (`patients_id`)
    REFERENCES `medical_cemter_db`.`patients` (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `medical_cemter_db`.`physical_exam`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `medical_cemter_db`.`physical_exam` ;

CREATE TABLE IF NOT EXISTS `medical_cemter_db`.`physical_exam` (
  `id` VARCHAR(255) NOT NULL,
  `history_id` VARCHAR(255) NOT NULL,
  `value` TEXT NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_physical_exam_history1_idx` (`history_id` ASC) VISIBLE,
  CONSTRAINT `fk_physical_exam_history1`
    FOREIGN KEY (`history_id`)
    REFERENCES `medical_cemter_db`.`history` (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `medical_cemter_db`.`representative`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `medical_cemter_db`.`representative` ;

CREATE TABLE IF NOT EXISTS `medical_cemter_db`.`representative` (
  `id` VARCHAR(255) NOT NULL,
  `identification` VARCHAR(255) NOT NULL,
  `firstname` VARCHAR(45) NOT NULL,
  `lastname` VARCHAR(45) NOT NULL,
  `age` VARCHAR(45) NOT NULL,
  `gender` ENUM('F', 'M') NOT NULL,
  `email` VARCHAR(45) NULL DEFAULT NULL,
  `phone` VARCHAR(45) NOT NULL,
  `address` TEXT NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `identification_UNIQUE` (`identification` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `medical_cemter_db`.`relationship`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `medical_cemter_db`.`relationship` ;

CREATE TABLE IF NOT EXISTS `medical_cemter_db`.`relationship` (
  `representative_id` VARCHAR(255) NOT NULL,
  `patients_id` VARCHAR(255) NOT NULL,
  `title` VARCHAR(45) NOT NULL,
  `description` VARCHAR(45) NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`representative_id`, `patients_id`),
  INDEX `fk_representative_has_patients_patients1_idx` (`patients_id` ASC) VISIBLE,
  INDEX `fk_representative_has_patients_representative1_idx` (`representative_id` ASC) VISIBLE,
  CONSTRAINT `fk_representative_has_patients_patients1`
    FOREIGN KEY (`patients_id`)
    REFERENCES `medical_cemter_db`.`patients` (`id`),
  CONSTRAINT `fk_representative_has_patients_representative1`
    FOREIGN KEY (`representative_id`)
    REFERENCES `medical_cemter_db`.`representative` (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `medical_cemter_db`.`section_type`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `medical_cemter_db`.`section_type` ;

CREATE TABLE IF NOT EXISTS `medical_cemter_db`.`section_type` (
  `id` VARCHAR(255) NOT NULL,
  `section_type_id` VARCHAR(255) NULL DEFAULT NULL,
  `key` VARCHAR(45) NOT NULL,
  `value` VARCHAR(45) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `position` VARCHAR(45) NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_section_type_section_type1_idx` (`section_type_id` ASC) VISIBLE,
  CONSTRAINT `fk_section_type_section_type1`
    FOREIGN KEY (`section_type_id`)
    REFERENCES `medical_cemter_db`.`section_type` (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `medical_cemter_db`.`section_history`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `medical_cemter_db`.`section_history` ;

CREATE TABLE IF NOT EXISTS `medical_cemter_db`.`section_history` (
  `id` VARCHAR(255) NOT NULL,
  `history_id` VARCHAR(255) NOT NULL,
  `section_type_id` VARCHAR(255) NOT NULL,
  `title` VARCHAR(45) NOT NULL,
  `summary` TEXT NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_section_history_history1_idx` (`history_id` ASC) VISIBLE,
  INDEX `fk_section_history_section_type1_idx` (`section_type_id` ASC) VISIBLE,
  CONSTRAINT `fk_section_history_history1`
    FOREIGN KEY (`history_id`)
    REFERENCES `medical_cemter_db`.`history` (`id`),
  CONSTRAINT `fk_section_history_section_type1`
    FOREIGN KEY (`section_type_id`)
    REFERENCES `medical_cemter_db`.`section_type` (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `medical_cemter_db`.`users`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `medical_cemter_db`.`users` ;

CREATE TABLE IF NOT EXISTS `medical_cemter_db`.`users` (
  `id` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
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;
