const XLSX = require('xlsx-style')

let workbook = XLSX.readFile('data.xlsx', {
    cellStyles: true
})

let families = workbook.SheetNames

let fields = {
    A: 'Date',
    B: 'Lieu',
    C: 'Photo',
    D: 'Ordre',
    E: 'Genre',
    F: 'Espèce',
    G: 'Rav/Aux/S-E',
    H: 'Stade',
    I: 'Indice d\'abondance',
    J: 'Nourriture'
}

let impactsColor = {
    'FFFFFF00': 'Ravageur toléré (Léger)',
    'FFFD8207': 'Ravageur',
    'FFFF0000': 'Ravageur lourd',
    'FF00B050': 'Auxiliaire faible',
    'FF7030A0': 'Auxiliaire',
    'FF00B0F0': 'Auxiliaire important'
}

let entities = []
families.forEach(familyName => {
    let document = workbook.Sheets[familyName]
    let rows = Object.keys(document).reduce((acc, curr) => {
        let num = parseInt(curr.slice(1))
        if (!Number.isInteger(num)) {
            return acc
        }
        return num > acc ? num : acc
    }, 0)

    let firstIndex = 5
    for (row = firstIndex; row < rows; row++) {
        let obj = {}
        for (const fieldIndex in fields) {
            let fieldName = fields[fieldIndex]
            let value = document[`${fieldIndex}${row}`].v
            if (fieldName == 'Date' && value != '') {
                let date = XLSX.SSF.parse_date_code(value)
                obj[fieldName] = date
            } else if (fieldName == 'Rav/Aux/S-E') {
                let style = document[`${fieldIndex}${row}`].s
                let fillStyle = style.fill
                let bgColor = fillStyle ? fillStyle.fgColor : undefined
                if (bgColor) {
                    obj[fieldName] = impactsColor[bgColor.rgb]
                } else {
                    obj[fieldName] = 'Sans effet'
                }
            } else {
                obj[fieldName] = value
            }
            obj.familyName = familyName
        }
        entities.push(obj)
    }
})