migrations.sql

-- 1 up
create sequence "uid_seq" start 100000;

create table "users" (
    "id" integer not null default nextval('uid_seq'),
    "uuid" uuid not null,
    "octid" integer not null,
    "username" text,
    "displayname" text,
    primary key("id"),
    unique("uuid"),
    unique("octid")
);

create table "groups" (
    "id" integer not null default nextval('uid_seq'),
    "octid" integer not null,
    "name" text,
    primary key("id"),
    unique("octid")
);

create table "meets" (
    "id" integer not null default nextval('uid_seq'),
    "uuid" uuid not null, -- unique string
    "owner_id" integer not null,
    "deleted" timestamp(0),
    "name" text not null,
    "description" text,
    "properties" text,
    primary key("id"),
    unique("uuid"),
    foreign key ("owner_id") references "users" ("id") on update cascade on delete restrict
);

create table "meets_groups" (
    "meet_id" integer not null,
    "group_id" integer not null,
    primary key("meet_id", "group_id"),
    foreign key ("meet_id") references "meets" ("id") on update cascade on delete cascade,
    foreign key ("group_id") references "groups" ("id") on update cascade on delete cascade
);

-- 2 up

create view "meets_groups_view" as
select "meets_groups".*,
    "groups"."name" as "group_name"
from "meets_groups"
join "groups" on ("groups"."id" = "meets_groups"."group_id")
;

-- 3 up
alter table "groups" add "permissions" text;

-- 4 up
create table "moderators" (
    "meet_id" integer not null,
    "octid" integer not null,
    "name" text,
    primary key("meet_id", "octid"),
    foreign key ("meet_id") references "meets" ("id") on update cascade on delete cascade
);

-- 5 up
create table "meets_users" (
    "id" integer not null default nextval('uid_seq'),
    "meet_id" integer not null,
    "user_id" integer not null,
    "is_moderator" bool not null default 'f',
    primary key("id"),
    unique("meet_id", "user_id", "is_moderator"),
    foreign key ("meet_id") references "meets" ("id") on update cascade on delete cascade,
    foreign key ("user_id") references "users" ("id") on update cascade on delete cascade
);

create view "meets_users_view" as
select "meets_users".*,
    "users"."displayname" as "user_name"
from "meets_users"
join "users" on ("users"."id" = "meets_users"."user_id")
;

-- 6 up
drop table "moderators";
alter table "users" drop column "octid";