const db = require('../config/database');

class Payment {
  /**
   * 创建支付订单
   * @param {Object} paymentData - 支付订单数据
   * @returns {Object} 创建的支付订单
   */
  static async create(paymentData) {
    const {
      payment_no,
      order_id,
      user_id,
      amount,
      payment_method,
      return_url,
      notify_url
    } = paymentData;

    const query = `
      INSERT INTO payments (
        payment_no, order_id, user_id, amount, payment_method, 
        return_url, notify_url, status, created_at, updated_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, 'pending', NOW(), NOW())
    `;

    const [result] = await db.execute(query, [
      payment_no, order_id, user_id, amount, payment_method,
      return_url, notify_url
    ]);

    return this.findById(result.insertId);
  }

  /**
   * 根据ID查找支付订单
   * @param {number} id - 支付订单ID
   * @returns {Object|null} 支付订单信息
   */
  static async findById(id) {
    const query = `
      SELECT p.*, o.order_no, u.username, u.phone
      FROM payments p
      LEFT JOIN orders o ON p.order_id = o.id
      LEFT JOIN users u ON p.user_id = u.id
      WHERE p.id = ? AND p.deleted_at IS NULL
    `;

    const [rows] = await db.execute(query, [id]);
    return rows[0] || null;
  }

  /**
   * 根据支付订单号查找支付订单
   * @param {string} paymentNo - 支付订单号
   * @returns {Object|null} 支付订单信息
   */
  static async findByPaymentNo(paymentNo) {
    const query = `
      SELECT p.*, o.order_no, u.username, u.phone
      FROM payments p
      LEFT JOIN orders o ON p.order_id = o.id
      LEFT JOIN users u ON p.user_id = u.id
      WHERE p.payment_no = ? AND p.deleted_at IS NULL
    `;

    const [rows] = await db.execute(query, [paymentNo]);
    return rows[0] || null;
  }

  /**
   * 根据订单ID查找支付订单
   * @param {number} orderId - 订单ID
   * @returns {Array} 支付订单列表
   */
  static async findByOrderId(orderId) {
    const query = `
      SELECT * FROM payments
      WHERE order_id = ? AND deleted_at IS NULL
      ORDER BY created_at DESC
    `;

    const [rows] = await db.execute(query, [orderId]);
    return rows;
  }

  /**
   * 更新支付状态
   * @param {number} id - 支付订单ID
   * @param {Object} updateData - 更新数据
   * @returns {Object} 更新后的支付订单
   */
  static async updateStatus(id, updateData) {
    const {
      status,
      transaction_id,
      paid_amount,
      paid_at,
      failure_reason
    } = updateData;

    const query = `
      UPDATE payments 
      SET status = ?, transaction_id = ?, paid_amount = ?, 
          paid_at = ?, failure_reason = ?, updated_at = NOW()
      WHERE id = ? AND deleted_at IS NULL
    `;

    await db.execute(query, [
      status, transaction_id, paid_amount, 
      paid_at, failure_reason, id
    ]);

    return this.findById(id);
  }

  /**
   * 获取用户支付订单列表
   * @param {number} userId - 用户ID
   * @param {Object} options - 查询选项
   * @returns {Object} 分页结果
   */
  static async getUserPayments(userId, options = {}) {
    const {
      page = 1,
      limit = 10,
      status,
      payment_method,
      start_date,
      end_date
    } = options;

    const offset = (page - 1) * limit;
    let whereConditions = ['p.user_id = ?', 'p.deleted_at IS NULL'];
    let params = [userId];

    // 添加筛选条件
    if (status) {
      whereConditions.push('p.status = ?');
      params.push(status);
    }

    if (payment_method) {
      whereConditions.push('p.payment_method = ?');
      params.push(payment_method);
    }

    if (start_date) {
      whereConditions.push('DATE(p.created_at) >= ?');
      params.push(start_date);
    }

    if (end_date) {
      whereConditions.push('DATE(p.created_at) <= ?');
      params.push(end_date);
    }

    const whereClause = whereConditions.join(' AND ');

    // 查询总数
    const countQuery = `
      SELECT COUNT(*) as total
      FROM payments p
      WHERE ${whereClause}
    `;
    const [countResult] = await db.execute(countQuery, params);
    const total = countResult[0].total;

    // 查询数据
    const dataQuery = `
      SELECT p.*, o.order_no, o.title as order_title
      FROM payments p
      LEFT JOIN orders o ON p.order_id = o.id
      WHERE ${whereClause}
      ORDER BY p.created_at DESC
      LIMIT ? OFFSET ?
    `;
    params.push(limit, offset);
    const [rows] = await db.execute(dataQuery, params);

    return {
      data: rows,
      pagination: {
        page: parseInt(page),
        limit: parseInt(limit),
        total,
        pages: Math.ceil(total / limit)
      }
    };
  }

