BEGIN TRANSACTION;
CREATE TABLE trials (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        code TEXT,
        created DATETIME DEFAULT CURRENT_TIMESTAMP,
        modified DATETIME DEFAULT CURRENT_TIMESTAMP,
        prob_method INTEGER DEFAULT 0,
        base_prob FLOAT DEFAULT 0.7,
        dist_method INTEGER DEFAULT 0,
        identifier_type INTEGER DEFAULT 0,
        identifier_order INTEGER DEFAULT 0,
        identifier_length INTEGER DEFAULT 3,
        recycle_ids INTEGER DEFAULT 0,
        new_subject_random INTEGER DEFAULT 0,
        arms_weight FLOAT DEFAULT 0);
CREATE TABLE discarded_identifiers (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        trial_id INTEGER NOT NULL,
        identifier INTEGER NOT NULL,
        FOREIGN KEY (trial_id) REFERENCES trials (id) ON DELETE CASCADE,
        UNIQUE (trial_id, identifier) ON CONFLICT ABORT);
CREATE TABLE treatments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        trial_id INTEGER NOT NULL,
        title TEXT NOT NULL,
        ratio INTEGER DEFAULT 1,
        FOREIGN KEY (trial_id) REFERENCES trials (id) ON DELETE CASCADE,
        UNIQUE (trial_id, title) ON CONFLICT ABORT);
CREATE TABLE factors (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        trial_id INTEGER NOT NULL,
        title TEXT NOT NULL,
        weight FLOAT DEFAULT 1.0 NOT NULL,
        FOREIGN KEY (trial_id) REFERENCES trials (id) ON DELETE CASCADE,
        UNIQUE (trial_id, title) ON CONFLICT ABORT);
CREATE TABLE levels (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        trial_id INTEGER NOT NULL,
        factor_id INTEGER NOT NULL,
        title TEXT NOT NULL,
        FOREIGN KEY (trial_id) REFERENCES trials (id) ON DELETE CASCADE,
        FOREIGN KEY (factor_id) REFERENCES factors (id) ON DELETE CASCADE,
        UNIQUE (factor_id, title) ON CONFLICT ABORT);
CREATE TABLE subjects (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        trial_id INTEGER NOT NULL,
        identifier TEXT NOT NULL,
        identifier_value INTEGER NOT NULL,
        treatment_id INTEGER NOT NULL,
        enrolled DATETIME DEFAULT CURRENT_TIMESTAMP,
        modified DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (trial_id) REFERENCES trials (id) ON DELETE CASCADE,
        FOREIGN KEY (treatment_id) REFERENCES treatments (id) ON DELETE CASCADE,
        UNIQUE (trial_id, identifier) ON CONFLICT ABORT);
CREATE TABLE subject_levels (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        trial_id INTEGER NOT NULL,
        subject_id INTEGER NOT NULL,
        factor_id INTEGER NOT NULL,
        level_id INTEGER NOT NULL,
        FOREIGN KEY (trial_id) REFERENCES trials (id) ON DELETE CASCADE,
        FOREIGN KEY (subject_id) REFERENCES subjects (id) ON DELETE CASCADE,
        FOREIGN KEY (factor_id) REFERENCES factors (id) ON DELETE CASCADE,
        FOREIGN KEY (level_id) REFERENCES levels (id) ON DELETE CASCADE,
        UNIQUE (factor_id, subject_id) ON CONFLICT ABORT);
CREATE TABLE preloads (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        trial_id INTEGER NOT NULL,
        treatment_id INTEGER NOT NULL,
        factor_id INTEGER NOT NULL,
        level_id INTEGER NOT NULL,
        count INTEGER DEFAULT 0,
        FOREIGN KEY (trial_id) REFERENCES trials (id) ON DELETE CASCADE,
        FOREIGN KEY (treatment_id) REFERENCES treatments (id) ON DELETE CASCADE,
        FOREIGN KEY (factor_id) REFERENCES factors (id) ON DELETE CASCADE,
        FOREIGN KEY (level_id) REFERENCES levels (id) ON DELETE CASCADE,
        UNIQUE (treatment_id, factor_id, level_id) ON CONFLICT ABORT);
DELETE FROM sqlite_sequence;
CREATE VIEW subject_treatment AS SELECT 
        subjects.id id, subjects.trial_id trial_id, identifier, treatments.title as treatment 
        FROM subjects INNER JOIN treatments ON treatments.id = treatment_id;
CREATE VIEW sl_view as SELECT subject_levels.id id, subject_levels.subject_id subject_id, 
subject_levels.factor_id factor_id, subject_levels.level_id level_id, factors.title factor, 
levels.title level, subjects.treatment_id from subject_levels LEFT JOIN levels ON 
subject_levels.level_id = levels.id LEFT JOIN factors ON subject_levels.factor_id = factors.id 
LEFT JOIN subjects ON subject_levels.subject_id = subjects.id;
COMMIT;

