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

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

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 NOT NULL,
    secret BOOLEAN NOT NULL,
    FOREIGN KEY (workspace_id) REFERENCES workspaces (id) ON DELETE CASCADE,
    FOREIGN KEY (env_id) REFERENCES workspace_envs (id) ON DELETE CASCADE,
    UNIQUE(env_id, name)
);

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,
    auth INTEGER,
    auth_inherit BOOLEAN NOT NULL DEFAULT TRUE,
    FOREIGN KEY (parent_id) REFERENCES workspace_entries (id) ON DELETE CASCADE,
    FOREIGN KEY (workspace_id) REFERENCES workspaces (id) ON DELETE CASCADE,
    FOREIGN KEY (auth) REFERENCES auth (id) ON DELETE SET NULL
);

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_path_params (
    id INTEGER PRIMARY KEY NOT NULL,
    position INTEGER NOT NULL,
    request_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    value TEXT NOT NULL,
    UNIQUE(position, request_id),
    FOREIGN KEY (request_id) REFERENCES workspace_entries (id) ON DELETE CASCADE
);

CREATE TABLE request_query_params (
    id INTEGER PRIMARY KEY NOT NULL,
    -- A non-null position means a QP is enabled
    position INTEGER,
    request_id INTEGER NOT NULL,
    key TEXT NOT NULL,
    value TEXT NOT NULL,
    UNIQUE(position, request_id),
    FOREIGN KEY (request_id) REFERENCES workspace_entries (id) ON DELETE CASCADE
);

CREATE TABLE request_bodies (
    id INTEGER PRIMARY KEY NOT NULL,
    request_id UNIQUE NOT NULL,
    ty TEXT NOT NULL,
    content 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
);
