supabase-setup.sql

-- DEEP营任务考核平台 — 数据库初始化
-- 请在 Supabase SQL Editor 中运行此脚本:
-- https://supabase.com/dashboard/project/gwhlkumqkcyclvlahkkp/sql/new

-- ============================================
-- 1. 清理旧数据（mangoos 项目残留）
-- ============================================
DROP TABLE IF EXISTS "Submission" CASCADE;
DROP TABLE IF EXISTS "Task" CASCADE;
DROP TABLE IF EXISTS "Phase" CASCADE;
DROP TABLE IF EXISTS "Reward" CASCADE;
DROP TABLE IF EXISTS "InviteCode" CASCADE;
DROP TABLE IF EXISTS "User" CASCADE;
DROP TABLE IF EXISTS "_prisma_migrations" CASCADE;

-- ============================================
-- 2. 创建表
-- ============================================

-- User
CREATE TABLE "User" (
    "id" TEXT PRIMARY KEY,
    "email" TEXT NOT NULL UNIQUE,
    "name" TEXT NOT NULL,
    "passwordHash" TEXT NOT NULL,
    "role" TEXT NOT NULL DEFAULT 'STUDENT',
    "inviteCode" TEXT,
    "avatarUrl" TEXT,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- InviteCode
CREATE TABLE "InviteCode" (
    "id" TEXT PRIMARY KEY,
    "code" TEXT NOT NULL UNIQUE,
    "role" TEXT NOT NULL DEFAULT 'STUDENT',
    "used" BOOLEAN NOT NULL DEFAULT false,
    "usedBy" TEXT,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Phase
CREATE TABLE "Phase" (
    "id" TEXT PRIMARY KEY,
    "name" TEXT NOT NULL,
    "seq" INTEGER NOT NULL UNIQUE,
    "startDate" TIMESTAMP(3) NOT NULL,
    "endDate" TIMESTAMP(3) NOT NULL,
    "status" TEXT NOT NULL DEFAULT 'UPCOMING',
    "isFinal" BOOLEAN NOT NULL DEFAULT false,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Task
CREATE TABLE "Task" (
    "id" TEXT PRIMARY KEY,
    "phaseId" TEXT NOT NULL REFERENCES "Phase"("id") ON DELETE CASCADE,
    "title" TEXT NOT NULL,
    "description" TEXT NOT NULL,
    "deadline" TIMESTAMP(3) NOT NULL,
    "uploadRequirements" TEXT NOT NULL,
    "fileFormats" TEXT[] NOT NULL DEFAULT ARRAY['PDF'],
    "scoringCriteria" TEXT NOT NULL,
    "status" TEXT NOT NULL DEFAULT 'DRAFT',
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Submission
CREATE TABLE "Submission" (
    "id" TEXT PRIMARY KEY,
    "userId" TEXT NOT NULL REFERENCES "User"("id") ON DELETE CASCADE,
    "taskId" TEXT NOT NULL REFERENCES "Task"("id") ON DELETE CASCADE,
    "fileUrl" TEXT NOT NULL,
    "fileName" TEXT NOT NULL,
    "fileType" TEXT NOT NULL,
    "fileSize" INTEGER NOT NULL,
    "description" TEXT,
    "status" TEXT NOT NULL DEFAULT 'SUBMITTED',
    "aiScore" DOUBLE PRECISION,
    "aiFeedback" TEXT,
    "aiIssues" JSONB,
    "aiSuggestions" TEXT,
    "aiReviewedAt" TIMESTAMP(3),
    "mentorScore" DOUBLE PRECISION,
    "mentorFeedback" TEXT,
    "mentorAnnotations" JSONB,
    "mentorReviewedAt" TIMESTAMP(3),
    "mentorId" TEXT,
    "finalScore" DOUBLE PRECISION,
    "submittedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE("userId", "taskId")
);

-- Reward
CREATE TABLE "Reward" (
    "id" TEXT PRIMARY KEY,
    "name" TEXT NOT NULL,
    "description" TEXT NOT NULL,
    "imageUrl" TEXT,
    "type" TEXT NOT NULL DEFAULT 'COMPLETION',
    "sortOrder" INTEGER NOT NULL DEFAULT 0,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- ============================================
-- 3. 索引
-- ============================================
CREATE INDEX idx_submission_userId ON "Submission"("userId");
CREATE INDEX idx_submission_taskId ON "Submission"("taskId");
CREATE INDEX idx_submission_status ON "Submission"("status");
CREATE INDEX idx_task_phaseId ON "Task"("phaseId");
CREATE INDEX idx_task_status ON "Task"("status");
CREATE INDEX idx_phase_status ON "Phase"("status");

-- ============================================
-- 4. 启用 Supabase REST API
-- ============================================
-- 授权 service_role 访问所有表
ALTER TABLE "User" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "InviteCode" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "Phase" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "Task" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "Submission" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "Reward" ENABLE ROW LEVEL SECURITY;

-- service_role 可以绕过 RLS
CREATE POLICY "Service role bypass" ON "User" FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "Service role bypass" ON "InviteCode" FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "Service role bypass" ON "Phase" FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "Service role bypass" ON "Task" FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "Service role bypass" ON "Submission" FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "Service role bypass" ON "Reward" FOR ALL USING (true) WITH CHECK (true);

-- ============================================
-- 5. 种子数据
-- ============================================
-- Admin user (password: admin123, bcrypt hashed)
INSERT INTO "User" ("id", "email", "name", "passwordHash", "role") VALUES
('u_admin_001', 'admin@deepcamp.com', '管理员', '$2a$12$pbuZ3UxtkaYG.bozROt/3ODapAqYrZM7yeYZvgxrtOoex66ZgpU5O', 'ADMIN');

-- Invite codes
INSERT INTO "InviteCode" ("id", "code") VALUES
('ic_001', 'DEEP001'), ('ic_002', 'DEEP002'), ('ic_003', 'DEEP003'),
('ic_004', 'DEEP004'), ('ic_005', 'DEEP005'), ('ic_006', 'DEEP006'),
('ic_007', 'DEEP007'), ('ic_008', 'DEEP008'), ('ic_009', 'DEEP009'),
('ic_010', 'DEEP010');

-- Phases
INSERT INTO "Phase" ("id", "name", "seq", "startDate", "endDate", "status", "isFinal") VALUES
('ph_1', '第一期', 1, '2026-06-01', '2026-06-21', 'ACTIVE', false),
('ph_2', '第二期', 2, '2026-06-22', '2026-07-12', 'UPCOMING', false),
('ph_3', '第三期', 3, '2026-07-13', '2026-08-02', 'UPCOMING', false),
('ph_4', '第四期 · 最终考核', 4, '2026-08-03', '2026-08-23', 'UPCOMING', true);

-- Tasks (Phase 1)
INSERT INTO "Task" ("id", "phaseId", "title", "description", "deadline", "uploadRequirements", "fileFormats", "scoringCriteria", "status") VALUES
('t_001', 'ph_1', 'AI行业研究报告',
 '撰写一份关于人工智能在金融领域应用的行业研究报告。要求：分析至少3个AI在金融领域的应用场景，每个场景需包含技术原理、市场现状、未来趋势，引用至少5篇学术论文或权威报告，提出自己的见解和分析。',
 '2026-06-14T23:59:59Z', '请上传PDF或Word格式，页数不少于5页', ARRAY['PDF','WORD'],
 '内容深度(30分)+结构逻辑(25分)+引用规范(20分)+语言表达(15分)+格式规范(10分)', 'PUBLISHED'),

('t_002', 'ph_1', '经济学人文章分析',
 '选择一篇最新的经济学人(The Economist)文章进行深度分析。要求：文章主题需涉及宏观经济或金融政策，分析核心论点、论证逻辑和证据，提炼关键经济学术语和表达，撰写300词以上英文摘要和评论。',
 '2026-06-21T23:59:59Z', '请上传Markdown或PDF格式', ARRAY['PDF','MARKDOWN'],
 '分析深度(30分)+批判思维(25分)+术语运用(20分)+英文表达(15分)+格式规范(10分)', 'PUBLISHED');

-- Rewards
INSERT INTO "Reward" ("id", "name", "description", "type", "sortOrder") VALUES
('rw_1', '北大周边礼盒', '北京大学限定周边，包含笔记本、书签和徽章套装', 'COMPLETION', 1),
('rw_2', '结业书信', '由导师亲笔签名的DEEP营结业证明和寄语', 'COMPLETION', 2),
('rw_3', 'NUS小熊', '新加坡国立大学限定小熊，颁发给优秀学员', 'OUTSTANDING', 3);