CREATE TABLE step (
    id INTEGER PRIMARY KEY,
    code TEXT UNIQUE NOT NULL
);

CREATE TABLE step_input (
    id INTEGER PRIMARY KEY,
    step_id INTEGER NOT NULL,
    code TEXT NOT NULL,
    UNIQUE(step_id, code),
    FOREIGN KEY (step_id) REFERENCES step(id)
);

CREATE TABLE step_data (
    id INTEGER PRIMARY KEY,
    step_id INTEGER NOT NULL,
    session_date TEXT NOT NULL,
    participant TEXT NOT NULL,
    judge TEXT NOT NULL,
    version TEXT NOT NULL,
    entry_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    interval_start REAL,
    interval_duration REAL CHECK (interval_duration > 0),
    FOREIGN KEY (step_id) REFERENCES step(id),
    UNIQUE (step_id, session_date, participant, judge)
);

CREATE TABLE step_input_data (
    step_input_id INTEGER,
    step_data_id INTEGER,
    info_value REAL NOT NULL,
    version TEXT NOT NULL,
    PRIMARY KEY (step_input_id, step_data_id),
    FOREIGN KEY (step_input_id) REFERENCES step_input(id),
    FOREIGN KEY (step_data_id) REFERENCES step_data(id)
);
