import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.PrintStream;
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.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Properties;
import java.util.Set;

/**
 * 只读探测工具，迁移期间使用：
 *   list                   列出旧库 b_other_bmfl 全部分组与模块清单（启用、旧库行数、新库是否已建）
 *   tables <表名...>        输出指定表的三段信息：新库结构、旧库结构、旧库数据样例（前 3 行）
 *
 * 输出写入 tools/migration/ 下的文件（UTF-8 BOM，便于 Windows 记事本打开）：
 *   list   → otherdata-modules.txt
 *   tables → otherdata-probe-out.txt
 *
 * 用法（源码启动模式，不产生 .class 文件）：
 *   cd fms-api
 *   java -cp "tools/migration/mssql-jdbc-13.4.0.jre11.jar" tools/migration/OtherDataProbe.java list
 *   java -cp "tools/migration/mssql-jdbc-13.4.0.jre11.jar" tools/migration/OtherDataProbe.java tables b_port b_money
 */
public final class OtherDataProbe {

    private static final Path MIGRATION_DIR = Path.of("").toAbsolutePath().normalize().resolve("tools/migration");

    public static void main(String[] args) throws Exception {
        if (args.length == 0) {
            usage();
            return;
        }
        switch (args[0]) {
            case "list" -> listModules();
            case "columns" -> listColumns();
            case "tables" -> {
                if (args.length < 2) {
                    usage();
                    return;
                }
                probeTables(Arrays.copyOfRange(args, 1, args.length));
            }
            default -> usage();
        }
    }

    private static void usage() {
        System.out.println("用法:");
        System.out.println("  list                   列出旧库 b_other_bmfl 全部分组与模块清单");
        System.out.println("  columns                导出其他数据各字典在旧库 s_columnLib 的列配置");
        System.out.println("  tables b_port b_money  输出指定表的新库结构、旧库结构、旧库数据样例");
    }

    // ------------------------------------------------------------ list 模式

    /** 旧库 b_other_bmfl 的一个模块（一条数据表登记） */
    private record Module(String table, String name, boolean enabled) {}

    private static void listModules() throws Exception {
        Path output = MIGRATION_DIR.resolve("otherdata-modules.txt");
        try (PrintStream out = newBomPrintStream(output);
             Connection old = connect("G3HY2025");
             Connection fms = connect("G3HD")) {

            Map<String, List<Module>> groups = new LinkedHashMap<>();
            try (Statement statement = old.createStatement();
                 ResultSet rows = statement.executeQuery("""
                         select isnull(nullif(b_groupby, ''), '(未分组)') as grp,
                                ltrim(rtrim(b_tablename)) as tbl,
                                isnull(mc_b, '') as mc,
                                isnull(b_canuse, '') as canuse
                         from G3HY2025.dbo.b_other_bmfl
                         where b_tablename is not null and ltrim(rtrim(b_tablename)) != ''
                         order by grp, b_xh, mc
                         """)) {
                while (rows.next()) {
                    groups.computeIfAbsent(rows.getString("grp"), key -> new ArrayList<>())
                            .add(new Module(rows.getString("tbl"), rows.getString("mc"),
                                    "1".equals(rows.getString("canuse"))));
                }
            }

            Set<String> fmsObjects = fmsObjectNames(fms);

            out.println("=== 旧库 b_other_bmfl 模块清单 ===");
            out.println("  表名" + pad("", 22) + "名称" + pad("", 20) + "状态  旧库行数  新库");

            int total = 0;
            int totalEnabled = 0;
            int totalWithData = 0;
            int totalBuilt = 0;
            int groupIndex = 0;
            for (Map.Entry<String, List<Module>> entry : groups.entrySet()) {
                groupIndex++;
                int enabled = 0;
                int withData = 0;
                int built = 0;
                StringBuilder body = new StringBuilder();
                for (Module module : entry.getValue()) {
                    long oldRows = countRows(old, module.table());
                    boolean exists = existsInFms(fmsObjects, module.table());
                    if (module.enabled()) {
                        enabled++;
                    }
                    if (oldRows > 0) {
                        withData++;
                    }
                    if (exists) {
                        built++;
                    }
                    body.append("  ")
                            .append(pad(module.table(), 26))
                            .append(pad(module.name(), 24))
                            .append(pad(module.enabled() ? "启用" : "停用", 6))
                            .append(pad(oldRows < 0 ? "-" : String.valueOf(oldRows), 10))
                            .append(exists ? "已建" : "-")
                            .append(System.lineSeparator());
                }
                total += entry.getValue().size();
                totalEnabled += enabled;
                totalWithData += withData;
                totalBuilt += built;
                out.println();
                out.println("[" + groupIndex + "] " + entry.getKey() + " —— " + entry.getValue().size()
                        + " 张（启用 " + enabled + "、有数据 " + withData + "、新库已建 " + built + "）");
                out.print(body);
            }

            out.println();
            out.println("合计 " + total + " 张（启用 " + totalEnabled + "、有数据 " + totalWithData
                    + "、新库已建 " + totalBuilt + "）");
        }
        System.out.println("输出已写入: " + output);
    }