  /**
   * 获取所有支付订单列表（管理员）
   * @param {Object} options - 查询选项
   * @returns {Object} 分页结果
   */
  static async getAllPayments(options = {}) {
    const {
      page = 1,
      limit = 10,
      status,
      payment_method,
      user_id,
      start_date,
      end_date,
      keyword
    } = options;

    const offset = (page - 1) * limit;
    let whereConditions = ['p.deleted_at IS NULL'];
    let params = [];

    // 添加筛选条件
    if (status) {
      whereConditions.push('p.status = ?');
      params.push(status);
    }

    if (payment_method) {
      whereConditions.push('p.payment_method = ?');
      params.push(payment_method);
    }

    if (user_id) {
      whereConditions.push('p.user_id = ?');
      params.push(user_id);
    }

    if (start_date) {
      whereConditions.push('DATE(p.created_at) >= ?');
      params.push(start_date);
    }

    if (end_date) {
      whereConditions.push('DATE(p.created_at) <= ?');
      params.push(end_date);
    }

    if (keyword) {
      whereConditions.push('(p.payment_no LIKE ? OR o.order_no LIKE ? OR u.username LIKE ?)');
      const keywordPattern = `%${keyword}%`;
      params.push(keywordPattern, keywordPattern, keywordPattern);
    }

    const whereClause = whereConditions.join(' AND ');

    // 查询总数
    const countQuery = `
      SELECT COUNT(*) as total
      FROM payments p
      LEFT JOIN orders o ON p.order_id = o.id
      LEFT JOIN users u ON p.user_id = u.id
      WHERE ${whereClause}
    `;
    const [countResult] = await db.execute(countQuery, params);
    const total = countResult[0].total;

    // 查询数据
    const dataQuery = `
      SELECT p.*, o.order_no, o.title as order_title, 
             u.username, u.phone
      FROM payments p
      LEFT JOIN orders o ON p.order_id = o.id
      LEFT JOIN users u ON p.user_id = u.id
      WHERE ${whereClause}
      ORDER BY p.created_at DESC
      LIMIT ? OFFSET ?
    `;
    params.push(limit, offset);
    const [rows] = await db.execute(dataQuery, params);

    return {
      data: rows,
      pagination: {
        page: parseInt(page),
        limit: parseInt(limit),
        total,
        pages: Math.ceil(total / limit)
      }
    };
  }

  /**
   * 创建退款记录
   * @param {Object} refundData - 退款数据
   * @returns {Object} 创建的退款记录
   */
  static async createRefund(refundData) {
    const {
      refund_no,
      payment_id,
      user_id,
      refund_amount,
      refund_reason
    } = refundData;

    const query = `
      INSERT INTO refunds (
        refund_no, payment_id, user_id, refund_amount, 
        refund_reason, status, created_at, updated_at
      ) VALUES (?, ?, ?, ?, ?, 'pending', NOW(), NOW())
    `;

    const [result] = await db.execute(query, [
      refund_no, payment_id, user_id, refund_amount, refund_reason
    ]);

    return this.findRefundById(result.insertId);
  }

  /**
   * 根据ID查找退款记录
   * @param {number} id - 退款ID
   * @returns {Object|null} 退款记录
   */
  static async findRefundById(id) {
    const query = `
      SELECT r.*, p.payment_no, p.amount as payment_amount,
             u.username, u.phone,
             admin.username as processed_by_name
      FROM refunds r
      LEFT JOIN payments p ON r.payment_id = p.id
      LEFT JOIN users u ON r.user_id = u.id
      LEFT JOIN users admin ON r.processed_by = admin.id
      WHERE r.id = ? AND r.deleted_at IS NULL
    `;

    const [rows] = await db.execute(query, [id]);
    return rows[0] || null;
  }

  /**
   * 更新退款状态
   * @param {number} id - 退款ID
   * @param {Object} updateData - 更新数据
   * @returns {Object} 更新后的退款记录
   */
  static async updateRefundStatus(id, updateData) {
    const {
      status,
      processed_by,
      process_remark,
      refund_transaction_id,
      refunded_at
    } = updateData;

    const query = `
      UPDATE refunds 
      SET status = ?, processed_by = ?, process_remark = ?, 
          refund_transaction_id = ?, refunded_at = ?, 
          processed_at = NOW(), updated_at = NOW()
      WHERE id = ? AND deleted_at IS NULL
    `;

    await db.execute(query, [
      status, processed_by, process_remark,
      refund_transaction_id, refunded_at, id
    ]);

    return this.findRefundById(id);
  }

