<?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;
use PhpOffice\PhpSpreadsheet\Chart\Chart;
use PhpOffice\PhpSpreadsheet\Chart\DataSeries;
use PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues;
use PhpOffice\PhpSpreadsheet\Chart\Legend;
use PhpOffice\PhpSpreadsheet\Chart\PlotArea;
use PhpOffice\PhpSpreadsheet\Chart\Title;

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, $charts = [])
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

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

        $chartsToAdd = $charts;
        if (empty($chartsToAdd) && is_array($pageList)) {
            foreach ($pageList as $page) {
                if (is_object($page) && property_exists($page, 'charts') && is_array($page->charts) && count($page->charts) > 0) {
                    $chartsToAdd = array_merge($chartsToAdd, $page->charts);
                }
            }
        }

        foreach (range('A', 'AA') as $columnID) {
            $sheet->getColumnDimension($columnID)->setAutoSize(true);
        }

        foreach ($chartsToAdd as $chart) {
            if (
                empty($chart['title']) ||
                empty($chart['labelsRange']) ||
                empty($chart['valuesRange'])
            ) {
                continue;
            }

            $this->addPieChart(
                $sheet,
                $chart['title'],
                $chart['labelsRange'],
                $chart['valuesRange'],
                $chart['topLeft'] ?? ($chart['cell'] ?? 'H2'),
                $chart['bottomRight'] ?? 'L20'
            );
        }

        $writer = IOFactory::createWriter($spreadsheet, "Xlsx");
        $writer->setIncludeCharts(true);
        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;
                        case 'rotate':
                            $column = $this->rotate($sheet, $order, $column, $currentRow);
                            break;
                        case 'height':
                            $this->height($sheet, $order, $column, $currentRow);
                            break;
                    }
                }
                $column++;
            }
        }
    }

    protected function height(Worksheet $sheet, $order, $column, $currentRow)
    {
        $sheet->getRowDimension($currentRow)->setRowHeight($order['height']);
        return $column;
    }

    protected function rotate(Worksheet $sheet, $order, $column, $currentRow)
    {
        $angle = $order['angle'] ?? 90;

        $sheet->getStyle($this->columnLetter($column) . $currentRow)
            ->getAlignment()
            ->setTextRotation($angle);

        return $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];
        } elseif ($c < 130) {
            $letter = 'D' . self::LETTERS[$c - 104];
        } elseif ($c < 156) {
            $letter = 'E' . self::LETTERS[$c - 130];
        } elseif ($c < 182) {
            $letter = 'F' . self::LETTERS[$c - 156];
        } elseif ($c < 208) {
            $letter = 'G' . self::LETTERS[$c - 182];
        }
        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);
        // var_dump($order['color']);
        $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;
    }

    public function addPieChart($sheet, $chartName, $labelsRange, $valuesRange, $topLeft, $bottomRight)
    {
        $sheetName = $sheet->getTitle();

        if (strpos($labelsRange, '!') === false) {
            $labelsRange = "{$sheetName}!{$labelsRange}";
        }

        if (strpos($valuesRange, '!') === false) {
            $valuesRange = "{$sheetName}!{$valuesRange}";
        }

        $labels = [
            new DataSeriesValues('String', $labelsRange, null)
        ];

        $values = [
            new DataSeriesValues('Number', $valuesRange, null)
        ];

        $series = new DataSeries(
            DataSeries::TYPE_PIECHART,
            null,
            range(0, count($values) - 1),
            [],
            $labels,
            $values
        );

        $plotArea = new PlotArea(null, [$series]);
        $legend   = new Legend(Legend::POSITION_RIGHT, null, false);
        $title    = new Title($chartName);

        $chart = new Chart(
            $chartName,
            $title,
            $legend,
            $plotArea
        );

        $chart->setTopLeftPosition($topLeft);
        $chart->setBottomRightPosition($bottomRight);

        $sheet->addChart($chart);
    }

}
