path: root/migrations/20231003193749_pinussy.sql

create type privacy as enum ('private', 'unlisted', 'public');
create type file_type as enum ('image', 'video', 'audio', 'text', 'document', 'site', 'other');
create extension if not exists "uuid-ossp";

create table users (
    id uuid primary key default gen_random_uuid(),
    username varchar(32) not null,
    unique(username),
    password varchar(128) not null,
    email varchar(128),
    bio text,
    site varchar(128),
    privacy privacy not null default 'public',
    admin boolean not null default true
);

-- create table sessions (
--     id varchar(128) primary key,
--     expires timestamp with time zone,
--     user_id integer not null,
--     foreign key (user_id) references users(id)
-- );

create table boards (
    id uuid primary key default gen_random_uuid(),
    idname varchar(256),
    name varchar(256),
    description text,
    privacy privacy
);

create table board_ownership (
    board_id uuid not null,
    user_id uuid not null,
    foreign key (board_id) references boards(id),
    foreign key (user_id) references users(id),
    primary key (board_id, user_id)
);

create table pins (
    id uuid primary key default gen_random_uuid(),
    idname varchar(256),
    subject varchar(256),
    notes text,
    privacy privacy
);

create table pin_ownership (
    pin_id uuid not null,
    user_id uuid not null,
    foreign key (pin_id) references pins(id),
    foreign key (user_id) references users(id),
    primary key (pin_id, user_id)
);

create table pins_boards (
    pin_id uuid not null,
    board_id uuid not null,
    foreign key (pin_id) references pins(id),
    foreign key (board_id) references boards(id),
    primary key (pin_id, board_id)
);

create table sources (
    id uuid primary key default gen_random_uuid(),
    idname varchar(256),
    title varchar(256),
    author varchar(256),
    url varchar(256),
    iban varchar(13),
    page varchar(256),
    location varchar(256)
);

create table pin_sources (
    source_id uuid not null,
    pin_id uuid not null,
    foreign key (source_id) references sources(id),
    foreign key (pin_id) references pins(id),
    primary key (source_id, pin_id)
);

create table files (
    id uuid primary key default gen_random_uuid(),
    idname varchar(256),
    thumbnail varchar(256),
    path varchar(256) not null,
    title varchar(256),
    type file_type,
    alt_text text,
    pin_id uuid not null,
    foreign key (pin_id) references pins(id)
);

create table tags (
    tag varchar(128) not null primary key
);

create table tags_pins (
    tag varchar(128) not null,
    pin_id uuid not null,
    foreign key (tag) references tags(tag),
    foreign key (pin_id) references pins(id),
    primary key (tag, pin_id)
);