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

class Tour {
  static async getAll(filters = {}) {
    let query = `
      SELECT t.*, sc.name as sub_category_name, sc.slug as sub_category_slug,
             c.name as category_name, c.slug as category_slug,
             a.name as airline_name, a.code as airline_code, a.logo_url as airline_logo
      FROM tours t
      LEFT JOIN sub_categories sc ON t.sub_category_id = sc.id
      LEFT JOIN categories c ON sc.category_id = c.id
      LEFT JOIN airlines a ON t.airline_id = a.id
      WHERE 1=1
    `;
    const params = [];

    if (filters.status) {
      query += ' AND t.status = ?';
      params.push(filters.status);
    }

    if (filters.featured !== undefined) {
      query += ' AND t.featured = ?';
      params.push(filters.featured ? 1 : 0);
    }

    if (filters.sub_category_id) {
      query += ' AND t.sub_category_id = ?';
      params.push(filters.sub_category_id);
    }

    if (filters.category_id) {
      query += ' AND sc.category_id = ?';
      params.push(filters.category_id);
    }

    query += ' ORDER BY t.created_at DESC';

    const [rows] = await db.query(query, params);

    // Parse JSON fields
    return rows.map(tour => this.parseJsonFields(tour));
  }

  static async getById(id) {
    const [rows] = await db.query(
      `SELECT t.*, sc.name as sub_category_name, sc.slug as sub_category_slug,
              c.name as category_name, c.slug as category_slug,
              a.name as airline_name, a.code as airline_code, a.logo_url as airline_logo
       FROM tours t
       LEFT JOIN sub_categories sc ON t.sub_category_id = sc.id
       LEFT JOIN categories c ON sc.category_id = c.id
       LEFT JOIN airlines a ON t.airline_id = a.id
       WHERE t.id = ?`,
      [id]
    );

    if (rows.length === 0) return null;
    return this.parseJsonFields(rows[0]);
  }

  static async getBySlug(slug) {
    const [rows] = await db.query(
      `SELECT t.*, sc.name as sub_category_name, sc.slug as sub_category_slug,
              c.name as category_name, c.slug as category_slug,
              a.name as airline_name, a.code as airline_code, a.logo_url as airline_logo
       FROM tours t
       LEFT JOIN sub_categories sc ON t.sub_category_id = sc.id
       LEFT JOIN categories c ON sc.category_id = c.id
       LEFT JOIN airlines a ON t.airline_id = a.id
       WHERE t.slug = ?`,
      [slug]
    );

    if (rows.length === 0) return null;

    // Increment view count
    await db.query('UPDATE tours SET views = views + 1 WHERE id = ?', [rows[0].id]);

    return this.parseJsonFields(rows[0]);
  }