    private static long countRows(Connection old, String table) {
        for (String name : List.of(table, "b_" + table, "v_" + table)) {
            try (Statement statement = old.createStatement();
                 ResultSet rows = statement.executeQuery(
                         "select count(*) from dbo.[" + name.replace("]", "]]") + "]")) {
                rows.next();
                return rows.getLong(1);
            } catch (Exception ignored) {
                // 试下一个候选名
            }
        }
        return -1;
    }

    private static Set<String> fmsObjectNames(Connection fms) throws Exception {
        Set<String> names = new HashSet<>();
        try (Statement statement = fms.createStatement();
             ResultSet rows = statement.executeQuery(
                     "select name from sys.objects where type in ('U', 'V')")) {
            while (rows.next()) {
                names.add(rows.getString(1).toLowerCase(Locale.ROOT));
            }
        }
        return names;
    }

    private static boolean existsInFms(Set<String> fmsObjects, String table) {
        return fmsObjects.contains(table.toLowerCase(Locale.ROOT))
                || fmsObjects.contains(("v_" + table).toLowerCase(Locale.ROOT));
    }

    // --------------------------------------------------------- columns 模式

    /** 导出 base_otherdata 各字典表在旧库 s_columnLib 的列配置（列表列 / 表单字段） */
    private static void listColumns() throws Exception {
        Path output = MIGRATION_DIR.resolve("otherdata-columns.txt");
        try (PrintStream out = newBomPrintStream(output);
             Connection fms = connect("G3HD")) {

            List<String[]> modules = new ArrayList<>();
            try (Statement statement = fms.createStatement();
                 ResultSet rows = statement.executeQuery(
                         "select b_save_table, b_name from dbo.s_module "
                                 + "where b_path like '/base/base_otherdata/%' and b_module_type = 'data' "
                                 + "order by b_xh, b_id")) {
                while (rows.next()) {
                    modules.add(new String[] {rows.getString(1), rows.getString(2)});
                }
            }

            out.println("=== 旧库 s_columnLib 配置（base_otherdata 各字典） ===");
            out.println("  列表=col_Visible（1 进列表）  表单=col_edit（1 进表单）  宽=col_Width  位置=col_Position  表单位置=col_edit_position");

            int total = 0;
            int inList = 0;
            int inEdit = 0;
            for (String[] module : modules) {
                String table = module[0];
                out.println();
                out.println("--- " + table + "（" + module[1] + "）---");
                int count = 0;
                try (PreparedStatement statement = fms.prepareStatement(
                        "select col_FieldName, col_Caption, col_Visible, col_edit, col_Width, col_Position, col_edit_position "
                                + "from G3HY2025.dbo.s_columnLib where col_TableName = ? "
                                + "order by isnull(col_Position, 9999), col_FieldName")) {
                    statement.setString(1, table);
                    try (ResultSet rows = statement.executeQuery()) {
                        while (rows.next()) {
                            count++;
                            String visible = text(rows.getString(3));
                            String edit = text(rows.getString(4));
                            Object width = rows.getObject(5);
                            Object position = rows.getObject(6);
                            Object editPosition = rows.getObject(7);
                            if ("1".equals(visible)) {
                                inList++;
                            }
                            if ("1".equals(edit)) {
                                inEdit++;
                            }
                            out.println("  " + pad(rows.getString(1), 24) + pad(rows.getString(2), 20)
                                    + "列表=" + pad(visible, 5) + "表单=" + pad(edit, 5)
                                    + "宽=" + pad(width == null ? "-" : String.valueOf(width), 7)
                                    + "位置=" + pad(position == null ? "-" : String.valueOf(position), 7)
                                    + "表单位置=" + (editPosition == null ? "-" : String.valueOf(editPosition)));
                        }
                    }
                }
                total += count;
                if (count == 0) {
                    out.println("  （旧库无配置）");
                }
            }

            out.println();
            out.println("合计: " + modules.size() + " 个模块、" + total + " 条字段配置（进列表 " + inList
                    + "、进表单 " + inEdit + "）");
        }
        System.out.println("输出已写入: " + output);
    }

