-- ============================================================
-- LMS Schema Addon 3 — ALTER 전용 (v3.0 기획서 반영)
-- Version : 1.0
-- Date    : 2026-03-23
-- Purpose : LMS_전체_상세기획서 v3.0 (Ch23~Ch29) 대응
--           기존 테이블에 컬럼 추가만 수행
-- Engine  : MySQL 8.x  InnoDB  utf8mb4_unicode_ci
-- Rule    : 기존 테이블 구조 변경 NO, ALTER ADD COLUMN 만 허용
-- ============================================================


-- ============================================================
-- 안전한 ALTER: 컬럼 존재 여부 확인 후 추가
-- MySQL 8.x에서는 IF NOT EXISTS 미지원이므로 프로시저 사용
-- ============================================================

DELIMITER //

DROP PROCEDURE IF EXISTS safe_add_column //
CREATE PROCEDURE safe_add_column(
  IN p_table VARCHAR(64),
  IN p_column VARCHAR(64),
  IN p_definition TEXT
)
BEGIN
  SET @col_exists = (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = p_table
      AND COLUMN_NAME = p_column
  );
  IF @col_exists = 0 THEN
    SET @sql = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN `', p_column, '` ', p_definition);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END //

DELIMITER ;


-- ============================================================
-- [1] lms_site — 연수원(멀티사이트) 지원 (Ch28)
-- ============================================================

CALL safe_add_column('lms_site', 'site_type',
  "VARCHAR(20) NOT NULL DEFAULT 'main' COMMENT 'main:본사이트 branch:연수원'");

CALL safe_add_column('lms_site', 'parent_site_id',
  "INT NOT NULL DEFAULT 0 COMMENT '본사이트 ID (연수원일 때)'");

CALL safe_add_column('lms_site', 'subdomain',
  "VARCHAR(100) DEFAULT NULL COMMENT '2차도메인 (예: abc001)'");

CALL safe_add_column('lms_site', 'subscription_start',
  "DATE DEFAULT NULL COMMENT '구독 시작일'");

CALL safe_add_column('lms_site', 'subscription_end',
  "DATE DEFAULT NULL COMMENT '구독 종료일'");

CALL safe_add_column('lms_site', 'subscription_status',
  "CHAR(1) NOT NULL DEFAULT 'Y' COMMENT 'Y:활성 N:비활성 E:만료'");


-- ============================================================
-- [2] lms_template — 알림톡 코드 연동 (Ch24)
-- ============================================================

CALL safe_add_column('lms_template', 'alimtalk_code',
  "VARCHAR(50) DEFAULT NULL COMMENT '카카오 알림톡 템플릿 코드'");

CALL safe_add_column('lms_template', 'replace_codes',
  "TEXT DEFAULT NULL COMMENT '사용 가능 치환코드 목록 (JSON)'");


-- ============================================================
-- [3] lms_goods — 과정 연결 강화 (Ch26)
-- ============================================================

CALL safe_add_column('lms_goods', 'course_ids',
  "TEXT DEFAULT NULL COMMENT '연결 과정 ID (JSON 배열)'");

CALL safe_add_column('lms_goods', 'is_required',
  "CHAR(1) NOT NULL DEFAULT 'N' COMMENT '수강신청 시 필수 구매'");

CALL safe_add_column('lms_goods', 'stock_qty',
  "INT NOT NULL DEFAULT 0 COMMENT '재고 수량 (-1:무제한)'");


-- ============================================================
-- [4] lms_goods_order — 배송 상태 관리 (Ch26)
-- ============================================================

CALL safe_add_column('lms_goods_order', 'shipping_status',
  "VARCHAR(20) NOT NULL DEFAULT 'READY' COMMENT 'READY:준비 SHIPPED:발송 DELIVERED:도착 RETURNED:반품'");

CALL safe_add_column('lms_goods_order', 'tracking_no',
  "VARCHAR(50) DEFAULT NULL COMMENT '운송장번호'");

CALL safe_add_column('lms_goods_order', 'shipping_company',
  "VARCHAR(50) DEFAULT NULL COMMENT '택배사'");


-- ============================================================
-- [5] lms_note — 일괄발송 지원 (Ch26)
-- ============================================================

CALL safe_add_column('lms_note', 'is_bulk',
  "CHAR(1) NOT NULL DEFAULT 'N' COMMENT 'Y:일괄발송'");

CALL safe_add_column('lms_note', 'bulk_group_id',
  "INT DEFAULT NULL COMMENT '일괄발송 그룹 ID'");


-- ============================================================
-- [6] lms_counseling — 상담 유형 확장 (Ch23)
-- ============================================================

CALL safe_add_column('lms_counseling', 'qna_type',
  "CHAR(1) NOT NULL DEFAULT 'T' COMMENT 'O:1:1온라인 L:학습상담 T:전화 C:고충'");

CALL safe_add_column('lms_counseling', 'class_id',
  "INT DEFAULT NULL COMMENT '관련 클래스 ID (학습상담)'");

CALL safe_add_column('lms_counseling', 'tutor_id',
  "INT DEFAULT NULL COMMENT '담당 튜터 (학습상담)'");


-- ============================================================
-- [7] lms_branch — 연수원 연결 (Ch28)
-- ============================================================

CALL safe_add_column('lms_branch', 'site_id',
  "INT DEFAULT NULL COMMENT '연수원 site_id (lms_site)'");


-- ============================================================
-- 인덱스 추가 (존재하지 않을 때만)
-- ============================================================

-- lms_site 연수원 검색용
SET @idx_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'lms_site' AND INDEX_NAME = 'idx_site_type');
SET @sql = IF(@idx_exists = 0,
  'ALTER TABLE `lms_site` ADD INDEX `idx_site_type` (`site_type`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'lms_site' AND INDEX_NAME = 'idx_parent_site_id');
SET @sql = IF(@idx_exists = 0,
  'ALTER TABLE `lms_site` ADD INDEX `idx_parent_site_id` (`parent_site_id`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- lms_counseling 유형별 검색
SET @idx_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'lms_counseling' AND INDEX_NAME = 'idx_qna_type');
SET @sql = IF(@idx_exists = 0,
  'ALTER TABLE `lms_counseling` ADD INDEX `idx_qna_type` (`qna_type`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- lms_note 일괄발송 검색
SET @idx_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'lms_note' AND INDEX_NAME = 'idx_bulk_group');
SET @sql = IF(@idx_exists = 0,
  'ALTER TABLE `lms_note` ADD INDEX `idx_bulk_group` (`bulk_group_id`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;


-- ============================================================
-- 프로시저 정리
-- ============================================================
DROP PROCEDURE IF EXISTS safe_add_column;


-- ============================================================
-- 완료 요약
-- ============================================================
-- ALTER 대상 테이블 (7개):
--   [1] lms_site: +6 컬럼 (연수원 멀티사이트)
--   [2] lms_template: +2 컬럼 (알림톡 코드)
--   [3] lms_goods: +3 컬럼 (과정 연결, 필수구매, 재고)
--   [4] lms_goods_order: +3 컬럼 (배송상태, 운송장, 택배사)
--   [5] lms_note: +2 컬럼 (일괄발송)
--   [6] lms_counseling: +3 컬럼 (상담유형확장, 클래스, 튜터)
--   [7] lms_branch: +1 컬럼 (연수원 연결)
-- 신규 인덱스: 4개
-- ============================================================
