import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.SQLException;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;

public class CreateTable {
    public static void main(String[] args) {
        // Database connection information
        String url = "jdbc:dm://192.168.0.145:30236?charset=utf8";
        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();
            
            // Execute create table SQL
            System.out.println("Executing create table SQL...");
            executeSqlFile(conn, "d:\\Web\\DN\\ruoyi-admin\\src\\main\\resources\\sql\\project.sql");
            
            // Execute insert data SQL
            System.out.println("Executing insert data SQL...");
            executeSqlFile(conn, "d:\\Web\\DN\\ruoyi-admin\\src\\main\\resources\\sql\\project_data.sql");
            
            System.out.println("Table created and data inserted successfully!");
            
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            // Close connection
            try {
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    // Execute SQL file
    private static void executeSqlFile(Connection conn, String filePath) throws SQLException, IOException {
        BufferedReader reader = new BufferedReader(new FileReader(filePath));
        String line;
        StringBuilder sql = new StringBuilder();
        
        while ((line = reader.readLine()) != null) {
            line = line.trim();
            // Skip comments and empty lines
            if (line.isEmpty() || line.startsWith("--")) {
                continue;
            }
            // Skip CREATE TABLE IF NOT EXISTS - we want to drop and recreate
            if (line.toUpperCase().startsWith("DROP TABLE IF EXISTS")) {
                Statement stmt = conn.createStatement();
                stmt.executeUpdate(line);
                stmt.close();
                continue;
            }
            // Skip BEGIN and COMMIT statements
            if (line.toUpperCase().equals("BEGIN;") || line.toUpperCase().equals("COMMIT;")) {
                continue;
            }
            // Append line to SQL
            sql.append(" ").append(line);
            // If ends with semicolon, execute SQL
            if (line.endsWith(";")) {
                String sqlStatement = sql.toString().trim();
                if (!sqlStatement.isEmpty()) {
                    Statement stmt = conn.createStatement();
                    try {
                        stmt.executeUpdate(sqlStatement);
                    } catch (SQLException e) {
                        System.err.println("Error executing SQL: " + sqlStatement);
                        throw e;
                    } finally {
                        stmt.close();
                    }
                }
                sql = new StringBuilder();
            }
        }
        
        // Execute any remaining SQL
        String remainingSql = sql.toString().trim();
        if (!remainingSql.isEmpty()) {
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(remainingSql);
            stmt.close();
        }
        
        reader.close();
    }
}