<?php

namespace Teknisa\Libs\Util\XLS;

use Teknisa\Libs\Util\XLS\Exception;
use Teknisa\Libs\Util\XLS\Table;
use Teknisa\Libs\Util\XLS\Error;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Worksheet;

class FileReader{ 
    
    //Constantes de erro de leitura de aquivo
    
    const XLS_READER_EMPTY_COLUMN_VALUE_ERROR = 'XLS_READER_EMPTY_COLUMN_VALUE_ERROR';
    const XLS_READER_FILE_FORMAT_ERROR = 'XLS_READER_FILE_FORMAT_ERROR';
    const XLS_READER_HEADER_SIZE_ERROR  = 'XLS_READER_HEADER_SIZE_ERROR';
    const XLS_READER_INVALID_COLUMN_VALUE_ERROR = 'XLS_READER_INVALID_COLUMN_VALUE_ERROR';
    
    public function __construct() {}
    
    //Cria a associação entre nomes e index de colunas na planilha
    protected static function buildColumnNameReference($worksheet, $maxColumn, $columnNames){
        $reference = array();
        $errors = array();
        
        $column = 1;
        
        if(self::isHeaderSizeRight($maxColumn, $columnNames)){
            for($column; $column <= $maxColumn; $column++){
                $columnString = self::getColumnString($column);
                $columnName = $worksheet->getCell($columnString.'1')->getValue();
                $error = self::validateColumnValue($columnName, $columnNames, 1, $columnString);
                if($error === NULL){
                    $reference[$columnString] = mb_strtoupper($columnName, 'UTF-8');
                }
                else{
                    array_push($errors, $error);
                }
            }
        }
        else{
            $error = new Error(self::XLS_READER_HEADER_SIZE_ERROR, 1);
            array_push($errors, $error->toArray());
        }
        if(count($errors) > 0){
            throw new Exception($errors, self::XLS_READER_FILE_FORMAT_ERROR);
        }
        return ($reference);
    }
    
    //verifica se a coluna existe na tabela
    protected static function columnExists($columnName, $columnNames){
        return (in_array(mb_strtoupper($columnName, 'UTF-8'), $columnNames));
    }
    
    protected static function getColumnIndex($column){
        if(gettype($column) == 'string'){
            return Coordinate::columnIndexFromString($column);
        }
        else
            return $column;
    }
    
    protected static function getColumnString($column){
        if(gettype($column) == 'integer'){
            return Coordinate::stringFromColumnIndex($column);
        }
        else
            return $column;
    }
    
    protected static function isHeaderSizeRight($maxColumn, $columnNames){
        return ($maxColumn == count($columnNames));  
    }
    
    protected static function validateColumnValue($columnName, $columnNames, $row, $column){
        $error = NULL;
        if($columnName === NULL){
            $error = new Error(self::XLS_READER_EMPTY_COLUMN_VALUE_ERROR, $row, $column);
            $error = $error->toArray();
        }
        else{
            if(!self::columnExists($columnName, $columnNames)){
                $error =  new Error(self::XLS_READER_INVALID_COLUMN_VALUE_ERROR, $row, $column);
                $error = $error->toArray();
            }
        }
        return $error;
    }
    
    
    /* Lê uma tabela na planilha e retorna um objeto Table com as informações
    *  referentes.
    */
    public static function readWorksheetTable($worksheet, $columnNames, $minRowIndex = 2, $maxRowIndex = NULL){
        
        //configura as dimensões da tabela
        $worksheetDimensions = $worksheet->getHighestRowAndColumn();
        if($maxRowIndex === NULL){
            $maxRowIndex = $worksheetDimensions['row'];
        }
        $maxColumnIndex = self::getColumnIndex($worksheetDimensions['column']);
        
        $columnsReference = self::buildColumnNameReference($worksheet, $maxColumnIndex, $columnNames);
        
        /*Lê o arquivo construindo a linha e monta um array de arrays associativos
        * (linhas da tabela) e cada valor da linha é acessado por uma chave que
        * é o nome da coluna na planilha
        */
        $tableRows = array();
        for($row = $minRowIndex; $row <= $maxRowIndex; $row++){
            $worksheetRowContent = array();
            $rowIsEmpty = true;
            
            $column = 1;
            
            for($column; $column <= $maxColumnIndex; $column++){
                $columnLetterIndex = self::getColumnString($column);
                $columnName = $columnsReference[$columnLetterIndex];
                $cellValue = $worksheet->getCell($columnLetterIndex.$row)->getValue();
                if($cellValue !== NULL){
                    $rowIsEmpty = false;
                    if(strstr($cellValue,'=')==true){
                        $cellValue = $worksheet->getCell($columnLetterIndex.$row)->getFormattedValue();
                    }
                }
                $worksheetRowContent = array_merge($worksheetRowContent, array($columnName => $cellValue));
            }
            if(!$rowIsEmpty){
                $worksheetRow = array(
                    'index' => $row,
                    'content' => $worksheetRowContent
                );
                array_push($tableRows, $worksheetRow);
            }
        }
        
        /* Retorna o dados da tabela: um array de referencia que retorna o 
        * titulo da coluna pra cada chave com o valor de um index da planilha e 
        * as linhas da tabela.
        */
        $table = new Table($columnsReference, $tableRows);
        return $table;
    }
    
}