<?php

namespace App\Services;

use App\Models\Department;
use App\Models\Designation;
use App\Models\EmployeeImport;
use App\Models\EmployeeImportRow;
use App\Models\Location;
use App\Models\Role;
use App\Models\SalaryGroup;
use App\Models\Scopes\CompanyScope;
use App\Models\Shift;
use App\Models\User;
use App\Services\SalaryRevisionService;
use App\Services\TenantRoleService;
use Illuminate\Http\UploadedFile;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Hash;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Facades\Validator;
use Illuminate\Validation\Rule;

class EmployeeImportService
{
    public const SYNC_ROW_THRESHOLD = 50;

    /**
     * @return array{
     *   departments: array<string, int>,
     *   designations: array<string, int>,
     *   locations: array<string, int>,
     *   shifts: array<string, int>,
     *   salary_groups: array<string, int>,
     *   roles: array<string, int>
     * }
     */
    public function newLookupCache(): array
    {
        return [
            'departments' => [],
            'designations' => [],
            'locations' => [],
            'shifts' => [],
            'salary_groups' => [],
            'roles' => [],
        ];
    }

    /**
     * @return array<int, string>
     */
    public function templateHeaders(): array
    {
        return [
            'name',
            'email',
            'password',
            'employee_number',
            'phone',
            'gender',
            'dob',
            'father_name',
            'joining_date',
            'registration_date',
            'confirmation_date',
            'probation_end_date',
            'last_working_date',
            'department',
            'designation',
            'location',
            'shift',
            'salary_group',
            'report_to',
            'annual_ctc',
            'account_holder_name',
            'bank_name',
            'account_number',
            'branch_name',
            'city',
            'ifsc_code',
            'pan_number',
            'uan_number',
            'pf_join_date',
            'work_state',
            'pt_location',
            'role',
        ];
    }

    public function createImportRecord(UploadedFile $file, User $actor): EmployeeImport
    {
        $companyId = $actor->company_id;
        $path = $file->store('imports/employees/' . ($companyId ?? 'shared'), 'public');
        $rows = $this->readCsvRows($path);
        $mode = count($rows) <= self::SYNC_ROW_THRESHOLD ? 'sync' : 'queued';

        return EmployeeImport::query()->create([
            'company_id' => $companyId,
            'created_by' => $actor->id,
            'status' => 'pending',
            'mode' => $mode,
            'file_path' => $path,
            'original_file_name' => $file->getClientOriginalName(),
            'total_rows' => count($rows),
            'summary_json' => [
                'headers' => $this->templateHeaders(),
                'notes' => ['Import is create-only in this version.'],
            ],
        ]);
    }

    public function process(EmployeeImport $import): EmployeeImport
    {
        $companyId = $import->company_id;
        $actor = User::query()->find($import->created_by);
        $rows = $this->readCsvRows((string) $import->file_path);
        $lookupCache = $this->newLookupCache();

        $import->update([
            'status' => 'processing',
            'started_at' => now(),
            'processed_rows' => 0,
            'success_rows' => 0,
            'failed_rows' => 0,
        ]);

        $success = 0;
        $failed = 0;

        foreach ($rows as $rowNumber => $row) {
            [$status, $message, $userId] = $this->processRow($row, $companyId, $actor, $lookupCache);
            EmployeeImportRow::query()->create([
                'employee_import_id' => $import->id,
                'row_number' => $rowNumber,
                'status' => $status,
                'user_id' => $userId,
                'error_message' => $message,
                'payload_json' => $row,
            ]);

            if ($status === 'success') {
                $success++;
            } else {
                $failed++;
            }
        }

        $import->update([
            'status' => 'completed',
            'processed_rows' => $success + $failed,
            'success_rows' => $success,
            'failed_rows' => $failed,
            'completed_at' => now(),
        ]);

        return $import->fresh(['rows']);
    }

