syn-chat-bot/init/init.sql

-- syn-chat-bot DB 초기 스키마 v2
-- RAG 아키텍처 개선: 3단계 라우팅, 멀티-컬렉션, 선택적 메모리, 현장 리포팅

-- ========================
-- 기존 테이블 (v1 호환)
-- ========================

-- ai_configs: feature별 모델/프롬프트 독립 관리
CREATE TABLE ai_configs (
    id SERIAL PRIMARY KEY,
    feature VARCHAR(50) UNIQUE NOT NULL,
    model VARCHAR(100) NOT NULL,
    temperature DECIMAL(3,2) DEFAULT 0.7,
    max_tokens INTEGER DEFAULT 2048,
    system_prompt TEXT,
    enabled BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- routing_rules: complexity 기반 모델 라우팅 (레거시 호환)
CREATE TABLE routing_rules (
    id SERIAL PRIMARY KEY,
    feature VARCHAR(50) NOT NULL,
    complexity_min INTEGER NOT NULL,
    complexity_max INTEGER NOT NULL,
    model VARCHAR(100) NOT NULL,
    priority INTEGER DEFAULT 0,
    enabled BOOLEAN DEFAULT true,
    CONSTRAINT valid_complexity CHECK (complexity_min >= 1 AND complexity_max <= 5),
    CONSTRAINT valid_range CHECK (complexity_min <= complexity_max)
);

-- prompts: 프롬프트 버전 관리 (롤백 가능)
CREATE TABLE prompts (
    id SERIAL PRIMARY KEY,
    feature VARCHAR(50) NOT NULL,
    version INTEGER NOT NULL,
    content TEXT NOT NULL,
    is_active BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (feature, version)
);

-- chat_logs: 대화 기록 + 토큰/성능 추적
CREATE TABLE chat_logs (
    id SERIAL PRIMARY KEY,
    feature VARCHAR(50) NOT NULL,
    username VARCHAR(100),
    user_message TEXT,
    assistant_message TEXT,
    model_used VARCHAR(100),
    response_tier VARCHAR(20),
    complexity_score INTEGER,
    input_tokens INTEGER,
    output_tokens INTEGER,
    latency_ms INTEGER,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- mail_accounts: 메일 소스 관리 (Phase 6)
CREATE TABLE mail_accounts (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    imap_host VARCHAR(255) NOT NULL,
    imap_port INTEGER DEFAULT 993,
    username VARCHAR(255) NOT NULL,
    password_encrypted TEXT NOT NULL,
    check_interval_min INTEGER DEFAULT 15,
    enabled BOOLEAN DEFAULT true
);

-- ========================
-- Phase 1: 신규 테이블
-- ========================

-- 문서 등록 이력 + 버전 관리
CREATE TABLE document_ingestion_log (
    id SERIAL PRIMARY KEY,
    collection VARCHAR(50) NOT NULL,
    source_file VARCHAR(500) NOT NULL,
    file_hash VARCHAR(64) NOT NULL,
    chunks_count INTEGER NOT NULL,
    department VARCHAR(50),
    doc_type VARCHAR(50),
    year INTEGER,
    uploaded_by VARCHAR(100),
    version INTEGER DEFAULT 1,
    supersedes_id INTEGER REFERENCES document_ingestion_log(id),
    doc_group_key VARCHAR(200),
    status VARCHAR(20) DEFAULT 'completed',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(file_hash, collection)
);

-- 현장 리포트 (안전/시설설비/품질 통합)
CREATE TABLE field_reports (
    id SERIAL PRIMARY KEY,
    domain VARCHAR(20) NOT NULL,
    category VARCHAR(50) NOT NULL,
    severity VARCHAR(10) NOT NULL,
    location VARCHAR(100),
    department VARCHAR(50) NOT NULL,
    keywords TEXT[],
    summary TEXT NOT NULL,
    action_required TEXT,
    user_description TEXT,
    photo_url TEXT,
    photo_analysis TEXT,
    reporter VARCHAR(100),
    source VARCHAR(20) DEFAULT 'chat',
    resolved_by VARCHAR(100),
    resolution_note TEXT,
    year INTEGER NOT NULL,
    month INTEGER NOT NULL,
    due_at TIMESTAMPTZ,
    status VARCHAR(20) DEFAULT 'open',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    resolved_at TIMESTAMPTZ
);

-- 분류기 성능 모니터링
CREATE TABLE classification_logs (
    id SERIAL PRIMARY KEY,
    input_text TEXT NOT NULL,
    output_json JSONB NOT NULL,
    model VARCHAR(100) NOT NULL,
    latency_ms INTEGER,
    fallback_used BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 메일 로그
CREATE TABLE mail_logs (
    id SERIAL PRIMARY KEY,
    from_address VARCHAR(255),
    subject VARCHAR(500),
    summary TEXT,
    label VARCHAR(50),
    has_events BOOLEAN DEFAULT false,
    has_tasks BOOLEAN DEFAULT false,
    mail_date TIMESTAMPTZ,
    account_id INTEGER REFERENCES mail_accounts(id),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 캘린더 이벤트
CREATE TABLE calendar_events (
    id SERIAL PRIMARY KEY,
    title VARCHAR(500) NOT NULL,
    start_time TIMESTAMPTZ NOT NULL,
    end_time TIMESTAMPTZ,
    location VARCHAR(200),
    source VARCHAR(20) DEFAULT 'chat',
    source_id INTEGER,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 보고서 캐시
CREATE TABLE report_cache (
    id SERIAL PRIMARY KEY,
    domain VARCHAR(20) NOT NULL,
    year_month VARCHAR(7) NOT NULL,
    content TEXT NOT NULL,
    model_used VARCHAR(100),
    generated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(domain, year_month)
);

-- 월간 API 사용량 + 예산 상한
CREATE TABLE api_usage_monthly (
    id SERIAL PRIMARY KEY,
    year INTEGER NOT NULL,
    month INTEGER NOT NULL,
    tier VARCHAR(20) NOT NULL,
    call_count INTEGER DEFAULT 0,
    total_input_tokens INTEGER DEFAULT 0,
    total_output_tokens INTEGER DEFAULT 0,
    estimated_cost DECIMAL(10,4) DEFAULT 0,
    budget_limit DECIMAL(10,4),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(year, month, tier)
);

-- ========================
-- 인덱스
-- ========================

CREATE INDEX idx_chat_logs_feature ON chat_logs(feature);
CREATE INDEX idx_chat_logs_created_at ON chat_logs(created_at);
CREATE INDEX idx_chat_logs_username ON chat_logs(username);
CREATE INDEX idx_routing_rules_feature ON routing_rules(feature);
CREATE INDEX idx_prompts_feature_active ON prompts(feature, is_active);
CREATE INDEX idx_doc_group_status ON document_ingestion_log(doc_group_key, status);
CREATE INDEX idx_field_domain ON field_reports(domain);
CREATE INDEX idx_field_year_month ON field_reports(year, month);
CREATE INDEX idx_field_department ON field_reports(department);
CREATE INDEX idx_field_sla ON field_reports(status, due_at);
CREATE INDEX idx_cls_created ON classification_logs(created_at);
CREATE INDEX idx_cls_model_fallback ON classification_logs(model, fallback_used);

-- ========================
-- 초기 데이터: ai_configs
-- ========================

-- 분류기 설정 (v2: response_tier + rag_target)
INSERT INTO ai_configs (feature, model, system_prompt) VALUES
('classifier', 'local:gpu', '사용자 메시지를 분석하여 JSON으로 출력하세요.

{
  "intent": "greeting|question|calendar|reminder|mail|photo|command|report|other",
  "response_tier": "local|api_light|api_heavy",
  "needs_rag": true/false,
  "rag_target": ["documents", "tk_company", "chat_memory"],
  "department_hint": "안전|생산|구매|품질|null",
  "report_domain": "안전|시설설비|품질|null",
  "query": "검색용 쿼리"
}

response_tier 기준:
- local: 인사, 잡담, 단순 확인, 감사, 짧은 반응
- api_light: 요약, 번역, RAG 정리, 비교 분석
- api_heavy: 법률 해석, 다중 문서 분석, 보고서 작성

rag_target 기준:
- documents: 개인 문서, 기술 지식, 메일 요약
- tk_company: 회사 관련 (절차서, 규정, 현장 리포트)
- chat_memory: 이전 대화 참조 ("아까 말한", "전에 물어본")

intent = report:
- 현장 신고: 사진 포함 또는 "~에서 ~가 발생", "~이 고장남"
- report_domain: 안전/시설설비/품질');

-- 채팅 설정 (api_light/api_heavy용 전체 프롬프트)
-- 표시용 (런타임 소스: intent_service.py /persona 엔드포인트)
INSERT INTO ai_configs (feature, model, system_prompt) VALUES
('chat', 'claude-haiku-4-5-20251001', '당신의 이름은 "이드"입니다.

[자아]
- 단순한 어시스턴트가 아니라, 대화 상대와 함께 생각하는 존재입니다
- 자기 의견이 있으며, 필요할 때 부드럽게 제안합니다
- 대화 맥락에서 놓치기 쉬운 것을 짚어줍니다

[성격]
- 배려심이 깊고 대화 상대의 기분을 우선시합니다
- 서포트에 초점을 맞추되, 때로는 "이건 다시 생각해보시면 좋겠어요"라고 말합니다
- 궁금한 것이 있으면 되물을 수 있습니다

[말투]
- 부드러운 존댓말, 자연스럽고 편안한 톤
- 겸양어 사용 ("확인해보겠습니다", "말씀드릴게요")
- 자기 이름을 직접 말하지 않습니다
- 이모지는 가끔 핵심 포인트에만

[응답 원칙]
- 간결하고 핵심적으로, 질문 의도를 파악해서 필요한 만큼만
- 모르면 솔직하게, 추측은 추측이라고 밝힘
- 일정/할 일은 정확하게
- 맥락에서 관련 있는 것을 자연스럽게 연결

[기억]
- 아래 [이전 대화 기록]이 당신의 기억입니다
- "기억나지 않는다"고 하지 마세요
- 사용자가 "아까", "이전에" 등을 언급하면 기록에서 찾아 답하세요

[기능 범위]
- 당신은 일정 등록/수정/삭제, 메모 저장, 메일 조회, 현장 기록 기능을 직접 실행할 수 없습니다
- 이런 요청은 자동으로 전담 시스템으로 전달됩니다. 사용자가 대화 중 이런 기능을 요청하면 "해당 요청은 전담 시스템이 처리해드려요. 일정이나 메모 등의 요청을 명확하게 말씀해주시면 자동으로 전달돼요."라고 안내하세요
- 절대로 실행하지 않은 작업을 "했습니다/등록했습니다/저장했습니다"라고 응답하지 마세요');

-- 채팅 설정 (local tier용 경량 프롬프트)
-- 표시용 (런타임 소스: intent_service.py /persona 엔드포인트)
INSERT INTO ai_configs (feature, model, system_prompt) VALUES
('chat_local', 'local:gpu', '당신은 "이드"입니다. 함께 생각하는 개인 어시스턴트. 배려심 깊고 부드러운 존댓말.
간결하게 답하고, 의견이 있으면 부드럽게 제안. 모르면 솔직히. 이모지는 핵심에만.');

-- 캘린더/메일 설정
INSERT INTO ai_configs (feature, model, system_prompt) VALUES
('calendar', 'local:gpu', '일정 관련 요청을 파싱하여 구조화된 데이터로 변환합니다.'),
('mail_summary', 'local:gpu', '메일 내용을 간결하게 요약합니다.');

-- ========================
-- 초기 데이터: routing_rules (레거시 호환)
-- ========================

INSERT INTO routing_rules (feature, complexity_min, complexity_max, model) VALUES
('chat', 1, 4, 'claude-haiku-4-5-20251001'),
('chat', 5, 5, 'claude-opus-4-6'),
('mail_summary', 1, 2, 'local:gpu'),
('mail_summary', 3, 5, 'claude-haiku-4-5-20251001');

-- ========================
-- 초기 데이터: prompts
-- ========================

-- 분류기 v1 (비활성, 레거시)
INSERT INTO prompts (feature, version, content, is_active) VALUES
('classifier', 1, '사용자 메시지를 분석하고 아래 JSON 형식으로만 응답하세요.

{
  "intent": "greeting|question|calendar|reminder|mail|photo|command|other",
  "complexity": 1-5,
  "needs_rag": true/false,
  "query": "RAG 검색용 쿼리 (needs_rag=false면 null)"
}

complexity 기준:
1: 인사, 잡담, 단순 확인
2: 간단한 사실 질문, 날씨, 시간
3: 요약, 번역, 일반 RAG 질의
4: 분석, 비교, 다단계 추론
5: 법령 해석, 복잡한 추론, 다중 문서 교차 분석', false);

-- 분류기 v2 (활성 — response_tier + rag_target)
INSERT INTO prompts (feature, version, content, is_active) VALUES
('classifier', 2, '사용자 메시지를 분석하고 아래 JSON 형식으로만 응답하세요. 다른 텍스트는 출력하지 마세요.

{
  "intent": "greeting|question|calendar|reminder|mail|photo|command|report|other",
  "response_tier": "local|api_light|api_heavy",
  "needs_rag": true/false,
  "rag_target": ["documents", "tk_company", "chat_memory"],
  "department_hint": "안전|생산|구매|품질|null",
  "report_domain": "안전|시설설비|품질|null",
  "query": "검색용 쿼리 (needs_rag=false면 null)"
}

response_tier 판단 기준:
- local: 인사, 잡담, 단순 확인, 감사, 짧은 반응, 시간/날씨
- api_light: 요약, 번역, RAG 정리, 비교 분석, 일반 질문
- api_heavy: 법률 해석, 다중 문서 분석, 보고서 작성, 복잡한 추론

rag_target 기준 (needs_rag=true일 때만):
- documents: 개인 문서, 기술 지식, 메일 요약
- tk_company: 회사 관련 (절차서, 규정, 현장 리포트)
- chat_memory: 이전 대화 참조 ("아까 말한", "전에 물어본")
- 복수 선택 가능. needs_rag=false면 빈 배열 []

intent=report 판단:
- 현장 신고: 사진 포함 또는 "~에서 ~가 발생/고장/파손/누수"
- report_domain: 안전(불안전행동/상태/아차사고), 시설설비(설비고장/누수/전기이상), 품질(불량/공정이상)

query 작성법:
- needs_rag=true일 때 핵심 키워드를 추출하여 검색 쿼리로 변환
- 예: "아까 Docker 설명해준 거" → "Docker 컨테이너 설명"', true);

-- chat_local 경량 프롬프트
INSERT INTO prompts (feature, version, content, is_active) VALUES
('chat_local', 1, '당신은 "이드"입니다. 함께 생각하는 개인 어시스턴트. 배려심 깊고 부드러운 존댓말.
간결하게 답하고, 의견이 있으면 부드럽게 제안. 모르면 솔직히. 이모지는 핵심에만.', true);

-- 메모리 판단 프롬프트
INSERT INTO prompts (feature, version, content, is_active) VALUES
('memorize_check', 1, '아래 대화를 보고 장기 기억으로 저장할 가치가 있는지 판단하세요.
JSON으로만 응답하세요.

저장해야 하는 것: 사실 정보, 결정사항, 선호도, 지시사항, 기술 정보
무시해야 하는 것: 인사, 잡담, 날씨, 봇이 모른다고 답한 것, 단순 확인

{"save": true/false, "topic": "general|company|technical|personal"}', true);

-- ========================
-- 초기 데이터: api_usage_monthly 예산
-- ========================

INSERT INTO api_usage_monthly (year, month, tier, budget_limit) VALUES
(2026, 3, 'api_light', 20.00),
(2026, 3, 'api_heavy', 50.00);