const XLSX = require('xlsx');
const path = require('path');

// ========== 配置 ==========
const EXCEL_FILE = path.join(__dirname, '新建 XLSX 工作表.xlsx');
const SHEET_NAME = '2026';

const BASE_URL = 'https://superstar.geelytravel.com';
const AUTH_TOKEN = 'Bearer eyJhbGciOiJIUzI1NiJ9.eyJzdWIiOiJKV1QtRC1TRVFfVVNFUl9UTUNfMDAwMDAxODMiLCJ1c2VyX2NvZGUiOiJTRVFfVVNFUl9UTUNfMDAwMDAxODMiLCJyb2xlcyI6WyLnu5PnrpfkuJPlkZgiLCLnu5PnrpfkuJPlkZgiXSwiZXhwIjoxNzg2NTMzNTUxLCJpYXQiOjE3ODM5NDE1NTF9.1Mcmf5IM07Scrry4FzPs6TAcfUu0Nqtjre_2iJlNd5c';

const COMMON_HEADERS = {
    'accept': 'application/json, text/plain, */*',
    'accept-language': 'zh-CN,zh;q=0.9',
    'authorization': AUTH_TOKEN,
    'cache-control': 'no-cache',
    'pragma': 'no-cache',
    'requestsource': 'D',
    'sec-fetch-dest': 'empty',
    'sec-fetch-mode': 'cors',
    'sec-fetch-site': 'same-origin',
    'usercode': 'SEQ_USER_TMC_00000183',
    'cookie': '_c_WBKFRo=CefecIUfnnuqejh60diYtytjDPwI9Qptslcwakc4; acw_tc=731dc87617840296932491914e815aef28b42e276e1a01eb53ed0f548bd5e3',
};

const INVOICE_APPLY_TITLE = '浙江吉利商务服务有限公司';
const INVOICE_TITLE = '吉智（杭州）文化创意有限公司';
const TAXPAYER_ID = '91330109MA2GM71J64';
const ADDRESS = '杭州市萧山区经纪技术开发区启迪路198号A-B102-126B室';
const TELEPHONE = '0571-85321518';
const DEPOSIT_BANK = '中国民生银行股份有限公司杭州分行';
const BANK_ACCOUNT = '631101003';

// ========== 1. 读取 Excel ==========
function readTeamIds() {
    const workbook = XLSX.readFile(EXCEL_FILE);
    const sheet = workbook.Sheets[SHEET_NAME];
    if (!sheet) {
        console.error('Sheet "' + SHEET_NAME + '" 不存在');
        process.exit(1);
    }
    const rows = XLSX.utils.sheet_to_json(sheet, { defval: null });
    const teamIds = [];
    for (const row of rows) {
        const val = row['团号'];
        if (val && /^TEAM-\d+$/.test(String(val).trim())) {
            teamIds.push(String(val).trim());
        }
    }
    console.log('[Excel] 找到 ' + teamIds.length + ' 个 TEAM ID: ' + teamIds.join(', ') + '\n');
    return teamIds;
}

// ========== 2. 查询 settlement account ==========
async function querySettlementAccountIds(teamId) {
    const url = BASE_URL + '/api/tmc/tmc/customerAccount/settlement/querySettlementAccountIds?customerSettlementAccountName=' + teamId;
    const resp = await fetch(url, { headers: COMMON_HEADERS, method: 'GET' });
    return resp.json();
}

// ========== 3. 查询 bill list ==========
async function queryBillList(settlementAccountId) {
    const url = BASE_URL + '/api/tmc/tmc/settlement/receiveBill/queryBillList?settlementAccountId=' + settlementAccountId + '&pageIndex=1&pageSize=10';
    const resp = await fetch(url, { headers: COMMON_HEADERS, method: 'GET' });
    return resp.json();
}

// ========== 4. 批量调整发票类型 ==========
async function batchAdjustInvoiceOrderPrice(settlementSeq, receiveBillId, originInvoiceType, targetInvoiceType) {
    const url = BASE_URL + '/api/tmc/tmc/settlement/receiveInvoice/batchAdjustInvoiceOrderPrice';
    const headers = {
        ...COMMON_HEADERS,
        'content-type': 'application/json;charset=UTF-8',
        'Referer': BASE_URL + '/new-tmc/finance/settlement/pay/invoice/billing/' + receiveBillId,
    };
    const body = JSON.stringify({
        settlementSeq: settlementSeq,
        orderMode: '1',
        originInvoiceType: originInvoiceType,
        targetInvoiceType: targetInvoiceType,
    });
    const resp = await fetch(url, { headers: headers, method: 'POST', body: body });
    return resp.json();
}

