<?php

namespace App\Exports;

use App\Models\Governorate;
use App\Models\Role;
use App\Models\User;
use App\Models\UserWorkSession;
use Carbon\Carbon;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use Maatwebsite\Excel\Concerns\WithColumnWidths;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithFormatData;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Events\AfterSheet;
use function Termwind\ValueObjects\getValue;

class UserSessionExport implements FromCollection, WithHeadings, WithMapping, WithColumnFormatting, WithColumnWidths, WithEvents, WithFormatData
{

    protected $userId;
    protected $governorateId;
    protected $dates;
    protected $sessions;

    function __construct($userId, $governorateId, $dates) {
        $this->userId = $userId;
        $this->governorateId = $governorateId;
        $this->dates = $dates;
    }
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
//        $query = UserWorkSession::query();
        $query = User::query();
        $sessions = UserWorkSession::query();
        $user = auth()->guard('admin')->user();
        if($this->governorateId != null){
            $query = $query->where('gov_id', $this->governorateId);
            $governorates = Governorate::query()->where('id', $this->governorateId)->get();
            $gov_id = $this->governorateId;
            $sessions = $sessions->whereIn('user_id', function($q) use($gov_id) {
                $q->select('id')->from((new User())->getTable())->where('gov_id', $gov_id)->get();
            });
        }else{
            if($user->role_id == Role::SUPERVISOR_ROLE){
                $governorates = Governorate::query()->where('id', $user->gov_id)->get();
            }else{
                $governorates = Governorate::query()->get();
            }
            $sessions = $sessions->whereIn('user_id', function($q) use($governorates) {
                $q->select('id')->from((new User())->getTable())->whereIn('gov_id', $governorates->pluck('id')->toArray())->get();
            });
        }

        if($this->userId != null){
            $query = $query->whereIn('id', $this->userId);
            $sessions = $sessions->whereIn('user_id', $this->userId);
        }else{
            $employees = User::query()->whereIn('gov_id', $governorates->keyBy('id')->keys()->toArray())->get();
            $sessions = $sessions->whereIn('user_id', $employees->pluck('id')->toArray());
        }

