package com.njcn.gather.systemops.database.component;

import cn.hutool.core.util.StrUtil;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.njcn.gather.systemops.database.constant.DatabaseOpsConst;
import com.njcn.gather.systemops.database.pojo.param.DatabaseBackupParam;
import com.njcn.gather.systemops.database.util.DatabaseFileNameUtil;
import lombok.Data;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Component;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.regex.Pattern;
import java.util.function.BooleanSupplier;

/**
 * JDBC 表数据导出与恢复组件。
 */
@Component
@RequiredArgsConstructor
public class JdbcExportComponent {

    private static final Pattern IDENTIFIER_PATTERN = Pattern.compile("^[A-Za-z][A-Za-z0-9_#$]*$");
    private static final DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    private final ObjectMapper objectMapper;

    public void exportCsv(Connection jdbcConnection, String ownerName, DatabaseBackupParam.CreateParam param,
                          Path dataFilePath, Path metadataFilePath) throws Exception {
        Files.createDirectories(dataFilePath.getParent());
        if (metadataFilePath.getParent() != null) {
            Files.createDirectories(metadataFilePath.getParent());
        }
        List<TableMetadata> metadataList = new ArrayList<>();
        try (BufferedWriter writer = Files.newBufferedWriter(dataFilePath, StandardCharsets.UTF_8)) {
            for (String tableName : param.getTargetNames()) {
                metadataList.add(exportTable(jdbcConnection, ownerName, tableName, param, writer));
            }
        }
        try (BufferedWriter metadataWriter = Files.newBufferedWriter(metadataFilePath, StandardCharsets.UTF_8)) {
            objectMapper.writeValue(metadataWriter, metadataList);
        }
    }

    public void importCsv(Connection jdbcConnection, Path dataFilePath, Path metadataFilePath, String dbType,
                          String restoreMode, String targetOwnerName) throws Exception {
        String metadataText = new String(Files.readAllBytes(metadataFilePath), StandardCharsets.UTF_8);
        if (metadataText.trim().startsWith("{")) {
            importCsvV2(jdbcConnection, metadataFilePath, dbType, restoreMode, targetOwnerName);
            return;
        }
        List<TableMetadata> metadataList = Arrays.asList(objectMapper.readValue(metadataFilePath.toFile(), TableMetadata[].class));
        Map<String, TableMetadata> metadataMap = new LinkedHashMap<>();
        for (TableMetadata metadata : metadataList) {
            metadataMap.put(metadata.getFullTableName(), metadata);
        }
        jdbcConnection.setAutoCommit(false);
        try (BufferedReader reader = Files.newBufferedReader(dataFilePath, StandardCharsets.UTF_8)) {
            try {
                String line;
                TableMetadata currentMetadata = null;
                List<String> currentColumns = null;
                while ((line = reader.readLine()) != null) {
                    if (line.startsWith("-- TABLE ")) {
                        currentMetadata = metadataMap.get(line.substring("-- TABLE ".length()).trim());
                        if (currentMetadata == null) {
                            throw new IllegalArgumentException("未找到表元数据：" + line);
                        }
                        currentColumns = null;
                        prepareTargetTable(jdbcConnection, currentMetadata, dbType, restoreMode, targetOwnerName);
                        continue;
                    }
                    if (currentMetadata == null) {
                        continue;
                    }
                    if (currentColumns == null) {
                        currentColumns = parseCsvLine(line);
                        continue;
                    }
                    List<String> values = parseCsvLine(line);
                    insertRow(jdbcConnection, currentMetadata, currentColumns, values, dbType, restoreMode, targetOwnerName);
                }
                jdbcConnection.commit();
            } catch (Exception exception) {
                jdbcConnection.rollback();
                throw exception;
            }
        }
    }

