<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;
use App\Departments;
use App\Employees;

class PayrollHistoryReportController extends Controller
{
     protected string $module = 'Reports Management';

    public function __construct()
    {
        $module = $this->module;
        $this->middleware("permission:$module Payroll History")->only(['index']);
    }
    /* ────────────────────────────────────────────────
     |  Index  (filter form + table)
     ──────────────────────────────────────────────── */
    public function index(Request $request)
    {
        $fromDate     = $request->input('from_date',     Carbon::now()->startOfMonth()->toDateString());
        $toDate       = $request->input('to_date',       Carbon::now()->toDateString());
        $departmentId = $request->input('department_id', null);
        $employeeId   = $request->input('employee_id',   null);

        $departments        = Departments::where('status','active')->orderBy('name')->get();
        $employees          = Employees::where('status','active')->orderBy('first_name')->get();
        $selectedDepartment = $departmentId ? Departments::find($departmentId) : null;
        $selectedEmployee   = $employeeId   ? Employees::find($employeeId)     : null;

        $reportData = null;

        if ($request->has('from_date')) {
            $reportData = $this->buildReport($fromDate, $toDate, $departmentId, $employeeId);
        }

        return view('reports.payroll_history', compact(
            'fromDate','toDate','departmentId','employeeId',
            'departments','employees',
            'selectedDepartment','selectedEmployee',
            'reportData'
        ));
    }

    /* ────────────────────────────────────────────────
     |  Print  (standalone page)
     ──────────────────────────────────────────────── */
    public function print(Request $request)
    {
        $fromDate     = $request->input('from_date');
        $toDate       = $request->input('to_date');
        $departmentId = $request->input('department_id', null);
        $employeeId   = $request->input('employee_id',   null);

        $selectedDepartment = $departmentId ? Departments::find($departmentId) : null;
        $selectedEmployee   = $employeeId   ? Employees::find($employeeId)     : null;

        $reportData = $this->buildReport($fromDate, $toDate, $departmentId, $employeeId);

        return view('reports.payroll_history_print_view', compact(
            'fromDate','toDate',
            'selectedDepartment','selectedEmployee',
            'reportData'
        ));
    }

