<?php

namespace App\Exports;

use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use App\Models\HRMS\Salary;
use App\Models\HRMS\EmployeeReimbursement;
use Carbon\Carbon;

class PayrollReport implements FromCollection, WithHeadings, WithStyles
{
    private $admin_id, $department_id, $branch_id, $start_date, $end_date;

    public function __construct($admin_id)
    {
        $this->admin_id = $admin_id;
    }
    
    public function collection()
    {
        $this->department_id = isset($_GET["department_id"]) ? $_GET["department_id"] : null;
        $this->branch_id = isset($_GET["branch_id"]) ? $_GET["branch_id"] : null;
        $this->start_date = isset($_GET["date_from"]) ? $_GET["date_from"] : null;
        $this->end_date = isset($_GET["date_to"]) ? $_GET["date_to"] : null;

        // Salary totals query
        $salaryQuery = Salary::where("admin_id", $this->admin_id)
            ->where("status", "paid");

        // Reimbursement totals query
        $reimbursementQuery = EmployeeReimbursement::where("admin_id", $this->admin_id)
            ->where("status", "approved");

        // Apply filters if present
        if ($this->department_id) {
            $salaryQuery->where('department_id', $this->department_id);
    
        }
        if ($this->branch_id) {
            $salaryQuery->where('branch_id', $this->branch_id);
            
        }
        if ($this->start_date && $this->end_date) {
            $salaryQuery->whereDate('created_at', '>=', $this->start_date)
                       ->whereDate('created_at', '<=', $this->end_date);
            
            $fromMonth = Carbon::parse($this->start_date)->format('Y-m');
            $toMonth = Carbon::parse($this->end_date)->format('Y-m');
            $reimbursementQuery->where('for_month', '>=', $fromMonth)
                             ->where('for_month', '<=', $toMonth);
        }

        // Get salary totals for all matching records
        $salaryTotals = $salaryQuery->get(); // Get the summed totals

        // Get reimbursement total
        $reimbursementTotal = $reimbursementQuery->sum('amount') ?? 0;

        // Prepare data in row format
        $data = [
            ['label' => 'Gross Pay', 'value' => $salaryTotals->sum("gross_salary") ?? 0],
            ['label' => 'Net Salary', 'value' => $salaryTotals->sum("net_salary") ?? 0],
            ['label' => 'Total Pay', 'value' => $salaryTotals->sum("final_salary") ?? 0],
            ['label' => 'Tax', 'value' => $salaryTotals->sum("tax") ?? 0],
            ['label' => 'Reimbursement', 'value' => $reimbursementTotal]
        ];

        return collect($data);
    }

    public function headings(): array
    {
        return [
            "Component",
            "Amount"
        ];
    }

    public function styles(Worksheet $sheet)
    {
        return [
            // Header row style
            1 => [
                'font' => ['bold' => true],
                'fill' => [
                    'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                    'startColor' => ['argb' => 'FFD9D9D9']
                ]
            ],
            // Number formatting for amounts
            'B' => ['numberFormat' => ['formatCode' => '#,##0.00']],
            // Bold labels
            'A2:A6' => ['font' => ['bold' => true]]
        ];
    }
}