Vérifier l’intégrité d’un fichier FAB-DIS : doublons, GTIN manquants, incohérences de prix

function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet();
  const usedRange = sheet.getUsedRange();
  
  if (!usedRange) {
    console.log("❌ Erreur : la feuille est vide");
    return;
  }
  
  // Récupère les en-têtes
  const headerRange = sheet.getRange("1:1");
  const headerValues = headerRange.getValues();
  
  if (!headerValues || headerValues.length === 0 || !headerValues[0]) {
    console.log("❌ Erreur : pas d'en-têtes trouvés");
    return;
  }
  
  const headers = headerValues[0];
  
  // Identifie les colonnes importantes
  const colIndex: {[key: string]: number} = {};
  const colNames = ["REFCIALE", "GTIN", "EAN", "LIBELLE40", "LIBELLE80", "LIBELLE240", 
                    "PUTTC", "TARIF", "TVA", "FAM1", "QMV", "STOCK"];
  
  headers.forEach((header, index) => {
    const headerStr = String(header).toUpperCase().trim();
    colNames.forEach(colName => {
      if (headerStr === colName || headerStr.includes(colName)) {
        colIndex[colName] = index;
      }
    });
  });
  
  // Récupère toutes les données
  const dataRange = sheet.getRangeByIndexes(
    1, 
    0, 
    usedRange.getRowCount() - 1, 
    usedRange.getColumnCount()
  );
  const values = dataRange.getValues();
  
  // Variables de comptage
  let totalRows = values.length;
  let errors: {[key: string]: string[]} = {
    "Doublons REFCIALE": [],
    "Doublons GTIN": [],
    "GTIN manquants": [],
    "GTIN invalides": [],
    "Prix incohérents": [],
    "Prix à zéro": [],
    "TVA invalide": [],
    "Libellé manquant": [],
    "Libellé trop long": [],
    "Quantité négative": [],
    "Catégorie manquante": [],
    "Unité manquante": [],
    "Ligne vide": []
  };
  
  // Structures pour détecter les doublons
  const seenREFCIALE: {[key: string]: number[]} = {};
  const seenGTIN: {[key: string]: number[]} = {};
  
  // Liste des taux de TVA valides en France
  const validTVA = [0, 2.1, 5.5, 10, 20];
  
  // Analyse ligne par ligne
  values.forEach((row: any[], rowIndex: number) => {
    const lineNumber = rowIndex + 2; // +2 car index 0 = ligne 2 (après en-tête)
    
    // Vérifie si la ligne est complètement vide
    const isEmpty = row.every(cell => !cell || String(cell).trim() === "");
    if (isEmpty) {
      errors["Ligne vide"].push(`Ligne ${lineNumber}`);
      return;
    }
    
    // 1. Vérification REFCIALE
    if (colIndex["REFCIALE"] !== undefined) {
      const REFCIALE = String(row[colIndex["REFCIALE"]] || "").trim();
      if (REFCIALE) {
        if (!seenREFCIALE[REFCIALE]) {
          seenREFCIALE[REFCIALE] = [];
        }
        seenREFCIALE[REFCIALE].push(lineNumber);
      }
    }
    
    // 2. Vérification GTIN/EAN
    const gtinCol = colIndex["GTIN"] !== undefined ? colIndex["GTIN"] : colIndex["EAN"];
    if (gtinCol !== undefined) {
      const gtin = String(row[gtinCol] || "").trim();
      
      if (!gtin) {
        errors["GTIN manquants"].push(`Ligne ${lineNumber}`);
      } else {
        // Vérifie le format (doit être numérique, 8/12/13/14 chiffres)
        if (!/^\d{8}$|^\d{12}$|^\d{13}$|^\d{14}$/.test(gtin)) {
          errors["GTIN invalides"].push(`Ligne ${lineNumber} : "${gtin}"`);
        }
        
        // Détection doublons GTIN
        if (!seenGTIN[gtin]) {
          seenGTIN[gtin] = [];
        }
        seenGTIN[gtin].push(lineNumber);
      }
    }
    
// 3. Vérification prix (PUHT = TARIF, calcul TTC via TVA)
if (colIndex["TARIF"] !== undefined && colIndex["TVA"] !== undefined) {
  const puht = Number(row[colIndex["TARIF"]] || 0);
  const tva = Number(row[colIndex["TVA"]] || 0);

  // Prix TTC calculé
  const puttc = puht * (1 + tva / 100);

  // Prix à zéro ou négatif
  if (puht <= 0) {
    errors["Prix à zéro"].push(`Ligne ${lineNumber} : PUHT = ${puht}`);
  }

  // Prix incohérent : TVA invalide ou calcul aberrant
  if (tva < 0 || tva > 20) {
    errors["TVA invalide"].push(`Ligne ${lineNumber} : TVA = ${tva}`);
  }

  // Vérification prix public si présent
  if (colIndex["PUTTC"] !== undefined) {
    const prixPublic = Number(row[colIndex["PUTTC"]] || 0);

    if (prixPublic < puttc) {
      errors["Prix incohérents"].push(
        `Ligne ${lineNumber} : Prix public ${prixPublic} < Prix TTC calculé ${puttc.toFixed(2)}`
      );
    }
  }
}

    
    // 4. Vérification TVA
    if (colIndex["TVA"] !== undefined) {
      const tva = Number(row[colIndex["TVA"]] || 0);
      if (!validTVA.includes(tva)) {
        errors["TVA invalide"].push(`Ligne ${lineNumber} : ${tva}%`);
      }
    }
    
    // 5. Vérification libellés
    const libelle40 = String(row[colIndex["LIBELLE40"]] || "").trim();
    const libelle80 = String(row[colIndex["LIBELLE80"]] || "").trim();
    
    if (!libelle40 && !libelle80) {
      errors["Libellé manquant"].push(`Ligne ${lineNumber}`);
    }
    
    if (libelle40.length > 40) {
      errors["Libellé trop long"].push(`Ligne ${lineNumber} : LIBELLE40 = ${libelle40.length} car.`);
    }
    
    if (libelle80 && libelle80.length > 80) {
      errors["Libellé trop long"].push(`Ligne ${lineNumber} : LIBELLE80 = ${libelle80.length} car.`);
    }
    
    // 6. Vérification stock/quantité
    if (colIndex["STOCK"] !== undefined) {
      const stock = Number(row[colIndex["STOCK"]] || 0);
      if (stock < 0) {
        errors["Quantité négative"].push(`Ligne ${lineNumber} : Stock = ${stock}`);
      }
    }
    
    // 7. Vérification catégorie
    if (colIndex["FAM1"] !== undefined) {
      const famille = String(row[colIndex["FAM1"]] || "").trim();
      if (!famille) {
        errors["Catégorie manquante"].push(`Ligne ${lineNumber}`);
      }
    }
    
    // 8. Vérification unité
    if (colIndex["QMV"] !== undefined) {
      const QMV = String(row[colIndex["QMV"]] || "").trim();
      if (!QMV) {
        errors["Unité manquante"].push(`Ligne ${lineNumber}`);
      }
    }
  });
  
  // Détection des doublons REFCIALE
  Object.keys(seenREFCIALE).forEach(code => {
    if (seenREFCIALE[code].length > 1) {
      errors["Doublons REFCIALE"].push(`"${code}" : lignes ${seenREFCIALE[code].join(", ")}`);
    }
  });
  
  // Détection des doublons GTIN
  Object.keys(seenGTIN).forEach(gtin => {
    if (seenGTIN[gtin].length > 1) {
      errors["Doublons GTIN"].push(`"${gtin}" : lignes ${seenGTIN[gtin].join(", ")}`);
    }
  });
  
  // Calcul du score de qualité
  let totalErrors = 0;
  Object.values(errors).forEach(errorList => {
    totalErrors += errorList.length;
  });
  
  const qualityScore = Math.max(0, Math.round((1 - totalErrors / totalRows) * 100));
  
  // Génération du rapport
  console.log("═══════════════════════════════════════════════════════");
  console.log("🛡️  RAPPORT DE VALIDATION D'INTÉGRITÉ FAB-DIS");
  console.log("═══════════════════════════════════════════════════════");
  console.log("");
  console.log(`📊 Produits analysés : ${totalRows}`);
  console.log(`📈 Score de qualité : ${qualityScore}%`);
  console.log(`⚠️  Anomalies détectées : ${totalErrors}`);
  console.log("");
  
  if (totalErrors === 0) {
    console.log("✅ EXCELLENT ! Aucune anomalie détectée.");
    console.log("✅ Le fichier est prêt à être importé.");
  } else {
    console.log("───────────────────────────────────────────────────────");
    console.log("📋 DÉTAIL DES ANOMALIES PAR CATÉGORIE");
    console.log("───────────────────────────────────────────────────────");
    
    Object.keys(errors).forEach(category => {
      if (errors[category].length > 0) {
        console.log("");
        console.log(`❌ ${category} (${errors[category].length})`);
        errors[category].slice(0, 10).forEach(error => {
          console.log(`   → ${error}`);
        });
        if (errors[category].length > 10) {
          console.log(`   ... et ${errors[category].length - 10} autres`);
        }
      }
    });
    
    console.log("");
    console.log("───────────────────────────────────────────────────────");
    console.log("⚠️  ATTENTION : Corrigez ces anomalies avant import !");
  }
  
  console.log("═══════════════════════════════════════════════════════");
}

