server.js

const express = require('express');
const sqlite3 = require('sqlite3').verbose();
const bodyParser = require('body-parser');
const session = require('express-session');
const SQLiteStore = require('connect-sqlite3')(session);
const bcrypt = require('bcryptjs');
const moment = require('moment');
const path = require('path');
const nodemailer = require('nodemailer');
const schedule = require('node-schedule');
const xlsx = require('xlsx');
const fs = require('fs');

const app = express();
const port = 3000;

// === 数据库初始化 ===
const dbPath = process.env.DB_PATH || path.join(__dirname, 'data', 'database.db');
fs.mkdirSync(path.dirname(dbPath), { recursive: true });
const db = new sqlite3.Database(dbPath);

db.serialize(() => {
    // 用户表
    db.run(`CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE,
        real_name TEXT,
        student_id TEXT UNIQUE,
        phone TEXT UNIQUE,
        password TEXT,
        role TEXT DEFAULT 'user' 
    )`);
    // 预约表
    db.run(`CREATE TABLE IF NOT EXISTS bookings (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER,
        date TEXT,
        slot_type TEXT, 
        own_filament INTEGER,
        print_type TEXT,
        weight REAL,
        cost REAL,
        file_name TEXT,
        purpose TEXT,
        status TEXT DEFAULT 'booked',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY(user_id) REFERENCES users(id)
    )`);
    // 设置表 (用于存储打印机全局状态)
    db.run(`CREATE TABLE IF NOT EXISTS settings (
        key TEXT UNIQUE,
        value TEXT
    )`);

    // 周报表
    db.run(`CREATE TABLE IF NOT EXISTS weekly_reports (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        start_date TEXT,
        end_date TEXT,
        file_path TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`);

    // 初始化超级管理员 (密码: admin123)
    const adminPass = bcrypt.hashSync('admin123', 10);
    db.run(`INSERT OR IGNORE INTO users (username, real_name, student_id, phone, password, role) 
            VALUES ('admin', '总管理员', '000000', '10000000000', ?, 'super_admin')`, [adminPass]);
    
    // 初始化打印机暂停状态
    db.run(`INSERT OR IGNORE INTO settings (key, value) VALUES ('printer_paused', 'false')`);

    // 数据库迁移：添加新字段 (如果列已存在会报错，忽略即可)
    db.run("ALTER TABLE bookings ADD COLUMN file_name TEXT", () => {});
    db.run("ALTER TABLE bookings ADD COLUMN purpose TEXT", () => {});
    db.run("ALTER TABLE bookings ADD COLUMN is_paid INTEGER DEFAULT 0", () => {});
    db.run("ALTER TABLE bookings ADD COLUMN rejection_reason TEXT", () => {});
});

// === 中间件配置 ===
app.set('view engine', 'ejs');
app.use(express.static('public'));
app.use(bodyParser.urlencoded({ extended: true }));
app.use(session({
    store: new SQLiteStore({
        db: path.basename(dbPath),
        dir: path.dirname(dbPath)
    }),
    secret: 'tianma-secret-key',
    resave: false,
    saveUninitialized: false,
    cookie: { maxAge: 7 * 24 * 60 * 60 * 1000 } // 1 week
}));

// 全局变量中间件
app.use((req, res, next) => {
    res.locals.user = req.session.user;
    next();
});

// === 辅助函数 ===
function requireLogin(req, res, next) {
    if (!req.session.user) return res.redirect('/login');
    next();
}

function requireAdmin(req, res, next) {
    if (!req.session.user || (req.session.user.role !== 'admin' && req.session.user.role !== 'super_admin')) {
        return res.status(403).send('无权访问');
    }
    next();
}

// === 路由 ===

