<?php

namespace App\Services;

use App\Models\RosterResultUpload;
use App\Support\RosterData;
use Carbon\CarbonImmutable;
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', 'agent_name', 'agent', 'customer_name'];
    private const DATE_COLUMNS = ['roster_date', 'date', 'work_date', 'shift_date'];
    private const HOURS_COLUMNS = ['total_hours', 'hours', 'worked_hours', 'duration', 'total'];
    private const STRUCTURED_HEADER_ALIASES = [
        'employee_name' => ['employee_name'],
        'cluster' => ['cluster', 'team'],
        'sign_in_date' => ['sign_in_date', 'signin_date', 'sign_in_dt'],
        'sign_in' => ['sign_in', 'signin'],
        'sign_out_date' => ['sign_out_date', 'signout_date', 'sign_out_dt'],
        'sign_out' => ['sign_out', 'signout'],
        'remarks' => ['remarks', 'remark', 'notes'],
    ];

    public function import(UploadedFile $file, string $team, string $rosterDate, ?int $uploadedByUserId): RosterResultUpload
    {
        return DB::transaction(function () use ($file, $team, $rosterDate, $uploadedByUserId) {
            $persistedTeam = RosterData::normalizeTeam($team);
            $persistedRosterDate = RosterData::normalizeDate($rosterDate, now()->toDateString()) ?? now()->toDateString();
            $parsed = $this->parseRows($file, $team, $rosterDate);

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

            $upload = RosterResultUpload::create([
                'team' => $persistedTeam,
                'roster_date' => $persistedRosterDate,
                'uploaded_by_user_id' => $uploadedByUserId,
                'source_filename' => $file->getClientOriginalName(),
            ]);

            $timestamp = now();
            $rows = array_map(function (array $row) use ($upload, $timestamp, $persistedTeam, $persistedRosterDate) {
                $rawPayload = is_array($row['raw_payload'] ?? null)
                    ? $row['raw_payload']
                    : json_decode((string) ($row['raw_payload'] ?? '{}'), true);

                if (! is_array($rawPayload)) {
                    $rawPayload = [];
                }

                $rawPayload['selected_team'] = $persistedTeam;
                $rawPayload['selected_roster_date'] = $persistedRosterDate;

                return [
                    ...$row,
                    'team' => $persistedTeam,
                    'roster_date' => $persistedRosterDate,
                    'raw_payload' => json_encode($rawPayload, JSON_THROW_ON_ERROR | JSON_INVALID_UTF8_SUBSTITUTE),
                    'upload_id' => $upload->id,
                    'created_at' => $timestamp,
                    'updated_at' => $timestamp,
                ];
            }, $parsed['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): array
    {
        $delimiter = $this->detectDelimiter($file);
        $handle = fopen($file->getRealPath(), 'rb');

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

        $allRows = [];

        while (($csvRow = fgetcsv($handle, 0, $delimiter)) !== false) {
            $allRows[] = $csvRow;
        }

        fclose($handle);

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

        $structured = $this->parseStructuredRosterCsv($allRows, $team, $rosterDate);

        if ($structured !== null) {
            return $structured;
        }

        return $this->parseGenericCsv($allRows, $team, $rosterDate);
    }

    private function parseStructuredRosterCsv(array $allRows, string $fallbackTeam, string $fallbackDate): ?array
    {
        $firstCell = trim((string) ($allRows[0][0] ?? ''));
        $secondCell = trim((string) ($allRows[1][0] ?? ''));
        $headerRow = $allRows[2] ?? null;

        if ($firstCell === '' || $secondCell === '' || ! is_array($headerRow)) {
            return null;
        }

        if (! str_contains(strtolower($secondCell), 'date:')) {
            return null;
        }

        $normalizedHeader = array_map([$this, 'normalizeHeader'], $headerRow);
        $employeeColumn = $this->findHeaderIndex($normalizedHeader, self::STRUCTURED_HEADER_ALIASES['employee_name']);
        $clusterColumn = $this->findHeaderIndex($normalizedHeader, self::STRUCTURED_HEADER_ALIASES['cluster']);
        $signInDateColumn = $this->findHeaderIndex($normalizedHeader, self::STRUCTURED_HEADER_ALIASES['sign_in_date']);
        $signInColumn = $this->findHeaderIndex($normalizedHeader, self::STRUCTURED_HEADER_ALIASES['sign_in']);
        $signOutDateColumn = $this->findHeaderIndex($normalizedHeader, self::STRUCTURED_HEADER_ALIASES['sign_out_date']);
        $signOutColumn = $this->findHeaderIndex($normalizedHeader, self::STRUCTURED_HEADER_ALIASES['sign_out']);

        if ($employeeColumn === null || $signInColumn === null || $signOutColumn === null) {
            return null;
        }

        $resolvedTeam = RosterData::normalizeTeam($firstCell, RosterData::normalizeTeam($fallbackTeam));
        $resolvedDate = $this->normalizeImportedDate(str_ireplace('date:', '', $secondCell), $fallbackDate);
        $rows = [];

        foreach (array_slice($allRows, 3) as $csvRow) {
            if ($this->rowIsBlank($csvRow)) {
                continue;
            }

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

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

            $rowTeam = $clusterColumn !== null
                ? RosterData::normalizeTeam((string) ($csvRow[$clusterColumn] ?? ''), $resolvedTeam)
                : $resolvedTeam;
            $signInDate = $signInDateColumn !== null
                ? $this->normalizeImportedDate((string) ($csvRow[$signInDateColumn] ?? ''), $resolvedDate)
                : $resolvedDate;
            $signOutDate = $signOutDateColumn !== null
                ? $this->normalizeImportedDate((string) ($csvRow[$signOutDateColumn] ?? ''), $signInDate)
                : $signInDate;
            $signInTime = RosterData::normalizeTime((string) ($csvRow[$signInColumn] ?? ''));
            $signOutTime = RosterData::normalizeTime((string) ($csvRow[$signOutColumn] ?? ''));
            $signInAt = RosterData::combineDateAndTime($signInDate, $signInTime);
            $signOutAt = RosterData::combineDateAndTime($signOutDate, $signOutTime);

            if ($signInAt && $signOutAt && $signOutAt->lessThan($signInAt)) {
                $signOutAt = $signOutAt->addDay();
            }

            $payload = [];

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

            $payload['csv_team'] = $rowTeam;
            $payload['csv_roster_date'] = $resolvedDate;
            $payload['sign_in_at'] = $signInAt?->format('Y-m-d h:i A');
            $payload['sign_out_at'] = $signOutAt?->format('Y-m-d h:i A');

            $rows[] = [
                'team' => $rowTeam,
                'roster_date' => $resolvedDate,
                'user_name' => $userName,
                'normalized_user_name' => RosterData::normalizeName($userName),
                'total_hours' => $this->calculateHoursFromRange($signInAt, $signOutAt),
                'raw_payload' => json_encode($payload, JSON_THROW_ON_ERROR | JSON_INVALID_UTF8_SUBSTITUTE),
            ];
        }

        return [
            'team' => $resolvedTeam,
            'roster_date' => $resolvedDate,
            'rows' => $rows,
        ];
    }

    private function parseGenericCsv(array $allRows, string $team, string $rosterDate): array
    {
        $header = $allRows[0] ?? null;

        if (! is_array($header)) {
            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) {
            throw ValidationException::withMessages([
                'file' => 'The CSV must include an employee name column.',
            ]);
        }

        $dateColumn = $this->findHeaderIndex($normalizedHeader, self::DATE_COLUMNS);
        $hoursColumn = $this->findHeaderIndex($normalizedHeader, self::HOURS_COLUMNS);
        $rows = [];
        $normalizedTeam = RosterData::normalizeTeam($team);
        $normalizedRosterDate = RosterData::normalizeDate($rosterDate, now()->toDateString());

        foreach (array_slice($allRows, 1) as $csvRow) {
            if ($this->rowIsBlank($csvRow)) {
                continue;
            }

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

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

            $effectiveDate = $dateColumn !== null
                ? $this->normalizeImportedDate((string) ($csvRow[$dateColumn] ?? ''), $normalizedRosterDate)
                : $normalizedRosterDate;

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

            $payload = [];

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

            $rows[] = [
                'team' => $normalizedTeam,
                'roster_date' => $effectiveDate,
                'user_name' => $userName,
                'normalized_user_name' => RosterData::normalizeName($userName),
                'total_hours' => $totalHours,
                'raw_payload' => json_encode($payload, JSON_THROW_ON_ERROR | JSON_INVALID_UTF8_SUBSTITUTE),
            ];
        }

        return [
            'team' => $normalizedTeam,
            'roster_date' => $normalizedRosterDate,
            'rows' => $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 normalizeImportedDate(?string $value, string $fallback): string
    {
        $trimmed = trim((string) $value);

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

        foreach (['d-m-Y', 'j-n-Y', 'n/j/Y', 'n/j/y', 'm/d/Y', 'Y-m-d'] as $format) {
            try {
                return CarbonImmutable::createFromFormat($format, $trimmed)->toDateString();
            } catch (\Throwable) {
            }
        }

        return RosterData::normalizeDate($trimmed, $fallback) ?? $fallback;
    }

    private function detectDelimiter(UploadedFile $file): string
    {
        $handle = fopen($file->getRealPath(), 'rb');

        if ($handle === false) {
            return ',';
        }

        $line = '';
        while (($candidate = fgets($handle)) !== false) {
            $trimmed = trim((string) $candidate);
            if ($trimmed !== '') {
                $line = $trimmed;
                break;
            }
        }

        fclose($handle);

        if ($line === '') {
            return ',';
        }

        $delimiters = [',', ';', "\t", '|'];
        $bestDelimiter = ',';
        $bestCount = -1;

        foreach ($delimiters as $delimiter) {
            $count = substr_count($line, $delimiter);
            if ($count > $bestCount) {
                $bestCount = $count;
                $bestDelimiter = $delimiter;
            }
        }

        return $bestDelimiter;
    }

    private function calculateHoursFromRange(?CarbonImmutable $start, ?CarbonImmutable $end): ?float
    {
        if (! $start || ! $end) {
            return null;
        }

        return round($start->diffInMinutes($end) / 60, 2);
    }

    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;
    }
}
