const express = require('express');
const router = express.Router();

// Importă conexiunea la baza de date
const pool = require('../db'); 

// Importă middleware-ul de autentificare
const authenticateToken = require('../middleware/auth');
function generateEAN13() {
    let barcode = '';
    for (let i = 0; i < 12; i++) {
        barcode += Math.floor(Math.random() * 10);
    }

    // Calculează cifra de control (checksum) pentru EAN-13
    let sum = 0;
    for (let i = 0; i < 12; i++) {
        const digit = parseInt(barcode[i]);
        sum += (i % 2 === 0) ? digit : digit * 3;
    }
    const checksum = (10 - (sum % 10)) % 10;
    return barcode + checksum;
}

// Endpoint pentru a obține produsele - protejează ruta cu middleware-ul de autentificare
router.post('/', async (req, res) => {
  const {
    name,
    barcode,
    price,
    vatRate,
    category,
    unitOfMeasure,
    garantieSgr,
    cod_nc,
    cod_cpv,
    tip_produs,
    id_garantie,
    cod_cantar // ✅ NOU
  } = req.body;

  const vatRateDecimal = parseFloat(vatRate);
  const garantieSgrValue = garantieSgr === 'da' ? 'da' : 'nu';
  const finalBarcode = barcode || generateEAN13();

  const codNcValue = cod_nc || 0;
  const codCpvValue = cod_cpv || 0;
  const idGarantieValue = garantieSgr === 'da' && id_garantie ? parseInt(id_garantie) : null;
  const codCantarValue = cod_cantar || null;

  const sql = `
    INSERT INTO products
    (name, barcode, price, vat_rate, category, unit_of_measure, garantie_sgr, cod_nc, cod_cpv, tip_produs, id_garantie, cod_cantar)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `;

  try {
    await pool.promise().query(sql, [
      name,
      finalBarcode,
      price,
      vatRateDecimal,
      category,
      unitOfMeasure,
      garantieSgrValue,
      codNcValue,
      codCpvValue,
      tip_produs,
      idGarantieValue,
      codCantarValue // ✅ NOU
    ]);

    res.send('Product added');
  } catch (err) {
    console.error('Error inserting product:', err);
    res.status(500).send('Error adding product');
  }
});


router.post('/show_products', async (req, res) => {
    const sql = `
        SELECT p.id, p.name, p.barcode, p.cod_cantar, p.price, p.vat_rate, p.category, p.unit_of_measure, p.garantie_sgr,
               COALESCE(s.cantitate_total, 0) AS stoc_actual
        FROM products p
        LEFT JOIN temp_stoc_produse s ON p.id = s.id_produs
        ORDER BY stoc_actual DESC
    `;

    try {
        const [results] = await pool.promise().query(sql);
        res.json(results);
    } catch (err) {
        console.error('Error retrieving products:', err);
        res.status(500).send('Error fetching products');
    }
});


router.get('/list_products', async (req, res) => {
  let sql = `
    SELECT 
      p.id, 
      p.name, 
      p.barcode, 
      p.price, 
      p.vat_rate, 
      p.category, 
      p.cod_cantar,
      p.unit_of_measure, 
      p.garantie_sgr,
      p.tip_produs,
      COALESCE(s.stoc_actual, 0) AS stoc_actual
    FROM products p
    LEFT JOIN stoc_produse s ON p.id = s.id_produs
    WHERE p.tip_produs IN ('marfa', 'produs_finit')
  `;

  try {
    const [results] = await pool.promise().query(sql);
    res.json(results);
  } catch (err) {
    console.error('Error fetching products:', err);
    res.status(500).json({ message: 'Error fetching products' });
  }
});
router.get('/all_products', async (req, res) => {
  let sql = `
    SELECT 
      p.id, 
      p.name, 
      p.barcode, 
      p.price, 
      p.vat_rate, 
      p.category, 
      p.cod_cantar,
      p.unit_of_measure, 
      p.garantie_sgr,
      p.tip_produs,
      COALESCE(s.stoc_actual, 0) AS stoc_actual
    FROM products p
    LEFT JOIN stoc_produse s ON p.id = s.id_produs
    -- WHERE p.tip_produs IN ('marfa', 'produs_finit')  // Eliminat pentru a returna toate produsele
  `;

  try {
    const [results] = await pool.promise().query(sql);
    res.json(results);
  } catch (err) {
    console.error('Error fetching products:', err);
    res.status(500).json({ message: 'Error fetching products' });
  }
});