    public ExportManifest exportMysqlCsvV2(Connection jdbcConnection, String databaseName, String taskNo,
                                           DatabaseBackupParam.CreateParam param, Path backupDirectory,
                                           Path metadataFilePath, int fetchSize, long maxPartBytes,
                                           BooleanSupplier cancelled) throws Exception {
        Files.createDirectories(backupDirectory);
        ExportManifest manifest = new ExportManifest();
        manifest.setVersion(2);
        manifest.setDbType("MYSQL");
        manifest.setBackupStrategy("JDBC_EXPORT");
        manifest.setTaskNo(taskNo);
        manifest.setDatabaseName(databaseName);
        List<TableExportMetadata> tableMetadataList = new ArrayList<>();
        manifest.setTables(tableMetadataList);
        for (String tableName : param.getTargetNames()) {
            checkCancelled(cancelled, backupDirectory);
            tableMetadataList.add(exportMysqlTableV2(jdbcConnection, tableName, param, backupDirectory, taskNo,
                    fetchSize, maxPartBytes, cancelled));
        }
        try (BufferedWriter metadataWriter = Files.newBufferedWriter(metadataFilePath, StandardCharsets.UTF_8)) {
            objectMapper.writeValue(metadataWriter, manifest);
        }
        return manifest;
    }

