import java.io.BufferedWriter;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.Types;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.zip.GZIPOutputStream;

/** One-time cleanup of the requested modules in mom-thzynew. No credentials are stored here. */
public class Cleanup {
    private static final String DB = "mom-thzynew";
    private static final String URL = "jdbc:mysql://1.95.78.124:9000/" + DB
            + "?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&connectTimeout=5000";
    private static final List<String> PRESERVED = List.of(
            "mes_wm_warehouse", "mes_wm_warehouse_area", "mes_wm_warehouse_location", "mes_pro_line");

    private static boolean selected(String table) {
        if (PRESERVED.contains(table)) return false;
        return table.startsWith("crm_")
                || table.startsWith("erp_sale_")
                || table.startsWith("mes_wm_")
                || table.startsWith("mes_qc_")
                || table.startsWith("mes_pro_bag_code")
                || table.startsWith("mes_pro_batch")
                || table.equals("mes_pro_pallet")
                || table.equals("mes_pro_scan_event")
                || table.equals("mes_trace_consumer_scan_event")
                || table.equals("mes_trace_cross_region_warning")
                || table.equals("mes_trace_event")
                || table.equals("mes_trace_integrity_record");
    }

    private static String password() throws IOException {
        String yaml = Files.readString(Path.of("yudao-server/src/main/resources/application-thzy.yaml"));
        return Arrays.stream(yaml.split("\\R")).map(String::trim)
                .filter(line -> line.startsWith("password:"))
                .findFirst().orElseThrow().substring("password:".length()).trim();
    }

    private static List<String> tables(Connection connection) throws Exception {
        List<String> result = new ArrayList<>();
        try (Statement statement = connection.createStatement();
             ResultSet rows = statement.executeQuery("SELECT table_name FROM information_schema.tables "
                     + "WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE' ORDER BY table_name")) {
            while (rows.next()) {
                String table = rows.getString(1);
                if (selected(table)) result.add(table);
            }
        }
        return result;
    }

    private static long count(Connection connection, String table) throws Exception {
        try (Statement statement = connection.createStatement();
             ResultSet rows = statement.executeQuery("SELECT COUNT(*) FROM `" + table + "`")) {
            rows.next();
            return rows.getLong(1);
        }
    }

    private static String hex(byte[] bytes) {
        StringBuilder result = new StringBuilder(bytes.length * 2);
        for (byte value : bytes) result.append(String.format("%02X", value & 0xff));
        return result.toString();
    }

    private static String literal(ResultSet row, ResultSetMetaData meta, int column) throws Exception {
        Object value = row.getObject(column);
        if (value == null) return "NULL";
        int type = meta.getColumnType(column);
        if (type == Types.BINARY || type == Types.VARBINARY || type == Types.LONGVARBINARY
                || type == Types.BLOB || type == Types.BIT || type == Types.BOOLEAN) {
            return "0x" + hex(row.getBytes(column));
        }
        if (type == Types.TINYINT || type == Types.SMALLINT || type == Types.INTEGER
                || type == Types.BIGINT || type == Types.FLOAT || type == Types.REAL
                || type == Types.DOUBLE || type == Types.DECIMAL || type == Types.NUMERIC) {
            return row.getString(column);
        }
        return "CONVERT(0x" + hex(row.getString(column).getBytes(StandardCharsets.UTF_8)) + " USING utf8mb4)";
    }

    private static void backup(Connection connection, List<String> tables, Path destination) throws Exception {
        Files.createDirectories(destination.getParent());
        try (BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(
                new GZIPOutputStream(Files.newOutputStream(destination)), StandardCharsets.UTF_8))) {
            writer.write("-- Data backup before requested cleanup of " + DB + "\n");
            writer.write("-- Restore only after reviewing target schema and current data.\n");
            writer.write("SET NAMES utf8mb4;\n");
            for (String table : tables) {
                writer.write("-- " + table + "\n");
                try (Statement statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                        ResultSet.CONCUR_READ_ONLY)) {
                    statement.setFetchSize(Integer.MIN_VALUE);
                    try (ResultSet rows = statement.executeQuery("SELECT * FROM `" + table + "`")) {
                        ResultSetMetaData meta = rows.getMetaData();
                        StringBuilder columns = new StringBuilder();
                        for (int i = 1; i <= meta.getColumnCount(); i++) {
                            if (i > 1) columns.append(',');
                            columns.append('`').append(meta.getColumnName(i)).append('`');
                        }
                        while (rows.next()) {
                            writer.write("INSERT INTO `" + table + "` (" + columns + ") VALUES (");
                            for (int i = 1; i <= meta.getColumnCount(); i++) {
                                if (i > 1) writer.write(',');
                                writer.write(literal(rows, meta, i));
                            }
                            writer.write(");\n");
                        }
                    }
                }
            }
        }
    }

    public static void main(String[] args) throws Exception {
        if (args.length < 1 || !List.of("audit", "backup", "delete").contains(args[0])) {
            throw new IllegalArgumentException("Usage: Cleanup audit|backup|delete [backup-path]");
        }
        try (Connection connection = DriverManager.getConnection(URL, "root", password())) {
            if (!DB.equals(connection.getCatalog())) throw new IllegalStateException("Wrong database");
            List<String> tables = tables(connection);
            long total = 0;
            for (String table : tables) {
                long rows = count(connection, table);
                total += rows;
                if (rows > 0 || args[0].equals("audit")) System.out.println(table + "\t" + rows);
            }
            System.out.println("TABLES=" + tables.size() + " ROWS=" + total);
            for (String preserved : PRESERVED) System.out.println("PRESERVED " + preserved + "=" + count(connection, preserved));
            if (args[0].equals("audit")) return;
            if (args.length != 2) throw new IllegalArgumentException("Backup path required");
            Path backupPath = Path.of(args[1]).toAbsolutePath();
            if (args[0].equals("backup")) {
                if (Files.exists(backupPath)) throw new IllegalStateException("Backup already exists");
                backup(connection, tables, backupPath);
                System.out.println("BACKUP=" + backupPath + " BYTES=" + Files.size(backupPath));
                return;
            }
            if (!Files.isRegularFile(backupPath) || Files.size(backupPath) == 0) {
                throw new IllegalStateException("Backup is missing or empty");
            }
            if (total != 53982 || count(connection, "mes_wm_warehouse") != 5
                    || count(connection, "mes_wm_warehouse_area") != 3
                    || count(connection, "mes_wm_warehouse_location") != 4
                    || count(connection, "mes_pro_line") != 3) {
                throw new IllegalStateException("Database changed since backup; aborting cleanup");
            }
            connection.setAutoCommit(false);
            try {
                for (String table : tables) {
                    try (Statement statement = connection.createStatement()) {
                        System.out.println("DELETED " + table + "=" + statement.executeUpdate("DELETE FROM `" + table + "`"));
                    }
                }
                for (String table : tables) {
                    if (count(connection, table) != 0) throw new IllegalStateException("Not empty: " + table);
                }
                connection.commit();
            } catch (Exception failure) {
                connection.rollback();
                throw failure;
            }
            System.out.println("COMMITTED " + LocalDateTime.now().format(DateTimeFormatter.ISO_LOCAL_DATE_TIME));
        }
    }
}