package org.micord.service;

import java.net.HttpURLConnection;
import java.net.http.HttpClient;
import java.net.http.HttpRequest;
import java.net.http.HttpResponse;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.CompletableFuture;

import com.arangodb.ArangoCursor;
import com.arangodb.ArangoDBException;
import com.arangodb.ArangoDatabase;
import com.arangodb.entity.StreamTransactionEntity;
import com.arangodb.model.AqlQueryOptions;
import com.arangodb.model.StreamTransactionOptions;
import org.micord.config.ArangoDBConnection;
import org.micord.config.DatabaseConnection;
import org.micord.config.S3HttpConnection;
import org.micord.models.AqlRequest;
import org.micord.models.RequestArgument;
import org.micord.models.Requests;
import org.micord.models.S3Request;
import org.micord.models.SqlRequest;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

/**
 * @author Maksim Tereshin
 */
@Service
public class SqlAqlService {

  private static final Logger logger = LoggerFactory.getLogger(SqlAqlService.class);

  @Autowired
  private HttpClient httpClient;

  public void processS3Requests(List<S3Request> s3Requests, List<String> ids) {
    if (s3Requests != null) {
      s3Requests.forEach(request -> {
        List<CompletableFuture<Void>> futures = ids.stream()
            .map(id -> CompletableFuture.runAsync(() -> processS3Request(request, id)))
            .toList();

        CompletableFuture.allOf(futures.toArray(new CompletableFuture[0]))
            .thenRun(() -> logger.info("Successfully processed all S3 requests."))
            .exceptionally(ex -> {
              logger.error("Failed to process S3 requests", ex);
              return null;
            });
      });
    }
  }

  private void processS3Request(S3Request request, String id) {
    try {
      List<String> files = new ArrayList<>();

      if (request.getRequestArguments() != null && !request.getRequestArguments().isEmpty()) {
        for (RequestArgument argument : request.getRequestArguments()) {
          try (Connection connection = DatabaseConnection.getConnection(
              argument.getSqlConnectionParams())) {
            String query = argument.getRequestURL();
            List<String> result = fetchFileListFromDatabaseSQL(connection, query);
            if (result != null && !result.isEmpty()) {
              files.addAll(result);
            }
          }
          catch (SQLException e) {
            logger.error("Failed to execute query for RequestArgument", e);
          }
        }
      }

      files.forEach(file -> {
        HttpRequest httpRequest;
        try {
          httpRequest = S3HttpConnection.buildHttpRequest(request, file);
        }
        catch (Exception e) {
          throw new RuntimeException(e);
        }

        httpClient.sendAsync(httpRequest, HttpResponse.BodyHandlers.ofString())
            .thenAccept(response -> {
              if (response.statusCode() == HttpURLConnection.HTTP_NO_CONTENT
                  || response.statusCode() == HttpURLConnection.HTTP_OK) {
                logger.info("Successfully deleted object for ID {}", id);
              }
              else {
                logger.error("Failed to delete object for ID {}. Response code: {}", id,
                    response.statusCode()
                );
              }
            })
            .exceptionally(ex -> {
              logger.error("Failed to delete object for ID {}", id, ex);
              return null;
            });
      });

    }
    catch (Exception e) {
      logger.error("Failed to process S3 request for id: {}", id, e);
    }
  }

  @Transactional
  public void processSqlAndAqlRequests(Requests config, List<String> ids) {
    if (config.getSqlRequests() != null) {
      for (SqlRequest request : config.getSqlRequests()) {
        processSqlRequests(request, ids);
      }
    }

    if (config.getAqlRequests() != null) {
      for (AqlRequest request : config.getAqlRequests()) {
        processAqlRequests(request, ids);
      }
    }
    processS3Requests(config.getS3Requests(), ids);
  }

  private void processSqlRequests(SqlRequest request, List<String> ids) {
    try (Connection connection = DatabaseConnection.getConnection(
        request.getSqlConnectionParams())) {
      String query = buildSqlQuery(request, String.join(",", ids));
      executeSqlQuery(connection, query);
    }
    catch (SQLException e) {
      logger.error("SQL query execution failed", e);
    }
  }

  private String buildSqlQuery(SqlRequest request, String ids) {
    StringBuilder clauseBuilder = new StringBuilder();

    if (request.getRequestArguments() != null && !request.getRequestArguments().isEmpty()) {
      for (RequestArgument argument : request.getRequestArguments()) {
        clauseBuilder.append(argument.getId()).append(" IN (").append(ids).append(")");

        if (argument.getSqlConnectionParams() != null) {
          try (Connection connection = DatabaseConnection.getConnection(
              argument.getSqlConnectionParams())) {
            String query = argument.getRequestURL();
            List<String> result = fetchFileListFromDatabaseSQL(connection, query);

            if (result != null && !result.isEmpty()) {
              String resultSet = String.join(", ", result.stream()
                  .map(s -> "'" + s + "'")
                  .toArray(String[]::new));
              clauseBuilder.append(" OR ")
                  .append(argument.getId())
                  .append(" IN (")
                  .append(resultSet)
                  .append(")");
            }
          }
          catch (SQLException e) {
            logger.error("Failed to execute query for RequestArgument", e);
          }
        }
      }
    }

    String clause = clauseBuilder.toString();
    return request.getRequestURL()
        .replace("${DB}", request.getSqlConnectionParams().getJdbcDatabase())
        .replace("${clause}", clause)
        .replace("${ids}", ids);
  }

