-- ============================================
-- 폐기 반출 시스템 고도화 - DB 점진적 마이그레이션
-- 에러 발생 시 건너뛰기 (이미 존재하는 컬럼 등)
-- ============================================

-- 1. products 테이블에 원가 컬럼 추가
-- ============================================
SET @alter_sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'products' 
     AND COLUMN_NAME = 'cost_price') = 0,
    'ALTER TABLE products ADD COLUMN cost_price DECIMAL(10,2) DEFAULT 0 COMMENT "매입 원가 (부가세 제외)"',
    'SELECT "cost_price already exists" as message'
);
PREPARE stmt FROM @alter_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 2. disposal_removals 테이블 고도화
-- ============================================

-- 2-1. 손실 금액 컬럼들
SET @alter_sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'disposal_removals' 
     AND COLUMN_NAME = 'total_cost_loss') = 0,
    'ALTER TABLE disposal_removals ADD COLUMN total_cost_loss DECIMAL(10,2) DEFAULT 0 COMMENT "원가 손실 (회계 기준)"',
    'SELECT "total_cost_loss already exists" as message'
);
PREPARE stmt FROM @alter_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @alter_sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'disposal_removals' 
     AND COLUMN_NAME = 'total_revenue_loss') = 0,
    'ALTER TABLE disposal_removals ADD COLUMN total_revenue_loss DECIMAL(10,2) DEFAULT 0 COMMENT "매출 손실 (기회비용)"',
    'SELECT "total_revenue_loss already exists" as message'
);
PREPARE stmt FROM @alter_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @alter_sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'disposal_removals' 
     AND COLUMN_NAME = 'total_margin_loss') = 0,
    'ALTER TABLE disposal_removals ADD COLUMN total_margin_loss DECIMAL(10,2) DEFAULT 0 COMMENT "마진 손실 (순이익 영향)"',
    'SELECT "total_margin_loss already exists" as message'
);
PREPARE stmt FROM @alter_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 2-2. 승인 워크플로우 컬럼들
SET @alter_sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'disposal_removals' 
     AND COLUMN_NAME = 'requires_approval') = 0,
    'ALTER TABLE disposal_removals ADD COLUMN requires_approval BOOLEAN DEFAULT TRUE COMMENT "승인 필요 여부"',
    'SELECT "requires_approval already exists" as message'
);
PREPARE stmt FROM @alter_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @alter_sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'disposal_removals' 
     AND COLUMN_NAME = 'approval_level') = 0,
    'ALTER TABLE disposal_removals ADD COLUMN approval_level ENUM("none", "manager", "branch", "owner") DEFAULT "manager" COMMENT "필요 승인 레벨"',
    'SELECT "approval_level already exists" as message'
);
PREPARE stmt FROM @alter_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @alter_sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'disposal_removals' 
     AND COLUMN_NAME = 'approval_status') = 0,
    'ALTER TABLE disposal_removals ADD COLUMN approval_status ENUM("pending", "approved", "rejected") DEFAULT "pending" COMMENT "승인 상태"',
    'SELECT "approval_status already exists" as message'
);
PREPARE stmt FROM @alter_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @alter_sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'disposal_removals' 
     AND COLUMN_NAME = 'approved_by') = 0,
    'ALTER TABLE disposal_removals ADD COLUMN approved_by INT NULL COMMENT "승인자 user_id"',
    'SELECT "approved_by already exists" as message'
);
PREPARE stmt FROM @alter_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @alter_sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'disposal_removals' 
     AND COLUMN_NAME = 'approved_at') = 0,
    'ALTER TABLE disposal_removals ADD COLUMN approved_at DATETIME NULL COMMENT "승인 일시"',
    'SELECT "approved_at already exists" as message'
);
PREPARE stmt FROM @alter_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @alter_sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'disposal_removals' 
     AND COLUMN_NAME = 'rejection_reason') = 0,
    'ALTER TABLE disposal_removals ADD COLUMN rejection_reason TEXT NULL COMMENT "반려 사유"',
    'SELECT "rejection_reason already exists" as message'
);
PREPARE stmt FROM @alter_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 2-3. 사진 첨부
SET @alter_sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'disposal_removals' 
     AND COLUMN_NAME = 'photos') = 0,
    'ALTER TABLE disposal_removals ADD COLUMN photos JSON NULL COMMENT "첨부 사진 경로 배열"',
    'SELECT "photos already exists" as message'
);
PREPARE stmt FROM @alter_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 2-4. 반출 일정
SET @alter_sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'disposal_removals' 
     AND COLUMN_NAME = 'preferred_date') = 0,
    'ALTER TABLE disposal_removals ADD COLUMN preferred_date DATE NULL COMMENT "희망 반출일"',
    'SELECT "preferred_date already exists" as message'
);
PREPARE stmt FROM @alter_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @alter_sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'disposal_removals' 
     AND COLUMN_NAME = 'preferred_time') = 0,
    'ALTER TABLE disposal_removals ADD COLUMN preferred_time ENUM("morning", "afternoon", "evening") NULL COMMENT "희망 시간대"',
    'SELECT "preferred_time already exists" as message'
);
PREPARE stmt FROM @alter_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @alter_sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'disposal_removals' 
     AND COLUMN_NAME = 'vehicle_info') = 0,
    'ALTER TABLE disposal_removals ADD COLUMN vehicle_info VARCHAR(255) NULL COMMENT "차량 정보"',
    'SELECT "vehicle_info already exists" as message'
);
PREPARE stmt FROM @alter_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @alter_sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'disposal_removals' 
     AND COLUMN_NAME = 'actual_removal_date') = 0,
    'ALTER TABLE disposal_removals ADD COLUMN actual_removal_date DATETIME NULL COMMENT "실제 반출 완료 일시"',
    'SELECT "actual_removal_date already exists" as message'
);
PREPARE stmt FROM @alter_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 2-5. 처리 비용
SET @alter_sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'disposal_removals' 
     AND COLUMN_NAME = 'removal_cost') = 0,
    'ALTER TABLE disposal_removals ADD COLUMN removal_cost DECIMAL(10,2) DEFAULT 0 COMMENT "반출 처리 비용 (운송비 등)"',
    'SELECT "removal_cost already exists" as message'
);
PREPARE stmt FROM @alter_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 2-6. 알림
SET @alter_sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'disposal_removals' 
     AND COLUMN_NAME = 'notification_sent') = 0,
    'ALTER TABLE disposal_removals ADD COLUMN notification_sent BOOLEAN DEFAULT FALSE COMMENT "알림 발송 여부"',
    'SELECT "notification_sent already exists" as message'
);
PREPARE stmt FROM @alter_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @alter_sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'disposal_removals' 
     AND COLUMN_NAME = 'notified_at') = 0,
    'ALTER TABLE disposal_removals ADD COLUMN notified_at DATETIME NULL COMMENT "알림 발송 일시"',
    'SELECT "notified_at already exists" as message'
);
PREPARE stmt FROM @alter_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT '✅ disposal_removals 테이블 업데이트 완료' as result;
