<?php
namespace App\Services;

use App\Core\Database;
use RuntimeException;
use PDO;

final class AttendanceService
{
    public function registerForWalk(int $walkId, int $userId): void
    {
        $pdo = Database::connection();
        $walk = $this->findWalk($walkId);
        if (!$walk || $walk['status'] !== 'open') throw new RuntimeException('Walk is not open for registration.');

        if (!empty($walk['registration_deadline'])) {
            $deadlineTs = $this->registrationDeadlineTimestamp((string)$walk['registration_deadline']);
            if ($deadlineTs !== null && $deadlineTs < time()) {
                throw new RuntimeException('Registration deadline has passed.');
            }
        }

        if (($walk['access_type'] ?? 'open') === 'invitation_only'
            && !$this->isInvited($walkId, $userId)
            && !$this->isAssignedToWalk($walkId, $userId)) {
            throw new RuntimeException('This walk is invitation-only. Please contact Pyrk if you believe you should have access.');
        }

        if ((int)($walk['waiver_required'] ?? 0) === 1 && !$this->hasSignedWaiver($walkId, $userId)) {
            throw new RuntimeException('Waiver must be signed before registration for this walk. Please sign the waiver, then register.');
        }

        $capacity = (int)$walk['capacity'];
        if ($capacity > 0) {
            $count = $pdo->prepare('SELECT COUNT(*) FROM walk_registrations WHERE walk_id = ? AND registration_status IN ("registered", "approved")');
            $count->execute([$walkId]);
            if ((int)$count->fetchColumn() >= $capacity) throw new RuntimeException('Walk capacity is full.');
        }

        $existing = $pdo->prepare('SELECT registration_status FROM walk_registrations WHERE walk_id = ? AND user_id = ? LIMIT 1');
        $existing->execute([$walkId, $userId]);
        $existingStatus = $existing->fetchColumn();
        if (in_array($existingStatus, ['registered', 'approved'], true)) {
            return;
        }

        $stmt = $pdo->prepare('INSERT INTO walk_registrations (walk_id, user_id, registration_status, registered_at, created_at, updated_at) VALUES (?, ?, "registered", NOW(), NOW(), NOW()) ON DUPLICATE KEY UPDATE registration_status = "registered", registered_at = NOW(), updated_at = NOW()');
        $stmt->execute([$walkId, $userId]);
    }

    public function signWaiver(int $walkId, int $userId, string $signedName, string $signatureData = ''): void
    {
        $pdo = Database::connection();
        $templateId = (int)$pdo->query('SELECT id FROM waiver_templates WHERE is_active = 1 ORDER BY version DESC LIMIT 1')->fetchColumn();
        if (!$templateId) throw new RuntimeException('No active waiver template found.');
        $ip = $_SERVER['REMOTE_ADDR'] ?? '';
        $device = $_SERVER['HTTP_USER_AGENT'] ?? '';
        $stmt = $pdo->prepare('INSERT INTO waiver_signatures (walk_id, user_id, waiver_template_id, signed_name, signature_data, accepted_terms, ip_address, device_info, signed_at, created_at) VALUES (?, ?, ?, ?, ?, 1, ?, ?, NOW(), NOW()) ON DUPLICATE KEY UPDATE signed_name = VALUES(signed_name), signature_data = VALUES(signature_data), accepted_terms = 1, ip_address = VALUES(ip_address), device_info = VALUES(device_info), signed_at = NOW()');
        $stmt->execute([$walkId, $userId, $templateId, $signedName, $signatureData, $ip, $device]);
    }

