CREATE TABLE authors (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  iri TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL
);

CREATE TABLE tags (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL
);

INSERT INTO authors (iri, display_name) VALUES ('https://lotte.chir.rs', 'Charlotte 🦝 Raccoon');

CREATE TYPE post_content_type AS ENUM ('html', 'markdown');
CREATE TYPE post_kind AS ENUM (
  'page', -- slug is the url starting with /, for example /about
  'post', -- slug is the last part of the url, the link is formatted like /2026/w35/1/post-slug
  -- from this point, the slug is not used, the path they are under is /2026/w35/1/post_type/encoded-id
  'note' -- microblogging message
);

CREATE TABLE pages (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug TEXT,
  kind post_kind NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  -- set to null before publication and after unpublication
  published_at TIMESTAMPTZ,
  CONSTRAINT slug_requirement CHECK (
    (kind IN ('page', 'post') AND slug IS NOT NULL AND length(trim(slug)) > 0)
    OR (kind NOT IN ('page', 'post') AND slug IS NULL)
  )
);
CREATE UNIQUE INDEX pages_slug ON pages (slug) WHERE slug IS NOT NULL;

CREATE TABLE page_versions (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  page_id BIGINT NOT NULL REFERENCES pages(id),
  content_type post_content_type NOT NULL,
  content TEXT NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE page_version_authors(
  page_version_id BIGINT NOT NULL REFERENCES page_versions(id),
  author_id BIGINT NOT NULL REFERENCES authors(id),
  PRIMARY KEY (page_version_id, author_id)
);

CREATE TABLE page_version_tags(
  page_version_id BIGINT NOT NULL REFERENCES page_versions(id),
  tag_id BIGINT NOT NULL REFERENCES tags(id),
  PRIMARY KEY (page_version_id, tag_id)
);

CREATE TABLE redirects(
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  src_slug TEXT NOT NULL UNIQUE,
  dst_page_id BIGINT NOT NULL REFERENCES pages(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_page_versions_page_id ON page_versions(page_id);
CREATE INDEX idx_page_version_authors_author_id ON page_version_authors(author_id);
CREATE INDEX idx_page_version_tags_tag_id ON page_version_tags(tag_id);