<?php

namespace App\Services;

use App\Models\RosterResultUpload;
use App\Support\RosterData;
use Illuminate\Http\UploadedFile;
use Illuminate\Support\Facades\DB;
use Illuminate\Validation\ValidationException;

class CsvRosterResultImporter
{
    private const USER_COLUMNS = ['user', 'name', 'user_name', 'username', 'employee', 'employee_name', 'member', 'member_name'];
    private const DATE_COLUMNS = ['roster_date', 'date', 'work_date', 'shift_date'];
    private const HOURS_COLUMNS = ['total_hours', 'hours', 'worked_hours', 'duration', 'total'];

    public function import(UploadedFile $file, string $team, string $rosterDate, ?int $uploadedByUserId): RosterResultUpload
    {
        return DB::transaction(function () use ($file, $team, $rosterDate, $uploadedByUserId) {
            $upload = RosterResultUpload::create([
                'team' => RosterData::normalizeTeam($team),
                'roster_date' => $rosterDate,
                'uploaded_by_user_id' => $uploadedByUserId,
                'source_filename' => $file->getClientOriginalName(),
            ]);

            $rows = $this->parseRows($file, $team, $rosterDate, $upload->id);

            if ($rows === []) {
                throw ValidationException::withMessages([
                    'file' => 'The CSV file did not contain any usable rows.',
                ]);
            }

            foreach (array_chunk($rows, 250) as $chunk) {
                DB::table('roster_result_rows')->insert($chunk);
            }

            $upload->update([
                'imported_rows' => count($rows),
            ]);

            return $upload->fresh();
        });
    }

    private function parseRows(UploadedFile $file, string $team, string $rosterDate, int $uploadId): array
    {
        $handle = fopen($file->getRealPath(), 'rb');

        if ($handle === false) {
            throw ValidationException::withMessages([
                'file' => 'Unable to open the uploaded CSV file.',
            ]);
        }

        $header = fgetcsv($handle);

        if (! is_array($header)) {
            fclose($handle);

            throw ValidationException::withMessages([
                'file' => 'The uploaded CSV file is empty.',
            ]);
        }

        $normalizedHeader = array_map([$this, 'normalizeHeader'], $header);
        $userColumn = $this->findHeaderIndex($normalizedHeader, self::USER_COLUMNS);

        if ($userColumn === null) {
            fclose($handle);

            throw ValidationException::withMessages([
                'file' => 'The CSV must include a user or name column.',
            ]);
        }

        $dateColumn = $this->findHeaderIndex($normalizedHeader, self::DATE_COLUMNS);
        $hoursColumn = $this->findHeaderIndex($normalizedHeader, self::HOURS_COLUMNS);
        $rows = [];
        $timestamp = now();

        while (($csvRow = fgetcsv($handle)) !== false) {
            if ($this->rowIsBlank($csvRow)) {
                continue;
            }

            $userName = trim((string) ($csvRow[$userColumn] ?? ''));

            if ($userName === '') {
                continue;
            }

            $effectiveDate = $dateColumn !== null
                ? RosterData::normalizeDate((string) ($csvRow[$dateColumn] ?? ''), $rosterDate)
                : $rosterDate;

            $totalHours = $hoursColumn !== null
                ? $this->parseHours((string) ($csvRow[$hoursColumn] ?? ''))
                : null;

            $payload = [];

            foreach ($header as $index => $columnName) {
                $payload[$columnName] = $csvRow[$index] ?? null;
            }

            $rows[] = [
                'upload_id' => $uploadId,
                'team' => RosterData::normalizeTeam($team),
                'roster_date' => $effectiveDate,
                'user_name' => $userName,
                'normalized_user_name' => RosterData::normalizeName($userName),
                'total_hours' => $totalHours,
                'raw_payload' => json_encode($payload, JSON_THROW_ON_ERROR),
                'created_at' => $timestamp,
                'updated_at' => $timestamp,
            ];
        }

        fclose($handle);

        return $rows;
    }

    private function rowIsBlank(array $row): bool
    {
        foreach ($row as $value) {
            if (trim((string) $value) !== '') {
                return false;
            }
        }

        return true;
    }

    private function normalizeHeader(string $value): string
    {
        $normalized = preg_replace('/^\xEF\xBB\xBF/', '', $value) ?? $value;

        return trim((string) preg_replace('/[^a-z0-9]+/i', '_', strtolower($normalized)), '_');
    }

    private function findHeaderIndex(array $normalizedHeader, array $aliases): ?int
    {
        foreach ($aliases as $alias) {
            $index = array_search($alias, $normalizedHeader, true);

            if ($index !== false) {
                return $index;
            }
        }

        return null;
    }

    private function parseHours(string $value): ?float
    {
        $trimmed = trim($value);

        if ($trimmed === '') {
            return null;
        }

        if (preg_match('/^\d+(?:\.\d+)?$/', $trimmed) === 1) {
            return round((float) $trimmed, 2);
        }

        if (preg_match('/^(\d{1,2}):(\d{2})$/', $trimmed, $matches) === 1) {
            return round((((int) $matches[1]) * 60 + ((int) $matches[2])) / 60, 2);
        }

        return null;
    }
}
