<?php

namespace App\Http\Controllers;

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

class PayrollController extends Controller
{
    protected string $module = 'Employee Payrolls';

    public function __construct()
    {
        $module = $this->module;
        $this->middleware("permission:$module list")->only(['index']);
    }

    public function index(Request $request)
    {
        $month = $request->get('month', Carbon::now()->month);
        $year  = $request->get('year',  Carbon::now()->year);

        $payrolls = EmployeePayroll::with('employee')
            ->where('month', $month)
            ->where('year',  $year)
            ->get();

        return view('employee_payrolls.index', compact('payrolls', 'month', 'year'));
    }

    /* ─────────────────────────────────────────────────────────
     |  Generate payroll — includes OT, Loan, Advance
     ───────────────────────────────────────────────────────── */
    public function generate(Request $request)
    {
        $request->validate([
            'month_year' => 'required|date_format:Y-m',
        ]);

        [$year, $month] = explode('-', $request->month_year);
        $year  = (int) $year;
        $month = (int) $month;

        $daysInMonth = Carbon::create($year, $month, 1)->daysInMonth;
        $workingDays = 26;  // adjust if needed
        $dailyHours  = 8;

        // Use raw DB query to avoid any Eloquent global scopes
        $employees = DB::table('employees')
            ->where('status', 'active')
            ->orderBy('id')
            ->get();

        DB::transaction(function () use ($employees, $month, $year, $daysInMonth, $workingDays, $dailyHours) {

            foreach ($employees as $employee) {

                // Skip if already generated for this month
                $exists = DB::table('employee_payrolls')
                    ->where('employee_id', $employee->id)
                    ->where('month', $month)
                    ->where('year',  $year)
                    ->exists();
                if ($exists) continue;

                /* ── Attendance counts ── */
                $startDate = Carbon::create($year, $month, 1)->startOfMonth()->toDateString();
                $endDate   = Carbon::create($year, $month, 1)->endOfMonth()->toDateString();

                $attendances = DB::table('employee_attendances')
                    ->where('employee_id', $employee->id)
                    ->whereBetween('date', [$startDate, $endDate])
                    ->orderBy('id')
                    ->get();

                $absentDays = $attendances->where('status', 'absent')->count();
                $lateDays   = $attendances->where('status', 'late')->count();
                $halfDays   = $attendances->where('status', 'half-day')->count();

                /* ── Rates ── */
                $basicSalary = (float) $employee->basic_salary;
                $dailySalary = $basicSalary / $daysInMonth;
                $hourlyRate  = $basicSalary / ($workingDays * $dailyHours);

                /* ── Attendance deductions ── */
                $absentAmount = round($absentDays * $dailySalary, 2);
                $lateAmount   = round($lateDays   * ($dailySalary * 0.5), 2);
                $halfAmount   = round($halfDays   * ($dailySalary * 0.5), 2);
                $totalAttendanceDed = $absentAmount + $lateAmount + $halfAmount;

                /* ── OT ── */
                $otRecord = DB::table('employee_overtimes')
                    ->where('employee_id', $employee->id)
                    ->where('year',  $year)
                    ->where('month', $month)
                    ->first();

                $otHours  = $otRecord ? (float) $otRecord->ot_hours  : 0;
                $otAmount = $otRecord ? (float) $otRecord->ot_amount  : 0;
                if ($otHours > 0 && $otAmount == 0) {
                    $multiplier = $otRecord ? (float)($otRecord->ot_multiplier ?? 1.5) : 1.5;
                    $otAmount   = round($hourlyRate * $otHours * $multiplier, 2);
                }

                /* ── Loan deductions ── */
                $loanDeduction = (float) DB::table('employee_loans')
                    ->where('employee_id', $employee->id)
                    ->where('status', 'active')
                    ->sum('monthly_deduction');

                /* ── Advance deductions ── */
                $advanceDeduction = (float) DB::table('employee_advances')
                    ->where('employee_id', $employee->id)
                    ->where('status', 'active')
                    ->sum('monthly_deduction');

                /* ── Final figures ── */
                $totalDeductions = $totalAttendanceDed + $loanDeduction + $advanceDeduction;
                $grossSalary     = $basicSalary + $otAmount;
                $netSalary       = $grossSalary - $totalDeductions;

                /* ── Insert payroll row ── */
                DB::table('employee_payrolls')->insert([
                    'employee_id'       => $employee->id,
                    'year'              => $year,
                    'month'             => $month,
                    'gross_salary'      => $grossSalary,
                    'deductions'        => $totalDeductions,
                    'net_salary'        => $netSalary,
                    'status'            => 'unpaid',
                    'ot_hours'          => $otHours,
                    'ot_amount'         => $otAmount,
                    'absent_days'       => $absentDays,
                    'absent_amount'     => $totalAttendanceDed,
                    'loan_deduction'    => $loanDeduction,
                    'advance_deduction' => $advanceDeduction,
                    'other_deduction'   => 0,
                    'award'             => 0,
                    'paid_at'           => null,
                    'created_at'        => now(),
                    'updated_at'        => now(),
                ]);

                /* ── Update each loan's paid_amount ── */
                $loans = DB::table('employee_loans')
                    ->where('employee_id', $employee->id)
                    ->where('status', 'active')
                    ->orderBy('id')
                    ->get();

                foreach ($loans as $loan) {
                    $newPaid   = (float)$loan->paid_amount + (float)$loan->monthly_deduction;
                    $newStatus = $newPaid >= (float)$loan->total_amount ? 'completed' : 'active';
                    DB::table('employee_loans')
                        ->where('id', $loan->id)
                        ->update([
                            'paid_amount' => min($newPaid, (float)$loan->total_amount),
                            'status'      => $newStatus,
                            'updated_at'  => now(),
                        ]);
                }

                /* ── Update each advance's recovered amount ── */
                $advances = DB::table('employee_advances')
                    ->where('employee_id', $employee->id)
                    ->where('status', 'active')
                    ->orderBy('id')
                    ->get();

                foreach ($advances as $adv) {
                    $newRecovered = (float)$adv->recovered + (float)$adv->monthly_deduction;
                    $newStatus    = $newRecovered >= (float)$adv->amount ? 'recovered' : 'active';
                    DB::table('employee_advances')
                        ->where('id', $adv->id)
                        ->update([
                            'recovered'  => min($newRecovered, (float)$adv->amount),
                            'status'     => $newStatus,
                            'updated_at' => now(),
                        ]);
                }
            }
        });

        return redirect()->back()->with(
            'success',
            'Payroll generated for ' . Carbon::create($year, $month, 1)->format('F, Y')
        );
    }

