package scheduled_task;
import java.sql.*;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;

public class TrackTableManager {

    // ÅäÖòÎÊý
    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/your_database?useSSL=false";
    private static final String USERNAME = "your_username";
    private static final String PASSWORD = "your_password";
    private static final int SAVE_DAYS = 30; // ¹ì¼£Êý¾Ý±£´æÌìÊý
    private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyyMMdd");
    private static final String TABLE_PREFIX = "tb_track_";

    public static void main(String[] args) {
        // ³õʼ»¯Ê±Á¢¼´Ö´ÐÐÒ»´Î
        manageTables();
        
        // ´´½¨¶¨Ê±ÈÎÎñ£¨Ã¿ÌìÁ賿1µãÖ´ÐУ©
        ScheduledExecutorService scheduler = Executors.newSingleThreadScheduledExecutor();
        scheduler.scheduleAtFixedRate(
            TrackTableManager::manageTables,
            getInitialDelay(),
            24 * 60 * 60, // 24СʱÖÜÆÚ
            TimeUnit.SECONDS
        );
    }

    // Ö÷¹ÜÀíÂß¼­
    private static void manageTables() {
        try (Connection conn = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD)) {
            // 1. ´´½¨Ã÷ÌìµÄ±í
            createTomorrowTable(conn);
            
            // 2. ɾ³ý¹ýÆÚ±í
            deleteExpiredTables(conn);
            
        } catch (SQLException e) {
            System.err.println("Êý¾Ý¿â²Ù×÷ʧ°Ü: " + e.getMessage());
        }
    }

    // ´´½¨Ã÷ÌìµÄ±í
    private static void createTomorrowTable(Connection conn) throws SQLException {
        String tomorrow = LocalDate.now().plusDays(1).format(DATE_FORMATTER);
        String tableName = TABLE_PREFIX + tomorrow;
        
        if (!tableExists(conn, tableName)) {
            String createSQL = "CREATE TABLE " + tableName + " (" +
                "id INT(30) PRIMARY KEY AUTO_INCREMENT COMMENT 'ÐòºÅ'," +
                "device_number VARCHAR(30) NOT NULL COMMENT 'É豸±àºÅ'," +
                "device_name VARCHAR(30) COMMENT 'É豸Ãû³Æ'," +
                "x_coordinate VARCHAR(30) COMMENT 'X×ø±ê'," +
                "y_coordinate VARCHAR(30) COMMENT 'Y×ø±ê'," +
                "z_coordinate VARCHAR(30) COMMENT 'Z×ø±ê'," +
                "layer VARCHAR(30) COMMENT 'ËùÔÚ²ã'," +
                "battery VARCHAR(30) COMMENT 'µçÁ¿'," +
                "utc_times VARCHAR(30) COMMENT 'UTCʱ¼ä'," +
                "latitude VARCHAR(30) COMMENT 'γ¶È'," +
                "longitude VARCHAR(30) COMMENT '¾­¶È'," +
                "positioning_quality VARCHAR(30) COMMENT '¶¨Î»ÖÊÁ¿'," +
                "satellite_count VARCHAR(30) COMMENT 'ÎÀÐÇÊýÁ¿'," +
                "hdop VARCHAR(30) COMMENT 'ˮƽ¾«¶ÈÒò×Ó'," +
                "altitude VARCHAR(30) COMMENT 'º£°Î¸ß¶È'," +
                "geoid_height VARCHAR(30) COMMENT '´óµØË®×¼Ãæ¸ß¶È'," +
                "differential_time VARCHAR(30) COMMENT '²î·Öʱ¼ä'," +
                "source VARCHAR(30) COMMENT '¶¨Î»½á¹ûÀ´Ô´(0=UWB,1=ÎÀÐÇ,3=À¶ÑÀ)'," +
                "company VARCHAR(30) COMMENT 'ËùÊô¹«Ë¾'," +
                "save_time VARCHAR(30) COMMENT '±£´æʱ¼ä'" +
                ") ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='¹ì¼£¼Ç¼±í'";
            
            try (Statement stmt = conn.createStatement()) {
                stmt.executeUpdate(createSQL);
                System.out.println("±í´´½¨³É¹¦: " + tableName);
            }
        } else {
            System.out.println("±íÒÑ´æÔÚ: " + tableName);
        }
    }

    // ɾ³ý¹ýÆÚ±í
    private static void deleteExpiredTables(Connection conn) throws SQLException {
        LocalDate thresholdDate = LocalDate.now().minusDays(SAVE_DAYS);
        String threshold = thresholdDate.format(DATE_FORMATTER);
        
        for (String table : getAllTrackTables(conn)) {
            String datePart = table.substring(TABLE_PREFIX.length());
            if (datePart.compareTo(threshold) < 0) {
                try (Statement stmt = conn.createStatement()) {
                    stmt.executeUpdate("DROP TABLE " + table);
                    System.out.println("ÒÑɾ³ý¹ýÆÚ±í: " + table);
                }
            }
        }
    }

    // ¼ì²é±íÊÇ·ñ´æÔÚ
    private static boolean tableExists(Connection conn, String tableName) throws SQLException {
        try (ResultSet rs = conn.getMetaData().getTables(null, null, tableName, null)) {
            return rs.next();
        }
    }

    // »ñÈ¡ËùÓй켣±í
    private static List<String> getAllTrackTables(Connection conn) throws SQLException {
        List<String> tables = new ArrayList<>();
        try (ResultSet rs = conn.getMetaData().getTables(null, null, TABLE_PREFIX + "%", null)) {
            while (rs.next()) {
                tables.add(rs.getString("TABLE_NAME"));
            }
        }
        return tables;
    }

    // ¼ÆËãÊ×´ÎÖ´ÐÐÑÓ³Ù£¨µ½ÏÂÒ»¸öÁ賿1µãµÄÃëÊý£©
    private static long getInitialDelay() {
        long now = System.currentTimeMillis() / 1000;
        long oneDay = 24 * 60 * 60;
        long target = (now / oneDay * oneDay) + oneDay + 3600; // ÏÂÒ»ÌìÁ賿1µã
        return target - now;
    }
}