  static async create(data) {
    const {
      sub_category_id, airline_id, title, slug, description, short_description,
      main_image, gallery, duration, tour_date, return_date, accommodation_type, route,
      tour_dates, registration_status, important_dates, hotel_info,
      pricing, included_services, excluded_services, itinerary,
      max_people, featured, status
    } = data;

    const [result] = await db.query(
      `INSERT INTO tours (
        sub_category_id, airline_id, title, slug, description, short_description,
        main_image, gallery, duration, tour_date, return_date, accommodation_type, route,
        tour_dates, registration_status, important_dates, hotel_info,
        pricing, included_services, excluded_services, itinerary,
        max_people, featured, status
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        sub_category_id, airline_id || null, title, slug, description || null, short_description || null,
        main_image || null,
        JSON.stringify(gallery || []),
        duration || null,
        tour_date || null,
        return_date || null,
        accommodation_type || null,
        route || null,
        JSON.stringify(tour_dates || []),
        registration_status || 'open',
        JSON.stringify(important_dates || []),
        JSON.stringify(hotel_info || []),
        JSON.stringify(pricing || {}),
        JSON.stringify(included_services || []),
        JSON.stringify(excluded_services || []),
        JSON.stringify(itinerary || []),
        max_people || 40,
        featured || false,
        status || 'draft'
      ]
    );

    return result.insertId;
  }

  static async update(id, data) {
    const {
      sub_category_id, airline_id, title, slug, description, short_description,
      main_image, gallery, duration, tour_date, return_date, accommodation_type, route,
      tour_dates, registration_status, important_dates, hotel_info,
      pricing, included_services, excluded_services, itinerary,
      max_people, featured, status
    } = data;

    const [result] = await db.query(
      `UPDATE tours SET
        sub_category_id = ?, airline_id = ?, title = ?, slug = ?, description = ?, short_description = ?,
        main_image = ?, gallery = ?, duration = ?, tour_date = ?, return_date = ?, accommodation_type = ?, route = ?,
        tour_dates = ?, registration_status = ?, important_dates = ?, hotel_info = ?,
        pricing = ?, included_services = ?, excluded_services = ?, itinerary = ?,
        max_people = ?, featured = ?, status = ?
       WHERE id = ?`,
      [
        sub_category_id, airline_id || null, title, slug, description, short_description,
        main_image,
        JSON.stringify(gallery || []),
        duration,
        tour_date,
        return_date,
        accommodation_type,
        route,
        JSON.stringify(tour_dates || []),
        registration_status,
        JSON.stringify(important_dates || []),
        JSON.stringify(hotel_info || []),
        JSON.stringify(pricing || {}),
        JSON.stringify(included_services || []),
        JSON.stringify(excluded_services || []),
        JSON.stringify(itinerary || []),
        max_people,
        featured,
        status,
        id
      ]
    );

    return result.affectedRows > 0;
  }

  static async delete(id) {
    const [result] = await db.query('DELETE FROM tours WHERE id = ?', [id]);
    return result.affectedRows > 0;
  }

  static async getFeatured(limit = 6) {
    const [rows] = await db.query(
      `SELECT t.*, sc.name as sub_category_name, c.name as category_name,
              a.name as airline_name, a.code as airline_code, a.logo_url as airline_logo
       FROM tours t
       LEFT JOIN sub_categories sc ON t.sub_category_id = sc.id
       LEFT JOIN categories c ON sc.category_id = c.id
       LEFT JOIN airlines a ON t.airline_id = a.id
       WHERE t.featured = 1 AND t.status = 'active'
       ORDER BY t.created_at DESC LIMIT ?`,
      [limit]
    );

    return rows.map(tour => this.parseJsonFields(tour));
  }

  static parseJsonFields(tour) {
    if (!tour) return null;

    const jsonFields = [
      'gallery', 'tour_dates', 'important_dates', 'hotel_info',
      'pricing', 'included_services', 'excluded_services', 'itinerary'
    ];

    jsonFields.forEach(field => {
      if (tour[field] && typeof tour[field] === 'string') {
        try {
          tour[field] = JSON.parse(tour[field]);
        } catch (e) {
          tour[field] = field === 'pricing' ? {} : [];
        }
      }
    });

    // Add hotels field for frontend compatibility
    if (tour.hotel_info && Array.isArray(tour.hotel_info)) {
      tour.hotels = tour.hotel_info;
    } else {
      tour.hotels = [];
    }

    return tour;
  }

  static async getNearestUmrahTours() {
    // Get date range: 2 months ago to 2 months later
    const now = new Date();
    const twoMonthsAgo = new Date();
    twoMonthsAgo.setMonth(now.getMonth() - 2);
    const twoMonthsLater = new Date();
    twoMonthsLater.setMonth(now.getMonth() + 2);

    const [tours] = await db.query(
      `SELECT t.*,
              sc.name as sub_category_name,
              sc.slug as sub_category_slug,
              c.name as category_name,
              c.slug as category_slug,
              a.name as airline_name,
              a.code as airline_code,
              a.logo_url as airline_logo
       FROM tours t
       LEFT JOIN sub_categories sc ON t.sub_category_id = sc.id
       LEFT JOIN categories c ON sc.category_id = c.id
       LEFT JOIN airlines a ON t.airline_id = a.id
       WHERE t.status = 'active'
       AND (c.slug LIKE '%umre%' OR sc.slug LIKE '%umre%' OR t.title LIKE '%umre%')
       ORDER BY t.tour_date ASC`
    );

    if (tours.length === 0) {
      return [];
    }

    // Parse JSON fields and extract dates
    const toursWithDates = [];

    tours.forEach(tour => {
      const parsedTour = this.parseJsonFields(tour);

      // Check if tour_dates array has data
      let dates = [];
      if (parsedTour.tour_dates && Array.isArray(parsedTour.tour_dates) && parsedTour.tour_dates.length > 0) {
        // New format: tour_dates array - filter by date range
        dates = parsedTour.tour_dates
          .filter(d => {
            if (!d.departureDate) return false;
            const depDate = new Date(d.departureDate);
            return depDate >= twoMonthsAgo && depDate <= twoMonthsLater;
          })
          .map(d => ({
            departureDate: d.departureDate,
            returnDate: d.returnDate,
            price: d.price,
            oldPrice: d.oldPrice
          }));
      } else if (parsedTour.tour_date) {
        // Old format: single tour_date field
        const tourDate = new Date(parsedTour.tour_date);
        // Include if within 2 months ago to 2 months later
        if (tourDate >= twoMonthsAgo && tourDate <= twoMonthsLater) {
          dates = [{
            departureDate: parsedTour.tour_date,
            returnDate: parsedTour.return_date,
            price: parsedTour.pricing?.double_room || null,
            oldPrice: null
          }];
        }
      }

      if (dates.length > 0) {
        toursWithDates.push({
          ...parsedTour,
          dates: dates
        });
      }
    });

    return toursWithDates;
  }
}

module.exports = Tour;
