<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use App\Modules\HR\Core\Models\Employee;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Facades\Excel;
use PhpOffice\PhpSpreadsheet\IOFactory;

class UpdateEmployeeCodes extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'app:update-employee-codes {--dry-run : Preview changes without committing to the database}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Update employee codes from public/empno.xlsx';

    /**
     * Execute the console command.
     */
    public function handle()
    {
        $filePath = public_path('empno.xlsx');

        if (!file_exists($filePath)) {
            $this->error("File not found: {$filePath}");
            return Command::FAILURE;
        }

        $dryRun = $this->option('dry-run');

        if ($dryRun) {
            $this->info("Running in DRY RUN mode. No changes will be committed.");
        }

        try {
            $spreadsheet = IOFactory::load($filePath);
            $worksheet = $spreadsheet->getActiveSheet();
            $rows = $worksheet->toArray(null, true, true, true);

            $this->info("Processing rows...");
            $updatedCount = 0;
            $notFoundCount = 0;
            $errorCount = 0;

            DB::beginTransaction();

            // Data starts at Row 3 based on preliminary check
            // Row 1: Headers (Old Emp No, New Emp No)
            // Row 2: Empty
            foreach ($rows as $index => $row) {
                if ($index < 3) continue;

                $oldCode = trim($row['B']);
                $newCode = trim($row['A']);

                if (empty($oldCode)) {
                    continue;
                }

                $employee = Employee::where('employee_code', $oldCode)->first();

                if (!$employee) {
                    $this->warn("Row {$index}: Employee with code '{$oldCode}' not found.");
                    $notFoundCount++;
                    continue;
                }

                if ($employee->employee_code === $newCode) {
                    $this->line("Row {$index}: Employee '{$oldCode}' already has the new code.");
                    continue;
                }

                $this->line("Row {$index}: Updating '{$oldCode}' to '{$newCode}'...");

                try {
                    if (!$dryRun) {
                        $employee->update(['employee_code' => $newCode]);
                    }
                    $updatedCount++;
                } catch (\Exception $e) {
                    $this->error("Row {$index}: Failed to update '{$oldCode}'. Error: " . $e->getMessage());
                    $errorCount++;
                    if (!$dryRun) {
                        DB::rollBack();
                        return Command::FAILURE;
                    }
                }
            }

            if ($dryRun) {
                DB::rollBack();
                $this->info("Dry run completed.");
            } else {
                DB::commit();
                $this->info("Update completed successfully.");
            }

            $this->table(
                ['Category', 'Count'],
                [
                    ['Updated', $updatedCount],
                    ['Not Found', $notFoundCount],
                    ['Errors', $errorCount],
                ]
            );

            return Command::SUCCESS;

        } catch (\Exception $e) {
            $this->error("An error occurred: " . $e->getMessage());
            if (!$dryRun) {
                DB::rollBack();
            }
            return Command::FAILURE;
        }
    }
}
