package de.avatic.lcc.repositories.calculation;

import de.avatic.lcc.database.dialect.SqlDialectProvider;
import de.avatic.lcc.model.db.calculations.CalculationJob;
import de.avatic.lcc.model.db.calculations.CalculationJobPriority;
import de.avatic.lcc.model.db.calculations.CalculationJobState;
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.*;
import java.util.List;
import java.util.Map;
import java.util.Optional;

@Repository
public class CalculationJobRepository {
    private final JdbcTemplate jdbcTemplate;
    private final SqlDialectProvider dialectProvider;

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

    @Transactional
    public Integer insert(CalculationJob job) {
        String sql = """
                INSERT INTO calculation_job (premise_id, calculation_date, validity_period_id, property_set_id, job_state, user_id)
                VALUES (?, ?, ?, ?, ?, ?)
                """;

        GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();

        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setInt(1, job.getPremiseId());

            ps.setTimestamp(2, job.getCalculationDate() == null ? null : Timestamp.valueOf(job.getCalculationDate()));

            ps.setInt(3, job.getValidityPeriodId());
            ps.setInt(4, job.getPropertySetId());
            ps.setString(5, job.getJobState().name()); // Convert enum to string
            ps.setInt(6, job.getUserId());
            return ps;
        }, keyHolder);

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

    /**
     * Fetches and locks the next available calculation job based on priority and state.
     * Uses pessimistic locking (SELECT FOR UPDATE) to ensure thread-safety across multiple instances.
     * <p>
     * Priority order:
     * 1. CREATED with HIGH priority
     * 2. CREATED with MEDIUM priority
     * 3. CREATED with LOW priority
     * 4. EXCEPTION with retries < 3
     *
     * @return Optional containing the locked job, or empty if no job available
     */
    @Transactional
    public Optional<CalculationJob> fetchAndLockNextJob() {
        // Build base query with ORDER BY (required for OFFSET/FETCH in MSSQL)
        String baseQuery = """
                    SELECT * FROM calculation_job
                    WHERE (job_state = 'CREATED')
                       OR (job_state = 'EXCEPTION' AND retries < 3)
                    ORDER BY
                        CASE
                            WHEN job_state = 'CREATED' AND priority = 'HIGH' THEN 1
                            WHEN job_state = 'CREATED' AND priority = 'MEDIUM' THEN 2
                            WHEN job_state = 'CREATED' AND priority = 'LOW' THEN 3
                            WHEN job_state = 'EXCEPTION' THEN 4
                        END,
                        calculation_date
                    """;

        // Add pagination (LIMIT 1 OFFSET 0)
        String paginatedQuery = baseQuery + " " + dialectProvider.buildPaginationClause(1, 0);

        // Add pessimistic locking with skip locked
        String sql = dialectProvider.buildSelectForUpdateSkipLocked(paginatedQuery);

        // Get pagination parameters in correct order for the database
        Object[] params = dialectProvider.getPaginationParameters(1, 0);

        var jobs = jdbcTemplate.query(sql, new CalculationJobMapper(), params);

        if (jobs.isEmpty()) {
            return Optional.empty();
        }

        CalculationJob job = jobs.getFirst();

        // Update state to SCHEDULED
        String updateSql = "UPDATE calculation_job SET job_state = ?, retries = retries + 1 WHERE id = ?";
        jdbcTemplate.update(updateSql, CalculationJobState.SCHEDULED.name(), job.getId());

        job.setJobState(CalculationJobState.SCHEDULED);

        return Optional.of(job);
    }

    /**
     * Marks a job as completed successfully
     */
    @Transactional
    public void markAsValid(Integer id) {
        String sql = "UPDATE calculation_job SET job_state = ? WHERE id = ?";
        var affectedRows = jdbcTemplate.update(sql, CalculationJobState.VALID.name(), id);

        if (1 != affectedRows) {
            throw new DatabaseException("Unable to mark calculation job as valid with id " + id);
        }
    }

    /**
     * Marks a job as failed and increments retry counter
     */
    @Transactional
    public void markAsException(Integer id, Integer errorId) {
        String sql = "UPDATE calculation_job SET job_state = ?, error_id = ?, retries = retries + 1 WHERE id = ?";
        var affectedRows = jdbcTemplate.update(sql, CalculationJobState.EXCEPTION.name(), errorId, id);

        if (1 != affectedRows) {
            throw new DatabaseException("Unable to mark calculation job as exception with id " + id);
        }
    }

    /* ****************************** check ******************** */

    @Transactional
    public Optional<CalculationJob> getCalculationJob(Integer id) {
        String query = "SELECT * FROM calculation_job WHERE id = ?";

        var job = jdbcTemplate.query(query, new CalculationJobMapper(), id);

        if (job.isEmpty())
            return Optional.empty();

        return Optional.of(job.getFirst());
    }

    @Transactional
    public void reschedule(Integer id) {
        String sql = "UPDATE calculation_job SET job_state = ?, calculation_date = ? WHERE id = ?";

        var affectedRows = jdbcTemplate.update(sql, CalculationJobState.CREATED.name(), java.time.LocalDateTime.now(), id);

        if (1 != affectedRows) {
            throw new DatabaseException("Unable to update calculation job with id " + id);
        }
    }

    @Transactional
    public Optional<CalculationJob> getCalculationJobWithJobStateValid(Integer periodId, Integer setId, Integer nodeId, Integer materialId) {

        /* there should only be one job per period id, node id and material id combination */
        String baseQuery = "SELECT * FROM calculation_job AS cj INNER JOIN premise AS p ON cj.premise_id = p.id WHERE job_state = 'VALID' AND validity_period_id = ? AND property_set_id = ? AND p.supplier_node_id = ? AND material_id = ? ORDER BY cj.calculation_date DESC ";
        String query = baseQuery + dialectProvider.buildPaginationClause(1, 0);
        Object[] params = dialectProvider.getPaginationParameters(1, 0);

        // Combine business logic params with pagination params
        Object[] allParams = new Object[]{periodId, setId, nodeId, materialId, params[0], params[1]};

        var job = jdbcTemplate.query(query, new CalculationJobMapper(), allParams);

        if (job.isEmpty())
            return Optional.empty();

        return Optional.of(job.getFirst());
    }

    @Transactional
    public Optional<CalculationJob> getCalculationJobWithJobStateValidUserNodeId(Integer periodId, Integer setId, Integer userNodeId, Integer materialId) {

        /* there should only be one job per period id, node id and material id combination */
        String baseQuery = "SELECT * FROM calculation_job AS cj INNER JOIN premise AS p ON cj.premise_id = p.id WHERE job_state = 'VALID' AND validity_period_id = ? AND property_set_id = ? AND p.user_supplier_node_id = ? AND material_id = ? ORDER BY cj.calculation_date DESC ";
        String query = baseQuery + dialectProvider.buildPaginationClause(1, 0);
        Object[] params = dialectProvider.getPaginationParameters(1, 0);

        // Combine business logic params with pagination params
        Object[] allParams = new Object[]{periodId, setId, userNodeId, materialId, params[0], params[1]};

        var job = jdbcTemplate.query(query, new CalculationJobMapper(), allParams);

        if (job.isEmpty())
            return Optional.empty();

        return Optional.of(job.getFirst());
    }


    @Transactional
    public void setStateTo(Integer id, CalculationJobState calculationJobState) {
        String sql = "UPDATE calculation_job SET job_state = ? WHERE id = ?";

        var affectedRows = jdbcTemplate.update(sql, calculationJobState.name(), id);

        if (1 != affectedRows) {
            throw new DatabaseException("Unable to update calculation job with id " + id);
        }
    }

    @Transactional
    public Optional<CalculationJob> findJob(Integer premiseId, Integer validSetId, Integer validPeriodId) {
        String sql = "SELECT * FROM calculation_job WHERE premise_id = ? AND validity_period_id = ? AND property_set_id = ?";

        var jobs = jdbcTemplate.query(sql, new CalculationJobMapper(), premiseId, validPeriodId, validSetId);

        return jobs.isEmpty() ? Optional.empty() : Optional.ofNullable(jobs.getFirst());
    }

    @Transactional
    public void invalidateByPropertySetId(Integer id) {
        String sql = "UPDATE calculation_job SET job_state = ? WHERE property_set_id = ?";
        jdbcTemplate.update(sql, CalculationJobState.INVALIDATED.name(), id);
    }

    @Transactional
    public void invalidateByPeriodId(Integer id) {
        String sql = "UPDATE calculation_job SET job_state = ? WHERE validity_period_id = ?";
        jdbcTemplate.update(sql, CalculationJobState.INVALIDATED.name(), id);
    }

    @Transactional
    public CalculationJobState getLastStateFor(Integer premiseId) {

        String baseQuery = "SELECT job_state FROM calculation_job WHERE premise_id = ? ORDER BY calculation_date DESC ";
        String sql = baseQuery + dialectProvider.buildPaginationClause(1, 0);
        Object[] params = dialectProvider.getPaginationParameters(1, 0);

        // Combine business logic params with pagination params
        Object[] allParams = new Object[]{premiseId, params[0], params[1]};

        var result = jdbcTemplate.query(sql, (rs, rowNum) -> CalculationJobState.valueOf(rs.getString("job_state")), allParams);

        if (result.isEmpty())
            return null;

        return result.getFirst();
    }

    @Transactional
    public Map<Integer, CalculationJobState> getStateFor(List<Integer> ids) {
        return null;
    }

    public Integer getFailedJobByUserId(Integer userId) {

        // Build date subtraction expression using dialect provider
        String dateCondition = dialectProvider.buildDateSubtraction(null, "3", SqlDialectProvider.DateUnit.DAY);

        String sql = String.format(
            "SELECT COUNT(*) FROM calculation_job WHERE user_id = ? AND job_state = 'EXCEPTION' AND calculation_date > %s",
            dateCondition
        );

        return jdbcTemplate.queryForObject(sql, Integer.class, userId);
    }

    public Integer getSelfScheduledJobCountByUserId(Integer userId) {

        String sql = "SELECT COUNT(*) FROM calculation_job WHERE user_id = ? AND ( job_state = 'SCHEDULED' or job_state = 'CREATED' )";


        return jdbcTemplate.queryForObject(sql, Integer.class, userId);
    }

    private static class CalculationJobMapper implements RowMapper<CalculationJob> {
        @Override
        public CalculationJob mapRow(ResultSet rs, int rowNum) throws SQLException {

            CalculationJob entity = new CalculationJob();

            entity.setId(rs.getInt("id"));
            entity.setPremiseId(rs.getInt("premise_id"));
            entity.setValidityPeriodId(rs.getInt("validity_period_id"));
            entity.setJobState(CalculationJobState.valueOf(rs.getString("job_state")));
            entity.setPropertySetId(rs.getInt("property_set_id"));
            entity.setUserId(rs.getInt("user_id"));
            entity.setCalculationDate(rs.getTimestamp("calculation_date").toLocalDateTime());

            entity.setPriority(CalculationJobPriority.valueOf(rs.getString("priority")));
            entity.setRetries(rs.getInt("retries"));

            Integer errorId = rs.getInt("error_id");
            if (!rs.wasNull()) {
                entity.setErrorId(errorId);
            }

            return entity;

        }
    }
}
