gistfile1.txt

-- migrate:down

drop table if exists users;

drop table if exists slot;

drop table if exists verify_token;

drop table if exists winner;

drop table if exists winners_reviews;

drop table if exists winner_review_images;

drop extension if exists citext;

create type user_role as enum('user', 'moderator', 'admin');

-- migrate:up

create extension if not exists citext ;

-- migrate:up

create table session (
    sid text primary key not null,
    sess json not null,
    expire timestamptz not null
);

create index idx_session_expire on session(expire);

-- migrate:up

create table users (
    id int primary key generated always as identity,
    first_name text not null,
    last_name text not null,
    email citext unique not null,
    username citext unique not null,
    phone_number text unique,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null,
    password text not null,
    profile_image text not null,
    profile_image_id text not null,
    verified boolean not null default false,
    google_user boolean not null default false
);

-- migrate:up

create table slot (
    id int primary key generated always as identity,
    slot_key text unique,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null,
    week_of_year integer not null,
    weekly_amount_spent integer not null,
    weekly_slot_own integer not null,
    year integer not null,
    user_id integer not null references users(id) on delete cascade on update cascade
);

-- migrate:up

create table verify_token (
    id int primary key generated always as identity,
    token text unique not null,
    user_id integer not null references users(id) on delete cascade on update cascade,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null
);

-- migrate:up

create table winner (
    id int primary key generated always as identity,
    user_id integer not null references users(id) on delete cascade on update cascade,
    profile_image text not null,
    profile_image_id text not null,
    winner_first_name text not null,
    winner_last_name text not null,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null,
    winner_username citext not null references users(username) on delete cascade on update cascade,
    amount_won integer not null
);

-- migrate:up

create table winners_review (
    id int primary key generated always as identity,
    review_title text not null,
    review_body text not null,
    winner_id integer unique not null references winner(id) on delete cascade on update cascade,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null,
    user_id integer not null references users
);

-- migrate:up

create table winner_review_images (
    id int primary key generated always as identity,
    review_image text [],
    review_image_id text [],
    created_at timestamptz not null default now(),
    updated_at timestamptz not null,
    winners_review_id integer unique not null references winners_review(id) on delete cascade on update cascade
);