const { getConnection } = require('../config/database');

class Category {
  static async findAll() {
    const connection = getConnection();
    const [rows] = await connection.execute(`
      SELECT c.*, p.name as platform_name, p.slug as platform_slug, p.icon as platform_icon
      FROM categories c
      JOIN platforms p ON c.platform_id = p.id
      WHERE c.is_active = TRUE AND p.is_active = TRUE
      ORDER BY p.sort_order ASC, c.sort_order ASC
    `);
    return rows;
  }

  static async findByPlatform(platformId) {
    const connection = getConnection();
    const [rows] = await connection.execute(`
      SELECT c.*, p.name as platform_name, p.slug as platform_slug
      FROM categories c
      JOIN platforms p ON c.platform_id = p.id
      WHERE c.platform_id = ? AND c.is_active = TRUE AND p.is_active = TRUE
      ORDER BY c.sort_order ASC
    `, [platformId]);
    return rows;
  }

  static async findById(id) {
    const connection = getConnection();
    const [rows] = await connection.execute(`
      SELECT c.*, p.name as platform_name, p.slug as platform_slug
      FROM categories c
      JOIN platforms p ON c.platform_id = p.id
      WHERE c.id = ?
    `, [id]);
    return rows[0];
  }

  static async findBySlug(platformSlug, categorySlug) {
    const connection = getConnection();
    const [rows] = await connection.execute(`
      SELECT c.*, p.name as platform_name, p.slug as platform_slug
      FROM categories c
      JOIN platforms p ON c.platform_id = p.id
      WHERE p.slug = ? AND c.slug = ? AND c.is_active = TRUE AND p.is_active = TRUE
    `, [platformSlug, categorySlug]);
    return rows[0];
  }

  static async create(categoryData) {
    const connection = getConnection();
    const { platform_id, name, slug, description, sort_order } = categoryData;
    
    const [result] = await connection.execute(
      'INSERT INTO categories (platform_id, name, slug, description, sort_order) VALUES (?, ?, ?, ?, ?)',
      [platform_id, name, slug, description, sort_order || 0]
    );
    
    return await this.findById(result.insertId);
  }

  static async update(id, categoryData) {
    const connection = getConnection();
    const { platform_id, name, slug, description, sort_order, is_active } = categoryData;
    
    await connection.execute(
      'UPDATE categories SET platform_id = ?, name = ?, slug = ?, description = ?, sort_order = ?, is_active = ? WHERE id = ?',
      [platform_id, name, slug, description, sort_order, is_active, id]
    );
    
    return await this.findById(id);
  }

  static async delete(id) {
    const connection = getConnection();
    await connection.execute('DELETE FROM categories WHERE id = ?', [id]);
  }
}

module.exports = Category;