12066befa8
- Introduced new models in the Prisma schema for statistics, social feeds, scheduled messages, and guild backups, enhancing the bot's functionality. - Implemented command modules for managing stats channels, feeds, scheduling messages, and creating/restoring backups, improving user engagement and server management. - Updated job handling to include dedicated workers for stats updates, feed polling, scheduling, and backup restoration, enhancing automation. - Enhanced command localization for new features in both German and English. - Documented the new features and their setup processes in PHASE-TRACKING.md for clarity on implementation steps.
117 lines
5.0 KiB
SQL
117 lines
5.0 KiB
SQL
CREATE TABLE "StatsConfig" (
|
|
"id" TEXT NOT NULL,
|
|
"guildId" TEXT NOT NULL,
|
|
"enabled" BOOLEAN NOT NULL DEFAULT false,
|
|
"membersChannelId" TEXT,
|
|
"onlineChannelId" TEXT,
|
|
"boostsChannelId" TEXT,
|
|
"membersTemplate" TEXT NOT NULL DEFAULT 'Members: {count}',
|
|
"onlineTemplate" TEXT NOT NULL DEFAULT 'Online: {count}',
|
|
"boostsTemplate" TEXT NOT NULL DEFAULT 'Boosts: {count}',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "StatsConfig_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "InviteStat" (
|
|
"id" TEXT NOT NULL,
|
|
"guildId" TEXT NOT NULL,
|
|
"inviterId" TEXT NOT NULL,
|
|
"code" TEXT,
|
|
"joins" INTEGER NOT NULL DEFAULT 0,
|
|
"leaves" INTEGER NOT NULL DEFAULT 0,
|
|
"fakes" INTEGER NOT NULL DEFAULT 0,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "InviteStat_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "InviteJoin" (
|
|
"id" TEXT NOT NULL,
|
|
"guildId" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"inviterId" TEXT,
|
|
"code" TEXT,
|
|
"isFake" BOOLEAN NOT NULL DEFAULT false,
|
|
"leftAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "InviteJoin_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "ActivityStat" (
|
|
"id" TEXT NOT NULL,
|
|
"guildId" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"channelId" TEXT,
|
|
"messageCount" INTEGER NOT NULL DEFAULT 0,
|
|
"voiceMinutes" INTEGER NOT NULL DEFAULT 0,
|
|
"day" TIMESTAMP(3) NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "ActivityStat_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "SocialFeed" (
|
|
"id" TEXT NOT NULL,
|
|
"guildId" TEXT NOT NULL,
|
|
"type" TEXT NOT NULL,
|
|
"sourceId" TEXT NOT NULL,
|
|
"channelId" TEXT NOT NULL,
|
|
"rolePingId" TEXT,
|
|
"template" TEXT,
|
|
"lastItemId" TEXT,
|
|
"enabled" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "SocialFeed_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "ScheduledMessage" (
|
|
"id" TEXT NOT NULL,
|
|
"guildId" TEXT NOT NULL,
|
|
"channelId" TEXT NOT NULL,
|
|
"creatorId" TEXT NOT NULL,
|
|
"content" TEXT,
|
|
"embed" JSONB,
|
|
"rolePingId" TEXT,
|
|
"cron" TEXT,
|
|
"runAt" TIMESTAMP(3),
|
|
"jobId" TEXT,
|
|
"enabled" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "ScheduledMessage_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "GuildBackup" (
|
|
"id" TEXT NOT NULL,
|
|
"guildId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"createdById" TEXT NOT NULL,
|
|
"payload" JSONB NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "GuildBackup_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE UNIQUE INDEX "StatsConfig_guildId_key" ON "StatsConfig"("guildId");
|
|
CREATE UNIQUE INDEX "InviteStat_guildId_inviterId_key" ON "InviteStat"("guildId", "inviterId");
|
|
CREATE INDEX "InviteStat_guildId_joins_idx" ON "InviteStat"("guildId", "joins");
|
|
CREATE UNIQUE INDEX "InviteJoin_guildId_userId_key" ON "InviteJoin"("guildId", "userId");
|
|
CREATE INDEX "InviteJoin_guildId_inviterId_idx" ON "InviteJoin"("guildId", "inviterId");
|
|
CREATE UNIQUE INDEX "ActivityStat_guildId_userId_channelId_day_key" ON "ActivityStat"("guildId", "userId", "channelId", "day");
|
|
CREATE INDEX "ActivityStat_guildId_day_idx" ON "ActivityStat"("guildId", "day");
|
|
CREATE INDEX "SocialFeed_guildId_type_idx" ON "SocialFeed"("guildId", "type");
|
|
CREATE INDEX "SocialFeed_enabled_idx" ON "SocialFeed"("enabled");
|
|
CREATE INDEX "ScheduledMessage_guildId_enabled_idx" ON "ScheduledMessage"("guildId", "enabled");
|
|
CREATE INDEX "GuildBackup_guildId_createdAt_idx" ON "GuildBackup"("guildId", "createdAt");
|
|
|
|
ALTER TABLE "StatsConfig" ADD CONSTRAINT "StatsConfig_guildId_fkey" FOREIGN KEY ("guildId") REFERENCES "Guild"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "InviteStat" ADD CONSTRAINT "InviteStat_guildId_fkey" FOREIGN KEY ("guildId") REFERENCES "Guild"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "InviteJoin" ADD CONSTRAINT "InviteJoin_guildId_fkey" FOREIGN KEY ("guildId") REFERENCES "Guild"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "ActivityStat" ADD CONSTRAINT "ActivityStat_guildId_fkey" FOREIGN KEY ("guildId") REFERENCES "Guild"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "SocialFeed" ADD CONSTRAINT "SocialFeed_guildId_fkey" FOREIGN KEY ("guildId") REFERENCES "Guild"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "ScheduledMessage" ADD CONSTRAINT "ScheduledMessage_guildId_fkey" FOREIGN KEY ("guildId") REFERENCES "Guild"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "GuildBackup" ADD CONSTRAINT "GuildBackup_guildId_fkey" FOREIGN KEY ("guildId") REFERENCES "Guild"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|