DROP TABLE IF EXISTS vote_member;
DROP TABLE IF EXISTS member;
DROP TABLE IF EXISTS vote;
DROP TABLE IF EXISTS poll;
DROP TABLE IF EXISTS guild;

CREATE TABLE guild
(
    guild_id VARCHAR(255),
    PRIMARY KEY (guild_id)
);

CREATE TABLE poll
(
    poll_id  UUID,
    creator  VARCHAR(255),
    guild_id VARCHAR(255),
    question VARCHAR(255),
    opened   BOOLEAN,
    PRIMARY KEY (poll_id),
    FOREIGN KEY (guild_id) REFERENCES guild (guild_id)
);

CREATE TABLE vote
(
    poll_id     UUID,
    vote_id     VARCHAR(255),
    vote_option VARCHAR(255),
    PRIMARY KEY (vote_id),
    FOREIGN KEY (poll_id) REFERENCES poll (poll_id)
);

CREATE TABLE member
(
    user_id   VARCHAR(255),
    guild_id  VARCHAR(255),
    username  VARCHAR(255),
    points    bigint DEFAULT 50 NOT NULL CHECK (points >= 50),
    bet_value bigint DEFAULT 50,
    PRIMARY KEY (user_id),
    FOREIGN KEY (guild_id) REFERENCES guild (guild_id)
);

CREATE TABLE vote_member
(
    user_id   VARCHAR(255),
    guild_id  VARCHAR(255),
    vote_id   VARCHAR(255),
    poll_id   UUID,
    bet_value bigint DEFAULT 50,
    FOREIGN KEY (guild_id) REFERENCES guild (guild_id),
    FOREIGN KEY (user_id) REFERENCES member (user_id),
    FOREIGN KEY (vote_id) REFERENCES vote (vote_id),
    FOREIGN KEY (poll_id) REFERENCES poll (poll_id)
);