router.get('/:barcode', async (req, res) => {
    const barcode = req.params.barcode;
    
    const sql = `
        SELECT 
            p.id, p.name, p.barcode, p.id_garantie, p.tva_int, p.price, p.price_fact, 
            p.unit_of_measure, p.garantie_sgr, p.pret_achizitie, 
            p.cod_nc, p.cod_cpv, p.cod_cantar,
            c.valoare_tva, c.id_cota
        FROM products p
        JOIN cote_tva c ON p.vat_rate = c.id_cota
        WHERE p.barcode = ? OR p.cod_cantar = ?
    `;

    try {
        const [result] = await pool.promise().query(sql, [barcode, barcode]);
        if (result.length === 0) {
            res.status(404).json({ error: 'Product not found' });
        } else {
            res.json(result[0]);
        }
    } catch (err) {
        console.error('Database error:', err);
        res.status(500).json({ error: 'Database error' });
    }
});

router.get('/get_product/:id', async (req, res) => {
    const { id } = req.params;

    const sql = `
        SELECT id, name, barcode, price, vat_rate, category, id_garantie, unit_of_measure, cod_cantar, garantie_sgr, cod_nc, cod_cpv 
        FROM products 
        WHERE id = ?
    `;

    try {
        const [results] = await pool.promise().query(sql, [id]);
        if (results.length > 0) {
            res.json(results[0]);
        } else {
            res.status(404).send('Product not found');
        }
    } catch (err) {
        console.error('Error retrieving product:', err);
        res.status(500).send('Error fetching product');
    }
});

router.post('/get_product', async (req, res) => {
    const { id } = req.body;

    const sql = `
        SELECT id, name, barcode, price, vat_rate, category, id_garantie, unit_of_measure, cod_cantar, garantie_sgr, cod_nc, cod_cpv, tip_produs 
        FROM products 
        WHERE id = ?
    `;

    try {
        const [results] = await pool.promise().query(sql, [id]);
        if (results.length > 0) {
            res.json(results[0]);
        } else {
            res.status(404).send('Product not found');
        }
    } catch (err) {
        console.error('Error retrieving product:', err);
        res.status(500).send('Error fetching product');
    }
});

router.post('/multiple', async (req, res) => {
    const products = req.body;

    if (!Array.isArray(products)) {
        return res.status(400).send('Datele trimise trebuie să fie un array de produse');
    }

    const sql = `INSERT INTO products (name, barcode, price, vat_rate, category, unit_of_measure, garantie_sgr) 
                 VALUES (?, ?, ?, ?, ?, ?, ?)`;

    try {
        const promises = products.map(product => {
            const { name, barcode, price, vatRate, category, unitOfMeasure, garantieSgr } = product;

            // Generează un cod de bare dacă nu este furnizat
            const finalBarcode = barcode || generateEAN13();

            return pool.promise().query(sql, [
                name,
                finalBarcode,
                parseFloat(price),
                parseFloat(vatRate),
                category,
                unitOfMeasure,
                garantieSgr === 'da' ? 'da' : 'nu',
            ]);
        });

        await Promise.all(promises);
        res.send('Produse adăugate cu succes');
    } catch (err) {
        console.error('Eroare la inserarea produselor:', err);
        res.status(500).send('Eroare la baza de date');
    }
});
router.delete('/delete_product/:id', async (req, res) => {
    const { id } = req.params;
    const sql = 'DELETE FROM products WHERE id = ?';
    
    try {
        await pool.promise().query(sql, [id]);
        res.send(`Product with id ${id} deleted`);
    } catch (err) {
        console.error('Error deleting product:', err);
        res.status(500).send('Error deleting product');
    }
});


