/**
 * 数据清洗
 */

import moment = require("moment");
import { BANXUELEIXING, CERTIFICATETYPE, DOCUMENTTYPE, EDUCATION, INDIVIDUALMEMBERTYPE, MEMBERLEVEL, MEMBERSTATE, MEMBERTYPE, NATION, PROFCATEGORY, SEX, STATE, USERREGISTERSTATE } from "../config/enum";
import { TABLEENUM } from "../data/models/model";
import { find, findOnce, findOnceToSort } from "../data/select";
import { updateOneData } from "../data/update";
import { BizError } from "../util/bizError";
import { changeEnumValue } from "../util/verificationEnum";
import { getCityNameByCode } from "../config/cityConfig";
import { getEdition } from "../tools/system";


const path = require('path');
const fs = require('fs');
const xlsx = require('xlsx');
const lodash = require('lodash');


export async function getUnitData() {
    let selectParam = {
        isAdmin:STATE.否, 
        memberType: MEMBERTYPE.单位会员, 
        userRegisterState: USERREGISTERSTATE.通过
    };
    let selectTitle = ["入会时间", "是否是导入用户", "注册手机号", "日常联系人手机号码", "会员状态", "证书类型", "个人会员类型", "会员等级/职务", 
    "是否处于宽限期", "用户名", "联系电话", "电子邮箱", "单位邮箱", "单位名称", "统一信用代码", 
    "法人代表", "法人代表联系电话", "法人邮箱", "日常联系人", "日常联系人职务", "营业执照", "单位会员类型", 
    "院校办学类型", "院校主管部门", "院校主要负责人", "院校主要负责人职务", "院校主要负责人电话", "院校办公负责人", "院校办公负责人电话", 
    "主要负责人邮箱", "院校科研负责人", "院校科研负责人电话", "教学负责人姓名", "院校学生总人数", "教职工总人数", "教学负责人电话", "开设专业", "单位简介"];
    let selectConf = ["joinTime", "isInput", "phone", "phone", "memberState", "certificateType", "individualMemberType", "memberLevel",
    "isGracePeriod", "loginId", "phone", "mail", "unitMail", "unitName", "uscc", 
    "legalPerson", "legalPersonPhone", "legalPersonMail", "contactPerson", "contactPersonDuties", "uusinessLicenseUrl", "unitMemberType", 
    "yuanXiaoBanXueLeiXing", "yuanXiaoZhuGuanBuMen", "yuanXiaoFuZeRen", "yuanXiaoFuZeRenZhiWu", "yuanXiaoFuZeRenDianHua", "yuanXiaoBanGongFuZeRen", "yuanXiaoBanGongFuZeRenDianHua", 
    "ZhuYaoFuZeRenYouXiang", "yuanXiaoKeYanFuZeRen", "yuanXiaoKeYanFuZeRenDianHua", "jiaoXueFuZeRenXinMing", "yuanXiaoXueShengZongRenShu", "yuanXiaoJiaoZhiGongZongRenShu", "jiaoXueFuZeRenDianHua", "kaishezhuanYe", "danWeiJianJie"];                                    
    let dbList = await find(TABLEENUM.用户表, selectParam, selectConf);
    let timeChnageList = ["joinTime"];
    let addList = ["sheng", "shi", "qu"];

    let dataList = [];

    for (let i = 0; i < dbList.length; i++) {
        let info = dbList[i];
        // let subList = [];
        let subInfo = {};
        for (let i = 0; i < selectConf.length; i++) {
            let key = selectConf[i];
            let value = info[key] || "";
            if (value) {
                if (timeChnageList.indexOf(key) != -1) value = moment(value).format("YYYY-MM-DD");
                else if (key == "isInput" || key == "isGracePeriod") value = value == true ? "是" : "否";
                else if (key == "memberState") value = changeEnumValue(MEMBERSTATE, value);
                else if (key == "certificateType") value = changeEnumValue(CERTIFICATETYPE, value);
                else if (key == "individualMemberType") value = changeEnumValue(INDIVIDUALMEMBERTYPE, value);
                else if (key == "unitMemberType") value = changeEnumValue(MEMBERTYPE, value);
                else if (key == "memberLevel") value = changeEnumValue(MEMBERLEVEL, value);
                else if (key == "yuanXiaoBanXueLeiXing") value = changeEnumValue(BANXUELEIXING, value);
                else if (key == "session") value = getEdition(Math.floor(value/100), value%100 );
                else if (key == "memberState") value = changeEnumValue(MEMBERSTATE, value);
                else if (addList.indexOf(key) != -1) value = getCityNameByCode(value);
            }
            // subList.push(value);
            subInfo[selectTitle[i]] = value;
        }
        dataList.push(subInfo);
    }



    // let selectTitle = ["userId", "uscc", "名称", "注册手机号码", "日常联系人手机号", "是否导入数据"];
    // let selectConf = ["userId", "uscc", "unitName", "phone", "contactPersonPhone", "isInput"];
    // let dbList = await find(TABLEENUM.用户表, selectParam, selectConf);
    // let dataList = [];
    // for (let i = 0; i < dbList.length; i++) {
    //     let info = dbList[i];
    //     let subList = [];
    //     let subInfo = {};
    //     for (let i = 0; i < selectConf.length; i++) {
    //         let key = selectConf[i];
    //         let value = info[key] || "";
    //         // subList.push(value);
            
    //         if (value) {
    //             if (key == "isInput" || key == "isGracePeriod") value = value == true ? "是" : "否";
    //         }
    //         subInfo[selectTitle[i]] = value;
    //     }
    //     dataList.push(subInfo);
    // }

    writeExcel(dataList, 'unitManber.xlsx');
    console.log('单位会员数据已输出到 unitManber.xlsx');
}



