-- all ids will be TEXT
-- make a table for storing webpages and related opengraph information in json format
CREATE TABLE webpages (
    id TEXT PRIMARY KEY,
    url TEXT NOT NULL UNIQUE,
    opengraph JSONB NOT NULL
);

-- add an index to the url column
CREATE INDEX url_index ON webpages (url);

-- make a table for storing comments on webpages and replies to comments
CREATE TABLE comments (
    id TEXT PRIMARY KEY,
    webpage_id TEXT NOT NULL,
    parent_id TEXT, -- NULL if top-level comment
    commenter TEXT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- make a table for storing votes on comments and webpages
CREATE TABLE votes (
    id TEXT PRIMARY KEY,
    webpage_id TEXT NOT NULL,
    comment_id TEXT, -- NULL if vote is on webpage
    voter TEXT NOT NULL,
    vote_type TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (voter, comment_id, webpage_id) -- ensure a user can only vote once per comment or webpage
);

-- make a table for storing users
CREATE TABLE users (
    id TEXT PRIMARY KEY,
    username TEXT NOT NULL,
    avatar TEXT NOT NULL
);
