-- Migration: Remove room_number column and add unique index on date + room_id
-- This migration removes the duplicate room_number column and adds a unique constraint
-- to prevent duplicate check-ins for the same room on the same day

-- Step 1: Delete existing unique index if it exists (for safety)
DROP INDEX IF EXISTS date_room_idx ON daily_checkins;

-- Step 2: Add unique index on date + room_id
-- This physically prevents creating two check-ins for the same room on the same day
CREATE UNIQUE INDEX date_room_idx ON daily_checkins (date, room_id);

-- Step 3: Drop the room_number column (it's now redundant with room_id)
ALTER TABLE daily_checkins DROP COLUMN room_number;

-- Step 4: Update room_id column to be NOT NULL (all existing records should have room_id)
-- First, ensure all records have room_id (should be true from snapshots)
UPDATE daily_checkins SET room_id = SUBSTRING(id, 1, 10) WHERE room_id IS NULL;

-- Then, make the column NOT NULL
ALTER TABLE daily_checkins MODIFY COLUMN room_id VARCHAR(10) NOT NULL;
