-- 用戶表
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 簽到記錄表
CREATE TABLE check_ins (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    check_in_time TIMESTAMP NOT NULL,
    check_in_date DATE NOT NULL,
    check_in_type VARCHAR(20) NOT NULL, -- 'DAILY', 'COURSE', 'MEETING' 等
    location VARCHAR(255),
    device_info VARCHAR(255),
    remark VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES users(id),
    UNIQUE KEY unique_user_date (user_id, check_in_date, check_in_type)
);

-- 簽到統(tǒng)計(jì)表
CREATE TABLE check_in_stats (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    total_days INT DEFAULT 0,
    continuous_days INT DEFAULT 0,
    last_check_in_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(id),
    UNIQUE KEY unique_user (user_id)
);

@Data
@TableName("check_ins")
public class CheckIn {
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;
    
    @TableField("user_id")
    private Long userId;
    
    @TableField("check_in_time")
    private LocalDateTime checkInTime;
    
    @TableField("check_in_date")
    private LocalDate checkInDate;
    
    @TableField("check_in_type")
    private String checkInType;
    
    private String location;
    
    @TableField("device_info")
    private String deviceInfo;
    
    private String remark;
}

@Data
@TableName("check_in_stats")
public class CheckInStats {
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;
    
    @TableField("user_id")
    private Long userId;
    
    @TableField("total_days")
    private Integer totalDays = 0;
    
    @TableField("continuous_days")
    private Integer continuousDays = 0;
    
    @TableField("last_check_in_date")
    private LocalDate lastCheckInDate;
}

@Data
@TableName("users")
public class User {
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;
    
    private String username;
    
    private String password;
    
    private String email;
    
    @TableField("created_at")
    private LocalDateTime createdAt;
}

@Mapper
public interface CheckInMapper extends BaseMapper<CheckIn> {
    
    @Select("SELECT COUNT(*) FROM check_ins WHERE user_id = #{userId} AND check_in_type = #{type}")
    int countByUserIdAndType(@Param("userId") Long userId, @Param("type") String type);
    
    @Select("SELECT * FROM check_ins WHERE user_id = #{userId} AND check_in_date BETWEEN #{startDate} AND #{endDate} ORDER BY check_in_date ASC")
    List<CheckIn> findByUserIdAndDateBetween(
            @Param("userId") Long userId, 
            @Param("startDate") LocalDate startDate, 
            @Param("endDate") LocalDate endDate);
    
    @Select("SELECT COUNT(*) FROM check_ins WHERE user_id = #{userId} AND check_in_date = #{date} AND check_in_type = #{type}")
    int existsByUserIdAndDateAndType(
            @Param("userId") Long userId, 
            @Param("date") LocalDate date, 
            @Param("type") String type);
}

@Mapper
public interface CheckInStatsMapper extends BaseMapper<CheckInStats> {
    
    @Select("SELECT * FROM check_in_stats WHERE user_id = #{userId}")
    CheckInStats findByUserId(@Param("userId") Long userId);
}

@Mapper
public interface UserMapper extends BaseMapper<User> {
    
    @Select("SELECT * FROM users WHERE username = #{username}")
    User findByUsername(@Param("username") String username);
}

@Service
@Transactional
public class CheckInService {
    
    @Autowired
    private CheckInMapper checkInMapper;
    
    @Autowired
    private CheckInStatsMapper checkInStatsMapper;
    
    @Autowired
    private UserMapper userMapper;
    
    /**
     * 用戶簽到
     */
    public CheckIn checkIn(Long userId, String type, String location, String deviceInfo, String remark) {
        // 檢查用戶是否存在
        User user = userMapper.selectById(userId);
        if (user == null) {
            throw new RuntimeException("User not found");
        }
        
        LocalDate today = LocalDate.now();
        
        // 檢查今天是否已經(jīng)簽到
        if (checkInMapper.existsByUserIdAndDateAndType(userId, today, type) > 0) {
            throw new RuntimeException("Already checked in today");
        }
        
        // 創(chuàng)建簽到記錄
        CheckIn checkIn = new CheckIn();
        checkIn.setUserId(userId);
        checkIn.setCheckInTime(LocalDateTime.now());
        checkIn.setCheckInDate(today);
        checkIn.setCheckInType(type);
        checkIn.setLocation(location);
        checkIn.setDeviceInfo(deviceInfo);
        checkIn.setRemark(remark);
        
        checkInMapper.insert(checkIn);
        
        // 更新簽到統(tǒng)計(jì)
        updateCheckInStats(userId, today);
        
        return checkIn;
    }
    
    /**
     * 更新簽到統(tǒng)計(jì)信息
     */
    private void updateCheckInStats(Long userId, LocalDate today) {
        CheckInStats stats = checkInStatsMapper.findByUserId(userId);
        
        if (stats == null) {
            stats = new CheckInStats();
            stats.setUserId(userId);
            stats.setTotalDays(1);
            stats.setContinuousDays(1);
            stats.setLastCheckInDate(today);
            
            checkInStatsMapper.insert(stats);
        } else {
            // 更新總簽到天數(shù)
            stats.setTotalDays(stats.getTotalDays() + 1);
            
            // 更新連續(xù)簽到天數(shù)
            if (stats.getLastCheckInDate() != null) {
                if (today.minusDays(1).equals(stats.getLastCheckInDate())) {
                    // 連續(xù)簽到
                    stats.setContinuousDays(stats.getContinuousDays() + 1);
                } else if (today.equals(stats.getLastCheckInDate())) {
                    // 當(dāng)天重復(fù)簽到，不計(jì)算連續(xù)天數(shù)
                } else {
                    // 中斷連續(xù)簽到
                    stats.setContinuousDays(1);
                }
            }
            
            stats.setLastCheckInDate(today);
            
            checkInStatsMapper.updateById(stats);
        }
    }
    
