package com.rongyichuang.judge;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ActiveProfiles;

import java.io.File;
import java.util.List;
import java.util.Map;

import static org.junit.jupiter.api.Assertions.*;

@SpringBootTest
@ActiveProfiles("test")
public class JudgeModuleCompleteTest {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    public void testCompleteJudgeModuleFlow() throws Exception {
        System.out.println("=== 开始完整的评委模块测试 ===");
        
        // 1. 验证logo图片文件存在
        System.out.println("步骤1: 验证头像图片文件");
        File logoFile = findLogoFile();
        assertNotNull(logoFile, "应该找到logo图片文件");
        System.out.println("✓ 找到头像图片: " + logoFile.getAbsolutePath());
        
        // 2. 模拟前端上传头像，保存到t_media表
        System.out.println("步骤2: 保存头像媒体信息");
        Long mediaId = saveAvatarMedia();
        assertNotNull(mediaId, "头像媒体信息保存应该成功");
        System.out.println("✓ 头像媒体信息保存成功，ID: " + mediaId);
        
        // 3. 创建评委信息，保存到t_judge表
        System.out.println("步骤3: 创建评委信息");
        Long judgeId = createJudge();
        assertNotNull(judgeId, "评委创建应该成功");
        System.out.println("✓ 评委创建成功，ID: " + judgeId);
        
        // 4. 创建标签并关联到评委
        System.out.println("步骤4: 创建标签并关联");
        createTagsAndAssociate(judgeId);
        System.out.println("✓ 标签创建和关联成功");
        
        // 5. 更新媒体记录的关联信息
        System.out.println("步骤5: 更新媒体关联信息");
        updateMediaAssociation(mediaId, judgeId);
        System.out.println("✓ 媒体关联信息更新成功");
        
        // 6. 验证所有数据库数据的正确性
        System.out.println("步骤6: 验证数据库数据");
        verifyAllDatabaseData(mediaId, judgeId);
        
        System.out.println("=== 完整的评委模块测试成功 ===");
    }

    private File findLogoFile() {
        String[] possiblePaths = {
            "../UI/logo.jpg",
            "UI/logo.jpg",
            "../UI/logo.png",
            "UI/logo.png"
        };
        
        for (String path : possiblePaths) {
            File file = new File(path);
            if (file.exists()) {
                return file;
            }
        }
        return null;
    }

    private Long saveAvatarMedia() {
        // 模拟文件上传到COS后的路径
        String cosPath = "avatars/judge_avatar_" + System.currentTimeMillis() + ".jpg";
        
        String sql = """
            INSERT INTO t_media (name, path, file_size, file_ext, media_type, target_type, target_id, state) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            """;
        
        jdbcTemplate.update(sql, 
            "judge_avatar.jpg",     // name
            cosPath,                // path
            1024,                   // file_size
            "jpg",                  // file_ext
            1,                      // media_type (1=图片)
            1,                      // target_type (1=评委头像)
            0L,                     // target_id (临时值，后续更新)
            1                       // state
        );
        
        // 获取刚插入的ID
        String selectSql = "SELECT id FROM t_media WHERE path = ? ORDER BY id DESC LIMIT 1";
        return jdbcTemplate.queryForObject(selectSql, Long.class, cosPath);
    }

    private Long createJudge() {
        String sql = """
            INSERT INTO t_judge (name, phone, gender, description, state) 
            VALUES (?, ?, ?, ?, ?)
            """;
        
        jdbcTemplate.update(sql, 
            "张三",                                    // name
            "13800138000",                           // phone
            1,                                       // gender (1=男)
            "拥有10年以上的技术开发经验，专注于云计算和大数据领域。", // description
            1                                        // state (1=正常)
        );
        
        // 获取刚插入的ID
        String selectSql = "SELECT id FROM t_judge WHERE phone = ? ORDER BY id DESC LIMIT 1";
        return jdbcTemplate.queryForObject(selectSql, Long.class, "13800138000");
    }

    private void createTagsAndAssociate(Long judgeId) {
        String[] tagNames = {"云计算", "大数据", "架构设计"};
        
        for (String tagName : tagNames) {
            // 检查标签是否已存在
            String checkSql = "SELECT COUNT(*) FROM t_tag WHERE name = ?";
            int count = jdbcTemplate.queryForObject(checkSql, Integer.class, tagName);
            
            Long tagId;
            if (count == 0) {
                // 创建新标签
                String insertTagSql = """
                    INSERT INTO t_tag (name, code, category, state) 
                    VALUES (?, ?, ?, ?)
                    """;
                
                jdbcTemplate.update(insertTagSql, 
                    tagName,                    // name
                    tagName.toLowerCase(),      // code
                    "技术",                     // category
                    1                          // state
                );
                
                // 获取标签ID
                String selectTagSql = "SELECT id FROM t_tag WHERE name = ? ORDER BY id DESC LIMIT 1";
                tagId = jdbcTemplate.queryForObject(selectTagSql, Long.class, tagName);
            } else {
                // 获取已存在的标签ID
                String selectTagSql = "SELECT id FROM t_tag WHERE name = ?";
                tagId = jdbcTemplate.queryForObject(selectTagSql, Long.class, tagName);
            }
            
            // 关联评委和标签
            String associateSql = """
                INSERT INTO t_judge_tag (judge_id, tag_id, state) 
                VALUES (?, ?, ?)
                """;
            
            jdbcTemplate.update(associateSql, judgeId, tagId, 1);
        }
    }