        if($this->dates != null && is_array($this->dates)){
            if(is_array($this->dates) && key_exists('start_date', $this->dates)){
                if(key_exists('end_date', $this->dates) && $this->dates['end_date'] != null){
                    $sessions = $sessions->whereDate('start_date', '>=', $this->dates['start_date']);
                }else{
                    $sessions = $sessions->whereDate('start_date', $this->dates['start_date']);
                }
            }
            if(is_array($this->dates) && key_exists('end_date', $this->dates) && $this->dates['end_date'] != null){
                $sessions = $sessions->whereDate('end_date', '<=', $this->dates['end_date']);
            }
        }
        $this->sessions = $sessions->get()->keyBy('user_id');
//        dd($query->toSql());
        return $query->get();
    }

    public function headings(): array
    {
        return [
            "Full Name",
            "Governorate",
            "Date",
            "Clock In",
            "Clock Out",
            "Start Online",
            "End Online",
            "Exception",
            "Duration",
        ];

    }

    // here you select the row that you want in the file
    public function map($row): array{
        $duration = "00:00";
        if(key_exists($row->id, $this->sessions->modelKeys())){
            $session = $this->sessions[$row->id];
        }else{
            $session = null;
        }
        if($session == null){
            $duration = 0;
            $fields = [
                User::getDecryptedData($row->full_name),
                $row->gov_id != null ? $row->governorate->defaultLang->name:"",
                "Absent",
                "N/A",
                "N/A",
                "",
                "",
                "",
                $duration
//            $row->start_online == null ? "Offline":($row->start_online == true ? "Online":"Offline"),
//            $row->end_online == null ? "Offline":($row->end_online == true ? "Online":"Offline"),
            ];
        }else{
            $startOnline = "Offline";
            if($session->start_date != null && $session->end_date != null){
                $duration = $session->end_date->diffInMinutes($session->start_date);
                $duration = date('H:i', mktime(0, $duration));
            }
            if($session->start_date != null) {
                if($session->start_online == 1)
                    $startOnline = "Online";
                elseif($session->start_online == 0)
                    $startOnline = "Offline";
                else
                    $startOnline = "";
            }else{
                $startOnline = "";
            }
            $endOnline = "Offline";
            if($session->end_date != null) {
                if($session->end_online == 1)
                    $endOnline = "Online";
                elseif($session->end_online == 0)
                    $endOnline = "Offline";
                else
                    $endOnline = "";
            }else{
                $endOnline = "";
            }
            $fields = [
                $session->user_id != null ? User::getDecryptedData($row->full_name):"",
                $session->user_id != null ? ($session->user->gov_id != null ? $row->governorate->defaultLang->name:""):"",
                $session->start_date != null ? $session->start_date->format('Y-m-d'):"N/A",
                $session->start_date != null ? $session->start_date->format('H:i:s'):"N/A",
                $session->end_date != null ? $session->end_date->format('H:i:s'):"N/A",
                $startOnline,
                $endOnline,
                "",
                $duration
//            $row->start_online == null ? "Offline":($row->start_online == true ? "Online":"Offline"),
//            $row->end_online == null ? "Offline":($row->end_online == true ? "Online":"Offline"),
            ];
        }
        return $fields;
    }

    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function(AfterSheet $event) {

                // Get the total number of rows
                $highestRow = $event->sheet->getHighestRow();
                $styleArray = array(
                    'font'  => array(
                        //  'bold'  => true,
                        'color' => array('rgb' => 'FF0000'),
                    ));
                // Loop through each row and apply conditional formatting
                for ($row = 2; $row <= $highestRow + 1; $row++) {
//                    $slaHours = $event->sheet->getCellByColumnAndRow(7, $row)->getValue();
//                    if( $slaHours < 700) {
//                        $event->sheet->getCellByColumnAndRow(2, $row)->getStyle()->applyFromArray($styleArray);
                    if($event->sheet->getCell('C' . $row)->getValue() == "Absent"){
                        $event->sheet->getStyle('C'.$row)->getFill()->applyFromArray(['fillType' => 'solid','rotation' => 0, 'color' => ['rgb' => 'FF0000']]);
                    }
                    $startOnline = $event->sheet->getCell('F' . $row)->getValue();
                    if ($startOnline == "Online") {
                        // Apply bold font to cell A in the current row
                        $event->sheet->getStyle('D'.$row)->getFill()->applyFromArray(['fillType' => 'solid','rotation' => 0, 'color' => ['rgb' => '92D050']]);
                    }elseif ($startOnline == "Offline") {
                        // Apply bold font to cell A in the current row
                        $event->sheet->getStyle('D'.$row)->getFill()->applyFromArray(['fillType' => 'solid','rotation' => 0, 'color' => ['rgb' => 'FF0000']]);
                    }
                    $endOnline = $event->sheet->getCell('G' . $row)->getValue();
                    if ($endOnline == "Online") {
                        // Apply bold font to cell A in the current row
                        $event->sheet->getStyle('E'.$row)->getFill()->applyFromArray(['fillType' => 'solid','rotation' => 0, 'color' => ['rgb' => '92D050']]);
                    }elseif ($endOnline == "Offline") {
                        // Apply bold font to cell A in the current row
                        $event->sheet->getStyle('E'.$row)->getFill()->applyFromArray(['fillType' => 'solid','rotation' => 0, 'color' => ['rgb' => 'FF0000']]);
                    }
                }
            },
        ];
    }

    public function columnFormats(): array
    {
        return [

        ];
    }

    public function columnWidths(): array
    {
        return [
            "A" => 30,
            "B" => 30,
            "C" => 30,
            "D" => 20,
            "E" => 20,
            "F" => 0,
            "G" => 0,
            "H" => 40,
            "I" => 20
        ];
    }
}
