<?php

namespace App\Console\Commands;

use App\Models\User;
use App\Models\Employee;
use App\Models\Profile;
use App\Models\JobInfo;
use App\Models\BankInfo;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

class SyncEmployeeWithUsers extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'users:copy-to-employees';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Copy users with type "employee" or "admin" to the employees table with related data';

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        $this->info('Starting to copy users to employees table with data from related tables...');

        // Get all users with type 'employee' or 'admin'
        $users = User::where(function ($query) {
            $query->where('user_type', 'employee')
                ->orWhere('user_type', 'admin')
                ->orWhere('user_type', 'like', '%employee%')
                ->orWhere('user_type', 'like', '%admin%');
        })->get();

        
        $this->info("Found {$users->count()} users with type 'employee' or 'admin'");

        $created = 0;
        $skipped = 0;
        $errors = 0;

        // Begin transaction
        DB::beginTransaction();

        try {
            foreach ($users as $user) {
                // Check if employee record already exists for this user
                $employeeExists = Employee::where('user_id', $user->id)->exists();

                if (!$employeeExists) {
                    try {
                        // Get related data from profile table
                        $profile = Profile::where('user_id', $user->id)->first();

                        // Get related data from job_info table (if needed)
                        $jobInfo = JobInfo::where('user_id', $user->id)->first();

                        // Get related data from bank_info table (if needed)
                        $bankInfo = BankInfo::where('user_id', $user->id)->first();

                        // Prepare employee data
                        $employeeData = [
                            'user_id' => $user->id,
                            "is_active" => $user->is_active,
                            "organisation_id" => $user->organisation_id,
                            "email" => $user->email,
                        ];

                        // Add profile data if available
                        if ($profile) {
                            // Concatenate first name and last name
                            $name = trim(($profile->first_name ?? '') . ' ' . ($profile->last_name ?? ''));

                            $employeeData = array_merge($employeeData, [
                                'name' => $name ?: null,
                                'dob' => $profile->dob ?? null,
                                'gender' => $profile->gender ?? null,
                                'phone' => $profile->work_phone ?? $profile->mobile_phone ?? null,
                                'address' => $profile->address ?? null,
                                'photo' => $profile->photo ?? null,
                            ]);
                        }

                        // Add job info data if available and needed
                        if ($jobInfo) {
                            // Add job info fields as needed
                            $employeeData = array_merge($employeeData, [
                                'salary_type' => $jobInfo->pay_frequency ?? null,
                                'salary' => $jobInfo->pay_rate ?? null,
                                'company_doj' => $jobInfo->hire_date ?? today(),
                                'department_id' => $jobInfo->department_id ?? null,
                                'designation_id' => $jobInfo->designation_id ?? null,
                            ]);
                        }

                        // Add bank info data if available and needed
                        if ($bankInfo) {
                            $employeeData = array_merge($employeeData, [
                                'account_holder_name' => $bankInfo->account_name ?? null,
                                'account_number' => $bankInfo->account_number ?? null,
                                'bank_name' => $bankInfo->bank_name ?? null,
                                'bank_identifier_code' => $bankInfo->bank_code ?? null,
                                'branch_location' => $bankInfo->branch_location ?? null,
                            ]);
                        }

                        // Set default values for required fields if they're null
                        $employeeData = array_merge([
                            'company_doj' => today(),
                            'is_active' => 1,
                        ], $employeeData);

                        // Create new employee record
                        Employee::create($employeeData);

                        $created++;
                        $this->info("Created employee record for user ID: {$user->id} (type: {$user->user_type})");
                    } catch (\Exception $e) {
                        $errors++;
                        $this->warn("Error processing user ID {$user->id}: {$e->getMessage()}");
                        $this->warn("Stack trace: {$e->getTraceAsString()}");
                    }
                } else {
                    $skipped++;
                    $this->info("Skipped user ID: {$user->id} (employee record already exists)");
                }
            }

            // Commit transaction
            DB::commit();

            $this->info("Summary:");
            $this->info("- Successfully created {$created} employee records");
            $this->info("- Skipped {$skipped} existing employee records");
            $this->info("- Encountered {$errors} errors during processing");

            return Command::SUCCESS;
        } catch (\Exception $e) {
            // Roll back transaction on error
            DB::rollBack();

            $this->error("A critical error occurred: {$e->getMessage()}");
            $this->error("Stack trace: {$e->getTraceAsString()}");
            return Command::FAILURE;
        }
    }