"""
BasicProcessor - Tier 1: CORE Features (41 columns)

Calculates fundamental player statistics from fact_match_players:
- Basic Performance (15 columns): rating, kd, adr, kast, rws, hs%, kills, deaths, assists
- Match Stats (8 columns): win_rate, mvps, duration, elo
- Weapon Stats (12 columns): awp, knife, zeus, diversity
- Objective Stats (6 columns): plants, defuses, flash_assists
"""

import sqlite3
from typing import Dict, Any
from .base_processor import BaseFeatureProcessor, SafeAggregator, WeaponCategories


class BasicProcessor(BaseFeatureProcessor):
    """Tier 1 CORE processor - Direct aggregations from fact_match_players"""
    
    MIN_MATCHES_REQUIRED = 1  # Basic stats work with any match count
    
    @staticmethod
    def calculate(steam_id: str, conn_l2: sqlite3.Connection) -> Dict[str, Any]:
        """
        Calculate all Tier 1 CORE features (41 columns)
        
        Returns dict with keys:
        - core_avg_rating, core_avg_rating2, core_avg_kd, core_avg_adr, etc.
        """
        features = {}
        
        # Get match count first
        match_count = BaseFeatureProcessor.get_player_match_count(steam_id, conn_l2)
        if match_count == 0:
            return _get_default_features()
        
        # Calculate each sub-section
        features.update(BasicProcessor._calculate_basic_performance(steam_id, conn_l2))
        features.update(BasicProcessor._calculate_match_stats(steam_id, conn_l2))
        features.update(BasicProcessor._calculate_weapon_stats(steam_id, conn_l2))
        features.update(BasicProcessor._calculate_objective_stats(steam_id, conn_l2))
        
        return features
    
    @staticmethod
    def _calculate_basic_performance(steam_id: str, conn_l2: sqlite3.Connection) -> Dict[str, Any]:
        """
        Calculate Basic Performance (15 columns)
        
        Columns:
        - core_avg_rating, core_avg_rating2
        - core_avg_kd, core_avg_adr, core_avg_kast, core_avg_rws
        - core_avg_hs_kills, core_hs_rate
        - core_total_kills, core_total_deaths, core_total_assists, core_avg_assists
        - core_kpr, core_dpr, core_survival_rate
        """
        cursor = conn_l2.cursor()
        
        # Main aggregation query
        cursor.execute("""
            SELECT 
                AVG(rating) as avg_rating,
                AVG(rating2) as avg_rating2,
                AVG(CAST(kills AS REAL) / NULLIF(deaths, 0)) as avg_kd,
                AVG(adr) as avg_adr,
                AVG(kast) as avg_kast,
                AVG(rws) as avg_rws,
                AVG(headshot_count) as avg_hs_kills,
                SUM(kills) as total_kills,
                SUM(deaths) as total_deaths,
                SUM(headshot_count) as total_hs,
                SUM(assists) as total_assists,
                AVG(assists) as avg_assists,
                SUM(round_total) as total_rounds
            FROM fact_match_players
            WHERE steam_id_64 = ?
        """, (steam_id,))
        
        row = cursor.fetchone()
        
        if not row:
            return {}
        
        total_kills = row[7] if row[7] else 0
        total_deaths = row[8] if row[8] else 1
        total_hs = row[9] if row[9] else 0
        total_rounds = row[12] if row[12] else 1
        
        return {
            'core_avg_rating': round(row[0], 3) if row[0] else 0.0,
            'core_avg_rating2': round(row[1], 3) if row[1] else 0.0,
            'core_avg_kd': round(row[2], 3) if row[2] else 0.0,
            'core_avg_adr': round(row[3], 2) if row[3] else 0.0,
            'core_avg_kast': round(row[4], 3) if row[4] else 0.0,
            'core_avg_rws': round(row[5], 2) if row[5] else 0.0,
            'core_avg_hs_kills': round(row[6], 2) if row[6] else 0.0,
            'core_hs_rate': round(total_hs / total_kills, 3) if total_kills > 0 else 0.0,
            'core_total_kills': total_kills,
            'core_total_deaths': total_deaths,
            'core_total_assists': row[10] if row[10] else 0,
            'core_avg_assists': round(row[11], 2) if row[11] else 0.0,
            'core_kpr': round(total_kills / total_rounds, 3) if total_rounds > 0 else 0.0,
            'core_dpr': round(total_deaths / total_rounds, 3) if total_rounds > 0 else 0.0,
            'core_survival_rate': round((total_rounds - total_deaths) / total_rounds, 3) if total_rounds > 0 else 0.0,
        }
    
    @staticmethod
    def _calculate_flash_assists(steam_id: str, conn_l2: sqlite3.Connection) -> int:
        """
        Calculate flash assists from fact_match_players (Total - Damage Assists)
        Returns total flash assist count (Estimated)
        """
        cursor = conn_l2.cursor()
        
        # NOTE: Flash Assist Logic
        # Source 'flash_assists' is often 0.
        # User Logic: Flash Assists = Total Assists - Damage Assists (assisted_kill)
        # We take MAX(0, diff) to avoid negative numbers if assisted_kill definition varies.
        
        cursor.execute("""
            SELECT SUM(MAX(0, assists - assisted_kill))
            FROM fact_match_players
            WHERE steam_id_64 = ?
        """, (steam_id,))
        
        res = cursor.fetchone()
        if res and res[0] is not None:
            return res[0]
            
        return 0
    
    @staticmethod
    def _calculate_match_stats(steam_id: str, conn_l2: sqlite3.Connection) -> Dict[str, Any]:
        """
        Calculate Match Stats (8 columns)
        
        Columns:
        - core_win_rate, core_wins, core_losses
        - core_avg_match_duration
        - core_avg_mvps, core_mvp_rate
        - core_avg_elo_change, core_total_elo_gained
        """
        cursor = conn_l2.cursor()
        
        # Win/loss stats
        cursor.execute("""
            SELECT 
                COUNT(*) as total_matches,
                SUM(CASE WHEN is_win = 1 THEN 1 ELSE 0 END) as wins,
                SUM(CASE WHEN is_win = 0 THEN 1 ELSE 0 END) as losses,
                AVG(mvp_count) as avg_mvps,
                SUM(mvp_count) as total_mvps
            FROM fact_match_players
            WHERE steam_id_64 = ?
        """, (steam_id,))
        
        row = cursor.fetchone()
        total_matches = row[0] if row[0] else 0
        wins = row[1] if row[1] else 0
        losses = row[2] if row[2] else 0
        avg_mvps = row[3] if row[3] else 0.0
        total_mvps = row[4] if row[4] else 0
        
        # Match duration (from fact_matches)
        cursor.execute("""
            SELECT AVG(m.duration) as avg_duration
            FROM fact_matches m
            JOIN fact_match_players p ON m.match_id = p.match_id
            WHERE p.steam_id_64 = ?
        """, (steam_id,))
        
        duration_row = cursor.fetchone()
        avg_duration = duration_row[0] if duration_row and duration_row[0] else 0
        
        # ELO stats (from elo_change column)
        cursor.execute("""
            SELECT 
                AVG(elo_change) as avg_elo_change,
                SUM(elo_change) as total_elo_gained
            FROM fact_match_players
            WHERE steam_id_64 = ?
        """, (steam_id,))
        
        elo_row = cursor.fetchone()
        avg_elo_change = elo_row[0] if elo_row and elo_row[0] else 0.0
        total_elo_gained = elo_row[1] if elo_row and elo_row[1] else 0.0
        
        return {
            'core_win_rate': round(wins / total_matches, 3) if total_matches > 0 else 0.0,
            'core_wins': wins,
            'core_losses': losses,
            'core_avg_match_duration': int(avg_duration),
            'core_avg_mvps': round(avg_mvps, 2),
            'core_mvp_rate': round(total_mvps / total_matches, 2) if total_matches > 0 else 0.0,
            'core_avg_elo_change': round(avg_elo_change, 2),
            'core_total_elo_gained': round(total_elo_gained, 2),
        }
    
    @staticmethod
    def _calculate_weapon_stats(steam_id: str, conn_l2: sqlite3.Connection) -> Dict[str, Any]:
        """
        Calculate Weapon Stats (12 columns)
        
        Columns:
        - core_avg_awp_kills, core_awp_usage_rate
        - core_avg_knife_kills, core_avg_zeus_kills, core_zeus_buy_rate
        - core_top_weapon, core_top_weapon_kills, core_top_weapon_hs_rate
        - core_weapon_diversity
        - core_rifle_hs_rate, core_pistol_hs_rate
        - core_smg_kills_total
        """
        cursor = conn_l2.cursor()
        
        # AWP/Knife/Zeus stats from fact_round_events
        cursor.execute("""
            SELECT 
                weapon,
                COUNT(*) as kill_count
            FROM fact_round_events
            WHERE attacker_steam_id = ?
            AND weapon IN ('AWP', 'Knife', 'Zeus', 'knife', 'awp', 'zeus')
            GROUP BY weapon
        """, (steam_id,))
        
        awp_kills = 0
        knife_kills = 0
        zeus_kills = 0
        for weapon, kills in cursor.fetchall():
            weapon_lower = weapon.lower() if weapon else ''
            if weapon_lower == 'awp':
                awp_kills += kills
            elif weapon_lower == 'knife':
                knife_kills += kills
            elif weapon_lower == 'zeus':
                zeus_kills += kills
        
        # Get total matches count for rates
        cursor.execute("""
            SELECT COUNT(DISTINCT match_id)
            FROM fact_match_players
            WHERE steam_id_64 = ?
        """, (steam_id,))
        total_matches = cursor.fetchone()[0] or 1
        
        avg_awp = awp_kills / total_matches
        avg_knife = knife_kills / total_matches
        avg_zeus = zeus_kills / total_matches
        
        # Flash assists from fact_round_events
        flash_assists = BasicProcessor._calculate_flash_assists(steam_id, conn_l2)
        avg_flash_assists = flash_assists / total_matches
        
        # Top weapon from fact_round_events
        cursor.execute("""
            SELECT 
                weapon,
                COUNT(*) as kill_count,
                SUM(CASE WHEN is_headshot = 1 THEN 1 ELSE 0 END) as hs_count
            FROM fact_round_events
            WHERE attacker_steam_id = ?
            AND weapon IS NOT NULL
            AND weapon != 'unknown'
            GROUP BY weapon
            ORDER BY kill_count DESC
            LIMIT 1
        """, (steam_id,))
        
        weapon_row = cursor.fetchone()
        top_weapon = weapon_row[0] if weapon_row else "unknown"
        top_weapon_kills = weapon_row[1] if weapon_row else 0
        top_weapon_hs = weapon_row[2] if weapon_row else 0
        top_weapon_hs_rate = top_weapon_hs / top_weapon_kills if top_weapon_kills > 0 else 0.0
        
        # Weapon diversity (number of distinct weapons with 10+ kills)
        cursor.execute("""
            SELECT COUNT(DISTINCT weapon) as weapon_count
            FROM (
                SELECT weapon, COUNT(*) as kills
                FROM fact_round_events
                WHERE attacker_steam_id = ?
                AND weapon IS NOT NULL
                GROUP BY weapon
                HAVING kills >= 10
            )
        """, (steam_id,))
        
        diversity_row = cursor.fetchone()
        weapon_diversity = diversity_row[0] if diversity_row else 0
        
        # Rifle/Pistol/SMG stats
        cursor.execute("""
            SELECT 
                weapon,
                COUNT(*) as kills,
                SUM(CASE WHEN is_headshot = 1 THEN 1 ELSE 0 END) as headshot_kills
            FROM fact_round_events
            WHERE attacker_steam_id = ?
            AND weapon IS NOT NULL
            GROUP BY weapon
        """, (steam_id,))
        
        rifle_kills = 0
        rifle_hs = 0
        pistol_kills = 0
        pistol_hs = 0
        smg_kills = 0
        awp_usage_count = 0
        
        for weapon, kills, hs in cursor.fetchall():
            category = WeaponCategories.get_category(weapon)
            if category == 'rifle':
                rifle_kills += kills
                rifle_hs += hs
            elif category == 'pistol':
                pistol_kills += kills
                pistol_hs += hs
            elif category == 'smg':
                smg_kills += kills
            elif weapon.lower() == 'awp':
                awp_usage_count += kills
        
        total_rounds = BaseFeatureProcessor.get_player_round_count(steam_id, conn_l2)
        
        return {
            'core_avg_awp_kills': round(avg_awp, 2),
            'core_awp_usage_rate': round(awp_usage_count / total_rounds, 3) if total_rounds > 0 else 0.0,
            'core_avg_knife_kills': round(avg_knife, 3),
            'core_avg_zeus_kills': round(avg_zeus, 3),
            'core_zeus_buy_rate': round(avg_zeus / total_matches, 3) if total_matches > 0 else 0.0,
            'core_avg_flash_assists': round(avg_flash_assists, 2),
            'core_top_weapon': top_weapon,
            'core_top_weapon_kills': top_weapon_kills,
            'core_top_weapon_hs_rate': round(top_weapon_hs_rate, 3),
            'core_weapon_diversity': weapon_diversity,
            'core_rifle_hs_rate': round(rifle_hs / rifle_kills, 3) if rifle_kills > 0 else 0.0,
            'core_pistol_hs_rate': round(pistol_hs / pistol_kills, 3) if pistol_kills > 0 else 0.0,
            'core_smg_kills_total': smg_kills,
        }
    
    @staticmethod
    def _calculate_objective_stats(steam_id: str, conn_l2: sqlite3.Connection) -> Dict[str, Any]:
        """
        Calculate Objective Stats (6 columns)
        
        Columns:
        - core_avg_plants, core_avg_defuses, core_avg_flash_assists
        - core_plant_success_rate, core_defuse_success_rate
        - core_objective_impact
        """
        cursor = conn_l2.cursor()
        
        # Get data from main table
        # Updated to use calculated flash assists formula
        
        # Calculate flash assists manually first (since column is 0)
        flash_assists_total = BasicProcessor._calculate_flash_assists(steam_id, conn_l2)
        match_count = BaseFeatureProcessor.get_player_match_count(steam_id, conn_l2)
        avg_flash_assists = flash_assists_total / match_count if match_count > 0 else 0.0
        
        cursor.execute("""
            SELECT 
                AVG(planted_bomb) as avg_plants,
                AVG(defused_bomb) as avg_defuses,
                SUM(planted_bomb) as total_plants,
                SUM(defused_bomb) as total_defuses
            FROM fact_match_players
            WHERE steam_id_64 = ?
        """, (steam_id,))
        
        row = cursor.fetchone()
        
        if not row:
            return {}
        
        avg_plants = row[0] if row[0] else 0.0
        avg_defuses = row[1] if row[1] else 0.0
        # avg_flash_assists computed above
        total_plants = row[2] if row[2] else 0
        total_defuses = row[3] if row[3] else 0
        
        # Get T side rounds
        cursor.execute("""
            SELECT COALESCE(SUM(round_total), 0)
            FROM fact_match_players_t
            WHERE steam_id_64 = ?
        """, (steam_id,))
        t_rounds = cursor.fetchone()[0] or 1
        
        # Get CT side rounds
        cursor.execute("""
            SELECT COALESCE(SUM(round_total), 0)
            FROM fact_match_players_ct
            WHERE steam_id_64 = ?
        """, (steam_id,))
        ct_rounds = cursor.fetchone()[0] or 1
        
        # Plant success rate: plants per T round
        plant_rate = total_plants / t_rounds if t_rounds > 0 else 0.0
        
        # Defuse success rate: approximate as defuses per CT round (simplified)
        defuse_rate = total_defuses / ct_rounds if ct_rounds > 0 else 0.0
        
        # Objective impact score: weighted combination
        objective_impact = (total_plants * 2.0 + total_defuses * 3.0 + avg_flash_assists * 0.5)
        
        return {
            'core_avg_plants': round(avg_plants, 2),
            'core_avg_defuses': round(avg_defuses, 2),
            'core_avg_flash_assists': round(avg_flash_assists, 2),
            'core_plant_success_rate': round(plant_rate, 3),
            'core_defuse_success_rate': round(defuse_rate, 3),
            'core_objective_impact': round(objective_impact, 2),
        }


