<?php

namespace App\Http\Controllers\SuperAdmin;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use App\Models\EmployeeAttendance;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use Illuminate\Support\Facades\Log;
use PhpOffice\PhpSpreadsheet\Shared\Date as ExcelDate;
use Carbon\Carbon;
use App\Models\EmployeeDetails;
use App\Models\EmployeeLeave;

class SuperAdminEmployeeAttendanceController extends Controller
{
    public function index(Request $request)
    {
        $employees = EmployeeDetails::all();
        $query = EmployeeAttendance::orderByDesc('date');

        if ($request->has('date') && $request->date != '') {
            $query->whereDate('date', $request->date);
        }
        // Removed strict 'else { today }' to show all imported data by default.
        
        // Using get() for DataTables client-side processing (400~ records is fine)
        $attendances = $query->get();
        
        return view('superadmin.employee.employeeattendance', compact('employees', 'attendances'));
    }

    public function attendanceMatrix(Request $request)
    {
        // Defaults to current month if not set
        $month = $request->input('month', date('Y-m'));
        $startDate = \Carbon\Carbon::parse($month . '-01');
        $endDate = $startDate->copy()->endOfMonth();
        $employees = EmployeeDetails::orderBy('employee_id')->get();

        // Get all dates in the month
        $dates = [];
        for ($d = $startDate->copy(); $d->lte($endDate); $d->addDay()) {
            $dates[] = $d->toDateString();
        }

        // Get holidays for the month
        $holidays = \App\Models\Holiday::whereBetween('date', [$startDate->toDateString(), $endDate->toDateString()])
            ->get()
            ->keyBy(function($row) {
                return \Carbon\Carbon::parse($row->date)->format('Y-m-d');
            });

        // Prepare data: $matrix[employee_id][date] = status
        $matrix = [];
        foreach ($employees as $emp) {
            $attendance = EmployeeAttendance::where('employee_id', $emp->employee_id)
                ->whereBetween('date', [$startDate->toDateString(), $endDate->toDateString()])
                ->get()
                ->keyBy(function($row) {
                    return \Carbon\Carbon::parse($row->date)->format('Y-m-d');
                });
            $leaves = EmployeeLeave::where('employee_id', $emp->id)
                ->where('status', 'Approved')
                ->where(function ($q) use ($startDate, $endDate) {
                    $q->whereBetween('leave_from', [$startDate, $endDate])
                      ->orWhereBetween('leave_to', [$startDate, $endDate])
                      ->orWhere(function ($q2) use ($startDate, $endDate) {
                          $q2->where('leave_from', '<', $startDate)
                             ->where('leave_to', '>', $endDate);
                      });
                })->get();

            // Build leave dates map for this employee
            $leaveDates = [];
            foreach ($leaves as $leave) {
                $leaveFrom = \Carbon\Carbon::parse($leave->leave_from);
                $leaveTo = \Carbon\Carbon::parse($leave->leave_to);
                for ($ld = $leaveFrom->copy(); $ld->lte($leaveTo); $ld->addDay()) {
                    $leaveDates[$ld->format('Y-m-d')] = strtoupper($leave->leave_type);
                }
            }

            foreach ($dates as $date) {
                $dateCarbon = \Carbon\Carbon::parse($date);
                $isSunday = $dateCarbon->dayOfWeek === 0;
                $isHoliday = isset($holidays[$date]);
                $hasAttendance = isset($attendance[$date]) && $attendance[$date]->check_in && $attendance[$date]->check_out;
                $hasLeave = isset($leaveDates[$date]);

                /**
                 * PRIORITY ORDER (highest to lowest):
                 * 1. ATTENDANCE (check_in + check_out exists) - actual work happened
                 * 2. HOLIDAY (public holiday) - only if no attendance
                 * 3. LEAVE (approved leave) - only if no attendance and not a holiday
                 * 4. SUNDAY (weekly off) - only if none of the above
                 * 5. ABSENT (working day with no attendance/leave)
                 */
                $status = 'Absent';

                // Priority 1: Attendance takes highest priority
                if ($hasAttendance) {
                    $status = 'Present';
                }
                // Priority 2: Holiday (only if no attendance)
                elseif ($isHoliday) {
                    $status = 'Holiday';
                }
                // Priority 3: Leave (only if no attendance and not holiday)
                elseif ($hasLeave) {
                    $leaveType = $leaveDates[$date];
                    $status = $leaveType === 'LOP' ? 'LOP' : $leaveType;
                }
                // Priority 4: Sunday (only if no attendance, no holiday, no leave)
                elseif ($isSunday) {
                    $status = 'Sunday';
                }
                // Priority 5: Absent (default)

                $matrix[$emp->employee_id][$date] = $status;
            }
        }

        return view('superadmin.employee.attendance_matrix', compact('employees', 'dates', 'matrix', 'month'));
    }

