<?php

namespace App\Exports;

use App\Models\NeracaAwal;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Events\AfterSheet;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class NeracaAwalExport implements FromCollection, WithEvents, WithMapping, WithColumnFormatting, WithHeadings
{
    protected $years;
    protected $rowNumber = 0;
    protected $debetTotal = 0;
    protected $kreditTotal = 0;

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

    public function collection()
    {
        return NeracaAwal::select(
            'neraca_awal.*',
            'master_coa.nama_akun'
        )
            ->leftJoin('master_coa', 'master_coa.no_akun', '=', 'neraca_awal.no_akun')
            ->where('neraca_awal.tahun', $this->years)
            ->orderBy('neraca_awal.no_akun', 'asc')
            ->get();
    }

    public function columnFormats(): array
    {
        return [
            'D' => NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1,
            'E' => NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1,
        ];
    }

    public function map($row): array
    {
        $this->rowNumber++;
        $debet = is_numeric($row->debet) ? $row->debet : 0;
        $kredit = is_numeric($row->kredit) ? $row->kredit : 0;

        $this->debetTotal += $debet;
        $this->kreditTotal += $kredit;

        return [
            $this->rowNumber,
            $row->no_akun,
            $row->nama_akun,
            $debet,
            $kredit
        ];
    }

    public function headings(): array
    {
        return [
            'No',
            'Nomor Akun',
            'Nama Akun',
            'Debet',
            'Kredit'
        ];
    }

    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function (AfterSheet $event) {
                $sheet = $event->sheet->getDelegate();

                // === 1. Judul ===
                $sheet->insertNewRowBefore(1, 3);
                $sheet->setCellValue('A1', 'PT. Berkah Semangat Sentosa');
                $sheet->setCellValue('A2', 'Neraca Awal Tahun ' . $this->years);
                $sheet->mergeCells('A1:E1');
                $sheet->mergeCells('A2:E2');
                $sheet->getStyle('A1:E2')->getAlignment()->setHorizontal('center');
                $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);

                // === 2. Header Baris ke-4 ===
                $sheet->setCellValue('A4', 'No');
                $sheet->setCellValue('B4', 'Nomor Akun');
                $sheet->setCellValue('C4', 'Nama Akun');
                $sheet->setCellValue('D4', 'Debet');
                $sheet->setCellValue('E4', 'Kredit');

                // === 3. Styling Header ===
                $headerStyle = [
                    'font' => ['bold' => true],
                    'alignment' => [
                        'horizontal' => 'center',
                        'vertical' => 'center',
                    ],
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                        ],
                    ],
                ];
                $sheet->getStyle('A4:E4')->applyFromArray($headerStyle);

                // === 4. Data Rows ===
                $startRow = 5;
                $endRow = $startRow + $this->rowNumber - 1;

                // Format number columns
                $sheet->getStyle("D{$startRow}:E{$endRow}")
                    ->getNumberFormat()
                    ->setFormatCode('#,##0');

                // Apply borders
                $sheet->getStyle("A{$startRow}:E{$endRow}")->applyFromArray([
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                        ],
                    ],
                ]);

                // === 5. Total Row ===
                $totalRow = $endRow + 1;
                $sheet->setCellValue("A{$totalRow}", 'TOTAL');
                $sheet->mergeCells("A{$totalRow}:C{$totalRow}");

                // Use calculated totals instead of formula
                $sheet->setCellValue("D{$totalRow}", $this->debetTotal);
                $sheet->setCellValue("E{$totalRow}", $this->kreditTotal);

                // Format total row
                $totalStyle = [
                    'font' => ['bold' => true],
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                        ],
                    ],
                ];
                $sheet->getStyle("A{$totalRow}:E{$totalRow}")->applyFromArray($totalStyle);
                $sheet->getStyle("D{$totalRow}:E{$totalRow}")
                    ->getNumberFormat()
                    ->setFormatCode('#,##0');
            }
        ];
    }
}
