-- 删除长时间未支付停车费的用户车辆信息和相关记录
DELETE FROM VehicleInformation
WHERE user_id IN (
    SELECT ui.user_id
    FROM UserInformation ui
    JOIN ParkingRecord pr ON ui.user_id = pr.user_id
    WHERE pr.parking_fee > 0 AND pr.end_time < NOW() - INTERVAL 30 DAY
);

-- 删除用户的 ICCardInformation 信息
DELETE FROM ICCardInformation
WHERE user_id IN (
    SELECT ui.user_id
    FROM UserInformation ui
    JOIN ParkingRecord pr ON ui.user_id = pr.user_id
    WHERE pr.parking_fee > 0 AND pr.end_time < NOW() - INTERVAL 30 DAY
);

-- 删除用户车辆信息
DELETE FROM UserInformation
WHERE user_id IN (
    SELECT ui.user_id
    FROM UserInformation ui
    JOIN ParkingRecord pr ON ui.user_id = pr.user_id
    WHERE pr.parking_fee > 0 AND pr.end_time < NOW() - INTERVAL 30 DAY
);

-- 释放过去一年内未使用过的停车位
DELETE FROM ParkingSpotInformation
WHERE parking_spot_id NOT IN (
    SELECT DISTINCT parking_spot_id
    FROM ParkingRecord
    WHERE end_time > NOW() - INTERVAL 1 YEAR
);