    public function import(Request $request)
    {
        Log::info('Attendance import started.');
        $file = $request->file('import_file');

        if (!$file) {
            Log::error('No file uploaded.');
            return back()->with('error', 'No file uploaded.');
        }

        try {
            // Load file
            $spreadsheet = IOFactory::load($file);
            $sheet = $spreadsheet->getActiveSheet();
            // Get highest row and column
            $highestRow = $sheet->getHighestRow();
            $highestColumn = $sheet->getHighestColumn();
            $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
        } catch (\Exception $e) {
            Log::error('Spreadsheet load error: '.$e->getMessage());
            return back()->with('error', 'Failed to read file: ' . $e->getMessage());
        }

        // DEBUG: Log first 5 rows to understand structure
        for ($r = 1; $r <= min(5, $highestRow); $r++) {
            $rowData = [];
            for ($c = 1; $c <= $highestColumnIndex; $c++) {
                $colLetter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($c);
                $val = $sheet->getCellByColumnAndRow($c, $r)->getValue();
                $rowData[$colLetter] = $val;
            }
            Log::info("Row $r raw:", $rowData);
        }

        // Helper function to parse date from various formats
        $parseDateValue = function($dateVal) {
            if (!$dateVal) return null;

            if (is_numeric($dateVal)) {
                return \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($dateVal)->format('Y-m-d');
            }

            try {
                // Clean string: "07/02/2025(Friday)" -> "07/02/2025"
                $cleanedDate = preg_replace('/\(.*?\)/', '', $dateVal);
                $cleanedDate = trim($cleanedDate);

                // Try explicit d/m/Y first (common in India/UK)
                return \Carbon\Carbon::createFromFormat('d/m/Y', $cleanedDate)->format('Y-m-d');
            } catch (\Exception $e) {
                try {
                    // Fallback to auto-detection
                    return \Carbon\Carbon::parse($cleanedDate)->format('Y-m-d');
                } catch (\Exception $e2) {
                    return null;
                }
            }
        };

        // Helper function to check if a value is a date row (e.g., "07/02/2025(Friday)")
        $isDateRow = function($val) {
            if (!$val || !is_string($val)) return false;
            // Match pattern like "07/02/2025(Friday)" or "07/02/2025"
            return preg_match('/^\d{1,2}\/\d{1,2}\/\d{4}(\s*\([A-Za-z]+\))?$/', trim($val));
        };

        // 1. Get initial Date from Cell B6 (first date in the file)
        $reportDateVal = $sheet->getCell('B6')->getValue();
        $currentDate = $parseDateValue($reportDateVal);

        if (!$currentDate) {
            return back()->with('error', "Could not find valid date in cell B6. Value: " . json_encode($reportDateVal));
        }

        Log::info("Starting attendance import. Initial date: $currentDate");

        // 2. Process Data Rows starting from B7
        $imported = 0;
        $startRow = 7;
        $datesProcessed = [$currentDate];

        // Clear debug log for fresh import
        file_put_contents(public_path('debug_import.log'), "Import started at " . date('Y-m-d H:i:s') . "\n");

        for ($row = $startRow; $row <= $highestRow; $row++) {
            // Get value from Col B
            $colBVal = trim($sheet->getCell('B' . $row)->getValue() ?? '');

            // Skip empty rows
            if (!$colBVal || $colBVal == '') {
                continue;
            }

            // Skip header rows
            if (strtolower($colBVal) == 'employee id' || strtolower($colBVal) == 'user' || strtolower($colBVal) == 'id') {
                continue;
            }

            // Skip SPFID legend rows
            if (stripos($colBVal, 'SPFID:') !== false) {
                continue;
            }

            // Check if this row is a date separator row (e.g., "08/02/2025(Saturday)")
            if ($isDateRow($colBVal)) {
                $newDate = $parseDateValue($colBVal);
                if ($newDate) {
                    $currentDate = $newDate;
                    if (!in_array($currentDate, $datesProcessed)) {
                        $datesProcessed[] = $currentDate;
                    }
                    Log::info("Switched to date: $currentDate (row $row)");
                }
                continue; // Skip this row, it's just a date separator
            }

            // Now process as employee data row
            $empIdVal = $colBVal;

            // Validate employee ID is numeric (skip any other non-employee rows)
            if (!is_numeric($empIdVal)) {
                Log::warning("Skipping non-numeric employee ID at row $row: $empIdVal");
                continue;
            }

            $nameVal = trim($sheet->getCell('C' . $row)->getValue() ?? '');

            // Read new fields: Shift (D), 1st Half (M), 2nd Half (N), Late-In (O), Early-Out (P)
            $shiftVal = trim($sheet->getCell('D' . $row)->getValue() ?? '');
            $firstHalfVal = trim($sheet->getCell('M' . $row)->getValue() ?? '');
            $secondHalfVal = trim($sheet->getCell('N' . $row)->getValue() ?? '');
            $lateInVal = trim($sheet->getCell('O' . $row)->getValue() ?? '');
            $earlyOutVal = trim($sheet->getCell('P' . $row)->getValue() ?? '');

            // Times helper - convert Excel time to Carbon
            $parseTimeCell = function($colLetter) use ($sheet, $row) {
                $val = $sheet->getCell($colLetter . $row)->getValue();
                if (!$val) return null;

                try {
                    if (is_numeric($val)) {
                        // Excel time is fraction of day -> convert to Carbon
                        $dt = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($val);
                        return \Carbon\Carbon::instance($dt);
                    }
                    // String parse
                    return \Carbon\Carbon::parse($val);
                } catch (\Exception $e) {
                    return null;
                }
            };

            $checkIn = $parseTimeCell('E');
            $breakOut = $parseTimeCell('G');
            $breakIn = $parseTimeCell('I');
            $checkOut = $parseTimeCell('K');

            // Read Excel columns for Work Hrs (V) and OT (S)
            $excelWorkHrsRaw = $sheet->getCell('V' . $row)->getValue();
            $excelOTRaw = $sheet->getCell('S' . $row)->getValue();

            // Helper to parse hours from various formats
            $parseHours = function($val) {
                if (!$val) return 0;
                if (is_numeric($val)) {
                    // If < 1, likely Excel time fraction (e.g. 0.354 = 8:30)
                    // If >= 1, could be hours or still Excel time fraction
                    if ($val < 1 && $val > 0) {
                        // Convert fraction of day to hours
                        return round($val * 24, 2);
                    }
                    // For values >= 1, check if it's a reasonable hour value
                    // If it's still a small decimal (like 0.4), treat as Excel time
                    return round((float)$val, 2);
                }
                // If string "08:30" or "8:30"
                if (is_string($val) && strpos($val, ':') !== false) {
                    try {
                        $parts = explode(':', $val);
                        return round((int)$parts[0] + ((int)($parts[1] ?? 0) / 60), 2);
                    } catch (\Exception $e) {
                        return 0;
                    }
                }
                return 0;
            };

            $excelWorkHrs = $parseHours($excelWorkHrsRaw);
            $excelOT = $parseHours($excelOTRaw);

            // Calculate working hours from check in/out times
            $totalWorkingMinutes = 0;
            if ($checkIn && $checkOut) {
                if ($breakOut && $breakIn) {
                    // Work time = (break_out - check_in) + (check_out - break_in)
                    $before = $breakOut->diffInMinutes($checkIn);
                    $after = $checkOut->diffInMinutes($breakIn);
                    $totalWorkingMinutes = $before + $after;
                } else {
                    $totalWorkingMinutes = $checkOut->diffInMinutes($checkIn);
                }
            }
            $systemCalcHours = round($totalWorkingMinutes / 60, 2);

            // Use system calculated hours if available, otherwise use Excel work hours
            $finalWorkHours = ($systemCalcHours > 0) ? $systemCalcHours : $excelWorkHrs;

            // OT calculation
            $standardHours = 9;
            $systemOT = max(0, $finalWorkHours - $standardHours);
            // Use Excel OT if provided, otherwise use calculated OT
            $finalOT = ($excelOT > 0) ? $excelOT : $systemOT;

            // Shortage calculation
            $shortageHours = max(0, $standardHours - $finalWorkHours);
            if ($finalWorkHours == 0) $shortageHours = 0;

            $insertData = [
                'date' => $currentDate,
                'employee_id' => $empIdVal,
                'employee_name' => $nameVal ?: 'Unknown',
                'shift' => $shiftVal ?: null,
                'check_in' => $checkIn ? $checkIn->format('H:i') : null,
                'break_out' => $breakOut ? $breakOut->format('H:i') : null,
                'break_in' => $breakIn ? $breakIn->format('H:i') : null,
                'check_out' => $checkOut ? $checkOut->format('H:i') : null,
                'first_half' => $firstHalfVal ?: null,
                'second_half' => $secondHalfVal ?: null,
                'late_in' => $lateInVal ?: null,
                'early_out' => $earlyOutVal ?: null,
                'working_hours' => $finalWorkHours,
                'ot_hours' => $finalOT,
                'shortage_hours' => $shortageHours
            ];

            \App\Models\EmployeeAttendance::updateOrCreate(
                ['employee_id' => $empIdVal, 'date' => $currentDate],
                $insertData
            );
            $imported++;
        }

        $datesSummary = implode(', ', $datesProcessed);
        Log::info("Import completed. $imported records processed for dates: $datesSummary");
        return back()->with('success', "Processed $imported attendance records for " . count($datesProcessed) . " date(s): $datesSummary");
    }

