package com.rongyichuang.tools;

import org.junit.jupiter.api.Test;

import java.sql.*;
import java.nio.file.*;
import java.util.HashMap;
import java.util.Map;

public class SchemaDumpTest {

  @Test
  public void dump() {
    String url = "jdbc:mysql://140.143.152.226:3308/ryc?useSSL=false&useUnicode=true&characterEncoding=utf8";
    String user = "openai";
    String pass = "Xml@uk2025";

    StringBuilder sb = new StringBuilder();
    sb.append("-- Database schema dump for ryc\n");

    try (Connection conn = DriverManager.getConnection(url, user, pass)) {
      System.out.println("[SchemaDump] Connected to MySQL.");

      String sqlTables = "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='ryc' ORDER BY TABLE_NAME";
      String sqlCols = "SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT, EXTRA, ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='ryc' ORDER BY TABLE_NAME, ORDINAL_POSITION";

      // Collect table comments
      Map<String, String> tableComments = new HashMap<>();
      try (Statement st = conn.createStatement(); ResultSet rt = st.executeQuery(sqlTables)) {
        while (rt.next()) {
          tableComments.put(rt.getString("TABLE_NAME"), rt.getString("TABLE_COMMENT"));
        }
      }

      try (Statement st = conn.createStatement(); ResultSet rc = st.executeQuery(sqlCols)) {
        String currentTable = null;
        while (rc.next()) {
          String table = rc.getString("TABLE_NAME");
          if (!table.equals(currentTable)) {
            // close previous table
            if (currentTable != null) {
              int lastComma = sb.lastIndexOf(",\n");
              if (lastComma >= 0) sb.delete(lastComma, lastComma + 2);
              sb.append("\n);\n");
            }
            currentTable = table;

            String tComment = tableComments.getOrDefault(table, "");
            sb.append("\n-- Table: ").append(table);
            if (tComment != null && !tComment.isEmpty()) {
              sb.append("  -- ").append(tComment);
            }
            sb.append("\n");
            sb.append("CREATE TABLE ").append(table).append(" (\n");
          }

          String col = rc.getString("COLUMN_NAME");
          String type = rc.getString("COLUMN_TYPE");
          String nullable = rc.getString("IS_NULLABLE");
          String def = rc.getString("COLUMN_DEFAULT");
          String comment = rc.getString("COLUMN_COMMENT");
          String extra = rc.getString("EXTRA");

          sb.append("  `").append(col).append("` ").append(type);
          if ("NO".equals(nullable)) sb.append(" NOT NULL");
          if (def != null) {
            sb.append(" DEFAULT '").append(def.replace("'", "''")).append("'");
          }
          if (extra != null && !extra.isEmpty()) sb.append(" ").append(extra);
          if (comment != null && !comment.isEmpty()) {
            sb.append(" COMMENT '").append(comment.replace("'", "''")).append("'");
          }
          sb.append(",\n");
        }
        if (currentTable != null) {
          int lastComma = sb.lastIndexOf(",\n");
          if (lastComma >= 0) sb.delete(lastComma, lastComma + 2);
          sb.append("\n);\n");
        }
      }

    } catch (Exception e) {
      System.err.println("[SchemaDump] Error: " + e.getMessage());
      // 不中断，仍尝试写入已收集内容
    }

    try {
      Path out = Paths.get("d:/code/new-ryc/db.sql");
      Files.writeString(out, sb.toString());
      System.out.println("[SchemaDump] Written to " + out.toString());
    } catch (Exception writeErr) {
      System.err.println("[SchemaDump] Write file error: " + writeErr.getMessage());
    }
  }
}