<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date as ExcelDate;

class ImportBoralesgamuwaEmployees extends Command
{
    protected $signature = 'employees:import-boralesgamuwa
                            {--step=all : Which step to run: staging|employees|all}
                            {--force : Re-import even if already imported}';

    protected $description = 'Import boralesgamuwa.xlsx employee data into staging tables then into the employees table';

    private int $branchId = 1; // Head Office

    public function handle(): int
    {
        $step = $this->option('step');

        if ($step === 'staging' || $step === 'all') {
            $this->importToStagingTables();
        }

        if ($step === 'employees' || $step === 'all') {
            $this->ensureLookupRecords();
            $this->insertEmployees();
        }

        return Command::SUCCESS;
    }

    // ─────────────────────────────────────────────
    // STEP 1 – Load Excel sheets into staging tables
    // ─────────────────────────────────────────────

    private function importToStagingTables(): void
    {
        $file = public_path('boralesgamuwa.xlsx');

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

        $this->info('Loading Excel file…');
        $spreadsheet = IOFactory::load($file);

        $sheets = [
            'Head Office' => [
                'table'   => 'import_head_office',
                'mapping' => $this->headOfficeMapping(),
            ],
            'Sales Team' => [
                'table'   => 'import_sales_team',
                'mapping' => $this->salesTeamMapping(),
            ],
            'Lid Factory' => [
                'table'   => 'import_lid_factory',
                'mapping' => $this->lidFactoryMapping(),
            ],
        ];

        foreach ($sheets as $sheetName => $config) {
            $sheet = $spreadsheet->getSheetByName($sheetName);

            if (! $sheet) {
                $this->warn("Sheet '{$sheetName}' not found, skipping.");
                continue;
            }

            $maxRow = $sheet->getHighestRow();
            $rows   = 0;
            $skipped = 0;

            $this->info("Importing sheet '{$sheetName}' ({$maxRow} rows)…");
            $bar = $this->output->createProgressBar($maxRow - 1);
            $bar->start();

            for ($row = 2; $row <= $maxRow; $row++) {
                $empNo = trim((string) $sheet->getCell($config['mapping']['emp_no'] . $row)->getValue());

                // skip blank rows
                if ($empNo === '') {
                    $skipped++;
                    $bar->advance();
                    continue;
                }

                // skip if already imported (unless --force)
                if (! $this->option('force') && DB::table($config['table'])->where('emp_no', $empNo)->exists()) {
                    $skipped++;
                    $bar->advance();
                    continue;
                }

                $record = $this->buildStagingRecord($sheet, $row, $config['mapping']);
                DB::table($config['table'])->updateOrInsert(['emp_no' => $empNo], $record);
                $rows++;
                $bar->advance();
            }

            $bar->finish();
            $this->newLine();
            $this->line("  → {$rows} imported, {$skipped} skipped.");
        }
    }

    private function buildStagingRecord($sheet, int $row, array $map): array
    {
        $get = fn(string $col) => $col ? trim((string) $sheet->getCell($col . $row)->getValue()) : null;

        return [
            'serial_no'             => $get($map['serial_no']) ?: null,
            'emp_no'                => $get($map['emp_no'])     ?: null,
            'epf_no'                => $get($map['epf_no'])     ?: null,
            'id_name'               => $get($map['id_name'])    ?: null,
            'calling_name'          => $get($map['calling_name']) ?: null,
            'initials_with_surname' => $get($map['initials_with_surname']) ?: null,
            'initials'              => $get($map['initials'])   ?: null,
            'surname'               => $get($map['surname'])    ?: null,
            'sex'                   => $get($map['sex'])        ?: null,
            'designation'           => $get($map['designation']) ?: null,
            'department'            => $get($map['department']) ?: null,
            'nic_no'                => $get($map['nic_no'])     ?: null,
            'status_of_appointment' => $get($map['status_of_appointment']) ?: null,
            'date_joined'           => $get($map['date_joined']) ?: null,
            'date_of_birth'         => $get($map['date_of_birth']) ?: null,
            'retirement_age'        => $get($map['retirement_age']) ?: null,
            'permanent_address'     => $get($map['permanent_address']) ?: null,
            'telephone_number'      => $get($map['telephone_number']) ?: null,
            'bank_acc_name'         => $get($map['bank_acc_name']) ?: null,
            'bank_name_code'        => $get($map['bank_name_code']) ?: null,
            'bank_branch_name_code' => $get($map['bank_branch_name_code']) ?: null,
            'bank_acc_no'           => $get($map['bank_acc_no']) ?: null,
            'b_card'                => $get($map['b_card'])     ?: null,
            'date_of_resignation'   => $get($map['date_of_resignation']) ?: null,
            'imported'              => false,
            'created_at'            => now(),
            'updated_at'            => now(),
        ];
    }

