-- 解决 Analytics RPC 函数冲突问题
-- 删除现有函数并重新创建正确版本

-- 1. 删除所有现有函数（如果存在）
DROP FUNCTION IF EXISTS public.get_teacher_analytics(uuid, text, text);
DROP FUNCTION IF EXISTS public.get_top_performers(uuid, text, text, integer);
DROP FUNCTION IF EXISTS public.get_chart_data(uuid, text, text, text);
DROP FUNCTION IF EXISTS public.get_recent_activities(uuid, integer);

-- 2. 创建 get_teacher_analytics 函数（返回测试数据）
CREATE OR REPLACE FUNCTION public.get_teacher_analytics(
    teacher_id uuid DEFAULT NULL,
    start_date text DEFAULT NULL,
    end_date text DEFAULT NULL
)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    result jsonb;
BEGIN
    -- 返回模拟数据用于测试
    result := jsonb_build_object(
        'total_students', 28,
        'total_assignments', 12,
        'completion_rate', 87.5,
        'average_score', 82.3,
        'active_classes', 4,
        'total_submissions', 285,
        'pending_reviews', 15,
        'graded_submissions', 270
    );
    
    RETURN result;
EXCEPTION WHEN OTHERS THEN
    -- 错误处理
    RETURN jsonb_build_object(
        'error', 'Function execution failed',
        'message', SQLERRM
    );
END;
$$;

-- 3. 创建 get_top_performers 函数
CREATE OR REPLACE FUNCTION public.get_top_performers(
    teacher_id uuid DEFAULT NULL,
    start_date text DEFAULT NULL,
    end_date text DEFAULT NULL,
    "limit" integer DEFAULT 10
)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    result jsonb;
BEGIN
    -- 返回模拟的优秀学员数据
    result := jsonb_build_array(
        jsonb_build_object(
            'student_id', '550e8400-e29b-41d4-a716-446655440001',
            'name', '王小明',
            'username', 'wangxiaoming',
            'avatar_url', null,
            'score', 96.8,
            'submission_count', 12,
            'completion_rate', 100.0,
            'class_name', '高三(1)班',
            'rank_position', 1
        ),
        jsonb_build_object(
            'student_id', '550e8400-e29b-41d4-a716-446655440002',
            'name', '张丽华',
            'username', 'zhanglihua',
            'avatar_url', null,
            'score', 94.2,
            'submission_count', 11,
            'completion_rate', 91.7,
            'class_name', '高三(2)班',
            'rank_position', 2
        ),
        jsonb_build_object(
            'student_id', '550e8400-e29b-41d4-a716-446655440003',
            'name', '李强',
            'username', 'liqiang',
            'avatar_url', null,
            'score', 92.5,
            'submission_count', 10,
            'completion_rate', 83.3,
            'class_name', '高三(1)班',
            'rank_position', 3
        ),
        jsonb_build_object(
            'student_id', '550e8400-e29b-41d4-a716-446655440004',
            'name', '陈美丽',
            'username', 'chenmeili',
            'avatar_url', null,
            'score', 90.1,
            'submission_count', 9,
            'completion_rate', 75.0,
            'class_name', '高三(2)班',
            'rank_position', 4
        ),
        jsonb_build_object(
            'student_id', '550e8400-e29b-41d4-a716-446655440005',
            'name', '刘志伟',
            'username', 'liuzhiwei',
            'avatar_url', null,
            'score', 88.7,
            'submission_count', 8,
            'completion_rate', 66.7,
            'class_name', '高三(1)班',
            'rank_position', 5
        )
    );
    
    RETURN result;
EXCEPTION WHEN OTHERS THEN
    RETURN jsonb_build_object(
        'error', 'Function execution failed',
        'message', SQLERRM
    );
END;
$$;

-- 4. 创建 get_chart_data 函数
CREATE OR REPLACE FUNCTION public.get_chart_data(
    teacher_id uuid DEFAULT NULL,
    start_date text DEFAULT NULL,
    end_date text DEFAULT NULL,
    "type" text DEFAULT 'completion_rate'
)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    result jsonb;
    chart_type text;
BEGIN
    chart_type := COALESCE("type", 'completion_rate');
    
    -- 根据类型返回不同的图表数据
    IF chart_type = 'completion_rate' THEN
        -- 完成率趋势数据
        result := jsonb_build_array(
            jsonb_build_object('date_key', '2024-06-01', 'value', 75.5, 'label', '完成率', 'count', 15),
            jsonb_build_object('date_key', '2024-06-02', 'value', 82.3, 'label', '完成率', 'count', 18),
            jsonb_build_object('date_key', '2024-06-03', 'value', 88.1, 'label', '完成率', 'count', 22),
            jsonb_build_object('date_key', '2024-06-04', 'value', 90.5, 'label', '完成率', 'count', 25),
            jsonb_build_object('date_key', '2024-06-05', 'value', 87.2, 'label', '完成率', 'count', 23),
            jsonb_build_object('date_key', '2024-06-06', 'value', 92.8, 'label', '完成率', 'count', 28),
            jsonb_build_object('date_key', '2024-06-07', 'value', 85.6, 'label', '完成率', 'count', 21)
        );
    ELSIF chart_type = 'score_distribution' THEN
        -- 成绩分布数据
        result := jsonb_build_array(
            jsonb_build_object('date_key', '2024-06-01', 'value', 0, 'label', '90-100', 'count', 25),
            jsonb_build_object('date_key', '2024-06-01', 'value', 0, 'label', '80-89', 'count', 45),
            jsonb_build_object('date_key', '2024-06-01', 'value', 0, 'label', '70-79', 'count', 35),
            jsonb_build_object('date_key', '2024-06-01', 'value', 0, 'label', '60-69', 'count', 20),
            jsonb_build_object('date_key', '2024-06-01', 'value', 0, 'label', '60以下', 'count', 8)
        );
    ELSIF chart_type = 'submission_trend' THEN
        -- 提交趋势数据
        result := jsonb_build_array(
            jsonb_build_object('date_key', '2024-06-01', 'value', 12, 'label', '提交数量', 'count', 12),
            jsonb_build_object('date_key', '2024-06-02', 'value', 15, 'label', '提交数量', 'count', 15),
            jsonb_build_object('date_key', '2024-06-03', 'value', 18, 'label', '提交数量', 'count', 18),
            jsonb_build_object('date_key', '2024-06-04', 'value', 22, 'label', '提交数量', 'count', 22),
            jsonb_build_object('date_key', '2024-06-05', 'value', 19, 'label', '提交数量', 'count', 19),
            jsonb_build_object('date_key', '2024-06-06', 'value', 25, 'label', '提交数量', 'count', 25),
            jsonb_build_object('date_key', '2024-06-07', 'value', 16, 'label', '提交数量', 'count', 16)
        );
    ELSE
        -- 默认返回空数组
        result := '[]'::jsonb;
    END IF;
    
    RETURN result;