    public function checkIn(int $walkId, int $userId, int $checkedInBy): void
    {
        $pdo = Database::connection();
        $reg = $pdo->prepare('SELECT id FROM walk_registrations WHERE walk_id = ? AND user_id = ? AND registration_status IN ("registered", "approved") LIMIT 1');
        $reg->execute([$walkId, $userId]);
        $registrationId = $reg->fetchColumn();
        if (!$registrationId) throw new RuntimeException('Member is not registered for this walk.');

        $done = $pdo->prepare('SELECT status, check_in_time, finish_time FROM attendance WHERE walk_id = ? AND user_id = ? LIMIT 1');
        $done->execute([$walkId, $userId]);
        $existingAttendance = $done->fetch(PDO::FETCH_ASSOC);
        if ($existingAttendance && (in_array(($existingAttendance['status'] ?? ''), ['finished', 'partially_completed', 'manual_verified'], true) || !empty($existingAttendance['finish_time']))) {
            throw new RuntimeException('This member already finished this walk. Re-check-in is not allowed.');
        }
        if ($existingAttendance && !empty($existingAttendance['check_in_time']) && in_array((string)($existingAttendance['status'] ?? ''), ['checked_in', 'started'], true)) {
            throw new RuntimeException('This member is already checked in for this walk.');
        }

        $walk = $this->findWalk($walkId);
        if ((int)$walk['waiver_required'] === 1 && !$this->hasSignedWaiver($walkId, $userId)) {
            throw new RuntimeException('Waiver must be signed before check-in.');
        }

        $stmt = $pdo->prepare('INSERT INTO attendance (walk_id, user_id, registration_id, check_in_time, status, checked_in_by, created_at, updated_at) VALUES (?, ?, ?, NOW(), "checked_in", ?, NOW(), NOW()) ON DUPLICATE KEY UPDATE check_in_time = IFNULL(check_in_time, NOW()), status = IF(status = "finished", status, "checked_in"), checked_in_by = VALUES(checked_in_by), updated_at = NOW()');
        $stmt->execute([$walkId, $userId, (int)$registrationId, $checkedInBy]);
    }

    public function finish(int $walkId, int $userId, int $verifiedBy): void
    {
        $pdo = Database::connection();
        $walk = $this->findWalk($walkId);
        if (!$walk) throw new RuntimeException('Walk not found.');
        $km = (float)$walk['total_km'];
        $existingStmt = $pdo->prepare('SELECT status, check_in_time, finish_time FROM attendance WHERE walk_id = ? AND user_id = ? LIMIT 1');
        $existingStmt->execute([$walkId, $userId]);
        $existing = $existingStmt->fetch(PDO::FETCH_ASSOC);
        if (!$existing || empty($existing['check_in_time'])) {
            throw new RuntimeException('Member is not checked in for this walk.');
        }
        if (!empty($existing['finish_time']) || in_array((string)($existing['status'] ?? ''), ['finished', 'partially_completed', 'manual_verified'], true)) {
            throw new RuntimeException('This member already finished this walk.');
        }

        $stmt = $pdo->prepare('UPDATE attendance SET finish_time = NOW(), status = "finished", verified_km = ?, updated_at = NOW() WHERE walk_id = ? AND user_id = ? AND status IN ("checked_in", "started") AND finish_time IS NULL');
        $stmt->execute([$km, $walkId, $userId]);
        if ($stmt->rowCount() === 0) throw new RuntimeException('Member is not checked in or already completed.');
        (new LoyaltyService())->addWalkCompletion($userId, $walkId, $km);
    }

    public function partialFinish(int $walkId, int $userId, int $verifiedBy, ?float $manualKm = null, string $source = 'manual'): float
    {
        $pdo = Database::connection();
        $walk = $this->findWalk($walkId);
        if (!$walk) throw new RuntimeException('Walk not found.');

        $totalKm = max(0.0, (float)($walk['total_km'] ?? 0));
        $km = $manualKm !== null ? (float)$manualKm : $this->estimateKmFromLastCheckpoint($walkId, $userId);

        if ($km < 0) throw new RuntimeException('Partial KM cannot be negative.');
        if ($totalKm > 0 && $km > $totalKm) $km = $totalKm;
        $km = round($km, 2);

        if ($km <= 0) {
            throw new RuntimeException('Partial KM must be greater than 0. Add KM manually or scan at least one checkpoint.');
        }

        $statusNote = $source === 'checkpoint' ? 'partially_completed_checkpoint' : 'partially_completed_manual';

        $stmt = $pdo->prepare('UPDATE attendance SET finish_time = NOW(), status = "partially_completed", verified_km = ?, updated_at = NOW() WHERE walk_id = ? AND user_id = ? AND status IN ("checked_in", "started", "manual_verified") AND finish_time IS NULL');
        $stmt->execute([$km, $walkId, $userId]);
        if ($stmt->rowCount() === 0) throw new RuntimeException('Member is not checked in or already completed.');

        (new LoyaltyService())->addPartialWalkCompletion($userId, $walkId, $km);
        return $km;
    }

