<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;
use PhpOffice\PhpSpreadsheet\IOFactory;
    use Carbon\Carbon;
use Exception;
use PDF; // at the top
use Illuminate\Support\Str;


class AttendanceController extends Controller
{

// public function generatePDF()
// {
//     $employees = [
//         ['name' => 'Aman Sinha', 'department' => 'IT Department', 'signature' => ''],
//         ['name' => 'Ankhush Sir', 'department' => 'Sales', 'signature' => ''],
//         ['name' => 'Irfan Khan', 'department' => 'IT Department', 'signature' => ''],
//         ['name' => 'Madan Choudhary', 'department' => 'IT Department', 'signature' => ''],
//         ['name' => 'Ghansham Tiwari', 'department' => 'IT Department', 'signature' => ''],
//         ['name' => 'Kripal Singh', 'department' => 'IT Department', 'signature' => ''],
//         ['name' => 'Anu', 'department' => 'Graphics', 'signature' => ''],
//         ['name' => 'Keshav', 'department' => 'Sales', 'signature' => ''],
//         ['name' => 'Puja Bist', 'department' => 'Sales', 'signature' => ''],
//     ];

//     $data = [
//         'company_name' => '99isolutions Pvt Ltd',
//         'employee_example' => 'Kripal Singh',
//         'employees' => $employees,
//         'date' => now()->format('d F Y'),
//     ];
    
//     //   return view('petition.pdf',compact('data'));
// // return view('petition.pdf')->with($data);

//     $pdf = PDF::loadView('petition.pdf', $data)->setPaper('A4', 'portrait');
//     return $pdf->download('Employee_Petition.pdf');
// }

public function showUploadForm() {
    return view('upload');
}

public function uploadMultiple() {
    return view('attendance.m_upload');
}

/*public function uploadMultipleExcel(Request $request)
{
    $request->validate([
        'attendance_file.*' => 'required|file|mimes:xlsx,xls',
    ]);

    $allResultsByMonth = [];
    $overallExtraDecimal = 0;

    foreach ($request->file('attendance_file') as $file) {
        $data = Excel::toArray([], $file);
        $rows = $data[0];

        $dept = 'N/A';
        $name = 'N/A';
        $dateRange = 'N/A';
        $details = [];
        $month = 'N/A';

        foreach ($rows as $row) {
            foreach ($row as $index => $cell) {
                if (is_string($cell)) {
                    $lowerCell = strtolower($cell);

                    if (Str::contains($lowerCell, 'dept')) {
                        $dept = $row[$index + 1] ?? 'N/A';
                    }
                    if (Str::contains($lowerCell, 'name')) {
                        $name = $row[$index + 1] ?? 'N/A';
                    }
                    if (Str::contains($lowerCell, 'date')) {
                        $next = $row[$index + 1] ?? '';
                        if (preg_match('/\d{4}-\d{2}-\d{2}~\d{4}-\d{2}-\d{2}/', $next)) {
                            $dateRange = $next;
                            $month = $this->getMonthInWords($dateRange);
                        }
                    }
                }
            }
        }

        $results = [];
        $monthExtraDecimal = 0;

        foreach ($rows as $i => $row) {
            try {
                if ($i < 12 || empty($row[0])) continue;

                $date = $row[0];

                $nonNullValues = array_values(array_filter($row, function ($v) {
                    return !is_null($v) && is_numeric($v);
                }));

                if (count($nonNullValues) < 2) continue;

                $middleIndex = floor(count($nonNullValues) / 2);
                $inDecimal = $nonNullValues[$middleIndex - 1] ?? null;
                $outDecimal = $nonNullValues[$middleIndex] ?? null;
                if (is_null($inDecimal) || is_null($outDecimal)) continue;
                if (!is_numeric($inDecimal) || !is_numeric($outDecimal)) continue;

                $inTimeHours = floatval($inDecimal) * 24;
                $outTimeHours = floatval($outDecimal) * 24;

                if ($outTimeHours < $inTimeHours) {
                    $outTimeHours += 12;
                }

                $worked = $outTimeHours - $inTimeHours;
                $isHoliday = $this->isHoliday($date);
                $holidayHours = 0;

                if ($isHoliday) {
                    $holidayHours = $worked;
                    $extra = $worked;
                } elseif ($worked > 9) {
                    $extra = $worked - 9;
                } else {
                    $extra = 0;
                }

                $status = 'Full Day';
                if ($worked < 4.5) {
                    $status = 'Too Short';
                } elseif ($worked < 7) {
                    $status = 'Half Day';
                    if (!$isHoliday) {
                        $extra = max(0, $worked - 4.5);
                    }
                } elseif ($worked < 9 && $worked > 7) {
                    $status = "Quator";
                }

                $monthExtraDecimal += $extra;
                $overallExtraDecimal += $extra;

                $results[] = [
                    'date' => $date,
                    'in_time' => $this->convertDecimalToTime($inTimeHours),
                    'out_time' => $this->convertDecimalToTime($outTimeHours),
                    'worked_hours' => $this->convertDecimalToHoursMinutes($worked),
                    'extra_hours' => $this->convertDecimalToHoursMinutes($extra),
                    'holiday_hours' => $isHoliday ? $this->convertDecimalToHoursMinutes($holidayHours) : null,
                    'is_holiday' => $isHoliday,
                    'status' => $status,
                ];

            } catch (\Exception $e) {
                return $e;
            }
        }

        $allResultsByMonth[] = [
            'month' => $month,
            'dept' => $dept,
            'name' => $name,
            'results' => $results,
            'total_extra_hours' => $this->convertDecimalToHoursMinutes($monthExtraDecimal),
            'sort_date' => Carbon::parse('01 ' . $month),
        ];
    }

    $overallTotalExtra = $this->convertDecimalToHoursMinutes($overallExtraDecimal);
    $allResultsByMonth = collect($allResultsByMonth)->sortByDesc('sort_date')->values()->toArray();

    session([
        'dataByMonth' => $allResultsByMonth,
        'overallTotalExtra' => $overallTotalExtra,
    ]);

    return view('attendance.m_result', [
        'dataByMonth' => $allResultsByMonth,
        'overallTotalExtra' => $overallTotalExtra,
    ]);
}

private function isHoliday($date)
{
    $carbonDate = Carbon::parse($date);
    $dayOfWeek = $carbonDate->dayOfWeek;

    if ($dayOfWeek == Carbon::SUNDAY) return true;

    if ($dayOfWeek == Carbon::SATURDAY) {
        $saturdays = 0;
        $monthStart = $carbonDate->copy()->startOfMonth();
        while ($monthStart->month == $carbonDate->month) {
            if ($monthStart->dayOfWeek == Carbon::SATURDAY) {
                $saturdays++;
                if ($monthStart->toDateString() == $carbonDate->toDateString()) {
                    if ($saturdays == 4) return true;
                    break;
                }
            }
            $monthStart->addDay();
        }
    }

    $customHolidays = [
        '2025-05-25', '2025-05-14',
    ];

    return in_array($carbonDate->toDateString(), $customHolidays);
}
*/


private function isHoliday($date)
{
    $carbonDate = Carbon::parse($date);
    $dateString = $carbonDate->toDateString();
    $dayOfWeek = $carbonDate->dayOfWeek;

    // Always off on Sunday
    if ($dayOfWeek === Carbon::SUNDAY) {
        return true;
    }

    // 4th Saturday OFF, EXCEPT in March 2025
    if (
        $dayOfWeek === Carbon::SATURDAY &&
        !($carbonDate->month == 3 && $carbonDate->year == 2025) &&
        $this->isFourthSaturday($carbonDate)
    ) {
        return true;
    }

    // Custom holidays (use strict comparison)
    $customHolidays = [
        '2024-10-02', '2024-10-30', '2024-10-31',
        '2024-11-01', '2024-11-02',
        '2024-12-30', '2024-12-31',
        '2025-01-01', '2025-01-14',
        // February 2025: No holidays
        '2025-03-04', '2025-03-05', '2025-03-06',
        '2025-03-13', '2025-03-14', '2025-03-15',
        // Add more if needed...
    ];

    return in_array($dateString, $customHolidays, true);
}

private function isFourthSaturday(Carbon $date): bool
{
    $month = $date->month;
    $year = $date->year;
    $saturdays = 0;

    for ($day = 1; $day <= 31; $day++) {
        try {
            $currentDate = Carbon::createFromDate($year, $month, $day);
        } catch (\Exception $e) {
            break;
        }

        if ($currentDate->dayOfWeek === Carbon::SATURDAY) {
            $saturdays++;

            if ($currentDate->isSameDay($date)) {
                return $saturdays === 4;
            }
        }
    }

    return false;
}

// private function isHoliday($date)
// {
//     $carbonDate = Carbon::parse($date);
// //   return  $carbonDate->toDateString();
     
//     $dayOfWeek = $carbonDate->dayOfWeek;

//     if ($dayOfWeek == Carbon::SUNDAY) return true;  

//     if ($dayOfWeek == Carbon::SATURDAY) {
//         $saturdays = 0;
//         $monthStart = $carbonDate->copy()->startOfMonth();
//         while ($monthStart->month == $carbonDate->month) {
//             if ($monthStart->dayOfWeek == Carbon::SATURDAY) {
//                 $saturdays++;
//                 if ($monthStart->toDateString() == $carbonDate->toDateString()) {
//                     if ($saturdays == 4) return true;
//                     break;
//                 }
//             }
//             $monthStart->addDay();
//         }
//     }

//     $customHolidays = [
//         '2024-05-25', '2025-05-14',
//     ];

//     return in_array($carbonDate->toDateString(), $customHolidays);
// }

public function uploadMultipleExcel(Request $request)
{
    $request->validate([
        'attendance_file.*' => 'required|file|mimes:xlsx,xls', 
    ]);

    $allResultsByMonth = [];
    $overallExtraDecimal = 0;
     

    foreach ($request->file('attendance_file') as $file) {
        $data = Excel::toArray([], $file);
        
        $rows = $data[0];
        // return $rows;
        // dd($rows);

         $dept = 'N/A';
        $name = 'N/A';
        $dateRange = 'N/A';
     $details = []; // This will store all extracted values


$month = 'N/A';
foreach ($rows as $row) {
    foreach ($row as $index => $cell) {
        if (is_string($cell)) {
            $lowerCell = strtolower($cell);

            // Pick department
            if (Str::contains($lowerCell, 'dept')) {
                $dept = $row[$index + 1] ?? 'N/A';
            }

            // Pick name
            if (Str::contains($lowerCell, 'name')) {
                $name = $row[$index + 1] ?? 'N/A';
            }

            // Pick correct dateRange (only if next cell looks like date range)
            if (Str::contains($lowerCell, 'date')) {
                $next = $row[$index + 1] ?? '';
                if (preg_match('/\d{4}-\d{2}-\d{2}~\d{4}-\d{2}-\d{2}/', $next)) {
                    $dateRange = $next;
                    $month = $this->getMonthInWords($dateRange);
                    $converted = Carbon::createFromFormat('F Y', $month)->format('m-Y');

// return $converted; // Output: 03-2025
                // $isHoliday = $this->isHoliday($converted);

                    // return $isHoliday;
                }
            }
        }
    }
}         
        $results = [];
        $monthExtraDecimal = 0;

        foreach ($rows as $i => $row) {
            try{
            if ($i < 12 || empty($row[0])) continue;

            $date = $row[0];
                        // return $date;
// $date = "01 Sa";

$parts = explode(' ', $date);
$day = isset($parts[0]) ? (int) $parts[0] : null; // $day will be 2
// return $day.'-'.$converted;

                            // $isHoliday = $this->isHoliday($day.'-'.$converted);
// return $isHoliday;
            // $inDecimal = $row[3] ?? $row[1]?? $row[6] ?? null;
            // $outDecimal = $row[12] ?? $row[10]?? $row[6] ?? $row[8] ??null;
$nonNullValues = array_values(array_filter($row, function($v) {
    return !is_null($v) && is_numeric($v);
}));

        if (count($nonNullValues) < 2) continue;

        $middleIndex = floor(count($nonNullValues) / 2);
        $inDecimal = $nonNullValues[$middleIndex - 1] ?? null;
        $outDecimal = $nonNullValues[$middleIndex] ?? null;
            if (is_null($inDecimal) || is_null($outDecimal)) continue;
            if (!is_numeric($inDecimal) || !is_numeric($outDecimal)) continue;

$inTimeHours = floatval($inDecimal) * 24;
$outTimeHours = floatval($outDecimal) * 24;

            if ($outTimeHours < $inTimeHours) {
    $outTimeHours += 12;
}


            
                    $worked = $outTimeHours - $inTimeHours;
                $isHoliday = $this->isHoliday($day.'-'.$converted);
                $holidayHours = 0;

                if ($isHoliday) {
                    $holidayHours = $worked;
                    $extra = $worked;
                    $status = "Holiday";
                } elseif ($worked > 9) {
                    $extra = $worked - 9;
                } else {
                    $extra = 0;
                }

                $status = 'Full Day';
                if ($worked < 4.5) {
                    $status = 'Too Short';
                } elseif ($worked < 7) {
                    $status = 'Half Day';
                    if (!$isHoliday) {
                        $extra = max(0, $worked - 4.5);
                    }
                } elseif ($worked < 9 && $worked > 7) {
                    $status = "Quator";
                }
                
                 if ($isHoliday) {
                    $status = "Holiday";
                }
        
//             $worked = $outTimeHours - $inTimeHours;
// $extra = max(0, $worked - 9);

// $status = 'Full Day';
// // $extra = $worked - 9;
// if ($worked < 4.5) {
//     $status = 'Too Short';
// } 
// elseif ($worked < 7) {
//     $status = 'Half Day';
//     $extra = max(0, $worked - 4.5); // Overtime after 4.5 hrs on Half Day
// } 

// elseif($worked < 9&&$worked >7) {
//   // $extra = $worked - 9;
//   $status = "Quator";
// }

$monthExtraDecimal += $extra;
$overallExtraDecimal += $extra;

// $results[] = [
//     'date' => $date,
//     'in_time' => $this->convertDecimalToTime($inTimeHours),
//     'out_time' => $this->convertDecimalToTime($outTimeHours),
//     'worked_hours' => $this->convertDecimalToHoursMinutes($worked),
//     'extra_hours' => $this->convertDecimalToHoursMinutes($extra),
//     'status' => $status,
// ];


                $results[] = [
                    'date' => $date,
                    'in_time' => $this->convertDecimalToTime($inTimeHours),
                    'out_time' => $this->convertDecimalToTime($outTimeHours),
                    'worked_hours' => $this->convertDecimalToHoursMinutes($worked),
                    'extra_hours' => $this->convertDecimalToHoursMinutes($extra),
                    'holiday_hours' => $isHoliday ? $this->convertDecimalToHoursMinutes($holidayHours) : null,
                    'is_holiday' => $isHoliday,
                    'status' => $status,
                ];

            }catch(\Exception $e){
return $e;

                
            }
        }

        $allResultsByMonth[] = [
            'month' => $month,
            'dept' => $dept,
            'name' => $name,
            'results' => $results,
            'total_extra_hours' => $this->convertDecimalToHoursMinutes($monthExtraDecimal),
                'sort_date' => Carbon::parse('01 ' . $month), // Using 1st of the month for sorting

        ];
        
      
    }

    $overallTotalExtra = $this->convertDecimalToHoursMinutes($overallExtraDecimal);
    $allResultsByMonth = collect($allResultsByMonth)->sortByDesc('sort_date')->values()->toArray();

session([
    'dataByMonth' => $allResultsByMonth,
    'overallTotalExtra' => $overallTotalExtra,
]);

    return view('attendance.m_result', [
        'dataByMonth' => $allResultsByMonth,
        'overallTotalExtra' => $overallTotalExtra,
    ]);
}


public function downloadPdf(Request $request)
{ 
    $dataByMonth = session('dataByMonth');
    $overallTotalExtra = session('overallTotalExtra');
// return $dataByMonth[0]['name'];
 $pdf_name = $dataByMonth[0]['name'];
    $pdf = PDF::loadView('attendance.m_result', compact('dataByMonth', 'overallTotalExtra'));
    // return $pdf->download('attendance_report.pdf');
    return $pdf->download($pdf_name.'.pdf');

    
}



//-----------------------------------------------------------------------------------------

function convertDecimalToHoursMinutes($decimalHours)
{
    $hours = floor($decimalHours);
    $minutes = round(($decimalHours - $hours) * 60);
    return sprintf('%02d hrs %02d mins', $hours, $minutes);
}
function convertDecimalToTime($decimal)
{
    if ($decimal === null) return '-';
    $hours = floor($decimal);
    $minutes = round(($decimal - $hours) * 60);
    return date("h:i A", mktime($hours, $minutes));
}
function getMonthInWords($dateRange) {
    if (!$dateRange || !str_contains($dateRange, '~')) {
        return null;
    }

    $dates = explode('~', $dateRange);
    $startDate = $dates[0];

    if (!$startDate) {
        return null;
    }

    return date('F Y', strtotime($startDate));
}


public function uploadExcel(Request $request)
{
    $request->validate([
        'attendance_file' => 'required|file|mimes:xlsx,xls',
    ]);

    $data = Excel::toArray([], $request->file('attendance_file'));
    $rows = $data[0];
// dd($rows);
    // Fallback-safe data extraction
    $dept = $rows[4][1] ?? $rows[6][1] ?? 'N/A';
    $name = $rows[4][9] ?? $rows[6][9] ?? 'N/A';
    $month = $this->getMonthInWords($rows[5][1] ?? $rows[7][1] ?? null);

    $totalExtraHoursDecimal = 0;
    $results = [];

    foreach ($rows as $i => $row) {
        if ($i < 12 || empty($row[0])) continue;

        $date = $row[0];
        $inDecimal = $row[3] ?? $row[1] ?? null;
        $outDecimal = $row[12] ?? $row[10] ?? $row[6] ?? null;

        if (is_null($inDecimal) || is_null($outDecimal)) continue;

        $inTimeHours = $inDecimal * 24;
        $outTimeHours = $outDecimal * 24;
        $worked = $outTimeHours - $inTimeHours;
        $extra = max(0, $worked - 9);

        $totalExtraHoursDecimal += $extra;

        $results[] = [
            'date' => $date,
            'in_time' => $this->convertDecimalToTime($inTimeHours),
            'out_time' => $this->convertDecimalToTime($outTimeHours),
            'worked_hours' => $this->convertDecimalToHoursMinutes($worked),
            'extra_hours' => $this->convertDecimalToHoursMinutes($extra),
        ];
    }

    $totalExtraHours = $this->convertDecimalToHoursMinutes($totalExtraHoursDecimal);

    return view('attendance.result', compact('results', 'totalExtraHours', 'dept', 'name', 'month'));
}



}



