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

class Category {
  static async getAll() {
    const [rows] = await db.query(
      'SELECT * FROM categories ORDER BY display_order ASC, created_at DESC'
    );
    return rows;
  }

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

  static async getById(id) {
    const [rows] = await db.query('SELECT * FROM categories WHERE id = ?', [id]);
    return rows[0];
  }

  static async getBySlug(slug) {
    const [rows] = await db.query('SELECT * FROM categories WHERE slug = ?', [slug]);
    return rows[0];
  }

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

  static async update(id, data) {
    const { name, slug, description, image_url, status, display_order } = data;
    const [result] = await db.query(
      `UPDATE categories SET name = ?, slug = ?, description = ?, image_url = ?,
       status = ?, display_order = ? WHERE 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 categories WHERE id = ?', [id]);
    return result.affectedRows > 0;
  }

  static async getWithSubCategories(categoryId) {
    const category = await this.getById(categoryId);
    if (!category) return null;

    const [subCategories] = await db.query(
      "SELECT * FROM sub_categories WHERE category_id = ? AND status = 'active' ORDER BY display_order ASC",
      [categoryId]
    );

    return {
      ...category,
      subCategories
    };
  }
}

module.exports = Category;
