-- 013-facebook

CREATE TABLE facebook_channels (
  id          NUMBER(19) PRIMARY KEY REFERENCES channels (id),
  uuid        VARCHAR2(36 CHAR),
  page_id     VARCHAR2(255 CHAR),
  page_name   VARCHAR2(1024 CHAR),
  deleted     NUMBER(1)                NOT NULL,

  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 UNIQUE INDEX facebook_channels_uuid_uniq
  ON facebook_channels (
    CASE WHEN uuid IS NOT NULL
      THEN uuid END
  );

CREATE UNIQUE INDEX facebook_channels_page_uniq
  ON facebook_channels (
    CASE WHEN deleted = 0 AND page_id IS NOT NULL
      THEN page_id END
  );

-- facebook-configs

CREATE TABLE facebook_configs (
  channel_id   NUMBER(19) REFERENCES channels (id),
  page_token   VARCHAR2(255 CHAR),
  verify_token VARCHAR2(255 CHAR)
);

-- facebook-users

CREATE TABLE facebook_users (
  id              NUMBER(19) PRIMARY KEY,
  channel_id      NUMBER(19)               NOT NULL REFERENCES channels (id),
  user_id         VARCHAR2(255 CHAR)       NOT NULL,
  first_name      VARCHAR2(255 CHAR),
  last_name       VARCHAR2(255 CHAR),
  gender          VARCHAR2(6 CHAR),
  profile_picture VARCHAR2(255 CHAR),

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

CREATE SEQUENCE facebook_users_id_seq;

CREATE UNIQUE INDEX facebook_users__uniq
  ON facebook_users (
    channel_id, user_id);

-- facebook-chats

CREATE TABLE facebook_chats (
  id         NUMBER(19) PRIMARY KEY,
  chat_id    NUMBER(19)               NOT NULL,
  user_id    NUMBER(19)               NOT NULL REFERENCES facebook_users (id),
  created_at TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE SEQUENCE facebook_chats_id_seq;

CREATE UNIQUE INDEX facebook_chats_chat_uniq
  ON facebook_chats (chat_id);
