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

class Umre {
  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 umre_tours t
      JOIN umre_categories c ON t.category_id = c.id
      LEFT JOIN umre_sub_categories sc ON t.sub_category_id = sc.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 t.category_id = ?';
      params.push(filters.category_id);
    }

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

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

    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 umre_tours t
       JOIN umre_categories c ON t.category_id = c.id
       LEFT JOIN umre_sub_categories sc ON t.sub_category_id = sc.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 umre_tours t
       JOIN umre_categories c ON t.category_id = c.id
       LEFT JOIN umre_sub_categories sc ON t.sub_category_id = sc.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 umre_tours SET views = views + 1 WHERE id = ?', [rows[0].id]);

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

  static async create(data) {
    const {
      category_id, 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 umre_tours (
        category_id, 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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        category_id, sub_category_id || null, 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 {
      category_id, 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 umre_tours SET
        category_id = ?, 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 = ?`,
      [
        category_id, sub_category_id || null, 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 umre_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 umre_tours t
       JOIN umre_categories c ON t.category_id = c.id
       LEFT JOIN umre_sub_categories sc ON t.sub_category_id = sc.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 async getUpcoming(limit = 6) {
    const twoMonthsFromNow = new Date();
    twoMonthsFromNow.setMonth(twoMonthsFromNow.getMonth() + 2);

    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 umre_tours t
       JOIN umre_categories c ON t.category_id = c.id
       LEFT JOIN umre_sub_categories sc ON t.sub_category_id = sc.id
       LEFT JOIN airlines a ON t.airline_id = a.id
       WHERE t.status = 'active'
       AND t.tour_date >= CURDATE()
       AND t.tour_date <= ?
       ORDER BY t.tour_date ASC LIMIT ?`,
      [twoMonthsFromNow, 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)) {
      // Convert image (string) to images (array) for each hotel
      tour.hotels = tour.hotel_info.map(hotel => ({
        ...hotel,
        images: hotel.image ? [hotel.image] : []
      }));
    } else {
      tour.hotels = [];
    }

    return tour;
  }
}

module.exports = Umre;