export function getData() {
    const oldMembers = readExcel(path.join(__dirname.substring(0,__dirname.indexOf("out")), "res", '20250307财务订单信息表.xlsx' ));
    const newMembers = readExcel(path.join(__dirname.substring(0,__dirname.indexOf("out")), "res", '20250307个人会员数据.xlsx' ));

    // 用于存储匹配到的重复数据
    const duplicates = [];
    // 用于存储不匹配的数据
    const mismatches = [];
    // 用于存储不匹配的邮箱数据
    const mismatchesMail = [];

    // 遍历新会员数据，与旧会员数据进行匹配
    newMembers.forEach(newMember => {
        const match = lodash.find(oldMembers, oldMember => {
            return oldMember['证件号码'] === newMember['证件号码'];
        });

        if (match) {
            // duplicates.push({
            //     姓名: newMember['姓名'],
            //     证件号码: newMember['证件号码'],
            //     手机号码: newMember['手机号码']
            // });

            // 检查身份证号码、手机号码、邮箱是否匹配
            if (
                match['注册邮箱'] !== newMember['邮箱'] || 
                match['注册邮箱'] !== match['发票邮箱']
                // match['证件号码'] !== newMember['证件号码'] ||
                // match['手机号码'] !== newMember['手机号码'] ||
                // match['邮箱'] !== newMember['邮箱']
            ) {
                mismatchesMail.push({
                    姓名: newMember['姓名'],
                    数据库身份证号码: newMember['证件号码'],
                    财务表身份证号码: match['证件号码'],
                    邮箱: newMember['邮箱'],
                    注册邮箱: match['注册邮箱'],
                    发票邮箱: match['发票邮箱'],
                    // 导入邮箱: match['邮箱'],
                    // 数据库邮箱: newMember['邮箱']
                });
            }
        }
    });

    // // 输出重复数据到Excel文件
    writeExcel(mismatchesMail, 'mail.xlsx');
    console.log('重复数据已输出到 mail.xlsx');
    // 输出不匹配的数据到Excel文件
    // writeExcel(mismatches, 'mismatches.xlsx');
    // console.log('不匹配的数据已输出到 mismatches.xlsx');

    console.log();
}


