56d9736597
All checks were successful
Deploy to Server / deploy (push) Successful in 5s
- 返回媒体总数、满分作品数、平均评分 - 按类型统计各类型作品数量 - 返回最新添加的5部作品 - 便于前端展示数据统计信息
268 lines
7.0 KiB
TypeScript
268 lines
7.0 KiB
TypeScript
import { Hono } from "hono";
|
||
import { db } from "../db/index.ts";
|
||
import type { JwtVariables } from "hono/jwt";
|
||
import { authMiddleware } from "../auth.ts";
|
||
|
||
const media = new Hono<{ Variables: JwtVariables }>();
|
||
|
||
// 查询满分（10分）作品，不需要JWT验证
|
||
media.get("/perfect", async (c) => {
|
||
try {
|
||
const perfectList = await db
|
||
.prepare("SELECT * FROM media WHERE rating = 10")
|
||
.all();
|
||
return c.json({
|
||
code: 0,
|
||
data: perfectList,
|
||
message: "Success",
|
||
});
|
||
} catch (error: any) {
|
||
return c.json({ code: 1, data: {}, message: error.message }, 500);
|
||
}
|
||
});
|
||
|
||
media.use("/*", authMiddleware);
|
||
|
||
// 获取所有媒体记录
|
||
media.get("/list", async (c) => {
|
||
try {
|
||
const mediaList = await db.prepare("SELECT * FROM media").all();
|
||
return c.json({
|
||
code: 0,
|
||
data: mediaList,
|
||
message: "Success",
|
||
});
|
||
} catch (error: any) {
|
||
return c.json({ code: 1, data: {}, message: error.message }, 500);
|
||
}
|
||
});
|
||
|
||
// 创建新的媒体记录
|
||
media.post("/create", async (c) => {
|
||
try {
|
||
const data = await c.req.json();
|
||
const { title, type, rating, notes, platform, date } = data;
|
||
|
||
const result = await db
|
||
.prepare(
|
||
`
|
||
INSERT INTO media (title, type, rating, notes, platform, date, created_at, updated_at)
|
||
VALUES (?, ?, ?, ?, ?, ?, NOW(), NOW())
|
||
RETURNING id
|
||
`
|
||
)
|
||
.run(title, type, rating, notes, platform, date);
|
||
|
||
const newMedia = await db
|
||
.prepare("SELECT * FROM media WHERE id = ?")
|
||
.get(result.lastInsertRowid);
|
||
|
||
return c.json(
|
||
{
|
||
code: 0,
|
||
data: newMedia,
|
||
message: "Created successfully",
|
||
},
|
||
201
|
||
);
|
||
} catch (error: any) {
|
||
return c.json({ code: 2, data: {}, message: error.message }, 500);
|
||
}
|
||
});
|
||
|
||
// 更新媒体记录
|
||
media.put("/updateById/:id", async (c) => {
|
||
try {
|
||
const id = c.req.param("id");
|
||
const data = await c.req.json();
|
||
const { title, type, rating, notes, platform, date } = data;
|
||
|
||
await db
|
||
.prepare(
|
||
`
|
||
UPDATE media
|
||
SET title = ?, type = ?, rating = ?, notes = ?, platform = ?, date = ?, updated_at = NOW()
|
||
WHERE id = ?
|
||
`
|
||
)
|
||
.run(title, type, rating, notes, platform, date, id);
|
||
|
||
const updatedMedia = await db
|
||
.prepare("SELECT * FROM media WHERE id = ?")
|
||
.get(id);
|
||
|
||
if (!updatedMedia) {
|
||
return c.json({ code: 1, data: {}, message: "Media not found" }, 404);
|
||
}
|
||
|
||
return c.json({
|
||
code: 0,
|
||
data: updatedMedia,
|
||
message: "Updated successfully",
|
||
});
|
||
} catch (error: any) {
|
||
return c.json({ code: 2, data: {}, message: error.message }, 500);
|
||
}
|
||
});
|
||
|
||
// 删除媒体记录
|
||
media.delete("/deleteById/:id", async (c) => {
|
||
try {
|
||
const id = c.req.param("id");
|
||
await db.prepare("DELETE FROM media WHERE id = ?").run(id);
|
||
return c.json({ code: 0, data: {}, message: "Deleted successfully" });
|
||
} catch (error: any) {
|
||
return c.json({ code: 2, data: {}, message: error.message }, 500);
|
||
}
|
||
});
|
||
|
||
// 分页查询媒体记录
|
||
media.get("/page", async (c) => {
|
||
try {
|
||
const type = c.req.query("type");
|
||
const currentPage = parseInt(c.req.query("currentPage") || "1");
|
||
const pageSize = parseInt(c.req.query("pageSize") || "10");
|
||
const title = c.req.query("title") || "";
|
||
const startDate = c.req.query("startDate");
|
||
const endDate = c.req.query("endDate");
|
||
const sortBy = c.req.query("sortBy") || "date";
|
||
const sortType = c.req.query("sortType") || "desc";
|
||
|
||
if (!type) {
|
||
return c.json({ code: 1, data: {}, message: "Type is required" }, 400);
|
||
}
|
||
|
||
let query = "SELECT * FROM media WHERE type = ?";
|
||
const params = [type];
|
||
|
||
if (title) {
|
||
query += " AND title LIKE ?";
|
||
params.push(`%${title}%`);
|
||
}
|
||
if (startDate) {
|
||
query += " AND date >= ?";
|
||
params.push(startDate);
|
||
}
|
||
if (endDate) {
|
||
query += " AND date <= ?";
|
||
params.push(endDate);
|
||
}
|
||
|
||
// 添加排序
|
||
if (sortBy === "date") {
|
||
query += ` ORDER BY date ${sortType.toUpperCase()}`;
|
||
} else if (sortBy === "score") {
|
||
query += ` ORDER BY rating ${sortType.toUpperCase()}`;
|
||
}
|
||
|
||
// 添加分页
|
||
const offset = (currentPage - 1) * pageSize;
|
||
query += " LIMIT ? OFFSET ?";
|
||
params.push(pageSize.toString(), offset.toString());
|
||
|
||
// 获取总数
|
||
const countQuery = query
|
||
.replace("SELECT *", "SELECT COUNT(*) as total")
|
||
.replace(/ORDER BY.*$/, "") // 移除 ORDER BY 子句
|
||
.replace(/LIMIT.*$/, ""); // 移除 LIMIT 和 OFFSET 子句
|
||
const totalResult = await db
|
||
.prepare(countQuery)
|
||
.get(...params.slice(0, -2));
|
||
const total = totalResult?.total || 0;
|
||
|
||
// 获取分页数据
|
||
const mediaList = await db.prepare(query).all(...params);
|
||
|
||
// 格式化 date 字段，只返回日期部分（YYYY-MM-DD）
|
||
const formattedList = mediaList.map((item: any) => {
|
||
if (item.date) {
|
||
// 如果是 Date 对象或字符串，格式化为 YYYY-MM-DD
|
||
const date = new Date(item.date);
|
||
if (!isNaN(date.getTime())) {
|
||
item.date = date.toISOString().split("T")[0];
|
||
}
|
||
}
|
||
return item;
|
||
});
|
||
|
||
return c.json({
|
||
code: 0,
|
||
data: {
|
||
list: formattedList,
|
||
total,
|
||
currentPage,
|
||
pageSize,
|
||
},
|
||
message: "Success",
|
||
});
|
||
} catch (error: any) {
|
||
return c.json({ code: 1, data: {}, message: error.message }, 500);
|
||
}
|
||
});
|
||
|
||
// 获取媒体统计信息
|
||
media.get("/stats", async (c) => {
|
||
try {
|
||
// 获取总数
|
||
const totalResult = await db
|
||
.prepare("SELECT COUNT(*) as total FROM media")
|
||
.get();
|
||
const total = totalResult?.total || 0;
|
||
|
||
// 获取满分作品数
|
||
const perfectResult = await db
|
||
.prepare("SELECT COUNT(*) as count FROM media WHERE rating = 10")
|
||
.get();
|
||
const perfectCount = perfectResult?.count || 0;
|
||
|
||
// 按类型统计
|
||
const typeStats = await db
|
||
.prepare(
|
||
"SELECT type, COUNT(*) as count FROM media GROUP BY type ORDER BY count DESC"
|
||
)
|
||
.all();
|
||
|
||
// 获取平均评分
|
||
const avgRatingResult = await db
|
||
.prepare("SELECT AVG(rating) as avg FROM media WHERE rating IS NOT NULL")
|
||
.get();
|
||
const avgRating = avgRatingResult?.avg
|
||
? parseFloat(avgRatingResult.avg).toFixed(2)
|
||
: "0.00";
|
||
|
||
// 获取最新添加的作品
|
||
const latestResult = await db
|
||
.prepare(
|
||
"SELECT title, type, rating, date FROM media ORDER BY created_at DESC LIMIT 5"
|
||
)
|
||
.all();
|
||
|
||
// 格式化最新作品的日期
|
||
const latest = latestResult.map((item: any) => {
|
||
if (item.date) {
|
||
const date = new Date(item.date);
|
||
if (!isNaN(date.getTime())) {
|
||
item.date = date.toISOString().split("T")[0];
|
||
}
|
||
}
|
||
return item;
|
||
});
|
||
|
||
return c.json({
|
||
code: 0,
|
||
data: {
|
||
total,
|
||
perfectCount,
|
||
avgRating,
|
||
typeStats,
|
||
latest,
|
||
},
|
||
message: "Success",
|
||
});
|
||
} catch (error: any) {
|
||
return c.json({ code: 1, data: {}, message: error.message }, 500);
|
||
}
|
||
});
|
||
|
||
export default media;
|