-- ============================================================
-- LMS Schema Addon 2 — 기획서 Gap 분석 기반 보완 DDL
-- Version : 1.0
-- Date    : 2026-03-22
-- Purpose : LMS_전체_상세기획서.md 분석 결과
--           현행 schema.sql(65) + schema_addon.sql(17) 에 부재한 기능 보완
-- Engine  : MySQL 8.x  InnoDB  utf8mb4_unicode_ci
-- Rule    : 기존 테이블 구조 변경 NO, 확장(ALTER ADD)만 허용
--           ALTER는 schema_addon2_alter.sql 에서 별도 처리
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


-- ============================================================
-- [GROUP W] EMON 전송 관리 보완 (기획서 5-4. EMON 관리자 화면)
-- ============================================================

-- W-1. EMON 전송 설정 (전송 주기, 배치 크기 등)
CREATE TABLE IF NOT EXISTS `lms_emon_config` (
  `config_id`     INT          NOT NULL AUTO_INCREMENT,
  `table_name`    VARCHAR(50)  NOT NULL DEFAULT '' COMMENT 'EMON 전송 대상 테이블명',
  `send_interval` INT          NOT NULL DEFAULT 5  COMMENT '전송 주기 (분)',
  `batch_size`    INT          NOT NULL DEFAULT 100 COMMENT '1회 전송 배치 크기',
  `retry_count`   INT          NOT NULL DEFAULT 3  COMMENT '최대 재시도 횟수',
  `retry_interval` INT         NOT NULL DEFAULT 10 COMMENT '재시도 간격 (분)',
  `use_yn`        CHAR(1)      NOT NULL DEFAULT 'Y',
  `last_send_at`  DATETIME              DEFAULT NULL COMMENT '마지막 전송 시각',
  `last_success_at` DATETIME            DEFAULT NULL COMMENT '마지막 성공 시각',
  `reg_date`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `mod_date`      DATETIME              DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`config_id`),
  UNIQUE KEY `uq_table_name` (`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='EMON 전송 설정 (테이블별)';

-- 기본 설정 데이터
INSERT INTO `lms_emon_config` (`table_name`, `send_interval`, `batch_size`) VALUES
  ('API_USER_HIST', 5, 100),
  ('API_USER_LOGIN_HIST', 5, 200),
  ('API_COURSE_HIST', 5, 50),
  ('API_CLASS_HIST', 5, 50),
  ('API_ATTEND_HIST', 5, 200),
  ('API_ATTEND_RESULT_HIST', 5, 200),
  ('API_SCORE_HIST', 5, 300),
  ('API_PHONE_AUTH_HIST', 5, 200)
ON DUPLICATE KEY UPDATE `config_id` = `config_id`;

-- W-2. EMON 전송 에러 상세 로그 (error_list 페이지용)
CREATE TABLE IF NOT EXISTS `lms_emon_error_log` (
  `error_id`      INT          NOT NULL AUTO_INCREMENT,
  `table_name`    VARCHAR(50)  NOT NULL DEFAULT '' COMMENT '전송 대상 테이블',
  `record_seq`    BIGINT       NOT NULL DEFAULT 0  COMMENT '전송 실패 레코드 SEQ',
  `error_code`    VARCHAR(50)           DEFAULT NULL COMMENT '오류 코드',
  `error_message` TEXT                  COMMENT '오류 메시지',
  `error_type`    VARCHAR(30)  NOT NULL DEFAULT 'UNKNOWN' COMMENT 'NETWORK:네트워크 DATA:데이터 AUTH:인증 TIMEOUT:타임아웃 UNKNOWN:알수없음',
  `request_data`  TEXT                  COMMENT '전송 시도 데이터 (JSON)',
  `response_data` TEXT                  COMMENT 'API 응답 (JSON)',
  `retry_count`   INT          NOT NULL DEFAULT 0  COMMENT '재전송 시도 횟수',
  `resolved_yn`   CHAR(1)      NOT NULL DEFAULT 'N' COMMENT 'Y:해결 N:미해결',
  `resolved_at`   DATETIME              DEFAULT NULL,
  `resolved_by`   INT                   DEFAULT NULL COMMENT '처리 관리자 ID',
  `resolved_memo` VARCHAR(300)          DEFAULT NULL,
  `reg_date`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`error_id`),
  KEY `idx_table_name`  (`table_name`),
  KEY `idx_error_type`  (`error_type`),
  KEY `idx_resolved_yn` (`resolved_yn`),
  KEY `idx_reg_date`    (`reg_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='EMON 전송 오류 상세 로그';


-- ============================================================
-- [GROUP X] 사업주 교육신청 접수 (기획서 Ch.14)
-- ============================================================

-- X-1. 사업주 교육신청 접수
--      기획서: lms_company_request 테이블은 schema.sql에 이미 있음
--      하지만 접수 처리 이력 추적을 위한 로그 테이블 추가
CREATE TABLE IF NOT EXISTS `lms_request_log` (
  `log_id`        INT          NOT NULL AUTO_INCREMENT,
  `request_id`    INT          NOT NULL DEFAULT 0  COMMENT 'lms_company_request.request_id',
  `action`        VARCHAR(50)  NOT NULL DEFAULT '' COMMENT 'CREATE:접수 REVIEW:검토 APPROVE:승인 REJECT:반려 ASSIGN:담당배정 CANCEL:취소',
  `before_status` CHAR(1)               DEFAULT NULL,
  `after_status`  CHAR(1)               DEFAULT NULL,
  `admin_id`      INT          NOT NULL DEFAULT 0,
  `admin_name`    VARCHAR(30)  NOT NULL DEFAULT '',
  `memo`          TEXT                  COMMENT '처리 메모',
  `reg_date`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`log_id`),
  KEY `idx_request_id` (`request_id`),
  KEY `idx_admin_id`   (`admin_id`),
  KEY `idx_reg_date`   (`reg_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='사업주 교육신청 처리 이력';


-- ============================================================
-- [GROUP Y] 알림 관리 보완 (기획서 Ch.9)
-- ============================================================
-- lms_notification_rule, lms_notification_queue, lms_notification_log,
-- lms_notification_tpl 은 schema.sql에 이미 존재.
-- 추가 필요: 발송 일시 통계 캐시

CREATE TABLE IF NOT EXISTS `lms_notification_stat` (
  `stat_id`       INT          NOT NULL AUTO_INCREMENT,
  `stat_date`     DATE         NOT NULL             COMMENT '통계 날짜',
  `channel`       VARCHAR(20)  NOT NULL DEFAULT '' COMMENT 'SMS/ALIMTALK/EMAIL/PUSH',
  `total_count`   INT          NOT NULL DEFAULT 0,
  `success_count` INT          NOT NULL DEFAULT 0,
  `fail_count`    INT          NOT NULL DEFAULT 0,
  `reg_date`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`stat_id`),
  UNIQUE KEY `uq_date_channel` (`stat_date`, `channel`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='알림 발송 일별 통계';


-- ============================================================
-- [GROUP Z] 수료증 발급 보완 (기획서 Ch.8)
-- ============================================================
-- lms_certificate, lms_certificate_tpl 은 schema.sql에 이미 존재.
-- 추가: 발급 순번 관리 테이블 (cert_no 자동 생성)

CREATE TABLE IF NOT EXISTS `lms_certificate_seq` (
  `seq_id`        INT          NOT NULL AUTO_INCREMENT,
  `seq_year`      INT(4)       NOT NULL DEFAULT 0  COMMENT '발급 연도',
  `course_id`     INT          NOT NULL DEFAULT 0  COMMENT '과정 ID',
  `last_seq`      INT          NOT NULL DEFAULT 0  COMMENT '마지막 순번',
  PRIMARY KEY (`seq_id`),
  UNIQUE KEY `uq_year_course` (`seq_year`, `course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='수료증 발급번호 순번 관리';


-- ============================================================
-- [GROUP AA] 일괄등록 보완 (기획서 Ch.7-2)
-- ============================================================
-- lms_bulk_register 는 schema.sql에 이미 있음.
-- 추가: 일괄등록 상세 행 결과

CREATE TABLE IF NOT EXISTS `lms_bulk_register_row` (
  `row_id`        INT          NOT NULL AUTO_INCREMENT,
  `bulk_id`       INT          NOT NULL DEFAULT 0  COMMENT 'lms_bulk_register.bulk_id',
  `row_no`        INT          NOT NULL DEFAULT 0  COMMENT '엑셀 행 번호',
  `raw_data`      TEXT                  COMMENT '원본 행 데이터 (JSON)',
  `status`        TINYINT      NOT NULL DEFAULT 0  COMMENT '0:대기 1:성공 2:실패 3:중복(기존회원)',
  `error_message` VARCHAR(300)          DEFAULT NULL,
  `member_id`     INT                   DEFAULT NULL COMMENT '등록된/매칭된 member_id',
  `reg_date`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`row_id`),
  KEY `idx_bulk_id` (`bulk_id`),
  KEY `idx_status`  (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='일괄등록 행별 결과';


-- ============================================================
-- [GROUP AB] 관리자 즐겨찾기 (기획서 10-1. 대시보드)
-- ============================================================

CREATE TABLE IF NOT EXISTS `lms_admin_favorite` (
  `fav_id`        INT          NOT NULL AUTO_INCREMENT,
  `admin_id`      INT          NOT NULL DEFAULT 0,
  `menu_url`      VARCHAR(300) NOT NULL DEFAULT '' COMMENT '메뉴 URL',
  `menu_name`     VARCHAR(100) NOT NULL DEFAULT '' COMMENT '메뉴명',
  `sort_order`    SMALLINT     NOT NULL DEFAULT 0,
  `reg_date`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`fav_id`),
  KEY `idx_admin_id` (`admin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='관리자 즐겨찾기 (최대 10개)';


-- ============================================================
-- [GROUP AC] 산업안전보건교육 자동생성 설정 (기획서 6-2)
-- ============================================================

CREATE TABLE IF NOT EXISTS `lms_auto_class_config` (
  `config_id`     INT          NOT NULL AUTO_INCREMENT,
  `course_id`     INT          NOT NULL DEFAULT 0  COMMENT '대상 과정 ID',
  `start_day`     TINYINT      NOT NULL DEFAULT 1  COMMENT '시작 요일 (1:월~7:일)',
  `interval_weeks` TINYINT     NOT NULL DEFAULT 1  COMMENT '생성 간격 (주)',
  `start_seq`     INT          NOT NULL DEFAULT 1  COMMENT '시작 차수 번호',
  `category_ids`  VARCHAR(200)          DEFAULT NULL COMMENT '적용 카테고리 ID (콤마 구분)',
  `tutor_id`      INT          NOT NULL DEFAULT 0  COMMENT '기본 담당 튜터',
  `capacity`      INT          NOT NULL DEFAULT 0  COMMENT '기본 정원',
  `use_yn`        CHAR(1)      NOT NULL DEFAULT 'Y',
  `last_created_seq` INT       NOT NULL DEFAULT 0  COMMENT '마지막 생성된 차수 번호',
  `last_created_at` DATETIME            DEFAULT NULL,
  `reg_admin_id`  INT          NOT NULL DEFAULT 0,
  `reg_date`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `mod_date`      DATETIME              DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`config_id`),
  KEY `idx_course_id` (`course_id`),
  KEY `idx_use_yn`    (`use_yn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='산업안전보건교육 차수 자동생성 설정';


-- ============================================================
-- [GROUP AD] 모사답안 검사 (기획서 Ch.6, 5-3)
-- ============================================================

CREATE TABLE IF NOT EXISTS `lms_exam_copy_check` (
  `check_id`      INT          NOT NULL AUTO_INCREMENT,
  `result_id`     INT          NOT NULL DEFAULT 0  COMMENT 'lms_exam_result.result_id',
  `compare_result_id` INT      NOT NULL DEFAULT 0  COMMENT '비교 대상 result_id',
  `similarity`    DECIMAL(5,2) NOT NULL DEFAULT 0.00 COMMENT '유사도 (%)',
  `is_copied`     CHAR(1)      NOT NULL DEFAULT 'N' COMMENT 'Y:모사의심 N:정상',
  `checked_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`check_id`),
  KEY `idx_result_id` (`result_id`),
  KEY `idx_is_copied` (`is_copied`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='모사답안 검사 결과';


-- ============================================================
-- [GROUP AE] OT 시청 기록 (기획서 11-2)
-- ============================================================

CREATE TABLE IF NOT EXISTS `lms_ot_record` (
  `ot_id`         INT          NOT NULL AUTO_INCREMENT,
  `enroll_id`     INT          NOT NULL DEFAULT 0,
  `member_id`     INT          NOT NULL DEFAULT 0,
  `course_id`     INT          NOT NULL DEFAULT 0,
  `watch_yn`      CHAR(1)      NOT NULL DEFAULT 'N' COMMENT 'Y:시청완료',
  `watched_at`    DATETIME              DEFAULT NULL,
  `ip_addr`       VARCHAR(45)           DEFAULT NULL,
  `reg_date`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ot_id`),
  UNIQUE KEY `uq_enroll` (`enroll_id`),
  KEY `idx_member_id` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='온라인 OT 시청 기록';


-- ============================================================
-- 완료 요약
-- ============================================================
-- 신규 테이블 (10개):
--   W: lms_emon_config, lms_emon_error_log
--   X: lms_request_log
--   Y: lms_notification_stat
--   Z: lms_certificate_seq
--   AA: lms_bulk_register_row
--   AB: lms_admin_favorite
--   AC: lms_auto_class_config
--   AD: lms_exam_copy_check
--   AE: lms_ot_record
--
-- ※ 총 테이블: 65(schema) + 17(addon) + 10(addon2) = 92개
-- ============================================================

SET FOREIGN_KEY_CHECKS = 1;
