import java.io.*;
import java.nio.charset.StandardCharsets;
import java.nio.file.*;
import java.security.MessageDigest;
import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.zip.GZIPOutputStream;

/** Temporary, scoped administrator for the requested mom-thzynew data cleanup. */
public class TempDbCleanup {
    private static final String DB = "mom-thzynew";
    private static final Set<String> PRO_TABLES = Set.of(
            "mes_pro_bag_code", "mes_pro_bag_code_replace", "mes_pro_batch",
            "mes_pro_batch_worker", "mes_pro_pallet", "mes_pro_qr_export_task",
            "mes_pro_scan_event");
    private static final Set<String> KEEP = Set.of("mes_wm_warehouse", "mes_wm_warehouse_area",
            "mes_wm_warehouse_location", "mes_pro_line");

    public static void main(String[] args) throws Exception {
        if (args.length < 1 || !Set.of("inventory", "backup", "clean").contains(args[0])
                || (args[0].equals("clean") ? args.length != 2 : args.length != 1)) {
            throw new IllegalArgumentException("Usage: TempDbCleanup inventory|backup|clean <backup.sql.gz>");
        }
        String yaml = Files.readString(Path.of("yudao-server/src/main/resources/application-thzy.yaml"));
        String block = yaml.substring(yaml.indexOf("master:"));
        String user = null, pass = null;
        for (String line : block.split("\\R")) {
            if (line.trim().startsWith("username:")) user = value(line);
            if (line.trim().startsWith("password:")) { pass = value(line); break; }
        }
        if (user == null || pass == null) throw new IllegalStateException("Missing database credentials");
        String url = "jdbc:mysql://1.95.78.124:9000/" + DB
                + "?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useCursorFetch=true";
        try (Connection conn = DriverManager.getConnection(url, user, pass)) {
            if (!DB.equals(conn.getCatalog())) throw new IllegalStateException("Wrong database: " + conn.getCatalog());
            List<String> tables = targetTables(conn);
            System.out.println("DATABASE=" + DB + " TARGET_TABLES=" + tables.size());
            if (args[0].equals("inventory")) inventory(conn, tables);
            if (args[0].equals("backup")) backup(conn, tables);
            if (args[0].equals("clean")) clean(conn, tables, Path.of(args[1]));
        }
    }

    private static String value(String line) {
        return line.trim().split(":", 2)[1].trim().split("\\s+#", 2)[0];
    }

    private static List<String> targetTables(Connection conn) throws SQLException {
        List<String> names = new ArrayList<>();
        try (Statement s = conn.createStatement(); ResultSet r = s.executeQuery(
                "SELECT table_name FROM information_schema.tables WHERE table_schema='" + DB + "' "
                + "AND table_type='BASE TABLE' ORDER BY table_name")) {
            while (r.next()) {
                String t = r.getString(1);
                if (KEEP.contains(t)) continue;
                if (PRO_TABLES.contains(t) || t.startsWith("mes_qc_") || t.startsWith("mes_wm_")
                        || t.startsWith("mes_trace_") || t.startsWith("crm_")) names.add(t);
            }
        }
        return names;
    }

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

    private static void inventory(Connection conn, List<String> tables) throws SQLException {
        for (String t : tables) {
            long n = count(conn, t);
            if (n > 0) System.out.println(t + "=" + n);
        }
        for (String t : KEEP) System.out.println("KEEP " + t + "=" + count(conn, t));
        try (Statement s = conn.createStatement(); ResultSet r = s.executeQuery(
                "SELECT status, COUNT(*), SUM(total_count-finished_count) FROM mes_pro_qr_export_task "
                        + "WHERE status IN (10,20) GROUP BY status")) {
            while (r.next()) System.out.println("ACTIVE_EXPORT status=" + r.getInt(1)
                    + " tasks=" + r.getLong(2) + " remaining=" + r.getLong(3));
        }
    }

    private static void requireIdle(Connection conn) throws SQLException {
        try (Statement s = conn.createStatement(); ResultSet r = s.executeQuery(
                "SELECT COUNT(*) FROM mes_pro_qr_export_task WHERE status IN (10,20)")) {
            r.next();
            if (r.getLong(1) != 0) throw new IllegalStateException("QR export task still active; aborting");
        }
    }

