CREATE TABLE emoji_category (
  emoji_category_id SMALLINT    NOT NULL PRIMARY KEY,
  description       VARCHAR(50) NOT NULL,
  code              VARCHAR(15) NOT NULL
);

INSERT INTO emoji_category (emoji_category_id, description, code) VALUES
  (1, 'Smileys & People', 'people'),
  (2, 'Animals & Nature', 'nature'),
  (3, 'Food & Drink', 'food-drink'),
  (4, 'Activity', 'activity'),
  (5, 'Travel & Places', 'travel-places'),
  (6, 'Objects', 'objects'),
  (7, 'Symbols', 'symbols'),
  (8, 'Flags', 'flags');


CREATE TABLE emoji (
  emoji_id          SMALLINT    NOT NULL PRIMARY KEY,
  emoji             VARCHAR(16) NOT NULL,
  description       TEXT,
  emoji_category_id SMALLINT REFERENCES emoji_category (emoji_category_id),
  shortcode         VARCHAR(50),
  active            BOOLEAN     NOT NULL DEFAULT TRUE,
  accepts_modifiers BOOLEAN     NOT NULL DEFAULT FALSE
);

CREATE TABLE emoji_icon (
  emoji_id SMALLINT NOT NULL REFERENCES emoji (emoji_id) ON DELETE CASCADE,
  modifier SMALLINT NOT NULL DEFAULT 0,
  url      VARCHAR(500),
  PRIMARY KEY (emoji_id, modifier)
);