    public function store(Request $request)
    {
        $request->validate([
            'employee_id' => 'required',
            'date' => 'required|date',
            'check_in' => 'nullable', 
            'check_out' => 'nullable',
        ]);

        $employee = EmployeeDetails::where('employee_id', $request->employee_id)->first();
        if (!$employee) {
             // Fallback if ID is ID not employee_id
             $employee = EmployeeDetails::find($request->employee_id);
        }

        // Calculate Hours
        $checkIn = $request->check_in ? \Carbon\Carbon::parse($request->check_in) : null;
        $checkOut = $request->check_out ? \Carbon\Carbon::parse($request->check_out) : null;
        $breakOut = $request->break_out ? \Carbon\Carbon::parse($request->break_out) : null;
        $breakIn = $request->break_in ? \Carbon\Carbon::parse($request->break_in) : null;

        $otHours = 0;
        $shortageHours = 0;
        $workingHours = 0;

        if ($checkIn && $checkOut) {
            $totalMinutes = $checkOut->diffInMinutes($checkIn);
            $breakMinutes = ($breakOut && $breakIn) ? $breakIn->diffInMinutes($breakOut) : 0;
            
            $netMinutes = max(0, $totalMinutes - $breakMinutes);
            $workingHours = round($netMinutes / 60, 2);

            $standardHours = 9; 
            if ($workingHours > $standardHours) {
                $otHours = round($workingHours - $standardHours, 2);
            } elseif ($workingHours < $standardHours) {
                $shortageHours = round($standardHours - $workingHours, 2);
            }
        }

        EmployeeAttendance::create([
            'employee_id' => $request->employee_id,
            'employee_name' => $employee ? $employee->name : 'Unknown',
            'date' => $request->date,
            'check_in' => $request->check_in,
            'break_out' => $request->break_out,
            'break_in' => $request->break_in,
            'check_out' => $request->check_out,
            'ot_hours' => $otHours,
            'shortage_hours' => $shortageHours,
            'working_hours' => $workingHours
        ]);

        return back()->with('success', 'Attendance added successfully.');
    }

