<?php

declare(strict_types=1);

final class StaffLocationService
{
    public static function ensureTables(PDO $pdo): void
    {
        $pdo->exec(
            "CREATE TABLE IF NOT EXISTS staff_location_sessions (
                session_id INT AUTO_INCREMENT PRIMARY KEY,
                staff_id INT NOT NULL,
                check_in_at DATETIME NOT NULL,
                check_out_at DATETIME NULL,
                check_in_lat DECIMAL(10,7) NULL,
                check_in_lng DECIMAL(10,7) NULL,
                check_out_lat DECIMAL(10,7) NULL,
                check_out_lng DECIMAL(10,7) NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_staff_sessions_staff_id (staff_id),
                INDEX idx_staff_sessions_check_in_at (check_in_at),
                INDEX idx_staff_sessions_check_out_at (check_out_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"
        );

        $pdo->exec(
            "CREATE TABLE IF NOT EXISTS staff_location_points (
                point_id INT AUTO_INCREMENT PRIMARY KEY,
                session_id INT NOT NULL,
                staff_id INT NOT NULL,
                latitude DECIMAL(10,7) NOT NULL,
                longitude DECIMAL(10,7) NOT NULL,
                accuracy_meters INT NULL,
                recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_staff_points_session_id (session_id),
                INDEX idx_staff_points_staff_id (staff_id),
                INDEX idx_staff_points_recorded_at (recorded_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"
        );
    }

    public static function getActiveSessionId(PDO $pdo, int $staffId): ?int
    {
        self::ensureTables($pdo);

        $stmt = $pdo->prepare(
            'SELECT session_id FROM staff_location_sessions WHERE staff_id = :sid AND check_out_at IS NULL ORDER BY check_in_at DESC LIMIT 1'
        );
        $stmt->execute(['sid' => $staffId]);
        $id = $stmt->fetchColumn();
        if ($id === false || $id === null || $id === '') {
            return null;
        }
        return (int)$id;
    }

    public static function startSession(PDO $pdo, int $staffId, float $lat, float $lng): int
    {
        self::ensureTables($pdo);

        $existing = self::getActiveSessionId($pdo, $staffId);
        if ($existing !== null) {
            return $existing;
        }

        $stmt = $pdo->prepare(
            'INSERT INTO staff_location_sessions (staff_id, check_in_at, check_in_lat, check_in_lng) VALUES (:sid, CURRENT_TIMESTAMP, :lat, :lng)'
        );
        $stmt->execute([
            'sid' => $staffId,
            'lat' => $lat,
            'lng' => $lng,
        ]);

        return (int)$pdo->lastInsertId();
    }

    public static function endSession(PDO $pdo, int $staffId, float $lat, float $lng): void
    {
        self::ensureTables($pdo);

        $sessionId = self::getActiveSessionId($pdo, $staffId);
        if ($sessionId === null) {
            return;
        }

        $stmt = $pdo->prepare(
            'UPDATE staff_location_sessions
             SET check_out_at = CURRENT_TIMESTAMP,
                 check_out_lat = :lat,
                 check_out_lng = :lng
             WHERE session_id = :id AND staff_id = :sid AND check_out_at IS NULL'
        );
        $stmt->execute([
            'lat' => $lat,
            'lng' => $lng,
            'id' => $sessionId,
            'sid' => $staffId,
        ]);
    }

    public static function logPoint(PDO $pdo, int $sessionId, int $staffId, float $lat, float $lng, ?int $accuracyMeters = null): void
    {
        self::ensureTables($pdo);

        $stmt = $pdo->prepare(
            'INSERT INTO staff_location_points (session_id, staff_id, latitude, longitude, accuracy_meters) VALUES (:sess, :sid, :lat, :lng, :acc)'
        );
        $stmt->execute([
            'sess' => $sessionId,
            'sid' => $staffId,
            'lat' => $lat,
            'lng' => $lng,
            'acc' => $accuracyMeters,
        ]);
    }

    public static function getSessions(PDO $pdo, int $staffId, int $limit = 30): array
    {
        self::ensureTables($pdo);

        $limit = max(1, min(200, $limit));
        $stmt = $pdo->prepare(
            'SELECT session_id, staff_id, check_in_at, check_out_at, check_in_lat, check_in_lng, check_out_lat, check_out_lng
             FROM staff_location_sessions
             WHERE staff_id = :sid
             ORDER BY check_in_at DESC
             LIMIT ' . $limit
        );
        $stmt->execute(['sid' => $staffId]);
        return $stmt->fetchAll();
    }

    public static function getPoints(PDO $pdo, int $sessionId, int $limit = 2000): array
    {
        self::ensureTables($pdo);

        $limit = max(1, min(5000, $limit));
        $stmt = $pdo->prepare(
            'SELECT point_id, session_id, staff_id, latitude, longitude, accuracy_meters, recorded_at
             FROM staff_location_points
             WHERE session_id = :sess
             ORDER BY recorded_at ASC
             LIMIT ' . $limit
        );
        $stmt->execute(['sess' => $sessionId]);
        return $stmt->fetchAll();
    }
}
