package com.rongyichuang.carousel;

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

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

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

    @Autowired
    private DataSource dataSource;

    @Test
    public void testCarouselTableSchema() {
        try (Connection connection = dataSource.getConnection()) {
            System.out.println("=== 数据库连接成功 ===");
            System.out.println("数据库URL: " + connection.getMetaData().getURL());
            System.out.println("数据库产品: " + connection.getMetaData().getDatabaseProductName());
            System.out.println("数据库版本: " + connection.getMetaData().getDatabaseProductVersion());
            
            // 检查表是否存在
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet tables = metaData.getTables(null, null, "t_carousel", null);
            
            if (tables.next()) {
                System.out.println("\n=== t_carousel 表存在 ===");
                
                // 获取表结构
                ResultSet columns = metaData.getColumns(null, null, "t_carousel", null);
                System.out.println("\n=== 表字段信息 ===");
                System.out.printf("%-20s %-15s %-10s %-10s %-15s%n", 
                    "字段名", "数据类型", "长度", "可空", "默认值");
                System.out.println("=".repeat(80));
                
                List<String> actualColumns = new ArrayList<>();
                while (columns.next()) {
                    String columnName = columns.getString("COLUMN_NAME");
                    String dataType = columns.getString("TYPE_NAME");
                    int columnSize = columns.getInt("COLUMN_SIZE");
                    String nullable = columns.getString("IS_NULLABLE");
                    String defaultValue = columns.getString("COLUMN_DEF");
                    
                    actualColumns.add(columnName);
                    System.out.printf("%-20s %-15s %-10d %-10s %-15s%n", 
                        columnName, dataType, columnSize, nullable, 
                        defaultValue != null ? defaultValue : "NULL");
                }
                
                // 对比实体类期望的字段
                System.out.println("\n=== 实体类字段对比 ===");
                String[] expectedColumns = {
                    "id", "title", "content", "sort_order", "state", 
                    "create_time", "update_time", "create_by", "update_by"
                };
                
                for (String expected : expectedColumns) {
                    if (actualColumns.contains(expected)) {
                        System.out.println("✓ " + expected + " - 存在");
                    } else {
                        System.out.println("✗ " + expected + " - 缺失");
                    }
                }
                
                // 检查是否有多余字段
                System.out.println("\n=== 多余字段检查 ===");
                for (String actual : actualColumns) {
                    boolean found = false;
                    for (String expected : expectedColumns) {
                        if (expected.equals(actual)) {
                            found = true;
                            break;
                        }
                    }
                    if (!found) {
                        System.out.println("! " + actual + " - 实体类中未定义");
                    }
                }
                
            } else {
                System.out.println("\n=== t_carousel 表不存在 ===");
                
                // 列出所有表
                System.out.println("\n=== 数据库中的所有表 ===");
                ResultSet allTables = metaData.getTables(null, null, "%", new String[]{"TABLE"});
                while (allTables.next()) {
                    String tableName = allTables.getString("TABLE_NAME");
                    if (tableName.toLowerCase().contains("carousel") || 
                        tableName.toLowerCase().contains("news") ||
                        tableName.toLowerCase().contains("banner")) {
                        System.out.println("- " + tableName);
                    }
                }
            }
            
        } catch (SQLException e) {
            System.err.println("数据库连接或查询失败: " + e.getMessage());
            e.printStackTrace();
        }
    }
    
    @Test
    public void testSimpleQuery() {
        try (Connection connection = dataSource.getConnection()) {
            // 尝试简单查询
            String sql = "SELECT COUNT(*) as count FROM t_carousel WHERE state = 1";
            try (PreparedStatement stmt = connection.prepareStatement(sql);
                 ResultSet rs = stmt.executeQuery()) {
                
                if (rs.next()) {
                    int count = rs.getInt("count");
                    System.out.println("t_carousel 表中有效记录数: " + count);
                }
            }
        } catch (SQLException e) {
            System.err.println("查询失败: " + e.getMessage());
            
            // 如果查询失败，尝试不带条件的查询
            try (Connection connection = dataSource.getConnection()) {
                String sql = "SELECT COUNT(*) as count FROM t_carousel";
                try (PreparedStatement stmt = connection.prepareStatement(sql);
                     ResultSet rs = stmt.executeQuery()) {
                    
                    if (rs.next()) {
                        int count = rs.getInt("count");
                        System.out.println("t_carousel 表总记录数: " + count);
                    }
                }
            } catch (SQLException e2) {
                System.err.println("简单查询也失败: " + e2.getMessage());
            }
        }
    }
}