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

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

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

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

  static async create(productData) {
    const connection = getConnection();
    const { 
      category_id, name, description, quantity, 
      original_price, sale_price, discount_percentage, sort_order 
    } = productData;
    
    const [result] = await connection.execute(
      `INSERT INTO products (category_id, name, description, quantity, original_price, sale_price, discount_percentage, sort_order) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
      [category_id, name, description, quantity, original_price, sale_price, discount_percentage || 0, sort_order || 0]
    );
    
    return await this.findById(result.insertId);
  }

  static async update(id, productData) {
    const connection = getConnection();
    const { 
      category_id, name, description, quantity, 
      original_price, sale_price, discount_percentage, sort_order, is_active 
    } = productData;
    
    await connection.execute(
      `UPDATE products SET category_id = ?, name = ?, description = ?, quantity = ?, 
       original_price = ?, sale_price = ?, discount_percentage = ?, sort_order = ?, is_active = ? 
       WHERE id = ?`,
      [category_id, name, description, quantity, original_price, sale_price, discount_percentage, sort_order, is_active, id]
    );
    
    return await this.findById(id);
  }

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

module.exports = Product;