var express = require('express');
var router = express.Router();
var mysql = require('../model/mysql');
// const _mysqluser = ["172.16.12.5", "registration"]; //用戶數據庫信息

const _mysqluser = ["183.36.25.93", "registration"]; //本地测试用戶數據庫信息
const crypto = require('crypto');
const XLSX = require('xlsx');

router.route('/user').post(async function (req, res) {
    const signUpData = [];
    const reqData = req.body;
    const uuid = crypto.randomUUID();

    let userData = JSON.parse(reqData.user);
    await mysql.usselect([_mysqluser[0], _mysqluser[1], "select_registration_user", reqData.idCard], function (r) {
        console.log(r[0] && r[0] ,r[0].some(item => item['type'] == Number(reqData.type)));
        if (r[0] && r[0].some(item => item['type'] == Number(reqData.type))) {
            res.send({ status: 'error', message: '选手报名失败，选手身份证号已存在' });
        } else {
            const insertUserPromises = userData.map(item => {
                return new Promise((resolve, reject) => {
                    const userList = [];
                    userList.unshift(_mysqluser[0], _mysqluser[1], "insert_registration_users", item.name, item.gender, item.ethnicity, item.birthdate, item.email, item.id_number, item.group, item.grade, item.school, item.schoolRegion, item.phone, uuid);
                    mysql.usselect(userList, function (data) {
                        // 可以根据 data 判断是否插入成功
                        console.log("insert_registration_users",data);
                        if (data != 0) {
                            resolve(data);
                        } else {
                            reject(data);
                        }
                    });
                });
            });

            signUpData.unshift(_mysqluser[0], _mysqluser[1], "insert_registrations", reqData.uploadUrl, "", reqData.type, reqData.idCard);
            Promise.all(insertUserPromises).then(() => {
                // 所有用户插入完成后再插入报名数据
                mysql.usselect(signUpData, function (data) {
                    if (data != 0) {
                        res.send({ status: 'ok', message: '选手报名成功' });
                    } else {
                        res.send({ status: 'error', message: '选手报名失败' });
                    }
                });
            }).catch(error => {
                console.error("error", error);
                res.send({ status: 'error', message: '选手报名失败，选手身份证号已存在' });
            });
        }
    })


});

router.route('/update').post(async function (req, res) {
    const { url, id_number } = req.body;
    const queryList = [_mysqluser[0], _mysqluser[1], "update_registration", url, id_number];
    mysql.usselect(queryList, function (data) {
        console.log(data);
        if (data == 1) {
            res.send({ status: 'ok', message: '报名信息更新成功' });
        } else {
            res.send({ status: 'error', message: '未找到报名信息' });
        }
    });
});


router.route('/exportData').get(async function (req, res) {
    const { start, end } = req.query;
    const queryList = [_mysqluser[0], _mysqluser[1], "sp_export_registration_data", start, end];
    let dataList = [];
    await mysql.usselect(queryList, function (data) {
        if (data[0] && data[0][0]) {
            dataList = data[0];
            dataList = dataList.map(item => {
                let user_group = ""
                if(item.type == 1){
                    user_group = item.user_group == 1?"小学低龄":item.user_group == 2?"小学高龄":item.user_group == 3?"中学":"高校"
                }else{
                    user_group = item.user_group == 1?"小学组（4-6年级）":item.user_group == 2?"初中组（7-9年级）":item.user_group == 3?"高中组（10-12年级，含中职）":"高校组（含高职）"
                }
                return {
                    小组id: item.user_groupid,
                    小组人数: item.id_card.split(",").length,
                    指导老师: item.upload_url,
                    比赛类型: item.type == 1 ? "桌游挑战赛" : "智能体应用",
                    姓名: item.user_name,
                    邮箱: item.user_email,
                    身份证号: item.user_id_number,
                    组别: user_group,
                    年级: item.user_grade,
                    学校: item.user_school,
                    学校地区: item.user_school_region,
                    电话: item.user_phone,
                    上传文件: item.registration_form_url.split(",")[0],
                    上传视频: item.registration_form_url.split(",")[1],
                    报名时间: new Date(item.created_at).toLocaleString()
                }
            });
            const workbook = XLSX.utils.book_new();
            const worksheet = XLSX.utils.json_to_sheet(dataList);
            XLSX.utils.book_append_sheet(workbook, worksheet, '报名数据');
            const buffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' });

            res.setHeader('Content-Disposition', 'attachment; filename="' + start + ':' + end + '.xlsx"');
            res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            res.send(buffer);
            return;
        }
    });

});


module.exports = router;