package databases;

import java.io.*;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Locale;
import java.util.Map;
import java.util.MissingResourceException;
import java.util.Properties;
import java.util.PropertyResourceBundle;
import java.util.ResourceBundle;

import window.Windows;

import java.util.Enumeration;
import java.util.HashMap;
import java.util.List;
import java.util.Collections;
import java.util.ArrayList;
import java.util.Arrays;

public class DBConnector {
    // ÅäÖòÎÊý
    private static String dbUser;
    private static String dbPass;
    private static String dbPort;
    private static String dbAddress;
    private static final String DB_NAME = "hxzk";
    static Connection conn;
    
    // ÓïÑÔÉèÖã¨Ä¬ÈÏÖÐÎÄ£©
    private static Locale currentLocale = Locale.SIMPLIFIED_CHINESE;
    private static ResourceBundle messages;
    
    // Îļþ·¾¶
    private static final String CONFIG_PATH = "systemfile/system.txt";
    private static final String LOG_DIR = "systemfile/";
    private static final String LANG_DIR = "systemfile/";
    
    static {
        // ¼ÓÔØĬÈÏÓïÑÔ
        setLanguage("zh");
        // ¼ÓÔØÅäÖÃÎļþ
        loadConfig();
    }

    // ÉèÖÃÓïÑÔ - ´Ó systemfile Îļþ¼Ð¼ÓÔØ
    public static void setLanguage(String langCode) {
        if ("en".equalsIgnoreCase(langCode)) {
            currentLocale = Locale.ENGLISH;
        } else {
            currentLocale = Locale.SIMPLIFIED_CHINESE;
        }
        
        // ³¢ÊÔ´Ó systemfile ¼ÓÔØÓïÑÔÎļþ
        messages = loadResourceBundle(currentLocale);
    }

    // ´ÓÎļþϵͳ¼ÓÔØ×ÊÔ´°ü
    private static ResourceBundle loadResourceBundle(Locale locale) {
        String langSuffix = locale.getLanguage();
        String fileName = "Messages_" + langSuffix + ".properties";
        File langFile = new File(LANG_DIR + fileName);
        
        // È·±£Ä¿Â¼´æÔÚ
        File langDir = new File(LANG_DIR);
        if (!langDir.exists()) {
            langDir.mkdirs();
        }
        
        // ³¢ÊÔ¼ÓÔØÎļþ
        if (langFile.exists()) {
            try (InputStream is = new FileInputStream(langFile)) {
                return new PropertyResourceBundle(is);
            } catch (IOException e) {
                System.err.println("ÓïÑÔÎļþ¼ÓÔØʧ°Ü: " + e.getMessage());
            }
        }
        
        // Îļþ²»´æÔÚʱʹÓÃÄÚÖÃĬÈÏÖµ
        System.err.println("¾¯¸æ£ºÎ´ÕÒµ½ÓïÑÔÎļþ " + fileName + "£¬Ê¹ÓÃÄÚÖÃĬÈÏÏûÏ¢");
        return new ResourceBundle() {
            @Override
            protected Object handleGetObject(String key) {
                switch (key) {
                    case "CONFIG_READ_ERROR":return "ÅäÖÃÎļþ¶ÁÈ¡´íÎó";
                    case "INCOMPLETE_CONFIG":return "Êý¾Ý¿âÅäÖÃÐÅÏ¢²»ÍêÕû";
                    case "DB_CONNECTION_FAIL":return "Êý¾Ý¿âÁ¬½Óʧ°Ü";
                    case "LOG_WRITE_FAIL":return "ÈÕÖ¾ÎļþдÈëʧ°Ü";
                    case "DB_UPDATE_ERROR": return "Êý¾Ý¸üÐÂʧ°Ü";
                    case "DB_INSERT_ERROR": return "Êý¾Ý²åÈëʧ°Ü";
                    case "DB_DELETE_ERROR": return "Êý¾Ýɾ³ýʧ°Ü";
                    case "DB_TRUNCATE_ERROR": return "Çå¿Õ±íÊý¾Ýʧ°Ü";
                    case "FIELD_VALIDATION_FAIL": return "×Ö¶ÎÑé֤ʧ°Ü";
                    case "DEVICE_ID_EXISTS": return "É豸±àºÅÒÑ´æÔÚ";
                    case "INVALID_NUMBER_FORMAT": return "ÎÞЧµÄÊý×Ö¸ñʽ"; 
                    // Ìí¼ÓȱʧµÄ¼ü
                    case "DB_QUERY_ERROR": return "Êý¾Ý¿â²éѯʧ°Ü";
                    default: 
                        return key;
                }
            }
            
            @Override
            public Enumeration<String> getKeys() {
                return Collections.enumeration(
                    Arrays.asList(
                    		"CONFIG_READ_ERROR", 
                            "INCOMPLETE_CONFIG", 
                            "DB_CONNECTION_FAIL", 
                            "LOG_WRITE_FAIL",
                            "DB_UPDATE_ERROR",
                            "DB_INSERT_ERROR",
                            "DB_DELETE_ERROR",
                            "DB_TRUNCATE_ERROR",
                            "FIELD_VALIDATION_FAIL",
                            "DEVICE_ID_EXISTS",
                            "INVALID_NUMBER_FORMAT",
                            "DB_QUERY_ERROR"  // Ìí¼ÓȱʧµÄ¼ü
                    )
                );
            }
        };
    }

