SY_project/server/routes/orders.js

const express = require('express');
const router = express.Router();
const { getPool } = require('../db');
const ExcelJS = require('exceljs');
const multer = require('multer');
const fs = require('fs');
const path = require('path');
const { CODE_TO_NAME, NAME_TO_CODE } = require('../logistics');

const upload = multer({ storage: multer.memoryStorage() });

// 日志文件目录
const LOG_DIR = path.join(__dirname, '..', 'logs');
if (!fs.existsSync(LOG_DIR)) fs.mkdirSync(LOG_DIR, { recursive: true });

// 生成物流公司代码转中文名的 CASE 语句
function companyCaseExpr(alias = 'l.company') {
  const cases = Object.entries(CODE_TO_NAME)
    .map(([code, name]) => `WHEN '${code}' THEN '${name}'`)
    .join('\n          ');
  return `CASE ${alias}\n          ${cases}\n          ELSE ${alias}\n        END`;
}

// 用户账号配置
const USERS = [
  { username: 'admin', password: 'admin123', role: 'admin' },
  { username: 'user', password: 'user123', role: 'user' },
];

// 根据请求头选择数据库中间件
function dbPool(req, res, next) {
  req.db = getPool(req.headers['x-project']);
  next();
}

// 登录接口
router.post('/login', async (req, res) => {
  const { username, password } = req.body;
  const user = USERS.find(u => u.username === username && u.password === password);
  if (user) {
    const token = Buffer.from(`${username}:${user.role}:${Date.now()}`).toString('base64');
    await logOperation(getPool('js'), user.username, user.role, '用户登录', `${user.username} 登录系统`, req.ip);
    res.json({ success: true, token, username: user.username, role: user.role });
  } else {
    res.json({ success: false, message: '用户名或密码错误' });
  }
});

// 验证token中间件（解析用户名和角色）
function authMiddleware(req, res, next) {
  const token = req.headers['authorization'];
  if (!token) {
    return res.status(401).json({ success: false, message: '未登录' });
  }
  try {
    const decoded = Buffer.from(token, 'base64').toString();
    const parts = decoded.split(':');
    req.user = { username: parts[0], role: parts[1] || 'user' };
  } catch (e) {
    return res.status(401).json({ success: false, message: '无效的登录状态' });
  }
  next();
}

// admin权限中间件
function adminOnly(req, res, next) {
  if (req.user && req.user.role === 'admin') {
    next();
  } else {
    return res.status(403).json({ success: false, message: '需要管理员权限' });
  }
}

// 记录操作日志（数据库 + 本地文件）
async function logOperation(db, username, role, action, detail, ip) {
  try {
    await db.query(
      `INSERT INTO sys_operation_log (username, role, action, detail, ip) VALUES ($1, $2, $3, $4, $5)`,
      [username, role, action, detail, ip]
    );
  } catch (e) {
    console.error('记录数据库日志失败:', e.message);
  }
  try {
    const now = new Date();
    const dateStr = now.toISOString().replace('T', ' ').substring(0, 19);
    const line = `[${dateStr}] [${role}] ${username} | ${action} | ${detail} | IP: ${ip || '-'}\n`;
    const logFile = path.join(LOG_DIR, `${now.getFullYear()}-${String(now.getMonth()+1).padStart(2,'0')}.log`);
    fs.appendFileSync(logFile, line, 'utf-8');
  } catch (e) {
    console.error('写入本地日志失败:', e.message);
  }
}

// 状态映射
const STATUS_MAP = {
  0: '待处理',
  1: '已完成',
  2: '已取消',
  3: '已发货',
};

