import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.SQLException;
import java.nio.charset.StandardCharsets;

public class FixChineseEncoding {
    public static void main(String[] args) {
        // Database connection information with charset parameter
        String url = "jdbc:dm://192.168.0.145:30236?charset=utf8&client_encoding=utf8"; // Explicit charset settings
        String username = "WATERSHED";
        String password = "WaterShed./1224";
        
        Connection conn = null;
        Statement stmt = null;
        
        try {
            // Load driver
            Class.forName("dm.jdbc.driver.DmDriver");
            
            // Establish connection
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(url, username, password);
            stmt = conn.createStatement();
            
            // Ensure connection uses UTF-8
            stmt.executeUpdate("SET CLIENT_ENCODING TO 'UTF8'");
            
            // Drop existing table if it exists
            System.out.println("Dropping existing table...");
            stmt.executeUpdate("DROP TABLE IF EXISTS \"PROJECT\"");
            
            // Create new table with explicit charset support
            System.out.println("Creating new table with UTF-8 support...");
            String createTableSql = "CREATE TABLE \"PROJECT\" (
                \"project_id\" BIGINT NOT NULL IDENTITY(1,1),
                \"project_name\" VARCHAR(100) NOT NULL,
                \"project_category\" VARCHAR(50) NOT NULL,
                \"project_subcategory\" VARCHAR(50) NOT NULL,
                \"project_description\" VARCHAR(500) DEFAULT NULL,
                \"project_url\" VARCHAR(255) DEFAULT NULL,
                \"status\" CHAR(1) NOT NULL DEFAULT '0',
                \"create_by\" VARCHAR(64) DEFAULT '',
                \"create_time\" DATETIME,
                \"update_by\" VARCHAR(64) DEFAULT '',
                \"update_time\" DATETIME,
                \"remark\" VARCHAR(500) DEFAULT NULL,
                PRIMARY KEY (\"project_id\")
            )";
            stmt.executeUpdate(createTableSql);
            
            // Add constraints
            System.out.println("Adding constraints...");
            stmt.executeUpdate("ALTER TABLE \"PROJECT\" ADD CONSTRAINT \"project_check_1\" CHECK (\"status\" IN ('0','1','2'))");
            
            // Add Chinese comments directly in code to avoid file encoding issues
            System.out.println("Adding Chinese comments...");
            stmt.executeUpdate("COMMENT ON TABLE \"PROJECT\" IS '项目表'");
            stmt.executeUpdate("COMMENT ON COLUMN \"PROJECT\".\"project_id\" IS '项目ID'");
            stmt.executeUpdate("COMMENT ON COLUMN \"PROJECT\".\"project_name\" IS '项目名称'");
            stmt.executeUpdate("COMMENT ON COLUMN \"PROJECT\".\"project_category\" IS '项目分类（数字孪生、水利信息化等）'");
            stmt.executeUpdate("COMMENT ON COLUMN \"PROJECT\".\"project_subcategory\" IS '项目子类（数字孪生太浦河、数字孪生水文站等）'");
            stmt.executeUpdate("COMMENT ON COLUMN \"PROJECT\".\"project_description\" IS '项目描述'");
            stmt.executeUpdate("COMMENT ON COLUMN \"PROJECT\".\"project_url\" IS '项目访问URL'");
            stmt.executeUpdate("COMMENT ON COLUMN \"PROJECT\".\"status\" IS '项目状态（0正常 1维护中 2已删除）'");
            stmt.executeUpdate("COMMENT ON COLUMN \"PROJECT\".\"create_by\" IS '创建者'");
            stmt.executeUpdate("COMMENT ON COLUMN \"PROJECT\".\"create_time\" IS '创建时间'");
            stmt.executeUpdate("COMMENT ON COLUMN \"PROJECT\".\"update_by\" IS '更新者'");
            stmt.executeUpdate("COMMENT ON COLUMN \"PROJECT\".\"update_time\" IS '更新时间'");
            stmt.executeUpdate("COMMENT ON COLUMN \"PROJECT\".\"remark\" IS '备注'");
            
            // Insert data with Chinese characters directly in code
            System.out.println("Inserting data with Chinese characters...");
            
            // Insert statement for each project
            String[] insertStatements = {
                "INSERT INTO \"PROJECT\" (\"project_name\", \"project_category\", \"project_subcategory\", \"project_description\", \"status\", \"create_by\", \"create_time\", \"update_by\", \"update_time\") VALUES
                ('数字孪生太浦河', '数字孪生', '数字孪生太浦河', '基于数字孪生技术的太浦河流域综合管理平台，实现河流监测、模拟预测与智能调度', '0', 'admin', '2024-06-15 00:00:00', 'admin', '2024-06-15 00:00:00')",
                
                "INSERT INTO \"PROJECT\" (\"project_name\", \"project_category\", \"project_subcategory\", \"project_description\", \"status\", \"create_by\", \"create_time\", \"update_by\", \"update_time\") VALUES
                ('数字孪生太浦闸', '数字孪生', '数字孪生太浦河', '基于数字孪生技术的太浦闸智能监控系统，实现闸门运行状态实时监测与调度控制', '0', 'admin', '2024-06-20 00:00:00', 'admin', '2024-06-20 00:00:00')",
                
                "INSERT INTO \"PROJECT\" (\"project_name\", \"project_category\", \"project_subcategory\", \"project_description\", \"status\", \"create_by\", \"create_time\", \"update_by\", \"update_time\") VALUES
                ('贡湖实验站', '数字孪生', '数字孪生水文站', '实现水文监测站点的数字化映射，提供实时数据可视化与智能分析功能', '0', 'admin', '2024-03-10 00:00:00', 'admin', '2024-03-10 00:00:00')",
                
                "INSERT INTO \"PROJECT\" (\"project_name\", \"project_category\", \"project_subcategory\", \"project_description\", \"status\", \"create_by\", \"create_time\", \"update_by\", \"update_time\") VALUES
                ('太师桥水文站', '数字孪生', '数字孪生水文站', '全流域水文数据采集与传输系统，支持多源数据融合与标准化管理', '0', 'admin', '2024-03-15 00:00:00', 'admin', '2024-03-15 00:00:00')",
                
                "INSERT INTO \"PROJECT\" (\"project_name\", \"project_category\", \"project_subcategory\", \"project_description\", \"status\", \"create_by\", \"create_time\", \"update_by\", \"update_time\") VALUES
                ('铜山源灌区', '数字孪生', '数字孪生灌区', '基于数字孪生技术的灌区水资源优化配置系统，实现精准灌溉与水资源高效利用', '0', 'admin', '2024-04-20 00:00:00', 'admin', '2024-04-20 00:00:00')",
                
                "INSERT INTO \"PROJECT\" (\"project_name\", \"project_category\", \"project_subcategory\", \"project_description\", \"status\", \"create_by\", \"create_time\", \"update_by\", \"update_time\") VALUES
                ('水资源评价调配', '水利信息化', '水资源评价调配', '水资源评价调配模型是宁波 \"库群互联、三网协同\" 智慧水网体系的核心技术支撑', '0', 'admin', '2024-05-10 00:00:00', 'admin', '2024-05-10 00:00:00')",
                
                "INSERT INTO \"PROJECT\" (\"project_name\", \"project_category\", \"project_subcategory\", \"project_description\", \"status\", \"create_by\", \"create_time\", \"update_by\", \"update_time\") VALUES
                ('流域数据采集平台', '水利信息化', '流域数据采集平台', '多参数水质在线监测系统，实现水质指标实时采集、分析与可视化，支持水质预警和决策辅助', '0', 'admin', '2024-05-15 00:00:00', 'admin', '2024-05-15 00:00:00')",
                
                "INSERT INTO \"PROJECT\" (\"project_name\", \"project_category\", \"project_subcategory\", \"project_description\", \"status\", \"create_by\", \"create_time\", \"update_by\", \"update_time\") VALUES
                ('千岛湖水资源评价', '水利信息化', '千岛湖水资源评价', '基于多源数据的千岛湖水资源综合评价系统，实现水资源量、质、效的全面监测与分析', '0', 'admin', '2024-06-01 00:00:00', 'admin', '2024-06-01 00:00:00')"
            };
            
            // Execute all insert statements
            for (String insertSql : insertStatements) {
                stmt.executeUpdate(insertSql);
            }
            
            System.out.println("\n✅ SUCCESS: Table created with proper Chinese encoding!");
            System.out.println("All Chinese descriptions should now display correctly.");
            
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            System.err.println("Error: " + e.getMessage());
            e.printStackTrace();
        } finally {
            // Close connection
            try {
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}