// 首页
app.get('/', (req, res) => {
    // 获取打印机状态
    db.get("SELECT value FROM settings WHERE key = 'printer_paused'", (err, row) => {
        const isPaused = row && row.value === 'true';
        
        // 计算逻辑状态
        const now = moment();
        const currentDate = now.format('YYYY-MM-DD');
        const currentTime = now.format('HH:mm');
        let logicStatus = '空闲'; // 默认
        let cssClass = 'status-idle';

        if (isPaused) {
            logicStatus = '暂停使用';
            cssClass = 'status-paused';
            renderHome(res, logicStatus, cssClass);
        } else {
            // 检查当前时间是否有人预约
            let currentSlot = null;
            if (currentTime >= '08:00' && currentTime <= '11:30') currentSlot = 'AM';
            if (currentTime >= '13:30' && currentTime <= '17:00') currentSlot = 'PM';

            if (currentSlot) {
                db.get("SELECT * FROM bookings WHERE date = ? AND slot_type = ?", [currentDate, currentSlot], (err, booking) => {
                    if (booking) {
                        if (booking.status === 'running') {
                            logicStatus = '正在运行';
                            cssClass = 'status-running';
                        } else {
                            logicStatus = '已预约';
                            cssClass = 'status-reserved';
                        }
                    }
                    renderHome(res, logicStatus, cssClass);
                });
            } else {
                renderHome(res, logicStatus, cssClass);
            }
        }
    });
});

function renderHome(res, status, cssClass) {
    res.render('index', { printerStatus: status, statusClass: cssClass });
}

// 登录页面
app.get('/login', (req, res) => res.render('login'));

// 登录处理
app.post('/login', (req, res) => {
    const { login_id, password } = req.body;
    // 四选一查询
    db.get(`SELECT * FROM users WHERE phone = ? OR username = ? OR real_name = ? OR student_id = ?`, 
        [login_id, login_id, login_id, login_id], (err, user) => {
        if (err || !user) return res.render('login', { error: '用户不存在' });
        
        if (bcrypt.compareSync(password, user.password)) {
            req.session.user = user;
            return res.redirect('/dashboard');
        } else {
            return res.render('login', { error: '密码错误' });
        }
    });
});

// 注册页面
app.get('/register', (req, res) => res.render('register'));

// 注册处理
app.post('/register', (req, res) => {
    const { username, real_name, student_id, phone, password } = req.body;
    const hash = bcrypt.hashSync(password, 10);
    
    db.run(`INSERT INTO users (username, real_name, student_id, phone, password) VALUES (?, ?, ?, ?, ?)`,
        [username, real_name, student_id, phone, hash], function(err) {
        if (err) {
            console.error(err);
            return res.render('register', { error: '注册失败，用户名/学号/手机号可能已存在' });
        }
        res.redirect('/login');
    });
});

// 退出登录
app.get('/logout', (req, res) => {
    req.session.destroy();
    res.redirect('/');
});

// === 个人中心路由 ===

// 个人信息页面
app.get('/profile', requireLogin, (req, res) => {
    res.render('profile');
});

// 更新基本信息
app.post('/profile/update', requireLogin, (req, res) => {
    const { phone } = req.body;
    const userId = req.session.user.id;

    // 检查手机号是否被其他用户占用
    db.get("SELECT id FROM users WHERE phone = ? AND id != ?", [phone, userId], (err, row) => {
        if (row) {
            return res.render('profile', { infoError: '该手机号已被其他用户使用' });
        }

        db.run("UPDATE users SET phone = ? WHERE id = ?", [phone, userId], (err) => {
            if (err) {
                console.error(err);
                return res.render('profile', { infoError: '更新失败，请重试' });
            }
            // 更新 session 中的用户信息
            req.session.user.phone = phone;
            res.render('profile', { infoSuccess: '基本信息更新成功' });
        });
    });
});

// 修改密码
app.post('/profile/password', requireLogin, (req, res) => {
    const { old_password, new_password, confirm_password } = req.body;
    const userId = req.session.user.id;

    if (new_password !== confirm_password) {
        return res.render('profile', { pwdError: '两次输入的新密码不一致' });
    }

    db.get("SELECT password FROM users WHERE id = ?", [userId], (err, user) => {
        if (err || !user) return res.render('profile', { pwdError: '系统错误' });

        if (!bcrypt.compareSync(old_password, user.password)) {
            return res.render('profile', { pwdError: '当前密码错误' });
        }

        const hash = bcrypt.hashSync(new_password, 10);
        db.run("UPDATE users SET password = ? WHERE id = ?", [hash, userId], (err) => {
            if (err) {
                console.error(err);
                return res.render('profile', { pwdError: '密码修改失败' });
            }
            res.render('profile', { pwdSuccess: '密码修改成功' });
        });
    });
});

