const express = require('express');
const axios = require('axios');
const router = express.Router();
const pool = require('../db');
const poolspv = require('../dbspv');
const jwt = require('jsonwebtoken');

const { XMLParser } = require('fast-xml-parser');
const unzipper = require('unzipper');
const xml2js = require('xml2js');
const stream = require('stream');
const { promisify } = require('util');

const pipeline = promisify(stream.pipeline);

router.get('/token-local-direct', (req, res) => {
  const {
    access_token,
    refresh_token,
    expires_in,
    token_type,
    serial
  } = req.query;

  if (!access_token || !serial) {
    return res.status(400).send('Date incomplete');
  }

  pool.getConnection((err, conn) => {
    if (err) {
      console.error('❌ Eroare conexiune la DB:', err.message);
      return res.status(500).send('Eroare conexiune la baza de date');
    }

    conn.query(
      `INSERT INTO tokens (access_token, refresh_token, expires_in, token_type, serial)
       VALUES (?, ?, ?, ?, ?)`,
      [access_token, refresh_token, expires_in, token_type, serial],
      (err) => {
        conn.release(); // Important! Eliberăm conexiunea

        if (err) {
          console.error('❌ Eroare la salvare token:', err.message);
          return res.status(500).send('Eroare la salvarea tokenului');
        }

        // Redirecționează după salvare cu succes
        res.redirect('http://localhost/spvefactura');
      }
    );
  });
});

router.get('/efactura/list', async (req, res) => {
  let conn;

  try {
    conn = await poolspv.getConnection();

    // Obține tokenul
    const [tokenRows] = await conn.query(`
      SELECT access_token 
      FROM tokens 
      ORDER BY created_at DESC 
      LIMIT 1
    `);

    if (!tokenRows.length || !tokenRows[0].access_token) {
      return res.status(401).json({ error: 'Token OAuth lipsă' });
    }

    const token = tokenRows[0].access_token;

    // Obține CUI firma
    const [firmaRows] = await conn.query(`
      SELECT cui 
      FROM firma 
      LIMIT 1
    `);

    if (!firmaRows.length || !firmaRows[0].cui) {
      return res.status(404).json({ error: 'CUI firma lipsă' });
    }

    const cif = firmaRows[0].cui;
    const zile = 60;

    const anafUrl = `https://api.anaf.ro/prod/FCTEL/rest/listaMesajeFactura?zile=${zile}&cif=${cif}&filtru=P`;

    

    const response = await axios.get(anafUrl, {
      headers: {
        'Authorization': `Bearer ${token}`,
      },
      timeout: 10000
    });

    // ✅ Log răspuns
   
    console.dir(response.data, { depth: null });

    const mesaje = response.data.mesaje || [];

    let inserate = 0;
    for (const factura of mesaje) {
      const [exists] = await conn.query(`
        SELECT 1 FROM spv_facturi_primite WHERE id = ?
      `, [factura.id]);

      if (exists.length === 0) {
        await conn.query(`
          INSERT INTO spv_facturi_primite 
          (id, data_creare, cif, id_solicitare, detalii, tip)
          VALUES (?, ?, ?, ?, ?, ?)
        `, [
          factura.id,
          factura.data_creare,
          factura.cif,
          factura.id_solicitare,
          factura.detalii,
          factura.tip
        ]);
        inserate++;
      }
    }

   
    res.json({
      status: 'success',
      facturi_primite: mesaje.length,
      facturi_noi_inserate: inserate
    });

  } catch (err) {
    console.error('❌ Eroare detaliată:', {
      message: err.message,
      response: {
        status: err.response?.status,
        data: err.response?.data,
        headers: err.response?.headers
      },
      stack: err.stack
    });

    res.status(500).json({
      error: 'Eroare ANAF',
      details: err.response?.data || err.message
    });

  } finally {
    if (conn) conn.release();
  }
});

