create extension if not exists "uuid-ossp";

create table artists (
    artist_id integer primary key generated always as identity,
    handle varchar(128) not null unique,
    name varchar(128),
    bio text,
    site varchar(256)
);

create table artworks (
    artwork_id integer primary key generated always as identity,
    title varchar(256),
    description text,
    url_source varchar(256),
    created_at timestamp not null default current_timestamp,
    artist_id integer not null,
    comment_number integer not null default 0,
    foreign key (artist_id) references artists(artist_id)
);

create table comments (
    comment_id integer unique not null,
    text text not null,
    artwork_id integer not null,
    created_at timestamp not null default current_timestamp,
    primary key (comment_id, artwork_id),
    foreign key (artwork_id) references artworks(artwork_id)
);

create table comment_relations (
    artwork_id integer,
    foreign key (artwork_id) references artworks(artwork_id),
    in_reply_to_id integer,
    foreign key (in_reply_to_id) references comments(comment_id),
    comment_id integer,
    foreign key (comment_id) references comments(comment_id),
    primary key (artwork_id, in_reply_to_id, comment_id)
);

create table artwork_files (
    file_id uuid primary key default gen_random_uuid(),
    alt_text text,
    extension varchar(16),
    artwork_id integer,
    foreign key (artwork_id) references artworks(artwork_id)
);