<?php

namespace App\Http\Controllers;

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

class LoanAdvanceReportController extends Controller
{

    protected string $module = 'Reports Management';

    public function __construct()
    {
        $module = $this->module;
        $this->middleware("permission:$module Loan Advance")->only(['index']);
    }
    /* ────────────────────────────────────────────────
     |  Index
     ──────────────────────────────────────────────── */
    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        = DB::table('departments')->where('status', 'active')->orderBy('name')->get();
        $employees          = DB::table('employees')->where('status', 'active')->orderBy('first_name')->get();
        $selectedDepartment = $departmentId ? DB::table('departments')->where('id', $departmentId)->first() : null;
        $selectedEmployee   = $employeeId   ? DB::table('employees')->where('id', $employeeId)->first()    : null;

        $reportData = null;

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

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

    /* ────────────────────────────────────────────────
     |  Print
     ──────────────────────────────────────────────── */
    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 ? DB::table('departments')->where('id', $departmentId)->first() : null;
        $selectedEmployee   = $employeeId   ? DB::table('employees')->where('id', $employeeId)->first()    : null;

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

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

    /* ────────────────────────────────────────────────
     |  Core builder
     ──────────────────────────────────────────────── */
    private function buildReport($fromDate, $toDate, $departmentId, $employeeId): array
    {
        /*
         * LOANS
         * ─────
         * Opening  = total_amount of loans that started BEFORE from_date
         * Payments = sum of monthly_deduction recovered WITHIN the date range
         *            We approximate: paid_amount prorated to the period.
         *            Since we store cumulative paid_amount, we use payroll
         *            loan_deduction for the period instead (more accurate).
         * Deductions = sum of loan_deduction saved in employee_payrolls for the period
         * Balance  = total_amount - paid_amount  (current outstanding)
         */

        /* Step 1 — get all employees matching filters */
        $empQuery = DB::table('employees as e')
            ->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',
                'd.name as department_name',
                'd.id   as department_id'
            )
            ->where('e.status', 'active')
            ->orderBy('d.name')
            ->orderBy('e.first_name');

        if ($departmentId) $empQuery->where('e.department_id', $departmentId);
        if ($employeeId)   $empQuery->where('e.id', $employeeId);

        $empList = $empQuery->get();

        if ($empList->isEmpty()) {
            return ['departments' => [], 'grand' => $this->emptyTotals()];
        }

        $empIds = $empList->pluck('employee_id')->toArray();

        /* Step 2 — designation lookup */
        $desigMap = DB::table('employees as e')
            ->leftJoin('designations as des', 'des.id', '=', 'e.designation_id')
            ->whereIn('e.id', $empIds)
            ->pluck('des.title', 'e.id');

        /* Step 3 — LOAN opening: total_amount of active/completed loans created before from_date */
        $loanOpening = DB::table('employee_loans')
            ->whereIn('employee_id', $empIds)
            ->where('start_date', '<', $fromDate)
            ->whereIn('status', ['active', 'completed'])
            ->selectRaw('employee_id, SUM(total_amount) as opening')
            ->groupBy('employee_id')
            ->pluck('opening', 'employee_id');

        /* Step 4 — LOAN deductions within period (from payroll breakdown column) */
        $loanPeriodDed = DB::table('employee_payrolls')
            ->whereIn('employee_id', $empIds)
            ->where(function ($q) use ($fromDate, $toDate) {
                // convert from_date/to_date to year/month ranges
                $from = Carbon::parse($fromDate);
                $to   = Carbon::parse($toDate);
                $cursor = $from->copy()->startOfMonth();
                while ($cursor->lte($to->copy()->endOfMonth())) {
                    $y = $cursor->year;
                    $m = $cursor->month;
                    $q->orWhere(fn($i) => $i->where('year', $y)->where('month', $m));
                    $cursor->addMonth();
                }
            })
            ->selectRaw('employee_id, SUM(COALESCE(loan_deduction,0)) as total_ded')
            ->groupBy('employee_id')
            ->pluck('total_ded', 'employee_id');

