-- 회의 참석자 테이블 생성
CREATE TABLE IF NOT EXISTS meeting_participants (
    meeting_id VARCHAR(50) NOT NULL,
    user_id VARCHAR(100) NOT NULL,
    invitation_status VARCHAR(20) DEFAULT 'PENDING',
    attended BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (meeting_id, user_id),
    CONSTRAINT fk_meeting_participants_meeting
        FOREIGN KEY (meeting_id) REFERENCES meetings(meeting_id)
        ON DELETE CASCADE
);

-- 기존 meetings 테이블의 participants 데이터를 meeting_participants 테이블로 마이그레이션
INSERT INTO meeting_participants (meeting_id, user_id, invitation_status, attended, created_at, updated_at)
SELECT
    m.meeting_id,
    TRIM(participant) as user_id,
    'PENDING' as invitation_status,
    FALSE as attended,
    m.created_at,
    m.updated_at
FROM meetings m
CROSS JOIN LATERAL unnest(string_to_array(m.participants, ',')) AS participant
WHERE m.participants IS NOT NULL AND m.participants != '';

-- meetings 테이블에서 participants 컬럼 삭제
ALTER TABLE meetings DROP COLUMN IF EXISTS participants;

-- 인덱스 생성
CREATE INDEX idx_meeting_participants_user_id ON meeting_participants(user_id);
CREATE INDEX idx_meeting_participants_invitation_status ON meeting_participants(invitation_status);
CREATE INDEX idx_meeting_participants_meeting_id_status ON meeting_participants(meeting_id, invitation_status);

-- 코멘트 추가
COMMENT ON TABLE meeting_participants IS '회의 참석자 정보';
COMMENT ON COLUMN meeting_participants.meeting_id IS '회의 ID';
COMMENT ON COLUMN meeting_participants.user_id IS '사용자 ID (이메일)';
COMMENT ON COLUMN meeting_participants.invitation_status IS '초대 상태 (PENDING, ACCEPTED, DECLINED)';
COMMENT ON COLUMN meeting_participants.attended IS '참석 여부';