router.get('/afisare/coduri_cpv',  async (req, res) => {
    const sql = 'SELECT * FROM coduri_cpv';

    try {
        // Execută interogarea SQL
        const [results] = await pool.promise().query(sql);
        res.json(results);
    } catch (err) {
        console.error('Error fetching cod_cpv:', err);
        res.status(500).json({ message: 'Error fetching cod_cpv' });
    }
});
router.get('/coloana/coduri_nc',  async (req, res) => {
    const sql = 'SELECT * FROM coduri_nc';

    try {
        // Execută interogarea SQL
        const [results] = await pool.promise().query(sql);
        res.json(results);
    } catch (err) {
        console.error('Error fetching products:', err);
        res.status(500).json({ message: 'Error fetching products' });
    }
});
// routes/products.js - Endpoint corectat
router.get('/:id/promotions', async (req, res) => {
  try {
    const productId = req.params.id;

    if (!productId || isNaN(productId)) {
      return res.status(400).json({ error: "ID produs invalid" });
    }

    const [promotions] = await pool.promise().query(`
      SELECT 
        id,
        product_id,
        discount_product_id,
        promotion_type,
        required_quantity,
        bonus_quantity,
        start_date,
        end_date
      FROM promotions 
      WHERE 
        product_id = ? 
        AND start_date <= NOW()
        AND end_date >= NOW()
        AND is_active = true
    `, [productId]);

    res.json(promotions);
    
  } catch (err) {
    console.error('Eroare endpoint promotions:', err);
    res.status(500).json({ 
      error: "Eroare server",
      details: err.message,
      stack: process.env.NODE_ENV === 'development' ? err.stack : undefined
    });
  }
});
// routes/products.js
router.post('/:id/promotions', async (req, res) => {
  try {
    const productId = req.params.id;
    const {
      discount_product_id,
      promotion_type,
      required_quantity,
      bonus_quantity,
      start_date,
      end_date,
      is_active
    } = req.body;

    if (!productId || isNaN(productId)) {
      return res.status(400).json({ error: "ID produs invalid" });
    }

    // Validări minime (poți extinde)
    if (!promotion_type || !start_date || !end_date) {
      return res.status(400).json({ error: "Date lipsă pentru promoție" });
    }

    await pool.promise().query(`
      INSERT INTO promotions (
        product_id,
        discount_product_id,
        promotion_type,
        required_quantity,
        bonus_quantity,
        start_date,
        end_date,
        is_active
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `, [
      productId,
      discount_product_id || null,
      promotion_type,
      required_quantity || 0,
      bonus_quantity || 0,
      start_date,
      end_date,
      is_active !== undefined ? is_active : true
    ]);

    res.status(201).json({ message: "Promoție adăugată cu succes" });

  } catch (err) {
    console.error('Eroare la inserare promoție:', err);
    res.status(500).json({
      error: "Eroare server",
      details: err.message,
      stack: process.env.NODE_ENV === 'development' ? err.stack : undefined
    });
  }
});
router.put('/update_product/:id', async (req, res) => {
  const { id } = req.params;
  const {
    name,
    barcode,
    price,
    vatRate,
    category,
    unitOfMeasure,
    garantieSgr,
    tip_produs,
    produs_asociat_id,
    cod_cantar // ✅ nou
  } = req.body;

  const vatRateDecimal = parseFloat(vatRate);
  const garantieSgrValue = garantieSgr === 'da' ? 'da' : 'nu';
  const idGarantie = garantieSgr === 'da' && produs_asociat_id ? parseInt(produs_asociat_id) : null;

  const sql = `
    UPDATE products 
    SET name = ?, 
        barcode = ?, 
        cod_cantar = ?,     -- ✅ adăugat în query
        price = ?, 
        vat_rate = ?, 
        category = ?, 
        unit_of_measure = ?, 
        garantie_sgr = ?,
        tip_produs = ?,
        id_garantie = ?
    WHERE id = ?
  `;

  try {
    const [result] = await pool.promise().query(sql, [
      name,
      barcode,
      cod_cantar || null,  // ✅ înlocuiește valoarea în query
      price,
      vatRateDecimal,
      category,
      unitOfMeasure,
      garantieSgrValue,
      tip_produs,
      idGarantie,
      id
    ]);

    if (result.affectedRows === 0) {
      return res.status(404).send('Produsul nu a fost găsit.');
    }

    res.send(`Produsul cu ID ${id} a fost actualizat cu succes.`);
  } catch (error) {
    console.error('Eroare la actualizarea produsului:', error);
    res.status(500).send('Eroare la actualizarea produsului.');
  }
});



module.exports = router;