    /**
     * @param array<string, int[]> $lookupCache
     * @return array{0:string,1:?string,2:?int}
     */
    private function processRow(array $row, ?int $companyId, ?User $actor, array &$lookupCache): array
    {
        $payload = $this->normalizeRow($row);
        $validator = Validator::make($payload, $this->rules($companyId));

        if ($validator->fails()) {
            return ['failed', implode('; ', $validator->errors()->all()), null];
        }

        $validated = $validator->validated();

        if (! ($actor && $actor->hasPermission('users_edit'))) {
            unset($validated['role']);
        }

        $manualEmployeeNumber = trim((string) ($validated['employee_number'] ?? ''));
        $validated['employee_number'] = $manualEmployeeNumber !== '' ? $manualEmployeeNumber : null;

        $initialAnnualCtc = (float) ($validated['annual_ctc'] ?? 0);
        unset($validated['annual_ctc']);

        $validated['company_id'] = $companyId;
        $validated['user_type'] = User::USER_TYPE_STAFF;
        $validated['status'] = 'active';
        $validated['password'] = Hash::make((string) $validated['password']);

        $user = null;

        try {
            DB::transaction(function () use (&$validated, $companyId, &$lookupCache, &$user) {
                $err = $this->applyNameResolutionsInTransaction($validated, $companyId, $lookupCache);
                if ($err !== null) {
                    throw new \InvalidArgumentException($err);
                }

                if (! isset($validated['role_id']) && $companyId) {
                    $validated['role_id'] = TenantRoleService::ensureEmployeeRoleForCompany((int) $companyId)->id;
                }

                if (empty($validated['employee_number'])) {
                    $validated['employee_number'] = $this->nextEmployeeNumber($companyId);
                }

                $user = User::query()->create($validated);
            });
        } catch (\InvalidArgumentException $e) {
            return ['failed', $e->getMessage(), null];
        } catch (\Throwable $e) {
            return ['failed', $e->getMessage(), null];
        }

        if ($initialAnnualCtc > 0 && ! empty($user->id) && ! empty($user->salary_group_id)) {
            app(SalaryRevisionService::class)->createSalaryRevision((int) $companyId, $user->id, $user->salary_group_id, $initialAnnualCtc);
        }

        return ['success', null, $user?->id];
    }