// 动态查询订单
router.post('/query', authMiddleware, dbPool, async (req, res) => {
  try {
    const { receiver_name, receiver_phone, order_no, start_date, end_date, status, page = 1, pageSize = 20 } = req.body;

    let sql = `
      SELECT
        c.batch_no AS "批次号",
        o.order_no AS "订单号",
        to_char(o.create_time, 'YYYY-MM-DD HH24:MI:SS') AS "下单时间",
        o.card_no AS "卡号",
        o.department AS "部门",
        o.employee_id AS "工号",
        o.employee_name AS "员工姓名",
        o.receiver_name AS "收货人",
        o.receiver_phone AS "手机号",
        concat(o.receiver_province, o.receiver_city, o.receiver_district, o.receiver_detail) AS "收货地址",
        oi.package_name AS "商品名称",
        oi.quantity AS "数量",
        oi.price AS "单价",
        oi.subtotal AS "小计",
        o.remark AS "备注",
        ${companyCaseExpr()} AS "物流公司",
        l.tracking_no AS "物流单号",
        CASE o.status
          WHEN 0 THEN '待处理'
          WHEN 1 THEN '已完成'
          WHEN 2 THEN '已取消'
          WHEN 3 THEN '已发货'
        END AS "订单状态"
      FROM gift_order o
      JOIN gift_card c ON c.id = o.card_id AND c.del_flag = '0'
      JOIN gift_order_item oi ON oi.order_id = o.id AND oi.del_flag = '0'
      LEFT JOIN gift_logistics l ON l.order_id = o.id AND l.del_flag = '0'
      WHERE o.del_flag = '0'
    `;

    const params = [];
    let paramIndex = 1;

    if (status !== undefined && status !== null && status !== '') {
      sql += ` AND o.status = $${paramIndex}`;
      params.push(status);
      paramIndex++;
    }

    if (receiver_name) {
      sql += ` AND o.receiver_name LIKE $${paramIndex}`;
      params.push(`%${receiver_name}%`);
      paramIndex++;
    }

    if (receiver_phone) {
      sql += ` AND o.receiver_phone LIKE $${paramIndex}`;
      params.push(`%${receiver_phone}%`);
      paramIndex++;
    }

    if (order_no) {
      sql += ` AND o.order_no = $${paramIndex}`;
      params.push(order_no);
      paramIndex++;
    }

    if (start_date) {
      sql += ` AND o.create_time >= $${paramIndex}`;
      params.push(start_date);
      paramIndex++;
    }

    if (end_date) {
      sql += ` AND o.create_time < $${paramIndex}`;
      params.push(end_date);
      paramIndex++;
    }

    const countSql = sql.replace(/SELECT[\s\S]*?FROM/, 'SELECT COUNT(*) AS total FROM');
    const countResult = await req.db.query(countSql, params);
    const total = parseInt(countResult.rows[0].total);

    const pageNum = Math.max(1, parseInt(page));
    const pageSizeNum = Math.max(1, Math.min(100, parseInt(pageSize)));
    sql += ' ORDER BY c.batch_no, o.create_time, o.order_no';
    sql += ` LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`;
    params.push(pageSizeNum, (pageNum - 1) * pageSizeNum);

    const result = await req.db.query(sql, params);
    await logOperation(req.db, req.user.username, req.user.role, '查询订单', `条件: 状态=${status||'全部'}, 收货人=${receiver_name||'-'}, 手机号=${receiver_phone||'-'}, 订单号=${order_no||'-'}, 日期=${start_date||'-'}~${end_date||'-'}, 第${pageNum}页`, req.ip);
    res.json({ success: true, data: result.rows, total, page: pageNum, pageSize: pageSizeNum });
  } catch (err) {
    console.error('查询订单失败:', err);
    res.status(500).json({ success: false, message: err.message });
  }
});

