// MySQL 后端：订单一张表，激活码单独一张表（唯一索引保证一单一码、码不重复）
const { getPool, initSchema } = require('./db.js')
const { GOODS } = require('./sign.js')
const { newActivationCode } = require('./code.js')

const ORDER_COLUMNS = [
  'out_trade_no',
  'openid',
  'product_id',
  'env',
  'channel',
  'amount',
  'status',
  'wx_order_id',
  'paid_fee',
  'notified',
  'refunded',
  'refund_order_id',
  'created_at',
  'delivered_at',
  'refunded_at',
]

const SELECT_ORDER = 'SELECT o.*, c.code FROM orders o LEFT JOIN codes c ON c.out_trade_no = o.out_trade_no'

const placeholders = (n) => Array(n).fill('?').join(', ')

// 库里存可读的 DATETIME，对外（接口、内存）还是毫秒时间戳，只在读写边界转换
const toDbDate = (ms) => (ms == null ? null : new Date(Number(ms)))
const toMs = (value) => (value == null ? null : value instanceof Date ? value.getTime() : Number(value))

function toRecord(row) {
  return {
    outTradeNo: row.out_trade_no,
    openid: row.openid,
    productId: row.product_id,
    env: row.env,
    channel: row.channel || '',
    amount: row.amount,
    status: row.status,
    code: row.code || '',
    wxOrderId: row.wx_order_id || '',
    paidFee: row.paid_fee,
    notified: Boolean(row.notified),
    refunded: Boolean(row.refunded),
    refundOrderId: row.refund_order_id || '',
    createdAt: toMs(row.created_at),
    deliveredAt: toMs(row.delivered_at),
    refundedAt: toMs(row.refunded_at),
  }
}

async function saveOrder(record) {
  const db = getPool()
  const values = [
    record.outTradeNo,
    record.openid,
    String(record.productId),
    record.env || 0,
    record.channel || null,
    record.amount || 0,
    record.status || 'created',
    record.wxOrderId || null,
    record.paidFee == null ? null : record.paidFee,
    record.notified ? 1 : 0,
    record.refunded ? 1 : 0,
    record.refundOrderId || null,
    toDbDate(record.createdAt || Date.now()),
    toDbDate(record.deliveredAt),
    toDbDate(record.refundedAt),
  ]
  const updates = ORDER_COLUMNS.filter((c) => c !== 'out_trade_no').map((c) => `${c} = VALUES(${c})`).join(', ')
  await db.query(
    `INSERT INTO orders (${ORDER_COLUMNS.join(', ')}) VALUES (${placeholders(ORDER_COLUMNS.length)}) ON DUPLICATE KEY UPDATE ${updates}`,
    values
  )
  if (record.code) {
    // 一单一码：out_trade_no 唯一索引兜底，重复写不动已有的码
    await db.query(
      `INSERT INTO codes (code, product_id, out_trade_no, openid, env, status, created_at, bound_at)
       VALUES (?, ?, ?, ?, ?, 'issued', ?, ?)
       ON DUPLICATE KEY UPDATE code = code`,
      [
        record.code,
        String(record.productId),
        record.outTradeNo,
        record.openid,
        record.env || 0,
        toDbDate(record.deliveredAt || Date.now()),
        toDbDate(record.deliveredAt || Date.now()),
      ]
    )
  }
  return record
}

