<?php

namespace App\Modules\HR\Payroll\Http\Controllers;

use App\Http\Controllers\Controller;
use App\Modules\HR\Payroll\Models\PayrollAdjustment;
use App\Modules\HR\Payroll\Models\PayrollAdjustmentDetail;
use App\Modules\HR\Payroll\Models\PayrollAdjustmentAddition;
use App\Modules\HR\Payroll\Models\PayrollAdjustmentDeduction;
use App\Modules\HR\Payroll\Models\PayrollGroup;
use App\Modules\HR\Payroll\Models\Addition;
use App\Modules\HR\Payroll\Models\Deduction;
use App\Modules\HR\Core\Models\Employee;
use App\Modules\PLATFORM\Settings\Models\Branch;
use Illuminate\Http\Request;
use Yajra\DataTables\Facades\DataTables;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\IOFactory;

class PayrollAdjustmentController extends Controller
{
    public function index(Request $request)
    {
        if ($request->ajax()) {
            $query = PayrollAdjustment::with('payrollGroup')->orderBy('created_at', 'desc');

            return DataTables::of($query)
                ->addIndexColumn()
                ->editColumn('payroll_group_id', function ($row) {
                    return $row->payrollGroup->name ?? '-';
                })
                ->editColumn('date_from', function ($row) {
                    return $row->date_from->format('Y-m-d');
                })
                ->editColumn('date_to', function ($row) {
                    return $row->date_to->format('Y-m-d');
                })
                ->editColumn('status', function ($row) {
                    $badges = [
                        'pending' => '<span class="badge bg-warning">Pending</span>',
                        'processed' => '<span class="badge bg-success">Processed</span>',
                        'cancelled' => '<span class="badge bg-danger">Cancelled</span>',
                    ];
                    return $badges[$row->status] ?? '-';
                })
                ->addColumn('action', function ($row) {
                    return view('payroll::payroll-adjustments/partials/actions', compact('row'))->render();
                })
                ->rawColumns(['status', 'action'])
                ->make(true);
        }

        return view('payroll::payroll-adjustments/index');
    }

    public function create()
    {
        $branches = Branch::where('status', true)->get();
        $payrollGroups = PayrollGroup::where('status', true)->get();
        return view('payroll::payroll-adjustments/create', compact('payrollGroups', 'branches'));
    }

    public function store(Request $request)
    {
        $validated = $request->validate([
            'branch_id' => 'required|exists:branches,id',
            'payroll_group_id' => 'required|exists:payroll_groups,id',
            'date_from' => 'required|date',
            'date_to' => 'required|date|after_or_equal:date_from',
            'remarks' => 'nullable|string',
        ]);

        $validated['status'] = 'pending';

        PayrollAdjustment::create($validated);

        return response()->json([
            'status' => true,
            'message' => 'Payroll adjustment created successfully',
        ]);
    }

    public function edit(PayrollAdjustment $payrollAdjustment)
    {
        // Get all employees in this payroll group with formatted display text
        $employees = Employee::where('payroll_group_id', $payrollAdjustment->payroll_group_id)
            ->where('status', 'active')
            ->select('id', 'employee_code', 'epf_no', 'first_name', 'middle_name', 'last_name')
            ->orderBy('employee_code')
            ->get()
            ->map(function ($emp) {
                // Format: "employee_code, epf_no - Name" if code != epf_no, else "employee_code - Name"
                $name = trim($emp->first_name . ' ' . $emp->middle_name . ' ' . $emp->last_name);
                if ($emp->employee_code !== $emp->epf_no && !empty($emp->epf_no)) {
                    $emp->display_text = $emp->employee_code . ', ' . $emp->epf_no . ' - ' . $name;
                } else {
                    $emp->display_text = $emp->employee_code . ' - ' . $name;
                }
                // For search: include code, epf_no, and name
                $emp->search_text = $emp->employee_code . ' ' . $emp->epf_no . ' ' . $name;
                return $emp;
            });

        // Get all additions and deductions
        $additions = Addition::where('status', true)->get();
        $deductions = Deduction::where('status', true)->get();
        $branches = Branch::where('status', true)->get();

        return view('payroll::payroll-adjustments/edit', compact(
            'payrollAdjustment',
            'employees',
            'additions',
            'deductions',
            'branches'
        ));
    }

