<?php
require_once 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Style\Border;

// Border Style
$borderStyle = [
    'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['argb' => 'FDD0CF']]]
];
// Clean title from filename
function cleanTitleFromFilename($filename)
{
    $title = preg_replace('/_\d{2}_\d{2}_\d{4}_\d{2}_\d{2}_\d{2}$/', '', $filename);
    return strtoupper(str_replace('_', ' ', $title)) . ' ';
}

// Apply Color
function applyColorAlterning($sheet, $startRow, $endRow, $lastColumn)
{
    global $borderStyle;
    for ($row = $startRow; $row <= $endRow; $row++) {
        if ($row % 2 == 0) { // Even rows
            $sheet->getStyle("A$row:$lastColumn" . "$row")
                ->getFill()
                ->setFillType(Fill::FILL_SOLID)
                ->getStartColor()
                ->setARGB('ffffff'); // SMAC graphic color scheme 
        } else {
            $sheet->getStyle("A$row:$lastColumn" . "$row")
                ->getFill()
                ->setFillType(Fill::FILL_SOLID)
                ->getStartColor()
                ->setARGB('d9d9d9'); // Duo tone if not even row
            $sheet->getStyle("A$row:$lastColumn" . "$row")
                ->applyFromArray($borderStyle);
        }
    }
}


// Apply filter and Main Color
function applyFilterAndColor($sheet, $firstColumn, $lastColumn, $row)
{
    $sheet->setAutoFilter("{$firstColumn}{$row}:{$lastColumn}{$row}");
    $sheet->getStyle("{$firstColumn}{$row}:{$lastColumn}{$row}")
        ->getFill()
        ->setFillType(Fill::FILL_SOLID)
        ->getStartColor()
        ->setARGB('a6a6a6');
}

// Apply Main Color Row
function applyMainColor($sheet, $firstColumn, $lastColumn, $row)
{
    $sheet->getStyle("{$firstColumn}{$row}:{$lastColumn}{$row}")
        ->getFill()
        ->setFillType(Fill::FILL_SOLID)
        ->getStartColor()
        ->setARGB('ff3300');
}

// Apply title and style
function applyTitleAndStyle($sheet, $firstColumn, $lastColumn, $row, $title)
{
    $sheet->mergeCells("{$firstColumn}{$row}:{$lastColumn}{$row}");
    $sheet->getStyle("{$firstColumn}{$row}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $sheet->getStyle("{$firstColumn}{$row}")->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
    $sheet->getStyle("{$firstColumn}{$row}")->getFont()->setBold(true);
    $sheet->setCellValue("{$firstColumn}{$row}", $title);
}

// Apply auto-sizing on Column
function applyAutoColumnSizing($sheet, $lastColumn)
{
    $highestColumnIndex = Coordinate::columnIndexFromString($lastColumn); // convert column to numeric index
    for ($col = 1; $col <= $highestColumnIndex; $col++) {
        $sheet->getColumnDimensionByColumn($col)->setAutoSize(true);
    }
}

// Apply monetary format
function applyMonetaryFormat($sheet, $columns, $format = 'FORMAT_CURRENCY_EUR_SIMPLE')
{
    foreach ($columns as $column) {
        // $sheet->getStyle($column)->getNumberFormat()->setFormatCode(NumberFormat::{$format});
        $sheet->getStyle($column)->getNumberFormat()->setFormatCode('#,##0.00 €');;
    }
}
