<?php

namespace App\Http\Controllers;

use Carbon\Carbon;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class PreventionGpsMapController extends Controller
{
    private const DEFAULT_ACTIVITY_CONNECTIONS = ['mysql', 'MAM_C1'];
    private const DEFAULT_POINT_LIMIT = 1000;
    private const MIN_POINT_LIMIT = 100;
    private const MAX_POINT_LIMIT = 20000;
    private const SESSION_SUMMARY_LIMIT = 50;

    public function index()
    {
        return view('Prevention.gps_map', [
            'activityConnection' => $this->resolveRequestedConnection(null),
            'defaultLimit' => self::DEFAULT_POINT_LIMIT,
        ]);
    }

    public function points(Request $request): JsonResponse
    {
        $connection = $this->resolveRequestedConnection($request->query('connection'));
        $limit = $this->safeLimit($request->query('limit'));
        $fromDate = $this->safeDate($request->query('from'));
        $toDate = $this->safeDate($request->query('to'));
        $peFilter = $this->normalizePeCode($request->query('pe_code'));

        try {
            if (!$this->activityTablesReady($connection)) {
                return response()->json([
                    'ok' => false,
                    'message' => "peer_mobile_activity tables are not available on connection {$connection}.",
                    'connection' => $connection,
                ], 422);
            }

            $sessions = $this->fetchSessions($connection, $fromDate, $toDate, $peFilter);
            $activities = $this->fetchActivities($connection, $fromDate, $toDate, $peFilter, $limit);
            $points = $this->buildMapPoints($sessions, $activities);

            return response()->json([
                'ok' => true,
                'connection' => $connection,
                'summary' => [
                    'sessions' => count($sessions),
                    'activities' => count($activities),
                    'mapped_points' => count($points),
                    'saved_visits' => collect($activities)
                        ->where('event_type', 'record_saved_logsheet')
                        ->count(),
                ],
                'sessions' => $sessions,
                'activities' => $activities,
                'points' => $points,
            ]);
        } catch (\Throwable $e) {
            return response()->json([
                'ok' => false,
                'message' => $e->getMessage(),
                'connection' => $connection,
            ], 500);
        }
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    private function fetchSessions(
        string $connection,
        ?string $fromDate,
        ?string $toDate,
        ?string $peFilter
    ): array {
        $rows = DB::connection($connection)
            ->table('peer_mobile_activity_sessions as s')
            ->leftJoin('peer_mobile_activity_events as e', 'e.session_id', '=', 's.id')
            ->select([
                's.id',
                's.session_uuid',
                's.pe_code',
                's.device_id',
                's.status',
                's.started_at',
                's.last_activity_at',
                's.ended_at',
                's.start_latitude',
                's.start_longitude',
                's.end_latitude',
                's.end_longitude',
            ])
            ->selectRaw('count(e.id) as event_count')
            ->selectRaw(
                "sum(case when e.event_type = 'record_saved_logsheet' then 1 else 0 end) as save_event_count"
            )
            ->when($peFilter !== null, fn ($query) => $query->where('s.pe_code', $peFilter))
            ->when($fromDate !== null, fn ($query) => $query->whereDate('s.started_at', '>=', $fromDate))
            ->when($toDate !== null, function ($query) use ($toDate) {
                $query->where(function ($subQuery) use ($toDate) {
                    $subQuery
                        ->whereDate('s.last_activity_at', '<=', $toDate)
                        ->orWhere(function ($nested) use ($toDate) {
                            $nested->whereNull('s.last_activity_at')
                                ->whereDate('s.started_at', '<=', $toDate);
                        });
                });
            })
            ->groupBy([
                's.id',
                's.session_uuid',
                's.pe_code',
                's.device_id',
                's.status',
                's.started_at',
                's.last_activity_at',
                's.ended_at',
                's.start_latitude',
                's.start_longitude',
                's.end_latitude',
                's.end_longitude',
            ])
            ->orderByRaw('coalesce(s.last_activity_at, s.started_at) desc')
            ->limit(self::SESSION_SUMMARY_LIMIT)
            ->get();

        return $rows->map(function ($row) {
            return [
                'id' => (int) $row->id,
                'session_uuid' => (string) $row->session_uuid,
                'pe_code' => (string) ($row->pe_code ?? ''),
                'device_id' => (string) ($row->device_id ?? ''),
                'status' => (string) ($row->status ?? ''),
                'started_at' => $this->formatTimestamp($row->started_at),
                'last_activity_at' => $this->formatTimestamp($row->last_activity_at),
                'ended_at' => $this->formatTimestamp($row->ended_at),
                'event_count' => (int) ($row->event_count ?? 0),
                'save_event_count' => (int) ($row->save_event_count ?? 0),
                'start_latitude' => $this->toFloatOrNull($row->start_latitude),
                'start_longitude' => $this->toFloatOrNull($row->start_longitude),
                'end_latitude' => $this->toFloatOrNull($row->end_latitude),
                'end_longitude' => $this->toFloatOrNull($row->end_longitude),
            ];
        })->values()->all();
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    private function fetchActivities(
        string $connection,
        ?string $fromDate,
        ?string $toDate,
        ?string $peFilter,
        int $limit
    ): array {
        $rows = DB::connection($connection)
            ->table('peer_mobile_activity_events as e')
            ->join('peer_mobile_activity_sessions as s', 's.id', '=', 'e.session_id')
            ->select([
                'e.id',
                'e.event_type',
                'e.latitude',
                'e.longitude',
                'e.accuracy_m',
                'e.occurred_at',
                'e.metadata',
                'e.session_uuid as event_session_uuid',
                'e.device_id as event_device_id',
                'e.pe_code as event_pe_code',
                's.session_uuid',
                's.device_id',
                's.pe_code',
                's.status',
            ])
            ->when($peFilter !== null, fn ($query) => $query->where('s.pe_code', $peFilter))
            ->when($fromDate !== null, fn ($query) => $query->whereDate('e.occurred_at', '>=', $fromDate))
            ->when($toDate !== null, fn ($query) => $query->whereDate('e.occurred_at', '<=', $toDate))
            ->orderByDesc('e.occurred_at')
            ->orderByDesc('e.id')
            ->limit($limit)
            ->get();

        return $rows->map(function ($row) {
            $metadata = $this->decodeMetadata($row->metadata);

            return [
                'id' => (int) $row->id,
                'session_uuid' => (string) ($row->event_session_uuid ?: $row->session_uuid),
                'pe_code' => (string) ($row->event_pe_code ?: $row->pe_code),
                'device_id' => (string) ($row->event_device_id ?: $row->device_id),
                'status' => (string) ($row->status ?? ''),
                'event_type' => (string) ($row->event_type ?? ''),
                'occurred_at' => $this->formatTimestamp($row->occurred_at),
                'lat' => $this->toFloatOrNull($row->latitude),
                'lng' => $this->toFloatOrNull($row->longitude),
                'accuracy_m' => $this->toFloatOrNull($row->accuracy_m),
                'tracking_origin' => (string) ($metadata['tracking_origin'] ?? ''),
                'meeting_point' => (string) ($metadata['meeting_point'] ?? ''),
            ];
        })->values()->all();
    }

    /**
     * @param array<int, array<string, mixed>> $sessions
     * @param array<int, array<string, mixed>> $activities
     * @return array<int, array<string, mixed>>
     */
    private function buildMapPoints(array $sessions, array $activities): array
    {
        $points = [];

        foreach ($sessions as $session) {
            $startLat = $session['start_latitude'] ?? null;
            $startLng = $session['start_longitude'] ?? null;
            if ($this->isValidCoordinatePair($startLat, $startLng)) {
                $points[] = [
                    'kind' => 'start',
                    'label' => 'Session Start',
                    'session_uuid' => $session['session_uuid'],
                    'pe_code' => $session['pe_code'],
                    'device_id' => $session['device_id'],
                    'event_type' => 'session_start',
                    'occurred_at' => $session['started_at'],
                    'lat' => $startLat,
                    'lng' => $startLng,
                    'tracking_origin' => 'session_start',
                    'meeting_point' => '',
                    'accuracy_m' => null,
                ];
            }

            $endLat = $session['end_latitude'] ?? null;
            $endLng = $session['end_longitude'] ?? null;
            if ($this->isValidCoordinatePair($endLat, $endLng)) {
                $points[] = [
                    'kind' => 'end',
                    'label' => 'Session End',
                    'session_uuid' => $session['session_uuid'],
                    'pe_code' => $session['pe_code'],
                    'device_id' => $session['device_id'],
                    'event_type' => 'session_end',
                    'occurred_at' => $session['ended_at'],
                    'lat' => $endLat,
                    'lng' => $endLng,
                    'tracking_origin' => 'session_end',
                    'meeting_point' => '',
                    'accuracy_m' => null,
                ];
            }
        }

        foreach ($activities as $activity) {
            $lat = $activity['lat'] ?? null;
            $lng = $activity['lng'] ?? null;
            if (!$this->isValidCoordinatePair($lat, $lng)) {
                continue;
            }

            $points[] = [
                'kind' => 'event',
                'label' => $activity['event_type'],
                'session_uuid' => $activity['session_uuid'],
                'pe_code' => $activity['pe_code'],
                'device_id' => $activity['device_id'],
                'event_type' => $activity['event_type'],
                'occurred_at' => $activity['occurred_at'],
                'lat' => $lat,
                'lng' => $lng,
                'tracking_origin' => $activity['tracking_origin'],
                'meeting_point' => $activity['meeting_point'],
                'accuracy_m' => $activity['accuracy_m'],
            ];
        }

        return $points;
    }

    private function activityTablesReady(string $connection): bool
    {
        return Schema::connection($connection)->hasTable('peer_mobile_activity_sessions')
            && Schema::connection($connection)->hasTable('peer_mobile_activity_events');
    }

    private function resolveRequestedConnection($connection): string
    {
        $requested = trim((string) $connection);
        $configuredConnections = array_keys((array) config('database.connections', []));

        if ($requested !== '' && in_array($requested, $configuredConnections, true)) {
            return $requested;
        }

        $preferred = array_values(array_unique(array_filter(array_merge(
            [(string) config('database.default', 'mysql')],
            self::DEFAULT_ACTIVITY_CONNECTIONS
        ))));

        foreach ($preferred as $candidate) {
            if (!in_array($candidate, $configuredConnections, true)) {
                continue;
            }

            try {
                if ($this->activityTablesReady($candidate)) {
                    return $candidate;
                }
            } catch (\Throwable $e) {
                continue;
            }
        }

        return in_array((string) config('database.default', 'mysql'), $configuredConnections, true)
            ? (string) config('database.default', 'mysql')
            : ($configuredConnections[0] ?? 'mysql');
    }

    private function safeLimit($value): int
    {
        $limit = (int) $value;
        if ($limit <= 0) {
            $limit = self::DEFAULT_POINT_LIMIT;
        }

        return max(self::MIN_POINT_LIMIT, min($limit, self::MAX_POINT_LIMIT));
    }

    private function safeDate($value): ?string
    {
        if (!is_string($value) || trim($value) === '') {
            return null;
        }

        try {
            return Carbon::parse($value)->format('Y-m-d');
        } catch (\Throwable $e) {
            return null;
        }
    }

    private function normalizePeCode($value): ?string
    {
        $digits = preg_replace('/\D+/', '', (string) $value) ?? '';
        if ($digits === '') {
            return null;
        }

        $trimmed = ltrim($digits, '0');
        return $trimmed === '' ? '0' : $trimmed;
    }

    /**
     * @return array<string, mixed>
     */
    private function decodeMetadata($value): array
    {
        if (!is_string($value) || trim($value) === '') {
            return [];
        }

        $decoded = json_decode($value, true);
        return is_array($decoded) ? $decoded : [];
    }

    private function formatTimestamp($value): ?string
    {
        if ($value === null || $value === '') {
            return null;
        }

        try {
            return Carbon::parse((string) $value)->format('Y-m-d H:i:s');
        } catch (\Throwable $e) {
            return (string) $value;
        }
    }

    private function toFloatOrNull($value): ?float
    {
        return is_numeric($value) ? (float) $value : null;
    }

    private function isValidCoordinatePair($lat, $lng): bool
    {
        if (!is_numeric($lat) || !is_numeric($lng)) {
            return false;
        }

        $lat = (float) $lat;
        $lng = (float) $lng;

        return $lat >= -90 && $lat <= 90 && $lng >= -180 && $lng <= 180;
    }
}