  private void executeSqlQuery(Connection connection, String query) throws SQLException {
    try (PreparedStatement stmt = connection.prepareStatement(query)) {
      stmt.execute();
    }
  }

  public List<String> fetchFileListFromDatabaseSQL(Connection connection, String query)
      throws SQLException {
    List<String> results = new ArrayList<>();
    try (PreparedStatement stmt = connection.prepareStatement(query);
         ResultSet rs = stmt.executeQuery()) {
      while (rs.next()) {
        results.add(rs.getString(1)); // Fetch the first column
      }
    }
    return results;
  }

  private void processAqlRequests(AqlRequest request, List<String> ids) {
    ArangoDatabase arangoDb = ArangoDBConnection.getConnection(request.getAqlConnectionParams());

    RequestArgument requestArgument = request.getRequestArguments().get(0);
    List<String> aqlCollectionRead = requestArgument.getAqlCollectionRead();
    String aqlCollectionWrite = requestArgument.getId();

    StreamTransactionEntity tx = null;
    try {
      StreamTransactionOptions options = new StreamTransactionOptions()
          .writeCollections(aqlCollectionWrite)
          .readCollections(aqlCollectionRead.toArray(new String[0]));

      tx = arangoDb.beginStreamTransaction(options);
      String transactionId = tx.getId();

      logger.info("Stream transaction started with ID: {}", transactionId);

      List<String> entities = executeSelectAqlRequest(arangoDb, request.getRequestArguments(), ids,
          transactionId
      );
      executeMainAqlRequest(arangoDb, request, entities, transactionId);

      arangoDb.commitStreamTransaction(transactionId);
      logger.info("Stream transaction with ID {} committed successfully", transactionId);

    }
    catch (ArangoDBException e) {
      if (tx != null) {
        arangoDb.abortStreamTransaction(tx.getId());
        logger.error("Stream transaction with ID {} aborted due to an error", tx.getId(), e);
      }
      throw new RuntimeException("Failed to execute AQL request within a stream transaction", e);
    }

    logger.info("Successfully executed AQL request");
  }

  private List<String> executeSelectAqlRequest(ArangoDatabase arangoDb,
                                               List<RequestArgument> requestArguments,
                                               List<String> ids, String transactionId) {
    List<String> entityIdList = new ArrayList<>();

    RequestArgument argument = requestArguments.get(0);

    String query = argument.getRequestURL();
    String entityType = argument.getId();

    Map<String, Object> bindVars = new HashMap<>();
    bindVars.put("ids", ids);

    AqlQueryOptions aqlQueryOptions = new AqlQueryOptions().streamTransactionId(transactionId);

    try (ArangoCursor<Map> cursor = arangoDb.query(query, Map.class, bindVars, aqlQueryOptions)) {
      while (cursor.hasNext()) {
        Map result = cursor.next();
        switch (entityType) {
          case "applicationId":
            entityIdList.add((String) result.get("applicationId"));
            break;
          case "edgesId":
            entityIdList.addAll((List<String>) result.get("edgesId"));
            break;
          case "subjectId":
            entityIdList.addAll((List<String>) result.get("subjectId"));
            break;
          case "historyId":
            entityIdList.addAll((List<String>) result.get("historyId"));
            break;
          case "interdepreqId":
            entityIdList.addAll((List<String>) result.get("interdepreqId"));
            break;
          default:
            throw new IllegalArgumentException("Invalid requestArgumentId: " + entityType);
        }
      }
    }
    catch (Exception e) {
      logger.error("Failed to execute AQL query", e);
    }

    return entityIdList;
  }

  private void executeMainAqlRequest(ArangoDatabase arangoDb, AqlRequest request,
                                     List<String> entityIdList, String transactionId) {
    if (entityIdList == null || entityIdList.isEmpty()) {
      logger.warn("No entities found for main AQL request.");
      return;
    }

    String entity = request.getRequestArguments().get(0).getId();

    Map<String, Object> bindVars = new HashMap<>();
    bindVars.put("ids", entityIdList);


    String finalQuery = request.getRequestURL()
        .replace("${entity}", entity);

    AqlQueryOptions aqlQueryOptions = new AqlQueryOptions().streamTransactionId(transactionId);

    arangoDb.query(finalQuery, null, bindVars, aqlQueryOptions);

    logger.info("Successfully removed {}: {}", entity, entityIdList);
  }

}
