-- ============================================================
-- Enterprise Template System - Database Schema
-- ============================================================
--
-- 작성일: 2024-01-22
-- 작성자: Claude Code
-- PHP 버전: 5.3.29 (MariaDB 5.0.96)
--
-- 실행 방법:
-- mysql -u root -p infoway < template_system.sql
--
-- ============================================================

-- ============================================================
-- STEP 1: iw_enterprise 테이블 수정 - 템플릿 관련 컬럼 추가
-- ============================================================

-- 템플릿 플래그 컬럼 추가
ALTER TABLE `iw_enterprise`
ADD COLUMN `is_template` TINYINT(1) DEFAULT 0 COMMENT '템플릿 여부 (0: 일반 사이트, 1: 템플릿)' AFTER `avail_type`;

-- 템플릿 메타데이터 컬럼 추가
ALTER TABLE `iw_enterprise`
ADD COLUMN `template_name` VARCHAR(100) NULL COMMENT '템플릿명' AFTER `is_template`;

ALTER TABLE `iw_enterprise`
ADD COLUMN `template_description` TEXT NULL COMMENT '템플릿 설명' AFTER `template_name`;

ALTER TABLE `iw_enterprise`
ADD COLUMN `template_category` VARCHAR(50) NULL COMMENT '템플릿 카테고리 (business/shopping/publishing/etc)' AFTER `template_description`;

ALTER TABLE `iw_enterprise`
ADD COLUMN `template_thumbnail` VARCHAR(255) NULL COMMENT '템플릿 썸네일 이미지 경로' AFTER `template_category`;

ALTER TABLE `iw_enterprise`
ADD COLUMN `template_preview_url` VARCHAR(255) NULL COMMENT '템플릿 미리보기 URL' AFTER `template_thumbnail`;

ALTER TABLE `iw_enterprise`
ADD COLUMN `template_created` DATETIME NULL COMMENT '템플릿 생성일시' AFTER `template_preview_url`;

ALTER TABLE `iw_enterprise`
ADD COLUMN `template_updated` DATETIME NULL COMMENT '템플릿 수정일시' AFTER `template_created`;

ALTER TABLE `iw_enterprise`
ADD COLUMN `template_order` INT DEFAULT 0 COMMENT '템플릿 정렬 순서' AFTER `template_updated`;

-- 템플릿 사용 추적 컬럼 추가
ALTER TABLE `iw_enterprise`
ADD COLUMN `template_source_ep` VARCHAR(50) NULL COMMENT '복제 원본 템플릿 ep_code' AFTER `template_order`;

ALTER TABLE `iw_enterprise`
ADD COLUMN `template_applied_date` DATETIME NULL COMMENT '템플릿 적용일시' AFTER `template_source_ep`;

-- 인덱스 추가 (성능 최적화)
ALTER TABLE `iw_enterprise`
ADD INDEX `idx_is_template` (`is_template`);

ALTER TABLE `iw_enterprise`
ADD INDEX `idx_template_category` (`template_category`);

ALTER TABLE `iw_enterprise`
ADD INDEX `idx_template_source` (`template_source_ep`);

ALTER TABLE `iw_enterprise`
ADD INDEX `idx_template_order` (`template_order`);

-- ============================================================
-- STEP 2: iw_template_log 테이블 생성 - 복제 이력 관리
-- ============================================================

