hsp-gdh/database.sql

SET default_storage_engine = InnoDB;
DROP DATABASE IF EXISTS `hsp_gdh`;
CREATE DATABASE IF NOT EXISTS `hsp_gdh`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

DROP USER IF EXISTS 'hsp_gdh';
CREATE USER 'hsp_gdh'@'%' IDENTIFIED BY 'PASSWORD';
GRANT ALL PRIVILEGES ON hsp_gdh.* TO 'hsp_gdh'@'%';

USE `hsp_gdh`;

CREATE TABLE roles (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  user_bitfield INT UNSIGNED NOT NULL,
  role_bitfield INT UNSIGNED NOT NULL,
  verification_code_bitfield INT UNSIGNED NOT NULL,
  ban_bitfield INT UNSIGNED NOT NULL,
  patient_bitfield INT UNSIGNED NOT NULL,
  doctor_bitfield INT UNSIGNED NOT NULL,
  service_bitfield INT UNSIGNED NOT NULL,
  company_bitfield INT UNSIGNED NOT NULL,
  hospital_bitfield INT UNSIGNED NOT NULL,
  room_bitfield INT UNSIGNED NOT NULL,
  appointment_bitfield INT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX r_name_idx (name)
) ENGINE=InnoDB;

INSERT INTO roles (name, user_bitfield, role_bitfield, verification_code_bitfield, ban_bitfield, patient_bitfield, doctor_bitfield, service_bitfield, company_bitfield, hospital_bitfield, room_bitfield, appointment_bitfield) VALUES
  ('Admin', 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7),
  ('Doctor', 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 0),
  ('Patient', 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 0);

CREATE TABLE users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(64) NOT NULL,
  last_name VARCHAR(64) NOT NULL,
  username VARCHAR(64) NOT NULL,
  password VARCHAR(255) NOT NULL,
  email VARCHAR(128) NOT NULL,
  email_verified BOOLEAN NOT NULL DEFAULT FALSE,
  phone VARCHAR(32) DEFAULT 'None',
  phone_verified BOOLEAN NOT NULL DEFAULT FALSE,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE user_roles (
  user_id INT UNSIGNED NOT NULL,
  role_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (user_id, role_id),
  CONSTRAINT ur_user_id
    FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  INDEX ur_user_id_idx (user_id),
  CONSTRAINT ur_role_id
    FOREIGN KEY (role_id)
    REFERENCES roles(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  INDEX ur_role_id_idx (role_id)
) ENGINE=InnoDB;

CREATE TABLE verification_codes (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  verification_code VARCHAR(255) NOT NULL,
  type VARCHAR(32) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  CONSTRAINT mv_user_id
    FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  INDEX mv_user_id_idx (user_id)
) ENGINE=InnoDB;

CREATE TABLE bans (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  reason TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  CONSTRAINT bn_user_id
    FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  INDEX bn_user_id_idx (user_id)
) ENGINE=InnoDB;

CREATE TABLE doctors (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone VARCHAR(20) NOT NULL,
  speciality VARCHAR(255) NOT NULL,
  status ENUM('Available', 'Absent', 'Unavailable') NOT NULL,
  is_verified BOOLEAN NOT NULL DEFAULT FALSE,
  PRIMARY KEY (id),
  CONSTRAINT dt_user_id
    FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  INDEX dt_user_id_idx (user_id),
  CONSTRAINT uc_doctor_user_id UNIQUE (user_id)
) ENGINE=InnoDB;

CREATE TABLE patients (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  date_of_birth DATE NOT NULL,
  gender ENUM('M', 'F', 'O') NOT NULL,
  address VARCHAR(255) NOT NULL,
  social_security_number VARCHAR(128) NOT NULL,
  insurance_number VARCHAR(128) NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT uc_user_id UNIQUE (user_id)
) ENGINE=InnoDB;

CREATE TABLE services (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE service_doctors (
  service_id INT UNSIGNED NOT NULL,
  doctor_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (service_id, doctor_id),
  CONSTRAINT sd_service_id
    FOREIGN KEY (service_id)
    REFERENCES services(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  INDEX sd_service_id_idx (service_id),
  CONSTRAINT sd_doctor_id
    FOREIGN KEY (doctor_id)
    REFERENCES doctors(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  INDEX sd_doctor_id_idx (doctor_id)
) ENGINE=InnoDB;

CREATE TABLE companies (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  code VARCHAR(2) NOT NULL,
  logo VARCHAR(255),
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE hospitals (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id INT UNSIGNED NOT NULL,
  name VARCHAR(255) NOT NULL,
  code VARCHAR(3) NOT NULL,
  country VARCHAR(255) NOT NULL,
  region VARCHAR(255) NOT NULL,
  city VARCHAR(255) NOT NULL,
  address VARCHAR(255) NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT hs_company_id
    FOREIGN KEY (company_id)
    REFERENCES companies(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  INDEX hs_company_id_idx (company_id)
) ENGINE=InnoDB;

CREATE TABLE rooms (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  hospital_id INT UNSIGNED NOT NULL,
  name VARCHAR(255) NOT NULL,
  code VARCHAR(3) NOT NULL,
  floor INT UNSIGNED NOT NULL,
  room_number INT UNSIGNED NOT NULL,
  room_type ENUM('General Ward', 'Private', 'Intensive Care Unit', 'Labor and Delivery', 'Operating', 'Recovery', 'Isolation', 'Emergency', 'Imaging', 'Procedure', 'Physical Therapy', 'Consultation') NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT ch_hospital_id
    FOREIGN KEY (hospital_id)
    REFERENCES hospitals(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  INDEX ch_hospital_id_idx (hospital_id)
) ENGINE=InnoDB;

CREATE TABLE hospital_doctors (
  hospital_id INT UNSIGNED NOT NULL,
  doctor_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (hospital_id, doctor_id),
  CONSTRAINT hd_hospital_id
    FOREIGN KEY (hospital_id)
    REFERENCES hospitals(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  INDEX hd_hospital_id_idx (hospital_id),
  CONSTRAINT hd_doctor_id
    FOREIGN KEY (doctor_id)
    REFERENCES doctors(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  INDEX hd_doctor_id_idx (doctor_id)
) ENGINE=InnoDB;

CREATE TABLE appointments (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id INT UNSIGNED NOT NULL,
  doctor_id INT UNSIGNED NOT NULL,
  service_id INT UNSIGNED NOT NULL,
  hospital_id INT UNSIGNED DEFAULT NULL,
  room_id INT UNSIGNED DEFAULT NULL,
  date DATE NOT NULL,
  time TIME NOT NULL,
  status ENUM('Confirmed', 'Completed', 'Absent', 'Cancelled by Patient', 'Cancelled by Doctor') NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT ap_patient_id
    FOREIGN KEY (patient_id)
    REFERENCES patients(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  INDEX ap_patient_id_idx (patient_id),
  CONSTRAINT ap_doctor_id
    FOREIGN KEY (doctor_id)
    REFERENCES doctors(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  INDEX ap_doctor_id_idx (doctor_id),
  CONSTRAINT ap_service_id
    FOREIGN KEY (service_id)
    REFERENCES services(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  INDEX ap_service_id_idx (service_id),
  CONSTRAINT ap_hospital_id
    FOREIGN KEY (hospital_id)
    REFERENCES hospitals(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  INDEX ap_hospital_id_idx (hospital_id),
  CONSTRAINT ap_room_id
    FOREIGN KEY (room_id)
    REFERENCES rooms(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  INDEX ap_room_id_idx (room_id)
) ENGINE=InnoDB;