    /* ────────────────────────────────────────────────
     |  Core report builder
     ──────────────────────────────────────────────── */
    private function buildReport($fromDate, $toDate, $departmentId, $employeeId): array
    {
        $from = Carbon::parse($fromDate);
        $to   = Carbon::parse($toDate);

        /* Month pairs covered by the date range */
        $monthPairs = [];
        $cursor = $from->copy()->startOfMonth();
        while ($cursor->lte($to->copy()->endOfMonth())) {
            $monthPairs[] = ['year' => $cursor->year, 'month' => $cursor->month];
            $cursor->addMonth();
        }

        /* ── Payrolls ── */
        $query = DB::table('employee_payrolls as ep')
            ->join('employees as e',   'e.id', '=', 'ep.employee_id')
            ->join('departments as d', 'd.id', '=', 'e.department_id')
            ->select(
                'e.id as employee_id',
                DB::raw("CONCAT(e.first_name,' ',COALESCE(e.last_name,'')) as employee_name"),
                'e.employee_no',
                'e.basic_salary',
                'd.name as department_name',
                'ep.year','ep.month',
                'ep.gross_salary','ep.deductions','ep.net_salary',
                // New breakdown columns added by migration (COALESCE = safe if not yet run)
                DB::raw('COALESCE(ep.ot_hours,0)          as ot_hours'),
                DB::raw('COALESCE(ep.ot_amount,0)         as ot_amount'),
                DB::raw('COALESCE(ep.award,0)             as award'),
                DB::raw('COALESCE(ep.absent_days,0)       as absent_days'),
                DB::raw('COALESCE(ep.absent_amount,0)     as absent_amount'),
                DB::raw('COALESCE(ep.advance_deduction,0) as advance_deduction'),
                DB::raw('COALESCE(ep.loan_deduction,0)    as loan_deduction'),
                DB::raw('COALESCE(ep.other_deduction,0)   as other_deduction')
            )
            ->where(function ($q) use ($monthPairs) {
                foreach ($monthPairs as $pair) {
                    $q->orWhere(fn($i) =>
                        $i->where('ep.year',  $pair['year'])
                          ->where('ep.month', $pair['month'])
                    );
                }
            });

        if ($departmentId) $query->where('e.department_id', $departmentId);
        if ($employeeId)   $query->where('ep.employee_id',  $employeeId);

        $payrolls = $query
            ->orderBy('d.name')
            ->orderBy('e.first_name')
            ->orderBy('ep.year')
            ->orderBy('ep.month')
            ->get();

        /* ── Absent days from attendance table ── */
        $absentQ = DB::table('employee_attendances')
            ->select('employee_id', DB::raw('COUNT(*) as absent_days'))
            ->where('status','absent')
            ->whereBetween('date', [$fromDate, $toDate]);
        if ($employeeId) $absentQ->where('employee_id', $employeeId);
        $absentMap = $absentQ->groupBy('employee_id')->pluck('absent_days','employee_id');

        /* ── OT records for the period ── */
        $otMap = DB::table('employee_overtimes')
            ->select('employee_id','year','month','ot_hours','ot_amount')
            ->where(function ($q) use ($monthPairs) {
                foreach ($monthPairs as $pair) {
                    $q->orWhere(fn($i) =>
                        $i->where('year',  $pair['year'])
                          ->where('month', $pair['month'])
                    );
                }
            })
            ->get()
            ->groupBy(fn($r) => "{$r->employee_id}_{$r->year}_{$r->month}");

        /* ── Active loan deductions per employee ── */
        $loanMap = DB::table('employee_loans')
            ->select('employee_id', DB::raw('SUM(monthly_deduction) as total_ded'))
            ->where('status','active')
            ->groupBy('employee_id')
            ->pluck('total_ded','employee_id');

        /* ── Active advance deductions per employee ── */
        $advanceMap = DB::table('employee_advances')
            ->select('employee_id', DB::raw('SUM(monthly_deduction) as total_ded'))
            ->where('status','active')
            ->groupBy('employee_id')
            ->pluck('total_ded','employee_id');

        /* ── Build grouped output ── */
        $workingDays = 26;  // standard working days/month — adjust as needed

        $departments = [];

        // Grand totals
        $gGross=$gDed=$gNet=$gAbsDays=$gAbsAmt=$gOtHours=$gOtAmt=$gAward=$gOtherDed=$gAdvDed=$gLoanDed = 0;

        foreach ($payrolls as $p) {
            $deptName = $p->department_name;
            $empKey   = "{$p->employee_id}_{$p->year}_{$p->month}";

            /* Absent */
            $absentDays = $p->absent_days > 0
                ? (float)$p->absent_days
                : (float)($absentMap[$p->employee_id] ?? 0);

            $dailyRate  = $workingDays > 0 ? ($p->basic_salary / $workingDays) : 0;
            $absentAmt  = $p->absent_amount > 0
                ? (float)$p->absent_amount
                : round($dailyRate * $absentDays, 0);

            /* OT — prefer payroll stored value, fall back to employee_overtimes table */
            $otRecord = isset($otMap[$empKey]) ? $otMap[$empKey][0] : null;
            $otHours  = $p->ot_hours  > 0 ? (float)$p->ot_hours  : (float)($otRecord?->ot_hours  ?? 0);
            $otAmt    = $p->ot_amount > 0 ? (float)$p->ot_amount : (float)($otRecord?->ot_amount ?? 0);

            /* Deductions — prefer payroll stored value, fall back to loan/advance tables */
            $loanDed    = $p->loan_deduction    > 0 ? (float)$p->loan_deduction    : (float)($loanMap[$p->employee_id]    ?? 0);
            $advanceDed = $p->advance_deduction > 0 ? (float)$p->advance_deduction : (float)($advanceMap[$p->employee_id] ?? 0);
            $otherDed   = (float)$p->other_deduction;
            $award      = (float)$p->award;

            $row = [
                'employee_name' => trim($p->employee_name),
                'employee_no'   => $p->employee_no,
                'salary_type'   => 'M',
                'from'          => Carbon::createFromDate($p->year, $p->month, 1)->format('d/m/y'),
                'to'            => Carbon::createFromDate($p->year, $p->month, 1)->endOfMonth()->format('d/m/y'),
                'salary'        => (float)$p->gross_salary,
                'ot_hours'      => $otHours,
                'overtime'      => $otAmt,
                'award'         => $award,
                'absent_days'   => $absentDays,
                'absent_amount' => $absentAmt,
                'other_ded'     => $otherDed,
                'advance_ded'   => $advanceDed,
                'loan_ded'      => $loanDed,
                'net_payable'   => (float)$p->net_salary,
            ];

            if (!isset($departments[$deptName])) {
                $departments[$deptName] = [
                    'rows'             => [],
                    'subSalary'        => 0, 'subOtHours'     => 0, 'subOvertime'     => 0,
                    'subAward'         => 0, 'subAbsentDays'  => 0, 'subAbsentAmount' => 0,
                    'subOtherDed'      => 0, 'subAdvanceDed'  => 0, 'subLoanDed'      => 0,
                    'subNet'           => 0,
                ];
            }

            $d = &$departments[$deptName];
            $d['rows'][]          = $row;
            $d['subSalary']      += $row['salary'];
            $d['subOtHours']     += $otHours;
            $d['subOvertime']    += $otAmt;
            $d['subAward']       += $award;
            $d['subAbsentDays']  += $absentDays;
            $d['subAbsentAmount']+= $absentAmt;
            $d['subOtherDed']    += $otherDed;
            $d['subAdvanceDed']  += $advanceDed;
            $d['subLoanDed']     += $loanDed;
            $d['subNet']         += $row['net_payable'];

            $gGross    += $row['salary'];
            $gOtHours  += $otHours;
            $gOtAmt    += $otAmt;
            $gAward    += $award;
            $gAbsDays  += $absentDays;
            $gAbsAmt   += $absentAmt;
            $gOtherDed += $otherDed;
            $gAdvDed   += $advanceDed;
            $gLoanDed  += $loanDed;
            $gNet      += $row['net_payable'];
            $gDed      += (float)$p->deductions;
        }

        return [
            'departments'        => $departments,
            'grandGross'         => $gGross,
            'grandDeductions'    => $gDed,
            'grandNet'           => $gNet,
            'grandAbsentDays'    => $gAbsDays,
            'grandAbsentAmount'  => $gAbsAmt,
            'grandOtHours'       => $gOtHours,
            'grandOvertime'      => $gOtAmt,
            'grandAward'         => $gAward,
            'grandOtherDed'      => $gOtherDed,
            'grandAdvanceDed'    => $gAdvDed,
            'grandLoanDed'       => $gLoanDed,
        ];
    }
}