package com.easyagents.scheduler.quartz;

import com.easyagents.scheduler.ScheduleErrorCode;
import com.easyagents.scheduler.ScheduleException;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Objects;
import java.util.Set;

/**
 * Quartz JDBC JobStore 必需结构的只读校验器。
 */
public final class QuartzSchemaValidator {

    private static final List<String> REQUIRED_SUFFIXES = List.of(
        "JOB_DETAILS",
        "TRIGGERS",
        "SIMPLE_TRIGGERS",
        "CRON_TRIGGERS",
        "SIMPROP_TRIGGERS",
        "BLOB_TRIGGERS",
        "CALENDARS",
        "PAUSED_TRIGGER_GRPS",
        "FIRED_TRIGGERS",
        "SCHEDULER_STATE",
        "LOCKS"
    );

    private static final Map<String, Set<String>> REQUIRED_COLUMNS = Map.ofEntries(
        Map.entry("JOB_DETAILS", Set.of(
            "SCHED_NAME", "JOB_NAME", "JOB_GROUP", "DESCRIPTION", "JOB_CLASS_NAME",
            "IS_DURABLE", "IS_NONCONCURRENT", "IS_UPDATE_DATA", "REQUESTS_RECOVERY",
            "JOB_DATA"
        )),
        Map.entry("TRIGGERS", Set.of(
            "SCHED_NAME", "TRIGGER_NAME", "TRIGGER_GROUP", "JOB_NAME", "JOB_GROUP",
            "DESCRIPTION", "NEXT_FIRE_TIME", "PREV_FIRE_TIME", "PRIORITY", "TRIGGER_STATE",
            "TRIGGER_TYPE", "START_TIME", "END_TIME", "CALENDAR_NAME", "MISFIRE_INSTR",
            "JOB_DATA"
        )),
        Map.entry("SIMPLE_TRIGGERS", Set.of(
            "SCHED_NAME", "TRIGGER_NAME", "TRIGGER_GROUP", "REPEAT_COUNT",
            "REPEAT_INTERVAL", "TIMES_TRIGGERED"
        )),
        Map.entry("CRON_TRIGGERS", Set.of(
            "SCHED_NAME", "TRIGGER_NAME", "TRIGGER_GROUP", "CRON_EXPRESSION", "TIME_ZONE_ID"
        )),
        Map.entry("SIMPROP_TRIGGERS", Set.of(
            "SCHED_NAME", "TRIGGER_NAME", "TRIGGER_GROUP", "STR_PROP_1", "STR_PROP_2",
            "STR_PROP_3", "INT_PROP_1", "INT_PROP_2", "LONG_PROP_1", "LONG_PROP_2",
            "DEC_PROP_1", "DEC_PROP_2", "BOOL_PROP_1", "BOOL_PROP_2"
        )),
        Map.entry("BLOB_TRIGGERS", Set.of(
            "SCHED_NAME", "TRIGGER_NAME", "TRIGGER_GROUP", "BLOB_DATA"
        )),
        Map.entry("CALENDARS", Set.of("SCHED_NAME", "CALENDAR_NAME", "CALENDAR")),
        Map.entry("PAUSED_TRIGGER_GRPS", Set.of("SCHED_NAME", "TRIGGER_GROUP")),
        Map.entry("FIRED_TRIGGERS", Set.of(
            "SCHED_NAME", "ENTRY_ID", "TRIGGER_NAME", "TRIGGER_GROUP", "INSTANCE_NAME",
            "FIRED_TIME", "SCHED_TIME", "PRIORITY", "STATE", "JOB_NAME", "JOB_GROUP",
            "IS_NONCONCURRENT", "REQUESTS_RECOVERY"
        )),
        Map.entry("SCHEDULER_STATE", Set.of(
            "SCHED_NAME", "INSTANCE_NAME", "LAST_CHECKIN_TIME", "CHECKIN_INTERVAL"
        )),
        Map.entry("LOCKS", Set.of("SCHED_NAME", "LOCK_NAME"))
    );

    private QuartzSchemaValidator() {
    }

    /**
     * 校验指定 DataSource 中的 Quartz 表和关键列。
     *
     * @param dataSource 调用方 DataSource
     * @param tablePrefix Quartz 表前缀，可包含 Schema
     * @throws ScheduleException 表或关键列缺失、元数据读取失败时抛出
     */
    public static void validate(DataSource dataSource, String tablePrefix) {
        Objects.requireNonNull(dataSource, "dataSource must not be null");
        PrefixParts prefixParts = PrefixParts.parse(tablePrefix);
        try (Connection connection = dataSource.getConnection()) {
            DatabaseMetaData metadata = connection.getMetaData();
            String databaseProduct = metadata.getDatabaseProductName();
            MetadataScope metadataScope = resolveMetadataScope(
                connection,
                metadata,
                prefixParts.schema()
            );
            Map<String, String> actualTables = loadTables(
                metadata,
                metadataScope
            );
            List<String> missingTables = new ArrayList<>();
            for (String suffix : REQUIRED_SUFFIXES) {
                String expected = normalize(prefixParts.tablePrefix() + suffix);
                if (!actualTables.containsKey(expected)) {
                    missingTables.add(prefixParts.displayPrefix() + suffix);
                }
            }
            if (!missingTables.isEmpty()) {
                throw invalid(
                    "missing Quartz tables in " + databaseProduct + ": "
                        + String.join(", ", missingTables)
                );
            }
            validateColumns(
                metadata,
                metadataScope,
                prefixParts,
                actualTables,
                databaseProduct
            );
        } catch (SQLException exception) {
            throw new ScheduleException(
                ScheduleErrorCode.SCHEMA_INVALID,
                "failed to inspect Quartz schema for prefix " + tablePrefix,
                exception
            );
        }
    }

