create schema picrinth;

create table picrinth.users (
	id serial not null,
	username text not null,
	"password" text not null,
	"role" text not null default 'user',
	"disabled" bool not null default false,
	last_seen_at timestamp with time zone null,
	created_at timestamp with time zone default now(),
	constraint username_unique unique (username)
);

create table picrinth.groups (
	id serial not null,
	groupname text not null,
	invite_code text not null,
	author text null,
	allow_skips bool not null default true,
	feed_interval_minutes integer null default 1440,
	last_feed_id int null,
	created_at timestamp with time zone default now(),
	constraint groupname_unique unique (groupname),
	constraint invite_code_unique unique (invite_code)
);

create table picrinth.memberships (
    username text,
    groupname text,
    joined_at timestamp with time zone null,
    primary key (username, groupname),
    foreign key (username) references picrinth.users (username) on delete cascade on update cascade,
    foreign key (groupname) references picrinth.groups (groupname) on delete cascade on update cascade
);

create table picrinth.pictures (
	id serial not null,
	source text not null,
	external_id text not null,
	"url" text not null,
	metadata jsonb null,
	created_at timestamp with time zone default now(),
	constraint pictures_pkey primary key (id),
	constraint url_unique unique (url)
);

create table picrinth.feeds (
	id serial not null,
	groupname text not null,
	image_ids integer[] not null,
	created_at timestamp with time zone default now(),
	constraint feeds_pkey primary key (id),
	foreign key (groupname) references picrinth.groups (groupname) on delete cascade on update cascade,
    constraint unique_feed_per_timestamp_per_group unique (groupname, created_at)
);

create table picrinth.swipes (
	id serial not null,
	username text not null,
	feed_id integer not null,
	picture_id integer not null,
	"value" smallint not null,
	created_at timestamp with time zone default now(),
	primary key (id),
    foreign key (username) references picrinth.users (username) on delete cascade on update cascade,
	foreign key (feed_id) references picrinth.feeds (id) on delete cascade on update cascade,
	foreign key (picture_id) references picrinth.pictures (id) on delete cascade on update cascade,
	constraint swipes_unique unique (username, feed_id, picture_id)
);

create table picrinth.accounts (
	groupname text not null,
	author text null,
	platform text not null,
	"login" text not null,
	"password" text not null,
	metadata jsonb null,
	created_at timestamp with time zone default now(),
    foreign key (groupname) references picrinth.groups (groupname) on delete cascade on update cascade,
    foreign key (author) references picrinth.groups (author) on delete cascade on update cascade,
	constraint unique_account_for_group_per_platform unique (groupname, platform)
);