CREATE TABLE "users" (
  id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  ts TIMESTAMP NOT NULL DEFAULT NOW(),
  username TEXT NOT NULL CHECK (username != '') UNIQUE,
  pass TEXT NOT NULL,
  can_view BOOL NOT NULL DEFAULT FALSE,
  can_comment BOOL NOT NULL DEFAULT FALSE,
  can_post BOOL NOT NULL DEFAULT FALSE,
  can_moderate BOOL NOT NULL DEFAULT FALSE,
  can_manage_users BOOL NOT NULL DEFAULT FALSE
);

CREATE TABLE "posts" (
  id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  ts TIMESTAMP NOT NULL DEFAULT NOW(),
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  author INT NOT NULL REFERENCES "users" (id) ON UPDATE CASCADE ON DELETE CASCADE,
  pinned BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE "comments" (
  id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  ts TIMESTAMP NOT NULL DEFAULT NOW(),
  body TEXT NOT NULL,
  author INT NOT NULL REFERENCES "users" (id) ON UPDATE CASCADE ON DELETE CASCADE,
  parent INT NOT NULL REFERENCES "posts" (id) ON UPDATE CASCADE ON DELETE CASCADE,
  reply_to INT REFERENCES "comments" (id) ON UPDATE CASCADE ON DELETE CASCADE
);

-- add test data

INSERT INTO "users" (username, pass, can_view, can_comment, can_post, can_moderate, can_manage_users) VALUES
  ('admin', '$2y$12$BTM/W6V9BTN005/APBxN0eX42ATlub0fKhWlQxsOfvyd4iz3m4b.a', TRUE, TRUE, TRUE, TRUE, TRUE);