-- Script to remove duplicate check-ins from daily_checkins table
-- For each (date, room_id) pair, keep only the record with the latest updated_at

-- Step 1: Identify duplicates and mark them for deletion
-- This creates a temporary table with IDs of records to delete
CREATE TEMPORARY TABLE duplicates_to_delete AS
SELECT 
    t1.id
FROM 
    daily_checkins t1
INNER JOIN 
    daily_checkins t2 
    ON t1.date = t2.date 
    AND t1.room_id = t2.room_id 
    AND t1.id < t2.id  -- t1 is older than t2
WHERE 
    t1.archived_at IS NULL;  -- Only consider active records

-- Step 2: Delete the identified duplicates
DELETE FROM daily_checkins 
WHERE id IN (SELECT id FROM duplicates_to_delete);

-- Step 3: Drop the temporary table
DROP TEMPORARY TABLE IF EXISTS duplicates_to_delete;

-- Verify: Check remaining duplicates (should be 0)
SELECT date, room_id, COUNT(*) as count 
FROM daily_checkins 
WHERE archived_at IS NULL
GROUP BY date, room_id 
HAVING count > 1;