// ========== 5. 确认开票 ==========
async function confirmInvoiceOrder(settlementSeq, receiveBillId) {
    const url = BASE_URL + '/api/tmc/tmc/settlement/receiveInvoice/confirmInvoiceOrder';
    const headers = {
        ...COMMON_HEADERS,
        'content-type': 'application/json;charset=UTF-8',
        'Referer': BASE_URL + '/new-tmc/finance/settlement/pay/invoice/billing/' + receiveBillId,
    };
    const body = JSON.stringify({ settlementSeq: settlementSeq, orderMode: '1' });
    const resp = await fetch(url, { headers: headers, method: 'POST', body: body });
    return resp.json();
}

// ========== 6. 提交开票 ==========
async function submitInvoiceApply(confirmInvoiceKey, invoiceInfoList, receiveBillId) {
    const url = BASE_URL + '/api/tmc/tmc/settlement/receiveInvoice/submitInvoiceApply';
    const headers = {
        ...COMMON_HEADERS,
        'content-type': 'application/json;charset=UTF-8',
        'Referer': BASE_URL + '/new-tmc/finance/settlement/pay/invoice/billing/' + receiveBillId,
    };
    const body = JSON.stringify({
        confirmInvoiceKey: confirmInvoiceKey,
        invoiceInfoList: invoiceInfoList,
    });
    const resp = await fetch(url, { headers: headers, method: 'POST', body: body });
    return resp.json();
}

// ========== 7. 填充公司信息 ==========
function fillCompanyInfo(item) {
    return {
        ...item,
        invoiceApplyTitle: INVOICE_APPLY_TITLE,
        invoiceTitle: INVOICE_TITLE,
        taxpayerIdentityNumber: TAXPAYER_ID,
        address: ADDRESS,
        telephone: TELEPHONE,
        depositBank: DEPOSIT_BANK,
        bankAccount: BANK_ACCOUNT,
    };
}

// ========== 8. 映射火车票开票项 ==========
function mapTrainInvoice(serverItem) {
    const invoiceType = serverItem.invoiceType || '';
    const item = {
        invoiceSerialNum: serverItem.invoiceSerialNum,
        invoiceType: invoiceType,
        invoicePrice: serverItem.invoicePrice,
        taxRate: invoiceType === '13' ? 6 : 0,
        invoiceSettlementDetail: serverItem.settlementDetails || '',
        invoiceQuantity: serverItem.invoiceQuantity || 0,
        ticketList: serverItem.ticketList || null,
    };
    if (invoiceType === '13') {
        item.invoiceContent = '*生产生活服务*代订火车票服务费';
    }
    return fillCompanyInfo(item);
}

// ========== 9. 映射酒店开票项 ==========
function mapHotelInvoice(serverItem) {
    const invoiceType = serverItem.invoiceType || '';
    const item = {
        invoiceSerialNum: serverItem.invoiceSerialNum,
        invoiceType: invoiceType,
        invoicePrice: serverItem.invoicePrice,
        taxRate: 6,
        invoiceSettlementDetail: serverItem.settlementDetails || '',
        invoiceQuantity: serverItem.invoiceQuantity || 0,
        ticketList: serverItem.ticketList || null,
    };
    if (invoiceType === '2' || invoiceType === '1') {
        item.invoiceContent = '*生产生活服务*代订住宿费';
    } else if (invoiceType === '13') {
        item.invoiceContent = '*生产生活服务*代订住宿服务费';
    }
    return fillCompanyInfo(item);
}

// ========== 10. 映射机票开票项（国内+国际通用） ==========
function mapFlightInvoice(serverItem) {
    const invoiceType = serverItem.invoiceType || '';
    const item = {
        invoiceSerialNum: serverItem.invoiceSerialNum,
        invoiceType: invoiceType,
        invoicePrice: serverItem.invoicePrice,
        taxRate: 6,
        invoiceSettlementDetail: serverItem.settlementDetails || '',
        invoiceQuantity: serverItem.invoiceQuantity || 0,
        ticketList: serverItem.ticketList || null,
    };
    if (invoiceType === '2') {
        item.invoiceContent = '*生产生活服务*代订机票';
    } else if (invoiceType === '13') {
        item.invoiceContent = '*生产生活服务*代订机票服务费';
    }
    return fillCompanyInfo(item);
}

