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

CREATE TABLE workspace_envs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    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 AUTOINCREMENT,
    env_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    value TEXT NOT NULL,
    secret BOOLEAN NOT NULL,
    FOREIGN KEY (env_id) REFERENCES workspace_envs (id) ON DELETE CASCADE
);

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

CREATE TABLE collection_variables (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    collection_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    value TEXT NOT NULL,
    FOREIGN KEY (collection_id) REFERENCES collections (id) ON DELETE CASCADE
);

CREATE TABLE http_requests (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    workspace_id INTEGER NOT NULL,
    -- null if request is in workspace root
    collection_id INTEGER,	
    name TEXT NOT NULL,
    method TEXT NOT NULL,
    url TEXT NOT NULL,
    FOREIGN KEY (collection_id) REFERENCES collections (id) ON DELETE CASCADE,
    FOREIGN KEY (workspace_id) REFERENCES workspaces (id) ON DELETE CASCADE
);

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

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