-- ═══════════════════════════════════════════════════════════════
-- Seed: Roommates for Testing
-- Type: Data
-- Version: 2026-05-12
-- Purpose: Add additional beds and residents to create roommate scenarios
--
-- This seed:
-- - Adds bed_101_2 to room_101
-- - Adds resident_6 in bed_101_2 (roommate with resident_1)
-- - Creates resident_stays for the new resident
--
-- Dependencies: 001-auth-schema.sql, 002-file-schema.sql, 005-resident-and-facility-schema.sql, 100-seed-roles-permissions.sql, 101-seed-default-users.sql, 102-seed-residents.sql
-- ═══════════════════════════════════════════════════════════════

DO $$
DECLARE
    admin_user_id UUID;
    -- Fixed UUIDs (matching seed 102)
    room_101_id UUID := '00000001-0001-0001-0001-000000000009';
    bed_101_2_id UUID := '00000001-0001-0001-0001-000000000100';
    resident_6_id UUID := '00000001-0001-0001-0001-000000000101';
BEGIN
    SELECT id INTO admin_user_id FROM users WHERE email = 'admin@bekind.com' LIMIT 1;
    
    IF admin_user_id IS NULL THEN
        RAISE NOTICE 'Admin user not found, skipping roommate seed';
        RETURN;
    END IF;

    -- Add bed_101_2 to room_101 (use code '3' to avoid conflict)
    INSERT INTO beds (id, room_id, name, code, status, created_by, updated_by, created_at, updated_at)
    VALUES
        (bed_101_2_id, room_101_id, 'Giường 2', '3', 'active', admin_user_id, admin_user_id, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
    ON CONFLICT (id) DO NOTHING;

    -- Add resident_6
    INSERT INTO residents (
        id,
        full_name,
        email,
        phone,
        id_card,
        date_of_birth,
        emergency_contact_name,
        emergency_contact_phone,
        emergency_contact_relationship,
        status,
        created_by,
        updated_by,
        created_at,
        updated_at
    ) VALUES
        (
            resident_6_id,
            'Nguyễn Thị Mai',
            'nguyenthimai@example.com',
            '+84911234567',
            '001234567895',
            '1995-06-18',
            'Nguyễn Văn Nam',
            '+84912345678',
            'Chồng',
            'active',
            admin_user_id,
            admin_user_id,
            CURRENT_TIMESTAMP,
            CURRENT_TIMESTAMP
        )
    ON CONFLICT (id) DO NOTHING;

    -- Add resident_stays for resident_6 in bed_101_2
    INSERT INTO resident_stays (id, resident_id, bed_id, move_in_at, move_out_at, created_by, updated_by, created_at, updated_at)
    VALUES
        ('00000001-0001-0001-0001-000000000102', resident_6_id, bed_101_2_id, '2024-03-01'::timestamp, NULL, admin_user_id, admin_user_id, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
    ON CONFLICT (id) DO NOTHING;

    RAISE NOTICE 'Roommate seed completed successfully';
    RAISE NOTICE 'Resident 1 (Nguyễn Văn An) and Resident 6 (Nguyễn Thị Mai) are now roommates in room 101';
END $$;
