<?php

namespace App\Providers\ExcelReport;

use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

class ExcelReport
{
    public const LETTERS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';

    private $equivalences = [
        'print' => 'print',
        'pass' => 'pass',
        'merge' => 'merge',
        'mergeborder' => 'mergeborder',
        'vmerge' => 'vmerge',
        'advance' => 'advance',
        'width' => 'width',
        'border' => 'border',
        'bold' => 'bold',
        'center' => 'center',
        'top' => 'top',
        'left' => 'left',
        'wrap' => 'wrap',
        'size' => 'size',
        'width-auto' => 'autosize',
        'rangeValues' => 'rangeValues',
        'color' => 'color',
        'background' => 'background'
    ];
    public function exportToExcel($data, $metadata, $pageList)
    {

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $this->render(
            $sheet,
            $data,
            $metadata,
            $pageList
        );

        foreach (range('A', 'AA') as $columnID) {
            $sheet->getColumnDimension($columnID)->setAutoSize(true);
        }
        $writer = IOFactory::createWriter($spreadsheet, "Xlsx");
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $metadata->title . '.xlsx"');
        $writer->save("php://output");
    }

    public function render($sheet, $data, $metadata, $pageList)
    {
        $currentRow = 0;

        foreach ($data as $k => $element) {
            foreach ($pageList as $page) {
                $this->renderAllInOnePage($sheet, $page, $element, $currentRow, $metadata);
            }
            $currentRow += $metadata->rowLength ? $metadata->rowLength : ($metadata->evaluateRowLength ? $metadata->evaluateRowLength() : 1);
        }
    }

    public function renderAllInOnePage($sheet, $page, $element, $initialRow, $metadata)
    {
        foreach ($page->run($element, $metadata) as $ind => $row) {
            $column = 0;
            $currentRow = ($initialRow + $ind + 1);
            foreach ($row as $orders) {
                foreach ($orders as $order) {
                    switch ($order['action']) {
                        case 'print':
                            $column = $this->print($sheet, $order, $column, $currentRow);
                            break;
                        case '':
                            $column = $this->pass($sheet, $order, $column, $currentRow);
                            break;
                        case 'merge':
                            $column = $this->merge($sheet, $order, $column, $currentRow);
                            break;
                        case 'mergeborder':
                            $column = $this->mergeborder($sheet, $order, $column, $currentRow);
                            break;
                        case 'vmerge':
                            $column = $this->vmerge($sheet, $order, $column, $currentRow);
                            break;
                        case 'advance':
                            $column = $this->advance($sheet, $order, $column, $currentRow);
                            break;
                        case 'width':
                            $column = $this->width($sheet, $order, $column, $currentRow);
                            break;
                        case 'border':
                            $column = $this->border($sheet, $order, $column, $currentRow);
                            break;
                        case 'borderBottom':
                            $column = $this->borderBottom($sheet, $order, $column, $currentRow);
                            break;
                        case 'bold':
                            $column = $this->bold($sheet, $order, $column, $currentRow);
                            break;
                        case 'center':
                            $column = $this->center($sheet, $order, $column, $currentRow);
                            break;
                        case 'top':
                            $column = $this->top($sheet, $order, $column, $currentRow);
                            break;
                        case 'left':
                            $column = $this->left($sheet, $order, $column, $currentRow);
                            break;
                        case 'wrap':
                            $column = $this->wrap($sheet, $order, $column, $currentRow);
                            break;
                        case 'size':
                            $column = $this->size($sheet, $order, $column, $currentRow);
                            break;
                        case 'width-auto':
                            $column = $this->autosize($sheet, $order, $column, $currentRow);
                            break;
                        case 'rangeValues':
                            $column = $this->rangeValues($sheet, $order, $column, $currentRow);
                            break;
                        case 'color':
                            $column = $this->color($sheet, $order, $column, $currentRow);
                            break;
                        case 'background':
                            $column = $this->background($sheet, $order, $column, $currentRow);
                            break;
                    }
                }
                $column++;
            }
        }
    }

    function columnLetter($c)
    {
        $c = intval($c);
        if ($c < 26) {
            $letter = self::LETTERS[$c];
        } elseif ($c < 52) {
            $letter = 'A' . self::LETTERS[$c - 26];
        } elseif ($c < 78) {
            $letter = 'B' . self::LETTERS[$c - 52];
        } elseif ($c < 104) {
            $letter = 'C' . self::LETTERS[$c - 78];
        } else {
            $letter = 'D' . self::LETTERS[$c - 104];
        }
        return $letter;
    }

    protected function rangeValues(Worksheet $sheet, $order, $column, $currentRow)
    {
        $range1 = $this->columnLetter($column) . $currentRow;
        $validation = $sheet->getCell($range1)->getDataValidation();
        $validation->setType(DataValidation::TYPE_LIST);
        $validation->setErrorStyle(DataValidation::STYLE_INFORMATION);
        $validation->setAllowBlank(false);
        $validation->setShowInputMessage(true);
        $validation->setShowErrorMessage(true);
        $validation->setShowDropDown(true);
        $validation->setErrorTitle($order['errorTitle'] ?? '');
        $validation->setError($order['error'] ?? '');
        $validation->setPromptTitle($order['promptTitle'] ?? '');
        $validation->setPrompt($order['prompt'] ?? '');
        $validation->setFormula1('"' . implode(',', $order['elements']) . '"');
        return $column;
    }

    protected function color(Worksheet $sheet, $order, $column, $currentRow)
    {
        $range1 = $this->columnLetter($column) . $currentRow;
        $sheet->getStyle($range1)->getFont()->getColor()->setARGB($order['color']);
        return $column;
    }

    protected function background(Worksheet $sheet, $order, $column, $currentRow)
    {
        $range1 = $this->columnLetter($column) . $currentRow;
        $sheet->getStyle($range1)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
        $sheet->getStyle($range1)->getFill()->getStartColor()->setARGB($order['color']);
        return $column;
    }

    protected function print($sheet, $order, $column, $currentRow)
    {
        $range1 = $this->columnLetter($column) . $currentRow;
        $sheet->setCellValue("$range1", $order['text']);
        return $column;
    }
    protected function pass($sheet, $order, $column, $currentRow)
    {
        return $column;
    }

    protected function mergeborder(Worksheet $sheet, $order, $column, $currentRow)
    {
        $range1 = $this->columnLetter($column) . $currentRow;
        $range2 = $this->columnLetter($column + $order['lines']) . $currentRow;
        $sheet->mergeCells("$range1:$range2");
        for ($ini = $column; $ini <= ($column + $order['lines']); $ini++) {
            $sheet->getStyle($this->columnLetter($ini) . $currentRow)
                ->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
        }
        $column += $order['lines'];
        return $column;
    }
    protected function merge($sheet, $order, $column, $currentRow)
    {
        $range1 = $this->columnLetter($column) . $currentRow;
        $range2 = self::LETTERS[$column + $order['lines']] . $currentRow;
        $sheet->mergeCells("$range1:$range2");
        $column += $order['lines'];
        return $column;
    }
    protected function vmerge($sheet, $order, $column, $currentRow)
    {
        $range1 = $this->columnLetter($column) . $currentRow;
        $range2 = $this->columnLetter($column) . ($currentRow - (-$order['lines']));
        $sheet->mergeCells("$range1:$range2");
        return $column;
    }
    protected function size($sheet, $order, $column, $currentRow)
    {
        $sheet->getStyle($this->columnLetter($column) . $currentRow)->getFont()->setSize($order['lines']);
        return $column;
    }
    protected function advance($sheet, $order, $column, $currentRow)
    {
        $column += $order['lines'];
        return $column;
    }

    protected function width($sheet, $order, $column, $currentRow)
    {
        $sheet->getColumnDimensionByColumn($column + 1)->setWidth($order['width']);
        return $column;
    }

    protected function autosize($sheet, $order, $column, $currentRow)
    {
        foreach (range('A', 'AA') as $columnID) {
            $sheet->getColumnDimension($columnID)->setAutoSize(true);
        }
        return $column;
    }

    protected function border(Worksheet $sheet, $order, $column, $currentRow)
    {
        $sheet->getStyle($this->columnLetter($column) . $currentRow)
            ->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
        return $column;
    }

    // protected function borderBottom(Worksheet $sheet, $order, $column, $currentRow)
    // {
    //     $borderWidth = [
    //         'thin' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
    //         'medium' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM,
    //         'double' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_DOUBLE,
    //     ];
    //     $sheet->getStyle($this->columnLetter($column) . $currentRow)
    //         ->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
    //     return $column;
    // }

    protected function borderBottom(Worksheet $sheet, $order, $column, $currentRow)
    {
        $sheet->getStyle($this->columnLetter($column) . $currentRow)
            ->getBorders()->getBottom()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM);
        return $column;
    }

    protected function bold($sheet, $order, $column, $currentRow)
    {
        $sheet->getStyle($this->columnLetter($column) . $currentRow)->getFont()->setBold(true);
        return $column;
    }

    protected function center($sheet, $order, $column, $currentRow)
    {
        $sheet->getStyle($this->columnLetter($column) . $currentRow)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        return $column;
    }

    protected function top($sheet, $order, $column, $currentRow)
    {
        $sheet->getStyle($this->columnLetter($column) . $currentRow)->getAlignment()->setVertical(Alignment::VERTICAL_TOP);
        return $column;
    }

    protected function left($sheet, $order, $column, $currentRow)
    {
        $sheet->getStyle($this->columnLetter($column) . $currentRow)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
        return $column;
    }

    protected function wrap($sheet, $order, $column, $currentRow)
    {
        $sheet->getStyle($this->columnLetter($column) . $currentRow)->getAlignment()->setWrapText(true);
        return $column;
    }
}