EXCEPTION WHEN OTHERS THEN
    RETURN jsonb_build_object(
        'error', 'Function execution failed',
        'message', SQLERRM,
        'chart_type', chart_type
    );
END;
$$;

-- 5. 创建 get_recent_activities 函数
CREATE OR REPLACE FUNCTION public.get_recent_activities(
    teacher_id uuid DEFAULT NULL,
    "limit" integer DEFAULT 20
)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    result jsonb;
BEGIN
    -- 返回模拟的近期活动数据
    result := jsonb_build_array(
        jsonb_build_object(
            'activity_id', '550e8400-e29b-41d4-a716-446655440001',
            'activity_type', 'assignment_submitted',
            'title', '王小明提交了跑步训练作业',
            'description', '完成了5公里跑步训练，用时25分钟',
            'student_name', '王小明',
            'assignment_title', '跑步训练',
            'activity_time', '2024-06-12T08:30:00Z',
            'time_ago', '2小时前'
        ),
        jsonb_build_object(
            'activity_id', '550e8400-e29b-41d4-a716-446655440002',
            'activity_type', 'assignment_submitted',
            'title', '张丽华提交了力量训练作业',
            'description', '完成了器械训练，表现优秀',
            'student_name', '张丽华',
            'assignment_title', '力量训练',
            'activity_time', '2024-06-12T07:15:00Z',
            'time_ago', '3小时前'
        ),
        jsonb_build_object(
            'activity_id', '550e8400-e29b-41d4-a716-446655440003',
            'activity_type', 'project_completed',
            'title', '李强完成了体能测试项目',
            'description', '各项指标达到优秀标准',
            'student_name', '李强',
            'assignment_title', '体能测试',
            'activity_time', '2024-06-12T06:45:00Z',
            'time_ago', '4小时前'
        ),
        jsonb_build_object(
            'activity_id', '550e8400-e29b-41d4-a716-446655440004',
            'activity_type', 'assignment_graded',
            'title', '陈美丽的作业已批改',
            'description', '体操训练作业获得优秀评价',
            'student_name', '陈美丽',
            'assignment_title', '体操训练',
            'activity_time', '2024-06-12T05:20:00Z',
            'time_ago', '5小时前'
        ),
        jsonb_build_object(
            'activity_id', '550e8400-e29b-41d4-a716-446655440005',
            'activity_type', 'assignment_submitted',
            'title', '刘志伟提交了游泳训练作业',
            'description', '完成了1000米自由泳，技术有所提升',
            'student_name', '刘志伟',
            'assignment_title', '游泳训练',
            'activity_time', '2024-06-12T04:15:00Z',
            'time_ago', '6小时前'
        )
    );
    
    RETURN result;
EXCEPTION WHEN OTHERS THEN
    RETURN jsonb_build_object(
        'error', 'Function execution failed',
        'message', SQLERRM
    );
END;
$$;

-- 6. 授权函数给 authenticated 用户
GRANT EXECUTE ON FUNCTION public.get_teacher_analytics TO authenticated;
GRANT EXECUTE ON FUNCTION public.get_top_performers TO authenticated; 
GRANT EXECUTE ON FUNCTION public.get_chart_data TO authenticated;
GRANT EXECUTE ON FUNCTION public.get_recent_activities TO authenticated;

-- 7. 添加函数注释
COMMENT ON FUNCTION public.get_teacher_analytics IS '获取教师统计数据（简化版-返回测试数据）';
COMMENT ON FUNCTION public.get_top_performers IS '获取优秀学员排行榜（简化版-返回测试数据）';
COMMENT ON FUNCTION public.get_chart_data IS '获取图表数据（简化版-返回测试数据）';
COMMENT ON FUNCTION public.get_recent_activities IS '获取近期活动（简化版-返回测试数据）';

-- 8. 验证函数创建成功
SELECT 
    routine_name,
    routine_type,
    data_type,
    specific_name
FROM information_schema.routines 
WHERE routine_schema = 'public' 
AND routine_name IN ('get_teacher_analytics', 'get_top_performers', 'get_chart_data', 'get_recent_activities')
ORDER BY routine_name;

-- 9. 测试函数调用
SELECT 'get_teacher_analytics test:' as test_name, public.get_teacher_analytics() as result
UNION ALL
SELECT 'get_top_performers test:', public.get_top_performers()
UNION ALL  
SELECT 'get_chart_data test:', public.get_chart_data()
UNION ALL
SELECT 'get_recent_activities test:', public.get_recent_activities();