-- ============================================================
-- LMS Schema Addon — 타사 SW솔루션 분석 기반 보완 DDL
-- Version : 1.0
-- Date    : 2026-03-20
-- Purpose : 경쟁사 관리자 매뉴얼 102슬라이드 분석 결과
--           현행 schema.sql(69테이블)에 부재한 기능 보완
-- Engine  : MySQL 8.x  InnoDB  utf8mb4_unicode_ci
-- Rule    : 기존 테이블 구조 변경 NO, 확장(ALTER ADD) 만 허용
-- ============================================================


-- ============================================================
-- [GROUP P] 개인정보 / 회원관리 보완
-- ============================================================

-- P-1. 개인정보 열람 승인 이력 (슬라이드 3, 91)
--      관리자가 회원 개인정보를 조회할 때마다 기록 + 승인 워크플로우
CREATE TABLE IF NOT EXISTS `lms_privacy_log` (
  `log_id`          INT          NOT NULL AUTO_INCREMENT,
  `admin_id`        INT          NOT NULL DEFAULT 0  COMMENT '조회 관리자 ID (lms_admin.admin_id)',
  `target_member_id` INT         NOT NULL DEFAULT 0  COMMENT '조회 대상 회원 ID (lms_member.member_id)',
  `access_purpose`  VARCHAR(200) NOT NULL DEFAULT '' COMMENT '열람 목적',
  `access_fields`   TEXT                             COMMENT '열람 항목 (JSON 배열 또는 콤마 구분)',
  `approve_yn`      CHAR(1)      NOT NULL DEFAULT 'N' COMMENT 'Y:승인완료 N:미승인 R:반려',
  `approver_id`     INT                  DEFAULT NULL COMMENT '승인자 관리자 ID',
  `approved_at`     DATETIME             DEFAULT NULL,
  `ip_address`      VARCHAR(45)  NOT NULL DEFAULT '' COMMENT '접근 IP',
  `note`            VARCHAR(500)          DEFAULT NULL COMMENT '비고',
  `reg_date`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`log_id`),
  KEY `idx_admin_id`    (`admin_id`),
  KEY `idx_target`      (`target_member_id`),
  KEY `idx_approve_yn`  (`approve_yn`),
  KEY `idx_reg_date`    (`reg_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='개인정보 열람 승인 이력';


-- P-2. 회원 변경 이력 타임라인 (슬라이드 6)
--      회원 정보 변경, 수강신청, 수료 등 모든 이력을 타임라인으로 표시
CREATE TABLE IF NOT EXISTS `lms_member_change_log` (
  `change_id`       INT          NOT NULL AUTO_INCREMENT,
  `member_id`       INT          NOT NULL DEFAULT 0,
  `change_type`     VARCHAR(50)  NOT NULL DEFAULT '' COMMENT 'INFO:기본정보 PW:비밀번호 STATUS:상태 ENROLL:수강 COMPLETE:수료 LOGIN:로그인 ETC:기타',
  `change_field`    VARCHAR(100)          DEFAULT NULL COMMENT '변경 컬럼명',
  `before_value`    TEXT                  COMMENT '변경 전 값',
  `after_value`     TEXT                  COMMENT '변경 후 값',
  `changed_by`      VARCHAR(20)  NOT NULL DEFAULT 'member' COMMENT 'member:본인 admin:관리자 system:시스템',
  `changed_admin_id` INT                  DEFAULT NULL COMMENT '관리자가 변경한 경우 admin_id',
  `ip_address`      VARCHAR(45)           DEFAULT NULL,
  `memo`            VARCHAR(500)          DEFAULT NULL,
  `reg_date`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`change_id`),
  KEY `idx_member_id`   (`member_id`),
  KEY `idx_change_type` (`change_type`),
  KEY `idx_reg_date`    (`reg_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='회원 변경 이력 타임라인';


-- ============================================================
-- [GROUP Q] 상담 관리 (슬라이드 60-65)
-- ============================================================

-- Q-1. 상담 분류
CREATE TABLE IF NOT EXISTS `lms_counseling_category` (
  `cat_id`      INT          NOT NULL AUTO_INCREMENT,
  `cat_name`    VARCHAR(100) NOT NULL DEFAULT '' COMMENT '분류명',
  `cat_type`    CHAR(1)      NOT NULL DEFAULT 'T' COMMENT 'T:전화상담 C:고충상담',
  `sort_order`  SMALLINT     NOT NULL DEFAULT 0,
  `use_yn`      CHAR(1)      NOT NULL DEFAULT 'Y',
  `reg_date`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`cat_id`),
  KEY `idx_cat_type` (`cat_type`),
  KEY `idx_use_yn`   (`use_yn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='상담 분류 (전화/고충)';


-- Q-2. 상담 내역
CREATE TABLE IF NOT EXISTS `lms_counseling` (
  `counseling_id`   INT          NOT NULL AUTO_INCREMENT,
  `cat_id`          INT          NOT NULL DEFAULT 0  COMMENT 'lms_counseling_category.cat_id',
  `counseling_type` CHAR(1)      NOT NULL DEFAULT 'T' COMMENT 'T:전화상담 C:고충상담',
  `member_id`       INT                   DEFAULT NULL COMMENT '회원 ID (비회원 가능)',
  `member_name`     VARCHAR(50)  NOT NULL DEFAULT '',
  `member_phone`    VARCHAR(20)           DEFAULT NULL,
  `member_email`    VARCHAR(100)          DEFAULT NULL,
  `company_name`    VARCHAR(100)          DEFAULT NULL,
  `subject`         VARCHAR(200) NOT NULL DEFAULT '' COMMENT '상담 제목/문의 내용 요약',
  `content`         TEXT         NOT NULL             COMMENT '상담 내용',
  `answer`          TEXT                  COMMENT '답변 내용',
  `status`          CHAR(1)      NOT NULL DEFAULT 'W' COMMENT 'W:대기 P:처리중 D:완료 C:취소',
  `priority`        TINYINT      NOT NULL DEFAULT 2   COMMENT '1:긴급 2:보통 3:낮음',
  `admin_id`        INT                   DEFAULT NULL COMMENT '담당 관리자',
  `answered_at`     DATETIME              DEFAULT NULL,
  `answered_admin_id` INT                 DEFAULT NULL,
  `course_id`       INT                   DEFAULT NULL COMMENT '관련 과정 (선택)',
  `attach_file`     VARCHAR(300)          DEFAULT NULL,
  `del_yn`          CHAR(1)      NOT NULL DEFAULT 'N',
  `reg_date`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `mod_date`        DATETIME              DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`counseling_id`),
  KEY `idx_cat_id`          (`cat_id`),
  KEY `idx_counseling_type` (`counseling_type`),
  KEY `idx_member_id`       (`member_id`),
  KEY `idx_status`          (`status`),
  KEY `idx_admin_id`        (`admin_id`),
  KEY `idx_del_yn`          (`del_yn`),
  KEY `idx_reg_date`        (`reg_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='상담 내역 (전화상담 / 고충상담)';


-- ============================================================
-- [GROUP R] 콘텐츠 / 자료실 관리 (슬라이드 66-72)
-- ============================================================

-- R-1. 학습자료실 분류 (슬라이드 66)
CREATE TABLE IF NOT EXISTS `lms_resource_category` (
  `cat_id`      INT          NOT NULL AUTO_INCREMENT,
  `parent_id`   INT          NOT NULL DEFAULT 0   COMMENT '부모 cat_id (0=최상위)',
  `cat_name`    VARCHAR(100) NOT NULL DEFAULT '',
  `depth`       TINYINT      NOT NULL DEFAULT 1,
  `sort_order`  SMALLINT     NOT NULL DEFAULT 0,
  `use_yn`      CHAR(1)      NOT NULL DEFAULT 'Y',
  `reg_date`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`cat_id`),
  KEY `idx_parent_id` (`parent_id`),
  KEY `idx_use_yn`    (`use_yn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='학습자료실 분류';


-- R-2. 학습자료실 (슬라이드 67)
CREATE TABLE IF NOT EXISTS `lms_resource` (
  `resource_id`   INT          NOT NULL AUTO_INCREMENT,
  `cat_id`        INT          NOT NULL DEFAULT 0,
  `title`         VARCHAR(200) NOT NULL DEFAULT '',
  `content`       TEXT                  COMMENT '설명',
  `file_name`     VARCHAR(200)          DEFAULT NULL COMMENT '원본 파일명',
  `file_path`     VARCHAR(300)          DEFAULT NULL COMMENT '저장 경로',
  `file_size`     INT                   DEFAULT 0,
  `file_ext`      VARCHAR(10)           DEFAULT NULL,
  `thumbnail`     VARCHAR(300)          DEFAULT NULL,
  `view_count`    INT          NOT NULL DEFAULT 0,
  `download_count` INT         NOT NULL DEFAULT 0,
  `is_public`     CHAR(1)      NOT NULL DEFAULT 'Y' COMMENT 'Y:전체공개 N:회원전용 C:특정기업',
  `company_ids`   TEXT                  COMMENT '공개 대상 기업 ID (JSON 배열, is_public=C 일 때)',
  `course_id`     INT                   DEFAULT NULL COMMENT '관련 과정 (선택)',
  `sort_order`    SMALLINT     NOT NULL DEFAULT 0,
  `reg_admin_id`  INT          NOT NULL DEFAULT 0,
  `del_yn`        CHAR(1)      NOT NULL DEFAULT 'N',
  `reg_date`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `mod_date`      DATETIME              DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`resource_id`),
  KEY `idx_cat_id`    (`cat_id`),
  KEY `idx_is_public` (`is_public`),
  KEY `idx_del_yn`    (`del_yn`),
  KEY `idx_reg_date`  (`reg_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='학습자료실';


-- R-3. 경력개발 자료실 분류 (슬라이드 68)
CREATE TABLE IF NOT EXISTS `lms_career_category` (
  `cat_id`      INT          NOT NULL AUTO_INCREMENT,
  `parent_id`   INT          NOT NULL DEFAULT 0,
  `cat_name`    VARCHAR(100) NOT NULL DEFAULT '',
  `depth`       TINYINT      NOT NULL DEFAULT 1,
  `sort_order`  SMALLINT     NOT NULL DEFAULT 0,
  `use_yn`      CHAR(1)      NOT NULL DEFAULT 'Y',
  `reg_date`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`cat_id`),
  KEY `idx_parent_id` (`parent_id`),
  KEY `idx_use_yn`    (`use_yn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='경력개발 자료실 분류';


-- R-4. 경력개발 자료실 (슬라이드 69)
CREATE TABLE IF NOT EXISTS `lms_career_resource` (
  `career_id`     INT          NOT NULL AUTO_INCREMENT,
  `cat_id`        INT          NOT NULL DEFAULT 0,
  `title`         VARCHAR(200) NOT NULL DEFAULT '',
  `content`       TEXT,
  `resource_type` VARCHAR(20)  NOT NULL DEFAULT 'FILE' COMMENT 'FILE:파일 LINK:외부링크 VIDEO:동영상',
  `file_name`     VARCHAR(200)          DEFAULT NULL,
  `file_path`     VARCHAR(300)          DEFAULT NULL,
  `file_size`     INT                   DEFAULT 0,
  `file_ext`      VARCHAR(10)           DEFAULT NULL,
  `link_url`      VARCHAR(500)          DEFAULT NULL COMMENT '외부 링크 URL',
  `thumbnail`     VARCHAR(300)          DEFAULT NULL,
  `view_count`    INT          NOT NULL DEFAULT 0,
  `download_count` INT         NOT NULL DEFAULT 0,
  `is_public`     CHAR(1)      NOT NULL DEFAULT 'Y',
  `sort_order`    SMALLINT     NOT NULL DEFAULT 0,
  `reg_admin_id`  INT          NOT NULL DEFAULT 0,
  `del_yn`        CHAR(1)      NOT NULL DEFAULT 'N',
  `reg_date`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `mod_date`      DATETIME              DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`career_id`),
  KEY `idx_cat_id`   (`cat_id`),
  KEY `idx_del_yn`   (`del_yn`),
  KEY `idx_reg_date` (`reg_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='경력개발 자료실';


-- R-5. 프로그램 다운로드 분류 (슬라이드 71)
CREATE TABLE IF NOT EXISTS `lms_download_category` (
  `cat_id`      INT          NOT NULL AUTO_INCREMENT,
  `cat_name`    VARCHAR(100) NOT NULL DEFAULT '',
  `sort_order`  SMALLINT     NOT NULL DEFAULT 0,
  `use_yn`      CHAR(1)      NOT NULL DEFAULT 'Y',
  `reg_date`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`cat_id`),
  KEY `idx_use_yn` (`use_yn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='프로그램 다운로드 분류';


-- R-6. 프로그램 다운로드 (슬라이드 72)
--      뷰어, 플러그인 등 필수 프로그램 다운로드 페이지
CREATE TABLE IF NOT EXISTS `lms_download` (
  `download_id`   INT          NOT NULL AUTO_INCREMENT,
  `cat_id`        INT          NOT NULL DEFAULT 0,
  `title`         VARCHAR(200) NOT NULL DEFAULT '',
  `description`   TEXT                  COMMENT '설명',
  `os_type`       VARCHAR(20)  NOT NULL DEFAULT 'ALL' COMMENT 'ALL WIN MAC LINUX',
  `version`       VARCHAR(30)           DEFAULT NULL COMMENT '버전 정보',
  `file_name`     VARCHAR(200)          DEFAULT NULL,
  `file_path`     VARCHAR(300)          DEFAULT NULL,
  `file_size`     INT                   DEFAULT 0,
  `file_ext`      VARCHAR(10)           DEFAULT NULL,
  `link_url`      VARCHAR(500)          DEFAULT NULL COMMENT '외부 다운로드 링크',
  `download_count` INT         NOT NULL DEFAULT 0,
  `thumbnail`     VARCHAR(300)          DEFAULT NULL,
  `is_required`   CHAR(1)      NOT NULL DEFAULT 'N' COMMENT 'Y:필수설치',
  `sort_order`    SMALLINT     NOT NULL DEFAULT 0,
  `use_yn`        CHAR(1)      NOT NULL DEFAULT 'Y',
  `reg_admin_id`  INT          NOT NULL DEFAULT 0,
  `del_yn`        CHAR(1)      NOT NULL DEFAULT 'N',
  `reg_date`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `mod_date`      DATETIME              DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`download_id`),
  KEY `idx_cat_id`    (`cat_id`),
  KEY `idx_os_type`   (`os_type`),
  KEY `idx_use_yn`    (`use_yn`),
  KEY `idx_del_yn`    (`del_yn`),
  KEY `idx_reg_date`  (`reg_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='프로그램 다운로드';


-- ============================================================
-- [GROUP S] 학습후기 / 심사계정 (슬라이드 18, 70)
-- ============================================================

-- S-1. 학습후기 (슬라이드 70)
--      수강 완료 회원의 과정별 후기 + 별점
CREATE TABLE IF NOT EXISTS `lms_review` (
  `review_id`   INT          NOT NULL AUTO_INCREMENT,
  `course_id`   INT          NOT NULL DEFAULT 0,
  `member_id`   INT          NOT NULL DEFAULT 0,
  `enroll_id`   INT                   DEFAULT NULL COMMENT '수강 ID (lms_enroll.enroll_id)',
  `rating`      TINYINT      NOT NULL DEFAULT 5   COMMENT '별점 1~5',
  `content`     TEXT         NOT NULL             COMMENT '후기 내용',
  `is_public`   CHAR(1)      NOT NULL DEFAULT 'Y' COMMENT 'Y:공개 N:비공개',
  `admin_reply` TEXT                  COMMENT '관리자 답글',
  `replied_at`  DATETIME              DEFAULT NULL,
  `replied_admin_id` INT              DEFAULT NULL,
  `report_count` SMALLINT    NOT NULL DEFAULT 0   COMMENT '신고 횟수',
  `blind_yn`    CHAR(1)      NOT NULL DEFAULT 'N' COMMENT 'Y:블라인드 처리',
  `del_yn`      CHAR(1)      NOT NULL DEFAULT 'N',
  `reg_date`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `mod_date`    DATETIME              DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`review_id`),
  KEY `idx_course_id`  (`course_id`),
  KEY `idx_member_id`  (`member_id`),
  KEY `idx_is_public`  (`is_public`),
  KEY `idx_blind_yn`   (`blind_yn`),
  KEY `idx_del_yn`     (`del_yn`),
  KEY `idx_reg_date`   (`reg_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='학습후기 (별점 포함)';


-- S-2. 심사계정 (슬라이드 18)
--      과정 심사 전용 임시 계정 관리
CREATE TABLE IF NOT EXISTS `lms_review_account` (
  `account_id`    INT          NOT NULL AUTO_INCREMENT,
  `course_id`     INT          NOT NULL DEFAULT 0  COMMENT '심사 대상 과정 ID',
  `user_id`       VARCHAR(50)  NOT NULL DEFAULT '' COMMENT '심사용 로그인 ID',
  `user_pw`       VARCHAR(255) NOT NULL DEFAULT '' COMMENT '심사용 비밀번호 (해시)',
  `reviewer_name` VARCHAR(50)  NOT NULL DEFAULT '' COMMENT '심사자 이름',
  `reviewer_org`  VARCHAR(100)          DEFAULT NULL COMMENT '심사 기관명',
  `valid_from`    DATE                  DEFAULT NULL COMMENT '심사 시작일',
  `valid_until`   DATE                  DEFAULT NULL COMMENT '심사 종료일',
  `login_count`   INT          NOT NULL DEFAULT 0,
  `last_login_at` DATETIME              DEFAULT NULL,
  `memo`          VARCHAR(300)          DEFAULT NULL,
  `use_yn`        CHAR(1)      NOT NULL DEFAULT 'Y',
  `del_yn`        CHAR(1)      NOT NULL DEFAULT 'N',
  `reg_admin_id`  INT          NOT NULL DEFAULT 0,
  `reg_date`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`account_id`),
  UNIQUE KEY `uq_user_id` (`user_id`),
  KEY `idx_course_id`  (`course_id`),
  KEY `idx_valid_until` (`valid_until`),
  KEY `idx_use_yn`     (`use_yn`),
  KEY `idx_del_yn`     (`del_yn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='과정 심사계정';


-- ============================================================
-- [GROUP T] 사이트관리 보완 (슬라이드 73-76, 92)
-- ============================================================

-- T-1. 상용구 관리 (슬라이드 73)
--      이메일/SMS/공지 작성 시 자주 쓰는 문구 템플릿 (notification_tpl 과 별개)
CREATE TABLE IF NOT EXISTS `lms_template` (
  `tpl_id`      INT          NOT NULL AUTO_INCREMENT,
  `tpl_type`    VARCHAR(20)  NOT NULL DEFAULT 'EMAIL' COMMENT 'EMAIL:이메일 SMS:SMS PUSH:푸시 NOTICE:공지',
  `tpl_name`    VARCHAR(100) NOT NULL DEFAULT '' COMMENT '상용구 이름',
  `tpl_subject` VARCHAR(200)          DEFAULT NULL COMMENT '제목 (EMAIL 일 때)',
  `tpl_content` TEXT         NOT NULL             COMMENT '내용',
  `use_yn`      CHAR(1)      NOT NULL DEFAULT 'Y',
  `sort_order`  SMALLINT     NOT NULL DEFAULT 0,
  `reg_admin_id` INT         NOT NULL DEFAULT 0,
  `del_yn`      CHAR(1)      NOT NULL DEFAULT 'N',
  `reg_date`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `mod_date`    DATETIME              DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`tpl_id`),
  KEY `idx_tpl_type` (`tpl_type`),
  KEY `idx_use_yn`   (`use_yn`),
  KEY `idx_del_yn`   (`del_yn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='상용구 관리 (이메일/SMS/공지 작성용 문구 템플릿)';


-- T-2. 문구 관리 (슬라이드 74)
--      사이트 각 영역별 안내/고정 문구 관리
CREATE TABLE IF NOT EXISTS `lms_notice_text` (
  `text_id`     INT          NOT NULL AUTO_INCREMENT,
  `text_key`    VARCHAR(80)  NOT NULL DEFAULT '' COMMENT '문구 키 (코드성, 예: login_notice, main_banner_sub)',
  `text_title`  VARCHAR(100) NOT NULL DEFAULT '' COMMENT '관리용 제목',
  `text_value`  TEXT         NOT NULL             COMMENT '실제 표시될 문구 (HTML 허용)',
  `text_area`   VARCHAR(50)  NOT NULL DEFAULT 'common' COMMENT '사용 영역 (login/main/mypage/common 등)',
  `use_yn`      CHAR(1)      NOT NULL DEFAULT 'Y',
  `reg_admin_id` INT         NOT NULL DEFAULT 0,
  `mod_date`    DATETIME              DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `reg_date`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`text_id`),
  UNIQUE KEY `uq_text_key` (`text_key`),
  KEY `idx_text_area` (`text_area`),
  KEY `idx_use_yn`    (`use_yn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='사이트 문구 관리 (영역별 안내 고정 문구)';


-- T-3. GNB 메뉴 관리 (슬라이드 75-76)
--      상단 내비게이션 메뉴 동적 관리
CREATE TABLE IF NOT EXISTS `lms_menu` (
  `menu_id`     INT          NOT NULL AUTO_INCREMENT,
  `parent_id`   INT          NOT NULL DEFAULT 0   COMMENT '부모 menu_id (0=1depth)',
  `menu_name`   VARCHAR(80)  NOT NULL DEFAULT '' COMMENT '메뉴 표시명',
  `menu_url`    VARCHAR(300)          DEFAULT NULL COMMENT '링크 URL',
  `target`      VARCHAR(10)  NOT NULL DEFAULT '_self' COMMENT '_self _blank',
  `menu_icon`   VARCHAR(100)          DEFAULT NULL COMMENT '아이콘 클래스 또는 경로',
  `depth`       TINYINT      NOT NULL DEFAULT 1,
  `sort_order`  SMALLINT     NOT NULL DEFAULT 0,
  `use_yn`      CHAR(1)      NOT NULL DEFAULT 'Y',
  `login_required` CHAR(1)  NOT NULL DEFAULT 'N' COMMENT 'Y:로그인 필요',
  `role`        VARCHAR(20)  NOT NULL DEFAULT 'all' COMMENT 'all:전체 member:회원 company:기업',
  `reg_date`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `mod_date`    DATETIME              DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`menu_id`),
  KEY `idx_parent_id` (`parent_id`),
  KEY `idx_depth`     (`depth`),
  KEY `idx_use_yn`    (`use_yn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='GNB 메뉴 관리';


-- T-4. 장애 일지 (슬라이드 92)
--      서비스 장애 발생/조치/종료 이력 관리
CREATE TABLE IF NOT EXISTS `lms_incident` (
  `incident_id`   INT          NOT NULL AUTO_INCREMENT,
  `title`         VARCHAR(200) NOT NULL DEFAULT '' COMMENT '장애 제목',
  `incident_type` VARCHAR(30)  NOT NULL DEFAULT 'SERVICE' COMMENT 'SERVICE:서비스 DB:데이터베이스 NETWORK:네트워크 SERVER:서버 ETC:기타',
  `severity`      TINYINT      NOT NULL DEFAULT 2  COMMENT '1:긴급 2:높음 3:보통 4:낮음',
  `status`        CHAR(1)      NOT NULL DEFAULT 'O' COMMENT 'O:발생 P:처리중 R:복구 C:완료',
  `occurred_at`   DATETIME     NOT NULL             COMMENT '장애 발생 시각',
  `resolved_at`   DATETIME              DEFAULT NULL COMMENT '복구 완료 시각',
  `description`   TEXT         NOT NULL             COMMENT '장애 내용',
  `cause`         TEXT                  COMMENT '원인 분석',
  `action_taken`  TEXT                  COMMENT '조치 내용',
  `affected_users` INT                  DEFAULT 0  COMMENT '영향 받은 사용자 수 (추산)',
  `admin_id`      INT          NOT NULL DEFAULT 0  COMMENT '등록 관리자',
  `handler_ids`   VARCHAR(200)          DEFAULT NULL COMMENT '처리 담당자 admin_id 목록 (콤마 구분)',
  `is_public`     CHAR(1)      NOT NULL DEFAULT 'N' COMMENT 'Y:공개(공지) N:내부',
  `del_yn`        CHAR(1)      NOT NULL DEFAULT 'N',
  `reg_date`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `mod_date`      DATETIME              DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`incident_id`),
  KEY `idx_status`     (`status`),
  KEY `idx_severity`   (`severity`),
  KEY `idx_occurred_at` (`occurred_at`),
  KEY `idx_del_yn`     (`del_yn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='장애 일지';


-- ============================================================
-- [GROUP U] 관리자 보안 강화 (슬라이드 81)
-- ============================================================

-- U-1. 관리자 허용 IP (슬라이드 81)
--      관리자 페이지 접근 허용 IP 화이트리스트
CREATE TABLE IF NOT EXISTS `lms_admin_allowed_ip` (
  `ip_id`       INT          NOT NULL AUTO_INCREMENT,
  `ip_address`  VARCHAR(45)  NOT NULL DEFAULT '' COMMENT 'IPv4 또는 IPv6, CIDR 표기 가능 (예: 192.168.1.0/24)',
  `ip_label`    VARCHAR(100)          DEFAULT NULL COMMENT 'IP 설명 (예: 본사 사무실)',
  `admin_id`    INT                   DEFAULT NULL COMMENT '특정 관리자 전용 (NULL=전체 관리자 적용)',
  `use_yn`      CHAR(1)      NOT NULL DEFAULT 'Y',
  `reg_admin_id` INT         NOT NULL DEFAULT 0,
  `reg_date`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ip_id`),
  KEY `idx_ip_address` (`ip_address`),
  KEY `idx_admin_id`   (`admin_id`),
  KEY `idx_use_yn`     (`use_yn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='관리자 허용 IP 화이트리스트';


-- ============================================================
-- [GROUP V] 관리자 메뉴 권한 세분화 (현행 JSON 방식 보완)
-- ============================================================

-- V-1. 관리자 메뉴 권한 (슬라이드 3 — 담당자관리 권한 세분화)
--      현행 lms_admin.menu_access JSON 보다 세밀한 메뉴별 권한
CREATE TABLE IF NOT EXISTS `lms_admin_menu_perm` (
  `perm_id`     INT          NOT NULL AUTO_INCREMENT,
  `admin_id`    INT          NOT NULL DEFAULT 0,
  `menu_code`   VARCHAR(80)  NOT NULL DEFAULT '' COMMENT '메뉴 코드 (예: member_list, course_create)',
  `can_view`    CHAR(1)      NOT NULL DEFAULT 'N' COMMENT '조회 권한',
  `can_write`   CHAR(1)      NOT NULL DEFAULT 'N' COMMENT '등록/수정 권한',
  `can_delete`  CHAR(1)      NOT NULL DEFAULT 'N' COMMENT '삭제 권한',
  `can_export`  CHAR(1)      NOT NULL DEFAULT 'N' COMMENT '엑셀 다운로드 권한',
  `reg_date`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `mod_date`    DATETIME              DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`perm_id`),
  UNIQUE KEY `uq_admin_menu` (`admin_id`, `menu_code`),
  KEY `idx_admin_id`  (`admin_id`),
  KEY `idx_menu_code` (`menu_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='관리자 메뉴별 세분화 권한';


-- ============================================================
-- [ALTER] 기존 테이블 컬럼 추가
-- ※ 이 파일(schema_addon.sql)에서는 ALTER 구문을 실행하지 않습니다.
-- ※ 반드시 schema_addon_alter.sql 을 별도로 실행하세요.
--    → 중복 컬럼 체크 후 없을 때만 ADD COLUMN 실행 (에러 없음)
-- ============================================================


-- ============================================================
-- 완료 요약
-- ============================================================
-- 신규 테이블 (16개):
--   P: lms_privacy_log, lms_member_change_log
--   Q: lms_counseling_category, lms_counseling
--   R: lms_resource_category, lms_resource,
--      lms_career_category, lms_career_resource,
--      lms_download_category, lms_download
--   S: lms_review, lms_review_account
--   T: lms_template, lms_notice_text, lms_menu, lms_incident
--   U: lms_admin_allowed_ip
--   V: lms_admin_menu_perm
-- ※ 신규 테이블 합계: 17개  →  총 테이블: 69 + 17 = 86개
--
-- ALTER TABLE (5개 기존 테이블 컬럼 추가):
--   lms_notification_rule (+8 컬럼: 3단계독려 + 시험/과제 조건)
--   lms_course            (+5 컬럼: 학습후기 + 심사계정)
--   lms_admin             (+3 컬럼: IP제한 + 계정잠금)
--   lms_member            (+2 컬럼: 상담건수 캐시)
--   lms_site              (+4 컬럼: 문구/메뉴)
-- ※ lms_encourage_rule 은 schema.sql 에 없어 제외
--    (독려기능은 lms_notification_rule 로 통합 처리)
-- ============================================================