// 客户端主页 (时间表)
app.get('/dashboard', requireLogin, (req, res) => {
    // 获取本周一到周日的日期
    const startOfWeek = moment().startOf('isoWeek');
    const weekDates = [];
    for(let i=0; i<7; i++) {
        weekDates.push(moment(startOfWeek).add(i, 'days').format('YYYY-MM-DD'));
    }

    // 获取所有预约 (排除已取消的和已拒绝的)
    db.all(`SELECT * FROM bookings WHERE date >= ? AND date <= ? AND status != 'cancelled' AND status != 'rejected'`, 
        [weekDates[0], weekDates[6]], (err, bookings) => {
        
        // 获取暂停状态
        db.get("SELECT value FROM settings WHERE key = 'printer_paused'", (err, row) => {
            const isPaused = row && row.value === 'true';
            
            // 获取用户的所有预约记录
            db.all("SELECT * FROM bookings WHERE user_id = ? ORDER BY date DESC, slot_type DESC", [req.session.user.id], (err, myBookings) => {
                res.render('dashboard', { 
                    weekDates, 
                    bookings, 
                    isPaused,
                    moment,
                    myBookings: myBookings || []
                });
            });
        });
    });
});

// 预约页面
app.get('/booking', requireLogin, (req, res) => {
    const { date, slot } = req.query;
    
    // 检查该时段是否已被预约
    db.get(`SELECT * FROM bookings WHERE date = ? AND slot_type = ? AND status != 'cancelled'`, [date, slot], (err, row) => {
        if (row) return res.send("该时段已被预约");
        
        // 检查是否暂停
        db.get("SELECT value FROM settings WHERE key = 'printer_paused'", (err, settings) => {
            if (settings && settings.value === 'true') return res.send("打印机暂停使用中，无法预约");
            res.render('booking', { date, slot });
        });
    });
});

// 提交预约
app.post('/booking', requireLogin, (req, res) => {
    const { date, slot_type, own_filament, print_type, weight, file_name, purpose, is_paid } = req.body;
    let cost = 0;
    let weightVal = parseFloat(weight) || 0;
    
    if (own_filament === 'no') {
        const unitPrice = (print_type === 'multi') ? 0.15 : 0.10;
        cost = (weightVal * unitPrice).toFixed(2);
    } else {
        weightVal = 0;
    }

    const paidStatus = is_paid === '1' ? 1 : 0;

    db.run(`INSERT INTO bookings (user_id, date, slot_type, own_filament, print_type, weight, cost, file_name, purpose, is_paid, status) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending')`,
            [req.session.user.id, date, slot_type, own_filament === 'yes' ? 1 : 0, print_type, weightVal, cost, file_name, purpose, paidStatus],
            (err) => {
                if (err) return res.send("预约失败，可能已被抢占");
                res.redirect('/dashboard');
            }
    );
});

// === 管理员审批相关路由 ===

// 管理员审批页面
app.get('/admin/approvals', requireAdmin, (req, res) => {
    db.all("SELECT bookings.*, users.real_name, users.student_id FROM bookings JOIN users ON bookings.user_id = users.id WHERE bookings.status = 'pending' ORDER BY bookings.date ASC, bookings.slot_type ASC", (err, bookings) => {
        res.render('admin_approvals', { bookings });
    });
});

// 审批通过
app.post('/admin/approve/:id', requireAdmin, (req, res) => {
    db.run("UPDATE bookings SET status = 'booked' WHERE id = ?", [req.params.id], (err) => {
        if (err) console.error(err);
        res.redirect('/admin/approvals');
    });
});

// 审批拒绝
app.post('/admin/reject/:id', requireAdmin, (req, res) => {
    const { reason } = req.body;
    db.run("UPDATE bookings SET status = 'rejected', rejection_reason = ? WHERE id = ?", [reason, req.params.id], (err) => {
        if (err) console.error(err);
        res.redirect('/admin/approvals');
    });
});

// 签到/开始运行 (用户)
app.post('/booking/checkin/:id', requireLogin, (req, res) => {
    const bookingId = req.params.id;
    // 仅允许本人在当天操作
    db.get("SELECT * FROM bookings WHERE id = ?", [bookingId], (err, booking) => {
        if (!booking) return res.status(404).send("预约不存在");

        const isAdmin = req.session.user.role !== 'user';
        const isOwner = booking.user_id === req.session.user.id;
        if (!isOwner && !isAdmin) return res.status(403).send("无法操作");

        const startClock = booking.slot_type === 'AM' ? '08:00' : '13:30';
        const startMoment = moment(`${booking.date} ${startClock}`, 'YYYY-MM-DD HH:mm');
        if (!isAdmin && moment().isBefore(startMoment)) {
            return res.status(400).send("未到开始时间，暂不可签到");
        }

        db.run("UPDATE bookings SET status = 'running' WHERE id = ?", [bookingId], () => {
            res.redirect('/dashboard');
        });
    });
});