// 导出 Excel
router.post('/export/excel', authMiddleware, dbPool, async (req, res) => {
  try {
    const { batch_no, card_no, order_no, start_date, end_date, status } = req.body;

    let sql = `
      SELECT
        c.batch_no AS "批次号",
        o.order_no AS "订单号",
        to_char(o.create_time, 'YYYY-MM-DD HH24:MI:SS') AS "下单时间",
        o.card_no AS "卡号",
        o.department AS "部门",
        o.employee_id AS "工号",
        o.employee_name AS "员工姓名",
        o.receiver_name AS "收货人",
        o.receiver_phone AS "手机号",
        concat(o.receiver_province, o.receiver_city, o.receiver_district, o.receiver_detail) AS "收货地址",
        oi.package_name AS "商品名称",
        oi.quantity AS "数量",
        oi.price AS "单价",
        oi.subtotal AS "小计",
        o.remark AS "备注",
        ${companyCaseExpr()} AS "物流公司",
        l.tracking_no AS "物流单号",
        CASE o.status
          WHEN 0 THEN '待处理'
          WHEN 1 THEN '已完成'
          WHEN 2 THEN '已取消'
          WHEN 3 THEN '已发货'
        END AS "订单状态"
      FROM gift_order o
      JOIN gift_card c ON c.id = o.card_id AND c.del_flag = '0'
      JOIN gift_order_item oi ON oi.order_id = o.id AND oi.del_flag = '0'
      LEFT JOIN gift_logistics l ON l.order_id = o.id AND l.del_flag = '0'
      WHERE o.del_flag = '0'
    `;

    const params = [];
    let paramIndex = 1;

    if (status !== undefined && status !== null && status !== '') {
      sql += ` AND o.status = $${paramIndex}`;
      params.push(status);
      paramIndex++;
    }
    if (batch_no) {
      sql += ` AND c.batch_no = $${paramIndex}`;
      params.push(batch_no);
      paramIndex++;
    }
    if (card_no) {
      sql += ` AND o.card_no = $${paramIndex}`;
      params.push(card_no);
      paramIndex++;
    }
    if (order_no) {
      sql += ` AND o.order_no = $${paramIndex}`;
      params.push(order_no);
      paramIndex++;
    }
    if (start_date) {
      sql += ` AND o.create_time >= $${paramIndex}`;
      params.push(start_date);
      paramIndex++;
    }
    if (end_date) {
      sql += ` AND o.create_time < $${paramIndex}`;
      params.push(end_date);
      paramIndex++;
    }

    sql += ' ORDER BY c.batch_no, o.create_time, o.order_no';

    const result = await req.db.query(sql, params);

    const workbook = new ExcelJS.Workbook();
    const sheet = workbook.addWorksheet('订单列表');

    const columns = [
      { header: '批次号', key: '批次号', width: 20 },
      { header: '订单号', key: '订单号', width: 18 },
      { header: '下单时间', key: '下单时间', width: 20 },
      { header: '卡号', key: '卡号', width: 15 },
      { header: '部门', key: '部门', width: 12 },
      { header: '工号', key: '工号', width: 12 },
      { header: '员工姓名', key: '员工姓名', width: 12 },
      { header: '收货人', key: '收货人', width: 12 },
      { header: '手机号', key: '手机号', width: 15 },
      { header: '收货地址', key: '收货地址', width: 40 },
      { header: '商品名称', key: '商品名称', width: 20 },
      { header: '数量', key: '数量', width: 8 },
      { header: '单价', key: '单价', width: 10 },
      { header: '小计', key: '小计', width: 10 },
      { header: '备注', key: '备注', width: 20 },
      { header: '物流公司', key: '物流公司', width: 15 },
      { header: '物流单号', key: '物流单号', width: 20 },
      { header: '订单状态', key: '订单状态', width: 12 },
    ];

    sheet.columns = columns;

    // 创建隐藏sheet存放物流公司列表，用于数据验证
    const hiddenSheet = workbook.addWorksheet('logistics_list', { properties: { tabVisible: false } });
    const companyNames = Object.values(CODE_TO_NAME);
    companyNames.forEach((name, i) => {
      hiddenSheet.getCell(i + 1, 1).value = name;
    });

    result.rows.forEach(row => {
      sheet.addRow(row);
    });

    // 给物流公司列(P列)加下拉验证
    const lastRow = result.rows.length + 1;
    if (lastRow > 1) {
      sheet.dataValidations.add(`P2:P${lastRow}`, {
        type: 'list',
        allowBlank: true,
        formulae: ['logistics_list!$A$1:$A$' + companyNames.length],
        showErrorMessage: true,
        errorTitle: '无效物流公司',
        error: '请从下拉列表中选择物流公司',
      });
    }

    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', `attachment; filename=orders_${Date.now()}.xlsx`);

    await workbook.xlsx.write(res);
    res.end();
    await logOperation(req.db, req.user.username, req.user.role, '导出Excel', `导出${result.rows.length}条订单`, req.ip);
  } catch (err) {
    console.error('导出Excel失败:', err);
    res.status(500).json({ success: false, message: err.message });
  }
});

