YANG JIANKUAN
394b643304
- 废弃 RedemptionRule（集 N 换 1），新增 Prize 表与 Stamp 1:1 关联 - Redemption 记录直接绑定到 stampId + prizeId + prizeName 快照 - 兑换事务用 updateMany + stock>0 条件作乐观锁 - 兑换后保留 Collection 记录，图章持续彩色点亮并标记"已兑换" - 用户端入口改为点击已收集图章弹出兑换，库存为 0 时按钮禁用 - 管理后台删除 /admin/rules，奖品编辑嵌入 StampForm Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
61 lines
2.3 KiB
SQL
61 lines
2.3 KiB
SQL
-- CreateTable
|
|
CREATE TABLE "Prize" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"stampId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"stock" INTEGER NOT NULL DEFAULT 0,
|
|
"enabled" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL,
|
|
CONSTRAINT "Prize_stampId_fkey" FOREIGN KEY ("stampId") REFERENCES "Stamp" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Prize_stampId_key" ON "Prize"("stampId");
|
|
|
|
-- Backfill: create a default Prize for every existing Stamp
|
|
INSERT INTO "Prize" ("id", "stampId", "name", "description", "stock", "enabled", "createdAt", "updatedAt")
|
|
SELECT
|
|
lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-' || lower(hex(randomblob(2))) || '-' || lower(hex(randomblob(2))) || '-' || lower(hex(randomblob(6))),
|
|
s."id",
|
|
s."name" || ' · 纪念章',
|
|
'在「' || s."name" || '」集到的专属纪念奖品',
|
|
100,
|
|
true,
|
|
CURRENT_TIMESTAMP,
|
|
CURRENT_TIMESTAMP
|
|
FROM "Stamp" s;
|
|
|
|
-- Drop legacy rows: old Redemption records (ruleId/stampCount model) cannot be mapped to the new
|
|
-- one-stamp-one-prize schema, and RedemptionRule is being retired. Intentional data loss.
|
|
DELETE FROM "Redemption";
|
|
DELETE FROM "RedemptionRule";
|
|
|
|
-- RedefineTable Redemption
|
|
PRAGMA defer_foreign_keys=ON;
|
|
PRAGMA foreign_keys=OFF;
|
|
|
|
CREATE TABLE "new_Redemption" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"userId" TEXT NOT NULL,
|
|
"stampId" TEXT NOT NULL,
|
|
"prizeId" TEXT NOT NULL,
|
|
"prizeName" TEXT NOT NULL,
|
|
"redeemedAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "Redemption_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT "Redemption_stampId_fkey" FOREIGN KEY ("stampId") REFERENCES "Stamp" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "Redemption_prizeId_fkey" FOREIGN KEY ("prizeId") REFERENCES "Prize" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
DROP TABLE "Redemption";
|
|
ALTER TABLE "new_Redemption" RENAME TO "Redemption";
|
|
|
|
CREATE UNIQUE INDEX "Redemption_userId_stampId_key" ON "Redemption"("userId", "stampId");
|
|
CREATE INDEX "Redemption_userId_idx" ON "Redemption"("userId");
|
|
|
|
-- DropTable (now safe, no more FK references)
|
|
DROP TABLE "RedemptionRule";
|
|
|
|
PRAGMA foreign_keys=ON;
|
|
PRAGMA defer_foreign_keys=OFF;
|