    // ¼ÓÔØÅäÖÃÎļþ
    private static void loadConfig() {
        File configFile = new File(CONFIG_PATH);
        if (!configFile.exists()) {
            logError("CONFIG_READ_ERROR", "ÅäÖÃÎļþ²»´æÔÚ: " + CONFIG_PATH);
            return;
        }
        
        try (BufferedReader reader = new BufferedReader(new FileReader(configFile))) {
            String line;
            while ((line = reader.readLine()) != null) {
                if (line.startsWith("dbName:")) {
                    dbUser = line.substring(7).trim();
                } else if (line.startsWith("dbPass:")) {
                    dbPass = line.substring(7).trim();
                } else if (line.startsWith("databaseport:")) {
                    dbPort = line.substring(13).trim();
                } else if (line.startsWith("databaseadress:")) {
                    dbAddress = line.substring(15).trim();
                }
            }
        } catch (IOException e) {
            logError("CONFIG_READ_ERROR", e.getMessage());
        }
    }

       
    private static void logError(String errorCode, String detail) {
        String logFileName = LOG_DIR + new SimpleDateFormat("yyyyMMdd").format(new Date()) + "log.txt";
        
        // È·±£ÈÕ־Ŀ¼´æÔÚ
        File logDir = new File(LOG_DIR);
        if (!logDir.exists()) {
            logDir.mkdirs();
        }
        
        try (PrintWriter writer = new PrintWriter(new FileWriter(logFileName, true))) {
            String timestamp = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
            
            // °²È«»ñÈ¡×ÊÔ´×Ö·û´®£¬±ÜÃâMissingResourceException
            String message;
            try {
                message = messages.getString(errorCode);
            } catch (MissingResourceException e) {
                message = errorCode; // ʹÓôíÎó´úÂë×÷Ϊ»ØÍËÏûÏ¢
            }
            
            if (detail != null) {
                message += ": " + detail;
            }
            
            writer.println("[" + timestamp + "] " + message);
        } catch (IOException e) {
            System.err.println("ÈÕ־дÈëʧ°Ü: " + e.getMessage());
        }
    }

