package com.rongyichuang.region;

import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

@SpringBootTest
public class RegionDataInsertTest {

    @Test
    public void insertRegionData() {
        // 使用应用配置的数据库连接信息
        String url = "jdbc:mysql://139.155.104.10:3306/ryc?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
        String username = "ryc";
        String password = "KiYap3E8X8RLcM6T";

        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            System.out.println("=== 开始插入区域数据 ===");
            
            // 先查看表结构
            System.out.println("=== t_region 表结构 ===");
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet columns = metaData.getColumns(null, null, "t_region", null);
            
            while (columns.next()) {
                String columnName = columns.getString("COLUMN_NAME");
                String columnType = columns.getString("TYPE_NAME");
                System.out.printf("%-15s %s%n", columnName, columnType);
            }
            
            // 清空现有数据（可选）
            // connection.createStatement().executeUpdate("DELETE FROM t_region");
            
            String currentTime = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
            
            // 插入省级数据
            insertProvince(connection, "四川省", currentTime);
            insertProvince(connection, "重庆市", currentTime);
            
            // 获取四川省ID
            Long sichuanId = getRegionIdByName(connection, "四川省");
            
            // 插入市级数据
            insertCity(connection, "成都市", sichuanId, currentTime);
            insertCity(connection, "眉山市", sichuanId, currentTime);
            insertCity(connection, "德阳市", sichuanId, currentTime);
            
            // 获取成都市ID
            Long chengduId = getRegionIdByName(connection, "成都市");
            
            // 插入成都区县数据（根据图片内容）
            String[] chengduDistricts = {
                "天府新区", "东部新区", "高新", "锦江", "青羊", "金牛", "武侯", "成华",
                "龙泉", "青白江", "新都", "温江", "双流", "郫都", "简阳", "都江堰",
                "彭州", "邛崃", "崇州", "金堂", "新津", "大邑", "蒲江"
            };
            
            for (String district : chengduDistricts) {
                insertDistrict(connection, district, chengduId, currentTime);
            }
            
            // 查看插入结果
            System.out.println("\n=== 插入结果 ===");
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT id, name, pid, create_time FROM t_region ORDER BY pid, id");
            
            while (rs.next()) {
                System.out.printf("ID: %d, Name: %s, PID: %s, CreateTime: %s%n",
                    rs.getLong("id"),
                    rs.getString("name"),
                    rs.getObject("pid"),
                    rs.getString("create_time")
                );
            }
            
            System.out.println("=== 数据插入完成 ===");
            
        } catch (SQLException e) {
            System.err.println("数据库操作失败: " + e.getMessage());
            e.printStackTrace();
        }
    }
    
    private void insertProvince(Connection connection, String name, String createTime) throws SQLException {
        String sql = "INSERT INTO t_region (name, pid, code, level, leaf_flag, state, create_time, update_time, create_user_id, update_user_id, version) VALUES (?, 0, NULL, 1, 0, 1, ?, ?, 1, 1, 0)";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setString(1, name);
            stmt.setString(2, createTime);
            stmt.setString(3, createTime);
            int result = stmt.executeUpdate();
            System.out.println("插入省级: " + name + " - " + (result > 0 ? "成功" : "失败"));
        }
    }
    
    private void insertCity(Connection connection, String name, Long parentId, String createTime) throws SQLException {
        String sql = "INSERT INTO t_region (name, pid, code, level, leaf_flag, state, create_time, update_time, create_user_id, update_user_id, version) VALUES (?, ?, NULL, 2, 0, 1, ?, ?, 1, 1, 0)";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setString(1, name);
            stmt.setLong(2, parentId);
            stmt.setString(3, createTime);
            stmt.setString(4, createTime);
            int result = stmt.executeUpdate();
            System.out.println("插入市级: " + name + " (parent: " + parentId + ") - " + (result > 0 ? "成功" : "失败"));
        }
    }
    
    private void insertDistrict(Connection connection, String name, Long parentId, String createTime) throws SQLException {
        String sql = "INSERT INTO t_region (name, pid, code, level, leaf_flag, state, create_time, update_time, create_user_id, update_user_id, version) VALUES (?, ?, NULL, 3, 1, 1, ?, ?, 1, 1, 0)";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setString(1, name);
            stmt.setLong(2, parentId);
            stmt.setString(3, createTime);
            stmt.setString(4, createTime);
            int result = stmt.executeUpdate();
            System.out.println("插入区县: " + name + " (parent: " + parentId + ") - " + (result > 0 ? "成功" : "失败"));
        }
    }
    
    private Long getRegionIdByName(Connection connection, String name) throws SQLException {
        String sql = "SELECT id FROM t_region WHERE name = ?";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setString(1, name);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getLong("id");
            }
        }
        return null;
    }
}