    /**
     * 解析 JDBC 元数据查询使用的 Catalog 与 Schema。
     *
     * @param connection 当前数据库连接
     * @param metadata JDBC 元数据
     * @param declaredSchema 表前缀中显式声明的 Schema，可为空
     * @return 仅覆盖 Quartz 实际查询范围的元数据作用域
     * @throws SQLException 读取连接或数据库能力失败时抛出
     */
    private static MetadataScope resolveMetadataScope(
        Connection connection,
        DatabaseMetaData metadata,
        String declaredSchema
    ) throws SQLException {
        if (
            !metadata.supportsSchemasInTableDefinitions()
                && metadata.supportsCatalogsInTableDefinitions()
        ) {
            String catalog = declaredSchema == null ? connection.getCatalog() : declaredSchema;
            return new MetadataScope(catalog, null);
        }
        String schema = declaredSchema == null ? connection.getSchema() : declaredSchema;
        return new MetadataScope(connection.getCatalog(), schema);
    }

    /**
     * 加载指定作用域内的数据库表。
     *
     * @param metadata JDBC 元数据
     * @param metadataScope Catalog 与 Schema 作用域
     * @return 规范化表名到实际表名的映射
     * @throws SQLException 元数据读取失败时抛出
     */
    private static Map<String, String> loadTables(
        DatabaseMetaData metadata,
        MetadataScope metadataScope
    ) throws SQLException {
        Map<String, String> tables = new HashMap<>();
        try (ResultSet resultSet = metadata.getTables(
            metadataScope.catalog(),
            metadataScope.schema(),
            null,
            new String[]{"TABLE"}
        )) {
            while (resultSet.next()) {
                String tableName = resultSet.getString("TABLE_NAME");
                if (tableName != null) {
                    tables.put(normalize(tableName), tableName);
                }
            }
        }
        return tables;
    }

    /**
     * 校验指定作用域内 Quartz 表的必需列。
     *
     * @param metadata JDBC 元数据
     * @param metadataScope Catalog 与 Schema 作用域
     * @param prefixParts Quartz 表前缀
     * @param actualTables 已发现表
     * @param databaseProduct 数据库产品名
     * @throws SQLException 元数据读取失败时抛出
     */
    private static void validateColumns(
        DatabaseMetaData metadata,
        MetadataScope metadataScope,
        PrefixParts prefixParts,
        Map<String, String> actualTables,
        String databaseProduct
    ) throws SQLException {
        for (Map.Entry<String, Set<String>> requirement : REQUIRED_COLUMNS.entrySet()) {
            String normalizedName = normalize(prefixParts.tablePrefix() + requirement.getKey());
            String actualName = actualTables.get(normalizedName);
            Set<String> actualColumns = new HashSet<>();
            try (ResultSet resultSet = metadata.getColumns(
                metadataScope.catalog(),
                metadataScope.schema(),
                actualName,
                null
            )) {
                while (resultSet.next()) {
                    actualColumns.add(normalize(resultSet.getString("COLUMN_NAME")));
                }
            }
            Set<String> missingColumns = new HashSet<>(requirement.getValue());
            missingColumns.removeAll(actualColumns);
            if (!missingColumns.isEmpty()) {
                throw invalid(
                    "missing columns in " + databaseProduct + " table "
                        + prefixParts.displayPrefix() + requirement.getKey() + ": "
                        + String.join(", ", missingColumns)
                );
            }
        }
    }

    private static ScheduleException invalid(String message) {
        return new ScheduleException(ScheduleErrorCode.SCHEMA_INVALID, message);
    }

    private static String normalize(String value) {
        return value == null ? "" : value.toUpperCase(Locale.ROOT);
    }

    /** JDBC 元数据的 Catalog 与 Schema 查询范围。 */
    private record MetadataScope(String catalog, String schema) {
    }

    private record PrefixParts(String schema, String tablePrefix, String displayPrefix) {

        private static PrefixParts parse(String value) {
            if (value == null || value.isBlank()) {
                throw new IllegalArgumentException("tablePrefix must not be blank");
            }
            String normalized = value.trim();
            int separator = normalized.lastIndexOf('.');
            if (separator < 0) {
                return new PrefixParts(null, normalized, normalized);
            }
            String schema = normalized.substring(0, separator);
            String prefix = normalized.substring(separator + 1);
            if (schema.isBlank() || prefix.isBlank()) {
                throw new IllegalArgumentException("invalid tablePrefix: " + value);
            }
            return new PrefixParts(schema, prefix, normalized);
        }
    }
}