// 导入运单信息 - 批量更新物流（不允许重复发货）
router.post('/import/logistics', authMiddleware, dbPool, upload.single('file'), async (req, res) => {
  try {
    if (!req.file) {
      return res.status(400).json({ success: false, message: '请上传文件' });
    }

    const workbook = new ExcelJS.Workbook();
    await workbook.xlsx.load(req.file.buffer);

    const sheet = workbook.worksheets[0];
    const rows = [];
    const headers = [];

    sheet.eachRow((row, rowNumber) => {
      if (rowNumber === 1) {
        row.eachCell((cell, colNumber) => {
          headers[colNumber] = cell.value;
        });
      } else {
        const rowData = {};
        row.eachCell((cell, colNumber) => {
          rowData[headers[colNumber]] = cell.value;
        });
        rows.push(rowData);
      }
    });

    const results = { success: 0, failed: 0, errors: [] };

    // 去重：同一订单号只取第一行（导出表一个订单可能有多商品行）
    const seenOrders = new Set();
    const uniqueRows = [];
    for (const row of rows) {
      const orderNo = row['订单号'] || row['order_no'];
      if (orderNo && !seenOrders.has(orderNo)) {
        seenOrders.add(orderNo);
        uniqueRows.push(row);
      }
    }

    for (let i = 0; i < uniqueRows.length; i++) {
      const row = uniqueRows[i];
      const orderNo = row['订单号'] || row['order_no'];
      let company = row['物流公司'] || row['company'];
      const trackingNo = row['物流单号'] || row['tracking_no'];
      const remark = row['备注'] || row['remark'];

      // 验证物流公司
      if (!company) {
        results.failed++;
        results.errors.push(`第${i + 2}行: 缺少物流公司`);
        continue;
      }
      // 物流公司中文名转代码
      if (NAME_TO_CODE[company]) {
        company = NAME_TO_CODE[company];
      } else if (!CODE_TO_NAME[company]) {
        results.failed++;
        results.errors.push(`第${i + 2}行: 物流公司 "${company}" 无效，请从下拉列表中选择`);
        continue;
      }

      if (!orderNo) {
        results.failed++;
        results.errors.push(`第${i + 2}行: 缺少订单号`);
        continue;
      }

      if (!trackingNo || trackingNo === '-' || trackingNo === '—' || trackingNo === '--') {
        results.failed++;
        results.errors.push(`第${i + 2}行: 缺少物流单号`);
        continue;
      }

      try {
        await req.db.query('BEGIN');

        // 查询订单是否存在及状态
        const orderResult = await req.db.query(
          `SELECT id, status FROM gift_order WHERE order_no = $1 AND del_flag = '0'`,
          [orderNo]
        );

        if (orderResult.rows.length === 0) {
          await req.db.query('ROLLBACK');
          results.failed++;
          results.errors.push(`第${i + 2}行: 订单 ${orderNo} 不存在`);
          continue;
        }

        const orderId = orderResult.rows[0].id;
        const orderStatus = orderResult.rows[0].status;

        // 不允许重复发货/已完成
        if (orderStatus === 3) {
          await req.db.query('ROLLBACK');
          results.failed++;
          results.errors.push(`第${i + 2}行: 订单 ${orderNo} 已发货，不能重复导入`);
          continue;
        }
        if (orderStatus === 1) {
          await req.db.query('ROLLBACK');
          results.failed++;
          results.errors.push(`第${i + 2}行: 订单 ${orderNo} 已完成，不能导入`);
          continue;
        }

        // 更新订单状态为已发货，同时更新备注
        await req.db.query(
          `UPDATE gift_order SET status = 3, remark = COALESCE($1, remark), update_time = now(), update_by = $2
           WHERE id = $3 AND del_flag = '0'`,
          [remark, 'import', orderId]
        );

        // 插入或更新物流信息
        await req.db.query(
          `INSERT INTO gift_logistics (order_id, order_no, company, tracking_no, status, waybill_status, create_time, create_by, update_time, update_by, del_flag)
           VALUES ($1, $2, $3, $4, 0, '已发货', now(), 'import', now(), 'import', '0')
           ON CONFLICT (order_id) DO UPDATE
           SET tracking_no = $4, company = $3, status = 0, waybill_status = '已发货', update_time = now(), update_by = 'import', del_flag = '0'`,
          [orderId, orderNo, company || '', trackingNo]
        );

        await req.db.query('COMMIT');
        results.success++;
      } catch (err) {
        await req.db.query('ROLLBACK');
        results.failed++;
        results.errors.push(`第${i + 2}行: ${err.message}`);
      }
    }

    await logOperation(req.db, req.user.username, req.user.role, '导入运单', `成功${results.success}条, 失败${results.failed}条`, req.ip);
    res.json({ success: true, results });
  } catch (err) {
    console.error('导入运单失败:', err);
    res.status(500).json({ success: false, message: err.message });
  }
});

