<?php
namespace App\Service;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Csv;
use PhpOffice\PhpSpreadsheet\Writer\Ods;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Symfony\Component\HttpFoundation\StreamedResponse;

class SpreadsheetWriter
{
    public string $projectDir;

    public function __construct(string $projectDir) 
    {
        $this->projectDir = $projectDir;
    }

    //use this to create spreadsheet with multiple worksheets
    public function createEmptySpreadsheet() : Spreadsheet
    {
        $spreadsheet = new Spreadsheet();

        $spreadsheet->removeSheetByIndex(0);

        return $spreadsheet;
    }

    //add a work sheet to spreadsheet
    public function createWorksheet(Spreadsheet $spreadsheet, string $title, array $columns, array $rows)
    {
        $spreadsheet->createSheet();

        $count = $spreadsheet->getSheetCount();

        $worksheet = $spreadsheet->setActiveSheetIndex($count - 1);

        $worksheet->setTitle($title);

        $this->addDataToWorksheet($worksheet, $columns, $rows);
    }
    
    private function addDataToWorksheet(Worksheet $worksheet, array $columns, array $rows)
    {
        // Set column names 
        $rowNumber = 1;

        $columnLetter = 'A';

        foreach ($columns as $column) {
            $worksheet->setCellValue($columnLetter . $rowNumber, $column);

            $columnLetter++;
        }

        // Set rows
        foreach ($rows as $row) {
            $columnLetter = 'A';
            $rowNumber++;

            if (is_array($row)) {
                foreach ($row as $value) {
                    $worksheet->setCellValue($columnLetter . $rowNumber, $value);

                    $columnLetter++;
                }
            }
        }
    }

    //create a spreadsheet with a single worksheet simply
    public function createSpreadsheet(array $columns, array $rows) : Spreadsheet
    {
        $spreadsheet = new Spreadsheet();
        // Get active sheet - it is also possible to retrieve a specific worksheet
        $worksheet = $spreadsheet->getActiveSheet();

        // Set cell name and merge cells
        //$worksheet->setCellValue('A1', 'Browser characteristics')->mergeCells('A1:D1');

        $this->addDataToWorksheet($worksheet, $columns, $rows);

        return $spreadsheet;
    }

    public function streamSpreadsheet(Spreadsheet $spreadsheet, string $filename = 'spreadsheet.xlsx') : StreamedResponse
    {
        $pathinfo = pathinfo($filename);

        $___filename = $pathinfo['filename'];
        $extension = 'xlsx';

        if (isset($pathinfo['extension'])) {
            $extension = $pathinfo['extension'];
        }

        switch ($extension) {
            case 'csv':
                $contentType = 'text/csv';
                $writer = new Csv($spreadsheet);
                break;

            case 'ods':
                $contentType = 'application/vnd.oasis.opendocument.spreadsheet';
                $writer = new Ods($spreadsheet);
                break;

            case 'xlsx':
            default: 
                $extension = 'xlsx';
                $contentType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
                $writer = new Xlsx($spreadsheet);
                break;
        }

        $response = new StreamedResponse();
        $response->headers->set('Content-Type', $contentType);
        $response->headers->set('Content-Disposition', 'attachment;filename="' . $___filename . '.' . $extension . '"');
        $response->setPrivate();
        $response->headers->addCacheControlDirective('no-cache', true);
        $response->headers->addCacheControlDirective('must-revalidate', true);
        $response->setCallback(function() use ($writer) {
            $writer->save('php://output');
        });

        return $response;
    }

    public function writeSpreadsheet(Spreadsheet $spreadsheet, string $path, string $filename = 'spreadsheet.xlsx') : bool
    {
        if (is_dir($path) && is_writable($path)) {
            $pathinfo = pathinfo($filename);

            $___filename = $pathinfo['filename'];
            $extension = 'xlsx';

            if (isset($pathinfo['extension'])) {
                $extension = $pathinfo['extension'];
            }

            switch ($extension) {
                case 'csv':
                    $contentType = 'text/csv';
                    $writer = new Csv($spreadsheet);
                    break;

                case 'ods':
                    $contentType = 'application/vnd.oasis.opendocument.spreadsheet';
                    $writer = new Ods($spreadsheet);
                    break;

                case 'xlsx':
                default: 
                    $extension = 'xlsx';
                    $contentType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
                    $writer = new Xlsx($spreadsheet);
                    break;
            }

            $fullLocalFileName = $path . $___filename . '.' . $extension;

            if (file_exists($fullLocalFileName)) {
                @unlink($fullLocalFileName);
            }

            $writer->save($fullLocalFileName);

            if (file_exists($fullLocalFileName)) {
                return true;
            }
        }

        return false;
    }
}