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

class SubCategory {
  static async getAll() {
    const [rows] = await db.query(
      `SELECT sc.*, c.name as category_name
       FROM sub_categories sc
       LEFT JOIN categories c ON sc.category_id = c.id
       ORDER BY sc.display_order ASC, sc.created_at DESC`
    );
    return rows;
  }

  static async getByCategory(categoryId) {
    const [rows] = await db.query(
      'SELECT * FROM sub_categories WHERE category_id = ? ORDER BY display_order ASC',
      [categoryId]
    );
    return rows;
  }

  static async getActive() {
    const [rows] = await db.query(
      "SELECT * FROM sub_categories WHERE status = 'active' ORDER BY display_order ASC"
    );
    return rows;
  }

  static async getById(id) {
    const [rows] = await db.query(
      `SELECT sc.*, c.name as category_name, c.slug as category_slug
       FROM sub_categories sc
       LEFT JOIN categories c ON sc.category_id = c.id
       WHERE sc.id = ?`,
      [id]
    );
    return rows[0];
  }

  static async getBySlug(slug) {
    const [rows] = await db.query(
      `SELECT sc.*, c.name as category_name, c.slug as category_slug
       FROM sub_categories sc
       LEFT JOIN categories c ON sc.category_id = c.id
       WHERE sc.slug = ?`,
      [slug]
    );
    return rows[0];
  }

  static async create(data) {
    const { category_id, name, slug, description, image_url, status, display_order } = data;
    const [result] = await db.query(
      `INSERT INTO sub_categories (category_id, name, slug, description, image_url, status, display_order)
       VALUES (?, ?, ?, ?, ?, ?, ?)`,
      [category_id, name, slug, description || null, image_url || null, status || 'active', display_order || 0]
    );
    return result.insertId;
  }

  static async update(id, data) {
    const { category_id, name, slug, description, image_url, status, display_order } = data;
    const [result] = await db.query(
      `UPDATE sub_categories SET category_id = ?, name = ?, slug = ?, description = ?,
       image_url = ?, status = ?, display_order = ? WHERE id = ?`,
      [category_id, name, slug, description, image_url, status, display_order, id]
    );
    return result.affectedRows > 0;
  }

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

  static async getWithTours(subCategoryId) {
    const subCategory = await this.getById(subCategoryId);
    if (!subCategory) return null;

    const [tours] = await db.query(
      "SELECT * FROM tours WHERE sub_category_id = ? AND status = 'active' ORDER BY created_at DESC",
      [subCategoryId]
    );

    return {
      ...subCategory,
      tours
    };
  }
}

module.exports = SubCategory;