module.exports = {
  backend: 'mysql',
  ready: () => initSchema(GOODS),
  get: async (outTradeNo) => {
    const [rows] = await getPool().query(`${SELECT_ORDER} WHERE o.out_trade_no = ?`, [outTradeNo])
    return rows.length ? toRecord(rows[0]) : null
  },
  saveOrder,

  // 发货：codes 表里已经有就直接返回（幂等）；撞号或并发重复发就重来一次
  assignCode: async (outTradeNo, extra) => {
    for (let attempt = 0; attempt < 5; attempt++) {
      const record = await module.exports.get(outTradeNo)
      if (!record) throw new Error('没有这笔业务单号：' + outTradeNo)
      if (record.code) return record
      const updated = Object.assign(
        {},
        record,
        { status: 'delivered', code: newActivationCode(), deliveredAt: Date.now(), notified: false },
        extra
      )
      try {
        await saveOrder(updated)
        return updated
      } catch (err) {
        if (err.code !== 'ER_DUP_ENTRY') throw err
      }
    }
    throw new Error('激活码连续撞号，请重试')
  },

  markNotified: async (outTradeNo) => {
    await getPool().query('UPDATE orders SET notified = 1 WHERE out_trade_no = ?', [outTradeNo])
  },

  markRefunded: async (outTradeNo, refundOrderId) => {
    const now = Date.now()
    await getPool().query('UPDATE orders SET refunded = 1, refund_order_id = ?, refunded_at = ? WHERE out_trade_no = ?', [
      refundOrderId,
      toDbDate(now),
      outTradeNo,
    ])
    // 码作废但不删除：将来做核销时按 status 拒绝
    await getPool().query(`UPDATE codes SET status = 'revoked', revoked_at = ? WHERE out_trade_no = ?`, [
      toDbDate(now),
      outTradeNo,
    ])
  },

  listByOpenid: async (openid) => {
    const [rows] = await getPool().query(
      `${SELECT_ORDER} WHERE o.openid = ? AND c.code IS NOT NULL AND o.refunded = 0 ORDER BY o.delivered_at DESC`,
      [openid]
    )
    return rows.map(toRecord)
  },

  listPendingByOpenid: async (openid) => {
    const [rows] = await getPool().query(
      `${SELECT_ORDER}
       WHERE o.openid = ? AND o.refunded = 0 AND (c.code IS NULL OR o.notified = 0)
       ORDER BY o.created_at DESC LIMIT 3`,
      [openid]
    )
    return rows.map(toRecord)
  },

  listPendingAll: async (since) => {
    const [rows] = await getPool().query(
      `${SELECT_ORDER}
       WHERE o.refunded = 0 AND o.created_at >= ? AND (c.code IS NULL OR o.notified = 0)
       ORDER BY o.created_at ASC`,
      [toDbDate(since)]
    )
    return rows.map(toRecord)
  },

  listGoods: async () => {
    const [rows] = await getPool().query(
      'SELECT product_id, name, price, enabled, admin_only, note FROM goods ORDER BY price ASC, product_id ASC'
    )
    return rows.map((row) => ({
      productId: row.product_id,
      name: row.name,
      price: row.price,
      enabled: Boolean(row.enabled),
      adminOnly: Boolean(row.admin_only),
      note: row.note || '',
    }))
  },

  // 用户信息（管理员标志就从这来）
  getUser: async (openid) => {
    const [rows] = await getPool().query('SELECT openid, created_at, last_seen_at, is_admin FROM users WHERE openid = ?', [
      openid,
    ])
    if (!rows.length) return null
    return {
      openid: rows[0].openid,
      createdAt: toMs(rows[0].created_at),
      lastSeenAt: toMs(rows[0].last_seen_at),
      isAdmin: Boolean(rows[0].is_admin),
    }
  },

  // 页面文案配置（settings 表里有什么就是什么，兜底在 index.js）
  getSettings: async () => {
    const [rows] = await getPool().query('SELECT cfg_key, cfg_value FROM settings')
    const settings = {}
    for (const row of rows) settings[row.cfg_key] = row.cfg_value
    return settings
  },

  // 用户首次出现就插一行，之后每次只更新活跃时间（首次使用时间不会被覆盖）
  touchUser: async (openid, firstSeenAt) => {
    const now = new Date()
    const created = firstSeenAt ? new Date(Number(firstSeenAt)) : now
    await getPool().query(
      `INSERT INTO users (openid, created_at, last_seen_at, is_admin) VALUES (?, ?, ?, 0)
       ON DUPLICATE KEY UPDATE last_seen_at = VALUES(last_seen_at)`,
      [openid, created, now]
    )
  },

  // 核对用：订单数、激活码数、未完成的单
  stats: async () => {
    const db = getPool()
    const [[orders], [codes], [pending], [users]] = await Promise.all([
      db.query('SELECT COUNT(*) AS n FROM orders'),
      db.query('SELECT COUNT(*) AS n FROM codes'),
      db.query(`SELECT COUNT(*) AS n FROM orders o LEFT JOIN codes c ON c.out_trade_no = o.out_trade_no
                WHERE o.refunded = 0 AND (c.code IS NULL OR o.notified = 0)`),
      db.query('SELECT COUNT(*) AS n FROM users'),
    ])
    return { orders: orders[0].n, codes: codes[0].n, pending: pending[0].n, users: users[0].n }
  },
}