database/schema.sql - master - El_Capulco/Clinic

database/schema.sql
174 строки5 KB
Phofan
готовая бд
11 мар 2026, 19:20
11 мар 2026, 19:20f90ea36
-- MySQL 8+
CREATE DATABASE IF NOT EXISTS clinic
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;

USE clinic;

-- =========================
-- Doctors
-- =========================
CREATE TABLE IF NOT EXISTS doctors (
  id INT PRIMARY KEY AUTO_INCREMENT,
  fio VARCHAR(255) NOT NULL,
  specialty VARCHAR(255) NOT NULL,
  cabinet VARCHAR(50) NULL,
  phone VARCHAR(50) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- =========================
-- Users (auth + roles)
-- =========================
CREATE TABLE IF NOT EXISTS users (
  id VARCHAR(36) PRIMARY KEY, -- UUID
  name VARCHAR(255) NOT NULL,
  role ENUM('admin','registrar','doctor') NOT NULL,
  doctor_id INT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_users_doctor
    FOREIGN KEY (doctor_id) REFERENCES doctors(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB;

-- =========================
-- Patients (registrar creates automatically)
-- =========================
CREATE TABLE IF NOT EXISTS patients (
  code VARCHAR(50) PRIMARY KEY,
  fio VARCHAR(255) NOT NULL,
  phone VARCHAR(20) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- =========================
-- Work slots (schedule header)
-- =========================
CREATE TABLE IF NOT EXISTS work_slots (
  id VARCHAR(36) PRIMARY KEY, -- UUID
  doctor_id INT NOT NULL,
  date DATE NOT NULL,
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  slot_minutes INT NOT NULL,
  break_start TIME NULL,
  break_end TIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_work_slots_doctor
    FOREIGN KEY (doctor_id) REFERENCES doctors(id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,

  UNIQUE KEY uq_doctor_date (doctor_id, date),

  CHECK (slot_minutes > 0),
  CHECK (start_time < end_time),
  CHECK (break_start IS NULL OR break_end IS NULL OR break_start < break_end)
) ENGINE=InnoDB;

-- =========================
-- Slots (generated grid)
-- =========================
CREATE TABLE IF NOT EXISTS slots (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  work_slot_id VARCHAR(36) NOT NULL,
  doctor_id INT NOT NULL,
  slot_datetime DATETIME NOT NULL,
  is_available TINYINT(1) NOT NULL DEFAULT 1,

  CONSTRAINT fk_slots_workslot
    FOREIGN KEY (work_slot_id) REFERENCES work_slots(id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,

  CONSTRAINT fk_slots_doctor
    FOREIGN KEY (doctor_id) REFERENCES doctors(id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,

  UNIQUE KEY uq_slots_doctor_datetime (doctor_id, slot_datetime),

  INDEX idx_slot_datetime (slot_datetime),
  INDEX idx_workslot (work_slot_id)
) ENGINE=InnoDB;

-- =========================
-- Appointments
-- =========================
CREATE TABLE IF NOT EXISTS appointments (
  id VARCHAR(36) PRIMARY KEY, -- UUID
  doctor_id INT NOT NULL,
  slot_datetime DATETIME NOT NULL,
  status ENUM('booked','canceled','completed') NOT NULL,

  patient_code VARCHAR(50) NOT NULL,
  patient_name VARCHAR(255) NOT NULL,
  patient_phone VARCHAR(20) NOT NULL,

  booked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  booked_by VARCHAR(36) NULL,

  canceled_reason VARCHAR(255) NULL,

  reschedule_from VARCHAR(36) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_appointments_doctor
    FOREIGN KEY (doctor_id) REFERENCES doctors(id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,

  CONSTRAINT fk_appointments_user
    FOREIGN KEY (booked_by) REFERENCES users(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,

  CONSTRAINT fk_appointments_reschedule_from
    FOREIGN KEY (reschedule_from) REFERENCES appointments(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,

  -- patients existence (we auto-upsert in backend)
  CONSTRAINT fk_appointments_patient
    FOREIGN KEY (patient_code) REFERENCES patients(code)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,

  -- only existing slots can be booked
  CONSTRAINT fk_appointments_slot_exists
    FOREIGN KEY (doctor_id, slot_datetime)
    REFERENCES slots(doctor_id, slot_datetime)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,

  INDEX idx_appt_doctor_datetime (doctor_id, slot_datetime),
  INDEX idx_appt_status (status),
  INDEX idx_appt_doctor_status (doctor_id, status)
) ENGINE=InnoDB;

-- =========================
-- Operation log
-- =========================
CREATE TABLE IF NOT EXISTS operation_log (
  id VARCHAR(36) PRIMARY KEY,
  action VARCHAR(50) NOT NULL,
  appointment_id VARCHAR(36) NULL,
  user_id VARCHAR(36) NULL,
  timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  reason VARCHAR(255) NULL,
  details_json JSON NULL,

  CONSTRAINT fk_oplog_appointment
    FOREIGN KEY (appointment_id) REFERENCES appointments(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,

  CONSTRAINT fk_oplog_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB;