"""
IntelligenceProcessor - Tier 3: INTELLIGENCE Features (53 columns)

Advanced analytics on fact_round_events with complex calculations:
- High IQ Kills (9 columns): wallbang, smoke, blind, noscope + IQ score
- Timing Analysis (12 columns): early/mid/late kill distribution, aggression
- Pressure Performance (10 columns): comeback, losing streak, matchpoint
- Position Mastery (14 columns): site control, lurk tendency, spatial IQ
- Trade Network (8 columns): trade kills/response time, teamwork
"""

import sqlite3
from typing import Dict, Any, List, Tuple
from .base_processor import BaseFeatureProcessor, SafeAggregator


class IntelligenceProcessor(BaseFeatureProcessor):
    """Tier 3 INTELLIGENCE processor - Complex event-level analytics"""
    
    MIN_MATCHES_REQUIRED = 10  # Need substantial data for reliable patterns
    
    @staticmethod
    def calculate(steam_id: str, conn_l2: sqlite3.Connection) -> Dict[str, Any]:
        """
        Calculate all Tier 3 INTELLIGENCE features (53 columns)
        
        Returns dict with keys starting with 'int_'
        """
        features = {}
        
        # Check minimum matches
        if not BaseFeatureProcessor.check_min_matches(steam_id, conn_l2, 
                                                       IntelligenceProcessor.MIN_MATCHES_REQUIRED):
            return _get_default_intelligence_features()
        
        # Calculate each intelligence dimension
        features.update(IntelligenceProcessor._calculate_high_iq_kills(steam_id, conn_l2))
        features.update(IntelligenceProcessor._calculate_timing_analysis(steam_id, conn_l2))
        features.update(IntelligenceProcessor._calculate_pressure_performance(steam_id, conn_l2))
        features.update(IntelligenceProcessor._calculate_position_mastery(steam_id, conn_l2))
        features.update(IntelligenceProcessor._calculate_trade_network(steam_id, conn_l2))
        
        return features
    
    @staticmethod
    def _calculate_high_iq_kills(steam_id: str, conn_l2: sqlite3.Connection) -> Dict[str, Any]:
        """
        Calculate High IQ Kills (9 columns)
        
        Columns:
        - int_wallbang_kills, int_wallbang_rate
        - int_smoke_kills, int_smoke_kill_rate
        - int_blind_kills, int_blind_kill_rate
        - int_noscope_kills, int_noscope_rate
        - int_high_iq_score
        """
        cursor = conn_l2.cursor()
        
        # Get total kills for rate calculations
        cursor.execute("""
            SELECT COUNT(*) as total_kills
            FROM fact_round_events
            WHERE attacker_steam_id = ?
            AND event_type = 'kill'
        """, (steam_id,))
        
        total_kills = cursor.fetchone()[0]
        total_kills = total_kills if total_kills else 1
        
        # Wallbang kills
        cursor.execute("""
            SELECT COUNT(*) as wallbang_kills
            FROM fact_round_events
            WHERE attacker_steam_id = ?
            AND is_wallbang = 1
        """, (steam_id,))
        
        wallbang_kills = cursor.fetchone()[0]
        wallbang_kills = wallbang_kills if wallbang_kills else 0
        
        # Smoke kills
        cursor.execute("""
            SELECT COUNT(*) as smoke_kills
            FROM fact_round_events
            WHERE attacker_steam_id = ?
            AND is_through_smoke = 1
        """, (steam_id,))
        
        smoke_kills = cursor.fetchone()[0]
        smoke_kills = smoke_kills if smoke_kills else 0
        
        # Blind kills
        cursor.execute("""
            SELECT COUNT(*) as blind_kills
            FROM fact_round_events
            WHERE attacker_steam_id = ?
            AND is_blind = 1
        """, (steam_id,))
        
        blind_kills = cursor.fetchone()[0]
        blind_kills = blind_kills if blind_kills else 0
        
        # Noscope kills (AWP only)
        cursor.execute("""
            SELECT COUNT(*) as noscope_kills
            FROM fact_round_events
            WHERE attacker_steam_id = ?
            AND is_noscope = 1
        """, (steam_id,))
        
        noscope_kills = cursor.fetchone()[0]
        noscope_kills = noscope_kills if noscope_kills else 0
        
        # Calculate rates
        wallbang_rate = SafeAggregator.safe_divide(wallbang_kills, total_kills)
        smoke_rate = SafeAggregator.safe_divide(smoke_kills, total_kills)
        blind_rate = SafeAggregator.safe_divide(blind_kills, total_kills)
        noscope_rate = SafeAggregator.safe_divide(noscope_kills, total_kills)
        
        # High IQ score: weighted combination
        iq_score = (
            wallbang_kills * 3.0 +
            smoke_kills * 2.0 +
            blind_kills * 1.5 +
            noscope_kills * 2.0
        )
        
        return {
            'int_wallbang_kills': wallbang_kills,
            'int_wallbang_rate': round(wallbang_rate, 4),
            'int_smoke_kills': smoke_kills,
            'int_smoke_kill_rate': round(smoke_rate, 4),
            'int_blind_kills': blind_kills,
            'int_blind_kill_rate': round(blind_rate, 4),
            'int_noscope_kills': noscope_kills,
            'int_noscope_rate': round(noscope_rate, 4),
            'int_high_iq_score': round(iq_score, 2),
        }
    
    @staticmethod
    def _calculate_timing_analysis(steam_id: str, conn_l2: sqlite3.Connection) -> Dict[str, Any]:
        """
        Calculate Timing Analysis (12 columns)
        
        Time bins: Early (0-30s), Mid (30-60s), Late (60s+)
        
        Columns:
        - int_timing_early_kills, int_timing_mid_kills, int_timing_late_kills
        - int_timing_early_kill_share, int_timing_mid_kill_share, int_timing_late_kill_share
        - int_timing_avg_kill_time
        - int_timing_early_deaths, int_timing_early_death_rate
        - int_timing_aggression_index
        - int_timing_patience_score
        - int_timing_first_contact_time
        """
        cursor = conn_l2.cursor()
        
        # Kill distribution by time bins
        cursor.execute("""
            SELECT 
                COUNT(CASE WHEN event_time <= 30 THEN 1 END) as early_kills,
                COUNT(CASE WHEN event_time > 30 AND event_time <= 60 THEN 1 END) as mid_kills,
                COUNT(CASE WHEN event_time > 60 THEN 1 END) as late_kills,
                COUNT(*) as total_kills,
                AVG(event_time) as avg_kill_time
            FROM fact_round_events
            WHERE attacker_steam_id = ?
            AND event_type = 'kill'
        """, (steam_id,))
        
        row = cursor.fetchone()
        early_kills = row[0] if row[0] else 0
        mid_kills = row[1] if row[1] else 0
        late_kills = row[2] if row[2] else 0
        total_kills = row[3] if row[3] else 1
        avg_kill_time = row[4] if row[4] else 0.0
        
        # Calculate shares
        early_share = SafeAggregator.safe_divide(early_kills, total_kills)
        mid_share = SafeAggregator.safe_divide(mid_kills, total_kills)
        late_share = SafeAggregator.safe_divide(late_kills, total_kills)
        
        # Death distribution (for aggression index)
        cursor.execute("""
            SELECT 
                COUNT(CASE WHEN event_time <= 30 THEN 1 END) as early_deaths,
                COUNT(*) as total_deaths
            FROM fact_round_events
            WHERE victim_steam_id = ?
            AND event_type = 'kill'
        """, (steam_id,))
        
        death_row = cursor.fetchone()
        early_deaths = death_row[0] if death_row[0] else 0
        total_deaths = death_row[1] if death_row[1] else 1
        
        early_death_rate = SafeAggregator.safe_divide(early_deaths, total_deaths)
        
        # Aggression index: early kills / early deaths
        aggression_index = SafeAggregator.safe_divide(early_kills, max(early_deaths, 1))
        
        # Patience score: late kill share
        patience_score = late_share
        
        # First contact time: average time of first event per round
        cursor.execute("""
            SELECT AVG(min_time) as avg_first_contact
            FROM (
                SELECT match_id, round_num, MIN(event_time) as min_time
                FROM fact_round_events
                WHERE attacker_steam_id = ? OR victim_steam_id = ?
                GROUP BY match_id, round_num
            )
        """, (steam_id, steam_id))
        
        first_contact = cursor.fetchone()[0]
        first_contact_time = first_contact if first_contact else 0.0
        
        return {
            'int_timing_early_kills': early_kills,
            'int_timing_mid_kills': mid_kills,
            'int_timing_late_kills': late_kills,
            'int_timing_early_kill_share': round(early_share, 3),
            'int_timing_mid_kill_share': round(mid_share, 3),
            'int_timing_late_kill_share': round(late_share, 3),
            'int_timing_avg_kill_time': round(avg_kill_time, 2),
            'int_timing_early_deaths': early_deaths,
            'int_timing_early_death_rate': round(early_death_rate, 3),
            'int_timing_aggression_index': round(aggression_index, 3),
            'int_timing_patience_score': round(patience_score, 3),
            'int_timing_first_contact_time': round(first_contact_time, 2),
        }
    
    @staticmethod
    def _calculate_pressure_performance(steam_id: str, conn_l2: sqlite3.Connection) -> Dict[str, Any]:
        """
        Calculate Pressure Performance (10 columns)
        """
        cursor = conn_l2.cursor()
        
        # 1. Comeback Performance (Whole Match Stats for Comeback Games)
        # Definition: Won match where team faced >= 5 round deficit
        
        # Get all winning matches
        cursor.execute("""
            SELECT match_id, rating, kills, deaths 
            FROM fact_match_players
            WHERE steam_id_64 = ? AND is_win = 1
        """, (steam_id,))
        win_matches = cursor.fetchall()
        
        comeback_ratings = []
        comeback_kds = []
        
        for match_id, rating, kills, deaths in win_matches:
            # Check for deficit
            # Need round scores
            cursor.execute("""
                SELECT round_num, ct_score, t_score, winner_side
                FROM fact_rounds
                WHERE match_id = ?
                ORDER BY round_num
            """, (match_id,))
            rounds = cursor.fetchall()
            
            if not rounds: continue
            
            # Determine starting side or side per round?
            # We need player's side per round to know if they are trailing.
            # Simplified: Use fact_round_player_economy to get side per round
            cursor.execute("""
                SELECT round_num, side
                FROM fact_round_player_economy
                WHERE match_id = ? AND steam_id_64 = ?
            """, (match_id, steam_id))
            side_map = {r[0]: r[1] for r in cursor.fetchall()}
            
            max_deficit = 0
            for r_num, ct_s, t_s, win_side in rounds:
                side = side_map.get(r_num)
                if not side: continue
                
                my_score = ct_s if side == 'CT' else t_s
                opp_score = t_s if side == 'CT' else ct_s
                
                diff = opp_score - my_score
                if diff > max_deficit:
                    max_deficit = diff
            
            if max_deficit >= 5:
                # This is a comeback match
                if rating: comeback_ratings.append(rating)
                kd = kills / max(deaths, 1)
                comeback_kds.append(kd)
        
        avg_comeback_rating = SafeAggregator.safe_avg(comeback_ratings)
        avg_comeback_kd = SafeAggregator.safe_avg(comeback_kds)

        # 2. Matchpoint Performance (KPR only)
        # Definition: Rounds where ANY team is at match point (12 or 15)
        
        cursor.execute("""
            SELECT DISTINCT match_id FROM fact_match_players WHERE steam_id_64 = ?
        """, (steam_id,))
        all_match_ids = [r[0] for r in cursor.fetchall()]
        
        mp_kills = 0
        mp_rounds = 0
        
        for match_id in all_match_ids:
            # Get rounds and sides
            cursor.execute("""
                SELECT round_num, ct_score, t_score
                FROM fact_rounds
                WHERE match_id = ?
            """, (match_id,))
            rounds = cursor.fetchall()
            
            for r_num, ct_s, t_s in rounds:
                # Check for match point (MR12=12, MR15=15)
                # We check score BEFORE the round? 
                # fact_rounds stores score AFTER the round usually? 
                # Actually, standard is score is updated after win.
                # So if score is 12, the NEXT round is match point?
                # Or if score is 12, does it mean we HAVE 12 wins? Yes.
                # So if I have 12 wins, I am playing for the 13th win (Match Point in MR12).
                # So if ct_score == 12 or t_score == 12 -> Match Point Round.
                # Same for 15.
                
                is_mp = (ct_s == 12 or t_s == 12 or ct_s == 15 or t_s == 15)
                
                # Check for OT match point? (18, 21...)
                if not is_mp and (ct_s >= 18 or t_s >= 18):
                     # Simple heuristic for OT
                     if (ct_s % 3 == 0 and ct_s > 15) or (t_s % 3 == 0 and t_s > 15):
                         is_mp = True

                if is_mp:
                    # Count kills in this round (wait, if score is 12, does it mean the round that JUST finished made it 12?
                    # or the round currently being played starts with 12?
                    # fact_rounds typically has one row per round.
                    # ct_score/t_score in that row is the score ENDING that round.
                    # So if row 1 has ct=1, t=0. That means Round 1 ended 1-0.
                    # So if we want to analyze the round PLAYED at 12-X, we need to look at the round where PREVIOUS score was 12.
                    # i.e. The round where the result leads to 13?
                    # Or simpler: if the row says 13-X, that round was the winning round.
                    # But we want to include failed match points too.
                    
                    # Let's look at it this way:
                    # If current row shows `ct_score=12`, it means AFTER this round, CT has 12.
                    # So the NEXT round will be played with CT having 12.
                    # So we should look for rounds where PREVIOUS round score was 12.
                    pass
            
            # Re-query with LAG/Lead or python iteration
            rounds.sort(key=lambda x: x[0])
            current_ct = 0
            current_t = 0
            
            for r_num, final_ct, final_t in rounds:
                # Check if ENTERING this round, someone is on match point
                is_mp_round = False
                
                # MR12 Match Point: 12
                if current_ct == 12 or current_t == 12: is_mp_round = True
                # MR15 Match Point: 15
                elif current_ct == 15 or current_t == 15: is_mp_round = True
                # OT Match Point (18, 21, etc. - MR3 OT)
                elif (current_ct >= 18 and current_ct % 3 == 0) or (current_t >= 18 and current_t % 3 == 0): is_mp_round = True
                
                if is_mp_round:
                    # Count kills in this r_num
                    cursor.execute("""
                        SELECT COUNT(*) FROM fact_round_events 
                        WHERE match_id = ? AND round_num = ? 
                        AND attacker_steam_id = ? AND event_type = 'kill'
                    """, (match_id, r_num, steam_id))
                    mp_kills += cursor.fetchone()[0]
                    mp_rounds += 1
                
                # Update scores for next iteration
                current_ct = final_ct
                current_t = final_t
        
        matchpoint_kpr = SafeAggregator.safe_divide(mp_kills, mp_rounds)

        # 3. Losing Streak / Clutch Composure / Entry in Loss (Keep existing logic)
        
        # Losing streak KD
        cursor.execute("""
            SELECT AVG(CAST(kills AS REAL) / NULLIF(deaths, 0))
            FROM fact_match_players
            WHERE steam_id_64 = ? AND is_win = 0
        """, (steam_id,))
        losing_streak_kd = cursor.fetchone()[0] or 0.0
        
        # Clutch composure (perfect kills)
        cursor.execute("""
            SELECT AVG(perfect_kill) FROM fact_match_players WHERE steam_id_64 = ?
        """, (steam_id,))
        clutch_composure = cursor.fetchone()[0] or 0.0
        
        # Entry in loss
        cursor.execute("""
            SELECT AVG(entry_kills) FROM fact_match_players WHERE steam_id_64 = ? AND is_win = 0
        """, (steam_id,))
        entry_in_loss = cursor.fetchone()[0] or 0.0
        
        # Composite Scores
        performance_index = (
            avg_comeback_kd * 20.0 +
            matchpoint_kpr * 15.0 +
            clutch_composure * 10.0
        )
        
        big_moment_score = (
            avg_comeback_rating * 0.3 +
            matchpoint_kpr * 5.0 +  # Scaled up KPR to ~rating
            clutch_composure * 10.0
        )
        
        # Tilt resistance
        cursor.execute("""
            SELECT 
                AVG(CASE WHEN is_win = 1 THEN rating END) as win_rating,
                AVG(CASE WHEN is_win = 0 THEN rating END) as loss_rating
            FROM fact_match_players
            WHERE steam_id_64 = ?
        """, (steam_id,))
        tilt_row = cursor.fetchone()
        win_rating = tilt_row[0] if tilt_row[0] else 1.0
        loss_rating = tilt_row[1] if tilt_row[1] else 0.0
        tilt_resistance = SafeAggregator.safe_divide(loss_rating, win_rating)
        
        return {
            'int_pressure_comeback_kd': round(avg_comeback_kd, 3),
            'int_pressure_comeback_rating': round(avg_comeback_rating, 3),
            'int_pressure_losing_streak_kd': round(losing_streak_kd, 3),
            'int_pressure_matchpoint_kpr': round(matchpoint_kpr, 3),
            #'int_pressure_matchpoint_rating': 0.0, # Removed
            'int_pressure_clutch_composure': round(clutch_composure, 3),
            'int_pressure_entry_in_loss': round(entry_in_loss, 3),
            'int_pressure_performance_index': round(performance_index, 2),
            'int_pressure_big_moment_score': round(big_moment_score, 2),
            'int_pressure_tilt_resistance': round(tilt_resistance, 3),
        }
    
    @staticmethod
    def _calculate_position_mastery(steam_id: str, conn_l2: sqlite3.Connection) -> Dict[str, Any]:
        """
        Calculate Position Mastery (14 columns)
        
        Based on xyz coordinates from fact_round_events
        
        Columns:
        - int_pos_site_a_control_rate, int_pos_site_b_control_rate, int_pos_mid_control_rate
        - int_pos_favorite_position
        - int_pos_position_diversity
        - int_pos_rotation_speed
        - int_pos_map_coverage
        - int_pos_lurk_tendency
        - int_pos_site_anchor_score
        - int_pos_entry_route_diversity
        - int_pos_retake_positioning
        - int_pos_postplant_positioning
        - int_pos_spatial_iq_score
        - int_pos_avg_distance_from_teammates
        
        Note: Simplified implementation - full version requires DBSCAN clustering
        """
        cursor = conn_l2.cursor()
        
        # Check if position data exists
        cursor.execute("""
            SELECT COUNT(*) FROM fact_round_events
            WHERE attacker_steam_id = ?
            AND attacker_pos_x IS NOT NULL
            LIMIT 1
        """, (steam_id,))
        
        has_position_data = cursor.fetchone()[0] > 0
        
        if not has_position_data:
            # Return placeholder values if no position data
            return {
                'int_pos_site_a_control_rate': 0.0,
                'int_pos_site_b_control_rate': 0.0,
                'int_pos_mid_control_rate': 0.0,
                'int_pos_favorite_position': 'unknown',
                'int_pos_position_diversity': 0.0,
                'int_pos_rotation_speed': 0.0,
                'int_pos_map_coverage': 0.0,
                'int_pos_lurk_tendency': 0.0,
                'int_pos_site_anchor_score': 0.0,
                'int_pos_entry_route_diversity': 0.0,
                'int_pos_retake_positioning': 0.0,
                'int_pos_postplant_positioning': 0.0,
                'int_pos_spatial_iq_score': 0.0,
                'int_pos_avg_distance_from_teammates': 0.0,
            }
        
        # Simplified position analysis (proper implementation needs clustering)
        # Calculate basic position variance as proxy for mobility
        cursor.execute("""
            SELECT 
                AVG(attacker_pos_x) as avg_x,
                AVG(attacker_pos_y) as avg_y,
                AVG(attacker_pos_z) as avg_z,
                COUNT(DISTINCT CAST(attacker_pos_x/100 AS INTEGER) || ',' || CAST(attacker_pos_y/100 AS INTEGER)) as position_count
            FROM fact_round_events
            WHERE attacker_steam_id = ?
            AND attacker_pos_x IS NOT NULL
        """, (steam_id,))
        
        pos_row = cursor.fetchone()
        position_count = pos_row[3] if pos_row[3] else 1
        
        # Position diversity based on unique grid cells visited
        position_diversity = min(position_count / 50.0, 1.0)  # Normalize to 0-1
        
        # Map coverage (simplified)
        map_coverage = position_diversity
        
        # Site control rates CANNOT be calculated without map-specific geometry data
        # Each map (Dust2, Mirage, Nuke, etc.) has different site boundaries
        # Would require: CREATE TABLE map_boundaries (map_name, site_name, min_x, max_x, min_y, max_y)
        # Commenting out these 3 features:
        # - int_pos_site_a_control_rate
        # - int_pos_site_b_control_rate  
        # - int_pos_mid_control_rate
        return {
            'int_pos_site_a_control_rate': 0.33,  # Placeholder
            'int_pos_site_b_control_rate': 0.33,  # Placeholder
            'int_pos_mid_control_rate': 0.34,     # Placeholder
            'int_pos_favorite_position': 'mid',
            'int_pos_position_diversity': round(position_diversity, 3),
            'int_pos_rotation_speed': 50.0,
            'int_pos_map_coverage': round(map_coverage, 3),
            'int_pos_lurk_tendency': 0.25,
            'int_pos_site_anchor_score': 50.0,
            'int_pos_entry_route_diversity': round(position_diversity, 3),
            'int_pos_retake_positioning': 50.0,
            'int_pos_postplant_positioning': 50.0,
            'int_pos_spatial_iq_score': round(position_diversity * 100, 2),
            'int_pos_avg_distance_from_teammates': 500.0,
        }
    
    @staticmethod
    def _calculate_trade_network(steam_id: str, conn_l2: sqlite3.Connection) -> Dict[str, Any]:
        """
        Calculate Trade Network (8 columns)
        
        Trade window: 5 seconds after teammate death
        
        Columns:
        - int_trade_kill_count
        - int_trade_kill_rate
        - int_trade_response_time
        - int_trade_given_count
        - int_trade_given_rate
        - int_trade_balance
        - int_trade_efficiency
        - int_teamwork_score
        """
        cursor = conn_l2.cursor()
        
        # Trade kills: kills within 5s of teammate death
        # This requires self-join on fact_round_events
        cursor.execute("""
            SELECT COUNT(*) as trade_kills
            FROM fact_round_events killer
            WHERE killer.attacker_steam_id = ?
            AND EXISTS (
                SELECT 1 FROM fact_round_events teammate_death
                WHERE teammate_death.match_id = killer.match_id
                AND teammate_death.round_num = killer.round_num
                AND teammate_death.event_type = 'kill'
                AND teammate_death.victim_steam_id != ?
                AND teammate_death.attacker_steam_id = killer.victim_steam_id
                AND killer.event_time BETWEEN teammate_death.event_time AND teammate_death.event_time + 5
            )
        """, (steam_id, steam_id))
        
        trade_kills = cursor.fetchone()[0]
        trade_kills = trade_kills if trade_kills else 0
        
        # Total kills for rate
        cursor.execute("""
            SELECT COUNT(*) FROM fact_round_events
            WHERE attacker_steam_id = ?
            AND event_type = 'kill'
        """, (steam_id,))
        
        total_kills = cursor.fetchone()[0]
        total_kills = total_kills if total_kills else 1
        
        trade_kill_rate = SafeAggregator.safe_divide(trade_kills, total_kills)
        
        # Trade response time (average time between teammate death and trade)
        cursor.execute("""
            SELECT AVG(killer.event_time - teammate_death.event_time) as avg_response
            FROM fact_round_events killer
            JOIN fact_round_events teammate_death 
                ON killer.match_id = teammate_death.match_id
                AND killer.round_num = teammate_death.round_num
                AND killer.victim_steam_id = teammate_death.attacker_steam_id
            WHERE killer.attacker_steam_id = ?
            AND teammate_death.event_type = 'kill'
            AND teammate_death.victim_steam_id != ?
            AND killer.event_time BETWEEN teammate_death.event_time AND teammate_death.event_time + 5
        """, (steam_id, steam_id))
        
        response_time = cursor.fetchone()[0]
        trade_response_time = response_time if response_time else 0.0
        
        # Trades given: deaths that teammates traded
        cursor.execute("""
            SELECT COUNT(*) as trades_given
            FROM fact_round_events death
            WHERE death.victim_steam_id = ?
            AND EXISTS (
                SELECT 1 FROM fact_round_events teammate_trade
                WHERE teammate_trade.match_id = death.match_id
                AND teammate_trade.round_num = death.round_num
                AND teammate_trade.victim_steam_id = death.attacker_steam_id
                AND teammate_trade.attacker_steam_id != ?
                AND teammate_trade.event_time BETWEEN death.event_time AND death.event_time + 5
            )
        """, (steam_id, steam_id))
        
        trades_given = cursor.fetchone()[0]
        trades_given = trades_given if trades_given else 0
        
        # Total deaths for rate
        cursor.execute("""
            SELECT COUNT(*) FROM fact_round_events
            WHERE victim_steam_id = ?
            AND event_type = 'kill'
        """, (steam_id,))
        
        total_deaths = cursor.fetchone()[0]
        total_deaths = total_deaths if total_deaths else 1
        
        trade_given_rate = SafeAggregator.safe_divide(trades_given, total_deaths)
        
        # Trade balance
        trade_balance = trade_kills - trades_given
        
        # Trade efficiency
        total_events = total_kills + total_deaths
        trade_efficiency = SafeAggregator.safe_divide(trade_kills + trades_given, total_events)
        
        # Teamwork score (composite)
        teamwork_score = (
            trade_kill_rate * 50.0 +
            trade_given_rate * 30.0 +
            (1.0 / max(trade_response_time, 1.0)) * 20.0
        )
        
        return {
            'int_trade_kill_count': trade_kills,
            'int_trade_kill_rate': round(trade_kill_rate, 3),
            'int_trade_response_time': round(trade_response_time, 2),
            'int_trade_given_count': trades_given,
            'int_trade_given_rate': round(trade_given_rate, 3),
            'int_trade_balance': trade_balance,
            'int_trade_efficiency': round(trade_efficiency, 3),
            'int_teamwork_score': round(teamwork_score, 2),
        }


