

CREATE TABLE IF NOT EXISTS categories (
  id       SMALLSERIAL PRIMARY KEY,
  slug     VARCHAR(32) NOT NULL UNIQUE,
  title    VARCHAR(64) NOT NULL,
  sort     SMALLINT NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS threads (
  id           BIGSERIAL PRIMARY KEY,
  category_id  SMALLINT NOT NULL REFERENCES categories(id) ON DELETE RESTRICT,
  title        VARCHAR(140) NOT NULL,
  created_by   BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS thread_posts (
  id          BIGSERIAL PRIMARY KEY,
  thread_id   BIGINT NOT NULL REFERENCES threads(id) ON DELETE CASCADE,
  user_id     BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  body        TEXT NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_threads_created_at ON threads(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_thread_posts_thread_id ON thread_posts(thread_id, created_at);

CREATE TABLE IF NOT EXISTS chat_attachments (
  id          BIGSERIAL PRIMARY KEY,
  user_id     BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  filename    TEXT NOT NULL,
  mime        TEXT NOT NULL,
  size_bytes  BIGINT NOT NULL,
  url_path    TEXT NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS chat_messages (
  id             BIGSERIAL PRIMARY KEY,
  user_id        BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  message        TEXT NOT NULL,
  attachment_id  BIGINT NULL REFERENCES chat_attachments(id) ON DELETE SET NULL,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_chat_messages_created_at ON chat_messages(created_at DESC);

-- Seed categories (idempotent)
INSERT INTO categories (slug, title, sort)
VALUES
  ('manuals', 'Мануалы', 10),
  ('rules', 'Правила', 20),
  ('info', 'Информация', 30),
  ('market', 'Купля/Продажа', 40)
ON CONFLICT (slug) DO UPDATE
SET title = EXCLUDED.title, sort = EXCLUDED.sort;