    /**
     * 獲取用戶簽到統(tǒng)計(jì)
     */
    public CheckInStats getCheckInStats(Long userId) {
        CheckInStats stats = checkInStatsMapper.findByUserId(userId);
        if (stats == null) {
            throw new RuntimeException("Check-in stats not found");
        }
        return stats;
    }
    
    /**
     * 獲取用戶指定日期范圍內(nèi)的簽到記錄
     */
    public List<CheckIn> getCheckInHistory(Long userId, LocalDate startDate, LocalDate endDate) {
        return checkInMapper.findByUserIdAndDateBetween(userId, startDate, endDate);
    }
}

@RestController
@RequestMapping("/api/check-ins")
public class CheckInController {
    
    @Autowired
    private CheckInService checkInService;
    
    @PostMapping
    public ResponseEntity<CheckIn> checkIn(@RequestBody CheckInRequest request) {
        CheckIn checkIn = checkInService.checkIn(
                request.getUserId(),
                request.getType(),
                request.getLocation(),
                request.getDeviceInfo(),
                request.getRemark()
        );
        return ResponseEntity.ok(checkIn);
    }
    
    @GetMapping("/stats/{userId}")
    public ResponseEntity<CheckInStats> getStats(@PathVariable Long userId) {
        CheckInStats stats = checkInService.getCheckInStats(userId);
        return ResponseEntity.ok(stats);
    }
    
    @GetMapping("/history/{userId}")
    public ResponseEntity<List<CheckIn>> getHistory(
            @PathVariable Long userId,
            @RequestParam @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate startDate,
            @RequestParam @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate endDate) {
        List<CheckIn> history = checkInService.getCheckInHistory(userId, startDate, endDate);
        return ResponseEntity.ok(history);
    }
}

@Data
public class CheckInRequest {
    private Long userId;
    private String type;
    private String location;
    private String deviceInfo;
    private String remark;
}

@Configuration
public class RedisConfig {
    
    @Bean
    public RedisTemplate<String, Object> redisTemplate(RedisConnectionFactory factory) {
        RedisTemplate<String, Object> template = new RedisTemplate<>();
        template.setConnectionFactory(factory);
        
        // 使用Jackson2JsonRedisSerializer序列化和反序列化redis的value值
        Jackson2JsonRedisSerializer<Object> serializer = new Jackson2JsonRedisSerializer<>(Object.class);
        ObjectMapper mapper = new ObjectMapper();
        mapper.setVisibility(PropertyAccessor.ALL, JsonAutoDetect.Visibility.ANY);
        mapper.activateDefaultTyping(LaissezFaireSubTypeValidator.instance,
                ObjectMapper.DefaultTyping.NON_FINAL, JsonTypeInfo.As.PROPERTY);
        serializer.setObjectMapper(mapper);
        
        template.setValueSerializer(serializer);
        template.setKeySerializer(new StringRedisSerializer());
        template.setHashKeySerializer(new StringRedisSerializer());
        template.setHashValueSerializer(serializer);
        template.afterPropertiesSet();
        
        return template;
    }
}

@Service
public class RedisCheckInService {
    
    private static final String USER_CHECKIN_KEY = "checkin:user:";
    private static final String DAILY_CHECKIN_KEY = "checkin:daily:";
    private static final String CHECKIN_RANK_KEY = "checkin:rank:";
    private static final String USER_STATS_KEY = "checkin:stats:";
    
    @Autowired
    private RedisTemplate<String, Object> redisTemplate;
    
    @Autowired
    private StringRedisTemplate stringRedisTemplate;
    
    /**
     * 用戶簽到
     */
    public boolean checkIn(Long userId, String location) {
        String today = LocalDate.now().format(DateTimeFormatter.ISO_DATE);
        String userKey = USER_CHECKIN_KEY + userId;
        String dailyKey = DAILY_CHECKIN_KEY + today;
        
        // 判斷用戶今天是否已經(jīng)簽到
        Boolean isMember = redisTemplate.opsForSet().isMember(dailyKey, userId);
        if (isMember != null && isMember) {
            return false; // 已經(jīng)簽到過了
        }
        
        // 記錄用戶簽到
        redisTemplate.opsForSet().add(dailyKey, userId);
        
        // 設(shè)置過期時(shí)間（35天后過期，以便統(tǒng)計(jì)連續(xù)簽到）
        redisTemplate.expire(dailyKey, 35, TimeUnit.DAYS);
        
        // 記錄用戶簽到詳情
        Map<String, String> checkInInfo = new HashMap<>();
        checkInInfo.put("time", LocalDateTime.now().toString());
        checkInInfo.put("location", location);
        redisTemplate.opsForHash().putAll(userKey + ":" + today, checkInInfo);
        
        // 更新簽到排行榜
        redisTemplate.opsForZSet().incrementScore(CHECKIN_RANK_KEY + today, userId, 1);
        
        // 更新用戶簽到統(tǒng)計(jì)
        updateUserCheckInStats(userId);
        
        return true;
    }
    
    /**
     * 更新用戶簽到統(tǒng)計(jì)
     */
    private void updateUserCheckInStats(Long userId) {
        String userStatsKey = USER_STATS_KEY + userId;
        
        // 獲取當(dāng)前日期
        LocalDate today = LocalDate.now();
        String todayStr = today.format(DateTimeFormatter.ISO_DATE);
        
        // 獲取用戶最后簽到日期
        String lastCheckInDate = (String) redisTemplate.opsForHash().get(userStatsKey, "lastCheckInDate");
        
        // 更新總簽到天數(shù)
        redisTemplate.opsForHash().increment(userStatsKey, "totalDays", 1);
        
        // 更新連續(xù)簽到天數(shù)
        if (lastCheckInDate != null) {
            LocalDate lastDate = LocalDate.parse(lastCheckInDate, DateTimeFormatter.ISO_DATE);
            
            if (today.minusDays(1).equals(lastDate)) {
                // 連續(xù)簽到
                redisTemplate.opsForHash().increment(userStatsKey, "continuousDays", 1);
            } else if (today.equals(lastDate)) {
                // 當(dāng)天重復(fù)簽到，不增加連續(xù)天數(shù)
            } else {
                // 中斷連續(xù)簽到
                redisTemplate.opsForHash().put(userStatsKey, "continuousDays", 1);
            }
        } else {
            // 第一次簽到
            redisTemplate.opsForHash().put(userStatsKey, "continuousDays", 1);
        }
        
        // 更新最后簽到日期
        redisTemplate.opsForHash().put(userStatsKey, "lastCheckInDate", todayStr);
    }
    
    /**
     * 獲取用戶簽到統(tǒng)計(jì)信息
     */
    public Map<Object, Object> getUserCheckInStats(Long userId) {
        String userStatsKey = USER_STATS_KEY + userId;
        return redisTemplate.opsForHash().entries(userStatsKey);
    }
    
    /**
     * 獲取用戶是否已簽到
     */
    public boolean isUserCheckedInToday(Long userId) {
        String today = LocalDate.now().format(DateTimeFormatter.ISO_DATE);
        String dailyKey = DAILY_CHECKIN_KEY + today;
        
        Boolean isMember = redisTemplate.opsForSet().isMember(dailyKey, userId);
        return isMember != null && isMember;
    }
    
    /**
     * 獲取今日簽到用戶數(shù)
     */
    public long getTodayCheckInCount() {
        String today = LocalDate.now().format(DateTimeFormatter.ISO_DATE);
        String dailyKey = DAILY_CHECKIN_KEY + today;
        
        Long size = redisTemplate.opsForSet().size(dailyKey);
        return size != null ? size : 0;
    }
    
    /**
     * 獲取簽到排行榜
     */
    public Set<ZSetOperations.TypedTuple<Object>> getCheckInRank(int limit) {
        String today = LocalDate.now().format(DateTimeFormatter.ISO_DATE);
        String rankKey = CHECKIN_RANK_KEY + today;
        
        return redisTemplate.opsForZSet().reverseRangeWithScores(rankKey, 0, limit - 1);
    }
    
    /**
     * 檢查用戶在指定日期是否簽到
     */
    public boolean checkUserSignedInDate(Long userId, LocalDate date) {
        String dateStr = date.format(DateTimeFormatter.ISO_DATE);
        String dailyKey = DAILY_CHECKIN_KEY + dateStr;
        
        Boolean isMember = redisTemplate.opsForSet().isMember(dailyKey, userId);
        return isMember != null && isMember;
    }
    
    /**
     * 獲取用戶指定月份的簽到情況
     */
    public List<String> getMonthlyCheckInStatus(Long userId, int year, int month) {
        List<String> result = new ArrayList<>();
        YearMonth yearMonth = YearMonth.of(year, month);
        
        // 獲取指定月份的第一天和最后一天
        LocalDate firstDay = yearMonth.atDay(1);
        LocalDate lastDay = yearMonth.atEndOfMonth();
        
        // 逐一檢查每一天是否簽到
        LocalDate currentDate = firstDay;
        while (!currentDate.isAfter(lastDay)) {
            if (checkUserSignedInDate(userId, currentDate)) {
                result.add(currentDate.format(DateTimeFormatter.ISO_DATE));
            }
            currentDate = currentDate.plusDays(1);
        }
        
        return result;
    }
}

@RestController
@RequestMapping("/api/redis-check-in")
public class RedisCheckInController {
    
    @Autowired
    private RedisCheckInService checkInService;
    
    @PostMapping
    public ResponseEntity<?> checkIn(@RequestBody RedisCheckInRequest request) {
        boolean success = checkInService.checkIn(request.getUserId(), request.getLocation());
        
        if (success) {
            return ResponseEntity.ok(Map.of("message", "Check-in successful"));
        } else {
            return ResponseEntity.badRequest().body(Map.of("message", "Already checked in today"));
        }
    }
    
    @GetMapping("/stats/{userId}")
    public ResponseEntity<Map<Object, Object>> getUserStats(@PathVariable Long userId) {
        Map<Object, Object> stats = checkInService.getUserCheckInStats(userId);
        return ResponseEntity.ok(stats);
    }
    
    @GetMapping("/status/{userId}")
    public ResponseEntity<Map<String, Object>> getCheckInStatus(@PathVariable Long userId) {
        boolean checkedIn = checkInService.isUserCheckedInToday(userId);
        long todayCount = checkInService.getTodayCheckInCount();
        
        Map<String, Object> response = new HashMap<>();
        response.put("checkedIn", checkedIn);
        response.put("todayCount", todayCount);
        
        return ResponseEntity.ok(response);
    }
    
    @GetMapping("/rank")
    public ResponseEntity<Set<ZSetOperations.TypedTuple<Object>>> getCheckInRank(
            @RequestParam(defaultValue = "10") int limit) {
        Set<ZSetOperations.TypedTuple<Object>> rank = checkInService.getCheckInRank(limit);
        return ResponseEntity.ok(rank);
    }
    
