50 lines
2.1 KiB
SQL
50 lines
2.1 KiB
SQL
CREATE TABLE IF NOT EXISTS users (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
discord_id VARCHAR(32) NOT NULL UNIQUE,
|
|
username VARCHAR(100) NOT NULL,
|
|
avatar VARCHAR(128) NOT NULL DEFAULT '',
|
|
balance INT NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS promo_codes (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
code VARCHAR(32) NOT NULL UNIQUE,
|
|
bonus_type VARCHAR(16) NOT NULL DEFAULT 'fixed',
|
|
bonus_value INT NOT NULL DEFAULT 0,
|
|
max_uses INT NULL,
|
|
expires_at DATETIME NULL,
|
|
active TINYINT(1) NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS deposits (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
user_id BIGINT UNSIGNED NOT NULL,
|
|
amount INT NOT NULL,
|
|
bonus_amount INT NOT NULL DEFAULT 0,
|
|
promo_code_id BIGINT UNSIGNED NULL,
|
|
stripe_payment_intent_id VARCHAR(128) NOT NULL,
|
|
status VARCHAR(32) NOT NULL DEFAULT 'created',
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_deposits_user_id FOREIGN KEY (user_id) REFERENCES users(id),
|
|
CONSTRAINT fk_deposits_promo_code_id FOREIGN KEY (promo_code_id) REFERENCES promo_codes(id)
|
|
);
|
|
|
|
CREATE INDEX idx_deposits_stripe ON deposits (stripe_payment_intent_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS promo_redemptions (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
user_id BIGINT UNSIGNED NOT NULL,
|
|
promo_code_id BIGINT UNSIGNED NOT NULL,
|
|
deposit_id BIGINT UNSIGNED NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_redemptions_user_id FOREIGN KEY (user_id) REFERENCES users(id),
|
|
CONSTRAINT fk_redemptions_promo_code_id FOREIGN KEY (promo_code_id) REFERENCES promo_codes(id),
|
|
CONSTRAINT fk_redemptions_deposit_id FOREIGN KEY (deposit_id) REFERENCES deposits(id),
|
|
UNIQUE KEY uniq_user_promo (user_id, promo_code_id)
|
|
);
|