-- ============================================================================
-- 마이그레이션: is_deleted 필드 추가 (소프트 삭제 지원)
-- 생성일: 2026-01-28
-- 설명: 모든 테이블(refresh_token 제외)에 is_deleted 필드 및 인덱스 추가
-- ============================================================================

-- 주의: 이 마이그레이션을 실행하기 전에 데이터베이스 백업을 권장합니다.

-- ============================================================================
-- 1. Project 테이블
-- ============================================================================
ALTER TABLE project
ADD COLUMN is_deleted BOOLEAN NOT NULL DEFAULT FALSE COMMENT '소프트 삭제 여부 (True: 삭제됨)';

CREATE INDEX idx_project_is_deleted ON project(is_deleted);

-- ============================================================================
-- 2. Image 테이블
-- ============================================================================
ALTER TABLE image
ADD COLUMN is_deleted BOOLEAN NOT NULL DEFAULT FALSE COMMENT '소프트 삭제 여부 (True: 삭제됨)';

CREATE INDEX idx_image_is_deleted ON image(is_deleted);

-- ============================================================================
-- 3. Lyric 테이블
-- ============================================================================
ALTER TABLE lyric
ADD COLUMN is_deleted BOOLEAN NOT NULL DEFAULT FALSE COMMENT '소프트 삭제 여부 (True: 삭제됨)';

CREATE INDEX idx_lyric_is_deleted ON lyric(is_deleted);

-- ============================================================================
-- 4. Song 테이블
-- ============================================================================
ALTER TABLE song
ADD COLUMN is_deleted BOOLEAN NOT NULL DEFAULT FALSE COMMENT '소프트 삭제 여부 (True: 삭제됨)';

CREATE INDEX idx_song_is_deleted ON song(is_deleted);

-- ============================================================================
-- 5. SongTimestamp 테이블
-- ============================================================================
ALTER TABLE song_timestamp
ADD COLUMN is_deleted BOOLEAN NOT NULL DEFAULT FALSE COMMENT '소프트 삭제 여부 (True: 삭제됨)';

CREATE INDEX idx_song_timestamp_is_deleted ON song_timestamp(is_deleted);

-- ============================================================================
-- 6. Video 테이블
-- ============================================================================
ALTER TABLE video
ADD COLUMN is_deleted BOOLEAN NOT NULL DEFAULT FALSE COMMENT '소프트 삭제 여부 (True: 삭제됨)';

CREATE INDEX idx_video_is_deleted ON video(is_deleted);

-- ============================================================================
-- 7. SocialAccount 테이블
-- ============================================================================
ALTER TABLE social_account
ADD COLUMN is_deleted BOOLEAN NOT NULL DEFAULT FALSE COMMENT '소프트 삭제 여부 (True: 삭제됨)';

CREATE INDEX idx_social_account_is_deleted ON social_account(is_deleted);

-- ============================================================================
-- 검증 쿼리 (마이그레이션 후 실행하여 확인)
-- ============================================================================
-- SELECT
--     TABLE_NAME,
--     COLUMN_NAME,
--     DATA_TYPE,
--     COLUMN_DEFAULT
-- FROM INFORMATION_SCHEMA.COLUMNS
-- WHERE TABLE_SCHEMA = DATABASE()
--   AND COLUMN_NAME = 'is_deleted';