-- ============================================================
-- LMS v3.1 DDL 확장 스크립트
-- 원격훈련 인정요건 필수기능 관련 컬럼 추가
-- Date: 2026-04-06
-- ============================================================

-- 1. lms_site: 본인인증/mOTP/학습설정 컬럼 추가
ALTER TABLE `lms_site`
  ADD COLUMN `auth_provider`      VARCHAR(20) NOT NULL DEFAULT 'niceid'    COMMENT '본인인증 서비스사(niceid/pass/kmc)' AFTER `kakao_map_key`,
  ADD COLUMN `auth_site_code`     VARCHAR(100) NOT NULL DEFAULT ''         COMMENT '본인인증 사이트코드' AFTER `auth_provider`,
  ADD COLUMN `auth_site_pw`       VARCHAR(200) NOT NULL DEFAULT ''         COMMENT '본인인증 사이트 비밀번호' AFTER `auth_site_code`,
  ADD COLUMN `auth_api_key`       VARCHAR(200) NOT NULL DEFAULT ''         COMMENT '본인인증 API KEY' AFTER `auth_site_pw`,
  ADD COLUMN `auth_secret_key`    VARCHAR(200) NOT NULL DEFAULT ''         COMMENT '본인인증 SECRET KEY' AFTER `auth_api_key`,
  ADD COLUMN `auth_callback_url`  VARCHAR(300) NOT NULL DEFAULT ''         COMMENT '본인인증 콜백 URL' AFTER `auth_secret_key`,
  ADD COLUMN `auth_test_yn`       CHAR(1) NOT NULL DEFAULT 'Y'            COMMENT '본인인증 테스트모드(Y/N)' AFTER `auth_callback_url`,
  ADD COLUMN `motp_api_url`       VARCHAR(200) NOT NULL DEFAULT ''         COMMENT 'mOTP(TQI) API URL' AFTER `auth_test_yn`,
  ADD COLUMN `motp_api_key`       VARCHAR(200) NOT NULL DEFAULT ''         COMMENT 'mOTP(TQI) API KEY' AFTER `motp_api_url`,
  ADD COLUMN `motp_secret_key`    VARCHAR(200) NOT NULL DEFAULT ''         COMMENT 'mOTP(TQI) SECRET KEY' AFTER `motp_api_key`,
  ADD COLUMN `motp_agent_id`      VARCHAR(50) NOT NULL DEFAULT ''          COMMENT 'mOTP 기관코드' AFTER `motp_secret_key`,
  ADD COLUMN `motp_test_yn`       CHAR(1) NOT NULL DEFAULT 'Y'            COMMENT 'mOTP 테스트모드(Y/N)' AFTER `motp_agent_id`,
  ADD COLUMN `daily_study_limit`  INT(5) NOT NULL DEFAULT 480             COMMENT '1일 최대 학습시간(분, 기본480=8시간)' AFTER `motp_test_yn`,
  ADD COLUMN `auto_logout_min`    INT(5) NOT NULL DEFAULT 120             COMMENT '자동 로그아웃(분, 기본120=2시간)' AFTER `daily_study_limit`,
  ADD COLUMN `concurrent_login_yn` CHAR(1) NOT NULL DEFAULT 'N'           COMMENT '동시접속 허용(N=차단/Y=허용)' AFTER `auto_logout_min`,
  ADD COLUMN `progress_min_rate`  INT(3) NOT NULL DEFAULT 50              COMMENT '차시 진도율 인정 최소%(기본50)' AFTER `concurrent_login_yn`,
  ADD COLUMN `final_exam_progress` INT(3) NOT NULL DEFAULT 80             COMMENT '최종평가 응시 최소 진도율%(기본80)' AFTER `progress_min_rate`,
  ADD COLUMN `retake_days`        INT(3) NOT NULL DEFAULT 7               COMMENT 'N차 평가 허용 기간(종료일 이후 일수)' AFTER `final_exam_progress`,
  ADD COLUMN `copy_detect_yn`    CHAR(1) NOT NULL DEFAULT 'Y'            COMMENT '배끼기(답안유사도) 탐지 활성화(Y/N)' AFTER `retake_days`,
  ADD COLUMN `copy_detect_rate`  INT(3) NOT NULL DEFAULT 80              COMMENT '배끼기 탐지 유사도 기준%(기본80)' AFTER `copy_detect_yn`,
  ADD COLUMN `privacy_policy_ver` VARCHAR(10) NOT NULL DEFAULT '1.0'      COMMENT '개인정보처리방침 버전' AFTER `copy_detect_rate`;

