-- 简化版评分标准JSON迁移脚本
-- 适用于当前数据库结构 (title, sport_type, difficulty_level, is_active)
-- 执行日期：2025-06-11

-- 步骤1：创建备份表
CREATE TABLE IF NOT EXISTS ak_training_projects_backup_20250611 AS 
SELECT * FROM ak_training_projects;

-- 步骤2：检查备份是否成功
DO $$
DECLARE
    backup_count INTEGER;
    original_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO backup_count FROM ak_training_projects_backup_20250611;
    SELECT COUNT(*) INTO original_count FROM ak_training_projects;
    
    IF backup_count = original_count THEN
        RAISE NOTICE '备份成功: % 条记录已备份', backup_count;
    ELSE
        RAISE EXCEPTION '备份失败: 原表 % 条记录，备份表 % 条记录', original_count, backup_count;
    END IF;
END $$;

-- 步骤3：确保 scoring_criteria 字段为 JSONB 类型
DO $$
BEGIN
    -- 检查并转换字段类型
    IF EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'ak_training_projects' 
        AND column_name = 'scoring_criteria' 
        AND data_type != 'jsonb'
    ) THEN
        ALTER TABLE ak_training_projects 
        ALTER COLUMN scoring_criteria TYPE JSONB 
        USING CASE 
            WHEN scoring_criteria IS NULL OR scoring_criteria = '' THEN NULL
            WHEN scoring_criteria ~ '^[\s\t\n\r]*\{.*\}[\s\t\n\r]*$' THEN scoring_criteria::JSONB
            ELSE jsonb_build_object('legacy_text', scoring_criteria)
        END;
        
        RAISE NOTICE 'scoring_criteria 字段已转换为 JSONB 类型';
    ELSE
        RAISE NOTICE 'scoring_criteria 字段已经是 JSONB 类型';
    END IF;
END $$;

-- 步骤4：为没有评分标准的项目添加默认JSON结构
UPDATE ak_training_projects 
SET scoring_criteria = jsonb_build_object(
    'criteria', jsonb_build_array(
        jsonb_build_object(
            'min_score', 90,
            'max_score', 100,
            'description', '优秀：表现卓越，超出预期'
        ),
        jsonb_build_object(
            'min_score', 80,
            'max_score', 89,
            'description', '良好：表现良好，符合要求'
        ),
        jsonb_build_object(
            'min_score', 70,
            'max_score', 79,
            'description', '及格：基本达标，有待改进'
        ),
        jsonb_build_object(
            'min_score', 0,
            'max_score', 69,
            'description', '不及格：未达标准，需要重练'
        )
    ),
    'scoring_method', 'comprehensive',
    'weight_distribution', jsonb_build_object(
        'technique', 0.4,
        'effort', 0.3,
        'improvement', 0.3
    )
)
WHERE scoring_criteria IS NULL 
   OR scoring_criteria = '{}' 
   OR jsonb_typeof(scoring_criteria) != 'object'
   OR NOT (scoring_criteria ? 'criteria');

-- 步骤5：创建索引以提高查询性能
CREATE INDEX IF NOT EXISTS idx_ak_training_projects_scoring_criteria 
ON ak_training_projects USING GIN (scoring_criteria);

-- 步骤6：验证迁移结果
DO $$
DECLARE
    total_projects INTEGER;
    projects_with_criteria INTEGER;
    projects_with_valid_structure INTEGER;
BEGIN
    -- 统计总数
    SELECT COUNT(*) INTO total_projects FROM ak_training_projects;
    
    -- 统计有评分标准的项目
    SELECT COUNT(*) INTO projects_with_criteria 
    FROM ak_training_projects 
    WHERE scoring_criteria IS NOT NULL;
    
    -- 统计有效JSON结构的项目
    SELECT COUNT(*) INTO projects_with_valid_structure
    FROM ak_training_projects 
    WHERE scoring_criteria ? 'criteria' 
      AND jsonb_typeof(scoring_criteria->'criteria') = 'array'
      AND jsonb_array_length(scoring_criteria->'criteria') > 0;
    
    RAISE NOTICE '=== 迁移验证结果 ===';
    RAISE NOTICE '总项目数: %', total_projects;
    RAISE NOTICE '有评分标准的项目: %', projects_with_criteria;
    RAISE NOTICE '有效JSON结构的项目: %', projects_with_valid_structure;
    
    IF projects_with_valid_structure = total_projects THEN
        RAISE NOTICE '✅ 迁移成功！所有项目都有有效的评分标准JSON结构';
    ELSE
        RAISE WARNING '⚠️  有 % 个项目的评分标准结构可能需要检查', 
                     total_projects - projects_with_valid_structure;
    END IF;
END $$;

-- 步骤7：显示示例数据
DO $$
DECLARE
    example_record RECORD;
BEGIN
    RAISE NOTICE '=== 示例数据 ===';
    
    FOR example_record IN 
        SELECT 
            title,
            scoring_criteria->'criteria' as criteria,
            jsonb_array_length(scoring_criteria->'criteria') as criteria_count
        FROM ak_training_projects 
        WHERE scoring_criteria IS NOT NULL
        LIMIT 3
    LOOP
        RAISE NOTICE '项目: %', example_record.title;
        RAISE NOTICE '  评分标准数量: %', example_record.criteria_count;
        RAISE NOTICE '  评分标准: %', example_record.criteria;
        RAISE NOTICE '---';
    END LOOP;
END $$;

-- 完成提示
SELECT '🎉 评分标准JSON结构化迁移完成！' as status;

-- 清理建议（可选执行）
-- DROP TABLE IF EXISTS ak_training_projects_backup_20250611;