    /**
     * @param array<string, mixed> $data
     * @param array<string, int[]> $lookupCache
     */
    private function applyNameResolutionsInTransaction(
        array &$data,
        ?int $companyId,
        array &$lookupCache
    ): ?string {
        $dept = $this->nullableTrimmedString($data['department'] ?? null);
        $desig = $this->nullableTrimmedString($data['designation'] ?? null);
        $loc = $this->nullableTrimmedString($data['location'] ?? null);
        $shift = $this->nullableTrimmedString($data['shift'] ?? null);
        $reportTo = $this->nullableTrimmedString($data['report_to'] ?? null);
        $salaryGroup = $this->nullableTrimmedString($data['salary_group'] ?? null);
        $role = $this->nullableTrimmedString($data['role'] ?? null);

        foreach (['department', 'designation', 'location', 'shift', 'report_to','salary_group', 'role'] as $k) {
            unset($data[$k]);
        }

        if ($dept !== null) {
            if (! $companyId) {
                return 'A company is required to resolve or create a department from the department column.';
            }
            $data['department_id'] = $this->firstOrCreateBelongsToCompany(
                Department::class,
                (int) $companyId,
                $dept,
                $lookupCache,
                'departments'
            );
        } else {
            $data['department_id'] = null;
        }

        if ($desig !== null) {
            if (! $companyId) {
                return 'A company is required to resolve or create a designation from the designation column.';
            }
            $data['designation_id'] = $this->firstOrCreateBelongsToCompany(
                Designation::class,
                (int) $companyId,
                $desig,
                $lookupCache,
                'designations'
            );
        } else {
            $data['designation_id'] = null;
        }

        if ($loc !== null) {
            if (! $companyId) {
                return 'A company is required to resolve or create a location from the location column.';
            }
            $data['location_id'] = $this->firstOrCreateBelongsToCompany(
                Location::class,
                (int) $companyId,
                $loc,
                $lookupCache,
                'locations'
            );
        } else {
            $data['location_id'] = null;
        }

        if ($shift !== null) {
            if (! $companyId) {
                return 'A company is required to resolve a shift from the shift column.';
            }
            $idOrErr = $this->findIdStrict(Shift::class, (int) $companyId, 'name', $shift, $lookupCache, 'shifts', 'shift');
            if (is_string($idOrErr)) {
                return $idOrErr;
            }
            $data['shift_id'] = $idOrErr;
        } else {
            $defaultShift = Shift::query()
                ->withoutGlobalScope(CompanyScope::class)
                ->where('company_id', $companyId)
                ->first();

            $data['shift_id'] = $defaultShift?->id;
        }

        if( $reportTo !== null) {
            $reportToUser = User::query()->where('email', $reportTo)->first();
            if (! $reportToUser) {
                return 'No user with email "'.$reportTo.'" was found to report to.';
            }
            $data['report_to'] = $reportToUser?->id;
        } else {
            $data['report_to'] = null;
        }

        if ($salaryGroup !== null) {
            if (! $companyId) {
                return 'A company is required to resolve a salary group from the salary_group column.';
            }
            $idOrErr = $this->findIdStrict(SalaryGroup::class, (int) $companyId, 'name', $salaryGroup, $lookupCache, 'salary_groups', 'salary group');
            if (is_string($idOrErr)) {
                return $idOrErr;
            }
            $data['salary_group_id'] = $idOrErr;
        } else {
            $defaultGroup = SalaryGroup::query()
                ->withoutGlobalScope(CompanyScope::class)
                ->where('company_id', $companyId)
                ->first();

            $data['salary_group_id'] = $defaultGroup?->id ?? null;
        }

        if ($role !== null) {
            if (! $companyId) {
                return 'A company is required to resolve a role from the role column.';
            }
            $idOrErr = $this->findRoleIdStrict((int) $companyId, $role, $lookupCache, 'roles');
            if (is_string($idOrErr)) {
                return $idOrErr;
            }
            $data['role_id'] = $idOrErr;
        } else {
            $data['role_id'] = null;
        }

        return null;
    }

    /**
     * @param  array{departments: array<string, int>, designations: array<string, int>, locations: array<string, int>, shifts: array<string, int>, salary_groups: array<string, int>, roles: array<string, int>}  $lookupCache
     * @param  'departments'|'designations'|'locations'  $bucket
     */
    private function firstOrCreateBelongsToCompany(
        string $modelClass,
        int $companyId,
        string $submittedName,
        array &$lookupCache,
        string $bucket
    ): int {
        $cacheKey = $this->cacheKey($companyId, $submittedName);
        if (isset($lookupCache[$bucket][$cacheKey])) {
            return $lookupCache[$bucket][$cacheKey];
        }

        $lower = mb_strtolower($submittedName, 'UTF-8');

        $model = $modelClass::query()
            ->withoutGlobalScope(CompanyScope::class)
            ->where('company_id', $companyId)
            ->whereRaw('LOWER('.$this->tableColumn('name', $modelClass).') = ?', [$lower])
            ->lockForUpdate()
            ->first();

        if ($model) {
            $id = (int) $model->getKey();
            $lookupCache[$bucket][$cacheKey] = $id;

            return $id;
        }

        $trimmed = trim($submittedName);
        $instance = $modelClass::query()
            ->withoutGlobalScope(CompanyScope::class)
            ->create([
                'company_id' => $companyId,
                'name' => $trimmed,
            ]);
        $id = (int) $instance->getKey();
        $lookupCache[$bucket][$cacheKey] = $id;

        return $id;
    }