    // Êý¾Ý¿âÁ¬½Ó·½·¨ - Ìí¼ÓUTF-8×Ö·û¼¯Ö§³Ö
    public static Connection connectToDatabase() {
        // ¼ì²éÅäÖÃÍêÕûÐÔ
        if (dbUser == null || dbPass == null || dbPort == null || dbAddress == null) {
            logError("INCOMPLETE_CONFIG", null);
            return null;
        }

        // Ìí¼Ó×Ö·û¼¯²ÎÊý£ºuseUnicode=true&characterEncoding=UTF-8
        String url = "jdbc:mysql://" + dbAddress + ":" + dbPort + "/" + DB_NAME + 
                     "?useUnicode=true&characterEncoding=UTF-8";
        
        Properties properties = new Properties();
        properties.setProperty("user", dbUser);
        properties.setProperty("password", dbPass);
        properties.setProperty("useSSL", "false");
        properties.setProperty("serverTimezone", "UTC");

        try {
           conn = DriverManager.getConnection(url, properties);
            return conn;
        } catch (SQLException e) {
            logError("DB_CONNECTION_FAIL", e.getMessage());
            return null;
        }
    }
    
    /**
     * ²éѯָ¶¨±íµÄËùÓÐÊý¾Ý
     * @param tableName Òª²éѯµÄ±íÃû
     * @return °üº¬±íÖÐËùÓÐÊý¾ÝµÄList£¬Ã¿¸öÔªËØÊÇÒ»¸öMap±íʾһÐÐÊý¾Ý
     */
    public static ResultSet queryTableData(String tableName) {            
        Statement stmt = null;
        ResultSet rs = null;
        
        // ÑéÖ¤±íÃûÓÐЧÐÔ
        if (tableName == null || tableName.trim().isEmpty()) {
            logError("DB_QUERY_ERROR", "ÎÞЧµÄ±íÃû: " + tableName);
            return rs;
        }
        
        try {
            conn = connectToDatabase();
            if (conn == null) {
                logError("DB_CONNECTION_FAIL", "ÎÞ·¨Á¬½Óµ½Êý¾Ý¿â");
                return rs;
            }
            
            stmt = conn.createStatement();
            String sql = "SELECT * FROM " + tableName;
            rs = stmt.executeQuery(sql);            
            
        } catch (SQLException e) {
            logError("DB_QUERY_ERROR", "²éѯ±í[" + tableName + "]ʧ°Ü: " + e.getMessage());
            e.printStackTrace();
        } 
        
        return rs;
    }
    
    public static void lianjieshujuku() {
    	 // ÉèÖÃÓïÑÔ£¨Ä¬ÈÏΪÖÐÎÄ£¬¿ÉÇл»ÎªÓ¢ÎÄ£©
        String language = "zh"; // ĬÈÏÖÐÎÄ
        // String language = "en"; // Çл»ÎªÓ¢ÎÄ
        
        DBConnector.setLanguage(language);
        
        System.out.println("ÕýÔÚÁ¬½ÓÊý¾Ý¿â...");
        Connection conn = DBConnector.connectToDatabase();
        
        if (conn != null) {
            System.out.println("Êý¾Ý¿âÁ¬½Ó³É¹¦£¡");
            
            // ´´½¨²¢ÏÔʾ´°Ìå
            java.awt.EventQueue.invokeLater(() -> {
                Locale locale = "en".equals(language) ? Locale.ENGLISH : Locale.SIMPLIFIED_CHINESE;
                Windows window = new Windows("¶¨Î»¹ÜÀíϵͳ", conn, locale);
                window.setVisible(true);
            });
            
            // ʾÀý£ºÖ´Ðмòµ¥²éѯ
            try (java.sql.Statement stmt = conn.createStatement();
                 java.sql.ResultSet rs = stmt.executeQuery("SELECT VERSION()")) {
                
                if (rs.next()) {
                    System.out.println("MySQL°æ±¾: " + rs.getString(1));                   
                }
                
            } catch (Exception e) {
                System.err.println("²éѯʧ°Ü: " + e.getMessage());
            }
        } else {
            System.out.println("Êý¾Ý¿âÁ¬½Óʧ°Ü£¬Çë¼ì²éÈÕÖ¾Îļþ");
        }
    }
    
