<?php
defined('BASEPATH') or exit('No direct script access allowed');

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class Spreadsheets
{

    public function __construct()
    {
        require_once APPPATH . '../vendor/autoload.php';
    }

    public function export($data)
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $columnNames = [];
        foreach ($data as $key => $value) {
            $item = (array) $value;
            if (empty($columnNames)) {
                $columnNames = array_keys($item);
                $columnNames = array_map('strtoupper', $columnNames);
                $sheet->fromArray([$columnNames], null, 'A1');
            }
            $rowData = array_values($item);
            $row = $key + 2;
            $sheet->fromArray([$rowData], null, 'A' . ($row));
            if (!empty($rowData[55])){
                if ($rowData[55] == "red"){
                    $spreadsheet->getActiveSheet()->getStyle("A$row:ZZ$row")->getFill()
                        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                        ->getStartColor()->setARGB('FFFF0000');  
                } elseif ($rowData[55] == "yellow"){
                    $spreadsheet->getActiveSheet()->getStyle("A$row:ZZ$row")->getFill()
                        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                        ->getStartColor()->setARGB('FFFFFF00');  
                }
            }  
        }

        // // Autoajustar el ancho de las columnas
        // foreach (range('A', $sheet->getHighestDataColumn()) as $column) {
        //     $sheet->getColumnDimension($column)->setAutoSize(true);
        // }

        $writer = new Xlsx($spreadsheet);
        $tempFilePath = tempnam(sys_get_temp_dir(), 'spreadsheet_');
        $writer->save($tempFilePath);

        return $tempFilePath;
    }

    public function exportWithHeaderDescription($data, $data_header, $number_one_id = false)
    {

        // Antes de tu bucle foreach que agrega la cabecera
        $dataHeaderStyle = [
            'font' => [
                'bold' => true,
                'color' => ['rgb' => '000000'],
            ],
            // 'fill' => [
            //     'fillType' => 'solid',
            //     'startColor' => ['rgb' => 'D9D9D9'],
            // ],
        ];

      
        $headerStyle = [
            'font' => [
                'bold' => true,
                'color' => ['rgb' => 'FFFFFF'],
            ],
            'fill' => [
                'fillType' => 'solid',
                'startColor' => ['rgb' => '111e29'],
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];

        $detailStyle = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        
        $a = 1;
        // Agregar información adicional al inicio del archivo
        foreach ($data_header as $key => $value) {
            if($number_one_id == true && $a == 1){
                $sheet->setCellValue('A' . $a, "$key : $value");
            }else{
                $sheet->setCellValue('A' . $a, "$key");
            }
            $sheet->getStyle('A' . $a)->applyFromArray($dataHeaderStyle);
            if($number_one_id == true && $a > 1 || $number_one_id == false){
                $sheet->setCellValue('B' . $a, "$value");
                $sheet->getStyle('B' . $a)->applyFromArray($dataHeaderStyle);
            }
            $a++;
        }
        $a = ($a + 2);
        $b = 1 + $a;
        $columnNames = [];
        foreach ($data as $key => $value) {
            $item = (array) $value;
            if (empty($columnNames)) {
                $columnNames = array_keys($item);
                $columnNames = array_map('strtoupper', $columnNames);
                $sheet->fromArray([$columnNames], null, 'A' . $a);
                $sheet->getStyle('A' . $a . ':' . $sheet->getHighestDataColumn() . $a)->applyFromArray($headerStyle);
            }
            $rowData = array_values($item);
            $sheet->fromArray([$rowData], null, 'A' . ($key + $b));
            $sheet->getStyle('A' . ($key + $b) . ':' . $sheet->getHighestDataColumn() . ($key + $b))->applyFromArray($detailStyle);

        }

    
        // Autoajustar el ancho de las columnas
        // foreach (range('C', $sheet->getHighestDataColumn()) as $column) {
        //     $sheet->getColumnDimension($column)->setAutoSize(true);
        // }

        $writer = new Xlsx($spreadsheet);
        $tempFilePath = tempnam(sys_get_temp_dir(), 'spreadsheet_');
        $writer->save($tempFilePath);

        return $tempFilePath;
    }

    public function import($file_path, $letter)
    {
        // Cargar la clase Coordinate manualmente
        require_once APPPATH . '../vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Coordinate.php';

        // Cargar la hoja de cálculo usando la librería PhpSpreadsheet
        $spreadsheet = IOFactory::load($file_path);
        $sheet = $spreadsheet->getActiveSheet();

        $data = [];
        $highestRow = $sheet->getHighestRow();

        if (!empty($letter)) {
            $highestColumn = $letter; // Tomamos solo hasta la columna E (quinta columna);
        } else {
            $highestColumn = $sheet->getHighestColumn();
        }
        // Obtener el índice numérico de la columna más alta
        $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);

        // Recorrer las filas y columnas para obtener los datos
        for ($row = 1; $row <= $highestRow; ++$row) {
            $rowData = [];
            for ($col = 1; $col <= $highestColumnIndex; ++$col) {
                $cellValue = $sheet->getCellByColumnAndRow($col, $row)->getValue();
                $rowData[] = $cellValue;
            }
            $data[] = $rowData;
        }

        // Eliminamos la primera fila (encabezados)
        array_shift($data);

        return $data;
    }
	
	public function exportWithHeader($rows, $header)
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        
        foreach ($header as $key => $value) 
        {
            $sheet->setCellValue($key .'1', "$value");
        }

        for($i=65; $i<=90; $i++) // letters Array 
        {  
            $letters[] = chr($i);  
        }
        for($i=65; $i<=90; $i++) // letters Array 
        {  
            $letters[] = 'A'.chr($i);  
        }

        foreach ($rows as $row_key => $row_value) 
        {
            $array_row = array_values((array)$row_value);
            //pre($array_row);
            for ($i=0; $i < count($header); $i++) 
            {
                //$objPHPExcel->getActiveSheet()->SetCellValue($letters[$i].($row_key + 2), utf8_encode($array_row[$i]));
                $sheet->setCellValue($letters[$i].($row_key + 2), $array_row[$i]);
                //pre($array_row[$i]);
            }
            //pre($array_row);
        }

       /* $a = ($a + 2);
        $b = 1 + $a;
        $columnNames = [];
        foreach ($data as $key => $value) {
            $item = (array) $value;
            if (empty($columnNames)) {
                $columnNames = array_keys($item);
                $columnNames = array_map('strtoupper', $columnNames);
                $sheet->fromArray([$columnNames], null, 'A' . $a);
                $sheet->getStyle('A' . $a . ':' . $sheet->getHighestDataColumn() . $a)->applyFromArray($headerStyle);
            }
            $rowData = array_values($item);
            $sheet->fromArray([$rowData], null, 'A' . ($key + $b));
            $sheet->getStyle('A' . ($key + $b) . ':' . $sheet->getHighestDataColumn() . ($key + $b))->applyFromArray($detailStyle);

        }*/

        $writer = new Xlsx($spreadsheet);
        $tempFilePath = tempnam(sys_get_temp_dir(), 'spreadsheet_');
        $writer->save($tempFilePath);

        return $tempFilePath;
    }
}