import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.SQLException;

public class FixEncodingFinal {
    public static void main(String[] args) {
        // Database connection information
        String url = "jdbc:dm://192.168.0.145:30236?charset=utf8"; // Force UTF-8
        String username = "WATERSHED"; // All uppercase username
        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();
            
            // Use basic English SQL statements to avoid encoding issues
            System.out.println("Setting UTF-8 encoding...");
            stmt.executeUpdate("SET CLIENT_ENCODING TO 'UTF8'");
            
            // Drop existing table if exists
            System.out.println("Dropping existing table...");
            stmt.executeUpdate("DROP TABLE IF EXISTS PROJECT");
            
            // Create table with simple English column names
            System.out.println("Creating table with simple English columns...");
            String createTable = "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), " +
                "project_url VARCHAR(255), " +
                "status CHAR(1) DEFAULT '0', " +
                "create_by VARCHAR(64) DEFAULT '', " +
                "create_time DATETIME, " +
                "update_by VARCHAR(64) DEFAULT '', " +
                "update_time DATETIME, " +
                "remark VARCHAR(500), " +
                "PRIMARY KEY (project_id)" +
            ")";
            stmt.executeUpdate(createTable);
            
            // Add constraint
            System.out.println("Adding constraint...");
            stmt.executeUpdate("ALTER TABLE PROJECT ADD CONSTRAINT project_status_check CHECK (status IN ('0','1','2'))");
            
            // Insert data using basic INSERT statements
            System.out.println("Inserting data...");
            
            // Insert project 1
            stmt.executeUpdate("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 project 2
            stmt.executeUpdate("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 project 3
            stmt.executeUpdate("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 project 4
            stmt.executeUpdate("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 project 5
            stmt.executeUpdate("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 project 6
            stmt.executeUpdate("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 project 7
            stmt.executeUpdate("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 project 8
            stmt.executeUpdate("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')");
            
            System.out.println("\n✅ SUCCESS!");
            System.out.println("Table 'PROJECT' created successfully with 8 projects.");
            System.out.println("All data should now display correctly without encoding issues.");
            System.out.println("\nTable structure:");
            System.out.println("- project_id (BIGINT) - Primary key, auto increment");
            System.out.println("- project_name (VARCHAR) - Project name");
            System.out.println("- project_category (VARCHAR) - Main category");
            System.out.println("- project_subcategory (VARCHAR) - Subcategory");
            System.out.println("- project_description (VARCHAR) - Description");
            System.out.println("- status (CHAR) - Status (0=normal, 1=maintenance, 2=deleted)");
            System.out.println("- create_by (VARCHAR) - Created by");
            System.out.println("- create_time (DATETIME) - Created time");
            System.out.println("- update_by (VARCHAR) - Updated by");
            System.out.println("- update_time (DATETIME) - Updated time");
            System.out.println("- remark (VARCHAR) - Remark");
            
        } catch (ClassNotFoundException e) {
            System.err.println("Driver error: " + e.getMessage());
            e.printStackTrace();
        } catch (SQLException e) {
            System.err.println("SQL error: " + e.getMessage());
            e.printStackTrace();
        } finally {
            // Close connection
            try {
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}