<?php

namespace App\Http\Livewire\Pages\Dischargeport;

use Livewire\Component;
use Livewire\WithFileUploads;
use Illuminate\Support\Facades\DB;
use App\Models\MasterDischargePort;
use App\Models\MasterDischargePortCompare;

class DischargeportUpload extends Component
{
    use WithFileUploads;

    public $file;

    public function uploadFile()
    {
        $this->validate([
            'file' => 'required|file|mimes:txt,csv',
        ]);

        $filePath = $this->file->getRealPath();
        $file = fopen($filePath, 'r');

        MasterDischargePortCompare::truncate();

        while (($line = fgets($file)) !== false) {
            $isocode = substr($line, 0, 5);
            $portname = substr($line, 5, 100);
            $cntrycode = substr($line, 0, 2);
            $lastupdate = now();

            MasterDischargePortCompare::updateOrCreate(
                ['isocode' => $isocode],
                [
                    'portname' => htmlspecialchars(trim($portname)),
                    'cntrycode' => $cntrycode,
                    'usrname' => auth()->user()->name,
                    'lastupdate' => $lastupdate,
                ]
            );
        }

        fclose($file);

        $lastupdate = now();

        // update portname
        $updatePorts = DB::table('master_discharge_port_compare as c')
            ->leftJoin('master_discharge_port as p', 'c.isocode', '=', 'p.isocode')
            ->whereRaw("REPLACE(REPLACE(REPLACE(TRIM(c.portname),'&#039;','\''),'&amp;', '&'),'&quot;', '\"') <> p.portname")
            ->select('c.isocode', 'c.portname')
            ->get();

        foreach ($updatePorts as $updatePort) {
            DB::table('master_discharge_port')
                ->where('isocode', $updatePort->isocode)
                ->update([
                    'portname' => $updatePort->portname,
                    'lastupdate' => $lastupdate,
                ]);
        }

        // update flag
        $updateFlags = DB::table('master_discharge_port as p')
            ->leftJoin('master_discharge_port_compare as c', 'p.isocode', '=', 'c.isocode')
            ->where('p.flagstatus', 'Y')
            ->whereNotNull('c.isocode')
            ->select('c.isocode')
            ->get();

        foreach ($updateFlags as $updateFlag) {
            DB::table('master_discharge_port')
                ->where('isocode', $updateFlag->isocode)
                ->update([
                    'flagstatus' => null,
                    'lastupdate' => $lastupdate,
                ]);
        }

        // update delete flagstatus (Y is delete)
        $isoAll = DB::table('master_discharge_port')
            ->whereNotIn('isocode', function ($query) {
                $query->select('isocode')
                    ->from('master_discharge_port_compare');
            })
            ->pluck('isocode');

        if ($isoAll->isNotEmpty()) {
            DB::table('master_discharge_port')
                ->whereIn('isocode', $isoAll)
                ->update([
                    'flagstatus' => 'Y',
                    'lastupdate' => $lastupdate,
                ]);
        }

        // INSERT not have
        DB::table('master_discharge_port')
            ->insertUsing(['isocode', 'portname', 'cntrycode', 'usrname', 'lastupdate'], function ($query) {
                $query->select('t2.*')
                    ->from('master_discharge_port_compare as t2')
                    ->leftJoin('master_discharge_port as t1', 't1.isocode', '=', 't2.isocode')
                    ->whereNull('t1.isocode');
            });

        DB::table('master_discharge_port')
            ->where(function ($query) {
                $query->where('usrname', '')
                    ->orWhereNotNull('usrname');
            })
            ->update(['usrname' => null]);

        session()->flash('message', 'Upload File Success');
        return redirect()->route('main');
    }

    public function render()
    {
        return view('livewire.pages.dischargeport.dischargeport-upload');
    }
}