-- 学生成就系统表结构
-- 用于记录学生获得的各种成就和徽章

-- 成就定义表
CREATE TABLE IF NOT EXISTS public.ak_achievements (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(128) NOT NULL UNIQUE, -- 成就名称 (添加唯一约束)
    description TEXT, -- 成就描述
    category VARCHAR(32) DEFAULT 'general', -- 成就分类 (training, skill, progress, persistence, special)
    difficulty VARCHAR(16) DEFAULT 'easy', -- 难度等级 (easy, medium, hard, expert)
    points INT DEFAULT 10, -- 奖励积分
    icon VARCHAR(16) DEFAULT '🏆', -- 成就图标
    requirements TEXT, -- 获得条件说明
    is_active BOOLEAN DEFAULT true, -- 是否启用
    sort_order INT DEFAULT 0, -- 排序顺序
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
COMMENT ON TABLE public.ak_achievements IS '成就定义表';

-- 学生成就记录表
CREATE TABLE IF NOT EXISTS public.ak_student_achievements (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    student_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 学生ID
    achievement_id uuid REFERENCES public.ak_achievements(id) ON DELETE CASCADE, -- 成就ID
    unlocked_at TIMESTAMP WITH TIME ZONE DEFAULT now(), -- 获得时间
    progress_current INT DEFAULT 0, -- 当前进度
    progress_target INT DEFAULT 1, -- 目标进度
    is_featured BOOLEAN DEFAULT false, -- 是否为精选成就
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
COMMENT ON TABLE public.ak_student_achievements IS '学生成就记录表';

-- 创建索引
CREATE INDEX IF NOT EXISTS idx_achievements_category ON public.ak_achievements(category);
CREATE INDEX IF NOT EXISTS idx_achievements_difficulty ON public.ak_achievements(difficulty);
CREATE INDEX IF NOT EXISTS idx_student_achievements_student_id ON public.ak_student_achievements(student_id);
CREATE INDEX IF NOT EXISTS idx_student_achievements_achievement_id ON public.ak_student_achievements(achievement_id);
CREATE INDEX IF NOT EXISTS idx_student_achievements_unlocked_at ON public.ak_student_achievements(unlocked_at);

-- 防止重复获得同一成就
ALTER TABLE public.ak_student_achievements 
ADD CONSTRAINT uk_student_achievement_unique UNIQUE (student_id, achievement_id);

-- 插入一些基础成就
INSERT INTO public.ak_achievements (name, description, category, difficulty, points, icon, requirements) VALUES
('初出茅庐', '完成第一次训练记录', 'training', 'easy', 10, '🎯', '完成任意一次训练记录'),
('坚持不懈', '连续训练7天', 'persistence', 'medium', 50, '🔥', '连续7天都有训练记录'),
('技术精进', '单项技能评分达到90分以上', 'skill', 'hard', 100, '⚡', '在任意技能项目中获得90分以上评分'),
('全能选手', '完成所有训练项目', 'training', 'expert', 200, '🏆', '完成系统中所有的训练项目'),
('进步之星', '单月内平均分提升10分以上', 'progress', 'medium', 75, '📈', '在一个月内平均成绩提升10分或以上'),
('月度之王', '成为月度训练积分第一名', 'special', 'expert', 300, '👑', '在月度排行榜中获得第一名')
ON CONFLICT (name) DO NOTHING;

-- 创建触发器函数，自动检查成就获得条件
CREATE OR REPLACE FUNCTION check_student_achievements()
RETURNS TRIGGER AS $$
DECLARE
    student_uuid uuid;
    achievement_uuid uuid;
    assignment_count INT;
    avg_score FLOAT;
    consecutive_days INT;
BEGIN
    -- 获取学生ID
    student_uuid := NEW.student_id;
    
    -- 检查"初出茅庐"成就 - 完成第一次训练记录
    SELECT id INTO achievement_uuid FROM public.ak_achievements WHERE name = '初出茅庐';
    IF achievement_uuid IS NOT NULL THEN
        SELECT COUNT(*) INTO assignment_count 
        FROM public.ak_assignment_submissions 
        WHERE student_id = student_uuid AND status = 'submitted';
        
        IF assignment_count = 1 THEN
            INSERT INTO public.ak_student_achievements (student_id, achievement_id)
            VALUES (student_uuid, achievement_uuid)
            ON CONFLICT (student_id, achievement_id) DO NOTHING;
        END IF;
    END IF;
    
    -- 检查"技术精进"成就 - 90分以上评分
    SELECT id INTO achievement_uuid FROM public.ak_achievements WHERE name = '技术精进';
    IF achievement_uuid IS NOT NULL AND NEW.final_score >= 90 THEN
        INSERT INTO public.ak_student_achievements (student_id, achievement_id)
        VALUES (student_uuid, achievement_uuid)
        ON CONFLICT (student_id, achievement_id) DO NOTHING;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
DROP TRIGGER IF EXISTS trigger_check_achievements ON public.ak_assignment_submissions;
CREATE TRIGGER trigger_check_achievements
    AFTER INSERT OR UPDATE ON public.ak_assignment_submissions
    FOR EACH ROW
    EXECUTE FUNCTION check_student_achievements();

-- 创建获取用户成就统计的函数
CREATE OR REPLACE FUNCTION get_student_achievement_stats(p_student_id uuid)
RETURNS TABLE(
    total_achievements INT,
    total_points INT,
    completion_rate FLOAT,
    recent_achievements_count INT
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        COUNT(sa.id)::INT as total_achievements,
        COALESCE(SUM(a.points), 0)::INT as total_points,
        CASE 
            WHEN COUNT(DISTINCT a_all.id) > 0 
            THEN (COUNT(sa.id)::FLOAT / COUNT(DISTINCT a_all.id) * 100)
            ELSE 0.0
        END as completion_rate,
        COUNT(CASE WHEN sa.unlocked_at >= NOW() - INTERVAL '7 days' THEN 1 END)::INT as recent_achievements_count
    FROM public.ak_achievements a_all
    LEFT JOIN public.ak_student_achievements sa ON a_all.id = sa.achievement_id AND sa.student_id = p_student_id
    LEFT JOIN public.ak_achievements a ON sa.achievement_id = a.id
    WHERE a_all.is_active = true;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;