    private static String text(String value) {
        return value == null ? "" : value.trim();
    }

    // ---------------------------------------------------------- tables 模式

    private static void probeTables(String[] tables) throws Exception {
        Path output = MIGRATION_DIR.resolve("otherdata-probe-out.txt");
        try (PrintStream out = newBomPrintStream(output);
             Connection old = connect("G3HY2025");
             Connection fms = connect("G3HD")) {

            out.println("=== 新库 FMS：表结构 ===");
            for (String table : tables) {
                out.println();
                out.println("--- " + table + " ---");
                columns(fms, out, table);
            }

            out.println();
            out.println("=== 旧库 G3HY2025：表结构 ===");
            for (String table : tables) {
                out.println();
                out.println("--- " + table + " ---");
                columns(old, out, table);
            }

            out.println();
            out.println("=== 旧库 G3HY2025：数据样例（每表前 3 行） ===");
            for (String table : tables) {
                out.println();
                out.println("--- " + table + " ---");
                query(old, out, "select top 3 * from dbo.[" + table.replace("]", "]]") + "]");
            }
        }
        System.out.println("输出已写入: " + output);
    }

    private static void columns(Connection connection, PrintStream out, String table) {
        try (Statement statement = connection.createStatement();
             ResultSet rows = statement.executeQuery(
                     "select name, type_name(user_type_id) as t, max_length, is_nullable from sys.columns "
                             + "where object_id = object_id('dbo.[" + table.replace("]", "]]") + "]') order by column_id")) {
            int count = 0;
            while (rows.next()) {
                count++;
                out.println("  " + pad(rows.getString(1), 24) + pad(rows.getString(2), 14)
                        + pad(String.valueOf(rows.getInt(3)), 8) + (rows.getBoolean(4) ? "null" : "not null"));
            }
            if (count == 0) {
                out.println("  （表不存在）");
            }
        } catch (Exception e) {
            out.println("  读取失败: " + e.getMessage());
        }
    }

    private static void query(Connection connection, PrintStream out, String sql) {
        try (Statement statement = connection.createStatement();
             ResultSet rows = statement.executeQuery(sql)) {
            ResultSetMetaData meta = rows.getMetaData();
            StringBuilder header = new StringBuilder("  ");
            for (int i = 1; i <= meta.getColumnCount(); i++) {
                header.append(pad(meta.getColumnLabel(i), 20));
            }
            out.println(header.toString().stripTrailing());
            int count = 0;
            while (rows.next()) {
                count++;
                StringBuilder line = new StringBuilder("  ");
                for (int i = 1; i <= meta.getColumnCount(); i++) {
                    Object value = rows.getObject(i);
                    String text = value == null ? "(null)" : String.valueOf(value).replaceAll("\\s+", " ");
                    if (text.length() > 60) {
                        text = text.substring(0, 60) + "…";
                    }
                    line.append(pad(text, 20));
                }
                out.println(line.toString().stripTrailing());
            }
            out.println("  (" + count + " 行)");
        } catch (Exception e) {
            out.println("  查询失败: " + e.getMessage());
        }
    }

    // -------------------------------------------------------------- 基础设施

    private static PrintStream newBomPrintStream(Path output) throws Exception {
        FileOutputStream file = new FileOutputStream(output.toFile());
        file.write(0xEF);
        file.write(0xBB);
        file.write(0xBF);
        return new PrintStream(file, true, StandardCharsets.UTF_8);
    }

    private static Connection connect(String org) throws Exception {
        Path apiRoot = Path.of("").toAbsolutePath().normalize();
        Properties properties = new Properties();
        try (InputStream input = Files.newInputStream(apiRoot.resolve("config/dbconfigs/" + org + ".properties"))) {
            properties.load(input);
        }
        return DriverManager.getConnection(
                properties.getProperty("url"),
                properties.getProperty("username"),
                properties.getProperty("password"));
    }

    private static String pad(String value, int width) {
        String text = value == null ? "" : value;
        int display = text.length();
        while (display < width) {
            text += " ";
            display++;
        }
        return text;
    }
}