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

class TravelService {
  // 获取旅行计划列表
  static async getTravelPlans(searchParams) {
    try {
      const { userId, page = 1, pageSize = 10, status } = searchParams;
      const offset = (parseInt(page) - 1) * parseInt(pageSize);

      // 构建基础查询条件
      let whereClause = 'WHERE 1=1';
      const params = [];

      if (userId) {
        whereClause += ' AND tp.created_by = ?';
        params.push(userId);
      }

      if (status) {
        whereClause += ' AND tp.status = ?';
        params.push(status);
      }

      // 获取总数
      const countSql = `SELECT COUNT(*) as total FROM travel_plans tp ${whereClause}`;
      const countResult = await query(countSql, params);
      const total = countResult[0].total;

      // 获取数据 - 使用简单的查询方式
      const dataSql = `
        SELECT tp.*, u.username, u.real_name, u.avatar_url
        FROM travel_plans tp
        INNER JOIN users u ON tp.created_by = u.id
        ${whereClause}
        ORDER BY tp.created_at DESC
        LIMIT ${parseInt(pageSize)} OFFSET ${parseInt(offset)}
      `;
      
      console.log('执行SQL:', dataSql);
      console.log('参数:', params);
      
      const plans = await query(dataSql, params);
      
      return {
        plans: plans.map(plan => this.sanitizePlan(plan)),
        pagination: {
          page: parseInt(page),
          pageSize: parseInt(pageSize),
          total: parseInt(total),
          totalPages: Math.ceil(total / pageSize)
        }
      };
    } catch (error) {
      throw error;
    }
  }

  // 获取单个旅行计划详情
  static async getTravelPlanById(planId) {
    try {
      const sql = `
        SELECT tp.*, u.username, u.real_name, u.avatar_url
        FROM travel_plans tp
        INNER JOIN users u ON tp.created_by = u.id
        WHERE tp.id = ?
      `;
      
      const plans = await query(sql, [planId]);
      if (plans.length === 0) {
        throw new AppError('旅行计划不存在', 404);
      }

      return this.sanitizePlan(plans[0]);
    } catch (error) {
      throw error;
    }
  }

  // 创建旅行计划
  static async createTravelPlan(userId, planData) {
    try {
      const {
        title,
        description,
        destination,
        start_date,
        end_date,
        max_participants,
        price_per_person,
        itinerary,
        requirements,
        includes,
        excludes,
        images
      } = planData;

      const sql = `
        INSERT INTO travel_plans (
          created_by, title, description, destination, start_date, end_date, 
          max_participants, price_per_person, itinerary, 
          requirements, includes, excludes, images,
          status, created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'published', NOW(), NOW())
      `;

      const params = [
        userId,
        title,
        description || null,
        destination,
        start_date,
        end_date,
        max_participants || 20,
        price_per_person,
        JSON.stringify(itinerary || []),
        requirements || null,
        JSON.stringify(includes || []),
        JSON.stringify(excludes || []),
        JSON.stringify(images || [])
      ];

      // 调试：检查参数中是否有undefined
      console.log('SQL Parameters:', params);
      params.forEach((param, index) => {
        if (param === undefined) {
          console.log(`Parameter at index ${index} is undefined`);
        }
      });

      const result = await query(sql, params);
      return result.insertId;
    } catch (error) {
      throw error;
    }
  }

  // 更新旅行计划
  static async updateTravelPlan(planId, userId, updateData) {
    try {
      const allowedFields = [
        'title', 'description', 'destination', 'start_date', 'end_date', 
        'max_participants', 'price_per_person', 'includes', 'excludes',
        'itinerary', 'images', 'requirements', 'status'
      ];
      
      const setClauses = [];
      const params = [];

      for (const [key, value] of Object.entries(updateData)) {
        if (allowedFields.includes(key) && value !== undefined) {
          if (Array.isArray(value)) {
            setClauses.push(`${key} = ?`);
            params.push(JSON.stringify(value));
          } else {
            setClauses.push(`${key} = ?`);
            params.push(value);
          }
        }
      }

      if (setClauses.length === 0) {
        throw new AppError('没有有效的更新字段', 400);
      }

      setClauses.push('updated_at = NOW()');
      params.push(planId, userId);

      const sql = `
        UPDATE travel_plans 
        SET ${setClauses.join(', ')} 
        WHERE id = ? AND created_by = ?
      `;

      const result = await query(sql, params);
      if (result.affectedRows === 0) {
        throw new AppError('旅行计划不存在或没有权限修改', 404);
      }

      return await this.getTravelPlanById(planId);
    } catch (error) {
      throw error;
    }
  }

  // 删除旅行计划
  static async deleteTravelPlan(planId, userId) {
    try {
      const sql = 'DELETE FROM travel_plans WHERE id = ? AND created_by = ?';
      const result = await query(sql, [planId, userId]);
      
      if (result.affectedRows === 0) {
        throw new AppError('旅行计划不存在或没有权限删除', 404);
      }

      return true;
    } catch (error) {
      throw error;
    }
  }

  // 获取用户旅行统计
  static async getUserTravelStats(userId) {
    try {
      const sql = `
        SELECT 
          COUNT(*) as total_plans,
          COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed_plans,
          COUNT(CASE WHEN status = 'planning' THEN 1 END) as planning_plans,
          COUNT(CASE WHEN status = 'cancelled' THEN 1 END) as cancelled_plans,
          SUM(price_per_person * max_participants) as total_budget
        FROM travel_plans 
        WHERE created_by = ?
      `;

      const stats = await query(sql, [userId]);
      return stats[0];
    } catch (error) {
      throw error;
    }
  }

  // 安全返回旅行计划信息
  static sanitizePlan(plan) {
    if (!plan) return null;
    
    const sanitized = { ...plan };
    // 移除敏感信息或格式化数据
    return sanitized;
  }
}

module.exports = TravelService;