    /**
     * @param  array{departments: array<string, int>, designations: array<string, int>, locations: array<string, int>, shifts: array<string, int>, salary_groups: array<string, int>, roles: array<string, int>}  $lookupCache
     * @param  'shifts'|'salary_groups'  $bucket
     * @return int|string
     */
    private function findIdStrict(
        string $modelClass,
        int $companyId,
        string $column,
        string $submittedName,
        array &$lookupCache,
        string $bucket,
        string $userVisibleLabel
    ): int|string {
        $cacheKey = $this->cacheKey($companyId, $submittedName);
        if (isset($lookupCache[$bucket][$cacheKey])) {
            return $lookupCache[$bucket][$cacheKey];
        }

        $lower = mb_strtolower($submittedName, 'UTF-8');
        $col = $this->tableColumn($column, $modelClass);

        $model = $modelClass::query()
            ->withoutGlobalScope(CompanyScope::class)
            ->where('company_id', $companyId)
            ->whereRaw('LOWER('.$col.') = ?', [$lower])
            ->first();
        if (! $model) {
            return 'No '.$userVisibleLabel.' named "'.$submittedName.'" was found for this company.';
        }

        $id = (int) $model->getKey();
        $lookupCache[$bucket][$cacheKey] = $id;

        return $id;
    }

    /**
     * @param  array{departments: array<string, int>, designations: array<string, int>, locations: array<string, int>, shifts: array<string, int>, salary_groups: array<string, int>, roles: array<string, int>}  $lookupCache
     * @return int|string
     */
    private function findRoleIdStrict(int $companyId, string $submittedName, array &$lookupCache, string $bucket): int|string
    {
        $cacheKey = $this->cacheKey($companyId, $submittedName);
        if (isset($lookupCache[$bucket][$cacheKey])) {
            return $lookupCache[$bucket][$cacheKey];
        }

        $lower = mb_strtolower($submittedName, 'UTF-8');
        $nameCol = $this->tableColumn('name', Role::class);
        $displayCol = $this->tableColumn('display_name', Role::class);

        $model = Role::query()
            ->withoutGlobalScope(CompanyScope::class)
            ->where('company_id', $companyId)
            ->where(function ($q) use ($lower, $nameCol, $displayCol) {
                $q->whereRaw('LOWER('.$nameCol.') = ?', [$lower])
                    ->orWhereRaw('LOWER('.$displayCol.') = ?', [$lower]);
            })
            ->first();
        if (! $model) {
            return 'No role matching "'.$submittedName.'" was found (checked name and display name) for this company.';
        }

        $id = (int) $model->getKey();
        $lookupCache[$bucket][$cacheKey] = $id;

        return $id;
    }

    private function tableColumn(string $column, string $modelClass): string
    {
        $m = new $modelClass;
        $table = $m->getTable();
        if (! in_array($column, ['name', 'display_name'], true)) {
            $column = 'name';
        }

        return $table.'.'.$column;
    }

    private function cacheKey(int $companyId, string $name): string
    {
        return (string) $companyId.'|'.mb_strtolower(trim($name), 'UTF-8');
    }

    private function nullableTrimmedString(mixed $value): ?string
    {
        if (! is_string($value)) {
            $value = $value === null ? '' : (string) $value;
        }
        $t = trim($value);

        return $t === '' ? null : $t;
    }

