const { pool } = require('../config/database');

async function addSampleOrders() {
  try {
    // 检查时区
    const [tz] = await pool.query('SELECT @@session.time_zone, NOW() as now');
    console.log('MySQL时区设置:', tz[0]);

    // 获取用户和菜品
    const [users] = await pool.query('SELECT id FROM users LIMIT 2');
    const [dishes] = await pool.query('SELECT id, name, price FROM dishes LIMIT 3');

    if (!users.length || !dishes.length) {
      console.log('缺少用户或菜品数据');
      process.exit(1);
    }

    // 直接插入1月7日和1月10日的订单
    const orders = [
      { no: 'ORD20260107001', datetime: '2026-01-07 12:00:00', amount: 320 },
      { no: 'ORD20260107002', datetime: '2026-01-07 19:30:00', amount: 280 },
      { no: 'ORD20260110001', datetime: '2026-01-10 13:00:00', amount: 195 },
      { no: 'ORD20260110002', datetime: '2026-01-10 20:00:00', amount: 385 }
    ];

    for (const o of orders) {
      const [result] = await pool.query(
        `INSERT INTO orders (order_no, customer_id, waiter_id, order_type, table_number, total_amount, final_amount, status, created_at, updated_at)
         VALUES (?, ?, ?, 'dine_in', 5, ?, ?, 'confirmed', ?, ?)`,
        [o.no, users[0].id, users[1]?.id || users[0].id, o.amount, o.amount, o.datetime, o.datetime]
      );

      const dish = dishes[0];
      const qty = Math.ceil(o.amount / parseFloat(dish.price));

      await pool.query(
        `INSERT INTO order_items (order_id, dish_id, dish_name, quantity, price, subtotal, created_at)
         VALUES (?, ?, ?, ?, ?, ?, ?)`,
        [result.insertId, dish.id, dish.name, qty, dish.price, o.amount, o.datetime]
      );

      console.log(`✅ 插入: ${o.no} at ${o.datetime}, 金额: ¥${o.amount}`);
    }

    // 验证结果 - 查看所有日期
    const [all] = await pool.query(
      `SELECT
        DATE_FORMAT(created_at, '%Y-%m-%d') as date,
        COUNT(*) as count,
        SUM(final_amount) as revenue
       FROM orders
       WHERE status NOT IN ('cancelled')
       GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d')
       ORDER BY date ASC`
    );

    console.log('\n所有订单按日期统计:');
    console.table(all);

    await pool.end();
    process.exit(0);
  } catch (error) {
    console.error('错误:', error.message);
    process.exit(1);
  }
}

addSampleOrders();