// 下载导入模板
router.get('/import/template', authMiddleware, dbPool, async (req, res) => {
  try {
    const workbook = new ExcelJS.Workbook();
    const sheet = workbook.addWorksheet('运单导入模板');

    sheet.columns = [
      { header: '订单号', key: '订单号', width: 20 },
      { header: '物流公司', key: '物流公司', width: 18 },
      { header: '物流单号', key: '物流单号', width: 25 },
      { header: '备注', key: '备注', width: 30 },
    ];

    // 创建隐藏sheet存放物流公司列表
    const hiddenSheet = workbook.addWorksheet('logistics_list', { properties: { tabVisible: false } });
    const companyNames = Object.values(CODE_TO_NAME);
    companyNames.forEach((name, i) => {
      hiddenSheet.getCell(i + 1, 1).value = name;
    });

    // 数据验证引用隐藏sheet的范围
    const dataValidation = {
      type: 'list',
      allowBlank: false,
      formulae: ['logistics_list!$A$1:$A$' + companyNames.length],
      showErrorMessage: true,
      errorTitle: '无效物流公司',
      error: '请从下拉列表中选择物流公司',
    };
    sheet.dataValidations.add('B2:B1000', dataValidation);

    // 添加示例数据
    sheet.addRow({ '订单号': 'LP202607210007', '物流公司': '圆通快递', '物流单号': 'YT7631984758887', '备注': '示例备注' });
    sheet.addRow({ '订单号': 'LP202607210008', '物流公司': '顺丰速运', '物流单号': 'YT7631984758888' });

    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', 'attachment; filename=import_template.xlsx');

    await workbook.xlsx.write(res);
    res.end();
    await logOperation(req.db, req.user.username, req.user.role, '下载导入模板', '下载运单导入模板', req.ip);
  } catch (err) {
    console.error('下载模板失败:', err);
    res.status(500).json({ success: false, message: err.message });
  }
});

// 统计数据接口
router.get('/stats', dbPool, async (req, res) => {
  try {
    const result = await req.db.query(`
      SELECT
        COUNT(*) AS total,
        COUNT(*) FILTER (WHERE o.status = 0) AS pending,
        COUNT(*) FILTER (WHERE o.status = 1) AS completed,
        COUNT(*) FILTER (WHERE o.status = 2) AS cancelled,
        COUNT(*) FILTER (WHERE o.status = 3) AS shipped
      FROM gift_order o
      WHERE o.del_flag = '0'
    `);
    const row = result.rows[0];
    res.json({
      success: true,
      data: {
        total: parseInt(row.total),
        pending: parseInt(row.pending),
        completed: parseInt(row.completed),
        cancelled: parseInt(row.cancelled),
        shipped: parseInt(row.shipped),
      }
    });
  } catch (err) {
    console.error('获取统计失败:', err);
    res.status(500).json({ success: false, message: err.message });
  }
});

// 查询操作日志（仅admin）
router.get('/logs', authMiddleware, adminOnly, dbPool, async (req, res) => {
  try {
    const { page = 1, pageSize = 20, username, action, start_date, end_date } = req.query;
    let sql = `SELECT * FROM sys_operation_log WHERE 1=1`;
    const params = [];
    let paramIndex = 1;

    if (username) {
      sql += ` AND username = $${paramIndex}`;
      params.push(username);
      paramIndex++;
    }
    if (action) {
      sql += ` AND action LIKE $${paramIndex}`;
      params.push(`%${action}%`);
      paramIndex++;
    }
    if (start_date) {
      sql += ` AND create_time >= $${paramIndex}`;
      params.push(start_date);
      paramIndex++;
    }
    if (end_date) {
      sql += ` AND create_time < $${paramIndex}::timestamp + interval '1 day'`;
      params.push(end_date);
      paramIndex++;
    }

    // 总数
    const countSql = sql.replace('SELECT *', 'SELECT COUNT(*) as total');
    const countResult = await req.db.query(countSql, params);
    const total = parseInt(countResult.rows[0].total);

    sql += ' ORDER BY create_time DESC';
    sql += ` LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`;
    params.push(pageSize, (page - 1) * pageSize);

    const result = await req.db.query(sql, params);
    res.json({ success: true, data: result.rows, total, page: parseInt(page), pageSize: parseInt(pageSize) });
  } catch (err) {
    console.error('查询日志失败:', err);
    res.status(500).json({ success: false, message: err.message });
  }
});

module.exports = router;