class Calculator {  static sum(...nums) {
    return nums.reduce((accum, num) => accum + Number(num), 0)
  }

  static subtract(...nums) {
    const first = Number(nums[0])
    return nums.slice(1).reduce((accum, num) => accum - Number(num), first)
  }

  static multiply(...nums) {
    return nums.reduce((accum, num) => accum * Number(num), 1)
  }

  static divide(...nums) {
    const first = Number(nums[0])
    return nums.slice(1).reduce((accum, num) => accum / Number(num), first)
  }

  static minimum(...nums) {
    const sorted = nums.sort((a, b) => a - b)
    return sorted[0]
  }

  static maximum(...nums) {
    const sorted = nums.sort((a, b) => b - a)
    return sorted[0]
  }

  static average(...nums) {
    return nums.reduce((accum, num) => accum + num, 0) / nums.length
  }
}

module.exports = Calculator

const Calculator = require('./Calculator');

class Cell {
  constructor(sheet, label, value = '', formula = null, dependents = []) {
    this.sheet = sheet
    this.label = label
    this.dependents = dependents
    this.value = value
    this.formula = formula
  }

  static operations = {
    'SUM': Calculator.sum,
    'SUB': Calculator.subtract,
    'MUL': Calculator.multiply,
    'DIV': Calculator.divide,
    'MIN': Calculator.minimum,
    'MAX': Calculator.maximum,
    'AVG': Calculator.average
  }

  calculateFormula() {
    const operation = this.formula.slice(0, 3)
    const params = this.getParamsFromFormula()

    const values = this.sheet.cells.filter(cell => params.includes(cell.label)).map(cell => cell.value)

    const operationHandler = Cell.operations[operation]
    const result = operationHandler(...values)

    if (this.value === result) {
      return result
    }

    this.value = result

    console.log(`Calculated Formula: set value ${this.value} to cell ${this.label}`)

    this.notifyDependents()

    return result
  }

  getParamsFromFormula() {
    const extractedParamsString = this.formula.match(/\\(([^)]+)\\)/)[1]
    const paramsWithGroups = extractedParamsString.split(';')

    const allParams = []

    paramsWithGroups.forEach(param => {
      if (param.includes(':')) {
        const [first, last] = param.split(':')

        const firstLetter = first.match(/[A-Z]/)[0]
        const firstNumber = Number(first.match(/[0-9]/g).join(''))

        const lastLetter = last.match(/[A-Z]/)[0]
        const lastNumber = Number(last.match(/[0-9]/g).join(''))
        
        const cells = []

        for (let i = firstLetter.charCodeAt(0); i <= lastLetter.charCodeAt(0); i++) {
          for (let j = firstNumber; j <= lastNumber; j++) {
            cells.push(String.fromCharCode(i) + j.toString())
          }
        }

        allParams.push(...cells)
      } else {
        allParams.push(param)
      }
    })

    return allParams
  }

  addDependent(cell) {
    this.dependents.push(cell)
  }

  removeDependent(label) {
    const updatedDependents = this.dependents.filter(dependent => dependent.label !== label)
    this.dependents = updatedDependents
  }

  notifyDependents() {
    this.dependents.forEach(dependent => {
      console.log(`Need to update ${dependent.label}`)
      dependent.calculateFormula()
    })
  }

  update(value, formula = null) {
    this.value = value
    this.formula = formula

    console.log(`Updated value on ${this.label}. New value: ${this.value}. Formula: ${this.formula}`)

    this.notifyDependents()
  }
}

module.exports = Cell

const Cell = require('./Cell')
const fs = require('fs')

class Sheet {
  constructor(name, cells = []) {
    this.name = name
    this.cells = cells
  }

  readCell(label) {
    const cell = this.cells.find(cell => cell.label === label)
    return cell?.value ?? ''
  }

  updateCell(value, label, formula = null) {
    const cellIndex = this.cells.findIndex(cell => cell.label === label)
    const cellToUpdate = this.cells[cellIndex]

    cellToUpdate.update(value, formula)

    if (formula) {
      const params = cellToUpdate.getParamsFromFormula()

      this.cells.forEach(cell => {
        cell.removeDependent(label)

        if (params.includes(cell.label)) {
          cell.addDependent(cellToUpdate)
        }
      })

      cellToUpdate.calculateFormula()
    }
  }

  saveCell(value, label, formula = null) {
    const cellAlreadyExists = !!this.cells.find(cell => cell.label === label)

    if (cellAlreadyExists) {
      return this.updateCell(value, label, formula)
    }

    const newCell = new Cell(this, label, value, formula)
    this.cells.push(newCell)

    if (formula) {
      const params = newCell.getParamsFromFormula()

      this.cells.forEach(cell => {
        cell.removeDependent(label)

        if (params.includes(cell.label)) {
          cell.addDependent(newCell)
        }
      })

      newCell.calculateFormula()
    }
  }

