<?php

namespace App\Exports;

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Symfony\Component\HttpFoundation\StreamedResponse;

class AdherencePobDriveExport
{
    private const GREEN = 'C6EFCE';

    private const GREY = 'D9D9D9';

    private const PINK = 'F8CBAD';

    private const RED = 'C00000';

    private const MAROON = '8A1538';

    private const WHITE = 'FFFFFF';

    private const BLUE = '0000FF';

    /** @var array<int, mixed> */
    private $abmRows;

    /** @var array<int, mixed> */
    private $rbmRows;

    /** @var array<int, mixed> */
    private $tmRows;

    /** @var array<int, array<string, mixed>> */
    private $dayTemplate;

    private $month;

    public function __construct($data, string $month)
    {
        $this->abmRows = $this->listRows($data, 'abmReport');
        $this->rbmRows = $this->listRows($data, 'rbmReport');
        $this->tmRows = $this->listRows($data, 'tmReport');
        $this->dayTemplate = $this->dayTemplate();
        $this->month = $month;
    }

    public function download(): StreamedResponse
    {
        $spreadsheet = $this->spreadsheet();
        $filename = $this->filename();

        return response()->streamDownload(function () use ($spreadsheet) {
            $writer = new Xlsx($spreadsheet);
            $writer->save('php://output');
            $spreadsheet->disconnectWorksheets();
        }, $filename, [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        ]);
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    public function sections(): array
    {
        return [
            $this->abmSection(),
            $this->regionSection(),
            $this->territorySection(),
        ];
    }

    public function filename(): string
    {
        $slug = trim((string) preg_replace('/[^A-Za-z0-9]+/', '-', $this->month), '-');

        return 'Adherence-Pack-POB-Drive-' . ($slug !== '' ? $slug : 'report') . '.xlsx';
    }

    public function spreadsheet(): Spreadsheet
    {
        $spreadsheet = new Spreadsheet();
        $abm = $spreadsheet->getActiveSheet();
        $abm->setTitle('Details ABM wise');
        $this->writeAbmSheet($abm);

        $region = $spreadsheet->createSheet();
        $region->setTitle('Region Daily Winners');
        $this->writeRegionSheet($region);

        $territory = $spreadsheet->createSheet();
        $territory->setTitle('Territory Daily Winners');
        $this->writeTerritorySheet($territory);

        $spreadsheet->setActiveSheetIndex(0);

        return $spreadsheet;
    }

    private function writeAbmSheet($sheet): void
    {
        $identity = [
            'ZBM',
            'RBM',
            'ABM HQ',
            'HQ Div',
            'ABM Name',
            'Emp ID',
            'Reporting Manager (RBM)',
            'Email',
            'Approved Count per ABM',
            'Remarks',
        ];
        $summary = [
            'Working Days',
            'Daily Target (POBs = Approved Count)',
            'Month Target (POBs)',
            'Total TMs Qualified (≥₹15k POB)',
            'Achievement %',
            'Total Chemists Qualified (≥₹15k POB)',
            'Days Daily Target Met',
            'Target Status',
        ];
        $dayStart = count($identity) + 1;
        $summaryStart = $dayStart + count($this->dayTemplate);
        $lastCol = $summaryStart + count($summary) - 1;

        $sheet->setCellValue('A1', 'Adherence Pack POB Drive – ABM');
        $sheet->setCellValue('A3', 'Data of no. of chemists with Adherence Pack POB ≥ ₹15,000  will be added by Smartian App');
        foreach ($this->dayTemplate as $index => $day) {
            $sheet->setCellValue($this->col($dayStart + $index) . '4', $day['type'] === 'H' ? 'H' : 'W');
        }

        $headers = $identity;
        foreach ($this->dayTemplate as $day) {
            $headers[] = $this->dayHeader($day['label']);
        }
        $headers = array_merge($headers, $summary);
        $this->writeValues($sheet, 5, $headers);
        $this->styleHeaderRow($sheet, 5, $lastCol, count($identity), $summaryStart);
        $sheet->getStyle('A3')->getFont()->setName('Century Gothic')->setBold(true)->setSize(9)->getColor()->setARGB(self::BLUE);
        $this->paintDayTypes($sheet, 4, $dayStart, false);
        $this->paintDayHeaders($sheet, 5, $dayStart, false);

        $rowNumber = 6;
        foreach ($this->abmRows as $row) {
            $values = [];
            foreach ($identity as $key) {
                $values[] = $this->field($row, $key);
            }
            $dayMap = $this->dayMap($this->field($row, 'days'));
            foreach ($this->dayTemplate as $day) {
                $values[] = $this->dayValue($dayMap[$day['date']] ?? $day, 'value');
            }
            foreach ($summary as $key) {
                $values[] = $this->field($row, $key);
            }
            $this->writeValues($sheet, $rowNumber, $values);
            $this->paintDataDays($sheet, $rowNumber, $dayStart, false);
            if ($this->isVacantName($this->field($row, 'ABM HQ')) || $this->isVacantName($this->field($row, 'ABM Name'))) {
                $this->paintRow($sheet, $rowNumber, $lastCol, self::PINK);
            }
            $rowNumber++;
        }

        $this->finishSheet($sheet, 5, max(5, $rowNumber - 1), $lastCol, $dayStart, 14);
        $sheet->getStyle('A3')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
        $sheet->freezePane('A6');
    }

    private function writeRegionSheet($sheet): void
    {
        $columns = $this->regionColumns();
        $lastCol = 3 + count($columns);
        $sheet->setCellValue('A1', 'Region-wise Daily Winner – Highest no. of chemists with Adherence Pack POB ≥ ₹15,000');

        $typeRow = ['', '', ''];
        $headerRow = ['Region (RBM)', 'RBM Name', 'RBM Employee Code'];
        foreach ($columns as $column) {
            if ($column['kind'] === 'total-tm' || $column['kind'] === 'total-chemist') {
                $typeRow[] = '';
                $headerRow[] = $column['header'];
                continue;
            }
            $typeRow[] = $column['type'] === 'H' ? 'H' : 'W';
            $headerRow[] = $column['header'];
        }

        $this->writeValues($sheet, 3, $typeRow);
        $this->writeValues($sheet, 4, $headerRow);
        $this->styleHeaderRow($sheet, 4, $lastCol, 3, $lastCol - 1);
        $this->paintRegionDayMarks($sheet, $columns);

        $rowNumber = 5;
        foreach ($this->rbmRows as $row) {
            $dayMap = $this->dayMap($this->field($row, 'days'));
            $values = [
                $this->field($row, 'Region (RBM)'),
                $this->field($row, 'RBM Name'),
                $this->field($row, 'RBM Employee Code'),
            ];
            foreach ($columns as $column) {
                if ($column['kind'] === 'total-tm') {
                    $values[] = $this->field($row, 'Total TMs Qualified (≥₹15k POB)');
                    continue;
                }
                if ($column['kind'] === 'total-chemist') {
                    $values[] = $this->field($row, 'Total Chemists Qualified (≥₹15k POB)');
                    continue;
                }
                $day = $dayMap[$column['date']] ?? $column['day'];
                $values[] = $column['kind'] === 'holiday'
                    ? 'Holiday'
                    : $this->dayValue($day, $column['kind'] === 'chemist' ? 'chemistsQualified' : 'value');
            }
            $this->writeValues($sheet, $rowNumber, $values);
            $this->paintRegionDataDays($sheet, $rowNumber, $columns);
            if ($this->isVacantName($values[0]) || $this->isVacantName($values[1])) {
                $this->paintRow($sheet, $rowNumber, $lastCol, self::PINK);
            }
            $rowNumber++;
        }

        $this->finishSheet($sheet, 4, max(4, $rowNumber - 1), $lastCol, 4, 13);
        $sheet->freezePane('A5');
    }

    private function writeTerritorySheet($sheet): void
    {
        $dayStart = 4;
        $totalCol = $dayStart + count($this->dayTemplate);
        $sheet->setCellValue('A1', 'Territory-wise Daily Winner – Highest no. of chemists with Adherence Pack POB ≥ ₹15,000');

        $typeRow = ['', '', ''];
        foreach ($this->dayTemplate as $day) {
            $typeRow[] = $day['type'] === 'H' ? 'H' : 'W';
        }
        $this->writeValues($sheet, 3, $typeRow);

        $headers = ['Territory', 'TM Name', 'TM Employee Code'];
        foreach ($this->dayTemplate as $day) {
            $headers[] = $this->dayHeader($day['label']);
        }
        $headers[] = 'Total Chemists Qualified (≥₹15k POB)';
        $this->writeValues($sheet, 4, $headers);
        $this->styleHeaderRow($sheet, 4, $totalCol, 3, $totalCol);
        $this->paintDayTypes($sheet, 3, $dayStart, true);
        $this->paintDayHeaders($sheet, 4, $dayStart, true);

        $rowNumber = 5;
        foreach ($this->tmRows as $row) {
            $dayMap = $this->dayMap($this->field($row, 'days'));
            $values = [
                $this->field($row, 'Territory'),
                $this->field($row, 'TM Name'),
                $this->field($row, 'TM Employee Code'),
            ];
            foreach ($this->dayTemplate as $day) {
                $values[] = $this->dayValue($dayMap[$day['date']] ?? $day, 'value');
            }
            $values[] = $this->field($row, 'Total Chemists Qualified (≥₹15k POB)');
            $this->writeValues($sheet, $rowNumber, $values);
            $this->paintDataDays($sheet, $rowNumber, $dayStart, true);
            if ($this->isVacantName($values[0]) || $this->isVacantName($values[1])) {
                $this->paintRow($sheet, $rowNumber, $totalCol, self::PINK);
            }
            $rowNumber++;
        }

        $this->finishSheet($sheet, 4, max(4, $rowNumber - 1), $totalCol, $dayStart, 13);
        $sheet->freezePane('A5');
    }

    /**
     * @return array<string, mixed>
     */
    private function abmSection(): array
    {
        $identity = [
            'ZBM',
            'RBM',
            'ABM HQ',
            'HQ Div',
            'ABM Name',
            'Emp ID',
            'Reporting Manager (RBM)',
            'Email',
            'Approved Count per ABM',
            'Remarks',
        ];
        $summary = [
            'Working Days',
            'Daily Target (POBs = Approved Count)',
            'Month Target (POBs)',
            'Total TMs Qualified (≥₹15k POB)',
            'Achievement %',
            'Total Chemists Qualified (≥₹15k POB)',
            'Days Daily Target Met',
            'Target Status',
        ];
        $dayTypes = array_fill(0, count($identity), '');
        $headers = $identity;
        foreach ($this->dayTemplate as $day) {
            $dayTypes[] = $day['type'] === 'H' ? 'H' : 'W';
            $headers[] = $this->dayHeader($day['label']);
        }
        $dayTypes = array_merge($dayTypes, array_fill(0, count($summary), ''));
        $headers = array_merge($headers, $summary);

        $rows = [];
        foreach ($this->abmRows as $row) {
            $cells = [];
            foreach ($identity as $key) {
                $cells[] = $this->field($row, $key);
            }
            $dayMap = $this->dayMap($this->field($row, 'days'));
            foreach ($this->dayTemplate as $day) {
                $cells[] = $this->dayValue($dayMap[$day['date']] ?? $day, 'value');
            }
            foreach ($summary as $key) {
                $cells[] = $this->field($row, $key);
            }
            $rows[] = [
                'cells' => $cells,
                'vacant' => $this->isVacantName($this->field($row, 'ABM HQ')) || $this->isVacantName($this->field($row, 'ABM Name')),
            ];
        }

        return [
            'id' => 'abm',
            'title' => 'Details ABM wise – ' . $this->month,
            'dayTypes' => $dayTypes,
            'headers' => $headers,
            'rows' => $rows,
        ];
    }

    /**
     * @return array<string, mixed>
     */
    private function regionSection(): array
    {
        $columns = $this->regionColumns();
        $dayTypes = ['', '', ''];
        $headers = ['Region (RBM)', 'RBM Name', 'RBM Employee Code'];
        foreach ($columns as $column) {
            $dayTypes[] = $column['type'] === 'H' ? 'H' : ($column['type'] === 'W' ? 'W' : '');
            $headers[] = $column['header'];
        }

        $rows = [];
        foreach ($this->rbmRows as $row) {
            $dayMap = $this->dayMap($this->field($row, 'days'));
            $cells = [
                $this->field($row, 'Region (RBM)'),
                $this->field($row, 'RBM Name'),
                $this->field($row, 'RBM Employee Code'),
            ];
            foreach ($columns as $column) {
                if ($column['kind'] === 'total-tm') {
                    $cells[] = $this->field($row, 'Total TMs Qualified (≥₹15k POB)');
                    continue;
                }
                if ($column['kind'] === 'total-chemist') {
                    $cells[] = $this->field($row, 'Total Chemists Qualified (≥₹15k POB)');
                    continue;
                }
                $day = $dayMap[$column['date']] ?? $column['day'];
                $cells[] = $column['kind'] === 'holiday'
                    ? 'Holiday'
                    : $this->dayValue($day, $column['kind'] === 'chemist' ? 'chemistsQualified' : 'value');
            }
            $rows[] = [
                'cells' => $cells,
                'vacant' => $this->isVacantName($cells[0]) || $this->isVacantName($cells[1]),
            ];
        }

        return [
            'id' => 'region',
            'title' => 'Region Daily Winners – ' . $this->month,
            'dayTypes' => $dayTypes,
            'headers' => $headers,
            'rows' => $rows,
        ];
    }

    /**
     * @return array<string, mixed>
     */
    private function territorySection(): array
    {
        $dayTypes = ['', '', ''];
        $headers = ['Territory', 'TM Name', 'TM Employee Code'];
        foreach ($this->dayTemplate as $day) {
            $dayTypes[] = $day['type'] === 'H' ? 'H' : 'W';
            $headers[] = $this->dayHeader($day['label']);
        }
        $dayTypes[] = '';
        $headers[] = 'Total Chemists Qualified (≥₹15k POB)';

        $rows = [];
        foreach ($this->tmRows as $row) {
            $dayMap = $this->dayMap($this->field($row, 'days'));
            $cells = [
                $this->field($row, 'Territory'),
                $this->field($row, 'TM Name'),
                $this->field($row, 'TM Employee Code'),
            ];
            foreach ($this->dayTemplate as $day) {
                $cells[] = $this->dayValue($dayMap[$day['date']] ?? $day, 'value');
            }
            $cells[] = $this->field($row, 'Total Chemists Qualified (≥₹15k POB)');
            $rows[] = [
                'cells' => $cells,
                'vacant' => $this->isVacantName($cells[0]) || $this->isVacantName($cells[1]),
            ];
        }

        return [
            'id' => 'territory',
            'title' => 'Territory Daily Winners – ' . $this->month,
            'dayTypes' => $dayTypes,
            'headers' => $headers,
            'rows' => $rows,
        ];
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    private function regionColumns(): array
    {
        $columns = [];
        foreach ($this->dayTemplate as $day) {
            $header = $this->dayHeader($day['label']);
            if ($day['type'] === 'H') {
                $columns[] = [
                    'kind' => 'holiday',
                    'type' => 'H',
                    'date' => $day['date'],
                    'header' => $header,
                    'day' => $day,
                ];
                continue;
            }
            $columns[] = [
                'kind' => 'tm',
                'type' => 'W',
                'date' => $day['date'],
                'header' => $header,
                'day' => $day,
            ];
            $columns[] = [
                'kind' => 'chemist',
                'type' => 'W',
                'date' => $day['date'],
                'header' => $header,
                'day' => $day,
            ];
        }
        $columns[] = ['kind' => 'total-tm', 'type' => '', 'header' => 'Total TMs Qualified (≥₹15k POB)'];
        $columns[] = ['kind' => 'total-chemist', 'type' => '', 'header' => 'Total Chemists Qualified (≥₹15k POB)'];

        return $columns;
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    private function dayTemplate(): array
    {
        foreach ([$this->abmRows, $this->rbmRows, $this->tmRows] as $rows) {
            if ($rows === []) {
                continue;
            }
            $days = $this->orderedDays($this->field($rows[0], 'days'));
            if ($days !== []) {
                return $days;
            }
        }

        return [];
    }

    /**
     * @param mixed $days
     * @return array<int, array<string, mixed>>
     */
    private function orderedDays($days): array
    {
        if (is_object($days)) {
            $days = (array) $days;
        }
        if (!is_array($days)) {
            return [];
        }

        $list = [];
        foreach ($days as $label => $day) {
            if (is_object($day)) {
                $day = (array) $day;
            }
            if (!is_array($day)) {
                continue;
            }
            $list[] = [
                'label' => (string) $label,
                'date' => (string) ($day['date'] ?? ''),
                'type' => (string) ($day['type'] ?? 'W'),
                'value' => $day['value'] ?? 0,
                'chemistsQualified' => $day['chemistsQualified'] ?? 0,
            ];
        }
        usort($list, function ($a, $b) {
            return strcmp($a['date'], $b['date']);
        });

        return $list;
    }

    /**
     * @param mixed $days
     * @return array<string, array<string, mixed>>
     */
    private function dayMap($days): array
    {
        $map = [];
        foreach ($this->orderedDays($days) as $day) {
            if ($day['date'] !== '') {
                $map[$day['date']] = $day;
            }
        }

        return $map;
    }

    /**
     * @param array<string, mixed> $day
     * @return mixed
     */
    private function dayValue(array $day, string $field)
    {
        if (($day['type'] ?? '') === 'H' || ($day[$field] ?? null) === 'Holiday') {
            return 'Holiday';
        }
        $value = $day[$field] ?? 0;

        return is_numeric($value) ? 0 + $value : $value;
    }

    private function dayHeader(string $label): string
    {
        $parts = preg_split('/\s+/', trim($label), 2);
        if (is_array($parts) && count($parts) === 2) {
            return $parts[0] . "\n" . $parts[1];
        }

        return $label;
    }

    /**
     * @param mixed $data
     * @return array<int, mixed>
     */
    private function listRows($data, string $key): array
    {
        $rows = $this->field($data, $key);
        if (is_object($rows)) {
            $rows = (array) $rows;
        }

        return is_array($rows) ? array_values($rows) : [];
    }

    /**
     * @param mixed $row
     * @return mixed
     */
    private function field($row, string $key)
    {
        if (is_array($row)) {
            return $row[$key] ?? '';
        }
        if (is_object($row) && isset($row->{$key})) {
            return $row->{$key};
        }

        return '';
    }

    private function isVacantName($name): bool
    {
        return stripos(trim((string) $name), 'vacant') !== false;
    }

    /**
     * @param array<int, mixed> $values
     */
    private function writeValues($sheet, int $row, array $values, int $startColumn = 1): void
    {
        foreach ($values as $offset => $value) {
            $sheet->setCellValue($this->col($startColumn + $offset) . $row, $value);
        }
    }

    private function paintDayTypes($sheet, int $row, int $startColumn, bool $singleColumn): void
    {
        foreach ($this->dayTemplate as $index => $day) {
            $this->paintCell($sheet, $this->col($startColumn + $index) . $row, $day['type'] === 'H' ? self::GREY : self::GREEN);
        }
        if (!$singleColumn) {
            return;
        }
    }

    private function paintDayHeaders($sheet, int $row, int $startColumn, bool $singleColumn): void
    {
        foreach ($this->dayTemplate as $index => $day) {
            $cell = $this->col($startColumn + $index) . $row;
            $this->paintCell($sheet, $cell, $day['type'] === 'H' ? self::GREY : self::GREEN);
            $sheet->getStyle($cell)->getFont()->setBold(true)->setSize(11)->getColor()->setARGB('000000');
        }
        if ($singleColumn) {
            return;
        }
    }

    private function paintDataDays($sheet, int $row, int $startColumn, bool $singleColumn): void
    {
        foreach ($this->dayTemplate as $index => $day) {
            if ($day['type'] === 'H') {
                $this->paintCell($sheet, $this->col($startColumn + $index) . $row, self::GREY);
            }
        }
        if ($singleColumn) {
            return;
        }
    }

    /**
     * @param array<int, array<string, mixed>> $columns
     */
    private function paintRegionDayMarks($sheet, array $columns): void
    {
        foreach ($columns as $index => $column) {
            if ($column['kind'] === 'total-tm' || $column['kind'] === 'total-chemist') {
                continue;
            }
            $cell = $this->col(4 + $index);
            $color = $column['type'] === 'H' ? self::GREY : self::GREEN;
            $this->paintCell($sheet, $cell . '3', $color);
            $this->paintCell($sheet, $cell . '4', $color);
            $sheet->getStyle($cell . '4')->getFont()->setBold(true)->setSize(11)->getColor()->setARGB('000000');
        }
    }

    /**
     * @param array<int, array<string, mixed>> $columns
     */
    private function paintRegionDataDays($sheet, int $row, array $columns): void
    {
        foreach ($columns as $index => $column) {
            if (($column['type'] ?? '') === 'H') {
                $this->paintCell($sheet, $this->col(4 + $index) . $row, self::GREY);
            }
        }
    }

    private function paintRow($sheet, int $row, int $lastColumn, string $color): void
    {
        $sheet->getStyle('A' . $row . ':' . $this->col($lastColumn) . $row)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB($color);
    }

    private function paintCell($sheet, string $cell, string $color): void
    {
        $style = $sheet->getStyle($cell);
        $style->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB($color);
        $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER)->setVertical(Alignment::VERTICAL_CENTER);
    }

    private function styleHeaderRow($sheet, int $row, int $lastColumn, int $identityCount, int $summaryStart): void
    {
        $range = 'A' . $row . ':' . $this->col($lastColumn) . $row;
        $sheet->getStyle($range)->getAlignment()->setWrapText(true)->setVertical(Alignment::VERTICAL_CENTER)->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getRowDimension($row)->setRowHeight(36);
        if ($identityCount > 0) {
            $this->paintHeaderGroup($sheet, $row, 1, $identityCount, self::RED, 10);
        }
        if ($summaryStart > $identityCount && $summaryStart <= $lastColumn) {
            $this->paintHeaderGroup($sheet, $row, $summaryStart, $lastColumn, self::MAROON, 9);
        }
    }

    private function paintHeaderGroup($sheet, int $row, int $start, int $end, string $color, int $size): void
    {
        $range = $this->col($start) . $row . ':' . $this->col($end) . $row;
        $style = $sheet->getStyle($range);
        $style->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB($color);
        $style->getFont()->setName('Century Gothic')->setBold(true)->setSize($size)->getColor()->setARGB(self::WHITE);
        $style->getAlignment()->setWrapText(true)->setHorizontal(Alignment::HORIZONTAL_CENTER)->setVertical(Alignment::VERTICAL_CENTER);
    }

    private function finishSheet($sheet, int $headerRow, int $lastRow, int $lastColumn, int $dayStart, int $titleSize = 14): void
    {
        $sheet->getStyle('A1')->getFont()->setName('Century Gothic')->setBold(true)->setSize($titleSize)->getColor()->setARGB(self::MAROON);
        if ($lastRow > 1) {
            $sheet->getStyle('A2:' . $this->col($lastColumn) . $lastRow)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER)->setVertical(Alignment::VERTICAL_CENTER);
        }
        $sheet->getStyle('A' . $headerRow . ':' . $this->col($lastColumn) . $lastRow)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        for ($column = 1; $column <= $lastColumn; $column++) {
            $width = $column < $dayStart ? 22 : 14;
            $sheet->getColumnDimension($this->col($column))->setWidth($width);
        }
    }

    private function col(int $index): string
    {
        return Coordinate::stringFromColumnIndex($index);
    }
}