router.get('/efactura/descarcare/:id', async (req, res) => {
  const conn = await poolspv.getConnection();

  // Helper pentru extragere valori sigure
  const extractValue = (field, type = 'string') => {
    const value = typeof field === 'object' ? field?._ : field;
    if (type === 'number') {
      const num = parseFloat(value);
      return isNaN(num) ? 0 : num;
    }
    return value || '';
  };

  try {
    const id = req.params.id;

    // 1. Token
    const [tokenRows] = await conn.query(`
      SELECT access_token 
      FROM tokens 
      ORDER BY created_at DESC 
      LIMIT 1
    `);
    const token = tokenRows[0]?.access_token;
    if (!token) return res.status(401).json({ error: 'Token lipsă' });

    // 2. Cerere ANAF
    const url = `https://api.anaf.ro/prod/FCTEL/rest/descarcare?id=${id}`;
    const zipResponse = await axios.get(url, {
      responseType: 'stream',
      headers: { Authorization: `Bearer ${token}` }
    });

    const contentType = zipResponse.headers['content-type'];
    if (!contentType.includes('application/zip')) {
      return res.status(400).json({ error: 'Răspuns invalid de la ANAF' });
    }

    let facturaXml = '';
    await pipeline(
      zipResponse.data,
      unzipper.Parse(),
      stream.Transform({
        objectMode: true,
        transform(entry, _, cb) {
          const fileName = entry.path;
          if (fileName.endsWith('.xml')) {
            let xmlData = '';
            entry.on('data', chunk => xmlData += chunk);
            entry.on('end', () => {
              if (xmlData.includes('<Invoice')) {
                facturaXml = xmlData;
              }
              cb();
            });
          } else {
            entry.autodrain();
            cb();
          }
        }
      })
    );

    if (!facturaXml) {
      return res.status(400).json({ error: 'Fișier XML lipsă în arhivă' });
    }

    // 3. Parsare XML
    const parser = new xml2js.Parser({
      explicitArray: false,
      ignoreAttrs: false,
      tagNameProcessors: [xml2js.processors.stripPrefix]
    });
    const parsed = await parser.parseStringPromise(facturaXml);
    const inv = parsed.Invoice || Object.values(parsed).find(x => x?.ID && x?.IssueDate);
    if (!inv) return res.status(400).json({ error: 'Structură XML invalidă' });

       // 4. Extragere date
       const taxTotals = inv.TaxTotal?.TaxSubtotal 
       ? (Array.isArray(inv.TaxTotal.TaxSubtotal) ? inv.TaxTotal.TaxSubtotal : [inv.TaxTotal.TaxSubtotal])
       : [];
   
     let valoare_tva_9 = 0, valoare_tva_19 = 0;
     let baza_tva_9 = 0, baza_tva_19 = 0;
   
     for (const tax of taxTotals) {
       const procent = extractValue(tax.TaxCategory?.Percent, 'number');
       const valoareTva = extractValue(tax.TaxAmount, 'number');
       const bazaTva = extractValue(tax.TaxableAmount, 'number');
   
       if (procent === 9) {
         valoare_tva_9 = valoareTva;
         baza_tva_9 = bazaTva;
       } else if (procent === 19) {
         valoare_tva_19 = valoareTva;
         baza_tva_19 = bazaTva;
       }
     }
   
     const factura = {
       numar_factura: extractValue(inv.ID),
       data_factura: extractValue(inv.IssueDate),
       cif_emitent: extractValue(inv.AccountingSupplierParty?.Party?.PartyTaxScheme?.CompanyID),
       nume_emitent: extractValue(inv.AccountingSupplierParty?.Party?.PartyLegalEntity?.RegistrationName),
       rc_emitent: extractValue(inv.AccountingSupplierParty?.Party?.PartyLegalEntity?.CompanyID),
       adress_emitent: extractValue(inv.AccountingSupplierParty?.Party?.PostalAddress?.StreetName),
       oras: extractValue(inv.AccountingSupplierParty?.Party?.PostalAddress?.CityName),
       judet: extractValue(inv.AccountingSupplierParty?.Party?.PostalAddress?.CountrySubentity),
       cif_beneficiar: extractValue(inv.AccountingCustomerParty?.Party?.PartyTaxScheme?.CompanyID),
       nume_beneficiar: extractValue(inv.AccountingCustomerParty?.Party?.PartyName?.Name),
       total: extractValue(inv.LegalMonetaryTotal?.PayableAmount, 'number'),
       total_fara_tva: extractValue(inv.LegalMonetaryTotal?.TaxExclusiveAmount, 'number'),
       valuta: typeof inv.LegalMonetaryTotal?.PayableAmount === 'object'
         ? inv.LegalMonetaryTotal.PayableAmount.$?.currencyID || 'RON'
         : 'RON',
       valoare_tva_9,
       baza_tva_9,
       valoare_tva_19,
       baza_tva_19
     };

    // 5. Verificare duplicat
    const [exists] = await conn.query(`
      SELECT id FROM spv_detalii_factura 
      WHERE numar_factura = ? AND cif_emitent = ?
    `, [factura.numar_factura, factura.cif_emitent]);

    if (exists.length > 0) {
      return res.status(409).json({ error: 'Factura există deja în baza de date' });
    }

    // 5. Inserare în DB
    const [inserted] = await conn.query(`
      INSERT INTO spv_detalii_factura 
        (numar_factura, data_factura, cif_emitent, nume_emitent, cif_beneficiar, nume_beneficiar, total, total_fara_tva, valuta, rc_emitent, adresa_emitent, oras_emitent, jud_emitent, id_incarcare, valoare_tva_9, baza_tva_9, valoare_tva_19, baza_tva_19)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`, [
      factura.numar_factura,
      factura.data_factura,
      factura.cif_emitent,
      factura.nume_emitent,
      factura.cif_beneficiar,
      factura.nume_beneficiar,
      factura.total,
      factura.total_fara_tva,
      factura.valuta,
      factura.rc_emitent,
      factura.adress_emitent,
      factura.oras,
      factura.judet,
      id,
      factura.valoare_tva_9,
      factura.baza_tva_9,
      factura.valoare_tva_19,
      factura.baza_tva_19
    ]);


    const facturaId = inserted.insertId;

    // 7. Linii factură
    const lines = inv.InvoiceLine
      ? (Array.isArray(inv.InvoiceLine) ? inv.InvoiceLine : [inv.InvoiceLine])
      : [];

    for (const linie of lines) {
      if (!linie || !linie.ID) continue;

      const produs = {
        factura_id: facturaId,
        linie_id: extractValue(linie.ID),
        denumire_produs: extractValue(linie.Item?.Name),
        cod_produs: extractValue(linie.Item?.SellersItemIdentification?.ID),
        cantitate: extractValue(linie.InvoicedQuantity, 'number'),
        pret_unitar: extractValue(linie.Price?.PriceAmount, 'number'),
        valoare: extractValue(linie.LineExtensionAmount, 'number'),
        tva: extractValue(linie.Item?.ClassifiedTaxCategory?.Percent, 'number')
      };

      await conn.query(`
        INSERT INTO spv_factura_produse 
          (factura_id, linie_id, denumire_produs, cod_produs, cantitate, pret_unitar, valoare, tva)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)`, [
        produs.factura_id,
        produs.linie_id,
        produs.denumire_produs,
        produs.cod_produs,
        produs.cantitate,
        produs.pret_unitar,
        produs.valoare,
        produs.tva
      ]);
    }

    // 8. Update nume_emitent în spv_facturi_primite
    await conn.query(`
      UPDATE spv_facturi_primite 
      SET nume_emitent = ? 
      WHERE id = ?
    `, [factura.nume_emitent, id]);

    res.json({ status: 'ok', factura });

  } catch (err) {
    console.error('❌ Eroare la descarcare individuală:', err.message);
    res.status(500).json({ error: 'Eroare la procesare', details: err.message });
  } finally {
    if (conn) conn.release();
  }
});

