<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class MigrateUserKtp extends Command
{
    protected $signature = 'lms:migrate-ktp';
    protected $description = 'Migrasi data nomor KTP dari adhar_no atau note berdasarkan Email/Nama';

    public function handle()
    {
        $this->info('Memulai pemetaan Nomor KTP (Identity Number)...');
        
        // Ambil dari database lama yang adhar_no ATAU note-nya ada isinya
        $oldStudents = DB::table('lmsv2-old.students')
            ->where(function($query) {
                $query->whereNotNull('adhar_no')->where('adhar_no', '!=', '')
                      ->orWhereNotNull('note')->where('note', '!=', '');
            })->get();
        
        $bar = $this->output->createProgressBar(count($oldStudents));
        $bar->start();

        $successCount = 0;
        $failCount = 0;

        foreach ($oldStudents as $oldStudent) {
            $old = (array) $oldStudent;
            
            // Prioritaskan adhar_no, jika kosong gunakan note
            $ktpNumber = !empty($old['adhar_no']) ? $old['adhar_no'] : $old['note'];
            
            $matchedUser = null;

            // PRIORITAS 1: Pencocokan Email
            if (!empty($old['email'])) {
                $matchedUser = DB::table('lmsv2.users')->where('email', $old['email'])->first();
            }

            // PRIORITAS 2: Pencocokan Nama
            if (!$matchedUser) {
                $query = DB::table('lmsv2.users');
                
                if (!empty($old['first_name'])) {
                    $query->where('first_name', $old['first_name']);
                    if (!empty($old['last_name'])) {
                        $query->where('last_name', $old['last_name']);
                    }
                } elseif (!empty($old['name'])) {
                    $query->where(DB::raw("CONCAT(first_name, ' ', COALESCE(last_name, ''))"), 'LIKE', "%{$old['name']}%");
                } else {
                    $query->where('id', '<', 0); // Skip jika data nama rusak
                }
                
                $matchedUser = $query->first();
            }

            // JIKA KETEMU: Update kolom identity_number
            if ($matchedUser) {
                DB::table('lmsv2.users')
                    ->where('id', $matchedUser->id)
                    ->update(['identity_number' => $ktpNumber]);
                
                $successCount++;
            } else {
                $failCount++;
            }

            $bar->advance();
        }

        $bar->finish();
        $this->newLine();
        $this->info("Pembaruan KTP Selesai! Berhasil terpetakan: {$successCount} data. Tidak ditemukan (Nama/Email beda): {$failCount} data.");
    }
}