create table users
(
    id              serial
        primary key,
    username        varchar(50)                         not null
        unique,
    email           varchar(100)                        not null
        unique,
    hashed_password text                                not null,
    roles           text[],
    created_at      timestamp default CURRENT_TIMESTAMP not null,
    updated_at      timestamp default CURRENT_TIMESTAMP not null
);

alter table users
    owner to osirose;

create table refresh_tokens
(
    id         serial
        primary key,
    user_id    integer   not null
        references users
            on delete cascade,
    token      text      not null,
    created_at timestamp default CURRENT_TIMESTAMP,
    expires_at timestamp not null
);

alter table refresh_tokens
    owner to osirose;

create table characters
(
    id         serial
        primary key,
    user_id    integer     not null
        references users
            on delete cascade,
    is_active     boolean   default true,
    name       varchar(50) not null,
    level      smallint  default 1,
    experience bigint    default 0,
    inventory  jsonb     default '{}'::jsonb,
    stats      jsonb     default '{}'::jsonb,
    looks      jsonb     default '{}'::jsonb,
    position   jsonb     default '{}'::jsonb,
    created_at timestamp default CURRENT_TIMESTAMP,
    updated_at timestamp default CURRENT_TIMESTAMP,
    deleted_at timestamp
);

alter table characters
    owner to osirose;

create table items
(
    id          serial
        primary key,
    name        varchar(50) not null,
    description text,
    rarity      varchar(20),
    stats       jsonb default '{}'::jsonb
);

alter table items
    owner to osirose;

create table character_items
(
    id           serial
        primary key,
    character_id integer not null
        references characters
            on delete cascade,
    item_id      integer not null
        references items
            on delete cascade,
    quantity     integer default 1
);

alter table character_items
    owner to osirose;

create table events
(
    id         serial
        primary key,
    event_type varchar(50) not null,
    event_data jsonb       not null,
    created_at timestamp default CURRENT_TIMESTAMP
);

alter table events
    owner to osirose;

create table password_resets
(
    id          serial
        primary key,
    email       text      not null,
    reset_token text      not null,
    expires_at  timestamp not null
);

alter table password_resets
    owner to osirose;