router.get('/efactura/primite/bulk', async (req, res) => {
  const conn = await poolspv.getConnection();

  // Helper pentru extragere valori cu fallback
  const extractValue = (field, type = 'string') => {
    const value = typeof field === 'object' ? field?._ : field;
    if (type === 'number') {
      const num = parseFloat(value);
      return isNaN(num) ? 0 : num;
    }
    return value || '';
  };

  try {
    const [tokenRows] = await conn.query(`
      SELECT access_token 
      FROM tokens 
      ORDER BY created_at DESC 
      LIMIT 1
    `);
    const token = tokenRows[0]?.access_token;
    if (!token) return res.status(401).json({ error: 'Token lipsă' });

    const [facturi] = await conn.query(`
      SELECT id FROM spv_facturi_primite
    `);

    let descarcate = 0, duplicate = 0, esuate = 0;

    for (const f of facturi) {
      const id = f.id;

      try {
        const url = `https://api.anaf.ro/prod/FCTEL/rest/descarcare?id=${id}`;
        const zipResponse = await axios.get(url, {
          responseType: 'stream',
          headers: { Authorization: `Bearer ${token}` }
        });

        const contentType = zipResponse.headers['content-type'];
        if (!contentType.includes('application/zip')) {
          esuate++;
          continue;
        }

        let facturaXml = '';
        await pipeline(
          zipResponse.data,
          unzipper.Parse(),
          stream.Transform({
            objectMode: true,
            transform(entry, _, cb) {
              const fileName = entry.path;
              if (fileName.endsWith('.xml')) {
                let xmlData = '';
                entry.on('data', chunk => xmlData += chunk);
                entry.on('end', () => {
                  if (xmlData.includes('<Invoice')) {
                    facturaXml = xmlData;
                  }
                  cb();
                });
              } else {
                entry.autodrain();
                cb();
              }
            }
          })
        );

        if (!facturaXml) {
          esuate++;
          continue;
        }

        const parser = new xml2js.Parser({
          explicitArray: false,
          ignoreAttrs: false,
          tagNameProcessors: [xml2js.processors.stripPrefix]
        });

        const parsed = await parser.parseStringPromise(facturaXml);
        const inv = parsed.Invoice || Object.values(parsed).find(x => x?.ID && x?.IssueDate);
        if (!inv) {
          esuate++;
          continue;
        }


       // 4. Extragere date
    const taxTotals = inv.TaxTotal?.TaxSubtotal 
    ? (Array.isArray(inv.TaxTotal.TaxSubtotal) ? inv.TaxTotal.TaxSubtotal : [inv.TaxTotal.TaxSubtotal])
    : [];

  let valoare_tva_9 = 0, valoare_tva_19 = 0;
  let baza_tva_9 = 0, baza_tva_19 = 0;

  for (const tax of taxTotals) {
    const procent = extractValue(tax.TaxCategory?.Percent, 'number');
    const valoareTva = extractValue(tax.TaxAmount, 'number');
    const bazaTva = extractValue(tax.TaxableAmount, 'number');

    if (procent === 9) {
      valoare_tva_9 = valoareTva;
      baza_tva_9 = bazaTva;
    } else if (procent === 19) {
      valoare_tva_19 = valoareTva;
      baza_tva_19 = bazaTva;
    }
  }

  const factura = {
    numar_factura: extractValue(inv.ID),
    data_factura: extractValue(inv.IssueDate),
    cif_emitent: extractValue(inv.AccountingSupplierParty?.Party?.PartyTaxScheme?.CompanyID),
    nume_emitent: extractValue(inv.AccountingSupplierParty?.Party?.PartyLegalEntity?.RegistrationName),
    rc_emitent: extractValue(inv.AccountingSupplierParty?.Party?.PartyLegalEntity?.CompanyID),
    adress_emitent: extractValue(inv.AccountingSupplierParty?.Party?.PostalAddress?.StreetName),
    oras: extractValue(inv.AccountingSupplierParty?.Party?.PostalAddress?.CityName),
    judet: extractValue(inv.AccountingSupplierParty?.Party?.PostalAddress?.CountrySubentity),
    cif_beneficiar: extractValue(inv.AccountingCustomerParty?.Party?.PartyTaxScheme?.CompanyID),
    nume_beneficiar: extractValue(inv.AccountingCustomerParty?.Party?.PartyName?.Name),
    total: extractValue(inv.LegalMonetaryTotal?.PayableAmount, 'number'),
    total_fara_tva: extractValue(inv.LegalMonetaryTotal?.TaxExclusiveAmount, 'number'),
    valuta: typeof inv.LegalMonetaryTotal?.PayableAmount === 'object'
      ? inv.LegalMonetaryTotal.PayableAmount.$?.currencyID || 'RON'
      : 'RON',
    valoare_tva_9,
    baza_tva_9,
    valoare_tva_19,
    baza_tva_19
  };

        const [exists] = await conn.query(`
          SELECT id FROM spv_detalii_factura 
          WHERE numar_factura = ? AND cif_emitent = ?
        `, [factura.numar_factura, factura.cif_emitent]);

        if (exists.length > 0) {
          duplicate++;
          continue;
        }

        // 5. Inserare în DB
    const [inserted] = await conn.query(`
      INSERT INTO spv_detalii_factura 
        (numar_factura, data_factura, cif_emitent, nume_emitent, cif_beneficiar, nume_beneficiar, total, total_fara_tva, valuta, rc_emitent, adresa_emitent, oras_emitent, jud_emitent, id_incarcare, valoare_tva_9, baza_tva_9, valoare_tva_19, baza_tva_19)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`, [
      factura.numar_factura,
      factura.data_factura,
      factura.cif_emitent,
      factura.nume_emitent,
      factura.cif_beneficiar,
      factura.nume_beneficiar,
      factura.total,
      factura.total_fara_tva,
      factura.valuta,
      factura.rc_emitent,
      factura.adress_emitent,
      factura.oras,
      factura.judet,
      id,
      factura.valoare_tva_9,
      factura.baza_tva_9,
      factura.valoare_tva_19,
      factura.baza_tva_19
    ]);


        const facturaId = inserted.insertId;

        const lines = inv.InvoiceLine
          ? (Array.isArray(inv.InvoiceLine) ? inv.InvoiceLine : [inv.InvoiceLine])
          : [];

        for (const linie of lines) {
          if (!linie || !linie.ID) continue;

          const produs = {
            factura_id: facturaId,
            linie_id: extractValue(linie.ID),
            denumire_produs: extractValue(linie.Item?.Name),
            cod_produs: extractValue(linie.Item?.SellersItemIdentification?.ID),
            cantitate: extractValue(linie.InvoicedQuantity, 'number'),
            pret_unitar: extractValue(linie.Price?.PriceAmount, 'number'),
            valoare: extractValue(linie.LineExtensionAmount, 'number'),
            tva: extractValue(linie.Item?.ClassifiedTaxCategory?.Percent, 'number')
          };

          await conn.query(`
            INSERT INTO spv_factura_produse 
              (factura_id, linie_id, denumire_produs, cod_produs, cantitate, pret_unitar, valoare, tva)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)`, [
            produs.factura_id,
            produs.linie_id,
            produs.denumire_produs,
            produs.cod_produs,
            produs.cantitate,
            produs.pret_unitar,
            produs.valoare,
            produs.tva
          ]);
        }

        await conn.query(`
          UPDATE spv_facturi_primite 
          SET nume_emitent = ? 
          WHERE id = ?
        `, [factura.nume_emitent, id]);

        descarcate++;

      } catch (err) {
        console.error(`❌ Eroare la factura cu id_solicitare=${id}:`, err.message);
        esuate++;
      }
    }

    res.json({
      status: 'finalizat',
      facturi_total: facturi.length,
      descarcate,
      duplicate,
      esuate
    });

  } catch (err) {
    console.error('❌ Eroare procesare bulk:', err);
    res.status(500).json({ error: 'Eroare la procesare bulk', details: err.message });
  } finally {
    if (conn) conn.release();
  }
});

