init-database.sql

-- =============================================
-- PayNote 数据库初始化脚本
-- =============================================

-- 创建数据库（需要以超级用户身份执行）
-- 如果已连接到 postgres 数据库，执行以下命令：
CREATE DATABASE paynote;

-- 然后连接到 paynote 数据库执行以下内容
-- \c paynote

-- =============================================
-- 创建枚举类型
-- =============================================

CREATE TYPE "TransactionType" AS ENUM ('INCOME', 'EXPENSE');
CREATE TYPE "BudgetPeriod" AS ENUM ('MONTHLY', 'QUARTERLY', 'YEARLY');

-- =============================================
-- 创建表结构
-- =============================================

-- 用户表
CREATE TABLE "User" (
    "id" TEXT NOT NULL,
    "name" TEXT,
    "email" TEXT NOT NULL,
    "emailVerified" TIMESTAMP(3),
    "image" TEXT,
    "password" TEXT,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);

-- OAuth 账户表
CREATE TABLE "Account" (
    "id" TEXT NOT NULL,
    "userId" TEXT NOT NULL,
    "type" TEXT NOT NULL,
    "provider" TEXT NOT NULL,
    "providerAccountId" TEXT NOT NULL,
    "refresh_token" TEXT,
    "access_token" TEXT,
    "expires_at" INTEGER,
    "token_type" TEXT,
    "scope" TEXT,
    "id_token" TEXT,
    "session_state" TEXT,

    CONSTRAINT "Account_pkey" PRIMARY KEY ("id")
);

-- 会话表
CREATE TABLE "Session" (
    "id" TEXT NOT NULL,
    "sessionToken" TEXT NOT NULL,
    "userId" TEXT NOT NULL,
    "expires" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "Session_pkey" PRIMARY KEY ("id")
);

-- 验证令牌表
CREATE TABLE "VerificationToken" (
    "identifier" TEXT NOT NULL,
    "token" TEXT NOT NULL,
    "expires" TIMESTAMP(3) NOT NULL
);

-- 分类表
CREATE TABLE "Category" (
    "id" TEXT NOT NULL,
    "userId" TEXT,
    "name" TEXT NOT NULL,
    "type" "TransactionType" NOT NULL,
    "icon" TEXT,
    "color" TEXT,
    "isSystem" BOOLEAN NOT NULL DEFAULT false,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "Category_pkey" PRIMARY KEY ("id")
);

-- 交易记录表
CREATE TABLE "Transaction" (
    "id" TEXT NOT NULL,
    "userId" TEXT NOT NULL,
    "type" "TransactionType" NOT NULL,
    "amount" DECIMAL(10,2) NOT NULL,
    "categoryId" TEXT,
    "description" TEXT,
    "date" TIMESTAMP(3) NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "Transaction_pkey" PRIMARY KEY ("id")
);

-- 预算表
CREATE TABLE "Budget" (
    "id" TEXT NOT NULL,
    "userId" TEXT NOT NULL,
    "categoryId" TEXT,
    "amount" DECIMAL(10,2) NOT NULL,
    "period" "BudgetPeriod" NOT NULL,
    "startDate" TIMESTAMP(3) NOT NULL,
    "endDate" TIMESTAMP(3) NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "Budget_pkey" PRIMARY KEY ("id")
);

-- =============================================
-- 创建唯一索引
-- =============================================

CREATE UNIQUE INDEX "User_email_key" ON "User"("email");
CREATE UNIQUE INDEX "Account_provider_providerAccountId_key" ON "Account"("provider", "providerAccountId");
CREATE UNIQUE INDEX "Session_sessionToken_key" ON "Session"("sessionToken");
CREATE UNIQUE INDEX "VerificationToken_token_key" ON "VerificationToken"("token");
CREATE UNIQUE INDEX "VerificationToken_identifier_token_key" ON "VerificationToken"("identifier", "token");
CREATE UNIQUE INDEX "Category_userId_name_type_key" ON "Category"("userId", "name", "type");

-- =============================================
-- 创建普通索引
-- =============================================

