-- 002-users

-- user-roles

CREATE TABLE user_roles (
  id          NUMBER(19) PRIMARY KEY,
  title       VARCHAR2(64 CHAR)        NOT NULL,
  description VARCHAR2(255 CHAR),
  admin       NUMBER(1)                NOT NULL,
  deleted     NUMBER(1)                NOT NULL,
  permissions VARCHAR2(4000 CHAR),

  created_at  TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at  TIMESTAMP WITH TIME ZONE NOT NULL,
  deleted_at  TIMESTAMP WITH TIME ZONE,
  restored_at TIMESTAMP WITH TIME ZONE
);

CREATE SEQUENCE user_roles_id_seq;

CREATE UNIQUE INDEX user_roles_title_uniq
  ON user_roles (
    CASE WHEN deleted = 0
      THEN lower(title)
    END
  );

-- users

CREATE TABLE users (
  id                     NUMBER(19) PRIMARY KEY,
  name                   VARCHAR2(255 CHAR)       NOT NULL,
  email                  VARCHAR2(128 CHAR)       NOT NULL,
  position               VARCHAR2(255 CHAR)       NOT NULL,
  role_id                NUMBER(19) REFERENCES user_roles (id),
  avatar_id              VARCHAR2(255 CHAR),
  "online"               NUMBER(1)                NOT NULL,
  deleted                NUMBER(1)                NOT NULL,

  change_password        NUMBER(1)                NOT NULL,
  password_hash          VARCHAR2(255 CHAR),
  password_changed_at    TIMESTAMP WITH TIME ZONE,

  total_assigned_tickets NUMBER(10)               NOT NULL,

  created_at             TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at             TIMESTAMP WITH TIME ZONE NOT NULL,
  logged_in_at           TIMESTAMP WITH TIME ZONE,
  last_seen_at           TIMESTAMP WITH TIME ZONE,
  deleted_at             TIMESTAMP WITH TIME ZONE,
  restored_at            TIMESTAMP WITH TIME ZONE,
  assigned_at            TIMESTAMP WITH TIME ZONE
);

CREATE SEQUENCE users_id_seq;

CREATE UNIQUE INDEX users_email_uniq
  ON users (lower(email));

CREATE INDEX users_online_last_seen_at_ix
  ON users (
    CASE WHEN "online" = 1
      THEN last_seen_at
    END
  );

-- user-sessions

CREATE TABLE user_sessions (
  id         NUMBER(19) PRIMARY KEY,
  user_id    NUMBER(19)         NOT NULL REFERENCES users (id),
  token      VARCHAR2(255 CHAR) NOT NULL,
  active     NUMBER(1)          NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE,
  expires_at TIMESTAMP WITH TIME ZONE
);

CREATE SEQUENCE user_sessions_id_seq;

CREATE UNIQUE INDEX user_sessions_token_uniq
  ON user_sessions (token);

CREATE INDEX user_sessions_user_id_ix
  ON user_sessions (user_id);

-- user-agents

CREATE TABLE user_agents (
  id         NUMBER(19) PRIMARY KEY,
  user_agent VARCHAR2(1024 CHAR) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE
);

CREATE SEQUENCE user_agents_id_seq;

CREATE UNIQUE INDEX user_agents_uniq
  ON user_agents (lower(user_agent));