// ========== 11. 处理火车票 ==========
async function processTrainBill(bill, billIndex, totalBills) {
    const settlementSeq = bill.settlementSeq;
    const receiveBillId = bill.receiveBillId;

    console.log('  --- 火车票 [' + billIndex + '/' + totalBills + '] ' + bill.statementName + ' ---');

    const confirmResp = await confirmInvoiceOrder(settlementSeq, receiveBillId);
    console.log('  -> confirmInvoiceOrder:', JSON.stringify(confirmResp));

    const confirmResult = confirmResp?.result;
    if (!confirmResult || !confirmResult.invoiceAble) {
        console.log('  ⚠ 跳过: ' + (confirmResp?.message || '') + '\n');
        return;
    }

    const confirmInvoiceKey = confirmResult.confirmInvoiceKey;
    const submitList = (confirmResult.invoiceInfoList || []).map(mapTrainInvoice);

    const submitResp = await submitInvoiceApply(confirmInvoiceKey, submitList, receiveBillId);
    console.log('  -> submitInvoiceApply:', JSON.stringify(submitResp));
    console.log('');
}

// ========== 12. 处理酒店 ==========
async function processHotelBill(bill, billIndex, totalBills) {
    const settlementSeq = bill.settlementSeq;
    const receiveBillId = bill.receiveBillId;

    console.log('  --- 酒店 [' + billIndex + '/' + totalBills + '] ' + bill.statementName + ' ---');

    const adjustResp = await batchAdjustInvoiceOrderPrice(settlementSeq, receiveBillId, '1', '2');
    console.log('  -> batchAdjust (1→2):', JSON.stringify(adjustResp));
    if (adjustResp?.code !== 'Z000') {
        console.log('  ⚠ 调整失败: ' + (adjustResp?.message || '') + '\n');
        return;
    }

    const confirmResp = await confirmInvoiceOrder(settlementSeq, receiveBillId);
    console.log('  -> confirmInvoiceOrder:', JSON.stringify(confirmResp));

    const confirmResult = confirmResp?.result;
    if (!confirmResult || !confirmResult.invoiceAble) {
        console.log('  ⚠ 跳过: ' + (confirmResp?.message || '') + '\n');
        return;
    }

    const confirmInvoiceKey = confirmResult.confirmInvoiceKey;
    const submitList = (confirmResult.invoiceInfoList || []).map(mapHotelInvoice);

    const submitResp = await submitInvoiceApply(confirmInvoiceKey, submitList, receiveBillId);
    console.log('  -> submitInvoiceApply:', JSON.stringify(submitResp));
    console.log('');
}

// ========== 13. 处理国内机票 ==========
async function processFlightBill(bill, billIndex, totalBills) {
    const settlementSeq = bill.settlementSeq;
    const receiveBillId = bill.receiveBillId;

    console.log('  --- 国内机票 [' + billIndex + '/' + totalBills + '] ' + bill.statementName + ' ---');

    const adjust1Resp = await batchAdjustInvoiceOrderPrice(settlementSeq, receiveBillId, '4', '2');
    console.log('  -> batchAdjust (4→2):', JSON.stringify(adjust1Resp));
    if (adjust1Resp?.code !== 'Z000') {
        console.log('  ⚠ 调整失败: ' + (adjust1Resp?.message || '') + '\n');
        return;
    }

    const adjust2Resp = await batchAdjustInvoiceOrderPrice(settlementSeq, receiveBillId, '30', '2');
    console.log('  -> batchAdjust (30→2):', JSON.stringify(adjust2Resp));
    if (adjust2Resp?.code !== 'Z000') {
        console.log('  ⚠ 调整失败: ' + (adjust2Resp?.message || '') + '\n');
        return;
    }

    const confirmResp = await confirmInvoiceOrder(settlementSeq, receiveBillId);
    console.log('  -> confirmInvoiceOrder:', JSON.stringify(confirmResp));

    const confirmResult = confirmResp?.result;
    if (!confirmResult || !confirmResult.invoiceAble) {
        console.log('  ⚠ 跳过: ' + (confirmResp?.message || '') + '\n');
        return;
    }

    const confirmInvoiceKey = confirmResult.confirmInvoiceKey;
    const submitList = (confirmResult.invoiceInfoList || []).map(mapFlightInvoice);

    const submitResp = await submitInvoiceApply(confirmInvoiceKey, submitList, receiveBillId);
    console.log('  -> submitInvoiceApply:', JSON.stringify(submitResp));
    console.log('');
}

