supabase_init.sql

-- =====================================================
-- Supabase 초기 설정 통합 스크립트 (완전 초기화 버전)
-- 간호 인수인계 교육 플랫폼
-- 실행 순서: 이 파일을 Supabase SQL Editor에서 한 번만 실행하세요
-- =====================================================


-- =====================================================
-- 1. 기본 테이블 생성 (의존성 순서대로)
-- =====================================================

-- 1.1 patients 테이블 (환자 기본 정보)
CREATE TABLE patients (
    id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INTEGER NOT NULL,
    gender VARCHAR(10) NOT NULL,
    weight INTEGER,
    height INTEGER,
    diagnosis VARCHAR(200) NOT NULL,
    admission_date DATE NOT NULL,
    attending_physician VARCHAR(100),
    allergies VARCHAR(200),
    comorbidities TEXT,
    room_number VARCHAR(20),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

COMMENT ON TABLE patients IS '환자 기본 정보';

-- 1.2 scenarios 테이블 (인수인계 시나리오)
CREATE TABLE scenarios (
    id VARCHAR(50) PRIMARY KEY,
    patient_id VARCHAR(50) NOT NULL,
    title VARCHAR(200) NOT NULL,
    day INTEGER NOT NULL,
    handoff_situation VARCHAR(200),
    vitals JSONB,
    labs JSONB,
    orders JSONB,
    nursing_notes JSONB,
    physical_exam JSONB,
    imaging JSONB,
    surgery_info JSONB,
    discharge_education JSONB,
    hospital_day INTEGER,
    status_badge VARCHAR(20),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    CONSTRAINT scenarios_patient_fkey 
        FOREIGN KEY (patient_id) 
        REFERENCES patients(id) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE
);

COMMENT ON TABLE scenarios IS '인수인계 시나리오';

-- 1.3 sessions 테이블 (세션 관리) - session_id를 PK로
CREATE TABLE sessions (
    session_id VARCHAR(100) PRIMARY KEY,
    student_id VARCHAR(100) NOT NULL,
    scenario_id VARCHAR(50) NOT NULL,
    status VARCHAR(20) DEFAULT 'active',
    started_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    ended_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    CONSTRAINT sessions_scenario_fkey 
        FOREIGN KEY (scenario_id) 
        REFERENCES scenarios(id) 
        ON DELETE RESTRICT 
        ON UPDATE CASCADE
);

COMMENT ON TABLE sessions IS 'TSID 기반 채팅 세션 관리 테이블';
COMMENT ON COLUMN sessions.session_id IS 'TSID 기반 세션 식별자 (PRIMARY KEY)';

-- 1.4 chat_history 테이블 (학생과 AI 간의 채팅 기록)
CREATE TABLE chat_history (
    id SERIAL PRIMARY KEY,
    session_id VARCHAR(100) NOT NULL,
    student_id VARCHAR(100) NOT NULL,
    scenario_id VARCHAR(50) NOT NULL,
    role VARCHAR(20) NOT NULL CHECK (role IN ('user', 'assistant')),
    message TEXT NOT NULL,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    CONSTRAINT chat_history_session_fkey 
        FOREIGN KEY (session_id) 
        REFERENCES sessions(session_id) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE
);

COMMENT ON TABLE chat_history IS '학생과 AI 간의 채팅 기록';

-- 1.5 handoff_records 테이블 (인수인계 기록 및 평가)
CREATE TABLE handoff_records (
    id SERIAL PRIMARY KEY,
    session_id VARCHAR(100),
    student_id VARCHAR(100) NOT NULL,
    scenario_id VARCHAR(50) NOT NULL,
    submitted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    situation TEXT,
    background TEXT,
    assessment TEXT,
    recommendation TEXT,
    total_score FLOAT,
    category_scores JSONB,
    strengths JSONB,
    improvements JSONB,
    detailed_feedback TEXT,
    missing_critical_info JSONB,
    safety_concerns JSONB,
    CONSTRAINT handoff_records_scenario_fkey 
        FOREIGN KEY (scenario_id) 
        REFERENCES scenarios(id) 
        ON DELETE RESTRICT 
        ON UPDATE CASCADE,
    CONSTRAINT handoff_records_session_fkey 
        FOREIGN KEY (session_id) 
        REFERENCES sessions(session_id) 
        ON DELETE SET NULL 
        ON UPDATE CASCADE
);

COMMENT ON TABLE handoff_records IS '학생의 인수인계 기록 및 AI 평가 결과';
COMMENT ON COLUMN handoff_records.session_id IS '채팅 세션 ID (nullable)';

-- 1.6 sbar_drafts 테이블 (작성 중인 SBAR 임시 저장)
CREATE TABLE sbar_drafts (
    id SERIAL PRIMARY KEY,
    session_id VARCHAR(100) NOT NULL UNIQUE,
    student_id VARCHAR(100) NOT NULL,
    scenario_id VARCHAR(50) NOT NULL,
    situation TEXT DEFAULT '',
    background TEXT DEFAULT '',
    assessment TEXT DEFAULT '',
    recommendation TEXT DEFAULT '',
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    CONSTRAINT sbar_drafts_session_fkey 
        FOREIGN KEY (session_id) 
        REFERENCES sessions(session_id) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE
);

COMMENT ON TABLE sbar_drafts IS '작성 중인 SBAR 임시 저장';

-- =====================================================
-- 2. 인덱스 생성 (성능 최적화)
-- =====================================================

-- patients
CREATE INDEX idx_patients_name ON patients(name);

-- scenarios
CREATE INDEX idx_scenarios_patient ON scenarios(patient_id);
CREATE INDEX idx_scenarios_day ON scenarios(day);

-- sessions
CREATE INDEX idx_sessions_student ON sessions(student_id);
CREATE INDEX idx_sessions_scenario ON sessions(scenario_id);
CREATE INDEX idx_sessions_status ON sessions(status);
CREATE INDEX idx_sessions_started ON sessions(started_at DESC);

-- chat_history
CREATE INDEX idx_chat_history_session ON chat_history(session_id);
CREATE INDEX idx_chat_history_student ON chat_history(student_id);
CREATE INDEX idx_chat_history_timestamp ON chat_history(timestamp);

-- handoff_records
CREATE INDEX idx_handoff_student ON handoff_records(student_id);
CREATE INDEX idx_handoff_scenario ON handoff_records(scenario_id);
CREATE INDEX idx_handoff_session ON handoff_records(session_id);
CREATE INDEX idx_handoff_submitted ON handoff_records(submitted_at DESC);

-- sbar_drafts
CREATE INDEX idx_sbar_session ON sbar_drafts(session_id);
CREATE INDEX idx_sbar_student ON sbar_drafts(student_id);

-- =====================================================
-- 3. 트리거 함수 및 트리거 생성
-- =====================================================

-- updated_at 자동 업데이트 함수
CREATE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 세션 종료 시 ended_at 자동 업데이트 함수
CREATE FUNCTION update_session_ended_at()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status = 'completed' OR NEW.status = 'archived' THEN
        IF NEW.ended_at IS NULL THEN
            NEW.ended_at = NOW();
        END IF;
    END IF;
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 자동 세션 생성 함수 (chat_history insert 시)
CREATE FUNCTION auto_create_session()
RETURNS TRIGGER AS $$
BEGIN
    -- session_id가 sessions에 없으면 자동 생성
    IF NOT EXISTS (SELECT 1 FROM sessions WHERE session_id = NEW.session_id) THEN
        INSERT INTO sessions (session_id, student_id, scenario_id, status)
        VALUES (NEW.session_id, NEW.student_id, NEW.scenario_id, 'active')
        ON CONFLICT (session_id) DO NOTHING;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 트리거 생성
CREATE TRIGGER update_patients_updated_at 
    BEFORE UPDATE ON patients
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_scenarios_updated_at 
    BEFORE UPDATE ON scenarios
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_sessions_updated_at
    BEFORE UPDATE ON sessions
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_session_on_status_change
    BEFORE UPDATE ON sessions
    FOR EACH ROW
    WHEN (OLD.status IS DISTINCT FROM NEW.status)
    EXECUTE FUNCTION update_session_ended_at();

CREATE TRIGGER update_sbar_drafts_updated_at 
    BEFORE UPDATE ON sbar_drafts
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- chat_history insert 시 sessions 자동 생성
CREATE TRIGGER auto_create_session_trigger
    BEFORE INSERT ON chat_history
    FOR EACH ROW
    EXECUTE FUNCTION auto_create_session();

-- =====================================================
-- 4. Row Level Security (RLS) 설정
-- =====================================================

-- RLS 활성화
ALTER TABLE patients ENABLE ROW LEVEL SECURITY;
ALTER TABLE scenarios ENABLE ROW LEVEL SECURITY;
ALTER TABLE sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE chat_history ENABLE ROW LEVEL SECURITY;
ALTER TABLE handoff_records ENABLE ROW LEVEL SECURITY;
ALTER TABLE sbar_drafts ENABLE ROW LEVEL SECURITY;

-- 정책 생성 (모든 사용자 접근 허용)
CREATE POLICY "patients_all_access" ON patients 
    FOR ALL USING (true) WITH CHECK (true);

CREATE POLICY "scenarios_all_access" ON scenarios 
    FOR ALL USING (true) WITH CHECK (true);

CREATE POLICY "sessions_all_access" ON sessions 
    FOR ALL USING (true) WITH CHECK (true);

CREATE POLICY "chat_history_all_access" ON chat_history 
    FOR ALL USING (true) WITH CHECK (true);

CREATE POLICY "handoff_records_all_access" ON handoff_records 
    FOR ALL USING (true) WITH CHECK (true);

CREATE POLICY "sbar_drafts_all_access" ON sbar_drafts 
    FOR ALL USING (true) WITH CHECK (true);

-- =====================================================
-- 완료 메시지
-- =====================================================

DO $$
BEGIN
    RAISE NOTICE '';
    RAISE NOTICE '╔══════════════════════════════════════════════════════════════╗';
    RAISE NOTICE '║  ✅ Supabase 완전 초기화 완료!                                ║';
    RAISE NOTICE '╚══════════════════════════════════════════════════════════════╝';
    RAISE NOTICE '';
    RAISE NOTICE '📊 생성된 테이블:';
    RAISE NOTICE '   1. patients (환자 정보)';
    RAISE NOTICE '   2. scenarios (시나리오)';
    RAISE NOTICE '   3. sessions (세션 관리 - session_id가 PK)';
    RAISE NOTICE '   4. chat_history (채팅 기록)';
    RAISE NOTICE '   5. handoff_records (인수인계 기록)';
    RAISE NOTICE '   6. sbar_drafts (SBAR 초안)';
    RAISE NOTICE '';
    RAISE NOTICE '🔗 외래 키:';
    RAISE NOTICE '   - scenarios → patients';
    RAISE NOTICE '   - sessions → scenarios';
    RAISE NOTICE '   - chat_history → sessions (자동 생성 트리거)';
    RAISE NOTICE '   - handoff_records → sessions, scenarios';
    RAISE NOTICE '   - sbar_drafts → sessions';
    RAISE NOTICE '';
    RAISE NOTICE '⚡ 자동 기능:';
    RAISE NOTICE '   - chat_history insert 시 sessions 자동 생성';
    RAISE NOTICE '   - 세션 완료 시 ended_at 자동 설정';
    RAISE NOTICE '   - updated_at 자동 갱신';
    RAISE NOTICE '';
    RAISE NOTICE '📋 다음 단계:';
    RAISE NOTICE '   uv run python init_db.py - 시나리오 데이터 로드';
    RAISE NOTICE '';
END $$;