// Help System
CREATE TABLE IF NOT EXISTS help_channel_thanks
(
	id             BIGINT PRIMARY KEY AUTO_INCREMENT,
	reservation_id BIGINT       NOT NULL,
	user_id        BIGINT       NOT NULL,
	channel_id     BIGINT       NOT NULL,
	thanked_at     TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
	helper_id      BIGINT       NOT NULL,
	CONSTRAINT help_channel_thanks_unique UNIQUE (reservation_id, helper_id)
);

CREATE TABLE IF NOT EXISTS help_account
(
	user_id    BIGINT PRIMARY KEY,
	experience DOUBLE NOT NULL
);

CREATE TABLE IF NOT EXISTS help_transaction
(
	id          BIGINT PRIMARY KEY AUTO_INCREMENT,
	recipient   BIGINT       NOT NULL,
	created_at  TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
	weight      DOUBLE       NOT NULL,
	channel     BIGINT       DEFAULT -1
);

// Question of the Week
CREATE TABLE IF NOT EXISTS qotw_question
(
	id              BIGINT PRIMARY KEY AUTO_INCREMENT,
	created_at      TIMESTAMP(0)  NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
	guild_id        BIGINT        NOT NULL,
	created_by      BIGINT        NOT NULL,
	"TEXT"          VARCHAR(1024) NOT NULL,
	used            BOOLEAN       NOT NULL DEFAULT FALSE,
	question_number INTEGER       NULL     DEFAULT NULL,
	priority        INTEGER       NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS qotw_points
(
	user_id BIGINT,
	obtained_at DATE DEFAULT CURRENT_TIMESTAMP(0),
	points  BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (user_id, obtained_at)
);

// Warn
CREATE TABLE IF NOT EXISTS warn
(
	id              BIGINT PRIMARY KEY AUTO_INCREMENT,
	user_id         BIGINT        NOT NULL,
	warned_by       BIGINT        NOT NULL,
	created_at      TIMESTAMP(0)  NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
	severity        VARCHAR(32)   NOT NULL,
	severity_weight INT           NOT NULL,
	reason          VARCHAR(1024) NOT NULL,
	discarded       BOOL          NOT NULL DEFAULT FALSE
);

// Custom Tags
CREATE TABLE IF NOT EXISTS custom_tags
(
	id         BIGINT PRIMARY KEY AUTO_INCREMENT,
	guild_id   BIGINT        NOT NULL,
	created_by BIGINT        NOT NULL,
	name       VARCHAR(64)   NOT NULL,
	response   VARCHAR(2048) NOT NULL,
	reply      BOOL          NOT NULL DEFAULT TRUE,
	embed      BOOL          NOT NULL DEFAULT TRUE
);

// Starboard
CREATE TABLE IF NOT EXISTS starboard
(
	original_message_id  BIGINT PRIMARY KEY,
	guild_id             BIGINT NOT NULL,
	channel_id           BIGINT NOT NULL,
	author_id            BIGINT NOT NULL,
	starboard_message_id BIGINT NOT NULL
);

// Message Cache
CREATE TABLE IF NOT EXISTS message_cache
(
	message_id      BIGINT PRIMARY KEY,
	author_id       BIGINT        NOT NULL,
	message_content VARCHAR(4000) NOT NULL,
	channel_id      BIGINT        DEFAULT -1
);

CREATE TABLE IF NOT EXISTS message_cache_attachments (
	message_id     		BIGINT NOT NULL,
	attachment_index	INT NOT NULL,
	link				VARCHAR(511),
	PRIMARY KEY(message_id, attachment_index)
);

// User Preferences
CREATE TABLE IF NOT EXISTS user_preferences
(
	user_id BIGINT  NOT NULL,
	ordinal INTEGER NOT NULL,
	state   VARCHAR NOT NULL DEFAULT '',
	PRIMARY KEY (user_id, ordinal)
);

//QOTW champion
CREATE TABLE qotw_champion (
        guild_id        BIGINT NOT NULL,
        user_id         BIGINT NOT NULL,
        PRIMARY KEY(guild_id, user_id)
);

// staff activity
CREATE TABLE staff_activity_messages (
	guild_id	BIGINT NOT NULL,
	user_id		BIGINT NOT NULL,
	message_id	BIGINT NOT NULL,
	PRIMARY KEY(guild_id, user_id)
);

// custom voice channels
CREATE TABLE custom_vc (
	channel_id	BIGINT NOT NULL PRIMARY KEY,
	owner_id	BIGINT NOT NULL
);

// forms
CREATE TABLE forms (
    form_id BIGINT NOT NULL AUTO_INCREMENT,
    title VARCHAR NOT NULL,
    submit_message VARCHAR DEFAULT NULL,
    submit_channel BIGINT NOT NULL,
    message_id BIGINT DEFAULT NULL,
    message_channel BIGINT DEFAULT NULL,
    expiration TIMESTAMP DEFAULT NULL,
    closed BOOLEAN NOT NULL DEFAULT FALSE,
    onetime BOOLEAN NOT NULL DEFAULT FALSE,
    PRIMARY KEY (form_id)
);

CREATE TABLE form_fields (
    id BIGINT NOT NULL AUTO_INCREMENT,
    form_id BIGINT NOT NULL,
    label VARCHAR NOT NULL,
    min INTEGER DEFAULT 0 NOT NULL,
    max INTEGER DEFAULT 16 NOT NULL,
    placeholder VARCHAR,
    "required" BOOLEAN DEFAULT FALSE NOT NULL,
    "style" ENUM('SHORT', 'PARAGRAPH') DEFAULT 'SHORT' NOT NULL,
    initial VARCHAR DEFAULT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (form_id) REFERENCES forms(form_id) ON DELETE CASCADE ON UPDATE RESTRICT
);

CREATE TABLE form_submissions (
    message_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    form_id BIGINT NOT NULL,
    user_name VARCHAR NOT NULL,
    PRIMARY KEY (message_id),
    FOREIGN KEY (form_id) REFERENCES FORMS(form_id) ON DELETE CASCADE ON UPDATE RESTRICT
);

CREATE INDEX FORM_SUBMISSIONS_USER_ID_IDX ON form_submissions (user_id,form_id);