    // Column maps per sheet
    // Head Office: A=serial, B=emp_no, C=epf_no, D=id_name, E=calling_name,
    //              F=initials_surname, G=initials, H=surname, I=sex,
    //              J=designation, K=department, L=nic_no, M=status,
    //              N=date_joined, O=dob, P=retirement_age,
    //              R=address, S=phone, T=bank_acc_name, U=bank_name_code,
    //              X=bank_branch, Y=bank_acc_no, Z=b_card, AD=date_resign
    private function headOfficeMapping(): array
    {
        return [
            'serial_no'             => 'A',
            'emp_no'                => 'B',
            'epf_no'                => 'C',
            'id_name'               => 'D',
            'calling_name'          => 'E',
            'initials_with_surname' => 'F',
            'initials'              => 'G',
            'surname'               => 'H',
            'sex'                   => 'I',
            'designation'           => 'J',
            'department'            => 'K',
            'nic_no'                => 'L',
            'status_of_appointment' => 'M',
            'date_joined'           => 'N',
            'date_of_birth'         => 'O',
            'retirement_age'        => 'P',
            'permanent_address'     => 'R',
            'telephone_number'      => 'S',
            'bank_acc_name'         => 'T',
            'bank_name_code'        => 'U',
            'bank_branch_name_code' => 'X',
            'bank_acc_no'           => 'Y',
            'b_card'                => 'Z',
            'date_of_resignation'   => 'AD',
        ];
    }

    // Sales Team / Lid Factory: A=serial, B=emp_no, C=epf_no, E=id_name, F=calling_name,
    //   G=initials_surname, H=initials, I=surname, J=sex,
    //   K=designation, L=department, M=nic_no, N=status,
    //   O=date_joined, P=dob, Q=retirement_age, S=address,
    //   T=phone, U=bank_acc_name, V=bank_name_code,
    //   Y=bank_branch, Z=bank_acc_no, AA=b_card, AE=date_resign
    private function salesTeamMapping(): array
    {
        return [
            'serial_no'             => 'A',
            'emp_no'                => 'B',
            'epf_no'                => 'C',
            'id_name'               => 'E',
            'calling_name'          => 'F',
            'initials_with_surname' => 'G',
            'initials'              => 'H',
            'surname'               => 'I',
            'sex'                   => 'J',
            'designation'           => 'K',
            'department'            => 'L',
            'nic_no'                => 'M',
            'status_of_appointment' => 'N',
            'date_joined'           => 'O',
            'date_of_birth'         => 'P',
            'retirement_age'        => 'Q',
            'permanent_address'     => 'S',
            'telephone_number'      => 'T',
            'bank_acc_name'         => 'U',
            'bank_name_code'        => 'V',
            'bank_branch_name_code' => 'Y',
            'bank_acc_no'           => 'Z',
            'b_card'                => 'AA',
            'date_of_resignation'   => 'AE',
        ];
    }

    private function lidFactoryMapping(): array
    {
        return $this->salesTeamMapping(); // identical column layout
    }

    // ─────────────────────────────────────────────
    // STEP 2 – Ensure all required lookup records exist
    // ─────────────────────────────────────────────