// Calcul du score avec niveau d'alerte
const qualityScore = Math.max(0, Math.round((1 - totalErrors / totalRows) * 100));

let qualityLevel = "";
if (qualityScore >= 95) {
  qualityLevel = "✅ EXCELLENT";
} else if (qualityScore >= 85) {
  qualityLevel = "🟢 BON";
} else if (qualityScore >= 70) {
  qualityLevel = "🟡 MOYEN (à corriger)";
} else {
  qualityLevel = "🔴 CRITIQUE (import déconseillé)";
}

console.log(`📈 Score de qualité : ${qualityScore}% - ${qualityLevel}`);

// Créer une feuille "Rapport de validation"
let reportSheet = workbook.getWorksheet("Rapport de validation");
if (!reportSheet) {
  reportSheet = workbook.addWorksheet("Rapport de validation");
}
reportSheet.activate();

// Effacer le contenu existant
reportSheet.getRange().clear();

// Écrire le rapport
let currentRow = 1;
reportSheet.getRange(`A${currentRow}`).setValue("RAPPORT DE VALIDATION FAB-DIS");
currentRow += 2;

reportSheet.getRange(`A${currentRow}`).setValue("Produits analysés :");
reportSheet.getRange(`B${currentRow}`).setValue(totalRows);
currentRow++;

reportSheet.getRange(`A${currentRow}`).setValue("Score de qualité :");
reportSheet.getRange(`B${currentRow}`).setValue(`${qualityScore}%`);
currentRow++;

reportSheet.getRange(`A${currentRow}`).setValue("Anomalies détectées :");
reportSheet.getRange(`B${currentRow}`).setValue(totalErrors);
currentRow += 2;

// Écrire les anomalies par catégorie
Object.keys(errors).forEach(category => {
  if (errors[category].length > 0) {
    reportSheet.getRange(`A${currentRow}`).setValue(category);
    reportSheet.getRange(`B${currentRow}`).setValue(errors[category].length);
    currentRow++;
    
    errors[category].forEach(error => {
      reportSheet.getRange(`B${currentRow}`).setValue(error);
      currentRow++;
    });
    currentRow++;
  }
});