CREATE TABLE workspaces (
    id INTEGER PRIMARY KEY NOT NULL,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE workspace_envs (
    id INTEGER PRIMARY KEY NOT NULL,
    workspace_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    FOREIGN KEY (workspace_id) REFERENCES workspaces (id) ON DELETE CASCADE
);

CREATE TABLE workspace_env_variables (
    id INTEGER PRIMARY KEY NOT NULL,
    workspace_id INTEGER NOT NULL,
    env_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    value TEXT,
    secret BOOLEAN NOT NULL,
    -- Optional collection scope
    collection_id INTEGER,
    FOREIGN KEY (workspace_id) REFERENCES workspaces (id) ON DELETE CASCADE,
    FOREIGN KEY (env_id) REFERENCES workspace_envs (id) ON DELETE CASCADE
);

CREATE TABLE workspace_entries (
    id INTEGER PRIMARY KEY NOT NULL,
    workspace_id INTEGER NOT NULL,
    parent_id INTEGER,
    name TEXT NOT NULL,
    type INTEGER NOT NULL,
    FOREIGN KEY (parent_id) REFERENCES workspace_entries (id) ON DELETE CASCADE,
    FOREIGN KEY (workspace_id) REFERENCES workspaces (id) ON DELETE CASCADE
);

CREATE TABLE request_params (
    id INTEGER PRIMARY KEY NOT NULL,
    workspace_id INTEGER NOT NULL,
    request_id INTEGER UNIQUE NOT NULL,
    method TEXT NOT NULL,
    url TEXT NOT NULL,
    FOREIGN KEY (request_id) REFERENCES workspace_entries (id) ON DELETE CASCADE,
    FOREIGN KEY (workspace_id) REFERENCES workspaces (id) ON DELETE CASCADE
);

CREATE TABLE request_bodies (
    id INTEGER PRIMARY KEY NOT NULL,
    request_id UNIQUE NOT NULL,
    content_type TEXT NOT NULL,
    body TEXT NOT NULL,
    FOREIGN KEY (request_id) REFERENCES workspace_entries (id) ON DELETE CASCADE
);

CREATE TABLE request_headers (
    id INTEGER PRIMARY KEY NOT NULL,
    request_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    value TEXT NOT NULL,
    FOREIGN KEY (request_id) REFERENCES workspace_entries (id) ON DELETE CASCADE
);