    @GetMapping("/monthly/{userId}")
    public ResponseEntity<List<String>> getMonthlyStatus(
            @PathVariable Long userId,
            @RequestParam int year,
            @RequestParam int month) {
        List<String> checkInDays = checkInService.getMonthlyCheckInStatus(userId, year, month);
        return ResponseEntity.ok(checkInDays);
    }
}

@Data
public class RedisCheckInRequest {
    private Long userId;
    private String location;
}

@Service
public class BitmapCheckInService {
    
    @Autowired
    private StringRedisTemplate redisTemplate;
    
    /**
     * 用戶簽到
     */
    public boolean checkIn(Long userId) {
        LocalDate today = LocalDate.now();
        int day = today.getDayOfMonth(); // 獲取當(dāng)月的第幾天
        String key = buildSignKey(userId, today);
        
        // 檢查今天是否已經(jīng)簽到
        Boolean isSigned = redisTemplate.opsForValue().getBit(key, day - 1);
        if (isSigned != null && isSigned) {
            return false; // 已經(jīng)簽到
        }
        
        // 設(shè)置簽到標(biāo)記
        redisTemplate.opsForValue().setBit(key, day - 1, true);
        
        // 設(shè)置過期時(shí)間（確保數(shù)據(jù)不會(huì)永久保存）
        redisTemplate.expire(key, 100, TimeUnit.DAYS);
        
        // 更新連續(xù)簽到記錄
        updateContinuousSignDays(userId);
        
        return true;
    }
    
    /**
     * 更新連續(xù)簽到天數(shù)
     */
    private void updateContinuousSignDays(Long userId) {
        LocalDate today = LocalDate.now();
        String continuousKey = "user:sign:continuous:" + userId;
        
        // 判斷昨天是否簽到
        boolean yesterdayChecked = isSignedIn(userId, today.minusDays(1));
        
        if (yesterdayChecked) {
            // 昨天簽到了，連續(xù)簽到天數(shù)+1
            redisTemplate.opsForValue().increment(continuousKey);
        } else {
            // 昨天沒簽到，重置連續(xù)簽到天數(shù)為1
            redisTemplate.opsForValue().set(continuousKey, "1");
        }
    }
    
    /**
     * 判斷用戶指定日期是否簽到
     */
    public boolean isSignedIn(Long userId, LocalDate date) {
        int day = date.getDayOfMonth();
        String key = buildSignKey(userId, date);
        
        Boolean isSigned = redisTemplate.opsForValue().getBit(key, day - 1);
        return isSigned != null && isSigned;
    }
    
    /**
     * 獲取用戶連續(xù)簽到天數(shù)
     */
    public int getContinuousSignDays(Long userId) {
        String continuousKey = "user:sign:continuous:" + userId;
        String value = redisTemplate.opsForValue().get(continuousKey);
        return value != null ? Integer.parseInt(value) : 0;
    }
    
    /**
     * 獲取用戶當(dāng)月簽到次數(shù)
     */
    public long getMonthSignCount(Long userId, LocalDate date) {
        String key = buildSignKey(userId, date);
        int dayOfMonth = date.lengthOfMonth(); // 當(dāng)月總天數(shù)
        
        return redisTemplate.execute((RedisCallback<Long>) con -> {
            return con.bitCount(key.getBytes());
        });
    }
    
    /**
     * 獲取用戶當(dāng)月簽到情況
     */
    public List<Integer> getMonthSignData(Long userId, LocalDate date) {
        List<Integer> result = new ArrayList<>();
        String key = buildSignKey(userId, date);
        int dayOfMonth = date.lengthOfMonth(); // 當(dāng)月總天數(shù)
        
        for (int i = 0; i < dayOfMonth; i++) {
            Boolean isSigned = redisTemplate.opsForValue().getBit(key, i);
            result.add(isSigned != null && isSigned ? 1 : 0);
        }
        
        return result;
    }
    
    /**
     * 獲取用戶當(dāng)月首次簽到時(shí)間
     */
    public int getFirstSignDay(Long userId, LocalDate date) {
        String key = buildSignKey(userId, date);
        int dayOfMonth = date.lengthOfMonth(); // 當(dāng)月總天數(shù)
        
        for (int i = 0; i < dayOfMonth; i++) {
            Boolean isSigned = redisTemplate.opsForValue().getBit(key, i);
            if (isSigned != null && isSigned) {
                return i + 1; // 返回第一次簽到的日期
            }
        }
        
        return -1; // 本月沒有簽到記錄
    }
    
    /**
     * 構(gòu)建簽到Key
     */
    private String buildSignKey(Long userId, LocalDate date) {
        return String.format("user:sign:%d:%d%02d", userId, date.getYear(), date.getMonthValue());
    }
}

@RestController
@RequestMapping("/api/bitmap-check-in")
public class BitmapCheckInController {
    
    @Autowired
    private BitmapCheckInService checkInService;
    
    @PostMapping("/{userId}")
    public ResponseEntity<?> checkIn(@PathVariable Long userId) {
        boolean success = checkInService.checkIn(userId);
        
        if (success) {
            Map<String, Object> response = new HashMap<>();
            response.put("success", true);
            response.put("continuousDays", checkInService.getContinuousSignDays(userId));
            
            return ResponseEntity.ok(response);
        } else {
            return ResponseEntity.badRequest().body(Map.of("message", "Already checked in today"));
        }
    }
    
    @GetMapping("/{userId}/status")
    public ResponseEntity<?> checkInStatus(
            @PathVariable Long userId,
            @RequestParam(required = false) 
                @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate date) {
        
        if (date == null) {
            date = LocalDate.now();
        }
        
        Map<String, Object> response = new HashMap<>();
        response.put("signedToday", checkInService.isSignedIn(userId, date));
        response.put("continuousDays", checkInService.getContinuousSignDays(userId));
        response.put("monthSignCount", checkInService.getMonthSignCount(userId, date));
        response.put("monthSignData", checkInService.getMonthSignData(userId, date));
        
        return ResponseEntity.ok(response);
    }
    
