generated from azures04/Base-REST-API
azures04
b03f5c91e6
Introduce gamification and check-in support plus expanded transport/admin APIs. Added DDL for checkins, badges and user_badges. New repositories: badgeRepository, userBadgeRepository, checkinRepository. New routes: admin badge management, admin transport (lines/platforms/stations), public transport endpoints, user checkins and stations endpoints, and user badges on /me. Services updated: adminService (badge CRUD & user assignment), userService (checkins, badge queries), authService (hasAdminRights). Minor package.json name update. These changes implement database, repo, service and route wiring for badges, user_badins and checkin features and additional transport admin routes.
21 lines
1.1 KiB
SQL
21 lines
1.1 KiB
SQL
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
|
|
); |