-- HR 对话质量评估系统 - Supabase 数据库表结构
-- 在 Supabase SQL Editor 中执行此脚本

-- 创建对话历史表
CREATE TABLE IF NOT EXISTS conversation_history (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    session_id TEXT NOT NULL,
    user_message TEXT NOT NULL,
    assistant_message TEXT NOT NULL,
    analysis_report JSONB,
    context_summary JSONB,
    metadata JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 创建索引以提高查询性能
CREATE INDEX IF NOT EXISTS idx_conversation_history_session_id ON conversation_history(session_id);
CREATE INDEX IF NOT EXISTS idx_conversation_history_created_at ON conversation_history(created_at DESC);

-- 启用行级安全性 (RLS)
ALTER TABLE conversation_history ENABLE ROW LEVEL SECURITY;

-- 允许所有操作（演示环境，生产环境应限制访问）
CREATE POLICY "Enable all access for conversation_history" ON conversation_history
    FOR ALL USING (true);

-- 创建获取会话历史的函数
CREATE OR REPLACE FUNCTION get_conversation_history(p_session_id TEXT, p_limit INTEGER DEFAULT 50)
RETURNS TABLE (
    id UUID,
    session_id TEXT,
    user_message TEXT,
    assistant_message TEXT,
    analysis_report JSONB,
    context_summary JSONB,
    metadata JSONB,
    created_at TIMESTAMP WITH TIME ZONE
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT *
    FROM conversation_history
    WHERE session_id = p_session_id
    ORDER BY created_at ASC
    LIMIT p_limit;
END;
$$;

-- 创建删除会话的函数
CREATE OR REPLACE FUNCTION delete_session(p_session_id TEXT)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM conversation_history
    WHERE session_id = p_session_id;

    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RETURN deleted_count;
END;
$$;

-- 创建统计函数
CREATE OR REPLACE FUNCTION get_conversation_statistics(p_hours INTEGER DEFAULT 24)
RETURNS TABLE (
    total_conversations BIGINT,
    unique_sessions BIGINT,
    avg_score NUMERIC,
    high_risk_count BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        COUNT(*) as total_conversations,
        COUNT(DISTINCT session_id) as unique_sessions,
        COALESCE(AVG(
            CASE
                WHEN analysis_report->>'overall_score' IS NOT NULL
                THEN CAST(analysis_report->>'overall_score' AS NUMERIC)
                ELSE NULL
            END
        ), 0) as avg_score,
        COUNT(*) FILTER (
            WHERE analysis_report->'risk_assessment'->>'risk_level' = 'high'
        ) as high_risk_count
    FROM conversation_history
    WHERE created_at >= NOW() - (p_hours || ' hours')::INTERVAL;
END;
$$;

-- 评论：表创建完成
-- COMMENT ON TABLE conversation_history IS 'HR对话历史记录表';