{\n const [minutesRow] = await query<{ m: number }>(\n \"SELECT COALESCE(SUM(minutes),0) AS m FROM study_sessions WHERE user_id = ? AND date BETWEEN ? AND ? AND ended_at IS NOT NULL\",\n [userId, from, to],\n );\n const [planRow] = await query<{ total: number; done: number; goal: number }>(\n `SELECT COUNT(*) AS total,\n COALESCE(SUM(CASE WHEN status = 'done' THEN 1 ELSE 0 END), 0) AS done,\n COALESCE(SUM(goal_minutes), 0) AS goal\n FROM plans WHERE user_id = ? AND date BETWEEN ? AND ?`,\n [userId, from, to],\n );\n\n const dailyMinutes = await query<{ date: string; m: number }>(\n `SELECT date, COALESCE(SUM(minutes),0) AS m\n FROM study_sessions\n WHERE user_id = ? AND date BETWEEN ? AND ? AND ended_at IS NOT NULL\n GROUP BY date`,\n [userId, from, to],\n );\n const dailyPlans = await query<{ date: string; total: number; done: number }>(\n `SELECT date, COUNT(*) AS total,\n COALESCE(SUM(CASE WHEN status = 'done' THEN 1 ELSE 0 END), 0) AS done\n FROM plans WHERE user_id = ? AND date BETWEEN ? AND ? GROUP BY date`,\n [userId, from, to],\n );\n const dailyFocus = await query<{ date: string; focus: number | null }>(\n \"SELECT date, focus_score AS focus FROM diaries WHERE user_id = ? AND date BETWEEN ? AND ?\",\n [userId, from, to],\n );\n\n const minutesMap = new Map(dailyMinutes.map((r) => [r.date, Number(r.m)]));\n const planMap = new Map(dailyPlans.map((r) => [r.date, r]));\n const focusMap = new Map(dailyFocus.map((r) => [r.date, r.focus]));\n\n const daily = dateRange(from, to).map((date) => ({\n date,\n minutes: minutesMap.get(date) ?? 0,\n done: Number(planMap.get(date)?.done ?? 0),\n total: Number(planMap.get(date)?.total ?? 0),\n focus: focusMap.get(date) ?? null,\n }));\n\n const bySubjectRows = await query<\n {\n subject_id: string | null;\n name: string | null;\n color: string | null;\n minutes: number;\n total: number;\n done: number;\n }\n >(\n `SELECT p.subject_id,\n s.name, s.color,\n COALESCE(SUM(p.actual_minutes), 0) AS minutes,\n COUNT(*) AS total,\n COALESCE(SUM(CASE WHEN p.status = 'done' THEN 1 ELSE 0 END), 0) AS done\n FROM plans p LEFT JOIN subjects s ON s.id = p.subject_id\n WHERE p.user_id = ? AND p.date BETWEEN ? AND ?\n GROUP BY p.subject_id, s.name, s.color\n ORDER BY minutes DESC`,\n [userId, from, to],\n );\n\n const bySubject = bySubjectRows.map((r) => ({\n subjectId: r.subject_id,\n name: r.name ?? \"미지정\",\n color: r.color ?? \"#94a3b8\",\n minutes: Number(r.minutes),\n done: Number(r.done),\n total: Number(r.total),\n }));\n\n // 주간 집계 — 일별 결과를 주 단위로 접는다.\n const weekMap = new Map();\n for (const d of daily) {\n const wk = weekStart(d.date);\n const cur = weekMap.get(wk) ?? { minutes: 0, done: 0, total: 0 };\n cur.minutes += d.minutes;\n cur.done += d.done;\n cur.total += d.total;\n weekMap.set(wk, cur);\n }\n const weekly = [...weekMap.entries()]\n .sort(([a], [b]) => a.localeCompare(b))\n .map(([week, v]) => ({\n week,\n minutes: v.minutes,\n rate: v.total ? Math.round((v.done / v.total) * 100) : 0,\n }));\n\n // 연속 학습일 — 구간 안에서만 계산한다.\n let current = 0;\n let best = 0;\n for (const d of daily) {\n if (d.minutes > 0) {\n current += 1;\n best = Math.max(best, current);\n } else current = 0;\n }\n // 오늘 아직 공부 전이면 현재 연속이 0으로 보일 수 있다 — 마지막 날이 0이면 그 앞까지로 본다.\n if (daily.length && daily[daily.length - 1].minutes === 0) {\n let back = 0;\n for (let i = daily.length - 2; i >= 0 && daily[i].minutes > 0; i--) back++;\n current = back;\n }\n\n const studyDays = daily.filter((d) => d.minutes > 0).length;\n const focusValues = dailyFocus.map((r) => r.focus).filter((v): v is number =>\n typeof v === \"number\"\n );\n const diaryCount = Number(\n await scalar(\"SELECT COUNT(*) FROM diaries WHERE user_id = ? AND date BETWEEN ? AND ?\", [\n userId,\n from,\n to,\n ]) ?? 0,\n );\n const studyMinutes = Number(minutesRow?.m ?? 0);\n const planTotal = Number(planRow?.total ?? 0);\n const planDone = Number(planRow?.done ?? 0);\n\n return {\n from,\n to,\n totals: {\n studyMinutes,\n goalMinutes: Number(planRow?.goal ?? 0),\n planCount: planTotal,\n doneCount: planDone,\n completionRate: planTotal ? Math.round((planDone / planTotal) * 100) : 0,\n studyDays,\n avgMinutesPerStudyDay: studyDays ? Math.round(studyMinutes / studyDays) : 0,\n diaryCount,\n avgFocus: focusValues.length\n ? Math.round((focusValues.reduce((a, b) => a + b, 0) / focusValues.length) * 10) / 10\n : null,\n },\n daily,\n bySubject,\n weekly,\n streak: { current, best },\n };\n}\n\nexport function register(r: Router): void {\n r.get(\"/api/analytics/summary\", \"user\", async (ctx: Ctx) => {\n const userId = targetUserId(ctx);\n const today = toLocalDate();\n const { from, to } = rangeFromQuery(ctx.query, addDays(today, -27), today);\n return json(await buildSummary(userId, from, to));\n });\n\n /** 직전 동일 길이 구간과 비교 — \"지난주보다 얼마나\" 를 보여주기 위한 것 */\n r.get(\"/api/analytics/compare\", \"user\", async (ctx: Ctx) => {\n const userId = targetUserId(ctx);\n const today = toLocalDate();\n const { from, to } = rangeFromQuery(ctx.query, addDays(today, -6), today);\n const span = dateRange(from, to).length;\n const prevTo = addDays(from, -1);\n const prevFrom = addDays(prevTo, -(span - 1));\n\n const [cur, prev] = await Promise.all([\n buildSummary(userId, from, to),\n buildSummary(userId, prevFrom, prevTo),\n ]);\n return json({ current: cur, previous: prev });\n });\n}\n",[],[221,271],"4db72583-ffc0-4289-b9ca-3bf3ba195b50","admin.ts","0a4fc5d0-ab26-11f1-ba6d-1607ee4eb77e",26,["D",1788831527223],"/**\n * 관리자 / 관리쌤 화면용 API.\n */\nimport { requireAdmin, requireStaff } from \"../lib/auth.ts\";\nimport { badRequest, Ctx, json, notFound, Router } from \"../lib/http.ts\";\nimport { exec, one, query } from \"../lib/sqlite.ts\";\nimport { addDays, nowIso, toLocalDate } from \"../lib/time.ts\";\nimport { enumOf, optStr, str } from \"../lib/validate.ts\";\nimport { audit, notify } from \"./_shared.ts\";\nimport { buildSummary } from \"./analytics.ts\";\nimport { migrate } from \"../db/migrate.ts\";\n\nexport function register(r: Router): void {\n /** 오늘의 운영 현황 — 관리자 대시보드 첫 화면 */\n r.get(\"/api/admin/overview\", \"staff\", async (ctx: Ctx) => {\n requireStaff(ctx.user);\n const today = toLocalDate();\n\n const [students] = await query<{ c: number }>(\n \"SELECT COUNT(*) AS c FROM users WHERE role = 'student' AND status = 'active'\",\n );\n const [studiedToday] = await query<{ c: number }>(\n \"SELECT COUNT(DISTINCT user_id) AS c FROM study_sessions WHERE date = ?\",\n [today],\n );\n const [diariesToday] = await query<{ c: number }>(\n \"SELECT COUNT(*) AS c FROM diaries WHERE date = ? AND content <> ''\",\n [today],\n );\n const [pendingComments] = await query<{ c: number }>(\n \"SELECT COUNT(*) AS c FROM diaries WHERE coach_comment IS NULL AND date >= ?\",\n [addDays(today, -14)],\n );\n const [openConsults] = await query<{ c: number }>(\n \"SELECT COUNT(*) AS c FROM consults WHERE status IN ('requested','scheduled')\",\n );\n const [minutesToday] = await query<{ m: number }>(\n \"SELECT COALESCE(SUM(minutes),0) AS m FROM study_sessions WHERE date = ? AND ended_at IS NOT NULL\",\n [today],\n );\n\n return json({\n today,\n stats: {\n activeStudents: Number(students?.c ?? 0),\n studiedToday: Number(studiedToday?.c ?? 0),\n diariesToday: Number(diariesToday?.c ?? 0),\n pendingComments: Number(pendingComments?.c ?? 0),\n openConsults: Number(openConsults?.c ?? 0),\n studyMinutesToday: Number(minutesToday?.m ?? 0),\n },\n });\n });\n\n /**\n * 학생 목록 + 오늘 진도.\n * 화면에서 학생마다 API 를 또 부르지 않도록 한 번에 붙여서 준다.\n */\n r.get(\"/api/admin/students\", \"staff\", async (ctx: Ctx) => {\n requireStaff(ctx.user);\n const today = toLocalDate();\n const q = (ctx.query.get(\"q\") ?? \"\").trim();\n const like = `%${q}%`;\n\n const rows = await query(\n `SELECT u.id, u.name, u.email, u.grade, u.school, u.target, u.seat_no, u.status, u.role,\n u.last_login_at, u.coach_id,\n (SELECT COALESCE(SUM(s.minutes),0) FROM study_sessions s\n WHERE s.user_id = u.id AND s.date = ? AND s.ended_at IS NOT NULL) AS minutes_today,\n (SELECT COUNT(*) FROM plans p WHERE p.user_id = u.id AND p.date = ?) AS plans_today,\n (SELECT COUNT(*) FROM plans p WHERE p.user_id = u.id AND p.date = ? AND p.status = 'done') AS done_today,\n (SELECT COUNT(*) FROM diaries d WHERE d.user_id = u.id AND d.date = ? AND d.content <> '') AS diary_today\n FROM users u\n WHERE u.role = 'student' ${\n q ? \"AND (u.name LIKE ? OR u.email LIKE ? OR u.seat_no LIKE ?)\" : \"\"\n }\n ORDER BY u.status, u.name\n LIMIT 300`,\n q ? [today, today, today, today, like, like, like] : [today, today, today, today],\n );\n return json({ students: rows, today });\n });\n\n /** 학생 1명 상세 — 프로필 + 기간 분석 + 최근 일기 */\n r.get(\"/api/admin/students/:id\", \"staff\", async (ctx: Ctx) => {\n requireStaff(ctx.user);\n const user = await one(\"SELECT * FROM users WHERE id = ?\", [ctx.params.id]);\n if (!user) throw notFound(\"학생을 찾을 수 없습니다.\");\n\n const today = toLocalDate();\n const from = ctx.query.get(\"from\") ?? addDays(today, -27);\n const summary = await buildSummary(ctx.params.id, from, ctx.query.get(\"to\") ?? today);\n const diaries = await query(\n \"SELECT * FROM diaries WHERE user_id = ? ORDER BY date DESC LIMIT 10\",\n [ctx.params.id],\n );\n const consults = await query(\n \"SELECT * FROM consults WHERE user_id = ? ORDER BY created_at DESC LIMIT 10\",\n [ctx.params.id],\n );\n return json({ user, summary, diaries, consults });\n });\n\n /** 학생 정보 수정 (좌석·담당쌤·상태·권한) */\n r.patch(\"/api/admin/students/:id\", \"staff\", async (ctx: Ctx) => {\n requireStaff(ctx.user);\n const body = await ctx.body();\n const user = await one<{ id: string }>(\"SELECT id FROM users WHERE id = ?\", [ctx.params.id]);\n if (!user) throw notFound(\"학생을 찾을 수 없습니다.\");\n\n const sets: string[] = [];\n const args: (string | null)[] = [];\n const put = (c: string, v: string | null) => {\n sets.push(`${c} = ?`);\n args.push(v);\n };\n\n if (body.seatNo !== undefined) put(\"seat_no\", optStr(body, \"seatNo\", 20));\n if (body.grade !== undefined) put(\"grade\", optStr(body, \"grade\", 20));\n if (body.school !== undefined) put(\"school\", optStr(body, \"school\", 60));\n if (body.target !== undefined) put(\"target\", optStr(body, \"target\", 120));\n if (body.phone !== undefined) put(\"phone\", optStr(body, \"phone\", 30));\n if (body.coachId !== undefined) put(\"coach_id\", optStr(body, \"coachId\", 60));\n if (body.status !== undefined) {\n put(\"status\", enumOf(body, \"status\", [\"active\", \"paused\", \"left\"] as const, \"active\"));\n }\n // 권한 변경은 관리자만.\n if (body.role !== undefined) {\n requireAdmin(ctx.user);\n put(\"role\", enumOf(body, \"role\", [\"student\", \"coach\", \"admin\"] as const, \"student\"));\n }\n\n if (!sets.length) throw badRequest(\"변경할 값이 없습니다.\");\n sets.push(\"updated_at = ?\");\n args.push(nowIso(), ctx.params.id);\n await exec(`UPDATE users SET ${sets.join(\", \")} WHERE id = ?`, args);\n await audit(ctx.user.id, \"student.update\", ctx.params.id, body);\n\n return json({ user: await one(\"SELECT * FROM users WHERE id = ?\", [ctx.params.id]) });\n });\n\n /** 관리쌤 목록 (담당 배정 셀렉트박스용) */\n r.get(\"/api/admin/staff\", \"staff\", async (ctx: Ctx) => {\n requireStaff(ctx.user);\n const rows = await query(\n \"SELECT id, name, email, role FROM users WHERE role IN ('coach','admin') ORDER BY name\",\n );\n return json({ staff: rows });\n });\n\n /** 공지 — 전체 또는 지정 학생에게 알림을 보낸다 */\n r.post(\"/api/admin/notices\", \"staff\", async (ctx: Ctx) => {\n requireStaff(ctx.user);\n const body = await ctx.body();\n const title = str(body, \"title\", { required: true, max: 120 });\n const message = str(body, \"body\", { max: 1000 });\n const targets = Array.isArray(body.studentIds) && body.studentIds.length\n ? (body.studentIds as string[]).slice(0, 500)\n : (await query<{ id: string }>(\n \"SELECT id FROM users WHERE role = 'student' AND status = 'active'\",\n )).map((r) => r.id);\n\n for (const id of targets) await notify(id, \"notice\", title, message, \"/index.html\");\n await audit(ctx.user.id, \"notice.send\", undefined, { title, count: targets.length });\n return json({ sent: targets.length });\n });\n\n /** 마이그레이션 실행 (Val Town 최초 세팅/스키마 추가 시) */\n r.post(\"/api/admin/migrate\", \"admin\", async (ctx: Ctx) => {\n requireAdmin(ctx.user);\n const result = await migrate();\n await audit(ctx.user.id, \"db.migrate\", undefined, result);\n return json(result);\n });\n\n r.get(\"/api/admin/audit\", \"admin\", async (ctx: Ctx) => {\n requireAdmin(ctx.user);\n const rows = await query(\n `SELECT a.*, u.name AS actor_name FROM audit_logs a\n LEFT JOIN users u ON u.id = a.actor_id\n ORDER BY a.created_at DESC LIMIT 200`,\n );\n return json({ logs: rows });\n });\n}\n",[],[221,279],"5432eda6-3455-4b48-aa81-236fbe678efe","_shared.ts","09507b02-ab26-11f1-9183-1607ee4eb77e",21,["D",1788831527798],"/**\n * 라우트 공통 헬퍼.\n */\nimport { isStaff } from \"../lib/auth.ts\";\nimport { Ctx, forbidden, notFound } from \"../lib/http.ts\";\nimport { exec, one, query, uuid } from \"../lib/sqlite.ts\";\nimport { nowIso } from \"../lib/time.ts\";\nimport { DEFAULT_SUBJECTS } from \"../db/schema.ts\";\n\n/**\n * 조회/수정 대상 학생을 정한다.\n * - 학생 : 항상 본인\n * - 관리쌤/관리자 : ?studentId= 로 다른 학생을 지정할 수 있다\n *\n * 학생이 남의 데이터를 보려고 studentId 를 넣으면 403.\n */\nexport function targetUserId(ctx: Ctx): string {\n const requested = ctx.query.get(\"studentId\");\n if (!requested || requested === ctx.user.id) return ctx.user.id;\n if (!isStaff(ctx.user)) throw forbidden(\"다른 학생의 데이터에 접근할 수 없습니다.\");\n return requested;\n}\n\n/** 본문에 studentId 가 들어오는 POST/PATCH 용 */\nexport function targetUserIdFromBody(ctx: Ctx, body: Record): string {\n const requested = typeof body.studentId === \"string\" ? body.studentId : null;\n if (!requested || requested === ctx.user.id) return ctx.user.id;\n if (!isStaff(ctx.user)) throw forbidden(\"다른 학생의 데이터를 수정할 수 없습니다.\");\n return requested;\n}\n\nexport interface SubjectRow {\n id: string;\n user_id: string;\n name: string;\n color: string;\n sort_order: number;\n archived: number;\n}\n\n/** 과목이 하나도 없는 신규 학생에게 기본 과목을 깔아준다. */\nexport async function ensureSubjects(userId: string): Promise {\n const rows = await query(\n \"SELECT * FROM subjects WHERE user_id = ? AND archived = 0 ORDER BY sort_order, name\",\n [userId],\n );\n if (rows.length) return rows;\n\n const now = nowIso();\n for (let i = 0; i < DEFAULT_SUBJECTS.length; i++) {\n const s = DEFAULT_SUBJECTS[i];\n await exec(\n \"INSERT INTO subjects (id, user_id, name, color, sort_order, archived, created_at) VALUES (?, ?, ?, ?, ?, 0, ?)\",\n [uuid(), userId, s.name, s.color, i, now],\n );\n }\n return await query(\n \"SELECT * FROM subjects WHERE user_id = ? AND archived = 0 ORDER BY sort_order, name\",\n [userId],\n );\n}\n\n/** 소유자 확인 후 행을 돌려준다. 없거나 남의 것이면 404/403. */\nexport async function ownedRow(\n table: string,\n id: string,\n ctx: Ctx,\n): Promise {\n const row = await one(`SELECT * FROM ${table} WHERE id = ?`, [id]);\n if (!row) throw notFound();\n if (row.user_id && row.user_id !== ctx.user.id && !isStaff(ctx.user)) {\n throw forbidden(\"본인 데이터만 수정할 수 있습니다.\");\n }\n return row;\n}\n\nexport async function notify(\n userId: string,\n type: string,\n title: string,\n body?: string,\n link?: string,\n): Promise {\n await exec(\n \"INSERT INTO notifications (id, user_id, type, title, body, link, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)\",\n [uuid(), userId, type, title, body ?? null, link ?? null, nowIso()],\n );\n}\n\nexport async function audit(\n actorId: string,\n action: string,\n target?: string,\n meta?: unknown,\n): Promise {\n await exec(\n \"INSERT INTO audit_logs (id, actor_id, action, target, meta, created_at) VALUES (?, ?, ?, ?, ?, ?)\",\n [uuid(), actorId, action, target ?? null, meta ? JSON.stringify(meta) : null, nowIso()],\n );\n}\n",[],[221,287],"3fb43c0f-8230-4f8c-9ddf-d22e141f8fcb",4,["D",1788831525811],"directory",[182,183,184,185,186,187,188,189,190,191],[221],"df23e099-bed8-4548-83a7-37c579690a42","validate.ts","084b9f48-ab26-11f1-97be-1607ee4eb77e",["D",1788831526112],"/**\n * 입력 검증 — 얇은 헬퍼. 스키마 라이브러리를 넣지 않고 필요한 것만 둔다.\n * 모든 문자열은 trim 하고 길이 상한을 강제한다(DB 오염 방지).\n */\nimport { badRequest } from \"./http.ts\";\nimport { isDate } from \"./time.ts\";\n\ntype Obj = Record;\n\nexport function str(\n body: Obj,\n key: string,\n opts: { required?: boolean; max?: number; min?: number; default?: string } = {},\n): string {\n const raw = body[key];\n if (raw === undefined || raw === null || raw === \"\") {\n if (opts.required) throw badRequest(`'${key}' 값이 필요합니다.`);\n return opts.default ?? \"\";\n }\n if (typeof raw !== \"string\") throw badRequest(`'${key}' 는 문자열이어야 합니다.`);\n const v = raw.trim();\n const max = opts.max ?? 2000;\n if (v.length > max) throw badRequest(`'${key}' 는 ${max}자를 넘을 수 없습니다.`);\n if (opts.min && v.length < opts.min) {\n throw badRequest(`'${key}' 는 ${opts.min}자 이상이어야 합니다.`);\n }\n return v;\n}\n\nexport function optStr(body: Obj, key: string, max = 2000): string | null {\n const v = str(body, key, { max });\n return v === \"\" ? null : v;\n}\n\nexport function int(\n body: Obj,\n key: string,\n opts: { required?: boolean; min?: number; max?: number; default?: number } = {},\n): number {\n const raw = body[key];\n if (raw === undefined || raw === null || raw === \"\") {\n if (opts.required) throw badRequest(`'${key}' 값이 필요합니다.`);\n return opts.default ?? 0;\n }\n const n = Number(raw);\n if (!Number.isFinite(n)) throw badRequest(`'${key}' 는 숫자여야 합니다.`);\n const v = Math.trunc(n);\n if (opts.min !== undefined && v < opts.min) {\n throw badRequest(`'${key}' 는 ${opts.min} 이상이어야 합니다.`);\n }\n if (opts.max !== undefined && v > opts.max) {\n throw badRequest(`'${key}' 는 ${opts.max} 이하여야 합니다.`);\n }\n return v;\n}\n\nexport function optInt(body: Obj, key: string, min: number, max: number): number | null {\n const raw = body[key];\n if (raw === undefined || raw === null || raw === \"\") return null;\n return int(body, key, { min, max });\n}\n\nexport function enumOf(\n body: Obj,\n key: string,\n allowed: readonly T[],\n fallback: T,\n): T {\n const raw = body[key];\n if (raw === undefined || raw === null || raw === \"\") return fallback;\n if (typeof raw !== \"string\" || !allowed.includes(raw as T)) {\n throw badRequest(`'${key}' 는 ${allowed.join(\", \")} 중 하나여야 합니다.`);\n }\n return raw as T;\n}\n\nexport function dateOf(body: Obj, key: string, fallback?: string): string {\n const raw = body[key];\n if (raw === undefined || raw === null || raw === \"\") {\n if (fallback) return fallback;\n throw badRequest(`'${key}' 날짜가 필요합니다.`);\n }\n if (!isDate(raw)) throw badRequest(`'${key}' 는 YYYY-MM-DD 형식이어야 합니다.`);\n return raw;\n}\n\n/** ISO8601 시각 문자열 검증 */\nexport function isoOf(body: Obj, key: string, required = false): string | null {\n const raw = body[key];\n if (raw === undefined || raw === null || raw === \"\") {\n if (required) throw badRequest(`'${key}' 시각이 필요합니다.`);\n return null;\n }\n if (typeof raw !== \"string\" || Number.isNaN(Date.parse(raw))) {\n throw badRequest(`'${key}' 는 올바른 날짜/시각이어야 합니다.`);\n }\n return new Date(raw).toISOString();\n}\n\n/** 쿼리스트링에서 날짜 범위를 뽑는다. 없으면 기본 구간을 준다. */\nexport function rangeFromQuery(\n q: URLSearchParams,\n defaultFrom: string,\n defaultTo: string,\n): { from: string; to: string } {\n const from = q.get(\"from\");\n const to = q.get(\"to\");\n if (from && !isDate(from)) throw badRequest(\"'from' 은 YYYY-MM-DD 형식이어야 합니다.\");\n if (to && !isDate(to)) throw badRequest(\"'to' 는 YYYY-MM-DD 형식이어야 합니다.\");\n const f = from ?? defaultFrom;\n const t = to ?? defaultTo;\n if (f > t) throw badRequest(\"'from' 이 'to' 보다 늦습니다.\");\n return { from: f, to: t };\n}\n",[],[154,301],"3297f0d4-3d89-4bc6-835c-cfe724ea5bf9","time.ts","08ca7f20-ab26-11f1-9e2b-1607ee4eb77e",15,["D",1788831526935],"/**\n * 날짜 유틸. 서비스 기준 시간대(KST)로 \"오늘\"을 계산한다.\n * DB 에는 날짜는 YYYY-MM-DD(TEXT), 시각은 ISO8601 UTC(TEXT)로 저장한다.\n */\nimport { CONFIG } from \"../config.ts\";\n\nconst DATE_FMT = new Intl.DateTimeFormat(\"en-CA\", {\n timeZone: CONFIG.timezone,\n year: \"numeric\",\n month: \"2-digit\",\n day: \"2-digit\",\n});\n\n/** Date → 'YYYY-MM-DD' (KST 기준) */\nexport function toLocalDate(d: Date = new Date()): string {\n return DATE_FMT.format(d);\n}\n\n/** 현재 시각 ISO(UTC) */\nexport function nowIso(): string {\n return new Date().toISOString();\n}\n\n/** 'YYYY-MM-DD' 에 일수를 더한다. */\nexport function addDays(date: string, days: number): string {\n const [y, m, d] = date.split(\"-\").map(Number);\n const dt = new Date(Date.UTC(y, m - 1, d));\n dt.setUTCDate(dt.getUTCDate() + days);\n return dt.toISOString().slice(0, 10);\n}\n\n/** 두 날짜(양끝 포함) 사이의 날짜 배열. 최대 400일에서 끊는다. */\nexport function dateRange(from: string, to: string): string[] {\n const out: string[] = [];\n let cur = from;\n for (let i = 0; i < 400 && cur <= to; i++) {\n out.push(cur);\n cur = addDays(cur, 1);\n }\n return out;\n}\n\n/** 해당 날짜가 속한 주의 월요일 */\nexport function weekStart(date: string): string {\n const [y, m, d] = date.split(\"-\").map(Number);\n const dow = (new Date(Date.UTC(y, m - 1, d)).getUTCDay() + 6) % 7; // 월=0\n return addDays(date, -dow);\n}\n\nexport function isDate(v: unknown): v is string {\n return typeof v === \"string\" && /^\\d{4}-\\d{2}-\\d{2}$/.test(v);\n}\n",[],[154,308],"f3e0733b-4e89-4b1f-ae5f-40e750c11ca0","sqlite.ts","08548de2-ab26-11f1-aeaa-1607ee4eb77e",6,["D",1788831526144],"/**\n * SQLite 어댑터.\n *\n * 백엔드는 두 가지다.\n * - valtown : Val Town 내장 SQLite (https://esm.town/v/std/sqlite)\n * - local : jsr:@db/sqlite 파일 DB (로컬 개발/테스트)\n *\n * 두 백엔드의 반환 형태가 달라서(배열 행 vs 객체 행) 여기서 객체 행으로 통일한다.\n * 호출부는 어느 백엔드인지 알 필요가 없다.\n *\n * 모듈 경로를 변수로 조립하는 이유: `deno check` 가 정적 분석으로 원격 모듈을\n * 받아오려 하는 것을 막기 위해서다(로컬에서는 Val Town 모듈을 받을 수 없다).\n */\nimport { CONFIG, isLocal } from \"../config.ts\";\n\nexport type Row = Record;\nexport type Arg = string | number | boolean | null;\n\n// deno-lint-ignore no-explicit-any\ntype AnyDb = any;\n\nlet valtownSqlite: AnyDb | null = null;\nlet localDb: AnyDb | null = null;\n\n/**\n * Val Town 내장 SQLite.\n *\n * ★ 경로 끝의 `/main.ts` 를 반드시 붙인다.\n * 생략하면(`.../std/sqlite`) legacy account-scoped(global) DB 에 연결되고,\n * 그쪽은 rows 를 \"배열의 배열\"로 돌려줘 반환 형태까지 달라진다.\n * 현행 권장은 val-scoped(`/main.ts`)이며 rows 가 객체 배열이다.\n * https://docs.val.town/reference/std/sqlite/usage\n */\nasync function getValtown(): Promise {\n if (!valtownSqlite) {\n const spec = [\"https:/\", \"esm.town\", \"v\", \"std\", \"sqlite\", \"main.ts\"].join(\"/\");\n const mod = await import(spec);\n valtownSqlite = mod.sqlite;\n }\n return valtownSqlite;\n}\n\n/**\n * 로컬 백엔드는 Deno 내장 node:sqlite 를 쓴다.\n * (jsr:@db/sqlite 는 SQL 문에 한글이 섞이면 바이트 길이를 잘못 계산해\n * \"incomplete input\" 으로 실패한다 — DDL 주석이 한글이라 바로 걸린다.)\n */\nasync function getLocal(): Promise {\n if (!localDb) {\n const spec = [\"node:\", \"sqlite\"].join(\"\");\n const { DatabaseSync } = await import(spec);\n const path = CONFIG.localDbPath;\n const dir = path.slice(0, path.lastIndexOf(\"/\"));\n if (dir) await Deno.mkdir(dir, { recursive: true }).catch(() => {});\n localDb = new DatabaseSync(path);\n localDb.exec(\"PRAGMA journal_mode = WAL;\");\n localDb.exec(\"PRAGMA foreign_keys = ON;\");\n }\n return localDb;\n}\n\n/** SELECT — 객체 행 배열을 돌려준다. */\nexport async function query(sql: string, args: Arg[] = []): Promise {\n if (isLocal()) {\n const db = await getLocal();\n return db.prepare(sql).all(...args) as T[];\n }\n const sqlite = await getValtown();\n const res = await sqlite.execute({ sql, args });\n const rows: unknown[] = res.rows ?? [];\n if (!rows.length) return [];\n\n // val-scoped 는 객체 배열, legacy global 은 배열의 배열을 준다.\n // 어느 쪽에 붙어도 깨지지 않게 형태를 보고 분기한다.\n if (!Array.isArray(rows[0])) return rows as T[];\n\n const cols: string[] = res.columns ?? [];\n return (rows as unknown[][]).map((row) => {\n const obj: Row = {};\n cols.forEach((c, i) => (obj[c] = row[i]));\n return obj as T;\n });\n}\n\n/** SELECT 단건 */\nexport async function one(sql: string, args: Arg[] = []): Promise {\n const rows = await query(sql, args);\n return rows.length ? rows[0] : null;\n}\n\n/** INSERT / UPDATE / DELETE */\nexport async function exec(sql: string, args: Arg[] = []): Promise {\n if (isLocal()) {\n const db = await getLocal();\n db.prepare(sql).run(...args);\n return;\n }\n const sqlite = await getValtown();\n await sqlite.execute({ sql, args });\n}\n\n/** DDL 등 파라미터 없는 스크립트를 순서대로 실행 */\nexport async function execScript(statements: string[]): Promise {\n for (const sql of statements) {\n const trimmed = sql.trim();\n if (trimmed) await exec(trimmed);\n }\n}\n\n/** COUNT(*) 같은 단일 스칼라 */\nexport async function scalar(sql: string, args: Arg[] = []): Promise {\n const row = await one(sql, args);\n if (!row) return null;\n const first = Object.values(row)[0];\n return (first as T) ?? null;\n}\n\nexport function uuid(): string {\n return crypto.randomUUID();\n}\n",[],[154,316],"af4ffba0-23c5-4c86-9eff-6d32cdf4b408","http.ts",31,["D",1788831720182],"/**\n * HTTP 유틸 — 응답 헬퍼, CORS, 아주 작은 라우터.\n * 프레임워크를 넣지 않는 이유: Val Town 은 단일 fetch 핸들러라 이 정도면 충분하고,\n * 의존성이 적을수록 배포가 단순하다.\n */\nimport { CONFIG } from \"../config.ts\";\n\nexport class HttpError extends Error {\n constructor(\n public status: number,\n public code: string,\n message: string,\n public detail?: unknown,\n ) {\n super(message);\n this.name = \"HttpError\";\n }\n}\n\nexport const badRequest = (msg: string, detail?: unknown) =>\n new HttpError(400, \"bad_request\", msg, detail);\nexport const unauthorized = (msg = \"로그인이 필요합니다.\") =>\n new HttpError(401, \"unauthorized\", msg);\nexport const forbidden = (msg = \"권한이 없습니다.\") => new HttpError(403, \"forbidden\", msg);\nexport const notFound = (msg = \"대상을 찾을 수 없습니다.\") => new HttpError(404, \"not_found\", msg);\nexport const tooMany = (msg: string) => new HttpError(429, \"rate_limited\", msg);\n\n/**\n * 허용 오리진이면 그 오리진을 그대로 돌려준다.\n * ALLOWED_ORIGINS 가 비어 있으면 개발 편의를 위해 * 를 준다(운영에서는 반드시 채울 것).\n */\nexport function corsHeaders(req: Request): Record {\n const origin = req.headers.get(\"origin\") ?? \"\";\n const list = CONFIG.allowedOrigins;\n let allow = \"*\";\n if (list.length) allow = list.includes(origin) ? origin : list[0];\n\n return {\n \"access-control-allow-origin\": allow,\n \"access-control-allow-methods\": \"GET,POST,PATCH,PUT,DELETE,OPTIONS\",\n \"access-control-allow-headers\": \"authorization,content-type\",\n \"access-control-max-age\": \"86400\",\n \"vary\": \"origin\",\n };\n}\n\nexport function json(data: unknown, status = 200, headers: Record = {}): Response {\n return new Response(JSON.stringify(data), {\n status,\n headers: { \"content-type\": \"application/json; charset=utf-8\", ...headers },\n });\n}\n\nexport function errorResponse(e: unknown, req: Request): Response {\n const cors = corsHeaders(req);\n if (e instanceof HttpError) {\n return json({ error: e.code, message: e.message, detail: e.detail }, e.status, cors);\n }\n console.error(\"[unhandled]\", e);\n const message = e instanceof Error ? e.message : String(e);\n return json({ error: \"internal\", message }, 500, cors);\n}\n\n/* ── 라우터 ───────────────────────────────────────────────── */\n\nexport type AuthLevel = \"public\" | \"user\" | \"staff\" | \"admin\";\n\nexport interface AuthUser {\n id: string;\n email: string | null;\n name: string;\n role: \"student\" | \"coach\" | \"admin\";\n}\n\nexport interface Ctx {\n req: Request;\n url: URL;\n params: Record;\n query: URLSearchParams;\n /** auth 레벨이 public 이 아니면 항상 채워진다. */\n user: AuthUser;\n body: >() => Promise;\n}\n\nexport type Handler = (ctx: Ctx) => Promise | Response;\n\ninterface Route {\n method: string;\n segments: string[];\n handler: Handler;\n auth: AuthLevel;\n}\n\nexport class Router {\n private routes: Route[] = [];\n\n add(method: string, pattern: string, auth: AuthLevel, handler: Handler): this {\n this.routes.push({\n method: method.toUpperCase(),\n segments: pattern.split(\"/\").filter(Boolean),\n handler,\n auth,\n });\n return this;\n }\n\n get = (p: string, a: AuthLevel, h: Handler) => this.add(\"GET\", p, a, h);\n post = (p: string, a: AuthLevel, h: Handler) => this.add(\"POST\", p, a, h);\n patch = (p: string, a: AuthLevel, h: Handler) => this.add(\"PATCH\", p, a, h);\n del = (p: string, a: AuthLevel, h: Handler) => this.add(\"DELETE\", p, a, h);\n\n match(method: string, path: string): { route: Route; params: Record } | null {\n const parts = path.split(\"/\").filter(Boolean);\n let pathMatched = false;\n\n for (const route of this.routes) {\n if (route.segments.length !== parts.length) continue;\n const params: Record = {};\n let ok = true;\n for (let i = 0; i < parts.length; i++) {\n const seg = route.segments[i];\n if (seg.startsWith(\":\")) params[seg.slice(1)] = decodeURIComponent(parts[i]);\n else if (seg !== parts[i]) {\n ok = false;\n break;\n }\n }\n if (!ok) continue;\n pathMatched = true;\n if (route.method === method.toUpperCase()) return { route, params };\n }\n\n if (pathMatched) throw new HttpError(405, \"method_not_allowed\", \"허용되지 않은 메서드입니다.\");\n return null;\n }\n\n levelOf(route: Route): AuthLevel {\n return route.auth;\n }\n\n run(route: Route, ctx: Ctx): Promise | Response {\n return route.handler(ctx);\n }\n}\n\n/** 요청 본문 파서 — 빈 본문은 {} 로 준다. */\nexport function bodyReader(req: Request) {\n let cached: unknown;\n let read = false;\n return async >(): Promise => {\n if (read) return cached as T;\n read = true;\n const text = await req.text();\n if (!text.trim()) {\n cached = {};\n return cached as T;\n }\n try {\n cached = JSON.parse(text);\n } catch {\n throw badRequest(\"JSON 본문을 해석할 수 없습니다.\");\n }\n return cached as T;\n };\n}\n",[],[154,324],"a856c541-89d5-4b15-a59e-2ae5b080db9c","env.ts","0879b608-ab26-11f1-aeaa-1607ee4eb77e",8,["D",1788831526436],"/**\n * 로컬 개발용 .env 로더.\n * Val Town 에서는 대시보드 환경변수가 이 역할을 하므로 파일이 없으면 조용히 넘어간다.\n *\n * 이 모듈은 부수효과가 목적이다 — 진입점에서 `import \"./lib/env.ts\";` 로 가장 먼저 부른다.\n */\nexport async function loadEnvFile(url = new URL(\"../../.env\", import.meta.url)): Promise {\n let text: string;\n try {\n text = await Deno.readTextFile(url);\n } catch {\n return false;\n }\n\n for (const line of text.split(\"\\n\")) {\n const trimmed = line.trim();\n if (!trimmed || trimmed.startsWith(\"#\")) continue;\n const eq = trimmed.indexOf(\"=\");\n if (eq < 0) continue;\n const key = trimmed.slice(0, eq).trim();\n const value = trimmed.slice(eq + 1).trim().replace(/^[\"']|[\"']$/g, \"\");\n // 이미 설정된 시스템 환경변수가 우선한다.\n if (!Deno.env.get(key)) Deno.env.set(key, value);\n }\n return true;\n}\n\nawait loadEnvFile();\n",[],[154,331],["D",1788831526216],[],[154,63],"c5cef72f-db43-438c-977c-fe9723c10e7f","auth.ts","0882c860-ab26-11f1-aeaa-1607ee4eb77e",["D",1788841392119],2,"/**\n * Firebase ID 토큰 검증 + 사용자 upsert.\n *\n * Admin SDK 를 쓰지 않는다 — Deno/Val Town 에서 무겁고 서비스 계정 키까지 필요하다.\n * 대신 구글이 공개하는 JWK 로 RS256 서명을 WebCrypto 로 직접 검증한다.\n * (x509 PEM 엔드포인트가 아니라 JWK 엔드포인트를 쓰는 이유: importKey('jwk') 로 바로 들어간다.)\n */\nimport { CONFIG } from \"../config.ts\";\nimport { AuthUser, forbidden, unauthorized } from \"./http.ts\";\nimport { exec, one } from \"./sqlite.ts\";\nimport { nowIso } from \"./time.ts\";\n\nconst JWK_URL =\n \"https://www.googleapis.com/service_accounts/v1/jwk/securetoken@system.gserviceaccount.com\";\n\ninterface Jwk {\n kid: string;\n kty: string;\n n: string;\n e: string;\n alg: string;\n}\n\nlet keyCache: { keys: Map; expiresAt: number } | null = null;\n\nasync function getKeys(): Promise> {\n if (keyCache && keyCache.expiresAt > Date.now()) return keyCache.keys;\n\n const res = await fetch(JWK_URL);\n if (!res.ok) throw unauthorized(\"인증 키를 가져오지 못했습니다.\");\n\n const maxAge = Number(\n (res.headers.get(\"cache-control\") ?? \"\").match(/max-age=(\\d+)/)?.[1] ?? \"3600\",\n );\n const data = (await res.json()) as { keys: Jwk[] };\n\n const keys = new Map();\n for (const jwk of data.keys) {\n const key = await crypto.subtle.importKey(\n \"jwk\",\n { kty: jwk.kty, n: jwk.n, e: jwk.e, alg: \"RS256\", ext: true },\n { name: \"RSASSA-PKCS1-v1_5\", hash: \"SHA-256\" },\n false,\n [\"verify\"],\n );\n keys.set(jwk.kid, key);\n }\n\n keyCache = { keys, expiresAt: Date.now() + maxAge * 1000 };\n return keys;\n}\n\nfunction b64urlToBytes(s: string): Uint8Array {\n const b64 = s.replace(/-/g, \"+\").replace(/_/g, \"/\").padEnd(\n s.length + ((4 - (s.length % 4)) % 4),\n \"=\",\n );\n const bin = atob(b64);\n // WebCrypto 는 ArrayBuffer 기반 뷰만 받는다(SharedArrayBuffer 불가) — 버퍼를 직접 만들어 맞춘다.\n const out = new Uint8Array(new ArrayBuffer(bin.length));\n for (let i = 0; i < bin.length; i++) out[i] = bin.charCodeAt(i);\n return out;\n}\n\nfunction b64urlToJson(s: string): T {\n return JSON.parse(new TextDecoder().decode(b64urlToBytes(s))) as T;\n}\n\nexport interface FirebaseClaims {\n sub: string;\n email?: string;\n email_verified?: boolean;\n name?: string;\n picture?: string;\n aud: string;\n iss: string;\n exp: number;\n iat: number;\n}\n\n/** Firebase ID 토큰을 검증하고 클레임을 돌려준다. */\nexport async function verifyIdToken(token: string): Promise {\n const parts = token.split(\".\");\n if (parts.length !== 3) throw unauthorized(\"토큰 형식이 올바르지 않습니다.\");\n\n const [headerB64, payloadB64, sigB64] = parts;\n\n // 깨진 토큰은 base64/JSON 파싱에서 터진다. 그대로 두면 500 이 나가므로 401 로 바꾼다.\n let header: { alg: string; kid: string };\n try {\n header = b64urlToJson<{ alg: string; kid: string }>(headerB64);\n } catch {\n throw unauthorized(\"토큰을 해석할 수 없습니다. 다시 로그인해 주세요.\");\n }\n if (header.alg !== \"RS256\") throw unauthorized(\"지원하지 않는 서명 알고리즘입니다.\");\n\n const keys = await getKeys();\n const key = keys.get(header.kid);\n if (!key) throw unauthorized(\"토큰 서명 키를 찾을 수 없습니다.\");\n\n let valid: boolean;\n try {\n valid = await crypto.subtle.verify(\n \"RSASSA-PKCS1-v1_5\",\n key,\n b64urlToBytes(sigB64),\n new TextEncoder().encode(`${headerB64}.${payloadB64}`),\n );\n } catch {\n throw unauthorized(\"토큰 서명을 확인할 수 없습니다.\");\n }\n if (!valid) throw unauthorized(\"토큰 서명이 올바르지 않습니다.\");\n\n let claims: FirebaseClaims;\n try {\n claims = b64urlToJson(payloadB64);\n } catch {\n throw unauthorized(\"토큰을 해석할 수 없습니다. 다시 로그인해 주세요.\");\n }\n const projectId = CONFIG.firebaseProjectId;\n const now = Math.floor(Date.now() / 1000);\n\n if (!projectId) throw unauthorized(\"서버에 FIREBASE_PROJECT_ID 가 설정되지 않았습니다.\");\n if (claims.aud !== projectId) throw unauthorized(\"토큰 대상(aud)이 일치하지 않습니다.\");\n if (claims.iss !== `https://securetoken.google.com/${projectId}`) {\n throw unauthorized(\"토큰 발급자(iss)가 일치하지 않습니다.\");\n }\n if (claims.exp <= now) throw unauthorized(\"토큰이 만료되었습니다. 다시 로그인해 주세요.\");\n if (claims.iat > now + 300) throw unauthorized(\"토큰 발급 시각이 올바르지 않습니다.\");\n if (!claims.sub) throw unauthorized(\"토큰에 사용자 식별자가 없습니다.\");\n\n return claims;\n}\n\ninterface UserRow {\n id: string;\n email: string | null;\n name: string;\n role: \"student\" | \"coach\" | \"admin\";\n status: string;\n}\n\n/** 첫 로그인이면 만들고, 있으면 마지막 로그인 시각만 갱신한다. */\nasync function upsertUser(claims: {\n sub: string;\n email?: string;\n name?: string;\n picture?: string;\n}): Promise {\n const now = nowIso();\n const email = claims.email?.toLowerCase() ?? null;\n const existing = await one(\n \"SELECT id, email, name, role, status, photo_url FROM users WHERE id = ?\",\n [claims.sub],\n );\n\n if (existing) {\n await exec(\"UPDATE users SET last_login_at = ?, updated_at = ? WHERE id = ?\", [\n now,\n now,\n claims.sub,\n ]);\n /*\n * ★ 비어 있는 항목만 토큰 값으로 채운다.\n * 이메일/비밀번호 가입은 계정이 먼저 만들어지고 displayName 이 나중에 붙는다.\n * 그래서 첫 로그인 토큰에는 name 이 없고, 이 분기에서 갱신하지 않으면\n * 화면에 영원히 \"이름 없음\" 으로 남는다(실제로 그랬다).\n * 이미 값이 있으면 덮지 않는다 — 운영자가 고친 이름을 로그인할 때마다 되돌리면 안 된다.\n */\n if (!existing.name && claims.name) {\n await exec(\"UPDATE users SET name = ? WHERE id = ?\", [claims.name, claims.sub]);\n existing.name = claims.name;\n }\n if (!existing.photo_url && claims.picture) {\n await exec(\"UPDATE users SET photo_url = ? WHERE id = ?\", [claims.picture, claims.sub]);\n }\n\n // 운영자 이메일이 나중에 추가된 경우를 위해 매 로그인마다 승격 여부를 본다.\n if (email && CONFIG.adminEmails.includes(email) && existing.role !== \"admin\") {\n await exec(\"UPDATE users SET role = 'admin' WHERE id = ?\", [claims.sub]);\n existing.role = \"admin\";\n }\n return existing;\n }\n\n const role = email && CONFIG.adminEmails.includes(email) ? \"admin\" : \"student\";\n await exec(\n `INSERT INTO users (id, email, name, role, status, photo_url, created_at, updated_at, last_login_at)\n VALUES (?, ?, ?, ?, 'active', ?, ?, ?, ?)`,\n [claims.sub, email, claims.name ?? \"\", role, claims.picture ?? null, now, now, now],\n );\n\n return { id: claims.sub, email, name: claims.name ?? \"\", role, status: \"active\" };\n}\n\n/**\n * 요청에서 사용자를 뽑는다. 토큰이 없으면 null.\n * AUTH_MODE=dev 이면 \"dev::\" 형태를 허용한다(로컬 전용).\n */\nexport async function resolveUser(req: Request): Promise {\n const header = req.headers.get(\"authorization\") ?? \"\";\n if (!header.toLowerCase().startsWith(\"bearer \")) return null;\n const token = header.slice(7).trim();\n if (!token) return null;\n\n if (CONFIG.authMode === \"dev\" && token.startsWith(\"dev:\")) {\n const [, uid, role = \"student\"] = token.split(\":\");\n const row = await upsertUser({ sub: uid, email: `${uid}@dev.local`, name: `dev-${uid}` });\n if (role === \"admin\" || role === \"coach\") {\n await exec(\"UPDATE users SET role = ? WHERE id = ?\", [role, uid]);\n row.role = role as UserRow[\"role\"];\n }\n return { id: row.id, email: row.email, name: row.name, role: row.role };\n }\n\n const claims = await verifyIdToken(token);\n const row = await upsertUser(claims);\n if (row.status === \"left\") throw forbidden(\"이용이 종료된 계정입니다. 관리자에게 문의하세요.\");\n return { id: row.id, email: row.email, name: row.name, role: row.role };\n}\n\nexport function requireStaff(user: AuthUser): void {\n if (user.role !== \"admin\" && user.role !== \"coach\") {\n throw forbidden(\"관리자 또는 관리쌤만 사용할 수 있습니다.\");\n }\n}\n\nexport function requireAdmin(user: AuthUser): void {\n if (user.role !== \"admin\") throw forbidden(\"관리자만 사용할 수 있습니다.\");\n}\n\nexport const isStaff = (user: AuthUser) => user.role === \"admin\" || user.role === \"coach\";\n",[],[154,342],"f1a202e2-b1b4-42ba-aec0-af1add9fd545",["D",1788831524911],[174,175,176,177,178,179,180],[154],"3f856cc0-45ce-4120-bb92-946ca18a1a71","seed.ts","08c5d4ca-ab26-11f1-ba0e-1607ee4eb77e","07c0dc82-ab26-11f1-8306-1607ee4eb77e",14,["D",1788831526931],"/**\n * 로컬 개발용 시드. 운영에서는 실행하지 않는다.\n * deno task seed\n */\nimport \"../lib/env.ts\";\nimport { exec, one, query, uuid } from \"../lib/sqlite.ts\";\nimport { addDays, nowIso, toLocalDate } from \"../lib/time.ts\";\nimport { migrate } from \"./migrate.ts\";\nimport { DEFAULT_SUBJECTS } from \"./schema.ts\";\n\nconst STUDENT_ID = \"dev-student\";\nconst ADMIN_ID = \"dev-admin\";\n\nasync function upsertUser(\n id: string,\n name: string,\n role: string,\n extra: Record = {},\n) {\n const now = nowIso();\n const exists = await one(\"SELECT id FROM users WHERE id = ?\", [id]);\n if (exists) {\n await exec(\"UPDATE users SET name = ?, role = ?, updated_at = ? WHERE id = ?\", [\n name,\n role,\n now,\n id,\n ]);\n return;\n }\n await exec(\n `INSERT INTO users (id, email, name, role, status, school, grade, target, seat_no, created_at, updated_at)\n VALUES (?, ?, ?, ?, 'active', ?, ?, ?, ?, ?, ?)`,\n [\n id,\n `${id}@dev.local`,\n name,\n role,\n extra.school ?? null,\n extra.grade ?? null,\n extra.target ?? null,\n extra.seat ?? null,\n now,\n now,\n ],\n );\n}\n\nconst result = await migrate();\nconsole.log(\"마이그레이션:\", result.applied.length ? result.applied.join(\", \") : \"이미 적용됨\");\n\nawait upsertUser(ADMIN_ID, \"김관리\", \"admin\");\nawait upsertUser(STUDENT_ID, \"이학생\", \"student\", {\n school: \"로드맵고\",\n grade: \"고2\",\n target: \"서울대 컴퓨터공학과\",\n seat: \"A-14\",\n});\n\n// 과목\nconst subjectIds: string[] = [];\nfor (let i = 0; i < DEFAULT_SUBJECTS.length; i++) {\n const s = DEFAULT_SUBJECTS[i];\n const existing = await one<{ id: string }>(\n \"SELECT id FROM subjects WHERE user_id = ? AND name = ?\",\n [STUDENT_ID, s.name],\n );\n if (existing) {\n subjectIds.push(existing.id);\n continue;\n }\n const id = uuid();\n await exec(\n \"INSERT INTO subjects (id, user_id, name, color, sort_order, archived, created_at) VALUES (?, ?, ?, ?, ?, 0, ?)\",\n [id, STUDENT_ID, s.name, s.color, i, nowIso()],\n );\n subjectIds.push(id);\n}\n\n// 최근 14일 계획·세션·일기\nconst today = toLocalDate();\nconst already = await query(\"SELECT id FROM plans WHERE user_id = ? LIMIT 1\", [STUDENT_ID]);\nif (already.length) {\n console.log(\"이미 시드 데이터가 있습니다. 계획 생성은 건너뜁니다.\");\n} else {\n const titles = [\n [\"수능특강 독서 3강\", 60],\n [\"수학 미적분 유형편 20문항\", 90],\n [\"영단어 Day 12\", 30],\n [\"한국사 근현대 개념\", 45],\n [\"기출 오답 정리\", 50],\n ] as const;\n\n for (let d = 13; d >= 0; d--) {\n const date = addDays(today, -d);\n const count = 2 + (d % 3);\n for (let i = 0; i < count; i++) {\n const [title, goal] = titles[(d + i) % titles.length];\n const planId = uuid();\n const done = Math.random() > 0.3;\n const actual = done ? goal + Math.round((Math.random() - 0.5) * 20) : Math.round(goal * 0.4);\n const now = nowIso();\n\n await exec(\n `INSERT INTO plans (id, user_id, date, subject_id, title, goal_minutes, actual_minutes, status, priority, sort_order, created_by, created_at, updated_at)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, 2, ?, ?, ?, ?)`,\n [\n planId,\n STUDENT_ID,\n date,\n subjectIds[(d + i) % subjectIds.length],\n title,\n goal,\n Math.max(0, actual),\n done ? \"done\" : \"todo\",\n i,\n STUDENT_ID,\n now,\n now,\n ],\n );\n if (actual > 0) {\n await exec(\n `INSERT INTO study_sessions (id, user_id, plan_id, date, started_at, ended_at, minutes, source, created_at)\n VALUES (?, ?, ?, ?, ?, ?, ?, 'manual', ?)`,\n [\n uuid(),\n STUDENT_ID,\n planId,\n date,\n `${date}T10:00:00.000Z`,\n `${date}T11:00:00.000Z`,\n Math.max(0, actual),\n now,\n ],\n );\n }\n }\n\n if (d % 2 === 0) {\n await exec(\n `INSERT INTO diaries (id, user_id, date, mood, focus_score, content, keep_note, problem_note, try_note, created_at, updated_at)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n [\n uuid(),\n STUDENT_ID,\n date,\n 3 + (d % 3 === 0 ? 1 : 0),\n 3 + (d % 2),\n \"오늘은 수학에 시간을 많이 썼다. 미적분 유형이 아직 손에 안 익는다.\",\n \"계획한 분량을 거의 다 했다.\",\n \"쉬는 시간이 길어졌다.\",\n \"타이머를 켜고 50분 단위로 끊어보기.\",\n nowIso(),\n nowIso(),\n ],\n );\n }\n }\n console.log(\"계획·세션·일기 시드 완료\");\n}\n\nconsole.log(`\n시드 완료.\n 학생 토큰 : dev:${STUDENT_ID}:student\n 관리자 토큰: dev:${ADMIN_ID}:admin\n .env 에 AUTH_MODE=dev 로 두면 이 토큰으로 로그인할 수 있습니다.\n`);\n",[],[362,354],"db","4e4d5d4e-b2ed-4987-a594-9336aa870866","schema.ts","08b2b3b8-ab26-11f1-ba0e-1607ee4eb77e",12,["D",1788831526757],"/**\n * 스키마 SSOT.\n *\n * 규칙\n * - id 는 전부 TEXT(UUID). 자동증가 정수를 쓰지 않는다(환경 간 이관이 쉬움).\n * - 날짜는 'YYYY-MM-DD'(KST 기준), 시각은 ISO8601 UTC 문자열.\n * - 마이그레이션은 append-only. 기존 항목을 수정하지 말고 뒤에 추가한다.\n */\n\nexport interface Migration {\n id: string;\n statements: string[];\n}\n\nexport const MIGRATIONS: Migration[] = [\n {\n id: \"0001_init\",\n statements: [\n `CREATE TABLE IF NOT EXISTS users (\n id TEXT PRIMARY KEY, -- Firebase uid\n email TEXT,\n name TEXT NOT NULL DEFAULT '',\n phone TEXT,\n role TEXT NOT NULL DEFAULT 'student', -- student | coach | admin\n status TEXT NOT NULL DEFAULT 'active', -- active | paused | left\n school TEXT,\n grade TEXT,\n target TEXT, -- 목표 대학/시험\n seat_no TEXT, -- 독서실 좌석\n photo_url TEXT,\n coach_id TEXT, -- 담당 관리쌤\n created_at TEXT NOT NULL,\n updated_at TEXT NOT NULL,\n last_login_at TEXT\n )`,\n `CREATE INDEX IF NOT EXISTS idx_users_role ON users(role, status)`,\n `CREATE INDEX IF NOT EXISTS idx_users_coach ON users(coach_id)`,\n\n `CREATE TABLE IF NOT EXISTS subjects (\n id TEXT PRIMARY KEY,\n user_id TEXT NOT NULL,\n name TEXT NOT NULL,\n color TEXT NOT NULL DEFAULT '#4f46e5',\n sort_order INTEGER NOT NULL DEFAULT 0,\n archived INTEGER NOT NULL DEFAULT 0,\n created_at TEXT NOT NULL\n )`,\n `CREATE INDEX IF NOT EXISTS idx_subjects_user ON subjects(user_id, archived, sort_order)`,\n\n `CREATE TABLE IF NOT EXISTS plans (\n id TEXT PRIMARY KEY,\n user_id TEXT NOT NULL,\n date TEXT NOT NULL, -- YYYY-MM-DD\n subject_id TEXT,\n title TEXT NOT NULL,\n goal_minutes INTEGER NOT NULL DEFAULT 0,\n actual_minutes INTEGER NOT NULL DEFAULT 0,\n status TEXT NOT NULL DEFAULT 'todo', -- todo | doing | done | skipped\n priority INTEGER NOT NULL DEFAULT 2, -- 1 높음 2 보통 3 낮음\n memo TEXT,\n sort_order INTEGER NOT NULL DEFAULT 0,\n created_by TEXT NOT NULL, -- 학생 본인 또는 관리쌤 uid\n completed_at TEXT,\n created_at TEXT NOT NULL,\n updated_at TEXT NOT NULL\n )`,\n `CREATE INDEX IF NOT EXISTS idx_plans_user_date ON plans(user_id, date)`,\n `CREATE INDEX IF NOT EXISTS idx_plans_subject ON plans(subject_id)`,\n\n `CREATE TABLE IF NOT EXISTS study_sessions (\n id TEXT PRIMARY KEY,\n user_id TEXT NOT NULL,\n plan_id TEXT,\n date TEXT NOT NULL,\n started_at TEXT NOT NULL,\n ended_at TEXT,\n minutes INTEGER NOT NULL DEFAULT 0,\n source TEXT NOT NULL DEFAULT 'timer', -- timer | manual\n created_at TEXT NOT NULL\n )`,\n `CREATE INDEX IF NOT EXISTS idx_sessions_user_date ON study_sessions(user_id, date)`,\n `CREATE INDEX IF NOT EXISTS idx_sessions_open ON study_sessions(user_id, ended_at)`,\n\n `CREATE TABLE IF NOT EXISTS diaries (\n id TEXT PRIMARY KEY,\n user_id TEXT NOT NULL,\n date TEXT NOT NULL,\n mood INTEGER, -- 1..5\n focus_score INTEGER, -- 1..5\n content TEXT NOT NULL DEFAULT '',\n keep_note TEXT, -- 잘한 점\n problem_note TEXT, -- 아쉬운 점\n try_note TEXT, -- 내일 할 것\n coach_comment TEXT,\n coach_id TEXT,\n commented_at TEXT,\n created_at TEXT NOT NULL,\n updated_at TEXT NOT NULL\n )`,\n `CREATE UNIQUE INDEX IF NOT EXISTS uq_diary_user_date ON diaries(user_id, date)`,\n\n `CREATE TABLE IF NOT EXISTS schedule_events (\n id TEXT PRIMARY KEY,\n user_id TEXT, -- NULL 이면 전체 공지 일정\n title TEXT NOT NULL,\n type TEXT NOT NULL DEFAULT 'etc', -- exam | class | consult | holiday | etc\n start_at TEXT NOT NULL, -- ISO UTC\n end_at TEXT,\n all_day INTEGER NOT NULL DEFAULT 0,\n location TEXT,\n memo TEXT,\n created_by TEXT NOT NULL,\n created_at TEXT NOT NULL,\n updated_at TEXT NOT NULL\n )`,\n `CREATE INDEX IF NOT EXISTS idx_events_user_start ON schedule_events(user_id, start_at)`,\n\n `CREATE TABLE IF NOT EXISTS chat_threads (\n id TEXT PRIMARY KEY,\n user_id TEXT NOT NULL,\n title TEXT NOT NULL DEFAULT '새 대화',\n created_at TEXT NOT NULL,\n updated_at TEXT NOT NULL\n )`,\n `CREATE INDEX IF NOT EXISTS idx_threads_user ON chat_threads(user_id, updated_at DESC)`,\n\n `CREATE TABLE IF NOT EXISTS chat_messages (\n id TEXT PRIMARY KEY,\n thread_id TEXT NOT NULL,\n user_id TEXT NOT NULL,\n role TEXT NOT NULL, -- user | assistant\n content TEXT NOT NULL,\n tokens_in INTEGER NOT NULL DEFAULT 0,\n tokens_out INTEGER NOT NULL DEFAULT 0,\n created_at TEXT NOT NULL\n )`,\n `CREATE INDEX IF NOT EXISTS idx_messages_thread ON chat_messages(thread_id, created_at)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_user_day ON chat_messages(user_id, created_at)`,\n\n `CREATE TABLE IF NOT EXISTS consult_slots (\n id TEXT PRIMARY KEY,\n coach_id TEXT NOT NULL,\n start_at TEXT NOT NULL,\n end_at TEXT NOT NULL,\n capacity INTEGER NOT NULL DEFAULT 1,\n status TEXT NOT NULL DEFAULT 'open', -- open | closed\n memo TEXT,\n created_at TEXT NOT NULL\n )`,\n `CREATE INDEX IF NOT EXISTS idx_slots_start ON consult_slots(start_at, status)`,\n\n `CREATE TABLE IF NOT EXISTS consults (\n id TEXT PRIMARY KEY,\n user_id TEXT NOT NULL,\n slot_id TEXT,\n category TEXT NOT NULL DEFAULT 'study', -- study | plan | mental | career | etc\n topic TEXT NOT NULL,\n content TEXT NOT NULL DEFAULT '',\n mode TEXT NOT NULL DEFAULT 'offline', -- offline | online | phone\n preferred_at TEXT,\n status TEXT NOT NULL DEFAULT 'requested', -- requested | scheduled | done | canceled\n coach_id TEXT,\n scheduled_at TEXT,\n coach_note TEXT,\n student_feedback TEXT,\n created_at TEXT NOT NULL,\n updated_at TEXT NOT NULL\n )`,\n `CREATE INDEX IF NOT EXISTS idx_consults_user ON consults(user_id, created_at DESC)`,\n `CREATE INDEX IF NOT EXISTS idx_consults_status ON consults(status, created_at DESC)`,\n\n `CREATE TABLE IF NOT EXISTS notifications (\n id TEXT PRIMARY KEY,\n user_id TEXT NOT NULL,\n type TEXT NOT NULL,\n title TEXT NOT NULL,\n body TEXT,\n link TEXT,\n read_at TEXT,\n created_at TEXT NOT NULL\n )`,\n `CREATE INDEX IF NOT EXISTS idx_notif_user ON notifications(user_id, read_at, created_at DESC)`,\n\n `CREATE TABLE IF NOT EXISTS audit_logs (\n id TEXT PRIMARY KEY,\n actor_id TEXT,\n action TEXT NOT NULL,\n target TEXT,\n meta TEXT,\n created_at TEXT NOT NULL\n )`,\n `CREATE INDEX IF NOT EXISTS idx_audit_created ON audit_logs(created_at DESC)`,\n ],\n },\n];\n\nexport const DEFAULT_SUBJECTS: Array<{ name: string; color: string }> = [\n { name: \"국어\", color: \"#ef4444\" },\n { name: \"수학\", color: \"#3b82f6\" },\n { name: \"영어\", color: \"#10b981\" },\n { name: \"탐구\", color: \"#f59e0b\" },\n { name: \"기타\", color: \"#8b5cf6\" },\n];\n",[],[362,364],"42980979-1a80-46a5-b031-ae98396bf820","migrate.ts","08b84e36-ab26-11f1-84f5-1607ee4eb77e",13,["D",1788831526829],"/**\n * 마이그레이션 실행기.\n * 적용된 마이그레이션 id 를 _migrations 에 기록해 멱등하게 만든다.\n *\n * 로컬: deno task migrate\n * Val Town: /api/admin/migrate (관리자) 또는 이 파일을 한 번 실행하는 val\n */\nimport \"../lib/env.ts\";\nimport { exec, execScript, query } from \"../lib/sqlite.ts\";\nimport { nowIso } from \"../lib/time.ts\";\nimport { MIGRATIONS } from \"./schema.ts\";\n\nexport async function migrate(): Promise<{ applied: string[]; skipped: string[] }> {\n await exec(\n `CREATE TABLE IF NOT EXISTS _migrations (\n id TEXT PRIMARY KEY,\n applied_at TEXT NOT NULL\n )`,\n );\n\n const done = new Set(\n (await query<{ id: string }>(\"SELECT id FROM _migrations\")).map((r) => r.id),\n );\n\n const applied: string[] = [];\n const skipped: string[] = [];\n\n for (const m of MIGRATIONS) {\n if (done.has(m.id)) {\n skipped.push(m.id);\n continue;\n }\n await execScript(m.statements);\n await exec(\"INSERT INTO _migrations (id, applied_at) VALUES (?, ?)\", [m.id, nowIso()]);\n applied.push(m.id);\n }\n\n return { applied, skipped };\n}\n\nif (import.meta.main) {\n const result = await migrate();\n console.log(\"적용:\", result.applied.length ? result.applied.join(\", \") : \"(없음)\");\n console.log(\"건너뜀:\", result.skipped.length ? result.skipped.join(\", \") : \"(없음)\");\n}\n",[],[362,372],"1ab759e0-0152-48e1-8407-330dbb0c8088",["D",1788831525229],[170,171,172],[362],"1cf511e8-e749-44bd-bc68-e750233a04ea","daily.ts","08df94be-ab26-11f1-ba6d-1607ee4eb77e","07f16262-ab26-11f1-87f9-1607ee4eb77e",16,["D",1788831527099],"interval","/**\n * 매일 도는 크론 (Val Town Cron Val 로 등록. 권장 22:00 KST = 13:00 UTC).\n *\n * 하는 일\n * 1. 켜둔 채 잊은 타이머 정리 (12시간 상한으로 마감)\n * 2. 오늘 공부일기를 안 쓴 학생에게 알림\n * 3. 내일 상담이 잡힌 학생·관리쌤에게 알림\n *\n * Val Town 에서는 이 파일의 default export 가 스케줄러에 의해 호출된다.\n */\nimport \"../lib/env.ts\";\nimport { exec, query, uuid } from \"../lib/sqlite.ts\";\nimport { addDays, nowIso, toLocalDate } from \"../lib/time.ts\";\n\nasync function notify(userId: string, type: string, title: string, body: string, link: string) {\n await exec(\n \"INSERT INTO notifications (id, user_id, type, title, body, link, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)\",\n [uuid(), userId, type, title, body, link, nowIso()],\n );\n}\n\nexport default async function daily(): Promise<\n { closedTimers: number; diaryReminders: number; consultReminders: number }\n> {\n const today = toLocalDate();\n const now = nowIso();\n\n // 1) 방치된 타이머 마감\n const openSessions = await query<{ id: string; started_at: string; plan_id: string | null }>(\n \"SELECT id, started_at, plan_id FROM study_sessions WHERE ended_at IS NULL\",\n );\n for (const s of openSessions) {\n const raw = Math.round((Date.now() - new Date(s.started_at).getTime()) / 60000);\n const minutes = Math.max(0, Math.min(raw, 720));\n await exec(\"UPDATE study_sessions SET ended_at = ?, minutes = ? WHERE id = ?\", [\n now,\n minutes,\n s.id,\n ]);\n if (s.plan_id) {\n await exec(\n `UPDATE plans SET actual_minutes =\n (SELECT COALESCE(SUM(minutes),0) FROM study_sessions WHERE plan_id = ? AND ended_at IS NOT NULL)\n WHERE id = ?`,\n [s.plan_id, s.plan_id],\n );\n }\n }\n\n // 2) 오늘 공부한 기록은 있는데 일기를 안 쓴 학생\n const missingDiary = await query<{ id: string; name: string }>(\n `SELECT u.id, u.name FROM users u\n WHERE u.role = 'student' AND u.status = 'active'\n AND EXISTS (SELECT 1 FROM study_sessions s WHERE s.user_id = u.id AND s.date = ?)\n AND NOT EXISTS (SELECT 1 FROM diaries d WHERE d.user_id = u.id AND d.date = ? AND d.content <> '')`,\n [today, today],\n );\n for (const u of missingDiary) {\n await notify(\n u.id,\n \"diary_reminder\",\n \"오늘 공부일기를 아직 안 썼어요\",\n \"5분이면 충분해요. 오늘 하루를 정리해 볼까요?\",\n `/diary.html?date=${today}`,\n );\n }\n\n // 3) 내일 상담 안내\n const tomorrow = addDays(today, 1);\n const consults = await query<\n { id: string; user_id: string; coach_id: string | null; topic: string; scheduled_at: string }\n >(\n `SELECT id, user_id, coach_id, topic, scheduled_at FROM consults\n WHERE status = 'scheduled' AND scheduled_at >= ? AND scheduled_at < ?`,\n [`${tomorrow}T00:00:00.000Z`, `${addDays(tomorrow, 1)}T00:00:00.000Z`],\n );\n for (const c of consults) {\n await notify(c.user_id, \"consult_reminder\", \"내일 상담이 있어요\", c.topic, \"/consult.html\");\n if (c.coach_id) {\n await notify(\n c.coach_id,\n \"consult_reminder\",\n \"내일 상담 일정\",\n c.topic,\n \"/admin.html#consults\",\n );\n }\n }\n\n return {\n closedTimers: openSessions.length,\n diaryReminders: missingDiary.length,\n consultReminders: consults.length,\n };\n}\n\nif (import.meta.main) console.log(await daily());\n",[],[393,384],"crons","130359c1-3f85-42d1-853e-43f5d0e94aab",3,["D",1788831525524],[168],[393],["D",1788831523479],["D",1789529498250],{"_11":12,"_123":69},{"_11":12,"_119":12,"_68":69,"_30":67,"_120":121,"_122":24},{"_38":110,"_13":16,"_32":35,"_111":71,"_112":-5,"_113":-5,"_114":-5,"_115":-5,"_116":-5,"_117":14,"_118":12},"ctx",{"_51":-5,"_142":437,"_438":439,"_440":441,"_442":24,"_443":24,"_444":24,"_445":84,"_446":-5,"_447":-7,"_448":-7,"_449":26,"_450":24,"_451":24,"_452":-5,"_453":24,"_454":24,"_455":24,"_456":-5},"ENV",{"_408":409,"_410":411,"_412":16,"_413":414,"_415":416,"_417":418,"_419":420,"_421":422,"_423":424,"_425":426,"_427":428,"_429":430,"_431":432,"_433":434,"_435":436},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","e87eb04e707cf64e7e532c8e8ec395f209e17f15","RENDER_GIT_BRANCH","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]

History · claude.ts