// ========== 14. 处理国际机票 ==========
async function processInternationalFlightBill(bill, billIndex, totalBills) {
    const settlementSeq = bill.settlementSeq;
    const receiveBillId = bill.receiveBillId;

    console.log('  --- 国际机票 [' + billIndex + '/' + totalBills + '] ' + bill.statementName + ' ---');

    const confirmResp = await confirmInvoiceOrder(settlementSeq, receiveBillId);
    console.log('  -> confirmInvoiceOrder:', JSON.stringify(confirmResp));

    const confirmResult = confirmResp?.result;
    if (!confirmResult || !confirmResult.invoiceAble) {
        console.log('  ⚠ 跳过: ' + (confirmResp?.message || '') + '\n');
        return;
    }

    const confirmInvoiceKey = confirmResult.confirmInvoiceKey;
    const submitList = (confirmResult.invoiceInfoList || []).map(mapFlightInvoice);

    const submitResp = await submitInvoiceApply(confirmInvoiceKey, submitList, receiveBillId);
    console.log('  -> submitInvoiceApply:', JSON.stringify(submitResp));
    console.log('');
}

// ========== 主流程 ==========
async function main() {
    const teamIds = readTeamIds();
    if (teamIds.length === 0) {
        console.log('没有找到 TEAM ID，退出');
        return;
    }

    for (let i = 0; i < teamIds.length; i++) {
        const teamId = teamIds[i];
        console.log('[' + (i + 1) + '/' + teamIds.length + '] 处理 ' + teamId + ' ...');

        try {
            const settlementResp = await querySettlementAccountIds(teamId);
            const resultList = settlementResp?.result || [];
            if (resultList.length === 0) {
                console.log('  ⚠ ' + teamId + ' 没有 settlement account，跳过\n');
                continue;
            }
            const settlementAccountId = resultList[0]?.id;
            console.log('  -> settlementAccountId: ' + settlementAccountId);

            const billResp = await queryBillList(settlementAccountId);
            const allBills = billResp?.result?.list || [];
            const total = billResp?.result?.total || 0;
            console.log('  -> 共 ' + total + ' 条对账单');

            const skippedBills = allBills.filter(function (b) { return String(b.settlementStatus) === '3'; });
            skippedBills.forEach(function (b) {
                console.log('  ⚠ 跳过 settlementStatus=3 的对账单: ' + (b.statementName || b.receiveBillId || '') + '\n');
            });

            const bills = allBills.filter(function (b) { return String(b.settlementStatus) !== '3'; });
            console.log('  -> 实际处理 ' + bills.length + ' 条对账单\n');

            const trainBills = bills.filter(function (b) { return (b.statementName || '').includes('火车票'); });
            const hotelBills = bills.filter(function (b) { return (b.statementName || '').includes('酒店'); });
            const flightBills = bills.filter(function (b) { return (b.statementName || '').includes('国内机票'); });
            const intlFlightBills = bills.filter(function (b) { return (b.statementName || '').includes('国际机票'); });
            const otherBills = bills.filter(function (b) {
                const name = b.statementName || '';
                return !name.includes('火车票') && !name.includes('酒店') && !name.includes('国内机票') && !name.includes('国际机票');
            });

            console.log('  -> 火车票: ' + trainBills.length + ', 酒店: ' + hotelBills.length + ', 国内机票: ' + flightBills.length + ', 国际机票: ' + intlFlightBills.length + ', 其他: ' + otherBills.length + '\n');

            for (let j = 0; j < trainBills.length; j++) {
                await processTrainBill(trainBills[j], j + 1, trainBills.length);
            }
            for (let j = 0; j < hotelBills.length; j++) {
                await processHotelBill(hotelBills[j], j + 1, hotelBills.length);
            }
            for (let j = 0; j < flightBills.length; j++) {
                await processFlightBill(flightBills[j], j + 1, flightBills.length);
            }
            for (let j = 0; j < intlFlightBills.length; j++) {
                await processInternationalFlightBill(intlFlightBills[j], j + 1, intlFlightBills.length);
            }

            if (otherBills.length > 0) {
                console.log('  === 以下对账单未处理（未知类型） ===');
                otherBills.forEach(function (b) { console.log('  - ' + b.statementName); });
                console.log('');
            }
        } catch (err) {
            console.error('  ✗ ' + teamId + ' 出错:', err.message);
        }
        console.log('');
    }

    console.log('========== 完成 ==========');
}

main().catch(err => {
    console.error('Fatal error:', err);
    process.exit(1);
});