    public function update(Request $request, PayrollAdjustment $payrollAdjustment)
    {
        $validated = $request->validate([
            'branch_id' => 'required|exists:branches,id',
            'payroll_group_id' => 'required|exists:payroll_groups,id',
            'date_from' => 'required|date',
            'date_to' => 'required|date|after_or_equal:date_from',
            'remarks' => 'nullable|string',
        ]);

        $payrollAdjustment->update($validated);

        return response()->json([
            'status' => true,
            'message' => 'Payroll adjustment updated successfully',
        ]);
    }

    public function destroy(PayrollAdjustment $payrollAdjustment)
    {
        // Can only delete pending adjustments
        if ($payrollAdjustment->status !== 'pending') {
            return response()->json([
                'status' => false,
                'message' => 'Cannot delete processed or cancelled adjustments',
            ], 400);
        }

        $payrollAdjustment->delete();

        return response()->json([
            'status' => true,
            'message' => 'Payroll adjustment deleted successfully',
        ]);
    }

    public function getEmployeeAdjustments(Request $request, PayrollAdjustment $payrollAdjustment)
    {
        $employeeId = $request->input('employee_id');

        // Get existing additions for this employee
        $additions = PayrollAdjustmentAddition::where('payroll_adjustment_id', $payrollAdjustment->id)
            ->where('employee_id', $employeeId)
            ->with(['addition'])
            ->get();

        // Get existing deductions for this employee
        $deductions = PayrollAdjustmentDeduction::where('payroll_adjustment_id', $payrollAdjustment->id)
            ->where('employee_id', $employeeId)
            ->with(['deduction'])
            ->get();

        return response()->json([
            'status' => true,
            'additions' => $additions,
            'deductions' => $deductions,
        ]);
    }

    public function saveAdjustments(Request $request, PayrollAdjustment $payrollAdjustment)
    {
        $validated = $request->validate([
            'adjustments' => 'required|array',
            'adjustments.*.employee_id' => 'required|exists:employees,id',
            'adjustments.*.type' => 'required|in:addition,deduction',
            'adjustments.*.item_id' => 'required',
            'adjustments.*.amount' => 'required|numeric|min:0',
            'adjustments.*.remarks' => 'nullable|string',
        ]);

        DB::beginTransaction();
        try {
            // Collect unique employee IDs from this request
            $employeeIds = array_unique(array_column($request->adjustments, 'employee_id'));

            // Delete existing records only for the employees being saved
            PayrollAdjustmentAddition::where('payroll_adjustment_id', $payrollAdjustment->id)
                ->whereIn('employee_id', $employeeIds)
                ->delete();
            PayrollAdjustmentDeduction::where('payroll_adjustment_id', $payrollAdjustment->id)
                ->whereIn('employee_id', $employeeIds)
                ->delete();

            // Create new additions and deductions
            foreach ($request->adjustments as $adjustment) {
                $data = [
                    'payroll_adjustment_id' => $payrollAdjustment->id,
                    'employee_id' => $adjustment['employee_id'],
                    'amount' => $adjustment['amount'],
                    'remarks' => $adjustment['remarks'] ?? null,
                ];

                if ($adjustment['type'] === 'addition') {
                    $data['addition_id'] = $adjustment['item_id'];
                    PayrollAdjustmentAddition::create($data);
                } else {
                    $data['deduction_id'] = $adjustment['item_id'];
                    PayrollAdjustmentDeduction::create($data);
                }
            }

            // Update payroll_adjustment_details only for the affected employees
            PayrollAdjustmentDetail::where('payroll_adjustment_id', $payrollAdjustment->id)
                ->whereIn('employee_id', $employeeIds)
                ->delete();

            foreach ($employeeIds as $employeeId) {
                PayrollAdjustmentDetail::create([
                    'payroll_adjustment_id' => $payrollAdjustment->id,
                    'employee_id' => $employeeId,
                ]);
            }

            DB::commit();

            return response()->json([
                'status' => true,
                'message' => 'Adjustments saved successfully',
            ]);
        } catch (\Exception $e) {
            DB::rollBack();
            return response()->json([
                'status' => false,
                'message' => 'Error saving adjustments: ' . $e->getMessage(),
            ], 500);
        }
    }

    public function downloadTemplate(PayrollAdjustment $payrollAdjustment)
    {
        $headers = [
            'Content-Type' => 'text/csv',
            'Content-Disposition' => 'attachment; filename="payroll_adjustment_template.csv"',
        ];

        $content = "emp_no,amount\n";
        $content .= "EMP001,1000.00\n";
        $content .= "EMP002,500.00\n";

        return response($content, 200, $headers);
    }

