medica_client_structure.sql

/*
SQLyog Community v13.1.6 (64 bit)
MySQL - 8.0.21 : Database - medica_client
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE = ''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS = @@UNIQUE_CHECKS, UNIQUE_CHECKS = 0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS = @@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0 */;
/*!40101 SET @OLD_SQL_MODE = @@SQL_MODE, SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES = @@SQL_NOTES, SQL_NOTES = 0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS */`medica_client` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `medica_client`;

/*Table structure for table `appointments` */

DROP TABLE IF EXISTS `appointments`;

CREATE TABLE `appointments`
(
    `id`        int         NOT NULL AUTO_INCREMENT,
    `PatientId` int         NOT NULL,
    `doctorId`  int         NOT NULL,
    `type`      varchar(30) NOT NULL DEFAULT 'sick',
    `time`      timestamp   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 5
  DEFAULT CHARSET = latin1;

/*Table structure for table `conditions` */

DROP TABLE IF EXISTS `conditions`;

CREATE TABLE `conditions`
(
    `id`             int  NOT NULL AUTO_INCREMENT,
    `patientemail`   text,
    `conditionName`  text NOT NULL,
    `recommendation` text,
    `date`           date DEFAULT NULL,
    `category`       text NOT NULL,
    `description`    text NOT NULL,
    `doctorMail`     text NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 4
  DEFAULT CHARSET = latin1;

/*Table structure for table `hospital` */

DROP TABLE IF EXISTS `hospital`;

CREATE TABLE `hospital`
(
    `id`       int  NOT NULL AUTO_INCREMENT,
    `name`     text NOT NULL,
    `location` text NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = latin1;

/*Table structure for table `labtests` */

DROP TABLE IF EXISTS `labtests`;

CREATE TABLE `labtests`
(
    `id`          int         NOT NULL AUTO_INCREMENT,
    `tests`       text,
    `technician`  text,
    `patientname` text,
    `doctorname`  text,
    `results`     text,
    `status`      varchar(30) NOT NULL DEFAULT 'PENDING',
    `imageResult` blob,
    `done`        tinyint(1)           DEFAULT '0',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 11
  DEFAULT CHARSET = latin1;

/*Table structure for table `patients` */

DROP TABLE IF EXISTS `patients`;

CREATE TABLE `patients`
(
    `id`             int       NOT NULL AUTO_INCREMENT,
    `name`           text      NOT NULL,
    `email`          text      NOT NULL,
    `phonenumber`    text      NOT NULL,
    `birthdate`      date      NOT NULL,
    `identifier`     text,
    `sex`            text      NOT NULL,
    `branch`         text      NOT NULL,
    `dateregistered` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `identifier number` (`identifier`(11))
) ENGINE = InnoDB
  AUTO_INCREMENT = 6
  DEFAULT CHARSET = latin1;

/*Table structure for table `prescriptions` */

DROP TABLE IF EXISTS `prescriptions`;

CREATE TABLE `prescriptions`
(
    `id`             int       NOT NULL AUTO_INCREMENT,
    `patientemail`   text,
    `dateprescribed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `diagnosis`      text,
    `prescription`   text,
    `recommendation` text,
    `doctor`         text,
    `tests`          text,
    `ratings`        int                DEFAULT NULL,
    `completed`      tinyint(1)         DEFAULT '0',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 10
  DEFAULT CHARSET = latin1;

/*Table structure for table `push` */

DROP TABLE IF EXISTS `push`;

CREATE TABLE `push`
(
    `id`   int NOT NULL AUTO_INCREMENT,
    `time` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci;

/*Table structure for table `specialdisease` */

DROP TABLE IF EXISTS `specialdisease`;

CREATE TABLE `specialdisease`
(
    `id`             int  NOT NULL AUTO_INCREMENT,
    `type`           text NOT NULL,
    `prescription`   text NOT NULL,
    `recommendation` text NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = latin1;

/*Table structure for table `users` */

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users`
(
    `id`                    int  NOT NULL AUTO_INCREMENT,
    `name`                  text NOT NULL,
    `email`                 text NOT NULL,
    `password`              text NOT NULL,
    `hospital`              text NOT NULL,
    `status`                text NOT NULL,
    `userclearancelevel`    text NOT NULL,
    `certification`         longblob,
    `identification`        text,
    `description`           text NOT NULL,
    `numberoofappointments` int  NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 13
  DEFAULT CHARSET = latin1;

/*!40101 SET SQL_MODE = @OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS = @OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES = @OLD_SQL_NOTES */;