    /**
     * @return array<string, mixed>
     */
    private function rules(?int $companyId): array
    {
        return [
            'name' => 'required|string|max:191',
            'email' => 'required|email|unique:users,email',
            'password' => 'required|string|min:8',
            'employee_number' => [
                'nullable',
                'string',
                'max:50',
                Rule::unique('users', 'employee_number')
                    ->where(fn ($query) => $companyId ? $query->where('company_id', $companyId) : $query),
            ],
            'phone' => 'nullable|string|max:50',
            'gender' => 'nullable|in:male,female,other',
            'dob' => 'nullable|date',
            'father_name' => 'nullable|string|max:191',
            'joining_date' => 'nullable|date',
            'registration_date' => 'nullable|date',
            'confirmation_date' => 'nullable|date',
            'probation_end_date' => 'nullable|date',
            'last_working_date' => 'nullable|date',
            'department' => 'nullable|string|max:191',
            'designation' => 'nullable|string|max:191',
            'location' => 'nullable|string|max:191',
            'shift' => 'nullable|string|max:191',
            'salary_group' => 'nullable|string|max:191',
            'role' => 'nullable|string|max:191',
            'report_to' => 'nullable|string|max:191',
            'annual_ctc' => 'nullable|numeric|min:0',
            'account_holder_name' => 'nullable|string|max:191',
            'bank_name' => 'nullable|string|max:191',
            'account_number' => 'nullable|string|max:50',
            'branch_name' => 'nullable|string|max:191',
            'city' => 'nullable|string|max:100',
            'ifsc_code' => 'nullable|string|max:20',
            'pan_number' => 'nullable|string|max:20',
            'uan_number' => 'nullable|string|max:30',
            'pf_join_date' => 'nullable|date',
            'work_state' => 'nullable|string|max:100',
            'pt_location' => 'nullable|string|max:191',
        ];
    }

    /**
     * @return array<int, array<string, string>>
     */
    private function readCsvRows(string $path): array
    {
        $fullPath = Storage::disk('public')->path($path);
        if (! is_file($fullPath)) {
            return [];
        }

        $handle = fopen($fullPath, 'r');
        if ($handle === false) {
            return [];
        }

        $header = fgetcsv($handle) ?: [];
        $header = array_map(fn ($item) => trim((string) $item), $header);
        $rows = [];
        $index = 1;

        while (($line = fgetcsv($handle)) !== false) {
            $index++;
            if (! is_array($line)) {
                continue;
            }
            $assoc = [];
            foreach ($header as $offset => $column) {
                if ($column === '') {
                    continue;
                }
                $assoc[$column] = isset($line[$offset]) ? trim((string) $line[$offset]) : '';
            }

            if (array_filter($assoc, fn ($value) => $value !== '' && $value !== null) === []) {
                continue;
            }

            $rows[$index] = $assoc;
        }

        fclose($handle);

        return $rows;
    }

    /**
     * @param  array<string, mixed>  $row
     * @return array<string, mixed>
     */
    private function normalizeRow(array $row): array
    {
        $normalized = [];
        foreach ($this->templateHeaders() as $column) {
            $value = $row[$column] ?? null;
            if (is_string($value)) {
                $value = trim($value);
            }
            $normalized[$column] = $value === '' ? null : $value;
        }

        return $normalized;
    }

    private function nextEmployeeNumber(?int $companyId): string
    {
        if (! $companyId) {
            return str_pad('1', 1, '0', STR_PAD_LEFT);
        }

        $sequence = DB::table('employee_number_sequences')
            ->where('company_id', $companyId)
            ->lockForUpdate()
            ->first();

        if (! $sequence) {
            DB::table('employee_number_sequences')->insert([
                'company_id' => $companyId,
                'prefix' => null,
                'digits' => 1,
                'last_number' => 1,
                'created_at' => now(),
                'updated_at' => now(),
            ]);

            return str_pad('1', 1, '0', STR_PAD_LEFT);
        }

        $prefix = strtoupper(trim((string) ($sequence->prefix ?? null)));
        $digits = max(1, min(10, (int) ($sequence->digits ?? 1)));
        $nextNumber = (int) $sequence->last_number + 1;

        DB::table('employee_number_sequences')
            ->where('company_id', $companyId)
            ->update([
                'last_number' => $nextNumber,
                'prefix' => $prefix,
                'digits' => $digits,
                'updated_at' => now(),
            ]);

        return $prefix . str_pad((string) $nextNumber, $digits, '0', STR_PAD_LEFT);
    }
}