-- ============================================================
-- schema_addon_alter.sql
-- 기존 테이블 컬럼 안전 추가 (중복 에러 없음)
-- MySQL 8.x / InnoDB / utf8mb4_unicode_ci
-- 실행방법: schema_addon.sql 실행 후 이 파일 단독 실행
-- ============================================================
-- 원리: information_schema.COLUMNS 조회로 컬럼 존재 여부 확인 후
--       없을 때만 ALTER TABLE 실행 → 중복 에러 완전 방지
-- COMMENT 문자열을 동적 SQL 안에 넣지 않아 따옴표 충돌 없음
-- ============================================================


DROP PROCEDURE IF EXISTS _ac;

DELIMITER $$
CREATE PROCEDURE _ac(IN t VARCHAR(64), IN c VARCHAR(64), IN ddl TEXT)
BEGIN
  DECLARE n INT DEFAULT 0;
  SELECT COUNT(*) INTO n
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME   = t
    AND COLUMN_NAME  = c;
  IF n = 0 THEN
    SET @s = CONCAT('ALTER TABLE `', t, '` ADD COLUMN ', ddl);
    PREPARE st FROM @s;
    EXECUTE st;
    DEALLOCATE PREPARE st;
  END IF;
END$$
DELIMITER ;

-- ============================================================
-- A1. lms_notification_rule  (3단계 독려 / 슬라이드 52-55)
-- ============================================================
CALL _ac('lms_notification_rule','stage2_yn',
  '`stage2_yn` CHAR(1) NOT NULL DEFAULT "N" AFTER `send_type`');

CALL _ac('lms_notification_rule','stage2_progress',
  '`stage2_progress` TINYINT DEFAULT NULL AFTER `stage2_yn`');

CALL _ac('lms_notification_rule','stage2_days_before',
  '`stage2_days_before` TINYINT DEFAULT NULL AFTER `stage2_progress`');

CALL _ac('lms_notification_rule','stage3_yn',
  '`stage3_yn` CHAR(1) NOT NULL DEFAULT "N" AFTER `stage2_days_before`');

CALL _ac('lms_notification_rule','stage3_progress',
  '`stage3_progress` TINYINT DEFAULT NULL AFTER `stage3_yn`');

CALL _ac('lms_notification_rule','stage3_days_before',
  '`stage3_days_before` TINYINT DEFAULT NULL AFTER `stage3_progress`');

CALL _ac('lms_notification_rule','exam_not_taken_yn',
  '`exam_not_taken_yn` CHAR(1) NOT NULL DEFAULT "N" AFTER `stage3_days_before`');

CALL _ac('lms_notification_rule','task_not_submit_yn',
  '`task_not_submit_yn` CHAR(1) NOT NULL DEFAULT "N" AFTER `exam_not_taken_yn`');

-- ============================================================
-- A2. lms_course  (학습후기 + 심사계정 / 슬라이드 18,70)
-- ============================================================
CALL _ac('lms_course','review_yn',
  '`review_yn` CHAR(1) NOT NULL DEFAULT "N" AFTER `display_yn`');

CALL _ac('lms_course','review_open_yn',
  '`review_open_yn` CHAR(1) NOT NULL DEFAULT "Y" AFTER `review_yn`');

CALL _ac('lms_course','review_avg_rating',
  '`review_avg_rating` DECIMAL(3,1) NOT NULL DEFAULT 0.0 AFTER `review_open_yn`');

CALL _ac('lms_course','review_count',
  '`review_count` INT NOT NULL DEFAULT 0 AFTER `review_avg_rating`');

CALL _ac('lms_course','has_review_account',
  '`has_review_account` CHAR(1) NOT NULL DEFAULT "N" AFTER `review_count`');

-- ============================================================
-- A3. lms_admin  (IP제한 + 계정잠금 / 슬라이드 81)
-- last_login_at, last_login_ip 는 이미 존재하므로 제외
-- ============================================================
CALL _ac('lms_admin','ip_restrict_yn',
  '`ip_restrict_yn` CHAR(1) NOT NULL DEFAULT "N" AFTER `admin_type`');

CALL _ac('lms_admin','login_fail_count',
  '`login_fail_count` TINYINT NOT NULL DEFAULT 0 AFTER `last_login_ip`');

CALL _ac('lms_admin','locked_until',
  '`locked_until` DATETIME DEFAULT NULL AFTER `login_fail_count`');

-- ============================================================
-- A4. lms_member  (상담 캐시 / 슬라이드 60-65)
-- login_count 없음 → AFTER last_login_at
-- ============================================================
CALL _ac('lms_member','counseling_count',
  '`counseling_count` INT NOT NULL DEFAULT 0 AFTER `last_login_at`');

CALL _ac('lms_member','last_counseling_at',
  '`last_counseling_at` DATETIME DEFAULT NULL AFTER `counseling_count`');

-- ============================================================
-- A5. lms_site  (문구/메뉴 / 슬라이드 74-76)
-- footer_info 없음 → AFTER maintenance_msg
-- ============================================================
CALL _ac('lms_site','footer_text',
  '`footer_text` TEXT AFTER `maintenance_msg`');

CALL _ac('lms_site','login_notice',
  '`login_notice` VARCHAR(500) DEFAULT NULL AFTER `footer_text`');

CALL _ac('lms_site','main_notice',
  '`main_notice` VARCHAR(500) DEFAULT NULL AFTER `login_notice`');

CALL _ac('lms_site','use_gnb_menu_yn',
  '`use_gnb_menu_yn` CHAR(1) NOT NULL DEFAULT "N" AFTER `main_notice`');

-- 정리
DROP PROCEDURE IF EXISTS _ac;

SELECT 'schema_addon_alter.sql 완료' AS result;