// 获取预约详情（用户/管理员）
app.get('/booking/details/:id', requireLogin, (req, res) => {
    const bookingId = req.params.id;
    db.get("SELECT * FROM bookings WHERE id = ?", [bookingId], (err, booking) => {
        if (err || !booking) return res.status(404).json({ error: 'NOT_FOUND' });
        const isOwner = booking.user_id === req.session.user.id;
        const isAdmin = req.session.user.role !== 'user';
        if (!isOwner && !isAdmin) return res.status(403).json({ error: 'FORBIDDEN' });
        res.json(booking);
    });
});

// 更新预约信息（用户/管理员）
app.post('/booking/update/:id', requireLogin, (req, res) => {
    const bookingId = req.params.id;
    const { own_filament, print_type, weight, is_paid } = req.body;

    db.get("SELECT * FROM bookings WHERE id = ?", [bookingId], (err, booking) => {
        if (err || !booking) return res.status(404).send("预约不存在");
        const isOwner = booking.user_id === req.session.user.id;
        const isAdmin = req.session.user.role !== 'user';
        if (!isOwner && !isAdmin) return res.status(403).send("无法操作");

        const ownFlag = own_filament === 'yes' ? 1 : 0;
        const safePrintType = print_type === 'multi' ? 'multi' : 'single';
        const weightNum = parseFloat(weight) || 0;
        const unitPrice = safePrintType === 'multi' ? 0.15 : 0.10;
        const cost = ownFlag ? 0 : (weightNum * unitPrice).toFixed(2);
        const paidStatus = is_paid === '1' ? 1 : 0;

        db.run(
            "UPDATE bookings SET own_filament = ?, print_type = ?, weight = ?, cost = ?, is_paid = ? WHERE id = ?",
            [ownFlag, safePrintType, weightNum, cost, paidStatus, bookingId],
            () => res.redirect('/dashboard')
        );
    });
});

// 取消预约（用户/管理员）
app.post('/booking/cancel/:id', requireLogin, (req, res) => {
    const bookingId = req.params.id;
    db.get("SELECT * FROM bookings WHERE id = ?", [bookingId], (err, booking) => {
        if (err || !booking) return res.status(404).send("预约不存在");
        const isOwner = booking.user_id === req.session.user.id;
        const isAdmin = req.session.user.role !== 'user';
        if (!isOwner && !isAdmin) return res.status(403).send("无法操作");

        db.run("UPDATE bookings SET status = 'cancelled' WHERE id = ?", [bookingId], () => {
            res.redirect('/dashboard');
        });
    });
});

// === 管理端路由 ===

// 用户管理
app.get('/admin/users', requireAdmin, (req, res) => {
    db.all("SELECT id, username, real_name, student_id, phone, role FROM users", (err, users) => {
        res.render('admin_users', { users });
    });
});

// 创建用户 (管理员)
app.post('/admin/users/create', requireAdmin, (req, res) => {
    const { username, real_name, student_id, phone, password, role } = req.body;
    // 只有Super Admin能创建管理员
    if (role !== 'user' && req.session.user.role !== 'super_admin') {
        return res.send("权限不足，无法创建管理员");
    }
    const hash = bcrypt.hashSync(password, 10);
    db.run(`INSERT INTO users (username, real_name, student_id, phone, password, role) VALUES (?, ?, ?, ?, ?, ?)`,
        [username, real_name, student_id, phone, hash, role], (err) => {
            res.redirect('/admin/users');
        });
});

// 切换用户角色
app.post('/admin/users/role', requireAdmin, (req, res) => {
    if (req.session.user.role !== 'super_admin') return res.status(403).send("权限不足");
    const { user_id, new_role } = req.body;
    db.run("UPDATE users SET role = ? WHERE id = ?", [new_role, user_id], () => {
        res.redirect('/admin/users');
    });
});