export function getMailData() {
    const oldMembers = readExcel(path.join(__dirname.substring(0,__dirname.indexOf("out")), "res", '20250307财务订单信息表.xlsx' ));
    const newMembers = readExcel(path.join(__dirname.substring(0,__dirname.indexOf("out")), "res", '20250307个人会员数据.xlsx' ));

    // 用于存储不匹配的数据
    const mismatches = [];

    // 遍历新会员数据，与旧会员数据进行匹配
    newMembers.forEach(newMember => {
        const match = lodash.find(oldMembers, oldMember => {
            return oldMember['会员单位名称/姓名'] === newMember['姓名'];
        });

        if (match) {
            // 检查身份证号码、手机号码、邮箱是否匹配
            if (
                match['注册邮箱'] !== newMember['邮箱']
                // match['证件号码'] !== newMember['证件号码'] ||
                // match['手机号码'] !== newMember['手机号码'] ||
                // match['邮箱'] !== newMember['邮箱']
            ) {
                if (match['注册邮箱']) {
                    mismatches.push({
                        姓名: newMember['姓名'],
                        // 导入身份证号码: match['证件号码'],
                        // 数据库身份证号码: newMember['证件号码'],
                        // 导入手机号码: match['手机号码'],
                        // 数据库手机号码: newMember['手机号码'],
                        导入邮箱: match['注册邮箱'],
                        导入发票邮箱: match['发票邮箱'],
                        数据库邮箱: newMember['邮箱']
                    });
                }
            }
        }
    });

    // 输出不匹配的数据到Excel文件
    writeExcel(mismatches, 'mismatches.xlsx');
    console.log('不匹配的数据已输出到 mismatches.xlsx');

    console.log();
}


// 读取Excel文件
function readExcel(filePath) {
    const workbook = xlsx.readFile(filePath);
    const sheetName = workbook.SheetNames[0];
    const sheet = workbook.Sheets[sheetName];
    return xlsx.utils.sheet_to_json(sheet);
}

// 匹配新旧会员数据
function matchMembers(oldMembers, newMembers) {
    const matchedMembers = [];

    newMembers.forEach(newMember => {
        const oldMember = oldMembers.find(old => 
            // `${old['姓名']}-${old['证件号码']}` === `${newMember['姓名']}-${newMember['证件号码']}`
            old['姓名'] === newMember['姓名']
        );

        if (oldMember) {
            matchedMembers.push({
                ...oldMember,
                ...newMember
            });
        }
    });

    return matchedMembers;
}

// 写入Excel文件
function writeExcel(data, filePath) {
    const worksheet = xlsx.utils.json_to_sheet(data);
    const workbook = xlsx.utils.book_new();
    xlsx.utils.book_append_sheet(workbook, worksheet, 'Sheet1');
    xlsx.writeFile(workbook, filePath);
}


function getExcel(filePath) {
    const workSheetsFromFile = xlsx.parse(filePath);
    let sheetMap = {};
    let sheetList = [];

    for (let i = 0; i < workSheetsFromFile.length; i++) {
        let sheetInfo = workSheetsFromFile[i];
        sheetMap[sheetInfo.name] = sheetInfo.data;
        sheetList.push(sheetInfo);
    }

    return {sheetMap, sheetList}  
}


/**
 * 核对后修改身份证
 */
export async function changeDB() {
    const oldMembers = readExcel(path.join(__dirname.substring(0,__dirname.indexOf("out")), "res", '身份证差异.xlsx' ));
    for (let i = 0; i < oldMembers.length; i++){
        let old = oldMembers[i];
        let {姓名, 导入身份证号码, 数据库身份证号码} = old;
        let dbData = await findOnce(TABLEENUM.用户表, {name:姓名, documentId:数据库身份证号码});
        if (!dbData) throw new BizError(`${姓名}-${数据库身份证号码}：未找到对应数据`);

        let row = await updateOneData(TABLEENUM.用户表, {name:姓名, documentId:数据库身份证号码}, {documentId:导入身份证号码});
        if(row) console.log(`${姓名} ==> 错误身份证：${数据库身份证号码}  修复完成身份证：${导入身份证号码}`)
        console.log(row);
    }

    let documentTypeConf = [
        {
            "姓名":"陈日杰",
            "证件类型":"港澳通行证",
            "证件号码":"10337112"
        },
        {
            "姓名":"陳玟陵",
            "证件类型":"台胞证",
            "证件号码":"00103967"
        },
        {
            "姓名":"沈卡婷",
            "证件类型":"港澳通行证",
            "证件号码":"02018594"
        },
        {
            "姓名":"黄馥君",
            "证件类型":"台胞证",
            "证件号码":"03537113"
        },
    ];

    for (let i = 0; i < documentTypeConf.length; i++) {
        let {姓名, 证件类型, 证件号码} = documentTypeConf[i];
        let documentTypeData = await findOnce(TABLEENUM.用户表, {name:姓名, documentId:证件号码});

        if (!documentTypeData) throw new BizError(`${姓名}-${证件号码}：未找到对应数据`);

        let documentType = DOCUMENTTYPE[证件类型];
        let row = await updateOneData(TABLEENUM.用户表, {name:姓名, documentId:证件号码}, {documentType});
        if(row) console.log(`${姓名} ==> 身份证：${证件号码}  修复完成身份证类型：${documentType}-${证件类型}`)
        console.log(row);
    }

}


