-- Users table (both trainees and trainers)
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(100),
    role ENUM('trainer', 'trainee') NOT NULL
);

-- Blocks table
CREATE TABLE blocks (
    id VARCHAR(4) PRIMARY KEY, -- e.g. '1.1'
    long_name VARCHAR(255) NOT NULL
);

-- Tracks which trainers worked with which trainees
CREATE TABLE trainer_trainee (
    trainer_id INT,
    trainee_id INT,
    PRIMARY KEY (trainer_id, trainee_id),
    FOREIGN KEY (trainer_id) REFERENCES users(id),
    FOREIGN KEY (trainee_id) REFERENCES users(id)
);

-- Tracks what block items each trainee has completed
CREATE TABLE training_progress (
    trainee_id INT,
    block_id VARCHAR(4),
    completed_at DATETIME,
    trainer_id INT,
    PRIMARY KEY (trainee_id, block_id),
    FOREIGN KEY (trainee_id) REFERENCES users(id),
    FOREIGN KEY (block_id) REFERENCES blocks(id),
    FOREIGN KEY (trainer_id) REFERENCES users(id)
);
