<?php

namespace App\Modules\Hr\Services;

use App\Models\User;
use App\Modules\Hr\UserDetails;
use App\Modules\Hr\TimeLog;
use Carbon\Carbon;
use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Support\Facades\DB;

class ExcelExporter implements ExcelExporterInterface
{
    /**
     * Export using modern logic with endOfDay for complete date coverage
     */
    public function export($user_id, $start = null, $end = null)
    {
        // Store original dates for display purposes
        $originalStart = $start;
        $originalEnd = $end;
        
        // Get the period - Fixed to handle end date properly
        $start = Carbon::parse($start)->startOfDay()->toDateTimeString();
        // Set end date to end of day (23:59:59) to include all records from that date
        $end = Carbon::parse($end)->endOfDay()->toDateTimeString();

        $user_details = UserDetails::find($user_id);
        $user_details->load('user');

        // Fetch the clocking times for the interval $start - $end - EXACTLY like Laravel5
        $in = TimeLog::where('user_id', $user_id)
                ->where(DB::raw("DATE_FORMAT(`time`, '%Y-%m-%d %T')"), '>=', $start)
                ->where(DB::raw("DATE_FORMAT(`time`, '%Y-%m-%d %T')"), '<=', $end)
                ->where('type', 'in')
                ->orderBy('time', 'asc')
                ->get();

        $out = TimeLog::where('user_id', $user_id)
                ->where(DB::raw("DATE_FORMAT(`time`, '%Y-%m-%d %T')"), '>=', $start)
                ->where(DB::raw("DATE_FORMAT(`time`, '%Y-%m-%d %T')"), '<=', $end)
                ->where('type', 'out')
                ->orderBy('time', 'asc')
                ->get();

        // Generate a data array of all the times
        $data = [];
        $total_hours = 0;
        $total_money = 0;

        foreach ($in as $i => $clockIn) {
            $date = date('Y-m-d', strtotime($clockIn->time));
            $found = false;

            foreach ($out as $j => $clockOut) {
                if ($date == date('Y-m-d', strtotime($clockOut->time))) {
                    // FIXED: Ensure positive hours calculation
                    $dt1 = Carbon::parse($clockIn->time);
                    $dt2 = Carbon::parse($clockOut->time);
                    
                    // Calculate hours as positive value (clockOut - clockIn)
                    $seconds = abs($dt2->diffInSeconds($dt1));
                    $hours = number_format($seconds / 3600, 2);
                    $money = number_format($hours * $user_details->rate_per_hour, 2);
                    
                    // Use raw database times as they should already be in correct timezone
                    $data[] = [
                        $date, 
                        $clockIn->time,
                        $clockOut->time,
                        $hours,
                        $money
                    ];
                    
                    $total_hours += $hours;
                    $total_money += $money;
                    $found = true;
                    unset($out[$j]);
                    break;
                }
            }

            if (! $found) {
                $data[] = [$date, $clockIn->time, null, null, null];
            }

            unset($in[$i]);
        }

        // Check the left clockout times
        foreach ($out as $j => $clockOut) {
            $date = date('Y-m-d', strtotime($clockOut->time));
            $data[] = [$date, null, $clockOut->time, null, null];
        }

        // Sort the results by the date - EXACTLY like Laravel5
        usort($data, function ($a, $b) {
            return $b[1] < $a[1];
        });

        // Add row numbers
        foreach ($data as $key => $value) {
            array_unshift($data[$key], $key + 1);
        }

        // Create the export class
        $export = new TimesheetExport($data, $user_details, $start, $end, 
            number_format($total_hours, 2), number_format($total_money, 2),
            $originalStart, $originalEnd);

        // Return the export object directly
        return $export;
    }

    /**
     * Export using EXACT Laravel5 logic for HR downloading employee timesheets
     * This maintains the original behavior where end date is not modified to endOfDay
     */
    public function exportLaravel5Style($user_id, $start = null, $end = null)
    {
        // Store original dates for display purposes
        $originalStart = $start;
        $originalEnd = $end;
        
        // Get the period - Fixed to include full last date to match web interface data
        $start = Carbon::parse($start)->startOfDay()->toDateTimeString();
        $end = Carbon::parse($end)->endOfDay()->toDateTimeString();

        $user_details = UserDetails::find($user_id);
        $user_details->load('user');

        // Fetch the clocking times for the interval $start - $end - EXACTLY like Laravel5
        $in = TimeLog::where('user_id', $user_id)
                ->where(DB::raw("DATE_FORMAT(`time`, '%Y-%m-%d %T')"), '>=', $start)
                ->where(DB::raw("DATE_FORMAT(`time`, '%Y-%m-%d %T')"), '<=', $end)
                ->where('type', 'in')
                ->orderBy('time', 'asc')
                ->get();

        $out = TimeLog::where('user_id', $user_id)
                ->where(DB::raw("DATE_FORMAT(`time`, '%Y-%m-%d %T')"), '>=', $start)
                ->where(DB::raw("DATE_FORMAT(`time`, '%Y-%m-%d %T')"), '<=', $end)
                ->where('type', 'out')
                ->orderBy('time', 'asc')
                ->get();

        // Generate a data array of all the times - EXACTLY like Laravel5
        $data = [];
        $total_hours = 0;
        $total_money = 0;

        foreach ($in as $i => $clockIn) {
            $date = date('Y-m-d', strtotime($clockIn->time));
            $found = false;

            foreach ($out as $j => $clockOut) {
                if ($date == date('Y-m-d', strtotime($clockOut->time))) {
                    // FIXED: Ensure positive hours calculation (clockOut - clockIn)
                    $dt1 = Carbon::parse($clockIn->time);
                    $dt2 = Carbon::parse($clockOut->time);
                    $seconds = abs($dt2->diffInSeconds($dt1));
                    $hoursRaw = $seconds / 3600;
                    $moneyRaw = $hoursRaw * $user_details->rate_per_hour;
                    
                    $data[] = [
                        $i + 1, // ID
                        $date,
                        date('H:i:s', strtotime($clockIn->time)),  // Clocked In time only
                        date('H:i:s', strtotime($clockOut->time)), // Clocked Out time only
                        number_format($hoursRaw, 2),
                        number_format($moneyRaw, 2)
                    ];
                    
                    $total_hours += $hoursRaw;
                    $total_money += $moneyRaw;
                    $found = true;
                    unset($out[$j]);
                    break;
                }
            }

            if (!$found) {
                $data[] = [
                    $i + 1, // ID
                    $date,
                    date('H:i:s', strtotime($clockIn->time)), // Clocked In time only
                    null, // No clock out
                    null, // No hours
                    null  // No money
                ];
            }
        }

        // Create the export class
        $export = new TimesheetExport($data, $user_details, $start, $end, 
            number_format($total_hours, 2), number_format($total_money, 2),
            $originalStart, $originalEnd);

        // Return the export object directly
        return $export;
    }
}