    // 1. ÐÞ¸ÄÊý¾Ý¿âÄÚÈÝ
    public static int updateData(String tableName, Map<String, String> fieldValues, int id) {
        if (fieldValues == null || fieldValues.isEmpty()) {
            return 0;
        }
        
        StringBuilder sql = new StringBuilder("UPDATE " + tableName + " SET ");
        List<String> values = new ArrayList<>();
        
        // ¹¹½¨SET×Ó¾ä
        for (Map.Entry<String, String> entry : fieldValues.entrySet()) {
            sql.append(entry.getKey()).append(" = ?, ");
            values.add(entry.getValue());
        }
        sql.delete(sql.length() - 2, sql.length()); // ɾ³ý×îºóµÄ¶ººÅºÍ¿Õ¸ñ
        sql.append(" WHERE id = ?");
        
        try (Connection conn = connectToDatabase();
             PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {
            
            // ÉèÖòÎÊý
            int index = 1;
            for (String value : values) {
                pstmt.setString(index++, value);
            }
            pstmt.setInt(index, id);
            
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            logError("DB_UPDATE_ERROR", "±í: " + tableName + ", ID: " + id + ", ´íÎó: " + e.getMessage());
            return 0;
        }
    }
    
    // ÐÂÔö£º¸üÐÂϵͳÅäÖÃ×Ö¶Î
    // Ð޸ĸüз½·¨
    public static int updateSystemConfiguration(String field, String value) {
        String column = camelToUnderline(field); // ת»»×Ö¶ÎÃû
        Map<String, String> fieldValues = new HashMap<>();
        fieldValues.put(column, value);
        return updateData("system_configuration", fieldValues, 1);
    }
    // 2. ÐÂÔöÊý¾Ý¿âÄÚÈÝ
    public static int insertData(String tableName, Map<String, String> fieldValues) {
        if (fieldValues == null || fieldValues.isEmpty()) {
            return 0;
        }
        
        StringBuilder columns = new StringBuilder("(");
        StringBuilder placeholders = new StringBuilder("(");
        List<String> values = new ArrayList<>();
        
        // ¹¹½¨ÁÐÃûºÍռλ·û
        for (Map.Entry<String, String> entry : fieldValues.entrySet()) {
            columns.append(entry.getKey()).append(", ");
            placeholders.append("?, ");
            values.add(entry.getValue());
        }
        columns.delete(columns.length() - 2, columns.length()).append(")");
        placeholders.delete(placeholders.length() - 2, placeholders.length()).append(")");
        
        String sql = "INSERT INTO " + tableName + " " + columns + " VALUES " + placeholders;
        
        try (Connection conn = connectToDatabase();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            // ÉèÖòÎÊý
            for (int i = 0; i < values.size(); i++) {
                pstmt.setString(i + 1, values.get(i));
            }
            
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            logError("DB_INSERT_ERROR", "±í: " + tableName + ", ´íÎó: " + e.getMessage());
            return 0;
        }
    }

    // 3. ɾ³ýÖ¸¶¨Êý¾Ý
    public static int deleteDataById(String tableName, int id) {
        String sql = "DELETE FROM " + tableName + " WHERE id = ?";
        
        try (Connection conn = connectToDatabase();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, id);
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            logError("DB_DELETE_ERROR", "±í: " + tableName + ", ID: " + id + ", ´íÎó: " + e.getMessage());
            return 0;
        }
    }

    // 4. ɾ³ýËùÓÐÊý¾Ý
    public static int truncateTable(String tableName) {
        String sql = "DELETE FROM " + tableName; // ʹÓÃDELETE¶ø·ÇTRUNCATE
        
        try (Connection conn = connectToDatabase();
             Statement stmt = conn.createStatement()) {
            
            return stmt.executeUpdate(sql); // ·µ»Øɾ³ýµÄÐÐÊý
        } catch (SQLException e) {
            logError("DB_TRUNCATE_ERROR", "±í: " + tableName + ", ´íÎó: " + e.getMessage());
            return 0;
        }
    }

