--  schema.sql

DROP TABLE posts;
DROP TABLE threads;
DROP TABLE boards;

CREATE TABLE IF NOT EXISTS boards (
    board_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    watch BOOLEAN DEFAULT 'f' NOT NULL,
    abbr VARCHAR(16) NOT NULL UNIQUE,
    name VARCHAR(64) NOT NULL,
    description VARCHAR(256) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CLOCK_TIMESTAMP() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CLOCK_TIMESTAMP() NOT NULL
);

CREATE TABLE IF NOT EXISTS threads (
    thread_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
    board_id INTEGER NOT NULL,
    subject VARCHAR(256) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CLOCK_TIMESTAMP() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CLOCK_TIMESTAMP() NOT NULL,

    PRIMARY KEY (board_id, thread_id),
    FOREIGN KEY (board_id) REFERENCES boards (board_id)
);

CREATE TABLE IF NOT EXISTS posts (
    post_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
    thread_id INTEGER NOT NULL,
    board_id INTEGER NOT NULL,
    ordinal INTEGER NOT NULL,
    op BOOLEAN NOT NULL,
    author VARCHAR(128) NOT NULL,
    email VARCHAR(128) NOT NULL,
    subject VARCHAR(256) NOT NULL,
    comment VARCHAR(16384) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CLOCK_TIMESTAMP() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CLOCK_TIMESTAMP() NOT NULL,

    PRIMARY KEY (board_id, post_id),
    FOREIGN KEY (board_id) REFERENCES boards (board_id),
    FOREIGN KEY (board_id, thread_id) REFERENCES threads (board_id, thread_id)
);
