const {mysqlPool} = require('../libs/mysql');
const {logger} = require('../libs/logger');
const londonList = require('../data/london.json');
const nodemailer = require('nodemailer');
const _ = require('lodash');
const dayjs = require('dayjs');
const {dataToExcel} = require('../libs/excel');

const mails = ['feng.chen@yoho.cn', 'li.ma@yoho.cn', 'liu.cao@yoho.cn', 'dee.di@yoho.cn'];

module.exports = async() => {
  try {
    const orders = await mysqlPool.query('select s.product_id, s.size_id, o.order_code,count(*) as count from ufo_order.buyer_order o, ufo_order.orders_pay p, ufo_order.buyer_order_goods g, ufo_order.seller_order_goods s where g.order_code = o.order_code and s.id = g.skup and o.order_code = p.order_code and o.seller_uid = 8472177 and o.status = 1 and p.create_time >= (UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE)) - 28800) and p.create_time < (UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE)) + 57600) group by s.product_id, s.size_id, o.order_code');
    const products = await mysqlPool.query(`select p.product_code,p.id as pid, s.size_name,s.id as size_id from ufo_product.product p, ufo_product.size s, ufo_product.storage u where p.id = u.product_id and s.id = u.size_id and p.id in (${orders.map(o => o.product_id)}) and u.size_id in (${orders.map(o => o.size_id)})`);

    const data = orders.map(o => {
      const product = products.find(p => p.pid === o.product_id && p.size_id === o.size_id);
      const find = londonList.find(l => l.productCode === product.product_code && l.size === product.size_name);

      return [find.sku, find.productCode, find.size, find.color, o.order_code, o.count];
    });
    const header = [['有货sku', 'UFO货号', '尺码', '颜色', '订单号', '数量']];

    const date = dayjs().format('YYYY-MM-DD');
    const mailOptions = {
      from: '"automan" <automan@yoho.cn>',
      to: _.join(mails, ','),
      subject: `【boylondon待发货单】${date}`,
      text: `附件为${date}日UFO待发货单`,
      attachments: [{
        filename: `${date}_UFO.xlsx`,
        content: dataToExcel(header, data)
      }]
    };

    const transporter = nodemailer.createTransport({
      service: 'QQex',
      auth: {
        user: 'automan@yoho.cn',
        pass: '8AE83e639d6b65f7'
      }
    });

    transporter.sendMail(mailOptions, (error, info) => {
      if (error) {
        return this.error(error);
      }
      logger.info(`[london-task] Message sent ${info.messageId}, To:${mailOptions.to}`);
    });

    logger.info(`[london-task] ${JSON.stringify(data)}`);
  } catch (error) {
    logger.error(`[london-task]${error.message}`);
  }
};