-- ============================================
-- 初始化裁判-项目关联数据
-- 用于解决"您没有权限给该项目打分"的问题
-- ============================================

-- 说明：
-- 1. 这个脚本会为所有裁判分配所有项目的评分权限
-- 2. 如果需要更精细的权限控制，请根据实际情况修改
-- 3. 执行前请确保 martial_judge 和 martial_project 表中已有数据

-- 清空现有的裁判-项目关联（可选）
-- TRUNCATE TABLE martial_judge_project;

-- 方案1：为所有裁判分配所有项目（适用于测试环境）
INSERT INTO martial_judge_project (
    competition_id,
    judge_id,
    project_id,
    assign_time,
    status,
    is_deleted,
    create_time,
    update_time
)
SELECT
    j.competition_id,
    j.id AS judge_id,
    p.id AS project_id,
    NOW() AS assign_time,
    1 AS status,
    0 AS is_deleted,
    NOW() AS create_time,
    NOW() AS update_time
FROM martial_judge j
CROSS JOIN martial_project p
WHERE j.is_deleted = 0
  AND p.is_deleted = 0
  AND NOT EXISTS (
      SELECT 1 FROM martial_judge_project jp
      WHERE jp.judge_id = j.id
        AND jp.project_id = p.id
        AND jp.is_deleted = 0
  );

-- 方案2：为特定裁判分配特定项目（适用于生产环境）
-- 示例：为裁判ID=456分配项目ID=5的权限
/*
INSERT INTO martial_judge_project (
    competition_id,
    judge_id,
    project_id,
    assign_time,
    status,
    is_deleted,
    create_time,
    update_time
) VALUES (
    200,  -- 比赛ID
    456,  -- 裁判ID
    5,    -- 项目ID
    NOW(),
    1,
    0,
    NOW(),
    NOW()
);
*/

-- 验证数据
SELECT
    jp.id,
    j.name AS judge_name,
    p.project_name,
    jp.status,
    jp.assign_time
FROM martial_judge_project jp
LEFT JOIN martial_judge j ON jp.judge_id = j.id
LEFT JOIN martial_project p ON jp.project_id = p.id
WHERE jp.is_deleted = 0
ORDER BY jp.judge_id, jp.project_id;

-- 查看每个裁判分配的项目数量
SELECT
    j.id AS judge_id,
    j.name AS judge_name,
    COUNT(jp.id) AS project_count
FROM martial_judge j
LEFT JOIN martial_judge_project jp ON j.id = jp.judge_id AND jp.is_deleted = 0
WHERE j.is_deleted = 0
GROUP BY j.id, j.name
ORDER BY j.id;