package gc.mda.signal_batch.global.util;

import gc.mda.signal_batch.domain.gis.model.TileStatistics;

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;

import java.time.LocalDateTime;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.atomic.AtomicInteger;


@Slf4j
@Component
public class ConcurrentUpdateManager {

    private final JdbcTemplate queryJdbcTemplate;

    public ConcurrentUpdateManager(@Qualifier("queryJdbcTemplate") JdbcTemplate queryJdbcTemplate) {
        this.queryJdbcTemplate = queryJdbcTemplate;
    }

    // 락 통계 관리
    private final Map<String, LockStatistics> lockStats = new ConcurrentHashMap<>();

    /**
     * 타일 통계 병합 업데이트
     */
    @Transactional(isolation = Isolation.READ_COMMITTED)
    public void mergeTileStatistics(List<TileStatistics> statistics) {
        String sql = """
            INSERT INTO signal.t_tile_summary (
                tile_id, tile_level, time_bucket, vessel_count,
                unique_vessels, total_points, avg_sog, max_sog,
                vessel_density, created_at
            ) VALUES (
                ?, ?, ?, ?, ?::jsonb, ?, ?, ?, ?, ?
            )
            ON CONFLICT (tile_id, time_bucket) DO UPDATE SET
                vessel_count = t_tile_summary.vessel_count + EXCLUDED.vessel_count,
                unique_vessels = t_tile_summary.unique_vessels || EXCLUDED.unique_vessels,
                total_points = t_tile_summary.total_points + EXCLUDED.total_points,
                avg_sog = (t_tile_summary.avg_sog * t_tile_summary.total_points + 
                          EXCLUDED.avg_sog * EXCLUDED.total_points) / 
                         (t_tile_summary.total_points + EXCLUDED.total_points),
                max_sog = GREATEST(t_tile_summary.max_sog, EXCLUDED.max_sog),
                vessel_density = (t_tile_summary.vessel_count + EXCLUDED.vessel_count) / 
                               (SELECT area FROM signal.t_grid_tiles WHERE tile_id = t_tile_summary.tile_id)
        """;

        queryJdbcTemplate.batchUpdate(sql, statistics, 100, (ps, stat) -> {
            ps.setString(1, stat.getTileId());
            ps.setInt(2, stat.getTileLevel());
            ps.setTimestamp(3, java.sql.Timestamp.valueOf(stat.getTimeBucket()));
            ps.setInt(4, stat.getVesselCount());
            ps.setString(5, convertToJson(stat.getUniqueVessels()));
            ps.setLong(6, stat.getTotalPoints());
            ps.setBigDecimal(7, stat.getAvgSog());
            ps.setBigDecimal(8, stat.getMaxSog());
            ps.setBigDecimal(9, stat.getVesselDensity());
            ps.setTimestamp(10, java.sql.Timestamp.valueOf(LocalDateTime.now()));
        });
    }

    /**
     * 락 ID 생성
     */
    private long generateLockId(String key) {
        // PostgreSQL advisory lock은 bigint 사용
        return Math.abs(key.hashCode());
    }

    /**
     * JSON 변환
     */
    private String convertToJson(Object obj) {
        try {
            return new com.fasterxml.jackson.databind.ObjectMapper()
                    .writeValueAsString(obj);
        } catch (Exception e) {
            return "{}";
        }
    }

    /**
     * 락 통계 조회
     */
    public Map<String, Map<String, Object>> getLockStatistics() {
        Map<String, Map<String, Object>> result = new HashMap<>();

        lockStats.forEach((key, stats) -> {
            Map<String, Object> statMap = new HashMap<>();
            statMap.put("attempts", stats.attempts.get());
            statMap.put("successes", stats.successes.get());
            statMap.put("failures", stats.failures.get());
            statMap.put("errors", stats.errors.get());

            if (stats.attempts.get() > 0) {
                statMap.put("successRate",
                        (double) stats.successes.get() / stats.attempts.get());
                statMap.put("avgDurationMs",
                        stats.totalDuration.get() / stats.successes.get());
            }

            result.put(key, statMap);
        });

        return result;
    }

    /**
     * 현재 락 상태 모니터링
     */
    public List<Map<String, Object>> getCurrentLocks() {
        String sql = """
            SELECT 
                pid,
                locktype,
                database,
                relation::regclass,
                mode,
                granted,
                EXTRACT(EPOCH FROM (NOW() - query_start)) as duration_seconds
            FROM pg_locks l
            JOIN pg_stat_activity a ON l.pid = a.pid
            WHERE l.locktype IN ('advisory', 'relation', 'tuple')
              AND a.application_name LIKE '%vessel-batch%'
            ORDER BY duration_seconds DESC
        """;

        return queryJdbcTemplate.queryForList(sql);
    }

    /**
     * 데드락 모니터링
     */
    public List<Map<String, Object>> getDeadlockInfo() {
        String sql = """
            SELECT 
                pid,
                usename,
                application_name,
                client_addr,
                query_start,
                state,
                wait_event_type,
                wait_event,
                query
            FROM pg_stat_activity
            WHERE wait_event_type = 'Lock'
              AND state != 'idle'
            ORDER BY query_start
        """;

        return queryJdbcTemplate.queryForList(sql);
    }

    /**
     * 락 통계 클래스
     */
    private static class LockStatistics {
        final AtomicInteger attempts = new AtomicInteger();
        final AtomicInteger successes = new AtomicInteger();
        final AtomicInteger failures = new AtomicInteger();
        final AtomicInteger errors = new AtomicInteger();
        final AtomicInteger totalDuration = new AtomicInteger();
    }
}