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

/**
 * 商户模型类
 * 处理商户相关的数据库操作
 */
class Merchant {
  // 根据ID查找商户
  static async findById(id) {
    try {
      const sql = 'SELECT * FROM merchants WHERE id = ?';
      const rows = await query(sql, [id]);
      return rows.length > 0 ? rows[0] : null;
    } catch (error) {
      console.error('查找商户失败:', error);
      throw error;
    }
  }

  // 获取商户列表（支持分页和筛选）
  static async getMerchantList(options = {}) {
    try {
      const {
        page = 1,
        limit = 20,
        keyword = '',
        status = '',
        type = ''
      } = options;

      const offset = (page - 1) * limit;
      let whereConditions = [];
      let params = [];

      // 构建查询条件
      if (keyword) {
        whereConditions.push('(name LIKE ? OR contact_person LIKE ? OR contact_phone LIKE ?)');
        const keywordPattern = `%${keyword}%`;
        params.push(keywordPattern, keywordPattern, keywordPattern);
      }

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

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

      const whereClause = whereConditions.length > 0 ? `WHERE ${whereConditions.join(' AND ')}` : '';

      // 查询总数
      const countSql = `SELECT COUNT(*) as total FROM merchants ${whereClause}`;
      const countResult = await query(countSql, params);
      const total = countResult && countResult.length > 0 ? countResult[0].total : 0;

      // 查询数据
      const dataSql = `SELECT * FROM merchants ${whereClause} ORDER BY created_at DESC LIMIT ${parseInt(limit)} OFFSET ${parseInt(offset)}`;
      const rows = await query(dataSql, params);

      return {
        data: rows,
        pagination: {
          page: parseInt(page),
          limit: parseInt(limit),
          total,
          totalPages: Math.ceil(total / limit)
        }
      };
    } catch (error) {
      console.error('获取商户列表失败:', error);
      throw error;
    }
  }

  // 创建商户
  static async create(merchantData) {
    try {
      const {
        name,
        type,
        contact_person,
        contact_phone,
        email = null,
        address = null,
        description = null
      } = merchantData;

      const sql = `
        INSERT INTO merchants (name, type, contact_person, contact_phone, email, address, description, status, created_at, updated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, 'active', NOW(), NOW())
      `;
      
      const params = [name, type, contact_person, contact_phone, email, address, description];
      const result = await query(sql, params);
      
      // 返回创建的商户信息
      return await this.findById(result.insertId);
    } catch (error) {
      console.error('创建商户失败:', error);
      throw error;
    }
  }

  // 更新商户信息
  static async update(id, merchantData) {
    try {
      const updateFields = [];
      const params = [];

      // 动态构建更新字段
      const allowedFields = ['name', 'type', 'contact_person', 'contact_phone', 'email', 'address', 'description', 'status'];
      
      for (const field of allowedFields) {
        if (merchantData[field] !== undefined) {
          updateFields.push(`${field} = ?`);
          params.push(merchantData[field]);
        }
      }

      if (updateFields.length === 0) {
        throw new Error('没有提供要更新的字段');
      }

      updateFields.push('updated_at = NOW()');
      params.push(id);

      const sql = `UPDATE merchants SET ${updateFields.join(', ')} WHERE id = ?`;
      const result = await query(sql, params);

      if (result.affectedRows === 0) {
        throw new Error('商户不存在或更新失败');
      }

      // 返回更新后的商户信息
      return await this.findById(id);
    } catch (error) {
      console.error('更新商户失败:', error);
      throw error;
    }
  }

  // 删除商户
  static async delete(id) {
    try {
      const sql = 'DELETE FROM merchants WHERE id = ?';
      const result = await query(sql, [id]);
      
      if (result.affectedRows === 0) {
        throw new Error('商户不存在或删除失败');
      }

      return true;
    } catch (error) {
      console.error('删除商户失败:', error);
      throw error;
    }
  }

  // 获取商户详情（包含统计信息）
  static async getDetailWithStats(id) {
    try {
      const merchant = await this.findById(id);
      if (!merchant) {
        return null;
      }

      // 获取关联的动物数量
      const animalCountSql = 'SELECT COUNT(*) as count FROM animals WHERE merchant_id = ?';
      const animalResult = await query(animalCountSql, [id]);
      
      // 获取关联的订单数量
      const orderCountSql = 'SELECT COUNT(*) as count FROM orders WHERE merchant_id = ?';
      const orderResult = await query(orderCountSql, [id]);

      return {
        ...merchant,
        animal_count: animalResult && animalResult.length > 0 ? animalResult[0].count : 0,
        order_count: orderResult && orderResult.length > 0 ? orderResult[0].count : 0
      };
    } catch (error) {
      console.error('获取商户详情失败:', error);
      throw error;
    }
  }

  // 获取商户统计信息
  static async getStatistics() {
    try {
      const sql = `
        SELECT 
          COUNT(*) as total,
          SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active,
          SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) as inactive,
          SUM(CASE WHEN status = 'banned' THEN 1 ELSE 0 END) as banned,
          SUM(CASE WHEN type = 'individual' THEN 1 ELSE 0 END) as individual,
          SUM(CASE WHEN type = 'company' THEN 1 ELSE 0 END) as company
        FROM merchants
      `;
      
      const rows = await query(sql);
      return rows[0];
    } catch (error) {
      console.error('获取商户统计信息失败:', error);
      throw error;
    }
  }
}

module.exports = Merchant;