CREATE DATABASE IF NOT EXISTS clinical_trials;
USE clinical_trials;

CREATE TABLE users (
    user_id       INT          AUTO_INCREMENT PRIMARY KEY,
    username      VARCHAR(50)  NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    email         VARCHAR(150) NOT NULL UNIQUE,
    full_name     VARCHAR(150),
    is_active     TINYINT(1)   NOT NULL DEFAULT 1,
    created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE roles (
    role_id     INT         AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(50) NOT NULL UNIQUE,
    description TEXT
);

CREATE TABLE studies (
    study_id        INT          AUTO_INCREMENT PRIMARY KEY,
    study_code      VARCHAR(50)  NOT NULL UNIQUE,
    title           VARCHAR(255) NOT NULL,
    description     TEXT,
    contact_user_id INT,
    laboratory      VARCHAR(255),
    start_date      DATE,
    end_date        DATE,
    status          ENUM('draft','recruiting','active','completed','suspended') NOT NULL DEFAULT 'draft',
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (contact_user_id) REFERENCES users(user_id)
);

CREATE TABLE participants (
    participant_id INT         AUTO_INCREMENT PRIMARY KEY,
    pseudonym      VARCHAR(50) NOT NULL UNIQUE,
    year_of_birth  YEAR,
    sex            ENUM('M','F','other','unknown'),
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE enrollments (
    enrollment_id   INT  AUTO_INCREMENT PRIMARY KEY,
    study_id        INT  NOT NULL,
    participant_id  INT  NOT NULL,
    enrollment_date DATE NOT NULL,
    status          ENUM('active','withdrawn','completed') NOT NULL DEFAULT 'active',
    FOREIGN KEY (study_id)       REFERENCES studies(study_id),
    FOREIGN KEY (participant_id) REFERENCES participants(participant_id),
    UNIQUE KEY uq_enrollment (study_id, participant_id)
);

CREATE TABLE variables (
    variable_id INT          AUTO_INCREMENT PRIMARY KEY,
    study_id    INT          NOT NULL,
    name        VARCHAR(100) NOT NULL,
    description TEXT,
    unit        VARCHAR(50),
    data_type   ENUM('numeric','text','boolean','date') NOT NULL DEFAULT 'numeric',
    is_required TINYINT(1)  NOT NULL DEFAULT 0,
    FOREIGN KEY (study_id) REFERENCES studies(study_id),
    UNIQUE KEY uq_variable (study_id, name)
);

CREATE TABLE timepoints (
    timepoint_id INT          AUTO_INCREMENT PRIMARY KEY,
    study_id     INT          NOT NULL,
    name         VARCHAR(100) NOT NULL,
    day_offset   INT          NOT NULL DEFAULT 0,
    FOREIGN KEY (study_id) REFERENCES studies(study_id)
);

CREATE TABLE measurements (
    measurement_id INT      AUTO_INCREMENT PRIMARY KEY,
    enrollment_id  INT      NOT NULL,
    variable_id    INT      NOT NULL,
    timepoint_id   INT      NOT NULL,
    value_numeric  DOUBLE,
    value_text     TEXT,
    collected_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    collected_by   INT,
    FOREIGN KEY (enrollment_id) REFERENCES enrollments(enrollment_id),
    FOREIGN KEY (variable_id)   REFERENCES variables(variable_id),
    FOREIGN KEY (timepoint_id)  REFERENCES timepoints(timepoint_id),
    FOREIGN KEY (collected_by)  REFERENCES users(user_id)
);

CREATE TABLE data_files (
    file_id       INT          AUTO_INCREMENT PRIMARY KEY,
    enrollment_id INT          NOT NULL,
    timepoint_id  INT,
    filename      VARCHAR(255) NOT NULL,
    file_type     VARCHAR(50),
    file_path     VARCHAR(500) NOT NULL,
    uploaded_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    uploaded_by   INT,
    FOREIGN KEY (enrollment_id) REFERENCES enrollments(enrollment_id),
    FOREIGN KEY (timepoint_id)  REFERENCES timepoints(timepoint_id),
    FOREIGN KEY (uploaded_by)   REFERENCES users(user_id)
);

CREATE TABLE user_roles (
    user_role_id INT      AUTO_INCREMENT PRIMARY KEY,
    user_id      INT      NOT NULL,
    role_id      INT      NOT NULL,
    study_id     INT,
    granted_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id)  REFERENCES users(user_id),
    FOREIGN KEY (role_id)  REFERENCES roles(role_id),
    FOREIGN KEY (study_id) REFERENCES studies(study_id),
    UNIQUE KEY uq_user_role_study (user_id, role_id, study_id)
);

INSERT INTO roles (name, description) VALUES
    ('admin',      'Full access to all studies and system configuration'),
    ('supervisor', 'Oversight of study progress and data quality'),
    ('researcher', 'Read-only access to assigned study data and results'),
    ('clinician',  'Data entry and read access for assigned study participants'),
    ('data_entry', 'Limited data-entry access for assigned studies');