CREATE TABLE IF NOT EXISTS `iw_template_log` (
    `log_no` INT AUTO_INCREMENT PRIMARY KEY COMMENT '로그 번호',
    `log_type` VARCHAR(20) NOT NULL COMMENT '로그 유형 (create/apply/update/delete)',
    `template_ep_code` VARCHAR(50) NOT NULL COMMENT '템플릿 ep_code',
    `target_ep_code` VARCHAR(50) NULL COMMENT '대상 사이트 ep_code (apply 시)',
    `mb_code` VARCHAR(50) NOT NULL COMMENT '작업자 회원 코드',
    `mb_name` VARCHAR(50) NULL COMMENT '작업자 이름',
    `mb_mail` VARCHAR(100) NULL COMMENT '작업자 이메일',
    `log_action` VARCHAR(255) NOT NULL COMMENT '수행 작업 설명',
    `log_options` TEXT NULL COMMENT '복제 옵션 (JSON 형식)',
    `log_tables` TEXT NULL COMMENT '복제된 테이블 목록',
    `log_files` TEXT NULL COMMENT '복제된 파일 경로',
    `log_id_mapping` LONGTEXT NULL COMMENT 'ID 매핑 정보 (JSON)',
    `log_status` VARCHAR(20) DEFAULT 'success' COMMENT '작업 상태 (success/failed/partial)',
    `log_error` TEXT NULL COMMENT '오류 메시지',
    `log_ip` VARCHAR(45) NULL COMMENT '작업자 IP 주소',
    `log_user_agent` VARCHAR(255) NULL COMMENT '사용자 에이전트',
    `log_duration` FLOAT NULL COMMENT '작업 소요 시간 (초)',
    `log_memory_usage` INT NULL COMMENT '메모리 사용량 (bytes)',
    `log_datetime` DATETIME NOT NULL COMMENT '작업 일시',

    INDEX `idx_log_type` (`log_type`),
    INDEX `idx_template_ep` (`template_ep_code`),
    INDEX `idx_target_ep` (`target_ep_code`),
    INDEX `idx_mb_code` (`mb_code`),
    INDEX `idx_log_status` (`log_status`),
    INDEX `idx_log_datetime` (`log_datetime`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='템플릿 시스템 작업 로그';

-- ============================================================
-- STEP 3: iw_template_category 테이블 생성 - 템플릿 카테고리 관리
-- ============================================================

CREATE TABLE IF NOT EXISTS `iw_template_category` (
    `tc_no` INT AUTO_INCREMENT PRIMARY KEY COMMENT '카테고리 번호',
    `tc_code` VARCHAR(20) NOT NULL UNIQUE COMMENT '카테고리 코드',
    `tc_name` VARCHAR(50) NOT NULL COMMENT '카테고리명',
    `tc_description` TEXT NULL COMMENT '카테고리 설명',
    `tc_icon` VARCHAR(50) NULL COMMENT '카테고리 아이콘 (Font Awesome 클래스)',
    `tc_color` VARCHAR(7) NULL COMMENT '카테고리 색상 (HEX 코드)',
    `tc_order` INT DEFAULT 0 COMMENT '정렬 순서',
    `tc_count` INT DEFAULT 0 COMMENT '템플릿 수',
    `tc_active` TINYINT(1) DEFAULT 1 COMMENT '활성 상태',
    `tc_created` DATETIME NULL COMMENT '생성일시',

    INDEX `idx_tc_code` (`tc_code`),
    INDEX `idx_tc_order` (`tc_order`),
    INDEX `idx_tc_active` (`tc_active`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='템플릿 카테고리';

-- 기본 카테고리 데이터 삽입
INSERT INTO `iw_template_category` (`tc_code`, `tc_name`, `tc_description`, `tc_icon`, `tc_color`, `tc_order`, `tc_created`) VALUES
('business', '비즈니스', '일반 기업 및 비즈니스 사이트', 'fa-briefcase', '#4A90E2', 1, NOW()),
('shopping', '쇼핑몰', '온라인 쇼핑몰 및 이커머스', 'fa-shopping-cart', '#E74C3C', 2, NOW()),
('publishing', '출판', '출판사 및 도서 관련 사이트', 'fa-book', '#16A085', 3, NOW()),
('education', '교육', '교육 기관 및 학습 사이트', 'fa-graduation-cap', '#F39C12', 4, NOW()),
('portfolio', '포트폴리오', '개인 및 기업 포트폴리오', 'fa-images', '#9B59B6', 5, NOW()),
('blog', '블로그', '블로그 및 콘텐츠 사이트', 'fa-pen', '#1ABC9C', 6, NOW()),
('nonprofit', '비영리', '비영리 단체 및 공익 사이트', 'fa-hands-helping', '#34495E', 7, NOW()),
('other', '기타', '기타 템플릿', 'fa-folder', '#95A5A6', 99, NOW());

-- ============================================================
-- STEP 4: iw_template_options 테이블 생성 - 템플릿 복제 옵션
-- ============================================================

CREATE TABLE IF NOT EXISTS `iw_template_options` (
    `to_no` INT AUTO_INCREMENT PRIMARY KEY COMMENT '옵션 번호',
    `template_ep_code` VARCHAR(50) NOT NULL COMMENT '템플릿 ep_code',
    `option_key` VARCHAR(50) NOT NULL COMMENT '옵션 키',
    `option_value` TEXT NULL COMMENT '옵션 값',
    `option_type` VARCHAR(20) DEFAULT 'boolean' COMMENT '옵션 타입 (boolean/string/json)',
    `option_default` VARCHAR(255) NULL COMMENT '기본값',
    `option_required` TINYINT(1) DEFAULT 0 COMMENT '필수 여부',
    `option_order` INT DEFAULT 0 COMMENT '정렬 순서',

    UNIQUE KEY `uk_template_option` (`template_ep_code`, `option_key`),
    INDEX `idx_template_ep` (`template_ep_code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='템플릿별 복제 옵션 설정';

-- ============================================================
-- STEP 5: iw_template_statistics 테이블 생성 - 템플릿 통계
-- ============================================================

CREATE TABLE IF NOT EXISTS `iw_template_statistics` (
    `stat_no` INT AUTO_INCREMENT PRIMARY KEY COMMENT '통계 번호',
    `template_ep_code` VARCHAR(50) NOT NULL COMMENT '템플릿 ep_code',
    `stat_year` YEAR NOT NULL COMMENT '년도',
    `stat_month` TINYINT NOT NULL COMMENT '월',
    `use_count` INT DEFAULT 0 COMMENT '사용 횟수',
    `view_count` INT DEFAULT 0 COMMENT '조회 횟수',
    `preview_count` INT DEFAULT 0 COMMENT '미리보기 횟수',
    `last_used` DATETIME NULL COMMENT '마지막 사용일시',
    `last_viewed` DATETIME NULL COMMENT '마지막 조회일시',

    UNIQUE KEY `uk_template_period` (`template_ep_code`, `stat_year`, `stat_month`),
    INDEX `idx_stat_year_month` (`stat_year`, `stat_month`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='템플릿 사용 통계';

-- ============================================================
-- STEP 6: 뷰(View) 생성 - 템플릿 관련 통계 뷰
-- ============================================================

-- 기존 뷰 삭제 후 재생성 (MyISAM 호환)
DROP VIEW IF EXISTS `v_template_usage`;

-- 템플릿 사용 현황 뷰
CREATE VIEW `v_template_usage` AS
SELECT
    t.ep_code,
    t.template_name,
    t.template_category,
    t.template_created,
    COUNT(DISTINCT s.ep_code) as total_uses,
    MAX(s.template_applied_date) as last_used,
    GROUP_CONCAT(s.ep_corporate SEPARATOR ', ') as used_by
FROM
    iw_enterprise t
LEFT JOIN
    iw_enterprise s ON t.ep_code = s.template_source_ep
WHERE
    t.is_template = 1
GROUP BY
    t.ep_code;

-- ============================================================
-- STEP 7: 저장 프로시저 제거 (MyISAM 환경, PHP 5.3에서 미지원)
-- ============================================================
-- MyISAM 환경에서는 트랜잭션과 저장 프로시저를 사용하지 않습니다.
-- 모든 로직은 PHP 함수로 구현되어 있습니다.
-- template_functions.php 파일을 참조하세요.

-- ============================================================
-- ROLLBACK SQL (주석 처리)
-- ============================================================
--
-- 아래 SQL은 변경사항을 롤백할 때 사용합니다.
-- 실행 전 데이터 백업을 권장합니다.
--
-- -- Step 1: iw_enterprise 테이블 롤백
-- ALTER TABLE `iw_enterprise`
-- DROP COLUMN `is_template`,
-- DROP COLUMN `template_name`,
-- DROP COLUMN `template_description`,
-- DROP COLUMN `template_category`,
-- DROP COLUMN `template_thumbnail`,
-- DROP COLUMN `template_preview_url`,
-- DROP COLUMN `template_created`,
-- DROP COLUMN `template_updated`,
-- DROP COLUMN `template_order`,
-- DROP COLUMN `template_source_ep`,
-- DROP COLUMN `template_applied_date`,
-- DROP INDEX `idx_is_template`,
-- DROP INDEX `idx_template_category`,
-- DROP INDEX `idx_template_source`,
-- DROP INDEX `idx_template_order`;
--
-- -- Step 2: 템플릿 관련 테이블 삭제
-- DROP TABLE IF EXISTS `iw_template_log`;
-- DROP TABLE IF EXISTS `iw_template_category`;
-- DROP TABLE IF EXISTS `iw_template_options`;
-- DROP TABLE IF EXISTS `iw_template_statistics`;
--
-- -- Step 3: 뷰 삭제
-- DROP VIEW IF EXISTS `v_template_usage`;
--
-- ============================================================

-- ============================================================
-- 데이터 마이그레이션 (기존 템플릿 사이트가 있는 경우)
-- ============================================================
--
-- 기존에 'sample', 'home', 'wizwin' 등이 템플릿으로 사용되고 있다면:
--
-- UPDATE iw_enterprise SET
--     is_template = 1,
--     template_name = '샘플 템플릿',
--     template_description = '기본 샘플 템플릿',
--     template_category = 'business',
--     template_created = NOW()
-- WHERE ep_nick IN ('sample', 'home');
--
-- ============================================================

-- ============================================================
-- 권한 설정 (필요시)
-- ============================================================
-- GRANT SELECT, INSERT, UPDATE, DELETE ON infoway.* TO 'infoway_user'@'localhost';
-- FLUSH PRIVILEGES;

-- ============================================================
-- 실행 완료 메시지
-- ============================================================
SELECT 'Enterprise Template System - Database Schema 설치 완료!' AS message;
SELECT CONCAT('추가된 컬럼: ', COUNT(*)) AS added_columns
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'infoway'
AND TABLE_NAME = 'iw_enterprise'
AND (COLUMN_NAME LIKE 'template%' OR COLUMN_NAME = 'is_template');