    private void updateMediaAssociation(Long mediaId, Long judgeId) {
        String sql = "UPDATE t_media SET target_id = ? WHERE id = ?";
        int updated = jdbcTemplate.update(sql, judgeId, mediaId);
        assertEquals(1, updated, "应该更新一条媒体记录");
    }

    private void verifyAllDatabaseData(Long mediaId, Long judgeId) {
        System.out.println("=== 验证数据库数据 ===");
        
        // 验证t_media表数据
        System.out.println("验证t_media表数据...");
        String mediaSql = "SELECT id, name, path, target_type, target_id, media_type, file_size, file_ext FROM t_media WHERE id = ?";
        Map<String, Object> mediaRecord = jdbcTemplate.queryForMap(mediaSql, mediaId);
        
        assertNotNull(mediaRecord, "应该找到媒体记录");
        assertEquals("judge_avatar.jpg", mediaRecord.get("name"), "媒体文件名应该正确");
        assertTrue(mediaRecord.get("path").toString().contains("avatars/"), "媒体路径应该包含avatars目录");
        assertEquals(1, mediaRecord.get("target_type"), "target_type应该是1（评委头像）");
        assertEquals(judgeId.longValue(), ((Number)mediaRecord.get("target_id")).longValue(), "target_id应该是评委ID");
        assertEquals(1, mediaRecord.get("media_type"), "media_type应该是1（图片）");
        assertEquals(1024, mediaRecord.get("file_size"), "文件大小应该正确");
        assertEquals("jpg", mediaRecord.get("file_ext"), "文件扩展名应该正确");
        
        System.out.println("✓ t_media表数据验证通过");
        System.out.println("  - 文件名: " + mediaRecord.get("name"));
        System.out.println("  - 路径: " + mediaRecord.get("path"));
        System.out.println("  - 目标类型: " + mediaRecord.get("target_type"));
        System.out.println("  - 目标ID: " + mediaRecord.get("target_id"));
        System.out.println("  - 媒体类型: " + mediaRecord.get("media_type"));
        
        // 验证t_judge表数据
        System.out.println("验证t_judge表数据...");
        String judgeSql = "SELECT id, name, phone, gender, description, state FROM t_judge WHERE id = ?";
        Map<String, Object> judgeRecord = jdbcTemplate.queryForMap(judgeSql, judgeId);
        
        assertNotNull(judgeRecord, "应该找到评委记录");
        assertEquals("张三", judgeRecord.get("name"), "评委姓名应该正确");
        assertEquals("13800138000", judgeRecord.get("phone"), "评委电话应该正确");
        assertEquals(1, judgeRecord.get("gender"), "评委性别应该正确");
        assertTrue(judgeRecord.get("description").toString().contains("10年以上"), "评委介绍应该正确");
        assertEquals(1, judgeRecord.get("state"), "评委状态应该正确");
        
        System.out.println("✓ t_judge表数据验证通过");
        System.out.println("  - 姓名: " + judgeRecord.get("name"));
        System.out.println("  - 电话: " + judgeRecord.get("phone"));
        System.out.println("  - 性别: " + judgeRecord.get("gender"));
        System.out.println("  - 状态: " + judgeRecord.get("state"));
        
        // 验证t_judge_tag表数据
        System.out.println("验证t_judge_tag表数据...");
        String tagSql = """
            SELECT jt.judge_id, t.name as tag_name, t.category 
            FROM t_judge_tag jt 
            JOIN t_tag t ON jt.tag_id = t.id 
            WHERE jt.judge_id = ? AND jt.state = 1
            """;
        List<Map<String, Object>> tagRecords = jdbcTemplate.queryForList(tagSql, judgeId);
        
        assertFalse(tagRecords.isEmpty(), "应该找到评委标签记录");
        assertEquals(3, tagRecords.size(), "应该有3个标签");
        
        System.out.println("✓ t_judge_tag表数据验证通过");
        for (Map<String, Object> tagRecord : tagRecords) {
            System.out.println("  - 标签: " + tagRecord.get("tag_name") + " (分类: " + tagRecord.get("category") + ")");
        }
        
        // 验证数据关联的正确性
        System.out.println("验证数据关联...");
        String associationSql = """
            SELECT 
                j.name as judge_name,
                m.name as media_name,
                m.path as media_path,
                COUNT(jt.tag_id) as tag_count
            FROM t_judge j
            LEFT JOIN t_media m ON m.target_id = j.id AND m.target_type = 1
            LEFT JOIN t_judge_tag jt ON jt.judge_id = j.id AND jt.state = 1
            WHERE j.id = ?
            GROUP BY j.id, m.id
            """;
        
        Map<String, Object> associationRecord = jdbcTemplate.queryForMap(associationSql, judgeId);
        
        assertEquals("张三", associationRecord.get("judge_name"), "评委姓名应该正确");
        assertEquals("judge_avatar.jpg", associationRecord.get("media_name"), "关联的媒体文件名应该正确");
        assertTrue(associationRecord.get("media_path").toString().contains("avatars/"), "关联的媒体路径应该正确");
        assertEquals(3L, ((Number)associationRecord.get("tag_count")).longValue(), "应该关联3个标签");
        
        System.out.println("✓ 数据关联验证通过");
        System.out.println("  - 评委: " + associationRecord.get("judge_name"));
        System.out.println("  - 头像: " + associationRecord.get("media_name"));
        System.out.println("  - 标签数量: " + associationRecord.get("tag_count"));
        
        System.out.println("=== 所有数据库数据验证通过 ===");
    }
}