-- 2. lms_member: 동시접속 방지용 login_token + 본인인증/개인정보동의 컬럼
ALTER TABLE `lms_member`
  ADD COLUMN `login_token`        VARCHAR(64) NOT NULL DEFAULT ''         COMMENT '동시접속 방지 토큰' AFTER `last_login_ip`,
  ADD COLUMN `identity_verified`  CHAR(1) NOT NULL DEFAULT 'N'            COMMENT '본인인증 완료(Y/N)' AFTER `login_token`,
  ADD COLUMN `identity_ci`        VARCHAR(128) NOT NULL DEFAULT ''        COMMENT '본인인증 CI값(연계정보)' AFTER `identity_verified`,
  ADD COLUMN `identity_di`        VARCHAR(128) NOT NULL DEFAULT ''        COMMENT '본인인증 DI값(중복확인)' AFTER `identity_ci`,
  ADD COLUMN `identity_at`        DATETIME NULL DEFAULT NULL              COMMENT '최종 본인인증 일시' AFTER `identity_di`,
  ADD COLUMN `privacy_agree_yn`   CHAR(1) NOT NULL DEFAULT 'N'            COMMENT '개인정보수집이용동의(Y/N)' AFTER `identity_at`,
  ADD COLUMN `privacy_agree_at`   DATETIME NULL DEFAULT NULL              COMMENT '개인정보동의 일시' AFTER `privacy_agree_yn`,
  ADD COLUMN `third_party_agree_yn` CHAR(1) NOT NULL DEFAULT 'N'          COMMENT '제3자제공동의(Y/N)' AFTER `privacy_agree_at`,
  ADD COLUMN `third_party_agree_at` DATETIME NULL DEFAULT NULL            COMMENT '제3자제공동의 일시' AFTER `third_party_agree_yn`,
  ADD COLUMN `pw_strength_ok`     CHAR(1) NOT NULL DEFAULT 'N'            COMMENT '비밀번호규칙충족(Y/N)' AFTER `third_party_agree_at`,
  ADD COLUMN `force_pw_change`    CHAR(1) NOT NULL DEFAULT 'N'            COMMENT '비밀번호변경강제(Y=단체가입등)' AFTER `pw_strength_ok`,
  ADD COLUMN `bulk_registered`    CHAR(1) NOT NULL DEFAULT 'N'            COMMENT '단체가입여부(Y/N)' AFTER `force_pw_change`;