def _get_default_features() -> Dict[str, Any]:
    """Return default zero values for all 41 CORE features"""
    return {
        # Basic Performance (15)
        'core_avg_rating': 0.0,
        'core_avg_rating2': 0.0,
        'core_avg_kd': 0.0,
        'core_avg_adr': 0.0,
        'core_avg_kast': 0.0,
        'core_avg_rws': 0.0,
        'core_avg_hs_kills': 0.0,
        'core_hs_rate': 0.0,
        'core_total_kills': 0,
        'core_total_deaths': 0,
        'core_total_assists': 0,
        'core_avg_assists': 0.0,
        'core_kpr': 0.0,
        'core_dpr': 0.0,
        'core_survival_rate': 0.0,
        # Match Stats (8)
        'core_win_rate': 0.0,
        'core_wins': 0,
        'core_losses': 0,
        'core_avg_match_duration': 0,
        'core_avg_mvps': 0.0,
        'core_mvp_rate': 0.0,
        'core_avg_elo_change': 0.0,
        'core_total_elo_gained': 0.0,
        # Weapon Stats (12)
        'core_avg_awp_kills': 0.0,
        'core_awp_usage_rate': 0.0,
        'core_avg_knife_kills': 0.0,
        'core_avg_zeus_kills': 0.0,
        'core_zeus_buy_rate': 0.0,
        'core_top_weapon': 'unknown',
        'core_top_weapon_kills': 0,
        'core_top_weapon_hs_rate': 0.0,
        'core_weapon_diversity': 0,
        'core_rifle_hs_rate': 0.0,
        'core_pistol_hs_rate': 0.0,
        'core_smg_kills_total': 0,
        # Objective Stats (6)
        'core_avg_plants': 0.0,
        'core_avg_defuses': 0.0,
        'core_avg_flash_assists': 0.0,
        'core_plant_success_rate': 0.0,
        'core_defuse_success_rate': 0.0,
        'core_objective_impact': 0.0,
    }