<?php

namespace App\Imports;

use App\Models\SpatialPlanning;
use Carbon\Carbon;
use DateTime;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Log;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;

class SpatialPlanningImport implements ToCollection, WithMultipleSheets
{
    /**
    * @param Collection $collection
    */
    public function collection(Collection $collection)
    {
        $months = [
            "Januari" => "January", "Februari" => "February", "Maret" => "March",
            "April" => "April", "Mei" => "May", "Juni" => "June",
            "Juli" => "July", "Agustus" => "August", "September" => "September",
            "Oktober" => "October", "November" => "November", "Desember" => "December"
        ];

        $collection->skip(2)->each(function ($row) use ($months) {
            if (empty(array_filter($row->toArray()))) {
                return;
            }

            if (!isset($row[6]) || empty($row[6])) {
                return;
            }

            if(!SpatialPlanning::where("nomor", $row[6])->exists()){
                $clean_nomor = str_replace('\\','',$row[6]);
                $date_string = isset($row[7]) ? trim($row[7]) : null;
                $clean_sp_date = null;
                if ($date_string) {
                    if(is_numeric($date_string)) {
                        $clean_sp_date = Carbon::createFromFormat('Y-m-d', '1900-01-01')->addDays($date_string - 2)->format('Y-m-d');
                    }else{
                        foreach ($months as $id => $en) {
                            $date_string = str_replace($id, $en, $date_string);
                        }
    
                        $formats = ['j F Y', 'd F Y', 'j-M-Y', 'd-M-Y'];

                        foreach ($formats as $format) {
                            $date = DateTime::createFromFormat($format, $date_string);
                            if ($date) {
                                $clean_sp_date = $date->format('Y-m-d');
                                break;
                            }
                        }
                    }
                }
                SpatialPlanning::create([
                    'name'          => $row[1],
                    'kbli'         => $row[2],
                    'kegiatan'       => $row[3],
                    'luas'     => $row[4],
                    'lokasi' => $row[5],
                    'nomor'        => $clean_nomor,
                    'sp_date' => $clean_sp_date,
                ]);
            }
        });
    }

    public function sheets(): array {
        return [
            0 => $this
        ];
    }
}