from web.database import query_db


class PlayerProfileService:
    PERIOD_KEYS = (
        'career',
        'last_10',
        'last_20',
        'last_30',
        'days_30',
        'days_90',
    )

    @staticmethod
    def get_period_stats(steam_id):
        rows = query_db(
            'l3',
            """
            SELECT *
            FROM dm_player_period_stats
            WHERE steam_id_64 = ?
            ORDER BY CASE period_key
                WHEN 'career' THEN 1
                WHEN 'last_10' THEN 2
                WHEN 'last_20' THEN 3
                WHEN 'last_30' THEN 4
                WHEN 'days_30' THEN 5
                WHEN 'days_90' THEN 6
                ELSE 99
            END
            """,
            [steam_id],
        )
        return [dict(row) for row in rows]

    @staticmethod
    def get_period(steam_id, period_key):
        if period_key not in PlayerProfileService.PERIOD_KEYS:
            return None
        row = query_db(
            'l3',
            """
            SELECT *
            FROM dm_player_period_stats
            WHERE steam_id_64 = ? AND period_key = ?
            """,
            [steam_id, period_key],
            one=True,
        )
        return dict(row) if row else None

    @staticmethod
    def get_records(steam_id):
        rows = query_db(
            'l3',
            """
            SELECT *
            FROM dm_player_records
            WHERE steam_id_64 = ?
            ORDER BY CASE record_key
                WHEN 'highest_rating' THEN 1
                WHEN 'most_kills' THEN 2
                WHEN 'highest_adr' THEN 3
                WHEN 'highest_kd' THEN 4
                WHEN 'most_headshots' THEN 5
                WHEN 'longest_win_streak' THEN 6
                ELSE 99
            END
            """,
            [steam_id],
        )
        return [dict(row) for row in rows]

    @staticmethod
    def get_period_history(steam_id, period_key):
        period = PlayerProfileService.get_period(steam_id, period_key)
        if not period:
            return []
        rows = query_db(
            'l3',
            """
            SELECT
                match_date AS start_time,
                rating,
                kd_ratio,
                adr,
                kast,
                match_id,
                map_name,
                is_win,
                match_sequence AS match_index
            FROM dm_player_match_history
            WHERE steam_id_64 = ?
              AND match_date BETWEEN ? AND ?
            ORDER BY match_date, match_id
            """,
            [steam_id, period['period_start'], period['period_end']],
        )
        return [dict(row) for row in rows]

    @staticmethod
    def get_map_stats(steam_id):
        rows = query_db(
            'l3',
            """
            SELECT
                map_name,
                matches,
                wins,
                win_rate,
                avg_rating AS rating,
                avg_kd AS kd,
                avg_adr AS adr,
                avg_kast AS kast,
                best_rating,
                worst_rating
            FROM dm_player_map_stats
            WHERE steam_id_64 = ?
            ORDER BY matches DESC, map_name
            """,
            [steam_id],
        )
        return [dict(row) for row in rows]