  writeCell(value, label) {
    const operationsRegex = Object.keys(Cell.operations).join('\\\\(|^')
    const isFormulaRegex = new RegExp(`^${operationsRegex}\\\\(`)
    const isFormula = typeof value === 'string' && !!value.match(isFormulaRegex)

    if (isFormula) {
      this.saveCell('', label, value)
    } else {
      this.saveCell(value, label)
    }
  }

   static saveFile(sheet, name) {
    const nonCircularSheet = new Sheet(sheet.name)

    sheet.cells.forEach(cell => {
      nonCircularSheet.saveCell(cell.value, cell.label, cell.formula)
    })

    nonCircularSheet.cells.forEach(cell => {
      cell.sheet = 'self'
      cell.dependents = []
    })

    const stringifiedData = JSON.stringify(nonCircularSheet)

    fs.writeFile(`${name}.sheet`, stringifiedData, 'utf8', (err) => {
      console.log(err)
    })
  }

  static openFile(file) {
    return new Promise((resolve, reject) => {
      fs.readFile(file, 'utf8', (err, data) => {
        if (err) {

          console.log(err)
          reject(err)

        } else {

          const loadedSheet = JSON.parse(data)

          const newSheet = new Sheet(loadedSheet.name)

          loadedSheet.cells.forEach(cell => {
            newSheet.saveCell(cell.value, cell.label, cell.formula)
          })

          resolve(newSheet)
        }
      })
    })
  }
}

module.exports = Sheet

const Sheet = require('./Sheet')

const sheet1 = new Sheet('Planilha 1')
console.log(sheet1)

sheet1.writeCell(4, 'A1')
console.log('A1:', sheet1.readCell('A1'))

sheet1.writeCell(4, 'A2')
console.log('A2:', sheet1.readCell('A2'))

sheet1.writeCell(0, 'B1')
console.log('B1:', sheet1.readCell('B1'))

sheet1.writeCell(3, 'B2')
console.log('B2:', sheet1.readCell('B2'))

sheet1.writeCell(0, 'C1')
console.log('C1:', sheet1.readCell('C1'))

sheet1.writeCell(3, 'C2')
console.log('C2:', sheet1.readCell('C2'))

sheet1.writeCell('SUM(A1:C2)', 'A3')
console.log('A3:', sheet1.readCell('A3'))

sheet1.writeCell(8, 'A1')
console.log('A1:', sheet1.readCell('A1'))
console.log('A3:', sheet1.readCell('A3'))

sheet1.writeCell('AVG(A1;A2;A3)', 'A4')
console.log('A4:', sheet1.readCell('A4'))

sheet1.writeCell(9, 'B1')
console.log('B1:', sheet1.readCell('B1'))

sheet1.writeCell(1, 'B3')
console.log('B3:', sheet1.readCell('B3'))

sheet1.writeCell('SUB(B1:B3)', 'B4')
console.log('A3:', sheet1.readCell('A3'))
console.log('A4:', sheet1.readCell('A4'))
console.log('B4:', sheet1.readCell('B4'))

sheet1.writeCell('MIN(A1:A5)', 'A6')
sheet1.writeCell('MAX(B1:B6)', 'B6')
console.log('A6:', sheet1.readCell('A6'))
console.log('B6:', sheet1.readCell('B6'))

sheet1.writeCell(18, 'C1')
console.log('C1:', sheet1.readCell('C1'))
sheet1.writeCell('DIV(C1;C2)', 'C3')
console.log('A3:', sheet1.readCell('A3'))
console.log('A4:', sheet1.readCell('A4'))
console.log('C3:', sheet1.readCell('C3'))

sheet1.writeCell(4, 'D1')
sheet1.writeCell(8, 'D2')
console.log('D1:', sheet1.readCell('D1'))
console.log('D2:', sheet1.readCell('D2'))
sheet1.writeCell('MUL(D1;D2)', 'D3')
sheet1.writeCell('SUM(D1:D3)', 'D4')
sheet1.writeCell('SUM(A1:D4)', 'D5')
console.log('D3:', sheet1.readCell('D3'))
console.log('D4:', sheet1.readCell('D4'))
console.log('D5:', sheet1.readCell('D5'))

Sheet.saveFile(sheet1, 'teste')

Sheet.openFile('teste.sheet').then(sheet2 => {

  sheet2.writeCell('Teste', 'G1')
  sheet2.writeCell(3, 'G2')
  sheet2.writeCell(4, 'G3')
  sheet2.writeCell('SUM(G2;G3)', 'G4')
  console.log('Planilha 2 -> G1:', sheet2.readCell('G1'))
  console.log('Planilha 2 -> G2:', sheet2.readCell('G2'))
  console.log('Planilha 2 -> G3:', sheet2.readCell('G3'))
  console.log('Planilha 2 -> G4:', sheet2.readCell('G4'))
  console.log('Planilha 1 -> G1:', sheet1.readCell('G1'))
  console.log('Planilha 1 -> G2:', sheet1.readCell('G2'))
  console.log('Planilha 1 -> G3:', sheet1.readCell('G3'))
  console.log('Planilha 1 -> G4:', sheet1.readCell('G4'))

  console.log('\\n---\\n')
  console.log(sheet1)
  console.log(sheet2)
})