gistfile1.txt

-- migrate:up

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

create index idx_session_expire on session(expire);

-- migrate:down

-- migrate:up

create table slot (
    id int primary key generated always as identity,
    user_id integer not null references users(id) on delete cascade on update cascade,
    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
);

-- migrate:down

drop table slot;

-- 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,
    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,
    username citext unique not null,
    google_user boolean not null default false
);

-- migrate:down

drop table users;

-- 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:down

drop table verify_token;

-- 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 text not null references users(username) on delete cascade on update cascade,
    amount_won integer not null
);

-- migrate:down

drop table winner;

-- 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:down

drop table winner_review;

-- migrate:up

create table winners_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,
    winner_review_id integer unique not null references winners_review(id) on delete cascade on update cascade
);

-- migrate:down

drop table winner_review_images;