import json

from web.database import query_db


class NarrativeService:
    AWARD_TYPE_LABELS = {
        'daily': '单日最佳',
        'weekly': '星期最佳',
        'monthly': '月度最佳',
        'quarterly': '季度最佳',
        'yearly': '年度最佳',
    }

    PERFORMANCE_LABELS = {
        'surge': '状态爆发',
        'above_form': '高于近期',
        'stable': '稳定发挥',
        'below_form': '低于近期',
        'slump': '状态低迷',
        'insufficient_sample': '样本不足',
    }

    RECORD_LABELS = {
        'highest_rating': '最高 Rating',
        'most_kills': '最多击杀',
        'highest_adr': '最高 ADR',
        'highest_kd': '最高 K/D',
        'most_headshots': '最多爆头',
    }

    @staticmethod
    def _identity_map(steam_ids):
        steam_ids = sorted({str(value) for value in steam_ids if value})
        if not steam_ids:
            return {}
        placeholders = ','.join('?' for _ in steam_ids)
        rows = query_db(
            'l2',
            f"""
            SELECT steam_id_64, username, avatar_url
            FROM dim_players
            WHERE steam_id_64 IN ({placeholders})
            """,
            steam_ids,
        )
        return {str(row['steam_id_64']): dict(row) for row in rows}

    @staticmethod
    def get_match_report(match_id):
        report = query_db(
            'l3',
            'SELECT * FROM dm_match_reports WHERE match_id = ?',
            [match_id],
            one=True,
        )
        if not report:
            return None
        result = dict(report)
        player_rows = query_db(
            'l3',
            """
            SELECT *
            FROM dm_match_player_reports
            WHERE match_id = ?
            ORDER BY rating DESC
            """,
            [match_id],
        )
        player_reports = [dict(row) for row in player_rows]
        steam_ids = [row['steam_id_64'] for row in player_reports]
        steam_ids.extend([
            result.get('mvp_steam_id'),
            result.get('improver_steam_id'),
        ])
        duo = json.loads(result.get('strongest_duo_json') or 'null')
        if duo:
            steam_ids.extend(duo.get('steam_ids') or [])
        identities = NarrativeService._identity_map(steam_ids)

        for player in player_reports:
            player['identity'] = identities.get(
                str(player['steam_id_64']),
                {'username': player['steam_id_64']},
            )
            player['performance_text'] = NarrativeService.PERFORMANCE_LABELS.get(
                player['performance_label'],
                player['performance_label'],
            )
            record_keys = json.loads(player['record_keys_json'] or '[]')
            player['record_labels'] = [
                NarrativeService.RECORD_LABELS.get(key, key)
                for key in record_keys
            ]
        result['players'] = player_reports
        result['mvp'] = identities.get(str(result.get('mvp_steam_id')), {})
        result['improver'] = identities.get(
            str(result.get('improver_steam_id')),
            {},
        )
        if duo:
            duo['players'] = [
                identities.get(str(steam_id), {'username': steam_id})
                for steam_id in duo.get('steam_ids', [])
            ]
        result['strongest_duo'] = duo
        return result

    @staticmethod
    def list_match_reports(limit=30):
        rows = query_db(
            'l3',
            """
            SELECT *
            FROM dm_match_reports
            ORDER BY match_date DESC, match_id DESC
            LIMIT ?
            """,
            [limit],
        )
        identities = NarrativeService._identity_map(
            [row['mvp_steam_id'] for row in rows]
        )
        result = []
        for row in rows:
            item = dict(row)
            item['mvp'] = identities.get(str(item['mvp_steam_id']), {})
            result.append(item)
        return result

    @staticmethod
    def list_awards(award_type=None, limit=100):
        args = []
        where = ''
        if award_type in NarrativeService.AWARD_TYPE_LABELS:
            where = 'WHERE award_type = ?'
            args.append(award_type)
        args.append(limit)
        rows = query_db(
            'l3',
            f"""
            SELECT *
            FROM dm_player_awards
            {where}
            ORDER BY period_start DESC,
                     CASE award_type
                         WHEN 'yearly' THEN 1
                         WHEN 'quarterly' THEN 2
                         WHEN 'monthly' THEN 3
                         WHEN 'weekly' THEN 4
                         ELSE 5
                     END
            LIMIT ?
            """,
            args,
        )
        identities = NarrativeService._identity_map(
            [row['steam_id_64'] for row in rows]
        )
        result = []
        for row in rows:
            item = dict(row)
            item['identity'] = identities.get(str(item['steam_id_64']), {})
            item['award_label'] = NarrativeService.AWARD_TYPE_LABELS.get(
                item['award_type'],
                item['award_type'],
            )
            result.append(item)
        return result

    @staticmethod
    def get_award_summary():
        rows = query_db(
            'l3',
            """
            SELECT
                steam_id_64,
                COUNT(*) AS awards,
                SUM(CASE WHEN award_type = 'daily' THEN 1 ELSE 0 END) AS daily,
                SUM(CASE WHEN award_type = 'weekly' THEN 1 ELSE 0 END) AS weekly,
                SUM(CASE WHEN award_type = 'monthly' THEN 1 ELSE 0 END) AS monthly,
                SUM(CASE WHEN award_type = 'quarterly' THEN 1 ELSE 0 END) AS quarterly,
                SUM(CASE WHEN award_type = 'yearly' THEN 1 ELSE 0 END) AS yearly
            FROM dm_player_awards
            GROUP BY steam_id_64
            ORDER BY yearly DESC, quarterly DESC, monthly DESC,
                     weekly DESC, daily DESC
            """,
        )
        identities = NarrativeService._identity_map(
            [row['steam_id_64'] for row in rows]
        )
        result = []
        for row in rows:
            item = dict(row)
            item['identity'] = identities.get(str(item['steam_id_64']), {})
            result.append(item)
        return result

    @staticmethod
    def get_seasons():
        rows = query_db(
            'l3',
            """
            SELECT *
            FROM dm_team_season_stats
            ORDER BY season_key DESC
            """,
        )
        identities = NarrativeService._identity_map(
            [row['top_player_steam_id'] for row in rows]
        )
        result = []
        for row in rows:
            item = dict(row)
            item['top_player'] = identities.get(
                str(item['top_player_steam_id']),
                {},
            )
            result.append(item)
        return result

    @staticmethod
    def get_player_honors(steam_id, limit=20):
        rows = query_db(
            'l3',
            """
            SELECT *
            FROM dm_player_awards
            WHERE steam_id_64 = ?
            ORDER BY period_start DESC
            LIMIT ?
            """,
            [steam_id, limit],
        )
        awards = []
        for row in rows:
            item = dict(row)
            item['award_label'] = NarrativeService.AWARD_TYPE_LABELS.get(
                item['award_type'],
                item['award_type'],
            )
            awards.append(item)
        record_rows = query_db(
            'l3',
            """
            SELECT *
            FROM dm_player_record_events
            WHERE steam_id_64 = ?
            ORDER BY match_date DESC
            LIMIT ?
            """,
            [steam_id, limit],
        )
        record_events = []
        for row in record_rows:
            item = dict(row)
            item['record_label'] = NarrativeService.RECORD_LABELS.get(
                item['record_key'],
                item['record_key'],
            )
            record_events.append(item)
        return {
            'awards': awards,
            'award_count': len(awards),
            'record_events': record_events,
        }

    @staticmethod
    def get_player_identity(steam_id):
        feature = query_db(
            'l3',
            """
            SELECT
                total_matches,
                first_match_date,
                last_match_date,
                core_top_weapon,
                meta_map_best_map,
                tier_percentile
            FROM dm_player_features
            WHERE steam_id_64 = ?
            """,
            [steam_id],
            one=True,
        )
        roster = query_db(
            'web',
            """
            SELECT
                member.member_role,
                version.name AS roster_version,
                (
                    SELECT MIN(history_version.effective_from)
                    FROM team_roster_members history_member
                    JOIN team_roster_versions history_version
                      ON history_version.id = history_member.roster_version_id
                    WHERE history_member.steam_id_64 = member.steam_id_64
                ) AS effective_from
            FROM team_roster_members member
            JOIN team_roster_versions version
              ON version.id = member.roster_version_id
            WHERE member.steam_id_64 = ?
            ORDER BY version.is_current DESC, version.effective_from DESC
            LIMIT 1
            """,
            [steam_id],
            one=True,
        )
        awards = query_db(
            'l3',
            """
            SELECT award_type, COUNT(*) AS count
            FROM dm_player_awards
            WHERE steam_id_64 = ?
            GROUP BY award_type
            """,
            [steam_id],
        )
        result = dict(feature) if feature else {}
        if roster:
            result.update(dict(roster))
        result['awards_by_type'] = {
            row['award_type']: row['count'] for row in awards
        }
        result['award_count'] = sum(result['awards_by_type'].values())
        return result