    public function importData(Request $request, PayrollAdjustment $payrollAdjustment)
    {
        $request->validate([
            'type'    => 'required|in:addition,deduction',
            'item_id' => 'required|integer',
            'file'    => 'required|file|mimes:csv,xlsx,xls,txt',
        ]);

        $type   = $request->input('type');
        $itemId = (int) $request->input('item_id');

        if ($type === 'addition') {
            if (!Addition::where('id', $itemId)->where('status', true)->exists()) {
                return response()->json(['status' => false, 'message' => 'Selected addition not found.'], 422);
            }
        } else {
            if (!Deduction::where('id', $itemId)->where('status', true)->exists()) {
                return response()->json(['status' => false, 'message' => 'Selected deduction not found.'], 422);
            }
        }

        // Parse the uploaded file using PhpSpreadsheet
        try {
            $spreadsheet = IOFactory::load($request->file('file')->getPathname());
            $rows = $spreadsheet->getActiveSheet()->toArray(null, true, true, false);
        } catch (\Exception $e) {
            return response()->json(['status' => false, 'message' => 'Could not read file: ' . $e->getMessage()], 422);
        }

        if (empty($rows)) {
            return response()->json(['status' => false, 'message' => 'The file is empty.'], 422);
        }

        // Locate emp_no and amount columns from header row
        $header      = array_map(fn($v) => strtolower(trim((string) $v)), $rows[0]);
        $empNoIdx    = array_search('emp_no', $header);
        $amountIdx   = array_search('amount', $header);

        if ($empNoIdx === false || $amountIdx === false) {
            return response()->json([
                'status'  => false,
                'message' => 'Invalid file format. Required columns: emp_no, amount',
            ], 422);
        }

        // Build employee lookup keyed by employee_code
        $employees = Employee::where('payroll_group_id', $payrollAdjustment->payroll_group_id)
            ->where('status', 'active')
            ->pluck('id', 'employee_code')
            ->toArray();

        $inserted       = 0;
        $skipped        = 0;
        $errors         = [];

        DB::beginTransaction();
        try {
            $newEmployeeIds = [];

            foreach (array_slice($rows, 1) as $lineNo => $row) {
                $rowNum = $lineNo + 2; // 1-based, account for header
                $empNo  = trim((string) ($row[$empNoIdx] ?? ''));
                $amount = trim((string) ($row[$amountIdx] ?? ''));

                if ($empNo === '' && $amount === '') {
                    continue; // silently skip blank lines
                }

                if ($empNo === '' || $amount === '') {
                    $errors[] = "Row {$rowNum}: Missing emp_no or amount.";
                    $skipped++;
                    continue;
                }

                if (!isset($employees[$empNo])) {
                    $errors[] = "Row {$rowNum}: Employee '{$empNo}' not found in this payroll group.";
                    $skipped++;
                    continue;
                }

                if (!is_numeric($amount) || (float) $amount < 0) {
                    $errors[] = "Row {$rowNum}: Invalid amount '{$amount}' for employee '{$empNo}'.";
                    $skipped++;
                    continue;
                }

                $employeeId     = $employees[$empNo];
                $newEmployeeIds[] = $employeeId;

                $record = [
                    'payroll_adjustment_id' => $payrollAdjustment->id,
                    'employee_id'           => $employeeId,
                    'amount'                => (float) $amount,
                    'remarks'               => null,
                ];

                if ($type === 'addition') {
                    $record['addition_id'] = $itemId;
                    PayrollAdjustmentAddition::create($record);
                } else {
                    $record['deduction_id'] = $itemId;
                    PayrollAdjustmentDeduction::create($record);
                }

                $inserted++;
            }

            // Ensure each touched employee appears in payroll_adjustment_details
            foreach (array_unique($newEmployeeIds) as $empId) {
                PayrollAdjustmentDetail::firstOrCreate([
                    'payroll_adjustment_id' => $payrollAdjustment->id,
                    'employee_id'           => $empId,
                ]);
            }

            DB::commit();

            $message = "{$inserted} record(s) imported successfully.";
            if ($skipped > 0) {
                $message .= " {$skipped} row(s) skipped.";
            }

            return response()->json([
                'status'   => true,
                'message'  => $message,
                'inserted' => $inserted,
                'skipped'  => $skipped,
                'errors'   => $errors,
            ]);
        } catch (\Exception $e) {
            DB::rollBack();
            return response()->json([
                'status'  => false,
                'message' => 'Error importing data: ' . $e->getMessage(),
            ], 500);
        }
    }
}
