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 = (page - 1) * pageSize;

      let sql = `
        SELECT tp.*, u.username, u.real_name, u.avatar_url
        FROM travel_plans tp
        INNER JOIN users u ON tp.user_id = u.id
        WHERE 1=1
      `;
      const params = [];

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

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

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

      // 添加分页和排序
      sql += ' ORDER BY tp.created_at DESC LIMIT ? OFFSET ?';
      params.push(pageSize, offset);

      const plans = await query(sql, 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.user_id = 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 {
        destination,
        start_date,
        end_date,
        budget,
        companions,
        transportation,
        accommodation,
        activities,
        notes
      } = planData;

      const sql = `
        INSERT INTO travel_plans (
          user_id, destination, start_date, end_date, budget, companions,
          transportation, accommodation, activities, notes, status, created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'planning', NOW(), NOW())
      `;

      const params = [
        userId,
        destination,
        start_date,
        end_date,
        budget,
        companions,
        transportation,
        accommodation,
        activities,
        notes
      ];

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

  // 更新旅行计划
  static async updateTravelPlan(planId, userId, updateData) {
    try {
      const allowedFields = [
        'destination', 'start_date', 'end_date', 'budget', 'companions',
        'transportation', 'accommodation', 'activities', 'notes', 'status'
      ];
      
      const setClauses = [];
      const params = [];

      for (const [key, value] of Object.entries(updateData)) {
        if (allowedFields.includes(key) && value !== undefined) {
          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 user_id = ?
      `;

      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 user_id = ?';
      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(budget) as total_budget
        FROM travel_plans 
        WHERE user_id = ?
      `;

      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;