CREATE TABLE IF NOT EXISTS `checkins` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `userId` UUID NOT NULL,
    `stationId` INT UNSIGNED NOT NULL,
    `platformId` INT UNSIGNED NULL COMMENT 'Platform optionnelle pour plus de détail',
    `latitude` DECIMAL(10, 8) NOT NULL,
    `longitude` DECIMAL(11, 8) NOT NULL,
    `distance` FLOAT NOT NULL COMMENT 'Distance en mètres du point de poinçonnage à la plateforme/station',
    `checkinAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_checkins_user (userId),
    INDEX idx_checkins_station (stationId),
    INDEX idx_checkins_platform (platformId),
    INDEX idx_checkins_timestamp (checkinAt),
    UNIQUE KEY uq_checkins_user_station_time (userId, stationId, checkinAt),

    CONSTRAINT `fk_checkins_userId` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_checkins_stationId` FOREIGN KEY (`stationId`) REFERENCES `stations` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_checkins_platformId` FOREIGN KEY (`platformId`) REFERENCES `platforms` (`id`) ON DELETE SET NULL
);