package de.avatic.lcc.repositories.bulk;

import de.avatic.lcc.database.dialect.SqlDialectProvider;
import de.avatic.lcc.dto.bulk.BulkFileType;
import de.avatic.lcc.dto.bulk.BulkOperationState;
import de.avatic.lcc.dto.bulk.BulkProcessingType;
import de.avatic.lcc.model.bulk.BulkOperation;
import de.avatic.lcc.util.exception.internalerror.DatabaseException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Objects;
import java.util.Optional;

@Repository
public class BulkOperationRepository {


    private final JdbcTemplate jdbcTemplate;
    private final SqlDialectProvider dialectProvider;

    public BulkOperationRepository(JdbcTemplate jdbcTemplate, SqlDialectProvider dialectProvider) {
        this.jdbcTemplate = jdbcTemplate;
        this.dialectProvider = dialectProvider;
    }

    @Transactional
    public Integer insert(BulkOperation operation) {

        removeOld(operation.getUserId());

        String sql = String.format("""
                INSERT INTO bulk_operation (user_id, bulk_file_type, bulk_processing_type, state, %s, validity_period_id)
                VALUES (?, ?, ?, ?, ?, ?)
                """, dialectProvider.escapeIdentifier("file"));

        GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();

        var affectedRows = jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setInt(1, operation.getUserId());
            ps.setString(2, operation.getFileType().name());
            ps.setString(3, operation.getProcessingType().name());
            ps.setString(4, operation.getProcessState().name());
            ps.setBytes(5, operation.getFile());

            if (Objects.isNull(operation.getValidityPeriodId()))
                ps.setNull(6, java.sql.Types.INTEGER);
            else
                ps.setInt(6, operation.getValidityPeriodId());

            return ps;
        }, keyHolder);

        if (affectedRows != 1)
            throw new DatabaseException("Unable to schedule bulk operation");

        // Return the generated ID
        return keyHolder.getKey() != null ? keyHolder.getKey().intValue() : null;
    }

    @Transactional
    public void removeOld(Integer userId) {
        // First, fetch the IDs of the 10 newest operations to keep
        // (MySQL doesn't support LIMIT in IN/NOT IN subqueries)
        String fetchNewestSql = "SELECT id FROM bulk_operation WHERE user_id = ? AND state NOT IN ('SCHEDULED', 'PROCESSING') ORDER BY created_at DESC " +
            dialectProvider.buildPaginationClause(10, 0);
        Object[] paginationParams = dialectProvider.getPaginationParameters(10, 0);
        Object[] fetchParams = new Object[]{userId, paginationParams[0], paginationParams[1]};

        List<Integer> newestIds = jdbcTemplate.queryForList(fetchNewestSql, Integer.class, fetchParams);

        // If there are 10 or fewer operations, nothing to delete
        if (newestIds.size() <= 10) {
            return;
        }

        // Build comma-separated list of IDs to keep
        String idsToKeep = newestIds.stream()
                    .map(String::valueOf)
                    .reduce((a, b) -> a + "," + b)
                    .orElse("0");

        // Update sys_error records to set bulk_operation_id to NULL for operations that will be deleted
        String updateErrorsSql = String.format("""
                UPDATE sys_error
                SET bulk_operation_id = NULL
                WHERE bulk_operation_id IN (
                    SELECT id FROM bulk_operation
                    WHERE user_id = ?
                    AND state NOT IN ('SCHEDULED', 'PROCESSING')
                    AND id NOT IN (%s)
                )
                """, idsToKeep);

        jdbcTemplate.update(updateErrorsSql, userId);

        // Delete the old bulk_operation entries (keeping only the 10 newest for the current user)
        String deleteBulkSql = String.format("""
                DELETE FROM bulk_operation
                WHERE user_id = ?
                AND state NOT IN ('SCHEDULED', 'PROCESSING')
                AND id NOT IN (%s)
                """, idsToKeep);

        jdbcTemplate.update(deleteBulkSql, userId);
    }

    @Transactional
    public void updateState(Integer id, BulkOperationState state) {
        String sql = """
                UPDATE bulk_operation
                SET state = ?
                WHERE id = ?
                """;

        jdbcTemplate.update(sql, state.name(), id);
    }

    @Transactional
    public List<BulkOperation> listByUserId(Integer userId) {

        cleanupTimeouts(userId);

        String baseQuery = """
                SELECT id, user_id, bulk_file_type, bulk_processing_type, state, created_at, validity_period_id
                FROM bulk_operation
                WHERE user_id = ?
                ORDER BY created_at DESC
                """;

        String sql = baseQuery + dialectProvider.buildPaginationClause(10, 0);
        Object[] paginationParams = dialectProvider.getPaginationParameters(10, 0);

        // Combine userId with pagination params
        Object[] allParams = new Object[]{userId, paginationParams[0], paginationParams[1]};

        return jdbcTemplate.query(sql, new BulkOperationRowMapper(true), allParams);
    }

    private void cleanupTimeouts(Integer userId) {

        // Build date subtraction expression (60 minutes ago)
        String dateCondition = dialectProvider.buildDateSubtraction(null, "60", SqlDialectProvider.DateUnit.MINUTE);

        String sql = String.format("""
                UPDATE bulk_operation SET state = 'EXCEPTION'
                WHERE user_id = ?
                AND (state = 'PROCESSING' OR state = 'SCHEDULED')
                AND created_at < %s
                """, dateCondition);

        jdbcTemplate.update(sql, userId);
    }

    @Transactional
    public Optional<BulkOperation> getOperationById(Integer id) {
        String sql = String.format("""
                SELECT id, user_id, bulk_file_type, bulk_processing_type, state, %s, created_at, validity_period_id
                FROM bulk_operation
                WHERE id = ?
                """, dialectProvider.escapeIdentifier("file"));

        List<BulkOperation> results = jdbcTemplate.query(sql, new BulkOperationRowMapper(false), id);

        return Optional.ofNullable(results.isEmpty() ? null : results.getFirst());
    }

    @Transactional
    public void update(BulkOperation op) {
        String sql = String.format("""
                UPDATE bulk_operation
                SET user_id = ?, bulk_file_type = ?, state = ?, %s = ?, validity_period_id = ?
                WHERE id = ?
                """, dialectProvider.escapeIdentifier("file"));

        jdbcTemplate.update(sql,
                op.getUserId(),
                op.getFileType().name(),
                op.getProcessState().name(),
                op.getFile(),
                op.getValidityPeriodId(),
                op.getId()
        );
    }

    private record BulkOperationRowMapper(boolean skipFile) implements RowMapper<BulkOperation> {

        BulkOperationRowMapper() {
            this(false);
        }

        @Override
        public BulkOperation mapRow(ResultSet rs, int rowNum) throws SQLException {
            BulkOperation operation = new BulkOperation();
            operation.setId(rs.getInt("id"));
            operation.setUserId(rs.getInt("user_id"));
            operation.setProcessingType(BulkProcessingType.valueOf(rs.getString("bulk_processing_type")));
            operation.setFileType(BulkFileType.valueOf(rs.getString("bulk_file_type")));
            operation.setProcessState(BulkOperationState.valueOf(rs.getString("state")));


            operation.setValidityPeriodId(rs.getInt("validity_period_id"));
            if (rs.wasNull())
                operation.setValidityPeriodId(null);

            if (!skipFile)
                operation.setFile(rs.getBytes("file"));
            operation.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
            return operation;
        }
    }


}