router.get('/afisare/facturi', async (req, res) => {
  try {
    const [rows] = await pool
      .promise()
      .query(
        `SELECT * FROM spv_detalii_factura 
         WHERE status = 'vizibil' 
         ORDER BY data_factura DESC`
      );

    res.status(200).json(rows);
  } catch (error) {
    console.error('Eroare la preluarea facturilor:', error);
    res.status(500).json({ message: 'Eroare internă a serverului' });
  }
});
router.get('/autentificare/token', async (req, res) => {
  try {
    const [rows] = await pool
      .promise()
      .query('SELECT access_token FROM tokens ORDER BY created_at DESC LIMIT 1');

    if (rows.length === 0 || !rows[0].access_token) {
      return res.status(404).json({ message: 'Certificat inexistent' });
    }

    const token = rows[0].access_token;

    // Verificăm tokenul fără a verifica semnătura (doar pentru a extrage data expirării)
    const decoded = jwt.decode(token);

    if (!decoded || !decoded.exp) {
      return res.status(400).json({ message: 'Token invalid' });
    }

    const expiryDate = new Date(decoded.exp * 1000);
    const now = new Date();

    // Verificăm dacă tokenul a expirat
    if (expiryDate < now) {
      const formattedExpiryDate = expiryDate.toLocaleString('ro-RO', {
        day: '2-digit',
        month: '2-digit',
        year: 'numeric',
        hour: '2-digit',
        minute: '2-digit',
        hour12: false,
      });

      return res.status(401).json({
        message: `Token expirat în data de ${formattedExpiryDate}`,
      });
    }

    // Dacă tokenul este valid
    const formattedExpiryDate = expiryDate.toLocaleString('ro-RO', {
      day: '2-digit',
      month: '2-digit',
      year: 'numeric',
      hour: '2-digit',
      minute: '2-digit',
      hour12: false,
    });

    return res.status(200).json({
      message: `Certificatul este valid, expiră în data de ${formattedExpiryDate}`,
    });

  } catch (error) {
    console.error('Eroare la preluarea token:', error);
    res.status(500).json({ message: 'Eroare internă a serverului' });
  }
});
router.get('/afisare/factura/:id', async (req, res) => {
  const facturaId = req.params.id;

  try {
    // 1. Obține factura după ID
    const [facturaRows] = await pool
      .promise()
      .query('SELECT * FROM spv_detalii_factura WHERE id = ?', [facturaId]);

    if (facturaRows.length === 0) {
      return res.status(404).json({ message: 'Factura nu a fost găsită' });
    }

    const factura = facturaRows[0];

    // 2. Obține produsele aferente facturii
    const [produse] = await pool
      .promise()
      .query('SELECT * FROM spv_factura_produse WHERE factura_id = ?', [facturaId]);

    // 3. Returnează factura + produsele
    res.status(200).json({
      ...factura,
      produse
    });
  } catch (error) {
    console.error('Eroare la preluarea facturii:', error);
    res.status(500).json({ message: 'Eroare internă a serverului' });
  }
});
router.get('/afisare/factura-trimisa/:id', async (req, res) => {
  const facturaId = req.params.id;

  try {
    // 1. Obține factura după ID
    const [facturaRows] = await pool
      .promise()
      .query('SELECT * FROM spv_detalii_factura_trimisa WHERE id = ?', [facturaId]);

    if (facturaRows.length === 0) {
      return res.status(404).json({ message: 'Factura nu a fost găsită' });
    }

    const factura = facturaRows[0];

    // 2. Obține produsele aferente facturii
    const [produse] = await pool
      .promise()
      .query('SELECT * FROM spv_factura_produse_trimisa WHERE factura_id = ?', [facturaId]);

    // 3. Returnează factura + produsele
    res.status(200).json({
      ...factura,
      produse
    });
  } catch (error) {
    console.error('Eroare la preluarea facturii:', error);
    res.status(500).json({ message: 'Eroare internă a serverului' });
  }
});
function extrageIdIncarcare(detalii) {
  const match = detalii.match(/id_incarcare=(\d+)/);
  return match ? match[1] : null;
}
router.get('/efactura/list_trimise', async (req, res) => {
  let conn;

  try {
    conn = await poolspv.getConnection();

    // Obține tokenul
    const [tokenRows] = await conn.query(`
      SELECT access_token 
      FROM tokens 
      ORDER BY created_at DESC 
      LIMIT 1
    `);

    if (!tokenRows.length || !tokenRows[0].access_token) {
      return res.status(401).json({ error: 'Token OAuth lipsă' });
    }

    const token = tokenRows[0].access_token;

    // Obține CUI firma
    const [firmaRows] = await conn.query(`
      SELECT cui 
      FROM firma 
      LIMIT 1
    `);

    if (!firmaRows.length || !firmaRows[0].cui) {
      return res.status(404).json({ error: 'CUI firma lipsă' });
    }

    const cif = firmaRows[0].cui;
    const zile = 60;

    const anafUrl = `https://api.anaf.ro/prod/FCTEL/rest/listaMesajeFactura?zile=${zile}&cif=${cif}&filtru=T`;

   

    const response = await axios.get(anafUrl, {
      headers: {
        'Authorization': `Bearer ${token}`,
      },
      timeout: 10000
    });

    // ✅ Log răspuns
  
    console.dir(response.data, { depth: null });

    const mesaje = response.data.mesaje || [];

    let inserate = 0;
    for (const factura of mesaje) {
      const [exists] = await conn.query(`
        SELECT 1 FROM spv_facturi_trimise WHERE id = ?
      `, [factura.id]);
    
      if (exists.length === 0) {
        const idIncarcare = extrageIdIncarcare(factura.detalii || '');
    
        await conn.query(`
          INSERT INTO spv_facturi_trimise 
          (id, data_creare, cif, id_solicitare, detalii, tip, id_incarcare)
          VALUES (?, ?, ?, ?, ?, ?, ?)
        `, [
          factura.id,
          factura.data_creare,
          factura.cif,
          factura.id_solicitare,
          factura.detalii,
          factura.tip,
          idIncarcare
        ]);
        inserate++;
      }
    }
    

   
    res.json({
      status: 'success',
      facturi_primite: mesaje.length,
      facturi_noi_inserate: inserate
    });

  } catch (err) {
    console.error('❌ Eroare detaliată:', {
      message: err.message,
      response: {
        status: err.response?.status,
        data: err.response?.data,
        headers: err.response?.headers
      },
      stack: err.stack
    });

    res.status(500).json({
      error: 'Eroare ANAF',
      details: err.response?.data || err.message
    });

  } finally {
    if (conn) conn.release();
  }
});
router.get('/afisare/trimise', async (req, res) => {
  try {
    const [rows] = await pool
      .promise()
      .query(`
        SELECT 
          dft.*, 
          COALESCE(fu.stare, 'inexistent') AS stare_factura
        FROM spv_detalii_factura_trimisa dft
        LEFT JOIN spv_facturi_upload fu
          ON dft.id_incarcare = fu.id_incarcare
        WHERE dft.status = 'vizibil'
        ORDER BY dft.data_factura DESC
      `);

    res.status(200).json(rows);
  } catch (error) {
    console.error('Eroare la preluarea facturilor:', error);
    res.status(500).json({ message: 'Eroare internă a serverului' });
  }
});

