import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class ApplyBindingTargetAccountsMigration {

    private static final String DEFAULT_HOST = "cq-cdb-8fjmemkb.sql.tencentcdb.com";
    private static final int DEFAULT_PORT = 27220;
    private static final String DEFAULT_USER = "root";
    private static final String DEFAULT_PASSWORD = "Ylrz_1q2w3e4r5t6y";
    private static final String MASTER_DB = "ylrz_saas";

    private static final String[][] COLUMNS = {
            {"target_qw_user_ids", "VARCHAR(2000) NULL COMMENT '限定企微员工ID' AFTER target_channels"},
            {"target_wx_account_ids", "VARCHAR(2000) NULL COMMENT '限定个微账号ID' AFTER target_qw_user_ids"}
    };

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        List<TenantDb> tenants = new ArrayList<>();
        try (Connection master = connect(DEFAULT_HOST, DEFAULT_PORT, MASTER_DB, DEFAULT_USER, DEFAULT_PASSWORD)) {
            try (Statement st = master.createStatement();
                 ResultSet rs = st.executeQuery(
                         "SELECT id, db_name, db_url, db_account, db_pwd, status FROM tenant_info ORDER BY id")) {
                while (rs.next()) {
                    if (rs.getInt("status") != 1) continue;
                    TenantDb t = new TenantDb();
                    t.id = rs.getLong("id");
                    t.dbUrl = rs.getString("db_url");
                    t.dbName = rs.getString("db_name");
                    t.user = rs.getString("db_account");
                    t.password = rs.getString("db_pwd");
                    parseJdbc(t);
                    tenants.add(t);
                }
            }
        }
        for (TenantDb tenant : tenants) {
            apply(tenant);
        }
        System.out.println("Done.");
    }

    private static void apply(TenantDb tenant) throws Exception {
        try (Connection conn = connect(tenant.host, tenant.port, tenant.database, tenant.user, tenant.password);
             Statement st = conn.createStatement()) {
            for (String[] col : COLUMNS) {
                String name = col[0];
                String ddl = col[1];
                boolean exists;
                try (ResultSet rs = st.executeQuery(
                        "SELECT COUNT(*) FROM information_schema.COLUMNS "
                                + "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'company_tag_template_binding' "
                                + "AND COLUMN_NAME = '" + name + "'")) {
                    rs.next();
                    exists = rs.getInt(1) > 0;
                }
                if (exists) {
                    System.out.println("[SKIP] tenantId=" + tenant.id + " col=" + name);
                    continue;
                }
                st.execute("ALTER TABLE company_tag_template_binding ADD COLUMN " + name + " " + ddl);
                System.out.println("[OK] tenantId=" + tenant.id + " col=" + name + " db=" + tenant.database);
            }
        }
    }

    private static void parseJdbc(TenantDb t) {
        if (t.dbUrl != null && t.dbUrl.startsWith("jdbc:mysql://")) {
            String body = t.dbUrl.substring("jdbc:mysql://".length());
            int slash = body.indexOf('/');
            String hostPort = slash >= 0 ? body.substring(0, slash) : body;
            String rest = slash >= 0 ? body.substring(slash + 1) : t.dbName;
            int q = rest.indexOf('?');
            t.database = q >= 0 ? rest.substring(0, q) : rest;
            int colon = hostPort.indexOf(':');
            if (colon >= 0) {
                t.host = hostPort.substring(0, colon);
                t.port = Integer.parseInt(hostPort.substring(colon + 1));
            } else {
                t.host = hostPort;
                t.port = 3306;
            }
        } else {
            t.host = DEFAULT_HOST;
            t.port = DEFAULT_PORT;
            t.database = t.dbName;
        }
        if (t.user == null || t.user.isEmpty()) t.user = DEFAULT_USER;
        if (t.password == null) t.password = DEFAULT_PASSWORD;
    }

    private static Connection connect(String host, int port, String db, String user, String password) throws Exception {
        String url = "jdbc:mysql://" + host + ":" + port + "/" + db
                + "?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=true";
        return DriverManager.getConnection(url, user, password);
    }

    private static class TenantDb {
        long id;
        String dbUrl;
        String dbName;
        String host;
        int port;
        String database;
        String user;
        String password;
    }
}