  /**
   * 获取支付统计信息
   * @param {Object} filters - 筛选条件
   * @returns {Object} 统计信息
   */
  static async getPaymentStatistics(filters = {}) {
    const {
      start_date,
      end_date,
      payment_method
    } = filters;

    let whereConditions = ['deleted_at IS NULL'];
    let params = [];

    if (start_date) {
      whereConditions.push('DATE(created_at) >= ?');
      params.push(start_date);
    }

    if (end_date) {
      whereConditions.push('DATE(created_at) <= ?');
      params.push(end_date);
    }

    if (payment_method) {
      whereConditions.push('payment_method = ?');
      params.push(payment_method);
    }

    const whereClause = whereConditions.join(' AND ');

    // 总体统计
    const totalQuery = `
      SELECT 
        COUNT(*) as total_count,
        COALESCE(SUM(amount), 0) as total_amount,
        COUNT(CASE WHEN status = 'paid' THEN 1 END) as success_count,
        COALESCE(SUM(CASE WHEN status = 'paid' THEN paid_amount END), 0) as success_amount
      FROM payments
      WHERE ${whereClause}
    `;
    const [totalResult] = await db.execute(totalQuery, params);

    // 退款统计
    const refundQuery = `
      SELECT 
        COUNT(*) as refund_count,
        COALESCE(SUM(refund_amount), 0) as refund_amount
      FROM refunds r
      JOIN payments p ON r.payment_id = p.id
      WHERE r.status = 'completed' AND r.deleted_at IS NULL
        ${start_date ? 'AND DATE(r.created_at) >= ?' : ''}
        ${end_date ? 'AND DATE(r.created_at) <= ?' : ''}
        ${payment_method ? 'AND p.payment_method = ?' : ''}
    `;
    let refundParams = [];
    if (start_date) refundParams.push(start_date);
    if (end_date) refundParams.push(end_date);
    if (payment_method) refundParams.push(payment_method);
    
    const [refundResult] = await db.execute(refundQuery, refundParams);

    // 按支付方式统计
    const methodQuery = `
      SELECT 
        payment_method,
        COUNT(*) as count,
        COALESCE(SUM(CASE WHEN status = 'paid' THEN paid_amount END), 0) as amount
      FROM payments
      WHERE ${whereClause}
      GROUP BY payment_method
    `;
    const [methodResult] = await db.execute(methodQuery, params);

    return {
      total_count: totalResult[0].total_count,
      total_amount: parseFloat(totalResult[0].total_amount),
      success_count: totalResult[0].success_count,
      success_amount: parseFloat(totalResult[0].success_amount),
      refund_count: refundResult[0].refund_count,
      refund_amount: parseFloat(refundResult[0].refund_amount),
      method_stats: methodResult.map(row => ({
        payment_method: row.payment_method,
        count: row.count,
        amount: parseFloat(row.amount)
      }))
    };
  }

  /**
   * 检查支付订单是否存在
   * @param {number} id - 支付订单ID
   * @returns {boolean} 是否存在
   */
  static async exists(id) {
    const query = 'SELECT 1 FROM payments WHERE id = ? AND deleted_at IS NULL';
    const [rows] = await db.execute(query, [id]);
    return rows.length > 0;
  }

  /**
   * 软删除支付订单
   * @param {number} id - 支付订单ID
   * @returns {boolean} 删除结果
   */
  static async softDelete(id) {
    const query = `
      UPDATE payments 
      SET deleted_at = NOW(), updated_at = NOW()
      WHERE id = ? AND deleted_at IS NULL
    `;

    const [result] = await db.execute(query, [id]);
    return result.affectedRows > 0;
  }

  /**
   * 数据清理 - 删除过期的待支付订单
   * @param {number} hours - 过期小时数，默认24小时
   * @returns {number} 清理的记录数
   */
  static async cleanExpiredPayments(hours = 24) {
    const query = `
      UPDATE payments 
      SET status = 'cancelled', updated_at = NOW()
      WHERE status = 'pending' 
        AND created_at < DATE_SUB(NOW(), INTERVAL ? HOUR)
        AND deleted_at IS NULL
    `;

    const [result] = await db.execute(query, [hours]);
    return result.affectedRows;
  }
}

module.exports = Payment;