<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use Maatwebsite\Excel\Concerns\WithCustomValueBinder;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Cell\StringValueBinder;
use PhpOffice\PhpSpreadsheet\Cell\DataType;

use App\Models\Registration\Registration;
use App\Models\Tools\GasTools;
use App\Models\Tools\Fuels;

class RegisterExport implements FromCollection, WithMapping, WithHeadings, WithColumnFormatting, WithCustomValueBinder
{

    protected $request;
    protected $action;
    protected $counter = 1;
    protected $gasTools = [];
    protected $fuels = [];

    public function __construct($request, $action = 'register-list')
    {
        $this->request = $request;
        $this->action = $action;
        $this->gasTools = GasTools::where('is_deleted', 0)->orderBy('sort', 'ASC')->get();
        $this->fuels = Fuels::where('is_deleted', 0)->orderBy('sort', 'ASC')->get();
    }

    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        $data = Registration::with([
            'province',
            'town',
            'district',
            'village',
            'housingArea',
            'buildingType',
            'statusLocation',
            'electricalPower',
            'fuelUsages', 
            'gasToolUsages'
        ]);

        if ($this->action == 'register-log') {
            // $data = $data->where('is_send', 0);
        }

        if ($this->request->has('search')) {
            $data = $data->where('nama', 'like', '%'.$this->request->search.'%')
                ->orWhere('ktp', 'like', '%'.$this->request->search.'%')
                ->orWhere('no_telpon', 'like', '%'.$this->request->search.'%')
                ->orWhere('email', 'like', '%'.$this->request->search.'%');
        }

        return $data->orderBy('created_at', 'DESC')->get();
    }

    public function map($row): array
    {
        $data = [];

        if ($row) {
            $data = [
                'No' => $this->counter++,
                'Nama' => $row->nama,
                'No KTP' => $row->ktp,
                'No Handphone' => $row->no_telpon,
                'Email' => $row->email,
                'Kebutuhan Pelanggan' => $row->kebutuhan_pelanggan_text,
                'KTP File' => env('CDN_URL').$row->ktp_url_relyon,
                'Rekening Listrik File' => env('CDN_URL').$row->rekening_listrik_url_relyon,
                'Provinsi' => !empty($row->province) ? $row->province->name : '',
                'Kota/Kabupaten' => !empty($row->town) ? $row->town->name : '',
                'Kecamatan' => !empty($row->district) ? $row->district->name : '',
                'kelurahan' => !empty($row->village) ? $row->village->name : '',
                'Perumahan/Jalan Utama' => !empty($row->housingArea) ? $row->housingArea->name : '',
                'Nama Perumahan/Jalan Utama' => $row->nama_perumahan_jalan_utama,
                'RT' => $row->rt,
                'RW' => $row->rw,
                'Kode Pos' => $row->kode_pos,
                'Alamat Lengkap Lokasi Pemasangan' => $row->alamat,
                'Jenis Bangunan' => !empty($row->buildingType) ? $row->buildingType->name : '',
                'Status Lokasi Pemasangan' => $row->status_lokasi_pemasangan == 1 ? 'Milik Pemerintah' : 'Milik pribadi',
                'Latitude' => $row->Latitude,
                'Longitude' => $row->Longitude,
                'Status Kepemilikan' => !empty($row->statusLocation) ? $row->statusLocation->name : '',
                'Nama Pemilik Bangunan' => $row->nama_pemilik_bangunan,
                'No Telpon/HP Pemilik Bangunan' => $row->no_telpon_pemilik_bangunan,
                'Penggunaan Energi Listrik' => !empty($row->electricalPower) ? $row->electricalPower->value : '',
            ];

            foreach ($this->fuels as $key => $val) {
                $temp_val = '0';

                foreach ($row->fuelUsages as $key_2 => $val_2) {
                    if ($val->id == $val_2->fuel_id) {
                        $temp_val = $val_2->value_unit;
                    }
                }

                array_push($data, $temp_val);
            }
    
            foreach ($this->gasTools as $key => $val) {
                $temp_val = '0';

                foreach ($row->gasToolUsages as $key_2 => $val_2) {
                    if ($val->id == $val_2->gas_tool_id) {
                        $temp_val = $val_2->amount;
                    }
                }

                array_push($data, $temp_val);
            }
        }
    
        return $data;
    }

    public function headings(): array
    {
        $headers = [
            'No',
            'Nama',
            'No KTP',
            'No Handphone',
            'Email',
            'Kebutuhan Pelanggan',
            'KTP File',
            'Rekening Listrik File',
            'Provinsi',
            'Kota/Kabupaten',
            'Kecamatan',
            'kelurahan',
            'Perumahan/Jalan Utama',
            'Nama Perumahan/Jalan Utama',
            'RT',
            'RW',
            'Kode Pos',
            'Alamat Lengkap Lokasi Pemasangan',
            'Jenis Bangunan',
            'Status Lokasi Pemasangan',
            'Latitude',
            'Longitude',
            'Status Kepemilikan',
            'Nama Pemilik Bangunan',
            'No Telpon/HP Pemilik Bangunan',
            'Penggunaan Energi Listrik',
        ];

        foreach ($this->fuels as $key => $val) {
            array_push($headers, $val->name);
        }

        foreach ($this->gasTools as $key => $val) {
            array_push($headers, $val->name);
        }

        return $headers;
    }

    public function columnFormats(): array
    {
        return [
            'A' => NumberFormat::FORMAT_NUMBER,
            'B' => NumberFormat::FORMAT_NUMBER,
            'C' => NumberFormat::FORMAT_NUMBER
        ];
    }

    public function bindValue(\PhpOffice\PhpSpreadsheet\Cell\Cell $cell, $value)
    {
        $cell->setValueExplicit($value, DataType::TYPE_STRING);
        return true;
    }
}