        /* Step 5 — LOAN current balance (total_amount - paid_amount) */
        $loanBalance = DB::table('employee_loans')
            ->whereIn('employee_id', $empIds)
            ->whereIn('status', ['active', 'completed'])
            ->selectRaw('employee_id, SUM(total_amount - paid_amount) as balance')
            ->groupBy('employee_id')
            ->pluck('balance', 'employee_id');


        /* Step 6 — ADVANCE opening */
        $advOpening = DB::table('employee_advances')
            ->whereIn('employee_id', $empIds)
            ->where('given_date', '<', $fromDate)
            ->whereIn('status', ['active', 'recovered'])
            ->selectRaw('employee_id, SUM(amount) as opening')
            ->groupBy('employee_id')

            ->pluck('opening', 'employee_id');

        /* Step 7 — ADVANCE deductions within period */
        $advPeriodDed = DB::table('employee_payrolls')
            ->whereIn('employee_id', $empIds)
            ->where(function ($q) use ($fromDate, $toDate) {
                $from = Carbon::parse($fromDate);
                $to   = Carbon::parse($toDate);
                $cursor = $from->copy()->startOfMonth();
                while ($cursor->lte($to->copy()->endOfMonth())) {
                    $y = $cursor->year;
                    $m = $cursor->month;
                    $q->orWhere(fn($i) => $i->where('year', $y)->where('month', $m));
                    $cursor->addMonth();
                }
            })
            ->selectRaw('employee_id, SUM(COALESCE(advance_deduction,0)) as total_ded')
            ->groupBy('employee_id')
            ->pluck('total_ded', 'employee_id');

        /* Step 8 — ADVANCE current balance */
        $advBalance = DB::table('employee_advances')
            ->whereIn('employee_id', $empIds)
            ->whereIn('status', ['active', 'recovered'])
            ->selectRaw('employee_id, SUM(amount - recovered) as balance')
            ->groupBy('employee_id')
            ->pluck('balance', 'employee_id');

        /* Step 9 — Group into departments */
        $departments = [];
        $grand = $this->emptyTotals();

        foreach ($empList as $emp) {
            $eid      = $emp->employee_id;
            $deptName = $emp->department_name;

            // Combine loan + advance figures
            $opening    = (float)($loanOpening[$eid]   ?? 0) + (float)($advOpening[$eid]   ?? 0);
            $payments   = (float)($loanPeriodDed[$eid] ?? 0) + (float)($advPeriodDed[$eid] ?? 0);
            $deductions = $payments; // deductions = what was recovered from salary in the period
            $balance    = (float)($loanBalance[$eid]   ?? 0) + (float)($advBalance[$eid]   ?? 0);

            // Skip employees with no loan/advance activity at all
            if ($opening == 0 && $payments == 0 && $balance == 0) continue;

            $row = [
                'employee_name'   => trim($emp->employee_name),
                'designation'     => $desigMap[$eid] ?? '—',
                'card_no'         => $emp->employee_no,
                'opening'         => $opening,
                'payments'        => $payments,
                'deductions'      => $deductions,
                'balance'         => $balance,
            ];

            if (!isset($departments[$deptName])) {
                $departments[$deptName] = ['rows' => [], 'totals' => $this->emptyTotals()];
            }

            $departments[$deptName]['rows'][] = $row;

            $departments[$deptName]['totals']['opening']    += $opening;
            $departments[$deptName]['totals']['payments']   += $payments;
            $departments[$deptName]['totals']['deductions'] += $deductions;
            $departments[$deptName]['totals']['balance']    += $balance;

            $grand['opening']    += $opening;
            $grand['payments']   += $payments;
            $grand['deductions'] += $deductions;
            $grand['balance']    += $balance;
        }

        return compact('departments', 'grand');
    }

    private function emptyTotals(): array
    {
        return ['opening' => 0, 'payments' => 0, 'deductions' => 0, 'balance' => 0];
    }
}