router.get('/efactura/trimise/bulk-trimise', async (req, res) => {
  const conn = await poolspv.getConnection();

  // Helper pentru extragere valori cu fallback
  const extractValue = (field, type = 'string') => {
    const value = typeof field === 'object' ? field?._ : field;
    if (type === 'number') {
      const num = parseFloat(value);
      return isNaN(num) ? 0 : num;
    }
    return value || '';
  };

  try {
    const [tokenRows] = await conn.query(`
      SELECT access_token 
      FROM tokens 
      ORDER BY created_at DESC 
      LIMIT 1
    `);
    const token = tokenRows[0]?.access_token;
    if (!token) return res.status(401).json({ error: 'Token lipsă' });

    const [facturi] = await conn.query(`
      SELECT id, id_incarcare FROM spv_facturi_trimise
    `);

    let descarcate = 0, duplicate = 0, esuate = 0;

    for (const f of facturi) {
      const id = f.id;
      const id_incarcare = f.id_incarcare;

      try {
        const url = `https://api.anaf.ro/prod/FCTEL/rest/descarcare?id=${id}`;
        const zipResponse = await axios.get(url, {
          responseType: 'stream',
          headers: { Authorization: `Bearer ${token}` }
        });

        const contentType = zipResponse.headers['content-type'];
        if (!contentType.includes('application/zip')) {
          esuate++;
          continue;
        }

        let facturaXml = '';
        await pipeline(
          zipResponse.data,
          unzipper.Parse(),
          stream.Transform({
            objectMode: true,
            transform(entry, _, cb) {
              const fileName = entry.path;
              if (fileName.endsWith('.xml')) {
                let xmlData = '';
                entry.on('data', chunk => xmlData += chunk);
                entry.on('end', () => {
                  if (xmlData.includes('<Invoice')) {
                    facturaXml = xmlData;
                  }
                  cb();
                });
              } else {
                entry.autodrain();
                cb();
              }
            }
          })
        );

        if (!facturaXml) {
          esuate++;
          continue;
        }

        const parser = new xml2js.Parser({
          explicitArray: false,
          ignoreAttrs: false,
          tagNameProcessors: [xml2js.processors.stripPrefix]
        });

        const parsed = await parser.parseStringPromise(facturaXml);
        const inv = parsed.Invoice || Object.values(parsed).find(x => x?.ID && x?.IssueDate);
        if (!inv) {
          esuate++;
          continue;
        }

        const factura = {
          numar_factura: extractValue(inv.ID),
          data_factura: extractValue(inv.IssueDate),
          cif_emitent: extractValue(inv.AccountingSupplierParty?.Party?.PartyIdentification?.ID),
          nume_emitent: extractValue(inv.AccountingSupplierParty?.Party?.PartyLegalEntity?.RegistrationName),
          rc_emitent: extractValue(inv.AccountingSupplierParty?.Party?.PartyLegalEntity?.CompanyID),
          adress_emitent: extractValue(inv.AccountingSupplierParty?.Party?.PostalAddress?.StreetName),
          oras: extractValue(inv.AccountingSupplierParty?.Party?.PostalAddress?.CityName),
          judet: extractValue(inv.AccountingSupplierParty?.Party?.PostalAddress?.CountrySubentity),
          cif_beneficiar:extractValue(inv.AccountingCustomerParty?.Party?.PartyIdentification?.ID),
          nume_beneficiar: extractValue(inv.AccountingCustomerParty?.Party?.PartyLegalEntity?.RegistrationName),
          rc_beneficiar: extractValue(inv.AccountingCustomerParty?.Party?.PartyLegalEntity?.CompanyID),
          adress_beneficiar: extractValue(inv.AccountingCustomerParty?.Party?.PostalAddress?.StreetName),
          total: extractValue(inv.LegalMonetaryTotal?.PayableAmount, 'number'),
          total_fara_tva: extractValue(inv.TaxSubtotal?.TaxableAmount, 'number'),
          val_tva: extractValue(inv.TaxSubtotal?.TaxAmount, 'number'),
          valuta: typeof inv.LegalMonetaryTotal?.PayableAmount === 'object'
            ? inv.LegalMonetaryTotal.PayableAmount.$?.currencyID || 'RON'
            : 'RON'
        };
        const [exists] = await conn.query(`
          SELECT id FROM spv_detalii_factura_trimisa 
          WHERE numar_factura = ? AND cif_emitent = ?
        `, [factura.numar_factura, factura.cif_emitent]);

        if (exists.length > 0) {
          duplicate++;
          continue;
        }

        const [inserted] = await conn.query(`
          INSERT INTO spv_detalii_factura_trimisa 
          (numar_factura, data_factura, cif_emitent, nume_emitent, cif_beneficiar, nume_beneficiar, total_fara_tva, total, valuta, rc_emitent, adresa_emitent, oras_emitent, jud_emitent, rc_beneficiar, adresa_beneficiar, id_incarcare, id_descarcare)
          VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        `, [
          factura.numar_factura,
          factura.data_factura,
          factura.cif_emitent,
          factura.nume_emitent,
          factura.cif_beneficiar,
          factura.nume_beneficiar,
          factura.total_fara_tva,
          factura.total,
          factura.valuta,
          factura.rc_emitent,
          factura.adress_emitent,
          factura.oras,
          factura.judet,
          factura.rc_beneficiar,
          factura.adress_beneficiar,
          id_incarcare,
          id,
          
        ]);

        const facturaId = inserted.insertId;

        const lines = inv.InvoiceLine
          ? (Array.isArray(inv.InvoiceLine) ? inv.InvoiceLine : [inv.InvoiceLine])
          : [];

        for (const linie of lines) {
          if (!linie || !linie.ID) continue;

          const produs = {
            factura_id: facturaId,
            linie_id: extractValue(linie.ID),
            denumire_produs: extractValue(linie.Item?.Name),
            cod_produs: extractValue(linie.Item?.SellersItemIdentification?.ID),
            cantitate: extractValue(linie.InvoicedQuantity, 'number'),
            pret_unitar: extractValue(linie.Price?.PriceAmount, 'number'),
            valoare: extractValue(linie.LineExtensionAmount, 'number'),
            tva: extractValue(linie.Item?.ClassifiedTaxCategory?.Percent, 'number')
          };

          await conn.query(`
            INSERT INTO spv_factura_produse_trimisa 
              (factura_id, linie_id, denumire_produs, cod_produs, cantitate, pret_unitar, valoare, tva)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)`, [
            produs.factura_id,
            produs.linie_id,
            produs.denumire_produs,
            produs.cod_produs,
            produs.cantitate,
            produs.pret_unitar,
            produs.valoare,
            produs.tva
          ]);
        }

        await conn.query(`
          UPDATE spv_facturi_trimise
          SET nume_emitent = ? 
          WHERE id = ?
        `, [factura.nume_beneficiar, id]);

        descarcate++;

      } catch (err) {
        console.error(`❌ Eroare la factura cu id_solicitare=${id}:`, err.message);
        esuate++;
      }
    }

    res.json({
      status: 'finalizat',
      facturi_total: facturi.length,
      descarcate,
      duplicate,
      esuate
    });

  } catch (err) {
    console.error('❌ Eroare procesare bulk:', err);
    res.status(500).json({ error: 'Eroare la procesare bulk', details: err.message });
  } finally {
    if (conn) conn.release();
  }
});
router.put('/update-status', async (req, res) => {
  const { facturaId, newStatus } = req.body;

  // Validăm statusul (ar trebui să fie 'vizibil' sau 'invizibil')
  if (newStatus !== 'vizibil' && newStatus !== 'invizibil') {
    return res.status(400).json({ message: 'Status invalid. Trebuie să fie "vizibil" sau "invizibil".' });
  }

  try {
    const [result] = await pool
      .promise()
      .query(
        'UPDATE spv_detalii_factura SET status = ? WHERE id = ?',
        [newStatus, facturaId]
      );

    if (result.affectedRows === 0) {
      return res.status(404).json({ message: 'Factura nu a fost găsită.' });
    }

    res.status(200).json({ message: 'Status actualizat cu succes.' });
  } catch (error) {
    console.error('Eroare la actualizarea statusului:', error);
    res.status(500).json({ message: 'Eroare internă a serverului' });
  }
});


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

  try {
    // Căutăm factura după număr intern
    const [[factura]] = await pool.promise().query(
      `SELECT * FROM facturi WHERE numar_int = ?`,
      [id]
    );

    if (!factura) {
      return res.status(404).json({ message: 'Factura nu a fost găsită.' });
    }

    const idFactura = factura.id_factura;

    // Produsele din factură + coduri din products
    const [produse] = await pool.promise().query(
      `SELECT fp.*, p.cod_nc, p.cod_cpv
       FROM facturi_produse fp
       JOIN products p ON fp.id_produs = p.id
       WHERE fp.id_factura = ?`,
      [idFactura]
    );

    // Firma emitentă
    const [[firma]] = await pool.promise().query(
      `SELECT * FROM firma WHERE id_firma = ?`,
      [factura.id_firma]
    );

    // Furnizorul (clientul)
    const [[client]] = await pool.promise().query(
      `SELECT * FROM furnizori WHERE id_furnizor = ?`,
      [factura.id_furnizor]
    );

    // Trimitere răspuns JSON complet
    res.json({
      factura,
      firma: {
        denumire: firma.denumire,
        cui: firma.cui,
        cod_inmatriculare: firma.cod_inmatriculare,
        judet: firma.judet,
        localitate: firma.localitate,
        adresa: firma.adresa,
        tel: firma.tel
      },
      client: {
        denumire: client.denumire,
        cui: client.cui,
        cod_inmatriculare: client.cod_inmatriculare,
        judet: client.judet,
        localitate: client.localitate,
        adresa: client.adresa,
        tel: client.tel
      },
      produse
    });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'Eroare la preluarea datelor pentru XML.' });
  }
});
router.post('/anaf/uploadFactura', async (req, res) => {
  const { xml, standard, cif, numarint, waitForStatus } = req.body;
  let conn;

  try {
    conn = await poolspv.getConnection();
    const cleanedNumarint = numarint.trim();

    // Verificare dacă factura e deja trimisă
    const [existingRows] = await conn.query(
      'SELECT * FROM spv_facturi_upload WHERE numar_factura = ? LIMIT 1',
      [cleanedNumarint]
    );

    if (existingRows.length > 0) {
      return res.status(400).json({
        success: false,
        message: 'Factura a fost deja transmisă în SPV.',
        alreadyUploaded: true,
      });
    }

    // Obține tokenul OAuth
    const [tokenRows] = await conn.query(
      'SELECT access_token FROM tokens ORDER BY created_at DESC LIMIT 1'
    );
    const token = tokenRows[0]?.access_token;
    if (!token) {
      return res.status(401).json({ success: false, message: 'Token OAuth lipsă' });
    }

    // Trimite XML către ANAF
    const uploadUrl = `https://api.anaf.ro/prod/FCTEL/rest/upload?standard=${standard}&cif=${cif}`;
    const uploadResp = await axios.post(uploadUrl, xml, {
      headers: {
        'Authorization': `Bearer ${token}`,
        'Content-Type': 'application/xml'
      },
      timeout: 60000
    });

    // Extragere sigură a idIncarcare (index_incarcare) din răspuns XML
    let idIncarcare = null;
    if (typeof uploadResp.data === 'string') {
      const match = uploadResp.data.match(/index_incarcare="(\d+)"/);
      if (match) {
        idIncarcare = match[1];
      } else {
        console.warn('⚠️ Răspuns XML ANAF fără index_incarcare:', uploadResp.data);
      }
    } else {
      idIncarcare = uploadResp.data?.id || null;
    }

    // Extrage numărul facturii din XML (opțional)
    const numarFacturaMatch = xml.match(/<cbc:ID>(.*?)<\/cbc:ID>/);
    const numarFactura = numarFacturaMatch ? numarFacturaMatch[1] : null;

    // Salvează în DB chiar și fără idIncarcare
    await conn.query(
      'INSERT INTO spv_facturi_upload (id_incarcare, numar_factura, raspuns_raw) VALUES (?, ?, ?)',
      [idIncarcare, cleanedNumarint, typeof uploadResp.data === 'string' ? uploadResp.data : JSON.stringify(uploadResp.data)]
    );

    // Dacă lipsește idIncarcare
    if (!idIncarcare) {
      return res.status(200).json({
        success: false,
        message: 'Factura a fost trimisă, dar răspunsul ANAF nu conține index_incarcare.',
        data: uploadResp.data
      });
    }

    // Așteptare status dacă waitForStatus=true
    if (waitForStatus === true || waitForStatus === 'true') {
      try {
        const statusData = await waitForAnafProcessing(idIncarcare, token);
        return res.json({
          success: true,
          data: {
            id: idIncarcare,
            numar_factura: numarFactura,
            status: statusData
          }
        });
      } catch (statusErr) {
        return res.status(202).json({
          success: true,
          data: {
            id: idIncarcare,
            numar_factura: numarFactura,
            status: null
          },
          message: 'Factura a fost trimisă, dar statusul nu a fost obținut în timp util.'
        });
      }
    }

    // Returnare succes simplu
    return res.json({
      success: true,
      data: {
        id: idIncarcare,
        numar_factura: numarFactura
      }
    });

  } catch (err) {
    console.error('Eroare generală:', err);
    res.status(500).json({
      success: false,
      message: err.message,
      details: err.response?.data || null
    });
  } finally {
    if (conn) conn.release();
  }
});