CREATE INDEX "Category_userId_type_idx" ON "Category"("userId", "type");
CREATE INDEX "Transaction_userId_date_idx" ON "Transaction"("userId", "date");
CREATE INDEX "Transaction_userId_type_idx" ON "Transaction"("userId", "type");
CREATE INDEX "Transaction_userId_categoryId_idx" ON "Transaction"("userId", "categoryId");
CREATE INDEX "Budget_userId_startDate_endDate_idx" ON "Budget"("userId", "startDate", "endDate");
CREATE INDEX "Budget_userId_categoryId_idx" ON "Budget"("userId", "categoryId");

-- =============================================
-- 创建外键约束
-- =============================================

ALTER TABLE "Account" ADD CONSTRAINT "Account_userId_fkey"
    FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "Session" ADD CONSTRAINT "Session_userId_fkey"
    FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "Category" ADD CONSTRAINT "Category_userId_fkey"
    FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "Transaction" ADD CONSTRAINT "Transaction_userId_fkey"
    FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "Transaction" ADD CONSTRAINT "Transaction_categoryId_fkey"
    FOREIGN KEY ("categoryId") REFERENCES "Category"("id") ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE "Budget" ADD CONSTRAINT "Budget_userId_fkey"
    FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "Budget" ADD CONSTRAINT "Budget_categoryId_fkey"
    FOREIGN KEY ("categoryId") REFERENCES "Category"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- =============================================
-- 插入系统预设分类（种子数据）
-- =============================================

-- 支出分类
INSERT INTO "Category" ("id", "userId", "name", "type", "icon", "color", "isSystem", "createdAt") VALUES
    ('cat_expense_food', NULL, '餐饮', 'EXPENSE', '🍽️', '#ef4444', true, CURRENT_TIMESTAMP),
    ('cat_expense_transport', NULL, '交通', 'EXPENSE', '🚗', '#f97316', true, CURRENT_TIMESTAMP),
    ('cat_expense_shopping', NULL, '购物', 'EXPENSE', '🛒', '#eab308', true, CURRENT_TIMESTAMP),
    ('cat_expense_entertainment', NULL, '娱乐', 'EXPENSE', '🎮', '#22c55e', true, CURRENT_TIMESTAMP),
    ('cat_expense_housing', NULL, '居住', 'EXPENSE', '🏠', '#14b8a6', true, CURRENT_TIMESTAMP),
    ('cat_expense_communication', NULL, '通讯', 'EXPENSE', '📱', '#06b6d4', true, CURRENT_TIMESTAMP),
    ('cat_expense_medical', NULL, '医疗', 'EXPENSE', '🏥', '#3b82f6', true, CURRENT_TIMESTAMP),
    ('cat_expense_education', NULL, '教育', 'EXPENSE', '📚', '#8b5cf6', true, CURRENT_TIMESTAMP),
    ('cat_expense_clothing', NULL, '服饰', 'EXPENSE', '👔', '#d946ef', true, CURRENT_TIMESTAMP),
    ('cat_expense_other', NULL, '其他支出', 'EXPENSE', '📦', '#6b7280', true, CURRENT_TIMESTAMP);

-- 收入分类
INSERT INTO "Category" ("id", "userId", "name", "type", "icon", "color", "isSystem", "createdAt") VALUES
    ('cat_income_salary', NULL, '工资', 'INCOME', '💰', '#22c55e', true, CURRENT_TIMESTAMP),
    ('cat_income_bonus', NULL, '奖金', 'INCOME', '🎁', '#14b8a6', true, CURRENT_TIMESTAMP),
    ('cat_income_investment', NULL, '投资', 'INCOME', '📈', '#3b82f6', true, CURRENT_TIMESTAMP),
    ('cat_income_parttime', NULL, '兼职', 'INCOME', '💼', '#8b5cf6', true, CURRENT_TIMESTAMP),
    ('cat_income_redpacket', NULL, '红包', 'INCOME', '🧧', '#ef4444', true, CURRENT_TIMESTAMP),
    ('cat_income_other', NULL, '其他收入', 'INCOME', '💵', '#6b7280', true, CURRENT_TIMESTAMP);

-- =============================================
-- 完成
-- =============================================

-- 验证表创建成功
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';