    private static void backup(Connection conn, List<String> tables) throws Exception {
        requireIdle(conn);
        Path dir = Path.of("db-backups");
        Files.createDirectories(dir);
        String stamp = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd-HHmmss"));
        Path dest = dir.resolve(DB + "-business-cleanup-" + stamp + ".sql.gz");
        Path temp = dir.resolve(dest.getFileName() + ".part");
        long total = 0;
        Properties manifest = new Properties();
        manifest.setProperty("database", DB);
        manifest.setProperty("table.count", Integer.toString(tables.size()));
        conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
        conn.setAutoCommit(false);
        try (BufferedWriter out = new BufferedWriter(new OutputStreamWriter(
                new GZIPOutputStream(Files.newOutputStream(temp), 1024 * 1024), StandardCharsets.UTF_8), 1024 * 1024)) {
            out.write("-- mom-thzynew requested business-data backup; restore only to the intended database\n");
            out.write("SET NAMES utf8mb4;\nSET sql_mode='';\n");
            for (String table : tables) {
                long rows = 0;
                try (Statement s = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {
                    s.setFetchSize(1000);
                    try (ResultSet r = s.executeQuery("SELECT * FROM `" + table + "`")) {
                        ResultSetMetaData md = r.getMetaData();
                        int cols = md.getColumnCount();
                        StringJoiner columns = new StringJoiner(",");
                        for (int i = 1; i <= cols; i++) columns.add("`" + md.getColumnName(i) + "`");
                        String prefix = "INSERT INTO `" + table + "` (" + columns + ") VALUES (";
                        while (r.next()) {
                            out.write(prefix);
                            for (int i = 1; i <= cols; i++) {
                                if (i > 1) out.write(',');
                                writeSqlValue(out, r, md, i);
                            }
                            out.write(");\n");
                            rows++;
                        }
                    }
                }
                total += rows;
                manifest.setProperty("row." + table, Long.toString(rows));
                if (rows > 0) System.out.println("BACKED_UP " + table + "=" + rows);
            }
            out.write("-- backup complete: " + total + " rows\n");
            conn.rollback();
        } catch (Exception e) {
            conn.rollback();
            Files.deleteIfExists(temp);
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
        Files.move(temp, dest, StandardCopyOption.ATOMIC_MOVE);
        manifest.setProperty("sha256", sha256(dest));
        manifest.setProperty("rows.total", Long.toString(total));
        try (OutputStream m = Files.newOutputStream(Path.of(dest + ".manifest"))) {
            manifest.store(m, "mom-thzynew business data backup manifest");
        }
        System.out.println("BACKUP_FILE=" + dest.toAbsolutePath() + " ROWS=" + total
                + " BYTES=" + Files.size(dest));
    }

    private static void writeSqlValue(Writer out, ResultSet r, ResultSetMetaData md, int i) throws Exception {
        int type = md.getColumnType(i);
        if (type == Types.BINARY || type == Types.VARBINARY || type == Types.LONGVARBINARY
                || type == Types.BLOB || type == Types.BIT) {
            byte[] bytes = r.getBytes(i);
            if (bytes == null) { out.write("NULL"); return; }
            out.write("X'");
            for (byte b : bytes) out.write(String.format("%02X", b & 0xff));
            out.write("'");
            return;
        }
        String value = r.getString(i);
        if (value == null) { out.write("NULL"); return; }
        if (type == Types.INTEGER || type == Types.BIGINT || type == Types.SMALLINT
                || type == Types.TINYINT || type == Types.DECIMAL || type == Types.NUMERIC
                || type == Types.DOUBLE || type == Types.FLOAT || type == Types.REAL) {
            out.write(value); return;
        }
        out.write('\'');
        for (int p = 0; p < value.length(); p++) {
            char c = value.charAt(p);
            switch (c) {
                case '\\' -> out.write("\\\\");
                case '\'' -> out.write("\\'");
                case '\n' -> out.write("\\n");
                case '\r' -> out.write("\\r");
                case '\0' -> out.write("\\0");
                case 26 -> out.write("\\Z");
                default -> out.write(c);
            }
        }
        out.write('\'');
    }

    private static String sha256(Path path) throws Exception {
        MessageDigest digest = MessageDigest.getInstance("SHA-256");
        try (InputStream in = Files.newInputStream(path)) {
            byte[] buf = new byte[1024 * 1024];
            int n;
            while ((n = in.read(buf)) != -1) digest.update(buf, 0, n);
        }
        return HexFormat.of().formatHex(digest.digest());
    }

    private static void clean(Connection conn, List<String> tables, Path backup) throws Exception {
        requireIdle(conn);
        Properties manifest = new Properties();
        try (InputStream in = Files.newInputStream(Path.of(backup + ".manifest"))) { manifest.load(in); }
        if (!DB.equals(manifest.getProperty("database"))
                || tables.size() != Integer.parseInt(manifest.getProperty("table.count"))
                || !sha256(backup).equals(manifest.getProperty("sha256"))) {
            throw new IllegalStateException("Backup integrity or target mismatch");
        }
        // Verify every table before any destructive statement. New writes cause a hard abort.
        Map<String, Long> expected = new LinkedHashMap<>();
        for (String table : tables) {
            String raw = manifest.getProperty("row." + table);
            if (raw == null) throw new IllegalStateException("Missing backup count: " + table);
            long n = Long.parseLong(raw);
            long now = count(conn, table);
            if (n != now) throw new IllegalStateException("Table changed since backup: " + table
                    + " backup=" + n + " current=" + now);
            expected.put(table, n);
        }
        for (String table : List.of("mes_pro_line", "mes_wm_warehouse", "mes_wm_warehouse_area",
                "mes_wm_warehouse_location")) {
            System.out.println("PRESERVE " + table + "=" + count(conn, table));
        }
        for (Map.Entry<String, Long> entry : expected.entrySet()) {
            if (entry.getValue() == 0) continue;
            requireIdle(conn);
            try (Statement s = conn.createStatement()) {
                s.executeUpdate("TRUNCATE TABLE `" + entry.getKey() + "`");
            }
            System.out.println("CLEARED " + entry.getKey() + "=" + entry.getValue());
        }
        inventory(conn, tables);
    }
}