package com.agent.management.service.impl;

import com.agent.management.common.exception.BusinessException;
import com.agent.management.config.SqlConsoleProperties;
import com.agent.management.model.entity.DataSource;
import com.agent.management.repository.SqlQueryHistoryRepository;
import com.agent.management.model.entity.SqlQueryHistory;
import com.agent.management.service.DataSourceService;
import com.agent.management.service.SchemaExplorerService;
import com.agent.management.service.SqlGuardService;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

/**
 * SchemaExplorerService 实现
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class SchemaExplorerServiceImpl implements SchemaExplorerService {

    private final DataSourceService dataSourceService;
    private final com.agent.management.service.DynamicJdbcService jdbcService;
    private final SqlGuardService sqlGuard;
    private final SqlConsoleProperties props;
    private final SqlQueryHistoryRepository historyRepository;

    @Override
    public List<String> listSchemas(Long datasourceId) {
        DataSource ds = dataSourceService.getDecrypted(datasourceId);
        try (Connection conn = jdbcService.getConnection(ds)) {
            List<String> result = new ArrayList<>();
            try (var rs = conn.getMetaData().getSchemas()) {
                while (rs.next()) {
                    String name = rs.getString("TABLE_SCHEM");
                    if (!isSystemSchema(name)) result.add(name);
                }
            }
            return result;
        } catch (SQLException e) {
            throw new BusinessException("列出 schema 失败: " + e.getMessage());
        }
    }

    @Override
    public List<TableSummary> listTables(Long datasourceId, String schema) {
        DataSource ds = dataSourceService.getDecrypted(datasourceId);
        try (Connection conn = jdbcService.getConnection(ds)) {
            List<TableSummary> result = new ArrayList<>();
            try (var rs = conn.getMetaData().getTables(
                    conn.getCatalog(), resolveSchema(conn, ds, schema),
                    "%", new String[]{"TABLE", "VIEW"})) {
                while (rs.next()) {
                    result.add(new TableSummary(
                            rs.getString("TABLE_NAME"),
                            rs.getString("TABLE_TYPE"),
                            rs.getString("REMARKS")
                    ));
                }
            }
            return result;
        } catch (SQLException e) {
            throw new BusinessException("列出表失败: " + e.getMessage());
        }
    }

    @Override
    public List<ColumnSpec> describeTable(Long datasourceId, String schema, String table) {
        if (table == null || table.trim().isEmpty()) {
            throw new BusinessException("table 名不能为空");
        }
        DataSource ds = dataSourceService.getDecrypted(datasourceId);
        try (Connection conn = jdbcService.getConnection(ds)) {
            List<ColumnSpec> result = new ArrayList<>();
            try (var rs = conn.getMetaData().getColumns(
                    conn.getCatalog(), resolveSchema(conn, ds, schema), table, "%")) {
                while (rs.next()) {
                    result.add(new ColumnSpec(
                            rs.getString("COLUMN_NAME"),
                            rs.getString("TYPE_NAME"),
                            rs.getInt("COLUMN_SIZE"),
                            rs.getInt("NULLABLE") == 1,
                            rs.getString("REMARKS")
                    ));
                }
            }
            return result;
        } catch (SQLException e) {
            throw new BusinessException("描述表字段失败: " + e.getMessage());
        }
    }

    @Override
    public List<ForeignKeySpec> listForeignKeys(Long datasourceId, String schema, String table) {
        DataSource ds = dataSourceService.getDecrypted(datasourceId);
        try (Connection conn = jdbcService.getConnection(ds)) {
            List<ForeignKeySpec> result = new ArrayList<>();
            try (var rs = conn.getMetaData().getImportedKeys(conn.getCatalog(), resolveSchema(conn, ds, schema), table)) {
                while (rs.next()) result.add(new ForeignKeySpec(rs.getString("FK_NAME"), rs.getString("FKTABLE_NAME"),
                        rs.getString("FKCOLUMN_NAME"), rs.getString("PKTABLE_NAME"), rs.getString("PKCOLUMN_NAME")));
            }
            return result;
        } catch (SQLException e) { throw new BusinessException("读取外键失败: " + e.getMessage()); }
    }

    @Override
    public long countRows(Long datasourceId, String schema, String table) {
        if (table == null || table.trim().isEmpty()) {
            throw new BusinessException("table 名不能为空");
        }
        DataSource ds = dataSourceService.getDecrypted(datasourceId);
        try (Connection conn = jdbcService.getConnection(ds)) {
            String sql = "SELECT COUNT(*) FROM " + quoteIdentifier(table, ds);
            try (Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(sql)) {
                return rs.next() ? rs.getLong(1) : 0;
            }
        } catch (SQLException e) {
            throw new BusinessException("统计行数失败: " + e.getMessage());
        }
    }

    @Override
    public QueryResult previewTableData(Long datasourceId, String schema, String table, Integer limit) {
        if (table == null || table.trim().isEmpty()) {
            throw new BusinessException("table 名不能为空");
        }
        DataSource ds = dataSourceService.getDecrypted(datasourceId);
        String quotedTable = quoteIdentifier(table, ds);
        // 支持 schema/catalog 前缀（PG/H2/Oracle/DM 用 schema，MySQL 用 catalog）
        String qualifiedTable = qualifyTable(quotedTable, schema, ds);
        String sql = buildPreviewSql(qualifiedTable, ds, limit);
        return executeQuery(datasourceId, sql, limit);
    }

    @Override
    public QueryResult executeQuery(Long datasourceId, String sql, Integer limitOverride) {
        sqlGuard.validate(sql);
        int maxRows = props.getMaxRows();
        int limit = (limitOverride != null && limitOverride > 0) ? Math.min(limitOverride, maxRows) : maxRows;

        DataSource ds = dataSourceService.getDecrypted(datasourceId);
        long start = System.currentTimeMillis();

        SqlQueryHistory history = new SqlQueryHistory();
        history.setDatasourceId(datasourceId);
        history.setSql(sql.length() > 4000 ? sql.substring(0, 4000) : sql);

        try (Connection conn = jdbcService.getConnection(ds)) {
            try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                stmt.setQueryTimeout(props.getQueryTimeoutSeconds());
                stmt.setMaxRows(limit + 1);  // +1 用于检测截断
                boolean hasRs = stmt.execute();
                if (!hasRs) {
                    // 一般不会走到这里（SqlGuard 禁止 DML/DDL）
                    long elapsed = System.currentTimeMillis() - start;
                    history.setStatus("SUCCESS");
                    history.setRowCount(0);
                    history.setExecutionTimeMs(elapsed);
                    historyRepository.save(history);
                    return new QueryResult(List.of(), List.of(), elapsed);
                }
                try (ResultSet rs = stmt.getResultSet()) {
                    ResultSetMetaData meta = rs.getMetaData();
                    int colCount = meta.getColumnCount();
                    List<String> columns = new ArrayList<>(colCount);
                    for (int i = 1; i <= colCount; i++) {
                        columns.add(meta.getColumnLabel(i));
                    }
                    List<List<Object>> rows = new ArrayList<>(Math.min(limit, 1000));
                    int rowCount = 0;
                    while (rs.next() && rowCount < limit) {
                        List<Object> row = new ArrayList<>(colCount);
                        for (int i = 1; i <= colCount; i++) {
                            row.add(readColumn(rs, i, meta.getColumnType(i)));
                        }
                        rows.add(row);
                        rowCount++;
                    }
                    long elapsed = System.currentTimeMillis() - start;
                    log.info("查询执行完成 ds={} rows={} 耗时={}ms", datasourceId, rowCount, elapsed);

                    history.setStatus("SUCCESS");
                    history.setRowCount(rowCount);
                    history.setExecutionTimeMs(elapsed);
                    historyRepository.save(history);

                    return new QueryResult(columns, rows, elapsed);
                }
            }
        } catch (SQLException e) {
            long elapsed = System.currentTimeMillis() - start;
            log.warn("查询执行失败 ds={} 耗时={}ms err={}", datasourceId, elapsed, e.getMessage());
            history.setStatus("FAILED");
            history.setExecutionTimeMs(elapsed);
            history.setErrorMessage(e.getMessage());
            historyRepository.save(history);
            throw new BusinessException("查询执行失败: " + e.getMessage());
        }
    }

    // =========================== 内部 ===========================

    private boolean isSystemSchema(String name) {
        if (name == null) return false;
        for (String blocked : props.getForbiddenSystemSchemas()) {
            if (blocked.equalsIgnoreCase(name)) return true;
        }
        return false;
    }

    /**
     * 解析 schema 名（兼容不同 DB：H2/PG 用 schema，MySQL 用 catalog）
     */
    private String resolveSchema(Connection conn, DataSource ds, String input) throws SQLException {
        if (input != null && !input.trim().isEmpty()) return input;
        if (ds.getSchemaName() != null && !ds.getSchemaName().isEmpty()) return ds.getSchemaName();
        return null;
    }

    /**
     * 简单标识符包装（防止保留字冲突）
     */
    private String quoteIdentifier(String name, DataSource ds) {
        if (name == null) return "";
        String type = ds.getType() == null ? "" : ds.getType().toUpperCase();
        switch (type) {
            case "MYSQL":
            case "SQLSERVER":
                return "`" + name.replace("`", "") + "`";
            case "POSTGRESQL":
            case "ORACLE":
            case "H2":
            case "DAMENG":
            case "DM":
                return "\"" + name.replace("\"", "") + "\"";
            default:
                return name;
        }
    }

    /**
     * 构造带 schema/catalog 前缀的表名
     */
    private String qualifyTable(String quotedTable, String schema, DataSource ds) {
        String prefix = schema != null && !schema.trim().isEmpty()
                ? schema.trim()
                : ds.getSchemaName();
        if (prefix == null || prefix.isEmpty()) return quotedTable;
        String type = ds.getType() == null ? "" : ds.getType().toUpperCase();
        // MySQL/SQLServer 用 catalog.db.table 时 catalog 与 table 间用点号
        String quotedPrefix = quoteIdentifier(prefix, ds);
        return quotedPrefix + "." + quotedTable;
    }

    /**
     * 按数据库类型生成安全的 SELECT 预览 SQL
     */
    private String buildPreviewSql(String qualifiedTable, DataSource ds, Integer limit) {
        String type = ds.getType() == null ? "" : ds.getType().toUpperCase();
        int n = limit != null && limit > 0 ? limit : 100;
        return switch (type) {
            case "SQLSERVER" -> "SELECT TOP " + n + " * FROM " + qualifiedTable;
            case "ORACLE" -> "SELECT * FROM " + qualifiedTable + " FETCH FIRST " + n + " ROWS ONLY";
            default -> "SELECT * FROM " + qualifiedTable + " LIMIT " + n;
        };
    }

    /**
     * 按 JDBC 类型安全读取列值（避免大对象异常）
     */
    private Object readColumn(ResultSet rs, int idx, int sqlType) throws SQLException {
        switch (sqlType) {
            case Types.BLOB:
            case Types.BINARY:
            case Types.VARBINARY:
            case Types.LONGVARBINARY:
                return "[BINARY]";
            case Types.CLOB:
            case Types.NCLOB:
                return rs.getString(idx);
            default:
                Object obj = rs.getObject(idx);
                return obj;
        }
    }
}
