akmon/doc_zhipao/enhanced_sports_training_system.sql

-- 学校运动训练数据库优化方案
-- 增加AI支持和流程控制功能
-- 基于现有zhipao.sql的扩展

-- 运动作业类型定义表
CREATE TABLE public.ak_assignment_types (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(64) NOT NULL UNIQUE, -- 作业类型名称
    description TEXT, -- 描述
    default_duration_days INT DEFAULT 7, -- 默认持续天数
    requires_device BOOLEAN DEFAULT false, -- 是否需要设备数据
    requires_video BOOLEAN DEFAULT false, -- 是否需要视频上传
    scoring_criteria JSONB, -- 评分标准
    ai_evaluation_enabled BOOLEAN DEFAULT false, -- 是否启用AI评估
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
COMMENT ON TABLE public.ak_assignment_types IS '运动作业类型定义表';

-- 插入基础作业类型
INSERT INTO public.ak_assignment_types (name, description, default_duration_days, requires_device, requires_video, ai_evaluation_enabled, scoring_criteria) VALUES
('跑步训练', '日常跑步训练任务', 7, true, false, true, '{"distance": {"min": 1000, "max": 5000, "unit": "meters"}, "pace": {"target": 6, "unit": "min/km"}}'),
('体能测试', '综合体能评估', 1, true, true, true, '{"pushups": {"min": 10, "max": 50}, "situps": {"min": 20, "max": 100}, "pullups": {"min": 3, "max": 20}}'),
('技能训练', '运动技能专项训练', 14, false, true, true, '{"technique_score": {"min": 60, "max": 100}, "progress_rate": {"min": 5, "unit": "percent"}}'),
('耐力测试', '心肺耐力测试', 1, true, false, true, '{"vo2_max": {"min": 30, "max": 70}, "heart_rate": {"max": 180}}');

-- 扩展作业表，增加AI和流程控制字段
ALTER TABLE public.ak_assignments ADD COLUMN IF NOT EXISTS assignment_type_id uuid REFERENCES public.ak_assignment_types(id);
ALTER TABLE public.ak_assignments ADD COLUMN IF NOT EXISTS target_metrics JSONB; -- 目标指标
ALTER TABLE public.ak_assignments ADD COLUMN IF NOT EXISTS ai_grading_enabled BOOLEAN DEFAULT false; -- 是否启用AI评分
ALTER TABLE public.ak_assignments ADD COLUMN IF NOT EXISTS auto_approval BOOLEAN DEFAULT false; -- 是否自动审核
ALTER TABLE public.ak_assignments ADD COLUMN IF NOT EXISTS workflow_status VARCHAR(32) DEFAULT 'draft'; -- 工作流状态
ALTER TABLE public.ak_assignments ADD COLUMN IF NOT EXISTS approval_required BOOLEAN DEFAULT true; -- 是否需要审批
ALTER TABLE public.ak_assignments ADD COLUMN IF NOT EXISTS approved_by uuid REFERENCES public.ak_users(id); -- 审批人
ALTER TABLE public.ak_assignments ADD COLUMN IF NOT EXISTS approved_at TIMESTAMP WITH TIME ZONE; -- 审批时间

-- 作业审批工作流表
CREATE TABLE public.ak_assignment_workflow (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    assignment_id uuid REFERENCES public.ak_assignments(id) ON DELETE CASCADE,
    action VARCHAR(32) NOT NULL, -- 动作：submit, approve, reject, modify
    actor_id uuid REFERENCES public.ak_users(id) ON DELETE SET NULL, -- 操作人
    previous_status VARCHAR(32), -- 之前状态
    new_status VARCHAR(32) NOT NULL, -- 新状态
    comments TEXT, -- 备注
    metadata JSONB, -- 元数据
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
COMMENT ON TABLE public.ak_assignment_workflow IS '作业审批工作流记录表';
CREATE INDEX idx_assignment_workflow_assignment_id ON public.ak_assignment_workflow(assignment_id);
CREATE INDEX idx_assignment_workflow_actor_id ON public.ak_assignment_workflow(actor_id);

-- AI评估结果表
CREATE TABLE public.ak_ai_evaluations (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    submission_id uuid REFERENCES public.ak_assignment_submissions(id) ON DELETE CASCADE,
    evaluation_type VARCHAR(32) NOT NULL, -- 评估类型：performance, technique, progress
    ai_model VARCHAR(64), -- 使用的AI模型
    confidence_score FLOAT, -- 置信度
    evaluation_data JSONB NOT NULL, -- 评估详细数据
    suggested_score FLOAT, -- AI建议分数
    feedback_text TEXT, -- AI生成的反馈文本
    improvement_suggestions JSONB, -- 改进建议
    risk_indicators JSONB, -- 风险指标
    processed_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
COMMENT ON TABLE public.ak_ai_evaluations IS 'AI评估结果表';
CREATE INDEX idx_ai_evaluations_submission_id ON public.ak_ai_evaluations(submission_id);

-- 扩展作业提交表
ALTER TABLE public.ak_assignment_submissions ADD COLUMN IF NOT EXISTS device_data_id uuid REFERENCES public.ak_training_records(id); -- 关联设备数据
ALTER TABLE public.ak_assignment_submissions ADD COLUMN IF NOT EXISTS video_analysis_id uuid REFERENCES public.ak_video_analysis(id); -- 关联视频分析
ALTER TABLE public.ak_assignment_submissions ADD COLUMN IF NOT EXISTS ai_score FLOAT; -- AI评分
ALTER TABLE public.ak_assignment_submissions ADD COLUMN IF NOT EXISTS manual_score FLOAT; -- 人工评分
ALTER TABLE public.ak_assignment_submissions ADD COLUMN IF NOT EXISTS final_score FLOAT; -- 最终分数
ALTER TABLE public.ak_assignment_submissions ADD COLUMN IF NOT EXISTS review_status VARCHAR(32) DEFAULT 'pending'; -- 评阅状态
ALTER TABLE public.ak_assignment_submissions ADD COLUMN IF NOT EXISTS reviewed_by uuid REFERENCES public.ak_users(id); -- 评阅人
ALTER TABLE public.ak_assignment_submissions ADD COLUMN IF NOT EXISTS reviewed_at TIMESTAMP WITH TIME ZONE; -- 评阅时间
ALTER TABLE public.ak_assignment_submissions ADD COLUMN IF NOT EXISTS ai_feedback_approved BOOLEAN DEFAULT false; -- AI反馈是否已审核

-- 训练计划模板表
CREATE TABLE public.ak_training_templates (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(128) NOT NULL,
    description TEXT,
    target_age_min INT, -- 适用最小年龄
    target_age_max INT, -- 适用最大年龄
    difficulty_level INT DEFAULT 1, -- 难度级别 1-5
    duration_weeks INT DEFAULT 4, -- 持续周数
    template_data JSONB NOT NULL, -- 模板数据
    ai_adaptive BOOLEAN DEFAULT false, -- 是否支持AI自适应调整
    created_by uuid REFERENCES public.ak_users(id),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
COMMENT ON TABLE public.ak_training_templates IS '训练计划模板表';
CREATE INDEX idx_training_templates_created_by ON public.ak_training_templates(created_by);

-- AI训练建议表
CREATE TABLE public.ak_ai_training_suggestions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE,
    suggestion_type VARCHAR(32) NOT NULL, -- 建议类型：plan_adjustment, risk_warning, improvement
    priority VARCHAR(16) DEFAULT 'medium', -- 优先级：high, medium, low
    title VARCHAR(128) NOT NULL,
    content TEXT NOT NULL,
    data_source JSONB, -- 数据来源
    confidence_score FLOAT,
    action_required BOOLEAN DEFAULT false,
    acknowledged BOOLEAN DEFAULT false,
    acknowledged_by uuid REFERENCES public.ak_users(id),
    acknowledged_at TIMESTAMP WITH TIME ZONE,
    expires_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
COMMENT ON TABLE public.ak_ai_training_suggestions IS 'AI训练建议表';
CREATE INDEX idx_ai_training_suggestions_user_id ON public.ak_ai_training_suggestions(user_id);
CREATE INDEX idx_ai_training_suggestions_priority ON public.ak_ai_training_suggestions(priority);

-- 学习分析表
CREATE TABLE public.ak_learning_analytics (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE,
    class_id uuid REFERENCES public.ak_classes(id),
    analysis_period_start DATE,
    analysis_period_end DATE,
    total_assignments INT DEFAULT 0,
    completed_assignments INT DEFAULT 0,
    avg_score FLOAT,
    improvement_rate FLOAT, -- 改进率
    attendance_rate FLOAT, -- 参与率
    effort_score FLOAT, -- 努力程度评分
    skill_progression JSONB, -- 技能进步数据
    health_indicators JSONB, -- 健康指标
    risk_factors JSONB, -- 风险因素
    recommendations JSONB, -- 推荐建议
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
COMMENT ON TABLE public.ak_learning_analytics IS '学习分析表';
CREATE INDEX idx_learning_analytics_user_id ON public.ak_learning_analytics(user_id);
CREATE INDEX idx_learning_analytics_class_id ON public.ak_learning_analytics(class_id);

-- 数据质量监控表
CREATE TABLE public.ak_data_quality_logs (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    table_name VARCHAR(64) NOT NULL,
    record_id uuid,
    quality_check_type VARCHAR(32) NOT NULL, -- 检查类型
    issue_type VARCHAR(32), -- 问题类型
    severity VARCHAR(16) DEFAULT 'medium', -- 严重程度
    description TEXT,
    metadata JSONB,
    resolved BOOLEAN DEFAULT false,
    resolved_by uuid REFERENCES public.ak_users(id),
    resolved_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
COMMENT ON TABLE public.ak_data_quality_logs IS '数据质量监控表';
CREATE INDEX idx_data_quality_logs_table_name ON public.ak_data_quality_logs(table_name);
CREATE INDEX idx_data_quality_logs_severity ON public.ak_data_quality_logs(severity);

-- 新增权限
INSERT INTO public.ak_permissions (code, name, resource_type, action, is_system, description) VALUES
-- 作业管理增强权限
('teacher.assignment.ai_config', '配置作业AI评估', 'assignment', 'manage', true, '配置作业的AI评估参数'),
('teacher.assignment.workflow', '管理作业工作流', 'assignment', 'manage', true, '管理作业审批流程'),
('teacher.assignment.templates', '使用作业模板', 'assignment', 'read', true, '使用预定义的作业模板'),

-- AI评估相关权限
('teacher.ai_evaluation.review', '审核AI评估结果', 'ai_evaluation', 'manage', true, '审核和调整AI评估结果'),
('teacher.ai_suggestions.manage', '管理AI建议', 'ai_suggestions', 'manage', true, '查看和管理AI训练建议'),

-- 数据分析权限
('teacher.analytics.class', '查看班级分析', 'analytics', 'read', true, '查看班级学习分析数据'),
('school_admin.analytics.school', '查看学校分析', 'analytics', 'read', true, '查看学校级别的分析数据'),
('teacher.training_plans.ai_assist', 'AI辅助训练计划', 'training_plans', 'create', true, '使用AI辅助创建训练计划'),

-- 质量监控权限
('admin.data_quality.monitor', '数据质量监控', 'data_quality', 'read', true, '监控数据质量问题'),
('admin.data_quality.resolve', '处理数据质量问题', 'data_quality', 'manage', true, '处理和解决数据质量问题');

-- 工作流状态管理函数
CREATE OR REPLACE FUNCTION public.update_assignment_workflow_status(
    p_assignment_id uuid,
    p_new_status VARCHAR(32),
    p_actor_id uuid,
    p_comments TEXT DEFAULT NULL
) RETURNS boolean AS $$
DECLARE
    current_status VARCHAR(32);
    valid_transition boolean := false;
BEGIN
    -- 获取当前状态
    SELECT workflow_status INTO current_status 
    FROM public.ak_assignments 
    WHERE id = p_assignment_id;
    
    -- 验证状态转换的有效性
    CASE current_status
        WHEN 'draft' THEN
            valid_transition := p_new_status IN ('submitted', 'cancelled');
        WHEN 'submitted' THEN
            valid_transition := p_new_status IN ('approved', 'rejected', 'draft');
        WHEN 'approved' THEN
            valid_transition := p_new_status IN ('published', 'cancelled');
        WHEN 'published' THEN
            valid_transition := p_new_status IN ('completed', 'cancelled');
        WHEN 'rejected' THEN
            valid_transition := p_new_status IN ('draft', 'cancelled');
        ELSE
            valid_transition := false;
    END CASE;
    
    -- 如果转换无效，返回false
    IF NOT valid_transition THEN
        RETURN false;
    END IF;
    
    -- 更新作业状态
    UPDATE public.ak_assignments 
    SET workflow_status = p_new_status,
        updated_at = now(),
        approved_by = CASE WHEN p_new_status = 'approved' THEN p_actor_id ELSE approved_by END,
        approved_at = CASE WHEN p_new_status = 'approved' THEN now() ELSE approved_at END
    WHERE id = p_assignment_id;
    
    -- 记录工作流历史
    INSERT INTO public.ak_assignment_workflow (
        assignment_id, action, actor_id, previous_status, new_status, comments
    ) VALUES (
        p_assignment_id, p_new_status, p_actor_id, current_status, p_new_status, p_comments
    );
    
    RETURN true;
END;
$$ LANGUAGE plpgsql;

-- AI评估触发函数
CREATE OR REPLACE FUNCTION public.trigger_ai_evaluation(
    p_submission_id uuid
) RETURNS uuid AS $$
DECLARE
    submission_record RECORD;
    evaluation_id uuid;
BEGIN
    -- 获取提交记录和作业信息
    SELECT s.*, a.ai_grading_enabled, a.assignment_type_id, at.ai_evaluation_enabled
    INTO submission_record
    FROM public.ak_assignment_submissions s
    JOIN public.ak_assignments a ON s.assignment_id = a.id
    LEFT JOIN public.ak_assignment_types at ON a.assignment_type_id = at.id
    WHERE s.id = p_submission_id;
    
    -- 检查是否启用AI评估
    IF NOT submission_record.ai_grading_enabled OR NOT submission_record.ai_evaluation_enabled THEN
        RETURN NULL;
    END IF;
    
    -- 创建AI评估记录
    INSERT INTO public.ak_ai_evaluations (
        submission_id,
        evaluation_type,
        ai_model,
        confidence_score,
        evaluation_data,
        suggested_score,
        feedback_text
    ) VALUES (
        p_submission_id,
        'automated',
        'default_model_v1',
        0.85, -- 示例置信度
        '{"processing": "queued"}',
        null,
        'AI评估正在处理中...'
    ) RETURNING id INTO evaluation_id;
    
    -- 这里可以触发实际的AI评估服务
    -- PERFORM pg_notify('ai_evaluation_request', json_build_object('evaluation_id', evaluation_id)::text);
    
    RETURN evaluation_id;
END;
$$ LANGUAGE plpgsql;

-- 自动触发AI评估的触发器
CREATE OR REPLACE FUNCTION public.auto_trigger_ai_evaluation()
RETURNS TRIGGER AS $$
BEGIN
    -- 当作业提交时，自动触发AI评估
    PERFORM public.trigger_ai_evaluation(NEW.id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 在作业提交表上创建触发器
DROP TRIGGER IF EXISTS trigger_auto_ai_evaluation ON public.ak_assignment_submissions;
CREATE TRIGGER trigger_auto_ai_evaluation
    AFTER INSERT ON public.ak_assignment_submissions
    FOR EACH ROW
    EXECUTE FUNCTION public.auto_trigger_ai_evaluation();

-- 计算学习分析数据的函数
CREATE OR REPLACE FUNCTION public.calculate_learning_analytics(
    p_user_id uuid,
    p_period_start DATE,
    p_period_end DATE
) RETURNS uuid AS $$
DECLARE
    analytics_id uuid;
    user_class_id uuid;
    assignment_stats RECORD;
    health_data JSONB;
    skill_data JSONB;
BEGIN
    -- 获取用户的班级
    SELECT class_id INTO user_class_id 
    FROM public.ak_users 
    WHERE id = p_user_id;
    
    -- 计算作业统计
    SELECT 
        COUNT(*) as total_assignments,
        COUNT(s.id) as completed_assignments,
        AVG(s.final_score) as avg_score
    INTO assignment_stats
    FROM public.ak_assignments a
    LEFT JOIN public.ak_assignment_submissions s ON a.id = s.assignment_id AND s.student_id = p_user_id
    WHERE a.class_id = user_class_id
    AND a.created_at::date BETWEEN p_period_start AND p_period_end;
    
    -- 获取健康指标数据
    SELECT json_build_object(
        'avg_heart_rate', AVG(avg_heart_rate),
        'total_distance', SUM(distance_km),
        'total_calories', SUM(calories),
        'total_steps', SUM(steps)
    ) INTO health_data
    FROM public.ak_training_records
    WHERE user_id = p_user_id
    AND start_time::date BETWEEN p_period_start AND p_period_end;
    
    -- 技能进步数据（示例）
    skill_data := json_build_object(
        'endurance', COALESCE(assignment_stats.avg_score * 0.8, 0),
        'strength', COALESCE(assignment_stats.avg_score * 0.9, 0),
        'technique', COALESCE(assignment_stats.avg_score * 0.85, 0)
    );
    
    -- 插入或更新学习分析记录
    INSERT INTO public.ak_learning_analytics (
        user_id,
        class_id,
        analysis_period_start,
        analysis_period_end,
        total_assignments,
        completed_assignments,
        avg_score,
        improvement_rate,
        attendance_rate,
        effort_score,
        skill_progression,
        health_indicators
    ) VALUES (
        p_user_id,
        user_class_id,
        p_period_start,
        p_period_end,
        COALESCE(assignment_stats.total_assignments, 0),
        COALESCE(assignment_stats.completed_assignments, 0),
        assignment_stats.avg_score,
        -- 简化的改进率计算
        CASE 
            WHEN assignment_stats.total_assignments > 0 
            THEN (assignment_stats.completed_assignments::float / assignment_stats.total_assignments * 100)
            ELSE 0 
        END,
        -- 简化的参与率计算
        CASE 
            WHEN assignment_stats.total_assignments > 0 
            THEN (assignment_stats.completed_assignments::float / assignment_stats.total_assignments * 100)
            ELSE 0 
        END,
        COALESCE(assignment_stats.avg_score, 0),
        skill_data,
        health_data
    ) ON CONFLICT (user_id, analysis_period_start, analysis_period_end)
    DO UPDATE SET
        total_assignments = EXCLUDED.total_assignments,
        completed_assignments = EXCLUDED.completed_assignments,
        avg_score = EXCLUDED.avg_score,
        improvement_rate = EXCLUDED.improvement_rate,
        attendance_rate = EXCLUDED.attendance_rate,
        effort_score = EXCLUDED.effort_score,
        skill_progression = EXCLUDED.skill_progression,
        health_indicators = EXCLUDED.health_indicators,
        updated_at = now()
    RETURNING id INTO analytics_id;
    
    RETURN analytics_id;
END;
$$ LANGUAGE plpgsql;

-- 数据质量检查函数
CREATE OR REPLACE FUNCTION public.check_data_quality()
RETURNS void AS $$
DECLARE
    issue_count INT;
BEGIN
    -- 检查缺失的作业评分
    INSERT INTO public.ak_data_quality_logs (
        table_name, record_id, quality_check_type, issue_type, description
    )
    SELECT 
        'ak_assignment_submissions',
        id,
        'missing_score',
        'data_completeness',
        '作业提交缺少评分'
    FROM public.ak_assignment_submissions
    WHERE status = 'submitted' 
    AND created_at < now() - INTERVAL '7 days'
    AND (final_score IS NULL OR final_score = 0)
    AND NOT EXISTS (
        SELECT 1 FROM public.ak_data_quality_logs dql 
        WHERE dql.record_id = ak_assignment_submissions.id 
        AND dql.quality_check_type = 'missing_score'
        AND dql.resolved = false
    );
    
    -- 检查异常的训练数据
    INSERT INTO public.ak_data_quality_logs (
        table_name, record_id, quality_check_type, issue_type, description, severity
    )
    SELECT 
        'ak_training_records',
        id,
        'abnormal_values',
        'data_accuracy',
        '训练记录包含异常数值',
        'high'
    FROM public.ak_training_records
    WHERE (max_heart_rate > 220 OR max_heart_rate < 60)
    OR (distance_km > 50 OR distance_km < 0)
    OR (calories > 5000 OR calories < 0)
    AND NOT EXISTS (
        SELECT 1 FROM public.ak_data_quality_logs dql 
        WHERE dql.record_id = ak_training_records.id 
        AND dql.quality_check_type = 'abnormal_values'
        AND dql.resolved = false
    );
    
END;
$$ LANGUAGE plpgsql;

-- 创建定期执行数据质量检查的任务（需要pg_cron扩展）
-- SELECT cron.schedule('data-quality-check', '0 2 * * *', 'SELECT public.check_data_quality();');

-- 增加新的视图以便于数据分析
CREATE OR REPLACE VIEW public.vw_assignment_analytics AS
SELECT 
    a.id as assignment_id,
    a.title,
    a.class_id,
    c.name as class_name,
    g.name as grade_name,
    s.name as school_name,
    a.teacher_id,
    ut.username as teacher_name,
    a.workflow_status,
    a.created_at as assignment_created,
    a.due_date,
    COUNT(sub.id) as total_submissions,
    COUNT(CASE WHEN sub.status = 'submitted' THEN 1 END) as submitted_count,
    COUNT(CASE WHEN sub.review_status = 'reviewed' THEN 1 END) as reviewed_count,
    AVG(sub.final_score) as avg_score,
    COUNT(CASE WHEN ai_eval.id IS NOT NULL THEN 1 END) as ai_evaluated_count
FROM public.ak_assignments a
LEFT JOIN public.ak_classes c ON a.class_id = c.id
LEFT JOIN public.ak_grades g ON c.grade_id = g.id
LEFT JOIN public.ak_schools s ON g.school_id = s.id
LEFT JOIN public.ak_users ut ON a.teacher_id = ut.id
LEFT JOIN public.ak_assignment_submissions sub ON a.id = sub.assignment_id
LEFT JOIN public.ak_ai_evaluations ai_eval ON sub.id = ai_eval.submission_id
GROUP BY a.id, a.title, a.class_id, c.name, g.name, s.name, a.teacher_id, ut.username, a.workflow_status, a.created_at, a.due_date;

COMMENT ON VIEW public.vw_assignment_analytics IS '作业分析视图';

-- 学生表现综合视图
CREATE OR REPLACE VIEW public.vw_student_performance AS
SELECT 
    u.id as student_id,
    u.username,
    u.class_id,
    c.name as class_name,
    g.name as grade_name,
    la.total_assignments,
    la.completed_assignments,
    la.avg_score,
    la.improvement_rate,
    la.attendance_rate,
    la.skill_progression,
    la.health_indicators,
    COUNT(ats.id) as ai_suggestions_count,
    COUNT(CASE WHEN ats.priority = 'high' THEN 1 END) as high_priority_suggestions
FROM public.ak_users u
LEFT JOIN public.ak_classes c ON u.class_id = c.id
LEFT JOIN public.ak_grades g ON c.grade_id = g.id
LEFT JOIN public.ak_learning_analytics la ON u.id = la.user_id 
    AND la.analysis_period_end = (
        SELECT MAX(analysis_period_end) 
        FROM public.ak_learning_analytics la2 
        WHERE la2.user_id = u.id
    )
LEFT JOIN public.ak_ai_training_suggestions ats ON u.id = ats.user_id 
    AND ats.acknowledged = false
WHERE u.role = 'student'
GROUP BY u.id, u.username, u.class_id, c.name, g.name, la.total_assignments, 
         la.completed_assignments, la.avg_score, la.improvement_rate, 
         la.attendance_rate, la.skill_progression, la.health_indicators;

COMMENT ON VIEW public.vw_student_performance IS '学生表现综合视图';

-- 为教师角色分配新权限
INSERT INTO public.ak_role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM public.ak_roles r, public.ak_permissions p
WHERE r.name = 'teacher' AND p.code IN (
    'teacher.assignment.ai_config',
    'teacher.assignment.workflow', 
    'teacher.assignment.templates',
    'teacher.ai_evaluation.review',
    'teacher.ai_suggestions.manage',
    'teacher.analytics.class',
    'teacher.training_plans.ai_assist'
);

-- 为学校管理员分配新权限
INSERT INTO public.ak_role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM public.ak_roles r, public.ak_permissions p
WHERE r.name = 'school_admin' AND p.code IN (
    'school_admin.analytics.school'
);

-- 为系统管理员分配所有新权限
INSERT INTO public.ak_role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM public.ak_roles r, public.ak_permissions p
WHERE r.name = 'system_admin' AND p.code LIKE 'admin.data_quality.%';

-- 创建示例数据生成函数（用于测试）
CREATE OR REPLACE FUNCTION public.generate_sample_data()
RETURNS void AS $$
BEGIN
    -- 这里可以添加生成示例数据的逻辑
    -- 用于演示和测试目的
    RAISE NOTICE '示例数据生成功能预留，可在实际部署时实现';
END;
$$ LANGUAGE plpgsql;