    public function estimateKmFromLastCheckpoint(int $walkId, int $userId): float
    {
        $pdo = Database::connection();
        $walk = $this->findWalk($walkId);
        if (!$walk) return 0.0;

        $routeId = (int)($walk['route_id'] ?? 0);
        $totalKm = max(0.0, (float)($walk['total_km'] ?? 0));
        if (!$routeId || $totalKm <= 0) return 0.0;

        $countStmt = $pdo->prepare('SELECT COUNT(*) FROM route_points WHERE route_id = ?');
        $countStmt->execute([$routeId]);
        $pointCount = max(1, (int)$countStmt->fetchColumn());

        $lastStmt = $pdo->prepare('SELECT rp.id, rp.title, rp.point_type, rp.sort_order FROM checkpoint_logs cl JOIN route_points rp ON rp.id = cl.route_point_id WHERE cl.walk_id = ? AND cl.user_id = ? AND rp.route_id = ? ORDER BY cl.scanned_at DESC, cl.id DESC LIMIT 1');
        $lastStmt->execute([$walkId, $userId, $routeId]);
        $last = $lastStmt->fetch(PDO::FETCH_ASSOC);
        if (!$last) return 0.0;

        $sortOrder = max(1, (int)($last['sort_order'] ?? 1));
        if ($pointCount <= 1) return $totalKm;

        $ratio = min(1.0, max(0.0, ($sortOrder - 1) / ($pointCount - 1)));
        return round($totalKm * $ratio, 2);
    }

