dnssash/adm/survey_manage/install.sql

-- 설문 관리 솔루션 데이터베이스 스키마
-- Survey Management Solution Database Schema

-- 설문지 마스터 테이블
CREATE TABLE IF NOT EXISTS `survey_master` (
    `sv_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '설문지 ID',
    `sv_title` varchar(255) NOT NULL COMMENT '설문지 제목',
    `sv_description` text COMMENT '설문지 설명',
    `sv_start_date` datetime NOT NULL COMMENT '설문 시작일',
    `sv_end_date` datetime NOT NULL COMMENT '설문 종료일',
    `sv_status` enum('draft','active','closed','deleted') NOT NULL DEFAULT 'draft' COMMENT '설문 상태',
    `sv_allow_anonymous` tinyint(1) NOT NULL DEFAULT 1 COMMENT '익명 참여 허용',
    `sv_allow_multiple` tinyint(1) NOT NULL DEFAULT 0 COMMENT '중복 참여 허용',
    `sv_max_responses` int(11) DEFAULT NULL COMMENT '최대 응답 수',
    `sv_theme_color` varchar(7) DEFAULT '#AA20FF' COMMENT '테마 색상',
    `sv_thank_message` text COMMENT '완료 메시지',
    `sv_created_by` varchar(20) NOT NULL COMMENT '생성자',
    `sv_created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
    `sv_updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '수정일시',
    PRIMARY KEY (`sv_id`),
    KEY `idx_status` (`sv_status`),
    KEY `idx_dates` (`sv_start_date`, `sv_end_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='설문지 마스터';

-- 설문 질문 테이블
CREATE TABLE IF NOT EXISTS `survey_questions` (
    `sq_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '질문 ID',
    `sv_id` int(11) NOT NULL COMMENT '설문지 ID',
    `sq_order` int(11) NOT NULL DEFAULT 1 COMMENT '질문 순서',
    `sq_type` enum('text','textarea','radio','checkbox','select','rating','date','email','number') NOT NULL COMMENT '질문 유형',
    `sq_title` varchar(500) NOT NULL COMMENT '질문 제목',
    `sq_description` text COMMENT '질문 설명',
    `sq_required` tinyint(1) NOT NULL DEFAULT 0 COMMENT '필수 여부',
    `sq_options` text COMMENT '선택지 옵션 (JSON)',
    `sq_validation` text COMMENT '유효성 검사 규칙 (JSON)',
    `sq_created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
    PRIMARY KEY (`sq_id`),
    KEY `fk_survey_questions_master` (`sv_id`),
    KEY `idx_order` (`sv_id`, `sq_order`),
    CONSTRAINT `fk_survey_questions_master` FOREIGN KEY (`sv_id`) REFERENCES `survey_master` (`sv_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='설문 질문';

-- 설문 응답자 테이블
CREATE TABLE IF NOT EXISTS `survey_responses` (
    `sr_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '응답 ID',
    `sv_id` int(11) NOT NULL COMMENT '설문지 ID',
    `sr_mb_id` varchar(20) DEFAULT NULL COMMENT '회원 ID (익명시 NULL)',
    `sr_ip` varchar(45) NOT NULL COMMENT '응답자 IP',
    `sr_user_agent` text COMMENT '사용자 에이전트',
    `sr_session_id` varchar(128) COMMENT '세션 ID',
    `sr_started_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '시작 시간',
    `sr_completed_at` datetime DEFAULT NULL COMMENT '완료 시간',
    `sr_status` enum('started','completed','abandoned') NOT NULL DEFAULT 'started' COMMENT '응답 상태',
    PRIMARY KEY (`sr_id`),
    KEY `fk_survey_responses_master` (`sv_id`),
    KEY `idx_member` (`sr_mb_id`),
    KEY `idx_ip` (`sr_ip`),
    KEY `idx_status` (`sr_status`),
    CONSTRAINT `fk_survey_responses_master` FOREIGN KEY (`sv_id`) REFERENCES `survey_master` (`sv_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='설문 응답자';

-- 설문 답변 테이블
CREATE TABLE IF NOT EXISTS `survey_answers` (
    `sa_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '답변 ID',
    `sr_id` int(11) NOT NULL COMMENT '응답 ID',
    `sq_id` int(11) NOT NULL COMMENT '질문 ID',
    `sa_value` text COMMENT '답변 값',
    `sa_text` text COMMENT '기타 텍스트 답변',
    `sa_created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '답변 시간',
    PRIMARY KEY (`sa_id`),
    KEY `fk_survey_answers_response` (`sr_id`),
    KEY `fk_survey_answers_question` (`sq_id`),
    KEY `idx_response_question` (`sr_id`, `sq_id`),
    CONSTRAINT `fk_survey_answers_response` FOREIGN KEY (`sr_id`) REFERENCES `survey_responses` (`sr_id`) ON DELETE CASCADE,
    CONSTRAINT `fk_survey_answers_question` FOREIGN KEY (`sq_id`) REFERENCES `survey_questions` (`sq_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='설문 답변';

-- 설문 통계 캐시 테이블 (성능 최적화용)
CREATE TABLE IF NOT EXISTS `survey_statistics` (
    `ss_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '통계 ID',
    `sv_id` int(11) NOT NULL COMMENT '설문지 ID',
    `sq_id` int(11) NOT NULL COMMENT '질문 ID',
    `ss_option_value` varchar(500) COMMENT '선택지 값',
    `ss_count` int(11) NOT NULL DEFAULT 0 COMMENT '응답 수',
    `ss_percentage` decimal(5,2) NOT NULL DEFAULT 0.00 COMMENT '비율',
    `ss_updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '업데이트 시간',
    PRIMARY KEY (`ss_id`),
    UNIQUE KEY `uk_survey_stats` (`sv_id`, `sq_id`, `ss_option_value`),
    KEY `fk_survey_statistics_master` (`sv_id`),
    KEY `fk_survey_statistics_question` (`sq_id`),
    CONSTRAINT `fk_survey_statistics_master` FOREIGN KEY (`sv_id`) REFERENCES `survey_master` (`sv_id`) ON DELETE CASCADE,
    CONSTRAINT `fk_survey_statistics_question` FOREIGN KEY (`sq_id`) REFERENCES `survey_questions` (`sq_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='설문 통계 캐시';

-- 설문 템플릿 마스터 테이블
CREATE TABLE IF NOT EXISTS `survey_templates` (
    `st_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '템플릿 ID',
    `st_name` varchar(255) NOT NULL COMMENT '템플릿 이름',
    `st_description` text COMMENT '템플릿 설명',
    `st_category` varchar(50) NOT NULL DEFAULT '기타' COMMENT '템플릿 카테고리',
    `st_is_public` tinyint(1) NOT NULL DEFAULT 1 COMMENT '공개 여부',
    `st_created_by` varchar(20) NOT NULL COMMENT '생성자',
    `st_created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
    `st_updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '수정일시',
    PRIMARY KEY (`st_id`),
    KEY `idx_category` (`st_category`),
    KEY `idx_public` (`st_is_public`),
    KEY `idx_created_by` (`st_created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='설문 템플릿 마스터';

-- 설문 템플릿 질문 테이블
CREATE TABLE IF NOT EXISTS `survey_template_questions` (
    `stq_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '템플릿 질문 ID',
    `st_id` int(11) NOT NULL COMMENT '템플릿 ID',
    `stq_order` int(11) NOT NULL DEFAULT 1 COMMENT '질문 순서',
    `stq_type` enum('text','textarea','radio','checkbox','select','rating','date','email','number') NOT NULL COMMENT '질문 유형',
    `stq_title` varchar(500) NOT NULL COMMENT '질문 제목',
    `stq_description` text COMMENT '질문 설명',
    `stq_required` tinyint(1) NOT NULL DEFAULT 0 COMMENT '필수 여부',
    `stq_options` text COMMENT '선택지 옵션 (JSON)',
    `stq_created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
    PRIMARY KEY (`stq_id`),
    KEY `fk_survey_template_questions_master` (`st_id`),
    KEY `idx_order` (`st_id`, `stq_order`),
    CONSTRAINT `fk_survey_template_questions_master` FOREIGN KEY (`st_id`) REFERENCES `survey_templates` (`st_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='설문 템플릿 질문';

-- 기본 템플릿 데이터 삽입
INSERT INTO `survey_templates` (`st_name`, `st_description`, `st_category`, `st_is_public`, `st_created_by`) VALUES
('고객 만족도 조사', '제품이나 서비스에 대한 고객 만족도를 측정하는 기본 템플릿입니다.', '고객서비스', 1, 'admin'),
('제품 피드백 설문', '새로운 제품에 대한 사용자 피드백을 수집하는 템플릿입니다.', '제품개발', 1, 'admin'),
('마케팅 캠페인 효과 측정', '마케팅 캠페인의 효과를 측정하고 개선점을 찾는 템플릿입니다.', '마케팅', 1, 'admin');

-- 고객 만족도 조사 템플릿 질문들
INSERT INTO `survey_template_questions` (`st_id`, `stq_order`, `stq_type`, `stq_title`, `stq_description`, `stq_required`, `stq_options`) VALUES
(1, 1, 'rating', '전반적인 서비스 만족도를 평가해주세요.', '1점(매우 불만족)부터 5점(매우 만족)까지 평가해주세요.', 1, NULL),
(1, 2, 'radio', '우리 서비스를 다른 사람에게 추천하시겠습니까?', '', 1, '["매우 추천", "추천", "보통", "추천하지 않음", "절대 추천하지 않음"]'),
(1, 3, 'checkbox', '어떤 부분이 가장 만족스러우셨나요? (복수선택 가능)', '', 0, '["제품 품질", "고객 서비스", "배송 속도", "가격", "사용 편의성", "기타"]'),
(1, 4, 'textarea', '개선이 필요한 부분이나 추가 의견이 있으시면 자유롭게 작성해주세요.', '', 0, NULL);

-- 제품 피드백 설문 템플릿 질문들
INSERT INTO `survey_template_questions` (`st_id`, `stq_order`, `stq_type`, `stq_title`, `stq_description`, `stq_required`, `stq_options`) VALUES
(2, 1, 'radio', '이 제품을 어떻게 알게 되셨나요?', '', 1, '["검색엔진", "소셜미디어", "친구/지인 추천", "광고", "기타"]'),
(2, 2, 'rating', '제품의 사용 편의성은 어떠셨나요?', '1점(매우 어려움)부터 5점(매우 쉬움)까지 평가해주세요.', 1, NULL),
(2, 3, 'radio', '제품의 가격은 적정하다고 생각하시나요?', '', 1, '["매우 비쌈", "비쌈", "적정함", "저렴함", "매우 저렴함"]'),
(2, 4, 'checkbox', '어떤 기능을 가장 자주 사용하시나요?', '', 0, '["기본 기능", "고급 기능", "설정 기능", "공유 기능", "분석 기능"]'),
(2, 5, 'textarea', '추가하고 싶은 기능이나 개선사항이 있다면 알려주세요.', '', 0, NULL);

-- 마케팅 캠페인 효과 측정 템플릿 질문들
INSERT INTO `survey_template_questions` (`st_id`, `stq_order`, `stq_type`, `stq_title`, `stq_description`, `stq_required`, `stq_options`) VALUES
(3, 1, 'radio', '최근 우리 브랜드의 광고를 본 적이 있나요?', '', 1, '["예, 여러 번 봤습니다", "예, 한두 번 봤습니다", "아니오, 본 적 없습니다"]'),
(3, 2, 'checkbox', '어떤 채널에서 우리 광고를 보셨나요? (복수선택 가능)', '', 0, '["TV", "온라인 광고", "소셜미디어", "옥외광고", "라디오", "기타"]'),
(3, 3, 'rating', '광고가 제품에 대한 관심을 높이는데 도움이 되었나요?', '1점(전혀 도움 안됨)부터 5점(매우 도움됨)까지 평가해주세요.', 1, NULL),
(3, 4, 'radio', '광고를 본 후 실제로 제품을 구매하셨나요?', '', 1, '["예, 구매했습니다", "아니오, 하지만 구매를 고려중입니다", "아니오, 구매할 계획이 없습니다"]'),
(3, 5, 'textarea', '광고에 대한 전반적인 의견이나 개선사항을 알려주세요.', '', 0, NULL);