-- accounts
CREATE TABLE account(
  id serial PRIMARY KEY,
  username varchar NOT NULL,
  password_hash varchar NOT NULL,
  created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  role varchar DEFAULT 'consumer',
  UNIQUE (username)
);

CREATE TABLE account_artist_favorite(
  id serial,
  created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  account_id int NOT NULL REFERENCES account(id),
  service varchar(20) NOT NULL,
  artist_id varchar(255) NOT NULL,
  PRIMARY KEY (service, id),
  UNIQUE (account_id, service, artist_id)
);

CREATE INDEX ON account_artist_favorite(service, artist_id);

CREATE TABLE account_post_favorite(
  id serial,
  created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  account_id int NOT NULL REFERENCES account(id),
  service varchar(20) NOT NULL,
  artist_id varchar(255) NOT NULL,
  post_id varchar(255) NOT NULL,
  PRIMARY KEY (service, id),
  UNIQUE (account_id, service, artist_id, post_id)
);

CREATE TABLE notifications(
  id bigserial PRIMARY KEY,
  account_id int NOT NULL,
  type SMALLINT NOT NULL,
  extra_info jsonb,
  created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  is_seen boolean NOT NULL DEFAULT FALSE,
  FOREIGN KEY (account_id) REFERENCES account(id)
);

CREATE INDEX account_idx ON account USING BTREE(username, created_at, ROLE);

CREATE INDEX ON account_post_favorite(service, artist_id, post_id);

CREATE INDEX notifications_account_id_idx ON notifications USING BTREE("account_id");

CREATE INDEX notifications_created_at_idx ON notifications USING BTREE("created_at");

CREATE INDEX notifications_type_idx ON notifications USING BTREE("type");