<?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 ImportGalagedaraEmployees extends Command
{
    protected $signature = 'employees:import-galagedara
                            {--step=all : Which step to run: staging|employees|all}
                            {--force : Re-import staging even if already imported}';

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

    private int $branchId = 6; // Galagedara branch

    // Column mapping – same layout as Sales Team / Lid Factory sheets
    private array $columnMap = [
        'serial_no'             => 'A',
        'emp_no'                => 'B',
        'epf_no'                => 'C',
        'id_name'               => 'E',   // full name
        'calling_name'          => 'F',
        'initials_with_surname' => 'G',
        'initials'              => 'H',
        'surname'               => 'I',   // last name
        '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'   => '', // not present in this sheet
    ];

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

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

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

        return Command::SUCCESS;
    }

    // ─────────────────────────────────────────────
    // STEP 1 – Load Excel sheet into staging table
    // ─────────────────────────────────────────────

    private function importToStagingTable(): void
    {
        $file = public_path('galagedara.xlsx');

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

        $this->info('Loading galagedara.xlsx…');
        $spreadsheet = IOFactory::load($file);
        $sheet = $spreadsheet->getSheetByName('galagedara_factory');

        if (! $sheet) {
            $this->error("Sheet 'galagedara_factory' not found.");
            return;
        }

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

        $this->info("Importing 'galagedara_factory' ({$maxRow} rows incl. header)…");
        $bar = $this->output->createProgressBar($maxRow - 1);
        $bar->start();

        for ($row = 2; $row <= $maxRow; $row++) {
            $empNo = trim((string) $sheet->getCell('B' . $row)->getValue());

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

            if (! $this->option('force') && DB::table('import_galagedara_factory')->where('emp_no', $empNo)->exists()) {
                $skipped++;
                $bar->advance();
                continue;
            }

            $get = fn(string $col) => $col
                ? trim((string) $sheet->getCell($col . $row)->getValue())
                : null;

            $record = array_map(
                fn(string $col) => $col ? ($get($col) ?: null) : null,
                $this->columnMap
            );

            $record['imported']   = false;
            $record['created_at'] = now();
            $record['updated_at'] = now();

            DB::table('import_galagedara_factory')->updateOrInsert(['emp_no' => $empNo], $record);
            $rows++;
            $bar->advance();
        }

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

    // ─────────────────────────────────────────────
    // STEP 2 – Ensure all required lookup records for branch 6
    // ─────────────────────────────────────────────

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

        // Departments needed for this branch
        $departments = [
            'Production', 'HSE', 'Administration', 'VAT',
            'Transport', 'Finance', 'Factory', 'Stores',
        ];
        foreach ($departments 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: {$name}");
            }
        }

        // Designations needed for this branch
        $designations = [
            'Supervisor', 'Assistant', 'Machine Operator', 'Driver',
            'Manager', 'Chef', 'Assistant Chef', 'Assistant Manager', 'Executive',
        ];
        foreach ($designations 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: {$name}");
            }
        }

        // Employment types for this branch
        $empTypes = ['Permanent', 'Casual'];
        foreach ($empTypes as $name) {
            if (! DB::table('employment_types')->where('name', $name)->where('branch_id', $this->branchId)->exists()) {
                DB::table('employment_types')->insert([
                    'branch_id'  => $this->branchId,
                    'name'       => $name,
                    'status'     => 1,
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);
                $this->line("  + Employment type: {$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: -');
        }

        // 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: -');
        }

        // 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: -');
        }

        // 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: -');
        }

        // Gender records for branch 6 (Male / Female)
        foreach (['Male', 'Female'] as $name) {
            if (! DB::table('genders')->where('name', $name)->where('branch_id', $this->branchId)->exists()) {
                DB::table('genders')->insert([
                    'branch_id'  => $this->branchId,
                    'name'       => $name,
                    'status'     => 1,
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);
                $this->line("  + Gender: {$name}");
            }
        }

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

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

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

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

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

        $rows = DB::table('import_galagedara_factory')->where('imported', false)->get();
        $this->info("Processing {$rows->count()} staging rows…");

        $bar      = $this->output->createProgressBar($rows->count());
        $bar->start();

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

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

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

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

                // ── Names ──────────────────────────────────────
                $lastName  = trim($row->surname ?? '');
                $firstName = $this->deriveFirstName(
                    trim($row->id_name ?? ''),
                    $lastName,
                    trim($row->calling_name ?? '')
                );
                // If both are empty, use emp_no as fallback identifier
                if ($firstName === '-' && $lastName === '') {
                    $firstName = $empCode;
                    $lastName  = $empCode;
                } elseif ($lastName === '') {
                    $lastName = $firstName;
                }

                // ── FK lookups (with safe fallbacks for empty cells) ───
                $genderId  = $genders[strtolower(trim($row->sex ?? ''))]
                          ?? $genders['male']
                          ?? null;
                $deptId    = $departments[strtolower(trim($row->department ?? ''))] ?? null;
                $desigId   = $designations[strtolower(trim($row->designation ?? ''))] ?? null;
                $empTypeId = $empTypes[strtolower(trim($row->status_of_appointment ?? ''))]
                          ?? $empTypes['permanent']
                          ?? null;

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

                // ── Dates ──────────────────────────────────────
                $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('import_galagedara_factory')->where('id', $row->id)->update(['imported' => true]);
                $inserted++;

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

            $bar->advance();
        }

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

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

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

    /**
     * Derive first_name from the full ID Name by removing the surname suffix.
     *
     * Strategy: find the last occurrence of the surname in the full name and
     * take everything before it.  Falls back to the calling name if the result
     * would be empty.
     */
    private function deriveFirstName(string $idName, string $lastName, string $callingName): string
    {
        if ($lastName !== '' && $idName !== '') {
            // Find the last occurrence of the surname in the full name
            $pos = strrpos($idName, $lastName);
            if ($pos !== false) {
                $first = trim(substr($idName, 0, $pos));
                if ($first !== '') {
                    return $first;
                }
            }
        }

        // Fallback: use calling name, or first word of id_name
        if ($callingName !== '') {
            return $callingName;
        }

        if ($idName !== '') {
            return explode(' ', $idName)[0];
        }

        return '-';
    }

    /**
     * Build a lowercase-name → id cache filtered by branch_id.
     */
    private function buildCache(string $table, string $nameCol, string $idCol, int $branchId): array
    {
        $cache = [];
        DB::table($table)
            ->where('branch_id', $branchId)
            ->get([$nameCol, $idCol])
            ->each(function ($row) use (&$cache, $nameCol, $idCol) {
                $cache[strtolower(trim($row->$nameCol))] = $row->$idCol;
            });
        return $cache;
    }

    /**
     * Convert an Excel date serial number stored as string to Y-m-d.
     */
    private function convertExcelDate(?string $value): ?string
    {
        if (! $value || ! is_numeric($value)) {
            return null;
        }
        try {
            $dt = ExcelDate::excelToDateTimeObject((float) $value);
            if ($dt->format('Y') < 1900 || $dt->format('Y') > 2100) {
                return null;
            }
            return $dt->format('Y-m-d');
        } catch (\Throwable) {
            return null;
        }
    }
}
