<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Hash;

class TransferDataController extends Controller
{

    public function transferUserData()
    {
        set_time_limit(0); 
        $batchSize = 100; // Number of records to process at a time
        $offset = 0;

        while (true) {
            $oldUsers = DB::table('tab_user')->select([
                'UID',
                DB::raw('CONVERT(UCODE USING utf8) AS UCODE'),
                DB::raw('CONVERT(USERNAME USING utf8) AS USERNAME'),
                DB::raw('CONVERT(PASSWORD USING utf8) AS PASSWORD'),
                DB::raw('CONVERT(FIRSTNAME USING utf8) AS FIRSTNAME'),
                DB::raw('CONVERT(LASTNAME USING utf8) AS LASTNAME'),
                DB::raw('CONVERT(EMAIL USING utf8) AS EMAIL'),
                DB::raw('CONVERT(PHONE USING utf8) AS PHONE'),
                'APPROVED',
                'APPROVEDATE',
                'STATUSLOGIN',
                'LASTLOGIN',
                DB::raw('CONVERT(ACTIVITY USING utf8) AS ACTIVITY'),
                'ACTIVITY_TIME',
                DB::raw('CONVERT(PAYMENT_USER USING utf8) AS PAYMENT_USER'),
                DB::raw('CONVERT(PAYMENT_PASS USING utf8) AS PAYMENT_PASS'),
                DB::raw('CONVERT(PAYMENT_ORG USING utf8) AS PAYMENT_ORG'),
                DB::raw('CONVERT(BUCODE USING utf8) AS BUCODE'),
            ])->offset($offset)
                ->limit($batchSize)
                ->get();

            if ($oldUsers->isEmpty()) {
                break; // No more records to process
            }
            foreach ($oldUsers as $oldUser) {
                // if (is_null($oldUser->EMAIL)) {
                //     // Skip this record if email is NULL
                //     continue;
                // }

                try {

                    DB::table('users')->insert([
                        'uid' => $oldUser->UID,
                        'user_code' => $oldUser->UCODE,
                        'username' => $oldUser->USERNAME,
                        'password' => Hash::make($oldUser->PASSWORD),
                        'first_name' => $oldUser->FIRSTNAME,
                        'last_name' => $oldUser->LASTNAME,
                        'email' => $oldUser->EMAIL ?? '',
                        'phone' => $oldUser->PHONE,
                        'is_approved' => $oldUser->APPROVED === 'Y' ? true : false,
                        'approved_at' => isset($oldUser->APPROVEDDATE) ? $oldUser->APPROVEDDATE : null,
                        'status_login' => $oldUser->STATUSLOGIN,
                        'last_login_at' => isset($oldUser->LASTLOGIN) ? $oldUser->LASTLOGIN : null,
                        'activity' => isset($oldUser->ACTIVITY) ? $oldUser->ACTIVITY : null,
                        'activity_time' => isset($oldUser->ACTIVITY_TIME) ? $oldUser->ACTIVITY_TIME : null,
                        'payment_username' => isset($oldUser->PAYMENT_USER) ? $oldUser->PAYMENT_USER : null,
                        'payment_password' => isset($oldUser->PAYMENT_PASS) ? Hash::make($oldUser->PAYMENT_PASS) : null,
                        'payment_organization' => isset($oldUser->PAYMENT_ORG) ? $oldUser->PAYMENT_ORG : null,
                        'bu_code' => isset($oldUser->BUCODE) ? $oldUser->BUCODE : null,
                        'created_at' => now(),
                        'updated_at' => now(),
                        'deleted_at' => null,
                    ]);
                } catch (\Illuminate\Database\QueryException $ex) {
                    if ($ex->getCode() == 23000) {
                        continue; // Skip to the next iteration if duplicate entry
                    } else {
                        throw $ex;
                    }
                }
            }

            $offset += $batchSize; // Move to the next batch
        }

        return "Data transferred successfully!";
    }
}