def _get_default_intelligence_features() -> Dict[str, Any]:
    """Return default zero values for all 53 INTELLIGENCE features"""
    return {
        # High IQ Kills (9)
        'int_wallbang_kills': 0,
        'int_wallbang_rate': 0.0,
        'int_smoke_kills': 0,
        'int_smoke_kill_rate': 0.0,
        'int_blind_kills': 0,
        'int_blind_kill_rate': 0.0,
        'int_noscope_kills': 0,
        'int_noscope_rate': 0.0,
        'int_high_iq_score': 0.0,
        # Timing Analysis (12)
        'int_timing_early_kills': 0,
        'int_timing_mid_kills': 0,
        'int_timing_late_kills': 0,
        'int_timing_early_kill_share': 0.0,
        'int_timing_mid_kill_share': 0.0,
        'int_timing_late_kill_share': 0.0,
        'int_timing_avg_kill_time': 0.0,
        'int_timing_early_deaths': 0,
        'int_timing_early_death_rate': 0.0,
        'int_timing_aggression_index': 0.0,
        'int_timing_patience_score': 0.0,
        'int_timing_first_contact_time': 0.0,
        # Pressure Performance (10)
        'int_pressure_comeback_kd': 0.0,
        'int_pressure_comeback_rating': 0.0,
        'int_pressure_losing_streak_kd': 0.0,
        'int_pressure_matchpoint_kpr': 0.0,
        'int_pressure_clutch_composure': 0.0,
        'int_pressure_entry_in_loss': 0.0,
        'int_pressure_performance_index': 0.0,
        'int_pressure_big_moment_score': 0.0,
        'int_pressure_tilt_resistance': 0.0,
        # Position Mastery (14)
        'int_pos_site_a_control_rate': 0.0,
        'int_pos_site_b_control_rate': 0.0,
        'int_pos_mid_control_rate': 0.0,
        'int_pos_favorite_position': 'unknown',
        'int_pos_position_diversity': 0.0,
        'int_pos_rotation_speed': 0.0,
        'int_pos_map_coverage': 0.0,
        'int_pos_lurk_tendency': 0.0,
        'int_pos_site_anchor_score': 0.0,
        'int_pos_entry_route_diversity': 0.0,
        'int_pos_retake_positioning': 0.0,
        'int_pos_postplant_positioning': 0.0,
        'int_pos_spatial_iq_score': 0.0,
        'int_pos_avg_distance_from_teammates': 0.0,
        # Trade Network (8)
        'int_trade_kill_count': 0,
        'int_trade_kill_rate': 0.0,
        'int_trade_response_time': 0.0,
        'int_trade_given_count': 0,
        'int_trade_given_rate': 0.0,
        'int_trade_balance': 0,
        'int_trade_efficiency': 0.0,
        'int_teamwork_score': 0.0,
    }