/**
 * 导出个人会员数据
 * @param param0 
 */
 export async function outPutMember() {
    let selectParam = {
        isAdmin:STATE.否, 
        memberType: MEMBERTYPE.个人会员, 
        // userRegisterState: USERREGISTERSTATE.通过
    };
    let selectTitle = ["序号", "入会时间", "用户名", "联系电话", "邮箱", "真实姓名", "证件号码", "发票邮箱"];
    let selectConf = ["joinTime", "loginId", "phone", "mail", "name", "documentId", "invoiceMail"];                                    
    
    // let selectTitle = ["序号"];
    // let selectConf = [];
    // exportColumns.forEach( info=> {
    //     if (info.key == "省市区") {
    //         selectTitle.push("省", "市", "区");
    //         selectConf.push("sheng", "shi", "qu");
    //     } else {
    //         selectTitle.push(info.key);
    //         selectConf.push(info.value);
    //     }
    // })
    
    let dbList = await find(TABLEENUM.用户表, selectParam, []);
    let timeChnageList = ["joinTime", "birth"];
    let addList = ["sheng", "shi", "qu"];

    let dataList = [selectTitle];

    for (let i = 0; i < dbList.length; i++) {
        let info = dbList[i];
        let orderInfo = await findOnceToSort(TABLEENUM.订单表, {userId: info.userId}, {orderCycleStart:-1}, ["invoiceMail"]);
        let subList = [ (i+1).toString() ];
        for (let i = 0; i < selectConf.length; i++) {
            let key = selectConf[i];
            let value = info[key] || "";
            if (value) {
                if (timeChnageList.indexOf(key) != -1) value = moment(value).format("YYYY-MM-DD");
                else if (key == "invoiceMail") value = orderInfo.invoiceMail;
                else if (key == "isInput" || key == "isGracePeriod") value = value == true ? "是" : "否";
                else if (key == "memberState") value = changeEnumValue(MEMBERSTATE, value);
                else if (key == "certificateType") value = changeEnumValue(CERTIFICATETYPE, value);
                else if (key == "individualMemberType") value = changeEnumValue(INDIVIDUALMEMBERTYPE, value);
                else if (key == "memberLevel") value = changeEnumValue(MEMBERLEVEL, value);
                else if (key == "sex") value = changeEnumValue(SEX, value);
                else if (key == "documentType") value = changeEnumValue(DOCUMENTTYPE, value);
                else if (key == "nation") value = changeEnumValue(NATION, value);
                else if (key == "education") value = changeEnumValue(EDUCATION, value);
                else if (key == "yuanXiaoBanXueLeiXing") value = changeEnumValue(BANXUELEIXING, value);
                else if (key == "session") value = getEdition(Math.floor(value/100), value%100 );
                else if (key == "memberState") value = changeEnumValue(MEMBERSTATE, value);
                else if (key == "profCategory") value = changeEnumValue(PROFCATEGORY, value);
                else if (addList.indexOf(key) != -1) value = getCityNameByCode(value);
                else if (key == "lableId") {
                    value = [];
                    let lableInfo = await find(TABLEENUM.标签表, {lableId:{"$in":info.lableId}}, ["lableName"]);
                    lableInfo.forEach ( lableItem => {
                        value.push(lableItem.lableName);
                    });
                    value = value.toString();
                }
            }
            subList.push(value);
        }
        dataList.push(subList);
    }

    // 输出个人会员库的数据到Excel文件
    writeExcel(dataList, '数据库-个人会员.xlsx');
    console.log('数据已输出到 数据库-个人会员.xlsx');
    // return {dataList};
}









