-- ========================================
-- V3: 회의종료 기능을 위한 스키마 확장
-- ========================================
-- 작성일: 2025-10-28
-- 설명: 참석자별 회의록, 안건별 섹션, AI 요약 결과 캐싱, Todo 자동 추출 지원

-- ========================================
-- 1. minutes 테이블 확장
-- ========================================
-- 참석자별 회의록 지원 (user_id로 구분)
-- user_id가 NULL이면 AI 통합 회의록, NOT NULL이면 참석자별 회의록

ALTER TABLE minutes
ADD COLUMN IF NOT EXISTS user_id VARCHAR(100);

-- 인덱스 추가
CREATE INDEX IF NOT EXISTS idx_minutes_meeting_user ON minutes(meeting_id, user_id);

-- 코멘트 추가
COMMENT ON COLUMN minutes.user_id IS '작성자 사용자 ID (NULL: AI 통합 회의록, NOT NULL: 참석자별 회의록)';

-- ========================================
-- 2. agenda_sections 테이블 생성
-- ========================================
-- 안건별 AI 요약 결과 저장

CREATE TABLE IF NOT EXISTS agenda_sections (
    id VARCHAR(36) PRIMARY KEY,
    minutes_id VARCHAR(36) NOT NULL,
    meeting_id VARCHAR(50) NOT NULL,

    -- 안건 정보
    agenda_number INT NOT NULL,
    agenda_title VARCHAR(200) NOT NULL,

    -- AI 요약 결과
    ai_summary_short TEXT,
    discussions TEXT,
    decisions JSON,
    pending_items JSON,
    opinions JSON,

    -- 메타데이터
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- 외래키
    CONSTRAINT fk_agenda_sections_minutes
        FOREIGN KEY (minutes_id) REFERENCES minutes(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_agenda_sections_meeting
        FOREIGN KEY (meeting_id) REFERENCES meetings(meeting_id)
        ON DELETE CASCADE
);

-- 인덱스 생성
CREATE INDEX IF NOT EXISTS idx_sections_meeting ON agenda_sections(meeting_id);
CREATE INDEX IF NOT EXISTS idx_sections_agenda ON agenda_sections(meeting_id, agenda_number);
CREATE INDEX IF NOT EXISTS idx_sections_minutes ON agenda_sections(minutes_id);

-- 코멘트 추가
COMMENT ON TABLE agenda_sections IS '안건별 회의록 섹션 - AI 요약 결과 저장';
COMMENT ON COLUMN agenda_sections.id IS '섹션 고유 ID';
COMMENT ON COLUMN agenda_sections.minutes_id IS '회의록 ID (통합 회의록 참조)';
COMMENT ON COLUMN agenda_sections.meeting_id IS '회의 ID';
COMMENT ON COLUMN agenda_sections.agenda_number IS '안건 번호 (1, 2, 3...)';
COMMENT ON COLUMN agenda_sections.agenda_title IS '안건 제목';
COMMENT ON COLUMN agenda_sections.ai_summary_short IS 'AI 생성 짧은 요약 (1줄, 20자 이내)';
COMMENT ON COLUMN agenda_sections.discussions IS '논의 사항 (핵심 내용 3-5문장)';
COMMENT ON COLUMN agenda_sections.decisions IS '결정 사항 배열 (JSON)';
COMMENT ON COLUMN agenda_sections.pending_items IS '보류 사항 배열 (JSON)';
COMMENT ON COLUMN agenda_sections.opinions IS '참석자별 의견 (JSON: [{speaker, opinion}])';

-- ========================================
-- 3. ai_summaries 테이블 생성
-- ========================================
-- AI 요약 결과 캐싱 및 성능 최적화

CREATE TABLE IF NOT EXISTS ai_summaries (
    id VARCHAR(36) PRIMARY KEY,
    meeting_id VARCHAR(50) NOT NULL,
    summary_type VARCHAR(50) NOT NULL,

    -- 입력 정보
    source_minutes_ids JSON NOT NULL,

    -- AI 처리 결과
    result JSON NOT NULL,
    processing_time_ms INT,
    model_version VARCHAR(50) DEFAULT 'claude-3.5-sonnet',

    -- 통계 정보
    keywords JSON,
    statistics JSON,

    -- 메타데이터
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- 외래키
    CONSTRAINT fk_ai_summaries_meeting
        FOREIGN KEY (meeting_id) REFERENCES meetings(meeting_id)
        ON DELETE CASCADE
);

-- 인덱스 생성
CREATE INDEX IF NOT EXISTS idx_summaries_meeting ON ai_summaries(meeting_id);
CREATE INDEX IF NOT EXISTS idx_summaries_type ON ai_summaries(meeting_id, summary_type);
CREATE INDEX IF NOT EXISTS idx_summaries_created ON ai_summaries(created_at);

-- 코멘트 추가
COMMENT ON TABLE ai_summaries IS 'AI 요약 결과 캐시 테이블';
COMMENT ON COLUMN ai_summaries.id IS '요약 결과 고유 ID';
COMMENT ON COLUMN ai_summaries.meeting_id IS '회의 ID';
COMMENT ON COLUMN ai_summaries.summary_type IS '요약 타입 (CONSOLIDATED: 통합 요약, TODO_EXTRACTION: Todo 추출)';
COMMENT ON COLUMN ai_summaries.source_minutes_ids IS '통합에 사용된 회의록 ID 배열 (JSON)';
COMMENT ON COLUMN ai_summaries.result IS 'AI 응답 전체 결과 (JSON)';
COMMENT ON COLUMN ai_summaries.processing_time_ms IS 'AI 처리 시간 (밀리초)';
COMMENT ON COLUMN ai_summaries.model_version IS '사용한 AI 모델 버전';
COMMENT ON COLUMN ai_summaries.keywords IS '주요 키워드 배열 (JSON)';
COMMENT ON COLUMN ai_summaries.statistics IS '통계 정보 (참석자 수, 안건 수 등, JSON)';

-- ========================================
-- 4. todos 테이블 확장
-- ========================================
-- AI 자동 추출 정보 추가

ALTER TABLE todos
ADD COLUMN IF NOT EXISTS extracted_by VARCHAR(50) DEFAULT 'AI',
ADD COLUMN IF NOT EXISTS section_reference VARCHAR(200),
ADD COLUMN IF NOT EXISTS extraction_confidence DECIMAL(3,2) DEFAULT 0.00;

-- 인덱스 추가
CREATE INDEX IF NOT EXISTS idx_todos_extracted ON todos(extracted_by);
CREATE INDEX IF NOT EXISTS idx_todos_meeting ON todos(meeting_id);

-- 코멘트 추가
COMMENT ON COLUMN todos.extracted_by IS 'Todo 추출 방법 (AI: AI 자동 추출, MANUAL: 사용자 수동 작성)';
COMMENT ON COLUMN todos.section_reference IS '관련 회의록 섹션 참조 (예: "안건 1", "결정사항 #3")';
COMMENT ON COLUMN todos.extraction_confidence IS 'AI 추출 신뢰도 점수 (0.00~1.00)';

-- ========================================
-- 5. 제약조건 및 트리거 추가
-- ========================================

-- updated_at 자동 업데이트 함수 (PostgreSQL)
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- agenda_sections 테이블에 updated_at 트리거 추가
DROP TRIGGER IF EXISTS update_agenda_sections_updated_at ON agenda_sections;
CREATE TRIGGER update_agenda_sections_updated_at
    BEFORE UPDATE ON agenda_sections
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- ========================================
-- 6. 샘플 데이터 (개발 환경용)
-- ========================================
-- 실제 운영 환경에서는 주석 처리

-- 샘플 회의록 (참석자별)
-- INSERT INTO minutes (id, meeting_id, user_id, content, created_at)
-- VALUES
--     ('sample-minutes-1', 'sample-meeting-1', 'user1@example.com', '회의록 내용 1...', CURRENT_TIMESTAMP),
--     ('sample-minutes-2', 'sample-meeting-1', 'user2@example.com', '회의록 내용 2...', CURRENT_TIMESTAMP);

-- 샘플 통합 회의록 (user_id가 NULL)
-- INSERT INTO minutes (id, meeting_id, user_id, content, created_at)
-- VALUES
--     ('sample-minutes-consolidated', 'sample-meeting-1', NULL, 'AI 통합 회의록...', CURRENT_TIMESTAMP);

-- 샘플 안건 섹션
-- INSERT INTO agenda_sections (id, minutes_id, meeting_id, agenda_number, agenda_title, ai_summary_short, discussions, decisions, pending_items, opinions)
-- VALUES
--     ('sample-section-1', 'sample-minutes-consolidated', 'sample-meeting-1', 1, '신제품 기획 방향성',
--      '타겟 고객을 20-30대로 설정...',
--      '신제품의 주요 타겟 고객층을 20-30대 직장인으로 설정하고...',
--      '["타겟 고객: 20-30대 직장인", "UI/UX 개선을 최우선 과제로 설정"]'::json,
--      '[]'::json,
--      '[{"speaker": "김민준", "opinion": "타겟 고객층을 명확히 설정하여 마케팅 전략 수립 필요"}]'::json);

-- ========================================
-- 7. 권한 설정 (필요시)
-- ========================================
-- GRANT SELECT, INSERT, UPDATE, DELETE ON agenda_sections TO meeting_service_user;
-- GRANT SELECT, INSERT, UPDATE, DELETE ON ai_summaries TO meeting_service_user;
-- GRANT SELECT, INSERT, UPDATE, DELETE ON ai_summaries TO ai_service_user;