// 切换打印机全局状态
app.post('/admin/toggle-status', requireAdmin, (req, res) => {
    const { status } = req.body; // 'true' (paused) or 'false'
    db.run("INSERT OR REPLACE INTO settings (key, value) VALUES ('printer_paused', ?)", [status], () => {
        res.redirect('/dashboard'); // Admin也会看dashboard
    });
});

// 查看预约详情 (API)
app.get('/admin/booking-details/:id', requireAdmin, (req, res) => {
    db.get(`SELECT bookings.*, users.real_name, users.student_id, users.phone 
            FROM bookings JOIN users ON bookings.user_id = users.id 
            WHERE bookings.id = ?`, [req.params.id], (err, row) => {
        res.json(row);
    });
});

// 删除预约
app.post('/admin/booking/delete', requireAdmin, (req, res) => {
    const { id } = req.body;
    db.run("DELETE FROM bookings WHERE id = ?", [id], () => {
        res.redirect('/dashboard');
    });
});

// === 周报功能 ===

// 获取周报数据
function getWeeklyData(startDate, endDate) {
    return new Promise((resolve, reject) => {
        const query = `
            SELECT b.*, u.username, u.real_name, u.student_id, u.phone 
            FROM bookings b
            JOIN users u ON b.user_id = u.id
            WHERE b.date >= ? AND b.date <= ?
            ORDER BY b.date ASC, b.slot_type ASC
        `;
        db.all(query, [startDate, endDate], (err, rows) => {
            if (err) reject(err);
            else resolve(rows);
        });
    });
}

// 生成 Excel Buffer
function generateExcel(data) {
    const wb = xlsx.utils.book_new();
    const wsData = [
        ['预约ID', '用户', '真实姓名', '学号', '电话', '日期', '时段', '自带耗材', '打印类型', '重量(g)', '费用(元)', '是否支付', '文件名', '用途', '状态', '创建时间']
    ];
    
    data.forEach(row => {
        wsData.push([
            row.id, row.username, row.real_name, row.student_id, row.phone,
            row.date, row.slot_type, row.own_filament ? '是' : '否',
            row.print_type === 'multi' ? '多色' : '单色',
            row.weight, row.cost, row.is_paid ? '已支付' : '未支付', row.file_name, row.purpose,
            row.status, row.created_at
        ]);
    });

    const ws = xlsx.utils.aoa_to_sheet(wsData);
    xlsx.utils.book_append_sheet(wb, ws, "周报");
    return xlsx.write(wb, { type: 'buffer', bookType: 'xlsx' });
}

// 定时任务：每周一 0 点生成上周周报
schedule.scheduleJob('0 0 * * 1', async () => {
    console.log('开始生成周报...');
    const endDate = moment().subtract(1, 'days').format('YYYY-MM-DD'); // 上周日
    const startDate = moment().subtract(7, 'days').format('YYYY-MM-DD'); // 上周一
    
    try {
        const data = await getWeeklyData(startDate, endDate);
        const buffer = generateExcel(data);
        const fileName = `weekly_report_${startDate}_to_${endDate}.xlsx`;
        const filePath = path.join(__dirname, 'public/reports', fileName);
        
        fs.writeFileSync(filePath, buffer);
        
        // 存入数据库
        db.run(`INSERT INTO weekly_reports (start_date, end_date, file_path) VALUES (?, ?, ?)`, 
            [startDate, endDate, `/reports/${fileName}`]);

        console.log(`周报已生成: ${fileName}`);

    } catch (err) {
        console.error('周报生成失败:', err);
    }
});

// 周报管理页面
app.get('/admin/reports', requireAdmin, (req, res) => {
    db.all("SELECT * FROM weekly_reports ORDER BY created_at DESC", (err, reports) => {
        res.render('admin_reports', { reports });
    });
});

// 导出本周周报 (实时)
app.get('/admin/reports/export-current', requireAdmin, async (req, res) => {
    const startDate = moment().startOf('isoWeek').format('YYYY-MM-DD');
    const endDate = moment().endOf('isoWeek').format('YYYY-MM-DD');
    
    try {
        const data = await getWeeklyData(startDate, endDate);
        const buffer = generateExcel(data);
        
        res.setHeader('Content-Disposition', `attachment; filename="current_week_report.xlsx"`);
        res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        res.send(buffer);
    } catch (err) {
        console.error(err);
        res.status(500).send("生成失败");
    }
});

app.listen(port, () => {
    console.log(`系统已启动: http://localhost:${port}`);
});