    /* ─────────────────────────────────────────────────────────
     |  Pay single
     ───────────────────────────────────────────────────────── */
    public function pay(Request $request, $id)
    {
        $payroll = EmployeePayroll::findOrFail($id);

        if ($payroll->status === 'paid') {
            return redirect()->back()->with('error', 'Payroll already paid.');
        }

        $payroll->update([
            'status'  => 'paid',
            'paid_at' => now(),
        ]);

        return redirect()->back()->with('success', 'Payroll paid successfully.');
    }

    /* ─────────────────────────────────────────────────────────
     |  Pay all unpaid for a month
     ───────────────────────────────────────────────────────── */
    public function payAll(Request $request)
    {
        $request->validate([
            'month_year' => 'required|date_format:Y-m',
        ]);

        [$year, $month] = explode('-', $request->month_year);

        $payrolls = EmployeePayroll::where('year',   (int) $year)
            ->where('month',  (int) $month)
            ->where('status', 'unpaid')
            ->get();

        foreach ($payrolls as $payroll) {
            $payroll->update([
                'status'  => 'paid',
                'paid_at' => now(),
            ]);
        }

        return redirect()->back()->with(
            'success',
            'All payrolls for ' . Carbon::create((int)$year, (int)$month, 1)->format('F, Y') . ' have been paid.'
        );
    }
}