-- 3. lms_daily_study: 1일 학습시간 누적 추적 테이블
CREATE TABLE IF NOT EXISTS `lms_daily_study` (
  `daily_id`           BIGINT(20) NOT NULL AUTO_INCREMENT       COMMENT '고유번호',
  `member_id`          INT(10) NOT NULL                         COMMENT '회원 ID',
  `study_date`         DATE NOT NULL                            COMMENT '학습일자',
  `total_seconds`      INT(10) NOT NULL DEFAULT 0               COMMENT '당일 총 학습시간(초)',
  `last_content_id`    INT(10) NOT NULL DEFAULT 0               COMMENT '최종 학습 콘텐츠ID',
  `updated_at`         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '수정일시',
  PRIMARY KEY (`daily_id`),
  UNIQUE KEY `uk_member_date` (`member_id`, `study_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='1일 학습시간 누적 추적';

-- 4. lms_auth_verify_log: 본인인증 이력
CREATE TABLE IF NOT EXISTS `lms_auth_verify_log` (
  `verify_id`          BIGINT(20) NOT NULL AUTO_INCREMENT       COMMENT '인증로그 ID',
  `member_id`          INT(10) NOT NULL DEFAULT 0               COMMENT '회원 ID(0=가입전)',
  `verify_type`        VARCHAR(20) NOT NULL DEFAULT 'PHONE'     COMMENT '인증유형(PHONE:휴대폰,MOTP:mOTP,IPIN:아이핀)',
  `verify_purpose`     VARCHAR(20) NOT NULL DEFAULT 'REGISTER'  COMMENT '인증목적(REGISTER:가입,LOGIN:진도학습,EXAM:시험,TASK:과제)',
  `verify_result`      CHAR(1) NOT NULL DEFAULT 'N'             COMMENT '인증결과(Y:성공,N:실패)',
  `ci_value`           VARCHAR(128) NOT NULL DEFAULT ''         COMMENT 'CI(연계정보)',
  `di_value`           VARCHAR(128) NOT NULL DEFAULT ''         COMMENT 'DI(중복확인)',
  `user_ip`            VARCHAR(45) NOT NULL DEFAULT ''          COMMENT '사용자 IP',
  `user_agent`         VARCHAR(300) NOT NULL DEFAULT ''         COMMENT '브라우저 UA',
  `created_at`         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '인증일시',
  PRIMARY KEY (`verify_id`),
  KEY `idx_member_id` (`member_id`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='본인인증(휴대폰/mOTP) 이력';

-- 5. lms_exam_result: N차 평가 관련 컬럼 추가
ALTER TABLE `lms_exam_result`
  ADD COLUMN `is_retake`         CHAR(1) NOT NULL DEFAULT 'N'            COMMENT 'N차평가여부(Y/N)' AFTER `submit_at`,
  ADD COLUMN `retake_reason`     VARCHAR(200) NOT NULL DEFAULT ''        COMMENT 'N차사유(불합격/불가피미응시)' AFTER `is_retake`,
  ADD COLUMN `retake_approved_by` INT(10) NOT NULL DEFAULT 0             COMMENT 'N차승인관리자ID' AFTER `retake_reason`,
  ADD COLUMN `retake_approved_at` DATETIME NULL DEFAULT NULL             COMMENT 'N차승인일시' AFTER `retake_approved_by`,
  ADD COLUMN `copy_detect_yn`    CHAR(1) NOT NULL DEFAULT 'N'            COMMENT '배끼기 의심(Y/N)' AFTER `retake_approved_at`,
  ADD COLUMN `copy_detect_data`  TEXT NULL                                COMMENT '배끼기 탐지 상세(JSON)' AFTER `copy_detect_yn`;

-- 6. lms_exam_retake_request: N차 평가(재시험) 신청 이력
CREATE TABLE IF NOT EXISTS `lms_exam_retake_request` (
  `retake_request_id`  INT(10) NOT NULL AUTO_INCREMENT          COMMENT '재응시신청 고유번호',
  `paper_id`           INT(10) NOT NULL                         COMMENT '시험지 ID',
  `enroll_id`          INT(10) NOT NULL                         COMMENT '수강신청 ID',
  `member_id`          INT(10) NOT NULL                         COMMENT '회원 ID',
  `class_id`           INT(10) NOT NULL                         COMMENT '차수 ID',
  `course_id`          INT(10) NOT NULL                         COMMENT '과정 ID',
  `exam_type`          VARCHAR(10) NOT NULL DEFAULT 'FINAL'     COMMENT '시험유형(FINAL/MID/INGTEST)',
  `retake_reason`      TEXT NULL                                COMMENT '재응시 신청 사유',
  `status`             VARCHAR(20) NOT NULL DEFAULT 'PENDING'   COMMENT 'PENDING:대기 APPROVED:승인 REJECTED:거절',
  `approved_by`        INT(10) DEFAULT NULL                     COMMENT '승인자 관리자 ID',
  `approved_at`        DATETIME NULL DEFAULT NULL               COMMENT '승인일시',
  `rejection_reason`   TEXT NULL                                COMMENT '거절 사유',
  `created_at`         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '신청일시',
  `updated_at`         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '수정일시',
  `del_yn`             CHAR(1) NOT NULL DEFAULT 'N'             COMMENT '삭제여부(Y/N)',
  PRIMARY KEY (`retake_request_id`),
  KEY `idx_paper_id` (`paper_id`),
  KEY `idx_enroll_id` (`enroll_id`),
  KEY `idx_member_id` (`member_id`),
  KEY `idx_status` (`status`),
  UNIQUE KEY `uk_paper_enroll_member` (`paper_id`, `enroll_id`, `member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='N차 평가(재시험) 신청 이력';