    private function ensureLookupRecords(): void
    {
        $this->info('Ensuring lookup records…');

        // Missing departments from Excel
        $missingDepts = ['Sales', 'Production'];
        foreach ($missingDepts as $name) {
            if (! DB::table('departments')->where('name', $name)->where('branch_id', $this->branchId)->exists()) {
                DB::table('departments')->insert([
                    'branch_id'  => $this->branchId,
                    'code'       => $name,
                    'name'       => $name,
                    'status'     => 1,
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);
                $this->line("  + Department created: {$name}");
            }
        }

        // Missing designations from Excel
        $missingDesig = ['Area Manager', 'Watcher'];
        foreach ($missingDesig as $name) {
            if (! DB::table('designations')->where('name', $name)->where('branch_id', $this->branchId)->exists()) {
                DB::table('designations')->insert([
                    'branch_id'    => $this->branchId,
                    'code'         => $name,
                    'name'         => $name,
                    'level'        => 1,
                    'budget'       => 0,
                    'is_managerial'=> 0,
                    'status'       => 1,
                    'created_at'   => now(),
                    'updated_at'   => now(),
                ]);
                $this->line("  + Designation created: {$name}");
            }
        }

        // Grade fallback "-"
        if (! DB::table('grades')->where('name', '-')->where('branch_id', $this->branchId)->exists()) {
            DB::table('grades')->insert([
                'branch_id'   => $this->branchId,
                'name'        => '-',
                'min_salary'  => 0,
                'max_salary'  => 0,
                'status'      => 1,
                'created_at'  => now(),
                'updated_at'  => now(),
            ]);
            $this->line('  + Grade created: -');
        }

        // Team fallback "-"
        if (! DB::table('teams')->where('name', '-')->where('branch_id', $this->branchId)->exists()) {
            DB::table('teams')->insert([
                'branch_id'  => $this->branchId,
                'name'       => '-',
                'status'     => 1,
                'created_at' => now(),
                'updated_at' => now(),
            ]);
            $this->line('  + Team created: -');
        }

        // ShiftSegment fallback "-"
        if (! DB::table('shift_segments')->where('name', '-')->where('branch_id', $this->branchId)->exists()) {
            DB::table('shift_segments')->insert([
                'branch_id'      => $this->branchId,
                'name'           => '-',
                'effective_from' => '2000-01-01',
                'status'         => 1,
                'created_at'     => now(),
                'updated_at'     => now(),
            ]);
            $this->line('  + Shift segment created: -');
        }

        // MaritalStatus fallback "-"
        if (! DB::table('marital_statuses')->where('name', '-')->where('branch_id', $this->branchId)->exists()) {
            DB::table('marital_statuses')->insert([
                'branch_id'  => $this->branchId,
                'name'       => '-',
                'status'     => 1,
                'created_at' => now(),
                'updated_at' => now(),
            ]);
            $this->line('  + Marital status created: -');
        }

        $this->info('Lookup records ready.');
    }

    // ─────────────────────────────────────────────
    // STEP 3 – Transfer staging rows → employees
    // ─────────────────────────────────────────────

    private function insertEmployees(): void
    {
        $this->info('Building lookup caches…');

        // Build caches (lowercase name → id)
        $genders      = $this->buildCache('genders', 'name', 'id');
        $departments  = $this->buildCache('departments', 'name', 'id');
        $designations = $this->buildCache('designations', 'name', 'id');
        $empTypes     = $this->buildCache('employment_types', 'name', 'id');
        $grades       = $this->buildCache('grades', 'name', 'id');
        $teams        = $this->buildCache('teams', 'name', 'id');
        $segments     = $this->buildCache('shift_segments', 'name', 'id');
        $maritalStats = $this->buildCache('marital_statuses', 'name', 'id');

        $defaultGradeId     = $grades['-']           ?? null;
        $defaultTeamId      = $teams['-']            ?? null;
        $defaultSegmentId   = $segments['-']         ?? null;
        $defaultMaritalId   = $maritalStats['-']     ?? ($maritalStats['single'] ?? 1);
        $defaultNatId       = 1; // Sri Lanka

        $tables = [
            'import_head_office',
            'import_sales_team',
            'import_lid_factory',
        ];

        $inserted = 0;
        $skipped  = 0;
        $errors   = [];

        foreach ($tables as $table) {
            $rows = DB::table($table)->where('imported', false)->get();
            $this->info("Processing {$rows->count()} rows from {$table}…");
            $bar = $this->output->createProgressBar($rows->count());
            $bar->start();

            foreach ($rows as $row) {
                try {
                    $empCode = (string) $row->emp_no;

                    if (! $empCode) {
                        $skipped++;
                        $bar->advance();
                        continue;
                    }

                    // Skip duplicates already in employees
                    if (DB::table('employees')->where('branch_id', $this->branchId)->where('employee_code', $empCode)->exists()) {
                        DB::table($table)->where('id', $row->id)->update(['imported' => true]);
                        $skipped++;
                        $bar->advance();
                        continue;
                    }

                    $genderId      = $genders[strtolower(trim($row->sex ?? ''))] ?? ($genders['male'] ?? 1);
                    $deptId        = $departments[strtolower(trim($row->department ?? ''))] ?? null;
                    $desigId       = $designations[strtolower(trim($row->designation ?? ''))] ?? null;
                    $empTypeId     = $empTypes[strtolower(trim($row->status_of_appointment ?? ''))] ?? null;

                    if (! $deptId || ! $desigId || ! $empTypeId) {
                        $errors[] = "Row {$row->id} ({$table}): missing FK — dept={$row->department}, desig={$row->designation}, status={$row->status_of_appointment}";
                        $skipped++;
                        $bar->advance();
                        continue;
                    }

                    $firstName = $this->resolveFirstName($row);
                    $lastName  = $this->resolveLastName($row);
                    $dateJoined = $this->convertExcelDate($row->date_joined);
                    $dob        = $this->convertExcelDate($row->date_of_birth);
                    $exitDate   = $this->convertExcelDate($row->date_of_resignation);

                    DB::table('employees')->insert([
                        'branch_id'          => $this->branchId,
                        'employee_code'      => $empCode,
                        'epf_no'             => $row->epf_no ?: $empCode,
                        'first_name'         => $firstName,
                        'last_name'          => $lastName,
                        'gender_id'          => $genderId,
                        'date_of_birth'      => $dob,
                        'nationality_id'     => $defaultNatId,
                        'marital_status_id'  => $defaultMaritalId,
                        'status'             => 'active',
                        'department_id'      => $deptId,
                        'designation_id'     => $desigId,
                        'grade_id'           => $defaultGradeId,
                        'team_id'            => $defaultTeamId,
                        'employment_type_id' => $empTypeId,
                        'shift_segment_id'   => $defaultSegmentId,
                        'date_of_joining'    => $dateJoined ?? now()->format('Y-m-d'),
                        'salary_type'        => 'monthly',
                        'basic_salary'       => 0,
                        'exit_date'          => $exitDate,
                        'mobile_number'      => $row->telephone_number,
                        'permanent_address_line1' => $row->permanent_address,
                        'identification_number'   => $row->nic_no,
                        'account_holder_name'     => $row->bank_acc_name,
                        'account_number'          => $row->bank_acc_no,
                        'currently_working'  => $exitDate ? false : true,
                        'attendance'         => true,
                        'payroll'            => true,
                        'auto_precent'       => true,
                        'early_ot'           => true,
                        'created_at'         => now(),
                        'updated_at'         => now(),
                    ]);

                    DB::table($table)->where('id', $row->id)->update(['imported' => true]);
                    $inserted++;

                } catch (\Throwable $e) {
                    $errors[] = "Row {$row->id} ({$table}): " . $e->getMessage();
                    $skipped++;
                }

                $bar->advance();
            }

            $bar->finish();
            $this->newLine();
        }

        $this->info("Done. Inserted: {$inserted} | Skipped/errors: {$skipped}");

        if ($errors) {
            $this->warn('Issues encountered:');
            foreach (array_slice($errors, 0, 20) as $err) {
                $this->line("  {$err}");
            }
            if (count($errors) > 20) {
                $this->line('  … and ' . (count($errors) - 20) . ' more.');
            }
        }
    }

    // ─────────────────────────────────────────────
    // Helpers
    // ─────────────────────────────────────────────

    private function buildCache(string $table, string $nameCol, string $idCol): array
    {
        $cache = [];
        DB::table($table)->get([$nameCol, $idCol])->each(function ($row) use (&$cache, $nameCol, $idCol) {
            $cache[strtolower(trim($row->$nameCol))] = $row->$idCol;
        });
        return $cache;
    }

    private function resolveFirstName(object $row): string
    {
        if (! empty($row->calling_name)) {
            return trim($row->calling_name);
        }
        if (! empty($row->id_name)) {
            $parts = explode(' ', trim($row->id_name));
            return $parts[0];
        }
        return '-';
    }

    private function resolveLastName(object $row): string
    {
        if (! empty($row->surname)) {
            return trim($row->surname);
        }
        if (! empty($row->id_name)) {
            $parts = explode(' ', trim($row->id_name));
            return count($parts) > 1 ? end($parts) : $parts[0];
        }
        return '-';
    }

    /**
     * Convert an Excel date serial number (stored as string) to Y-m-d.
     * Returns null if value is empty or not numeric.
     */
    private function convertExcelDate(?string $value): ?string
    {
        if (! $value || ! is_numeric($value)) {
            return null;
        }
        try {
            $dt = ExcelDate::excelToDateTimeObject((float) $value);
            // sanity check – Excel serial 1 = 1900-01-01, guard against garbage
            if ($dt->format('Y') < 1900 || $dt->format('Y') > 2100) {
                return null;
            }
            return $dt->format('Y-m-d');
        } catch (\Throwable) {
            return null;
        }
    }
}
