<?php

namespace App\Exports;

use App\Models\Worker;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use Maatwebsite\Excel\Concerns\WithHeadings;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class WorkersExport implements FromQuery, WithHeadings, WithColumnFormatting, ShouldAutoSize
{
    /**
     * @return \Illuminate\Support\Collection
     */
    use Exportable;

    protected $company_id;
    protected $filters;

    public function __construct($id, $filters)
    {
        $this->company_id = $id;
        $this->filters = $filters;
    }

    public function query()
    {
        $company = $this->company_id;
        $filters = $this->filters;
        if ($filters['export_type'] == "company_workers") {
            return    Worker::query()
                ->select(
                    'workers.worker_id',
                    'workers.email_address',
                    DB::raw("CONCAT( '(', SUBSTR(workers.phone_num, 3, 3) , ')', SUBSTR(workers.phone_num, 6, 3), '-', SUBSTR(workers.phone_num, 9, 4)) as phone_num"),
                    'workers.first_name',
                    'workers.last_name',
                    'workers.street_address',
                    'workers.city',
                    'workers.state',
                    'workers.zipcode',
                    'workers.rate',
                    'workers.date_hired',
                    DB::raw("CONCAT(UPPER(SUBSTRING(company_users.availability_status, 1, 1)), LOWER(SUBSTRING(company_users.availability_status, 2))) as availability_status"),
                    'company_experiences.name as experience_name',
                    'expertises.name as expertise_name',
                    DB::raw("IFNULL(MIN(loan_borrow_lists.date_from),'N/A')"),
                    DB::raw("IFNULL(MAX(loan_borrow_lists.date_to),'N/A')"),
                    DB::raw("IF(company_users.company_id = " . $company . ",IF(loan_borrow_lists.borrower_company_id != " . $company . ",'Loaned Worker', 'Reserved' ),'Borrowed Worker') as type")
                )
                ->leftjoin('loan_borrow_lists', 'loan_borrow_lists.worker_id', '=', 'workers.id')
                ->join('company_users', 'company_users.worker_id', '=', 'workers.id')
                ->join('worker_experiences', 'worker_experiences.worker_id', '=', 'workers.id')
                ->join('company_experiences', 'company_experiences.id', '=', 'worker_experiences.expertise_id')
                ->join('expertises', 'expertises.id', '=', 'worker_experiences.expertise_id')
                ->with('work_area_lists:work_area_id,worker_id,name')
                ->with('roles:worker_id,role_id,name')
                ->where('company_users.company_id', $company)
                ->where('company_users.role_id', 5) // 5 = worker
                ->where('worker_experiences.is_worker', true)
                ->when($filters['dates'] == 'single_date', function ($query) use ($filters) {
                    return $query->Where(function ($query) use ($filters) {
                        $query->whereRaw('workers.date_hired <= date("' . Carbon::parse($filters['single_date'])->toDateString() . '")')
                            ->orWhere(function ($query) use ($filters) {
                                $query->whereRaw('loan_borrow_lists.date_to <= date("' . Carbon::parse($filters['single_date'])->toDateString() . '")');
                            });
                    });
                })
                ->when($filters['dates'] == 'date_range', function ($query) use ($filters) {
                    return $query->Where(function ($query) use ($filters) {
                        $query->whereBetween('workers.date_hired', [Carbon::parse($filters['date_hired_from']), Carbon::parse($filters['date_hired_to'])])
                            ->orWhere(function ($query) use ($filters) {
                                $query->whereRaw('loan_borrow_lists.date_from >= date("' . Carbon::parse($filters['date_hired_from'])->toDateString() . '")')
                                    ->orWhereRaw('loan_borrow_lists.date_from <= date("' . Carbon::parse($filters['date_hired_to'])->toDateString() . '")');
                            });
                    });
                })
                ->groupBy('workers.id');
        } elseif ($filters['export_type'] == "all") {
            return Worker::query()
                ->select(
                    'workers.worker_id',
                    'workers.email_address',
                    DB::raw("CONCAT( '(', SUBSTR(workers.phone_num, 3, 3) , ')', SUBSTR(workers.phone_num, 6, 3), '-', SUBSTR(workers.phone_num, 9, 4)) as phone_num"),
                    'workers.first_name',
                    'workers.last_name',
                    'workers.street_address',
                    'workers.city',
                    'workers.state',
                    'workers.zipcode',
                    'workers.rate',
                    'workers.date_hired',
                    DB::raw("CONCAT(UPPER(SUBSTRING(company_users.availability_status, 1, 1)), LOWER(SUBSTRING(company_users.availability_status, 2))) as availability_status"),
                    'company_experiences.name as experience_name',
                    'expertises.name as expertise_name',
                    DB::raw("IFNULL(MIN(loan_borrow_lists.date_from),'N/A')"),
                    DB::raw("IFNULL(MAX(loan_borrow_lists.date_to),'N/A')"),
                    DB::raw("IF(company_users.company_id = " . $company . ",IF(loan_borrow_lists.borrower_company_id != " . $company . ",'Loaned Worker', 'Reserved' ),'Borrowed Worker') as type")
                )
                ->leftjoin('loan_borrow_lists', 'loan_borrow_lists.worker_id', '=', 'workers.id')
                ->join('company_users', 'company_users.worker_id', '=', 'workers.id')
                ->join('worker_experiences', 'worker_experiences.worker_id', '=', 'workers.id')
                ->join('company_experiences', 'company_experiences.id', '=', 'worker_experiences.experience_id')
                ->join('expertises', 'expertises.id', '=', 'worker_experiences.expertise_id')
                ->with('work_area_lists:work_area_id,worker_id,name')
                ->with('roles:worker_id,role_id,name')
                ->where('company_users.role_id', 5) // 5 = worker
                ->where('worker_experiences.is_worker', true)
                ->where(function ($query) use ($company) {
                    return $query
                        ->where('company_users.company_id', $company)
                        ->orWhere('loan_borrow_lists.borrower_company_id', $company);
                })
                ->when($filters['dates'] == 'single_date', function ($query) use ($filters) {
                    return $query->Where(function ($query) use ($filters) {
                        $query->whereRaw('workers.date_hired <= date("' . Carbon::parse($filters['single_date'])->toDateString() . '")')
                            ->orWhere(function ($query) use ($filters) {
                                $query->whereRaw('loan_borrow_lists.date_to <= date("' . Carbon::parse($filters['single_date'])->toDateString() . '")');
                            });
                    });
                })
                ->when($filters['dates'] == 'date_range', function ($query) use ($filters) {
                    return $query->Where(function ($query) use ($filters) {
                        $query->whereBetween('workers.date_hired', [Carbon::parse($filters['date_hired_from']), Carbon::parse($filters['date_hired_to'])])
                            ->orWhere(function ($query) use ($filters) {
                                $query->whereRaw('loan_borrow_lists.date_from >= date("' . Carbon::parse($filters['date_hired_from'])->toDateString() . '")')
                                    ->orWhereRaw('loan_borrow_lists.date_from <= date("' . Carbon::parse($filters['date_hired_to'])->toDateString() . '")');
                            });
                    });
                })
                ->groupBy('workers.id');
        } elseif ($filters['export_type'] == "borrowed") {
            return Worker::query()
                ->select(
                    'workers.worker_id',
                    'workers.email_address',
                    DB::raw("CONCAT( '(', SUBSTR(workers.phone_num, 3, 3) , ')', SUBSTR(workers.phone_num, 6, 3), '-', SUBSTR(workers.phone_num, 9, 4)) as phone_num"),
                    'workers.first_name',
                    'workers.last_name',
                    'workers.street_address',
                    'workers.city',
                    'workers.state',
                    'workers.zipcode',
                    'workers.rate',
                    'workers.date_hired',
                    DB::raw("CONCAT(UPPER(SUBSTRING(company_users.availability_status, 1, 1)), LOWER(SUBSTRING(company_users.availability_status, 2))) as availability_status"),
                    'company_experiences.name as experience_name',
                    'expertises.name as expertise_name',
                    DB::raw("IFNULL(MIN(loan_borrow_lists.date_from), 'N/A')"),
                    DB::raw("IFNULL(MAX(loan_borrow_lists.date_to), 'N/A')"),
                    DB::raw("IF(company_users.company_id = " . $company . ",IF(loan_borrow_lists.borrower_company_id != " . $company . ",'Loaned Worker', 'Reserved' ),'Borrowed Worker') as type")
                )
                ->join('loan_borrow_lists', 'loan_borrow_lists.worker_id', '=', 'workers.id')
                ->join('company_users', 'company_users.worker_id', '=', 'workers.id')
                ->join('worker_experiences', 'worker_experiences.worker_id', '=', 'workers.id')
                ->join('company_experiences', 'company_experiences.id', '=', 'worker_experiences.experience_id')
                ->join('expertises', 'expertises.id', '=', 'worker_experiences.expertise_id')
                ->with('work_area_lists:work_area_id,worker_id,name')
                ->with('roles:worker_id,role_id,name')
                ->where('company_users.role_id', 5) // 5 = worker
                ->where('worker_experiences.is_worker', true)
                ->where('loan_borrow_lists.borrower_company_id', $company)
                ->when($filters['dates'] == 'single_date', function ($query) use ($filters) {
                    return $query->WhereRaw('loan_borrow_lists.date_to <= date("' . Carbon::parse($filters['single_date'])->toDateString() . '")');

                })
                ->when($filters['dates'] == 'date_range', function ($query) use ($filters) {
                    return $query->Where(function ($query) use ($filters) {
                        $query->whereRaw('loan_borrow_lists.date_from >= date("' . Carbon::parse($filters['date_hired_from'])->toDateString() . '")')
                            ->orWhereRaw('loan_borrow_lists.date_from <= date("' . Carbon::parse($filters['date_hired_to'])->toDateString() . '")');
                    });
                })
                ->groupBy('workers.id');
        }
    }

    public function headings(): array
    {
        return [
            "Worker ID",
            "Email Address",
            "Mobile Number",
            "First Name",
            "Last Name",
            "Street",
            "City",
            "State",
            "Zipcode",
            "Rate",
            "Date Hired",
            "Conx Status",
            "Experience",
            "Expertise",
            "Date Start of Work",
            "Date End of work",
            "Worker Type",
        ];
    }
    public function columnFormats(): array
    {
        return [
            'A' => NumberFormat::FORMAT_TEXT,
            'B' => NumberFormat::FORMAT_TEXT,
            'B' => NumberFormat::FORMAT_TEXT,
            'C' => NumberFormat::FORMAT_TEXT,
            'D' => NumberFormat::FORMAT_TEXT,
            'E' => NumberFormat::FORMAT_TEXT,
            'F' => NumberFormat::FORMAT_TEXT,
            'G' => NumberFormat::FORMAT_TEXT,
            'H' => NumberFormat::FORMAT_TEXT,
            'I' => NumberFormat::FORMAT_TEXT,
            'J' => NumberFormat::FORMAT_TEXT,
            'K' => NumberFormat::FORMAT_DATE_DDMMYYYY,
            'L' => NumberFormat::FORMAT_TEXT,
            'M' => NumberFormat::FORMAT_TEXT,
            'N' => NumberFormat::FORMAT_TEXT,
            'O' => NumberFormat::FORMAT_DATE_DDMMYYYY,
            'P' => NumberFormat::FORMAT_DATE_DDMMYYYY,
        ];
    }
}