/* =========================================================
   안전 삭제 스크립트 (세미콜론만 사용)
   - prefix 기반 테이블명 구성
   - @prefix, @shop_prefix가 환경과 일치하는지 확인 후 실행
   ========================================================= */

SET @schema       := DATABASE();
SET @prefix       := 'g5_';
SET @shop_prefix  := 'g5_shop_';

/* ---------- 테이블명 ----------- */
SET @tbl_config           := CONCAT(@prefix, 'config');
SET @tbl_member           := CONCAT(@prefix, 'member');
SET @tbl_board            := CONCAT(@prefix, 'board');
SET @tbl_stock            := CONCAT(@shop_prefix, 'item_stocksms');
SET @tbl_preset           := CONCAT(@prefix, 'kakao5_preset');
SET @tbl_preset_history   := CONCAT(@prefix, 'kakao5_preset_history');

/* 공용 작업 변수 */
SET @exists := 0;
SET @sql    := '';

/* =========================================================
   g5_config: 알림톡/팝빌 관련 컬럼 삭제 (존재 시에만)
   ========================================================= */

SET @exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @tbl_config AND COLUMN_NAME = 'cf_kakaotalk_use'
);
SET @sql := IF(@exists > 0, CONCAT('ALTER TABLE `', @tbl_config, '` DROP COLUMN `cf_kakaotalk_use`'), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @tbl_config AND COLUMN_NAME = 'cf_popbill_userid'
);
SET @sql := IF(@exists > 0, CONCAT('ALTER TABLE `', @tbl_config, '` DROP COLUMN `cf_popbill_userid`'), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @tbl_config AND COLUMN_NAME = 'cf_popbill_link_id'
);
SET @sql := IF(@exists > 0, CONCAT('ALTER TABLE `', @tbl_config, '` DROP COLUMN `cf_popbill_link_id`'), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @tbl_config AND COLUMN_NAME = 'cf_popbill_secretkey'
);
SET @sql := IF(@exists > 0, CONCAT('ALTER TABLE `', @tbl_config, '` DROP COLUMN `cf_popbill_secretkey`'), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

/* 추가 컬럼 */
SET @exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @tbl_config AND COLUMN_NAME = 'cf_kakaotalk_corpnum'
);
SET @sql := IF(@exists > 0, CONCAT('ALTER TABLE `', @tbl_config, '` DROP COLUMN `cf_kakaotalk_corpnum`'), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @tbl_config AND COLUMN_NAME = 'cf_kakaotalk_sender_hp'
);
SET @sql := IF(@exists > 0, CONCAT('ALTER TABLE `', @tbl_config, '` DROP COLUMN `cf_kakaotalk_sender_hp`'), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

/* =========================================================
   g5_member: 게시판 알림 설정 컬럼만 삭제 (광고성 동의 관련 제외)
   ========================================================= */

SET @exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @tbl_member AND COLUMN_NAME = 'mb_board_post'
);
SET @sql := IF(@exists > 0, CONCAT('ALTER TABLE `', @tbl_member, '` DROP COLUMN `mb_board_post`'), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @tbl_member AND COLUMN_NAME = 'mb_board_reply'
);
SET @sql := IF(@exists > 0, CONCAT('ALTER TABLE `', @tbl_member, '` DROP COLUMN `mb_board_reply`'), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @tbl_member AND COLUMN_NAME = 'mb_board_comment'
);
SET @sql := IF(@exists > 0, CONCAT('ALTER TABLE `', @tbl_member, '` DROP COLUMN `mb_board_comment`'), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @tbl_member AND COLUMN_NAME = 'mb_board_recomment'
);
SET @sql := IF(@exists > 0, CONCAT('ALTER TABLE `', @tbl_member, '` DROP COLUMN `mb_board_recomment`'), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

/* =========================================================
   g5_shop_item_stocksms: ss_channel 컬럼 삭제
   ========================================================= */

SET @exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @tbl_stock AND COLUMN_NAME = 'ss_channel'
);
SET @sql := IF(@exists > 0, CONCAT('ALTER TABLE `', @tbl_stock, '` DROP COLUMN `ss_channel`'), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

/* =========================================================
   g5_board: bo_use_kakaotalk 컬럼 삭제
   ========================================================= */

SET @exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @tbl_board AND COLUMN_NAME = 'bo_use_kakaotalk'
);
SET @sql := IF(@exists > 0, CONCAT('ALTER TABLE `', @tbl_board, '` DROP COLUMN `bo_use_kakaotalk`'), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

/* =========================================================
   신규 테이블 삭제 (존재 시에만)
   ========================================================= */

SET @exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @tbl_preset_history
);
SET @sql := IF(@exists > 0, CONCAT('DROP TABLE `', @tbl_preset_history, '`'), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @tbl_preset
);
SET @sql := IF(@exists > 0, CONCAT('DROP TABLE `', @tbl_preset, '`'), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;