    /**
     * Ö´ÐÐSQL²éѯÓï¾ä
     * @param sql ÒªÖ´ÐеÄSQL²éѯÓï¾ä
     * @return ²éѯ½á¹û¼¯ResultSet
     */
    public static ResultSet executeQuery(String sql) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        
        try {
            conn = connectToDatabase();
            if (conn == null) {
                logError("DB_CONNECTION_FAIL", "ÎÞ·¨Á¬½Óµ½Êý¾Ý¿â");
                return null;
            }
            
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            return rs;
            
        } catch (SQLException e) {
            logError("DB_QUERY_ERROR", "Ö´ÐÐSQL²éѯʧ°Ü: " + e.getMessage() + "\nSQL: " + sql);
            e.printStackTrace();
            return null;
        }
        // ×¢Ò⣺µ÷ÓÃÕßÐèÒª×ÔÐйرÕResultSet¡¢StatementºÍConnection
    }
    
    /**
     * Ö´Ðж¯Ì¬¹¹½¨µÄSQL²éѯ
     * @param tableName ±íÃû
     * @param columns ²éѯµÄÁУ¨¶ººÅ·Ö¸ô£¬null±íʾËùÓÐÁУ©
     * @param whereClause WHEREÌõ¼þ£¨¿Énull£©
     * @param orderBy ORDER BY×Ӿ䣨¿Énull£©
     * @return ²éѯ½á¹û¼¯ResultSet
     */
    public static ResultSet executeQuery(String tableName, String columns, String whereClause, String orderBy) {
        // ÑéÖ¤±íÃûÓÐЧÐÔ
        if (tableName == null || tableName.trim().isEmpty()) {
            logError("DB_QUERY_ERROR", "ÎÞЧµÄ±íÃû: " + tableName);
            return null;
        }
        
        // ¹¹½¨SQLÓï¾ä
        StringBuilder sqlBuilder = new StringBuilder("SELECT ");
        sqlBuilder.append(columns != null ? columns : "*")
                  .append(" FROM ")
                  .append(tableName);
        
        if (whereClause != null && !whereClause.trim().isEmpty()) {
            sqlBuilder.append(" WHERE ").append(whereClause);
        }
        
        if (orderBy != null && !orderBy.trim().isEmpty()) {
            sqlBuilder.append(" ORDER BY ").append(orderBy);
        }
        
        // µ÷ÓÃͨÓòéѯ·½·¨
        return executeQuery(sqlBuilder.toString());
    }
    
    /**
     * Ö´Ðдø²ÎÊýµÄSQL¸üвÙ×÷£¨INSERT, UPDATE, DELETE£©
     * @param sql ´ø²ÎÊýµÄSQLÓï¾ä
     * @param params ²ÎÊýÁбí
     * @return ÊÜÓ°ÏìµÄÐÐÊý
     * @throws SQLException Èç¹ûÊý¾Ý¿â²Ù×÷ʧ°Ü
     */
    public static int executeUpdate(String sql, Object... params) throws SQLException {
        try (Connection conn = connectToDatabase();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            // ÉèÖòÎÊý
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }
            
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            logError("DB_UPDATE_ERROR", "Ö´ÐÐSQL¸üÐÂʧ°Ü: " + e.getMessage() + "\nSQL: " + sql);
            throw e;
        }
    }
   
 // ÐÂÔö·½·¨£ºÍÕ·åתÏ»®Ïß
    public static String camelToUnderline(String camel) {
        if (camel == null || camel.isEmpty()) return camel;
        StringBuilder sb = new StringBuilder();
        for (char c : camel.toCharArray()) {
            if (Character.isUpperCase(c)) {
                sb.append('_').append(Character.toLowerCase(c));
            } else {
                sb.append(c);
            }
        }
        return sb.toString();
    }

 // Ìí¼Ó²ÎÊý»¯²éѯ·½·¨
    public static ResultSet executeQuery(String sql, Object... params) throws SQLException {
        Connection conn = connectToDatabase();
        if (conn == null) {
            throw new SQLException("ÎÞ·¨Á¬½Óµ½Êý¾Ý¿â");
        }
        PreparedStatement pstmt = conn.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            pstmt.setObject(i + 1, params[i]);
        }
        return pstmt.executeQuery();
    }
    
}