/**
  * Keys Table
  * The keys table stores the encryption keys used to encrypt user data.
  * It holds encrypted AES keys and the KMS key used to encrypt them.
 */
CREATE TABLE IF NOT EXISTS keys
(
    id            SERIAL PRIMARY KEY,                             -- Unique ID for the key
    kms_key_name  VARCHAR(255) NOT NULL,                          -- The name of the KMS key
    encrypted_key BYTEA        NOT NULL,                          -- The encrypted AES key (encrypted with the KMS key)
    created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP -- The datetime the key was created
);

/**
 * Users Table
 * The users table stores the user data for the app.
 */
CREATE TABLE IF NOT EXISTS users
(
    subject        TEXT PRIMARY KEY,            -- The unique subject of the user, retrieved from Auth0
    ibd_username   VARCHAR(255),                -- The IBD username of the user
    ibd_password   BYTEA,                       -- The encrypted IBD password. Encrypted with `encryption_key`
    encryption_key INTEGER REFERENCES keys (id) -- The encryption key used to encrypt the IBD password
);

/**
 * IBD Tokens Table
 * The IBD tokens table stores the tokens/cookies used to authenticate with the IBD website.
 * These are scraped using chromedb and stored in the database for future use via APIs.
 */
CREATE TABLE IF NOT EXISTS ibd_tokens
(
    id             SERIAL PRIMARY KEY,                            -- Unique ID for the token
    user_subject   TEXT      NOT NULL REFERENCES users (subject), -- The user in the users table associated with the token
    token          BYTEA     NOT NULL,                            -- The encrypted token/cookie
    encryption_key INTEGER   NOT NULL REFERENCES keys (id),       -- The encryption key used to encrypt the token
    created_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- The datetime the token was created
    expires_at     TIMESTAMP NOT NULL,                            -- The datetime the token expires
    /**
     * The `degraded` column is used to indicate that the token MAY be invalid.
     * It is set to TRUE when a worker reports a failure using the token.
     * This will cause the authentication cronjob to check the token and re-authenticate if necessary.
     */
    degraded       BOOLEAN   NOT NULL DEFAULT FALSE
);

CREATE INDEX IF NOT EXISTS idx_ibd_tokens_user_subject ON ibd_tokens (user_subject);