    public function edit($id)
    {
        // Not used by Modal approach usually, but route exists
        return response()->json(EmployeeAttendance::find($id));
    }

    public function update(Request $request, $id)
    {
        $attendance = EmployeeAttendance::findOrFail($id);
        
        $request->validate([
            'date' => 'required|date',
        ]);

        $checkIn = $request->check_in ? \Carbon\Carbon::parse($request->check_in) : null;
        $checkOut = $request->check_out ? \Carbon\Carbon::parse($request->check_out) : null;
        $breakOut = $request->break_out ? \Carbon\Carbon::parse($request->break_out) : null;
        $breakIn = $request->break_in ? \Carbon\Carbon::parse($request->break_in) : null;

        $otHours = 0;
        $shortageHours = 0;
        $workingHours = 0;

        if ($checkIn && $checkOut) {
            $totalMinutes = $checkOut->diffInMinutes($checkIn);
            $breakMinutes = ($breakOut && $breakIn) ? $breakIn->diffInMinutes($breakOut) : 0;

            $netMinutes = max(0, $totalMinutes - $breakMinutes);
            $workingHours = round($netMinutes / 60, 2);

            $standardHours = 9; 
            if ($workingHours > $standardHours) {
                $otHours = round($workingHours - $standardHours, 2);
            } elseif ($workingHours < $standardHours) {
                $shortageHours = round($standardHours - $workingHours, 2);
            }
        }

        $attendance->update([
            'employee_id' => $request->employee_id ?? $attendance->employee_id,
            'date' => $request->date,
            'check_in' => $request->check_in,
            'break_out' => $request->break_out,
            'break_in' => $request->break_in,
            'check_out' => $request->check_out,
            'ot_hours' => $otHours,
            'shortage_hours' => $shortageHours,
            'working_hours' => $workingHours
        ]);

        return back()->with('success', 'Attendance updated successfully.');
    }

    public function destroy($id)
    {
        EmployeeAttendance::find($id)->delete();
        return back()->with('success', 'Attendance deleted.');
    }
}