    @GetMapping("/{userId}/first-sign-day")
    public ResponseEntity<Integer> getFirstSignDay(
            @PathVariable Long userId,
            @RequestParam(required = false) 
                @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate date) {
        
        if (date == null) {
            date = LocalDate.now();
        }
        
        int firstDay = checkInService.getFirstSignDay(userId, date);
        return ResponseEntity.ok(firstDay);
    }
}

-- 簽到位置表
CREATE TABLE check_in_locations (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    latitude DOUBLE NOT NULL,
    longitude DOUBLE NOT NULL,
    radius DOUBLE NOT NULL, -- 有效半徑（米）
    address VARCHAR(255),
    location_type VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 地理位置簽到記錄表
CREATE TABLE geo_check_ins (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    location_id BIGINT NOT NULL,
    check_in_time TIMESTAMP NOT NULL,
    latitude DOUBLE NOT NULL,
    longitude DOUBLE NOT NULL,
    accuracy DOUBLE, -- 定位精度（米）
    is_valid BOOLEAN DEFAULT TRUE, -- 是否有效簽到
    device_info VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (location_id) REFERENCES check_in_locations(id)
);

@Data
@TableName("check_in_locations")
public class CheckInLocation {
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;
    
    private String name;
    
    private Double latitude;
    
    private Double longitude;
    
    private Double radius; // 單位：米
    
    private String address;
    
    @TableField("location_type")
    private String locationType;
    
    @TableField("created_at")
    private LocalDateTime createdAt;
}

@Data
@TableName("geo_check_ins")
public class GeoCheckIn {
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;
    
    @TableField("user_id")
    private Long userId;
    
    @TableField("location_id")
    private Long locationId;
    
    @TableField("check_in_time")
    private LocalDateTime checkInTime;
    
    private Double latitude;
    
    private Double longitude;
    
    private Double accuracy;
    
    @TableField("is_valid")
    private Boolean isValid = true;
    
    @TableField("device_info")
    private String deviceInfo;
}

@Mapper
public interface CheckInLocationMapper extends BaseMapper<CheckInLocation> {
    
    @Select("SELECT * FROM check_in_locations WHERE location_type = #{locationType}")
    List<CheckInLocation> findByLocationType(@Param("locationType") String locationType);
}

@Mapper
public interface GeoCheckInMapper extends BaseMapper<GeoCheckIn> {
    
    @Select("SELECT * FROM geo_check_ins WHERE user_id = #{userId} AND check_in_time BETWEEN #{startTime} AND #{endTime}")
    List<GeoCheckIn> findByUserIdAndCheckInTimeBetween(
            @Param("userId") Long userId, 
            @Param("startTime") LocalDateTime startTime, 
            @Param("endTime") LocalDateTime endTime);
    
    @Select("SELECT * FROM geo_check_ins WHERE user_id = #{userId} AND location_id = #{locationId} " +
            "AND DATE(check_in_time) = DATE(#{date})")
    GeoCheckIn findByUserIdAndLocationIdAndDate(
            @Param("userId") Long userId, 
            @Param("locationId") Long locationId, 
            @Param("date") LocalDateTime date);
}

@Service
@Transactional
public class GeoCheckInService {
    
    @Autowired
    private CheckInLocationMapper locationMapper;
    
    @Autowired
    private GeoCheckInMapper geoCheckInMapper;
    
    @Autowired
    private UserMapper userMapper;
    
    @Autowired
    private StringRedisTemplate redisTemplate;
    
    private static final String GEO_KEY = "geo:locations";
    
    @PostConstruct
    public void init() {
        // 將所有簽到位置加載到Redis GEO中
        List<CheckInLocation> locations = locationMapper.selectList(null);
        
        if (!locations.isEmpty()) {
            Map<String, Point> locationPoints = new HashMap<>();
            
            for (CheckInLocation location : locations) {
                locationPoints.put(location.getId().toString(), 
                        new Point(location.getLongitude(), location.getLatitude()));
            }
            
            redisTemplate.opsForGeo().add(GEO_KEY, locationPoints);
        }
    }
    
    /**
     * 添加新的簽到地點(diǎn)
     */
    public CheckInLocation addCheckInLocation(CheckInLocation location) {
        location.setCreatedAt(LocalDateTime.now());
        locationMapper.insert(location);
        
        // 添加到Redis GEO
        redisTemplate.opsForGeo().add(GEO_KEY, 
                new Point(location.getLongitude(), location.getLatitude()), 
                location.getId().toString());
        
        return location;
    }
    
    /**
     * 用戶地理位置簽到
     */
    public GeoCheckIn checkIn(Long userId, Long locationId, Double latitude, 
                              Double longitude, Double accuracy, String deviceInfo) {
        // 驗(yàn)證用戶和位置是否存在
        User user = userMapper.selectById(userId);
        if (user == null) {
            throw new RuntimeException("User not found");
        }
        
        CheckInLocation location = locationMapper.selectById(locationId);
        if (location == null) {
            throw new RuntimeException("Check-in location not found");
        }
        
        // 檢查今天是否已經(jīng)在該位置簽到
        LocalDateTime now = LocalDateTime.now();
        GeoCheckIn existingCheckIn = geoCheckInMapper
                .findByUserIdAndLocationIdAndDate(userId, locationId, now);
        
        if (existingCheckIn != null) {
            throw new RuntimeException("Already checked in at this location today");
        }
        
        // 驗(yàn)證用戶是否在簽到范圍內(nèi)
        boolean isWithinRange = isWithinCheckInRange(
                latitude, longitude, location.getLatitude(), location.getLongitude(), location.getRadius());
        
        // 創(chuàng)建簽到記錄
        GeoCheckIn checkIn = new GeoCheckIn();
        checkIn.setUserId(userId);
        checkIn.setLocationId(locationId);
        checkIn.setCheckInTime(now);
        checkIn.setLatitude(latitude);
        checkIn.setLongitude(longitude);
        checkIn.setAccuracy(accuracy);
        checkIn.setIsValid(isWithinRange);
        checkIn.setDeviceInfo(deviceInfo);
        
        geoCheckInMapper.insert(checkIn);
        
        return checkIn;
    }
    
    /**
     * 檢查用戶是否在簽到范圍內(nèi)
     */
    private boolean isWithinCheckInRange(Double userLat, Double userLng, 
                                        Double locationLat, Double locationLng, Double radius) {
        // 使用Redis GEO計(jì)算距離
        Distance distance = redisTemplate.opsForGeo().distance(
                GEO_KEY,
                locationLat + "," + locationLng,
                userLat + "," + userLng,
                Metrics.METERS
        );
        
        return distance != null && distance.getValue() <= radius;
    }
    
    /**
     * 查找附近的簽到地點(diǎn)
     */
    public List<GeoResult<RedisGeoCommands.GeoLocation<String>>> findNearbyLocations(
            Double latitude, Double longitude, Double radius) {
        
        Circle circle = new Circle(new Point(longitude, latitude), new Distance(radius, Metrics.METERS));
        RedisGeoCommands.GeoRadiusCommandArgs args = RedisGeoCommands.GeoRadiusCommandArgs
                .newGeoRadiusArgs().includeDistance().sortAscending();
        
        GeoResults<RedisGeoCommands.GeoLocation<String>> results = redisTemplate.opsForGeo()
                .radius(GEO_KEY, circle, args);
        
        return results != null ? results.getContent() : Collections.emptyList();
    }
    
    /**
     * 獲取用戶簽到歷史
     */
    public List<GeoCheckIn> getUserCheckInHistory(Long userId, LocalDate startDate, LocalDate endDate) {
        LocalDateTime startDateTime = startDate.atStartOfDay();
        LocalDateTime endDateTime = endDate.atTime(23, 59, 59);
        
        return geoCheckInMapper.findByUserIdAndCheckInTimeBetween(
                userId, startDateTime, endDateTime);
    }
}

@RestController
@RequestMapping("/api/geo-check-in")
public class GeoCheckInController {
    
    @Autowired
    private GeoCheckInService geoCheckInService;
    
    @PostMapping("/locations")
    public ResponseEntity<CheckInLocation> addLocation(@RequestBody CheckInLocation location) {
        CheckInLocation savedLocation = geoCheckInService.addCheckInLocation(location);
        return ResponseEntity.ok(savedLocation);
    }
    
    @PostMapping
    public ResponseEntity<?> checkIn(@RequestBody GeoCheckInRequest request) {
        try {
            GeoCheckIn checkIn = geoCheckInService.checkIn(
                    request.getUserId(),
                    request.getLocationId(),
                    request.getLatitude(),
                    request.getLongitude(),
                    request.getAccuracy(),
                    request.getDeviceInfo()
            );
            
            Map<String, Object> response = new HashMap<>();
            response.put("id", checkIn.getId());
            response.put("checkInTime", checkIn.getCheckInTime());
            response.put("isValid", checkIn.getIsValid());
            
            if (!checkIn.getIsValid()) {
                response.put("message", "You are not within the valid check-in range");
            }
            
            return ResponseEntity.ok(response);
        } catch (RuntimeException e) {
            return ResponseEntity.badRequest().body(Map.of("message", e.getMessage()));
        }
    }
    
    @GetMapping("/nearby")
    public ResponseEntity<?> findNearbyLocations(
            @RequestParam Double latitude,
            @RequestParam Double longitude,
            @RequestParam(defaultValue = "500") Double radius) {
        
        List<GeoResult<RedisGeoCommands.GeoLocation<String>>> locations = 
                geoCheckInService.findNearbyLocations(latitude, longitude, radius);
        
        return ResponseEntity.ok(locations);
    }
    
    @GetMapping("/history/{userId}")
    public ResponseEntity<List<GeoCheckIn>> getUserHistory(
            @PathVariable Long userId,
            @RequestParam @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate startDate,
            @RequestParam @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate endDate) {
        
        List<GeoCheckIn> history = geoCheckInService.getUserCheckInHistory(userId, startDate, endDate);
        return ResponseEntity.ok(history);
    }
}

@Data
public class GeoCheckInRequest {
    private Long userId;
    private Long locationId;
    private Double latitude;
    private Double longitude;
    private Double accuracy;
    private String deviceInfo;
}

-- 簽到活動(dòng)表
CREATE TABLE check_in_events (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    description VARCHAR(500),
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NOT NULL,
    location VARCHAR(255),
    organizer_id BIGINT,
    qr_code_refresh_interval INT DEFAULT 60, -- 二維碼刷新間隔（秒）
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (organizer_id) REFERENCES users(id)
);

-- 二維碼簽到記錄表
CREATE TABLE qr_check_ins (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    event_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    check_in_time TIMESTAMP NOT NULL,
    qr_code_token VARCHAR(100) NOT NULL,
    ip_address VARCHAR(50),
    device_info VARCHAR(255),
    FOREIGN KEY (event_id) REFERENCES check_in_events(id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    UNIQUE KEY unique_event_user (event_id, user_id)
);

@Data
@TableName("check_in_events")
public class CheckInEvent {
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;
    
    private String title;
    
    private String description;
    
    @TableField("start_time")
    private LocalDateTime startTime;
    
    @TableField("end_time")
    private LocalDateTime endTime;
    
    private String location;
    
    @TableField("organizer_id")
    private Long organizerId;
    
    @TableField("qr_code_refresh_interval")
    private Integer qrCodeRefreshInterval = 60; // 默認(rèn)60秒
    
    @TableField("created_at")
    private LocalDateTime createdAt;
    
    @TableField(exist = false)
    private String currentQrCode;
}

@Data
@TableName("qr_check_ins")
public class QrCheckIn {
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;
    
    @TableField("event_id")
    private Long eventId;
    
    @TableField("user_id")
    private Long userId;
    
    @TableField("check_in_time")
    private LocalDateTime checkInTime;
    
    @TableField("qr_code_token")
    private String qrCodeToken;
    
    @TableField("ip_address")
    private String ipAddress;
    
    @TableField("device_info")
    private String deviceInfo;
}

@Service
public class QrCodeService {
    
    @Value("${qrcode.secret:defaultSecretKey}")
    private String secretKey;
    
    @Autowired
    private StringRedisTemplate redisTemplate;
    
    /**
     * 生成帶簽名的二維碼內(nèi)容
     */
    public String generateQrCodeContent(Long eventId) {
        long timestamp = System.currentTimeMillis();
        String content = eventId + ":" + timestamp;
        String signature = generateSignature(content);
        
        // 創(chuàng)建完整的二維碼內(nèi)容
        String qrCodeContent = content + ":" + signature;
        
        // 保存到Redis，設(shè)置過期時(shí)間
        String redisKey = "qrcode:event:" + eventId + ":" + timestamp;
        redisTemplate.opsForValue().set(redisKey, qrCodeContent, 5, TimeUnit.MINUTES);
        
        return qrCodeContent;
    }
    
    /**
     * 驗(yàn)證二維碼內(nèi)容
     */
    public boolean validateQrCode(String qrCodeContent) {
        String[] parts = qrCodeContent.split(":");
        if (parts.length != 3) {
            return false;
        }
        
        String eventId = parts[0];
        String timestamp = parts[1];
        String providedSignature = parts[2];
        
        // 驗(yàn)證簽名
        String content = eventId + ":" + timestamp;
        String expectedSignature = generateSignature(content);
        
        if (!expectedSignature.equals(providedSignature)) {
            return false;
        }
        
        // 驗(yàn)證二維碼是否在Redis中存在（防止重復(fù)使用）
        String redisKey = "qrcode:event:" + eventId + ":" + timestamp;
        Boolean exists = redisTemplate.hasKey(redisKey);
        
        return exists != null && exists;
    }
    
    /**
     * 生成二維碼圖片
     */
    public byte[] generateQrCodeImage(String content, int width, int height) throws Exception {
        QRCodeWriter qrCodeWriter = new QRCodeWriter();
        BitMatrix bitMatrix = qrCodeWriter.encode(content, BarcodeFormat.QR_CODE, width, height);
        
        ByteArrayOutputStream pngOutputStream = new ByteArrayOutputStream();
        MatrixToImageWriter.writeToStream(bitMatrix, "PNG", pngOutputStream);
        
        return pngOutputStream.toByteArray();
    }
    
    /**
     * 生成內(nèi)容簽名
     */
    private String generateSignature(String content) {
        try {
            Mac sha256_HMAC = Mac.getInstance("HmacSHA256");
            SecretKeySpec secret_key = new SecretKeySpec(secretKey.getBytes(), "HmacSHA256");
            sha256_HMAC.init(secret_key);
            
            byte[] hash = sha256_HMAC.doFinal(content.getBytes());
            return Base64.getEncoder().encodeToString(hash);
        } catch (Exception e) {
            throw new RuntimeException("Failed to generate signature", e);
        }
    }
}

@Service
@Transactional
public class QrCheckInService {
    
    @Autowired
    private CheckInEventMapper eventMapper;
    
    @Autowired
    private QrCheckInMapper qrCheckInMapper;
    
    @Autowired
    private UserMapper userMapper;
    
    @Autowired
    private QrCodeService qrCodeService;
    
    /**
     * 創(chuàng)建簽到活動(dòng)
     */
    public CheckInEvent createEvent(CheckInEvent event) {
        event.setCreatedAt(LocalDateTime.now());
        eventMapper.insert(event);
        return event;
    }
    
    /**
     * 獲取活動(dòng)信息，包括當(dāng)前二維碼
     */
    public CheckInEvent getEventWithQrCode(Long eventId) {
        CheckInEvent event = eventMapper.selectById(eventId);
        if (event == null) {
            throw new RuntimeException("Event not found");
        }
        
        // 檢查活動(dòng)是否在有效期內(nèi)
        LocalDateTime now = LocalDateTime.now();
        if (now.isBefore(event.getStartTime()) || now.isAfter(event.getEndTime())) {
            throw new RuntimeException("Event is not active");
        }
        
        // 生成當(dāng)前二維碼
        String qrCodeContent = qrCodeService.generateQrCodeContent(eventId);
        event.setCurrentQrCode(qrCodeContent);
        
        return event;
    }
    
    /**
     * 用戶通過二維碼簽到
     */
    public QrCheckIn checkIn(String qrCodeContent, Long userId, String ipAddress, String deviceInfo) {
        // 驗(yàn)證二維碼
        if (!qrCodeService.validateQrCode(qrCodeContent)) {
            throw new RuntimeException("Invalid QR code");
        }
        
        // 解析二維碼內(nèi)容
        String[] parts = qrCodeContent.split(":");
        Long eventId = Long.parseLong(parts[0]);
        
        // 驗(yàn)證活動(dòng)和用戶
        CheckInEvent event = eventMapper.selectById(eventId);
        if (event == null) {
            throw new RuntimeException("Event not found");
        }
        
        User user = userMapper.selectById(userId);
        if (user == null) {
            throw new RuntimeException("User not found");
        }
        
        // 檢查活動(dòng)是否在有效期內(nèi)
        LocalDateTime now = LocalDateTime.now();
        if (now.isBefore(event.getStartTime()) || now.isAfter(event.getEndTime())) {
            throw new RuntimeException("Event is not active");
        }
        
        // 檢查用戶是否已經(jīng)簽到
        if (qrCheckInMapper.existsByEventIdAndUserId(eventId, userId) > 0) {
            throw new RuntimeException("User already checked in for this event");
        }
        
        // 創(chuàng)建簽到記錄
        QrCheckIn checkIn = new QrCheckIn();
        checkIn.setEventId(eventId);
        checkIn.setUserId(userId);
        checkIn.setCheckInTime(now);
        checkIn.setQrCodeToken(qrCodeContent);
        checkIn.setIpAddress(ipAddress);
        checkIn.setDeviceInfo(deviceInfo);
        
        qrCheckInMapper.insert(checkIn);
        return checkIn;
    }
    
    /**
     * 獲取活動(dòng)簽到列表
     */
    public List<QrCheckIn> getEventCheckIns(Long eventId) {
        return qrCheckInMapper.findByEventIdOrderByCheckInTimeDesc(eventId);
    }
    
    /**
     * 獲取用戶簽到歷史
     */
    public List<QrCheckIn> getUserCheckIns(Long userId) {
        return qrCheckInMapper.findByUserIdOrderByCheckInTimeDesc(userId);
    }
    
    /**
     * 獲取活動(dòng)簽到統(tǒng)計(jì)
     */
    public Map<String, Object> getEventStatistics(Long eventId) {
        CheckInEvent event = eventMapper.selectById(eventId);
        if (event == null) {
            throw new RuntimeException("Event not found");
        }
        
        long totalCheckIns = qrCheckInMapper.countByEventId(eventId);
        
        Map<String, Object> statistics = new HashMap<>();
        statistics.put("eventId", eventId);
        statistics.put("title", event.getTitle());
        statistics.put("startTime", event.getStartTime());
        statistics.put("endTime", event.getEndTime());
        statistics.put("totalCheckIns", totalCheckIns);
        
        return statistics;
    }
}

@RestController
@RequestMapping("/api/qr-check-in")
public class QrCheckInController {
    
    @Autowired
    private QrCheckInService checkInService;
    
    @Autowired
    private QrCodeService qrCodeService;
    
    @PostMapping("/events")
    public ResponseEntity<CheckInEvent> createEvent(@RequestBody CheckInEvent event) {
        CheckInEvent createdEvent = checkInService.createEvent(event);
        return ResponseEntity.ok(createdEvent);
    }
    
    @GetMapping("/events/{eventId}")
    public ResponseEntity<CheckInEvent> getEvent(@PathVariable Long eventId) {
        CheckInEvent event = checkInService.getEventWithQrCode(eventId);
        return ResponseEntity.ok(event);
    }
    
    @GetMapping("/events/{eventId}/qrcode")
    public ResponseEntity<?> getEventQrCode(
            @PathVariable Long eventId,
            @RequestParam(defaultValue = "300") int width,
            @RequestParam(defaultValue = "300") int height) {
        
        try {
            CheckInEvent event = checkInService.getEventWithQrCode(eventId);
            byte[] qrCodeImage = qrCodeService.generateQrCodeImage(
                    event.getCurrentQrCode(), width, height);
            
            return ResponseEntity.ok()
                    .contentType(MediaType.IMAGE_PNG)
                    .body(qrCodeImage);
        } catch (Exception e) {
            return ResponseEntity.badRequest().body(Map.of("message", e.getMessage()));
        }
    }
    
    @PostMapping("/check-in")
    public ResponseEntity<?> checkIn(@RequestBody QrCheckInRequest request, 
                                     HttpServletRequest httpRequest) {
        try {
            String ipAddress = httpRequest.getRemoteAddr();
            
            QrCheckIn checkIn = checkInService.checkIn(
                    request.getQrCodeContent(),
                    request.getUserId(),
                    ipAddress,
                    request.getDeviceInfo()
            );
            
            return ResponseEntity.ok(checkIn);
        } catch (RuntimeException e) {
            return ResponseEntity.badRequest().body(Map.of("message", e.getMessage()));
        }
    }
    
    @GetMapping("/events/{eventId}/check-ins")
    public ResponseEntity<List<QrCheckIn>> getEventCheckIns(@PathVariable Long eventId) {
        List<QrCheckIn> checkIns = checkInService.getEventCheckIns(eventId);
        return ResponseEntity.ok(checkIns);
    }
    
    @GetMapping("/users/{userId}/check-ins")
    public ResponseEntity<List<QrCheckIn>> getUserCheckIns(@PathVariable Long userId) {
        List<QrCheckIn> checkIns = checkInService.getUserCheckIns(userId);
        return ResponseEntity.ok(checkIns);
    }
    
    @GetMapping("/events/{eventId}/statistics")
    public ResponseEntity<Map<String, Object>> getEventStatistics(@PathVariable Long eventId) {
        Map<String, Object> statistics = checkInService.getEventStatistics(eventId);
        return ResponseEntity.ok(statistics);
    }
}

@Data
public class QrCheckInRequest {
    private String qrCodeContent;
    private Long userId;
    private String deviceInfo;
}