fa5910ec43
- Added new models for leveling and economy functionalities in the Prisma schema, including LevelingConfig, MemberLevel, LevelReward, EconomyConfig, MemberEconomy, ShopItem, and InventoryItem. - Introduced commands for managing XP, economy transactions, and shop interactions, enhancing user engagement. - Updated job handling to include reminders and poll closing functionalities. - Enhanced localization support for new commands and features in both German and English. - Improved the bot's job processing capabilities with a dedicated reminder worker for scheduled tasks.
181 lines
7.9 KiB
SQL
181 lines
7.9 KiB
SQL
-- CreateTable
|
|
CREATE TABLE "LevelingConfig" (
|
|
"id" TEXT NOT NULL,
|
|
"guildId" TEXT NOT NULL,
|
|
"enabled" BOOLEAN NOT NULL DEFAULT true,
|
|
"textXpMin" INTEGER NOT NULL DEFAULT 15,
|
|
"textXpMax" INTEGER NOT NULL DEFAULT 25,
|
|
"textCooldownSeconds" INTEGER NOT NULL DEFAULT 60,
|
|
"voiceXpPerMinute" INTEGER NOT NULL DEFAULT 10,
|
|
"noXpChannelIds" TEXT[] DEFAULT ARRAY[]::TEXT[],
|
|
"noXpRoleIds" TEXT[] DEFAULT ARRAY[]::TEXT[],
|
|
"roleMultipliers" JSONB,
|
|
"channelMultipliers" JSONB,
|
|
"levelUpMode" TEXT NOT NULL DEFAULT 'CHANNEL',
|
|
"levelUpChannelId" TEXT,
|
|
"levelUpMessage" TEXT,
|
|
"stackRewards" BOOLEAN NOT NULL DEFAULT true,
|
|
"cardAccentColor" TEXT NOT NULL DEFAULT '#6366F1',
|
|
"cardBackgroundColor" TEXT NOT NULL DEFAULT '#111827',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "LevelingConfig_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "MemberLevel" (
|
|
"id" TEXT NOT NULL,
|
|
"guildId" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"xp" INTEGER NOT NULL DEFAULT 0,
|
|
"level" INTEGER NOT NULL DEFAULT 0,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "MemberLevel_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "LevelReward" (
|
|
"id" TEXT NOT NULL,
|
|
"guildId" TEXT NOT NULL,
|
|
"level" INTEGER NOT NULL,
|
|
"roleId" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "LevelReward_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "EconomyConfig" (
|
|
"id" TEXT NOT NULL,
|
|
"guildId" TEXT NOT NULL,
|
|
"enabled" BOOLEAN NOT NULL DEFAULT true,
|
|
"currencyName" TEXT NOT NULL DEFAULT 'Coins',
|
|
"currencySymbol" TEXT NOT NULL DEFAULT '🪙',
|
|
"dailyAmount" INTEGER NOT NULL DEFAULT 200,
|
|
"weeklyAmount" INTEGER NOT NULL DEFAULT 1000,
|
|
"workMin" INTEGER NOT NULL DEFAULT 50,
|
|
"workMax" INTEGER NOT NULL DEFAULT 150,
|
|
"workCooldownSeconds" INTEGER NOT NULL DEFAULT 3600,
|
|
"startingBalance" INTEGER NOT NULL DEFAULT 0,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "EconomyConfig_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "MemberEconomy" (
|
|
"id" TEXT NOT NULL,
|
|
"guildId" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"balance" INTEGER NOT NULL DEFAULT 0,
|
|
"lastDailyAt" TIMESTAMP(3),
|
|
"lastWeeklyAt" TIMESTAMP(3),
|
|
"lastWorkAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "MemberEconomy_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "ShopItem" (
|
|
"id" TEXT NOT NULL,
|
|
"guildId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"price" INTEGER NOT NULL,
|
|
"roleId" TEXT,
|
|
"stock" INTEGER,
|
|
"enabled" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "ShopItem_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "InventoryItem" (
|
|
"id" TEXT NOT NULL,
|
|
"guildId" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"itemId" TEXT NOT NULL,
|
|
"quantity" INTEGER NOT NULL DEFAULT 1,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "InventoryItem_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "Poll" (
|
|
"id" TEXT NOT NULL,
|
|
"guildId" TEXT NOT NULL,
|
|
"channelId" TEXT NOT NULL,
|
|
"messageId" TEXT,
|
|
"creatorId" TEXT NOT NULL,
|
|
"question" TEXT NOT NULL,
|
|
"options" JSONB NOT NULL,
|
|
"multiSelect" BOOLEAN NOT NULL DEFAULT false,
|
|
"anonymous" BOOLEAN NOT NULL DEFAULT false,
|
|
"endsAt" TIMESTAMP(3),
|
|
"closedAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "Poll_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "PollVote" (
|
|
"id" TEXT NOT NULL,
|
|
"pollId" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"optionIndex" INTEGER NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "PollVote_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "Reminder" (
|
|
"id" TEXT NOT NULL,
|
|
"guildId" TEXT,
|
|
"userId" TEXT NOT NULL,
|
|
"channelId" TEXT NOT NULL,
|
|
"content" TEXT NOT NULL,
|
|
"remindAt" TIMESTAMP(3) NOT NULL,
|
|
"recurringCron" TEXT,
|
|
"jobId" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "Reminder_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "AfkStatus" (
|
|
"id" TEXT NOT NULL,
|
|
"guildId" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"reason" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "AfkStatus_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE UNIQUE INDEX "LevelingConfig_guildId_key" ON "LevelingConfig"("guildId");
|
|
CREATE UNIQUE INDEX "MemberLevel_guildId_userId_key" ON "MemberLevel"("guildId", "userId");
|
|
CREATE INDEX "MemberLevel_guildId_xp_idx" ON "MemberLevel"("guildId", "xp");
|
|
CREATE UNIQUE INDEX "LevelReward_guildId_level_key" ON "LevelReward"("guildId", "level");
|
|
CREATE INDEX "LevelReward_guildId_idx" ON "LevelReward"("guildId");
|
|
CREATE UNIQUE INDEX "EconomyConfig_guildId_key" ON "EconomyConfig"("guildId");
|
|
CREATE UNIQUE INDEX "MemberEconomy_guildId_userId_key" ON "MemberEconomy"("guildId", "userId");
|
|
CREATE INDEX "MemberEconomy_guildId_balance_idx" ON "MemberEconomy"("guildId", "balance");
|
|
CREATE INDEX "ShopItem_guildId_enabled_idx" ON "ShopItem"("guildId", "enabled");
|
|
CREATE UNIQUE INDEX "InventoryItem_guildId_userId_itemId_key" ON "InventoryItem"("guildId", "userId", "itemId");
|
|
CREATE INDEX "InventoryItem_guildId_userId_idx" ON "InventoryItem"("guildId", "userId");
|
|
CREATE INDEX "Poll_guildId_idx" ON "Poll"("guildId");
|
|
CREATE UNIQUE INDEX "PollVote_pollId_userId_optionIndex_key" ON "PollVote"("pollId", "userId", "optionIndex");
|
|
CREATE INDEX "PollVote_pollId_idx" ON "PollVote"("pollId");
|
|
CREATE INDEX "Reminder_userId_idx" ON "Reminder"("userId");
|
|
CREATE INDEX "Reminder_remindAt_idx" ON "Reminder"("remindAt");
|
|
CREATE UNIQUE INDEX "AfkStatus_guildId_userId_key" ON "AfkStatus"("guildId", "userId");
|
|
|
|
ALTER TABLE "LevelingConfig" ADD CONSTRAINT "LevelingConfig_guildId_fkey" FOREIGN KEY ("guildId") REFERENCES "Guild"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "MemberLevel" ADD CONSTRAINT "MemberLevel_guildId_fkey" FOREIGN KEY ("guildId") REFERENCES "Guild"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "LevelReward" ADD CONSTRAINT "LevelReward_guildId_fkey" FOREIGN KEY ("guildId") REFERENCES "Guild"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "EconomyConfig" ADD CONSTRAINT "EconomyConfig_guildId_fkey" FOREIGN KEY ("guildId") REFERENCES "Guild"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "MemberEconomy" ADD CONSTRAINT "MemberEconomy_guildId_fkey" FOREIGN KEY ("guildId") REFERENCES "Guild"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "ShopItem" ADD CONSTRAINT "ShopItem_guildId_fkey" FOREIGN KEY ("guildId") REFERENCES "Guild"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "InventoryItem" ADD CONSTRAINT "InventoryItem_guildId_fkey" FOREIGN KEY ("guildId") REFERENCES "Guild"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "InventoryItem" ADD CONSTRAINT "InventoryItem_itemId_fkey" FOREIGN KEY ("itemId") REFERENCES "ShopItem"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "Poll" ADD CONSTRAINT "Poll_guildId_fkey" FOREIGN KEY ("guildId") REFERENCES "Guild"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "PollVote" ADD CONSTRAINT "PollVote_pollId_fkey" FOREIGN KEY ("pollId") REFERENCES "Poll"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "Reminder" ADD CONSTRAINT "Reminder_guildId_fkey" FOREIGN KEY ("guildId") REFERENCES "Guild"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "AfkStatus" ADD CONSTRAINT "AfkStatus_guildId_fkey" FOREIGN KEY ("guildId") REFERENCES "Guild"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|