    public function lastCheckpointMeta(int $walkId, int $userId): ?array
    {
        $pdo = Database::connection();
        $walk = $this->findWalk($walkId);
        if (!$walk) return null;
        $routeId = (int)($walk['route_id'] ?? 0);
        if (!$routeId) return null;
        $stmt = $pdo->prepare('SELECT rp.id, rp.title, rp.point_type, rp.sort_order, cl.scanned_at FROM checkpoint_logs cl JOIN route_points rp ON rp.id = cl.route_point_id WHERE cl.walk_id = ? AND cl.user_id = ? AND rp.route_id = ? ORDER BY cl.scanned_at DESC, cl.id DESC LIMIT 1');
        $stmt->execute([$walkId, $userId, $routeId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function checkpoint(int $walkId, int $userId, int $routePointId, int $scannedBy, ?float $latitude = null, ?float $longitude = null, ?float $accuracyMeters = null, string $source = 'staff'): void
    {
        $pdo = Database::connection();
        $walk = $this->findWalk($walkId);
        if (!$walk) throw new RuntimeException('Walk not found.');

        $routeId = (int)($walk['route_id'] ?? 0);
        if (!$routeId) throw new RuntimeException('Walk route is not configured.');

        $pointStmt = $pdo->prepare('SELECT * FROM route_points WHERE id = ? AND route_id = ? LIMIT 1');
        $pointStmt->execute([$routePointId, $routeId]);
        $point = $pointStmt->fetch(PDO::FETCH_ASSOC);
        if (!$point) throw new RuntimeException('Checkpoint does not belong to this walk route.');

        $attendanceStmt = $pdo->prepare('SELECT id, status, check_in_time, finish_time FROM attendance WHERE walk_id = ? AND user_id = ? LIMIT 1');
        $attendanceStmt->execute([$walkId, $userId]);
        $attendance = $attendanceStmt->fetch(PDO::FETCH_ASSOC);
        if (!$attendance || empty($attendance['check_in_time'])) {
            throw new RuntimeException('Member must be checked in before checkpoint progress can be recorded.');
        }
        if (!empty($attendance['finish_time']) || in_array((string)($attendance['status'] ?? ''), ['finished', 'partially_completed'], true)) {
            throw new RuntimeException('This walk is already completed for this member.');
        }

        $existingStmt = $pdo->prepare('SELECT id FROM checkpoint_logs WHERE walk_id = ? AND user_id = ? AND route_point_id = ? LIMIT 1');
        $existingStmt->execute([$walkId, $userId, $routePointId]);
        if ($existingStmt->fetchColumn()) {
            throw new RuntimeException('This checkpoint is already completed.');
        }

        $distanceMeters = null;
        if ($latitude !== null && $longitude !== null && isset($point['latitude'], $point['longitude']) && $point['latitude'] !== null && $point['longitude'] !== null) {
            $distanceMeters = $this->distanceMeters((float)$latitude, (float)$longitude, (float)$point['latitude'], (float)$point['longitude']);
            $radius = isset($point['checkin_radius_meters']) && (int)$point['checkin_radius_meters'] > 0 ? (int)$point['checkin_radius_meters'] : 60;
            $accuracyAllowance = $accuracyMeters !== null ? min(max((float)$accuracyMeters, 0.0), 100.0) : 0.0;
            $allowedDistance = $radius + $accuracyAllowance + 25.0;

            if (str_starts_with($source, 'self') && $distanceMeters > $allowedDistance) {
                throw new RuntimeException('You are too far from this checkpoint. Please move closer and try again.');
            }
        }

        $columns = ['walk_id', 'user_id', 'route_point_id', 'scanned_at', 'scanned_by', 'latitude', 'longitude', 'created_at'];
        $values = [$walkId, $userId, $routePointId, date('Y-m-d H:i:s'), $scannedBy, $latitude, $longitude, date('Y-m-d H:i:s')];

        if ($this->columnExists('checkpoint_logs', 'accuracy_meters')) {
            $columns[] = 'accuracy_meters';
            $values[] = $accuracyMeters;
        }
        if ($this->columnExists('checkpoint_logs', 'distance_from_checkpoint_meters')) {
            $columns[] = 'distance_from_checkpoint_meters';
            $values[] = $distanceMeters !== null ? round($distanceMeters, 2) : null;
        }
        if ($this->columnExists('checkpoint_logs', 'location_source')) {
            $columns[] = 'location_source';
            $values[] = substr($source, 0, 30);
        }
        if ($this->columnExists('checkpoint_logs', 'checkin_method')) {
            $columns[] = 'checkin_method';
            $values[] = str_starts_with($source, 'self') ? 'self_mobile' : 'staff';
        }
        if ($this->columnExists('checkpoint_logs', 'updated_at')) {
            $columns[] = 'updated_at';
            $values[] = date('Y-m-d H:i:s');
        }

        $placeholders = implode(', ', array_fill(0, count($columns), '?'));
        $sql = 'INSERT INTO checkpoint_logs (' . implode(', ', $columns) . ') VALUES (' . $placeholders . ')';
        $pdo->prepare($sql)->execute($values);
    }

    public function checkpointProgress(int $walkId, int $userId): array
    {
        $pdo = Database::connection();
        $walk = $this->findWalk($walkId);
        if (!$walk) return ['completed_ids' => [], 'completed_count' => 0, 'next_point_id' => null];

        $routeId = (int)($walk['route_id'] ?? 0);
        if (!$routeId) return ['completed_ids' => [], 'completed_count' => 0, 'next_point_id' => null];

        $pointsStmt = $pdo->prepare('SELECT id, point_type, title, sort_order, is_required_checkpoint FROM route_points WHERE route_id = ? ORDER BY sort_order, id');
        $pointsStmt->execute([$routeId]);
        $routePoints = $pointsStmt->fetchAll(PDO::FETCH_ASSOC);

        $doneStmt = $pdo->prepare('SELECT route_point_id, scanned_at FROM checkpoint_logs WHERE walk_id = ? AND user_id = ? ORDER BY scanned_at, id');
        $doneStmt->execute([$walkId, $userId]);
        $completedIds = [];
        foreach ($doneStmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $completedIds[(int)$row['route_point_id']] = (string)$row['scanned_at'];
        }

        $navigationPoints = array_values(array_filter($routePoints, static function ($point) {
            return in_array((string)($point['point_type'] ?? ''), ['start', 'checkpoint', 'end'], true)
                || (int)($point['is_required_checkpoint'] ?? 0) === 1;
        }));

        $nextPointId = null;
        foreach ($navigationPoints as $point) {
            $pointId = (int)$point['id'];
            if (!array_key_exists($pointId, $completedIds)) {
                $nextPointId = $pointId;
                break;
            }
        }

        return [
            'completed_ids' => $completedIds,
            'completed_count' => count($completedIds),
            'navigation_total' => count($navigationPoints),
            'next_point_id' => $nextPointId,
        ];
    }

    private function distanceMeters(float $lat1, float $lon1, float $lat2, float $lon2): float
    {
        $earthRadius = 6371000.0;
        $dLat = deg2rad($lat2 - $lat1);
        $dLon = deg2rad($lon2 - $lon1);
        $a = sin($dLat / 2) ** 2 + cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * sin($dLon / 2) ** 2;
        return $earthRadius * 2 * atan2(sqrt($a), sqrt(1 - $a));
    }

    private function columnExists(string $table, string $column): bool
    {
        static $cache = [];
        $key = $table . '.' . $column;
        if (array_key_exists($key, $cache)) return $cache[$key];

        try {
            $pdo = Database::connection();
            $stmt = $pdo->prepare('SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = ? AND column_name = ?');
            $stmt->execute([$table, $column]);
            return $cache[$key] = ((int)$stmt->fetchColumn() > 0);
        } catch (\Throwable) {
            return $cache[$key] = false;
        }
    }


    private function registrationDeadlineTimestamp(string $deadline): ?int
    {
        $deadline = trim(str_replace('T', ' ', $deadline));
        if ($deadline === '') return null;

        // Admin UI may save a date-only deadline as YYYY-MM-DD 00:00:00.
        // For business use this means "until the end of that selected day",
        // not "closed at midnight before the walk day starts".
        if (preg_match('/^(\d{4}-\d{2}-\d{2})(?:\s+00:00(?::00)?)?$/', $deadline, $m)) {
            $ts = strtotime($m[1] . ' 23:59:59');
            return $ts === false ? null : $ts;
        }

        $ts = strtotime($deadline);
        return $ts === false ? null : $ts;
    }

    private function hasSignedWaiver(int $walkId, int $userId): bool
    {
        $pdo = Database::connection();
        $signed = $pdo->prepare('SELECT COUNT(*) FROM waiver_signatures WHERE walk_id = ? AND user_id = ? AND accepted_terms = 1');
        $signed->execute([$walkId, $userId]);
        return (int)$signed->fetchColumn() > 0;
    }

    private function isInvited(int $walkId, int $userId): bool
    {
        $pdo = Database::connection();
        $stmt = $pdo->prepare('SELECT COUNT(*) FROM walk_invitations wi WHERE wi.walk_id = ? AND wi.status = "invited" AND (wi.user_id = ? OR wi.group_id IN (SELECT mgm.group_id FROM member_group_members mgm WHERE mgm.user_id = ?))');
        $stmt->execute([$walkId, $userId, $userId]);
        return (int)$stmt->fetchColumn() > 0;
    }

    private function isAssignedToWalk(int $walkId, int $userId): bool
    {
        if ($walkId <= 0 || $userId <= 0) return false;

        try {
            $pdo = Database::connection();
            $stmt = $pdo->prepare('SELECT COUNT(*) FROM walk_staff_assignments WHERE walk_id = ? AND user_id = ?');
            $stmt->execute([$walkId, $userId]);
            return (int)$stmt->fetchColumn() > 0;
        } catch (\Throwable) {
            // Older installations without the team-assignment table keep the original invitation rule.
            return false;
        }
    }

    private function findWalk(int $walkId): ?array
    {
        $pdo = Database::connection();
        $stmt = $pdo->prepare('SELECT w.*, r.total_km FROM walks w JOIN routes r ON r.id = w.route_id WHERE w.id = ? LIMIT 1');
        $stmt->execute([$walkId]);
        $walk = $stmt->fetch(PDO::FETCH_ASSOC);
        return $walk ?: null;
    }
}