    private TableMetadata exportTable(Connection connection, String ownerName, String tableName,
                                      DatabaseBackupParam.CreateParam param, BufferedWriter writer) throws Exception {
        String normalizedOwner = normalizeOwner(ownerName);
        String normalizedTable = normalizeMysqlIdentifier(tableName);
        String fullTableName = buildFullTableName(normalizedOwner, normalizedTable);
        String querySql = buildQuerySql(fullTableName, param);
        TableMetadata metadata = new TableMetadata();
        metadata.setOwnerName(normalizedOwner);
        metadata.setTableName(normalizedTable);
        metadata.setFullTableName(fullTableName);
        metadata.setTimeColumn(StrUtil.isBlank(param.getTimeColumn()) ? null : normalizeMysqlIdentifier(param.getTimeColumn()));
        metadata.setStartTime(param.getStartTime() == null ? null : param.getStartTime().format(DATE_TIME_FORMATTER));
        metadata.setEndTime(param.getEndTime() == null ? null : param.getEndTime().format(DATE_TIME_FORMATTER));
        writer.write("-- TABLE " + fullTableName);
        writer.newLine();
        try (PreparedStatement statement = connection.prepareStatement(querySql)) {
            fillQueryParams(statement, param);
            try (ResultSet resultSet = statement.executeQuery()) {
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                int columnCount = resultSetMetaData.getColumnCount();
                List<String> columnNames = new ArrayList<>();
                List<String> columnTypes = new ArrayList<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = resultSetMetaData.getColumnName(i);
                    columnNames.add(normalizeMysqlIdentifier(columnName));
                    columnTypes.add(resultSetMetaData.getColumnTypeName(i));
                    if (i > 1) {
                        writer.write(",");
                    }
                    writer.write(escape(columnName));
                }
                writer.newLine();
                long rowCount = 0L;
                while (resultSet.next()) {
                    for (int i = 1; i <= columnCount; i++) {
                        if (i > 1) {
                            writer.write(",");
                        }
                        writer.write(escape(resultSet.getString(i)));
                    }
                    writer.newLine();
                    rowCount++;
                }
                metadata.setColumnNames(columnNames);
                metadata.setColumnTypes(columnTypes);
                metadata.setRowCount(rowCount);
                return metadata;
            }
        }
    }

    private TableExportMetadata exportMysqlTableV2(Connection connection, String tableName,
                                                   DatabaseBackupParam.CreateParam param, Path backupDirectory,
                                                   String taskNo, int fetchSize, long maxPartBytes,
                                                   BooleanSupplier cancelled) throws Exception {
        String normalizedTable = normalizeIdentifier(tableName);
        String querySql = buildQuerySql(normalizedTable, param);
        TableExportMetadata metadata = new TableExportMetadata();
        metadata.setTableName(normalizedTable);
        metadata.setFullTableName(normalizedTable);
        metadata.setTimeColumn(StrUtil.isBlank(param.getTimeColumn()) ? null : normalizeIdentifier(param.getTimeColumn()));
        metadata.setStartTime(param.getStartTime() == null ? null : param.getStartTime().format(DATE_TIME_FORMATTER));
        metadata.setEndTime(param.getEndTime() == null ? null : param.getEndTime().format(DATE_TIME_FORMATTER));
        metadata.setColumns(new ArrayList<>());
        metadata.setParts(new ArrayList<>());
        try (PreparedStatement statement = connection.prepareStatement(querySql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {
            statement.setFetchSize(fetchSize);
            fillQueryParams(statement, param);
            try (ResultSet resultSet = statement.executeQuery()) {
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                int columnCount = resultSetMetaData.getColumnCount();
                List<String> columnNames = new ArrayList<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = resultSetMetaData.getColumnName(i);
                    columnNames.add(columnName);
                    ColumnMetadata columnMetadata = new ColumnMetadata();
                    columnMetadata.setName(columnName);
                    columnMetadata.setType(resultSetMetaData.getColumnTypeName(i));
                    metadata.getColumns().add(columnMetadata);
                }
                PartWriter partWriter = openPartWriter(backupDirectory, normalizedTable, taskNo,
                        metadata.getParts().size() + 1, columnNames);
                metadata.getParts().add(partWriter.getPart());
                long totalRows = 0L;
                try {
                    while (resultSet.next()) {
                        checkCancelled(cancelled, backupDirectory);
                        if (partWriter.shouldRotate(maxPartBytes)) {
                            partWriter.close();
                            partWriter = openPartWriter(backupDirectory, normalizedTable, taskNo,
                                    metadata.getParts().size() + 1, columnNames);
                            metadata.getParts().add(partWriter.getPart());
                        }
                        partWriter.writeRow(resultSet, columnCount);
                        totalRows++;
                    }
                } finally {
                    partWriter.close();
                }
                metadata.setRowCount(totalRows);
                return metadata;
            }
        }
    }

    private String buildQuerySql(String fullTableName, DatabaseBackupParam.CreateParam param) {
        StringBuilder sql = new StringBuilder("SELECT * FROM ").append(fullTableName);
        if (param.getStartTime() != null && param.getEndTime() != null && StrUtil.isNotBlank(param.getTimeColumn())) {
            sql.append(" WHERE ").append(normalizeIdentifier(param.getTimeColumn())).append(" BETWEEN ? AND ?");
        }
        return sql.toString();
    }

    private void fillQueryParams(PreparedStatement statement, DatabaseBackupParam.CreateParam param) throws Exception {
        if (param.getStartTime() != null && param.getEndTime() != null && StrUtil.isNotBlank(param.getTimeColumn())) {
            statement.setString(1, param.getStartTime().format(DATE_TIME_FORMATTER));
            statement.setString(2, param.getEndTime().format(DATE_TIME_FORMATTER));
        }
    }

    private void prepareTargetTable(Connection connection, TableMetadata metadata, String dbType, String restoreMode,
                                    String targetOwnerName) throws Exception {
        if (!"TRUNCATE".equalsIgnoreCase(restoreMode)
                && !("REPLACE".equalsIgnoreCase(restoreMode) && !isMysql(dbType))) {
            return;
        }
        String fullTargetName = buildTargetTableName(metadata, targetOwnerName);
        try (Statement statement = connection.createStatement()) {
            statement.execute("TRUNCATE TABLE " + fullTargetName);
        }
    }

    private void insertRow(Connection connection, TableMetadata metadata, List<String> columns,
                           List<String> values, String dbType, String restoreMode, String targetOwnerName) throws Exception {
        String fullTargetName = buildTargetTableName(metadata, targetOwnerName);
        StringBuilder placeholders = new StringBuilder();
        for (int i = 0; i < columns.size(); i++) {
            if (i > 0) {
                placeholders.append(",");
            }
            placeholders.append("?");
        }
        String sql = buildInsertSql(dbType, restoreMode, fullTargetName, columns, placeholders.toString());
        try (PreparedStatement statement = connection.prepareStatement(sql)) {
            for (int i = 0; i < columns.size(); i++) {
                statement.setString(i + 1, i < values.size() ? values.get(i) : null);
            }
            statement.executeUpdate();
        }
    }

    private String buildInsertSql(String dbType, String restoreMode, String fullTargetName, List<String> columns,
                                  String placeholders) {
        String command = "INSERT INTO";
        if (isMysql(dbType) && "SKIP".equalsIgnoreCase(restoreMode)) {
            // MySQL 跳过重复主键行，避免普通恢复因历史数据重复而整体失败。
            command = "INSERT IGNORE INTO";
        } else if (isMysql(dbType) && "REPLACE".equalsIgnoreCase(restoreMode)) {
            command = "REPLACE INTO";
        }
        return command + " " + fullTargetName + " (" + String.join(",", columns) + ") VALUES (" + placeholders + ")";
    }

    private void importCsvV2(Connection jdbcConnection, Path metadataFilePath, String dbType, String restoreMode,
                             String targetOwnerName) throws Exception {
        ExportManifest manifest = objectMapper.readValue(metadataFilePath.toFile(), ExportManifest.class);
        jdbcConnection.setAutoCommit(false);
        try {
            for (TableExportMetadata tableMetadata : manifest.getTables()) {
                prepareTargetTable(jdbcConnection, toLegacyMetadata(tableMetadata), dbType, restoreMode, targetOwnerName);
                for (FilePartMetadata part : tableMetadata.getParts()) {
                    importPart(jdbcConnection, metadataFilePath.getParent(), tableMetadata, part, dbType, restoreMode, targetOwnerName);
                }
            }
            jdbcConnection.commit();
        } catch (Exception exception) {
            jdbcConnection.rollback();
            throw exception;
        }
    }

    private void importPart(Connection jdbcConnection, Path backupDirectory, TableExportMetadata tableMetadata,
                            FilePartMetadata part, String dbType, String restoreMode, String targetOwnerName) throws Exception {
        Path partPath = backupDirectory.resolve(part.getFileName()).normalize();
        if (!partPath.startsWith(backupDirectory.normalize())) {
            throw new IllegalArgumentException("备份分片路径不在元数据目录内：" + part.getFileName());
        }
        try (BufferedReader reader = Files.newBufferedReader(partPath, StandardCharsets.UTF_8)) {
            List<String> columns = null;
            String line;
            while ((line = reader.readLine()) != null) {
                if (line.startsWith("-- TABLE ")) {
                    continue;
                }
                if (columns == null) {
                    columns = parseCsvLine(line);
                    continue;
                }
                List<String> values = parseCsvLine(line);
                insertRow(jdbcConnection, toLegacyMetadata(tableMetadata), columns, values, dbType, restoreMode, targetOwnerName);
            }
        }
    }

    private boolean isMysql(String dbType) {
        return DatabaseOpsConst.DB_TYPE_MYSQL.equalsIgnoreCase(dbType);
    }

    private PartWriter openPartWriter(Path backupDirectory, String tableName, String taskNo, int partIndex,
                                      List<String> columnNames) throws IOException {
        String rawName = tableName.toLowerCase(Locale.ROOT) + "_part" + String.format("%03d", partIndex) + ".csv";
        String fileName = DatabaseFileNameUtil.appendTodayWithTask(rawName, taskNo);
        Path filePath = backupDirectory.resolve(fileName).normalize();
        BufferedWriter writer = Files.newBufferedWriter(filePath, StandardCharsets.UTF_8);
        writer.write("-- TABLE " + tableName);
        writer.newLine();
        for (int i = 0; i < columnNames.size(); i++) {
            if (i > 0) {
                writer.write(",");
            }
            writer.write(escape(columnNames.get(i)));
        }
        writer.newLine();
        FilePartMetadata part = new FilePartMetadata();
        part.setFileName(fileName);
        part.setFilePath(filePath.toString());
        part.setRowCount(0L);
        part.setFileSize(0L);
        return new PartWriter(writer, filePath, part);
    }

    private TableMetadata toLegacyMetadata(TableExportMetadata metadata) {
        TableMetadata legacy = new TableMetadata();
        legacy.setOwnerName(null);
        legacy.setTableName(metadata.getTableName());
        legacy.setFullTableName(metadata.getFullTableName());
        legacy.setTimeColumn(metadata.getTimeColumn());
        legacy.setStartTime(metadata.getStartTime());
        legacy.setEndTime(metadata.getEndTime());
        legacy.setRowCount(metadata.getRowCount());
        List<String> columnNames = new ArrayList<>();
        List<String> columnTypes = new ArrayList<>();
        for (ColumnMetadata column : metadata.getColumns()) {
            columnNames.add(column.getName());
            columnTypes.add(column.getType());
        }
        legacy.setColumnNames(columnNames);
        legacy.setColumnTypes(columnTypes);
        return legacy;
    }

    private void checkCancelled(BooleanSupplier cancelled, Path backupDirectory) {
        if (cancelled != null && cancelled.getAsBoolean()) {
            throw new IllegalStateException("备份任务已停止，已生成文件保留在：" + backupDirectory);
        }
    }

    private String buildTargetTableName(TableMetadata metadata, String targetOwnerName) {
        String owner = normalizeOwner(StrUtil.blankToDefault(targetOwnerName, metadata.getOwnerName()));
        return buildFullTableName(owner, metadata.getTableName());
    }

    private String buildFullTableName(String ownerName, String tableName) {
        if (StrUtil.isBlank(ownerName)) {
            return tableName;
        }
        return ownerName + "." + tableName;
    }

    private String normalizeOwner(String ownerName) {
        if (StrUtil.isBlank(ownerName)) {
            return null;
        }
        return normalizeIdentifier(ownerName);
    }

    private List<String> parseCsvLine(String line) {
        List<String> result = new ArrayList<>();
        StringBuilder current = new StringBuilder();
        boolean quoted = false;
        for (int i = 0; i < line.length(); i++) {
            char currentChar = line.charAt(i);
            if (currentChar == '"') {
                if (quoted && i + 1 < line.length() && line.charAt(i + 1) == '"') {
                    current.append('"');
                    i++;
                } else {
                    quoted = !quoted;
                }
                continue;
            }
            if (currentChar == ',' && !quoted) {
                result.add(current.toString());
                current.setLength(0);
                continue;
            }
            current.append(currentChar);
        }
        result.add(current.toString());
        return result;
    }

    private String escape(String value) {
        if (value == null) {
            return "";
        }
        return "\"" + value.replace("\"", "\"\"") + "\"";
    }

    private String normalizeIdentifier(String value) {
        if (value == null || !IDENTIFIER_PATTERN.matcher(value).matches()) {
            throw new IllegalArgumentException("数据库对象名称格式不正确：" + value);
        }
        return value.trim().toUpperCase(Locale.ROOT);
    }

    private String normalizeMysqlIdentifier(String value) {
        if (value == null || !IDENTIFIER_PATTERN.matcher(value).matches()) {
            throw new IllegalArgumentException("数据库对象名称格式不正确：" + value);
        }
        return value.trim();
    }

    @Data
    public static class TableMetadata {
        private String ownerName;
        private String tableName;
        private String fullTableName;
        private List<String> columnNames;
        private List<String> columnTypes;
        private String timeColumn;
        private String startTime;
        private String endTime;
        private Long rowCount;
    }

    @Data
    public static class ExportManifest {
        private Integer version;
        private String dbType;
        private String backupStrategy;
        private String taskNo;
        private String databaseName;
        private List<TableExportMetadata> tables;
    }

    @Data
    public static class TableExportMetadata {
        private String tableName;
        private String fullTableName;
        private String timeColumn;
        private String startTime;
        private String endTime;
        private List<ColumnMetadata> columns;
        private Long rowCount;
        private List<FilePartMetadata> parts;
    }

    @Data
    public static class ColumnMetadata {
        private String name;
        private String type;
    }

    @Data
    public static class FilePartMetadata {
        private String fileName;
        private String filePath;
        private Long rowCount;
        private Long fileSize;
    }

    private class PartWriter {
        private final BufferedWriter writer;
        private final Path filePath;
        private final FilePartMetadata part;

        private PartWriter(BufferedWriter writer, Path filePath, FilePartMetadata part) {
            this.writer = writer;
            this.filePath = filePath;
            this.part = part;
        }

        private FilePartMetadata getPart() {
            return part;
        }

        private boolean shouldRotate(long maxPartBytes) throws IOException {
            writer.flush();
            return part.getRowCount() > 0 && Files.size(filePath) >= maxPartBytes;
        }

        private void writeRow(ResultSet resultSet, int columnCount) throws Exception {
            for (int i = 1; i <= columnCount; i++) {
                if (i > 1) {
                    writer.write(",");
                }
                writer.write(escape(resultSet.getString(i)));
            }
            writer.newLine();
            part.setRowCount(part.getRowCount() + 1);
        }

        private void close() throws IOException {
            writer.close();
            part.setFileSize(Files.exists(filePath) ? Files.size(filePath) : 0L);
        }
    }
}