// Polling pentru statusul ANAF
async function waitForAnafProcessing(idIncarcare, token) {
  const timeoutMs = 60000;
  const intervalMs = 5000;
  const start = Date.now();

  const statusUrl = `https://api.anaf.ro/prod/FCTEL/rest/status?id=${idIncarcare}`;

  while (Date.now() - start < timeoutMs) {
    const statusResp = await axios.get(statusUrl, {
      headers: { 'Authorization': `Bearer ${token}` }
    });

    const status = statusResp.data?.status;
    if (status === 'FINALIZAT' || status === 'EROARE') {
      return statusResp.data;
    }

    await new Promise(resolve => setTimeout(resolve, intervalMs));
  }

  throw new Error('Timeout așteptând procesarea ANAF');
}
router.get('/anaf/verifica/:id', async (req, res) => {
  const { id } = req.params;
  let conn;

  try {
    conn = await poolspv.getConnection();

    // 🔍 Verifică existența în baza de date
    const [rows] = await conn.query(`
      SELECT stare, detalii
      FROM spv_facturi_upload
      WHERE id_incarcare = ?
      LIMIT 1
    `, [id]);

    const esteFinala = rows.length && ['ok', 'nok'].includes(rows[0].stare);

    if (esteFinala) {
      let parsedDetalii;

      try {
        const detaliiRaw = rows[0].detalii?.trim();
        if (detaliiRaw?.startsWith('<?xml')) {
          const parser = new XMLParser({ ignoreAttributes: false });
          parsedDetalii = parser.parse(detaliiRaw);
        } else {
          parsedDetalii = JSON.parse(detaliiRaw);
        }
      } catch (err) {
        console.warn('⚠️ Eroare la parsarea detaliilor existente:', err.message);
        parsedDetalii = rows[0].detalii;
      }

      return res.json({
        success: true,
        id_incarcare: id,
        stare: rows[0].stare,
        detalii: parsedDetalii
      });
    }

    // 🛰 Interogare către ANAF
    const [tokenRows] = await conn.query(`
      SELECT access_token 
      FROM tokens 
      ORDER BY created_at DESC 
      LIMIT 1
    `);

    const token = tokenRows[0]?.access_token;
    if (!token) {
      return res.status(401).json({ success: false, message: 'Token OAuth lipsă' });
    }

    const url = `https://api.anaf.ro/prod/FCTEL/rest/stareMesaj?id_incarcare=${id}`;
    const response = await axios.get(url, {
      headers: { Authorization: `Bearer ${token}` }
    });

    const responseBody = response.data;

    let stareNoua = 'necunoscuta';
    let rawDetalii = '';

    // 📥 Detectare și extragere stare din răspuns XML
    if (typeof responseBody === 'string' && responseBody.trim().startsWith('<?xml')) {
      const parser = new XMLParser({ ignoreAttributes: false });
      const parsed = parser.parse(responseBody);
      stareNoua = parsed?.['header']?.['@_stare'] || 'necunoscuta';
      rawDetalii = responseBody;
    } else if (typeof responseBody === 'object') {
      stareNoua = responseBody.stare || 'necunoscuta';
      rawDetalii = JSON.stringify(responseBody);
    }

    // 📝 Update în baza de date
    if (rows.length) {
      await conn.query(`
        UPDATE spv_facturi_upload
        SET stare = ?, detalii = ?
        WHERE id_incarcare = ?
      `, [stareNoua, rawDetalii, id]);
    } else {
      await conn.query(`
        INSERT INTO spv_facturi_upload (id_incarcare, stare, detalii)
        VALUES (?, ?, ?)
      `, [id, stareNoua, rawDetalii]);
    }

    return res.json({
      success: true,
      id_incarcare: id,
      stare: stareNoua,
      detalii: responseBody
    });

  } catch (err) {
    console.error('❌ Eroare verificare stare:', err.message);
    res.status(500).json({
      success: false,
      message: err.message,
      details: err.response?.data || null
    });
  } finally {
    if (conn) conn.release();
  }
});

  module.exports = router;