import java.io.InputStream;
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.Statement;
import java.util.Properties;

public final class ApplySimpleModuleRestore {
    public static void main(String[] args) throws Exception {
        Path apiRoot = Path.of("").toAbsolutePath().normalize();
        Path configPath = apiRoot.resolve("config/dbconfigs/g3hd.properties").normalize();
        Path migrationPath = apiRoot.resolve("config/migrations/035_restore_simple_module_tree.sql").normalize();

        Properties properties = new Properties();
        try (InputStream input = Files.newInputStream(configPath)) {
            properties.load(input);
        }

        try (Connection connection = DriverManager.getConnection(
                required(properties, "url"),
                required(properties, "username"),
                required(properties, "password"))) {
            if (!"FMS".equalsIgnoreCase(connection.getCatalog())) {
                throw new IllegalStateException("Refusing to restore database: " + connection.getCatalog());
            }
            if (args.length == 1 && "--verify-only".equals(args[0])) {
                verify(connection);
                return;
            }
            connection.setAutoCommit(false);
            try {
                String migration = Files.readString(migrationPath, StandardCharsets.UTF_8);
                int batches = executeBatches(connection, migration);
                verify(connection);
                connection.commit();
                System.out.println("APPLIED|035_restore_simple_module_tree.sql|batches=" + batches);
            } catch (Exception exception) {
                connection.rollback();
                throw exception;
            }
        }
    }

    private static int executeBatches(Connection connection, String migration) throws Exception {
        String[] batches = migration.split("(?im)^\\s*GO\\s*(?:--.*)?$");
        int count = 0;
        for (String batch : batches) {
            if (batch.isBlank()) continue;
            try (Statement statement = connection.createStatement()) {
                statement.execute(batch);
            }
            count++;
        }
        return count;
    }

    private static void verify(Connection connection) throws Exception {
        String sql = """
                SELECT
                    CASE WHEN COL_LENGTH(N'dbo.s_module', N'b_parent_id') IS NULL THEN 0 ELSE 1 END,
                    CASE WHEN OBJECT_ID(N'dbo.s_module_page', N'U') IS NULL THEN 0 ELSE 1 END,
                    (SELECT COUNT(*) FROM dbo.s_module WHERE b_module_type = 'data_category'),
                    (SELECT COUNT(*)
                       FROM dbo.s_module child
                       LEFT JOIN dbo.s_module parent ON parent.b_id = child.b_parent_id
                      WHERE (child.b_module_type IN ('directory', 'page')
                             AND child.b_parent_id IS NOT NULL
                             AND ISNULL(parent.b_module_type, '') <> 'directory')
                         OR (child.b_module_type IN ('data_category', 'data')
                             AND ISNULL(parent.b_module_type, '') NOT IN ('page', 'data_category')))
                """;
        try (Statement statement = connection.createStatement(); ResultSet rows = statement.executeQuery(sql)) {
            rows.next();
            if (rows.getInt(1) != 1 || rows.getInt(2) != 0 || rows.getInt(4) != 0) {
                throw new IllegalStateException("Simple module tree verification failed");
            }
            System.out.println(
                    "PASS|simple_module_tree|data_categories=" + rows.getInt(3) + "|invalid_parents=0");
        }
    }

    private static String required(Properties properties, String key) {
        String value = properties.getProperty(key);
        if (value == null || value.isBlank()) throw new IllegalStateException("Missing property: " + key);
        return value;
    }
}