from .config import *
from .db import *
from .models import *

import pandas as pd
def get_leaderboard(reveal_prelim = False, hide_battle_votes = False, sort_by_elo = False, hide_proprietary = False):
    conn = get_db()
    cursor = conn.cursor()
    
    if hide_battle_votes:
        sql = '''
        SELECT m.name, 
               SUM(CASE WHEN v.username NOT LIKE '%_battle' AND v.vote = 1 THEN 1 ELSE 0 END) as upvote, 
               SUM(CASE WHEN v.username NOT LIKE '%_battle' AND v.vote = -1 THEN 1 ELSE 0 END) as downvote
        FROM model m
        LEFT JOIN vote v ON m.name = v.model
        GROUP BY m.name
        '''
    else:
        sql = '''
        SELECT name, 
               SUM(CASE WHEN vote = 1 THEN 1 ELSE 0 END) as upvote, 
               SUM(CASE WHEN vote = -1 THEN 1 ELSE 0 END) as downvote
        FROM model
        LEFT JOIN vote ON model.name = vote.model
        GROUP BY name
        '''
    
    cursor.execute(sql)
    data = cursor.fetchall()
    df = pd.DataFrame(data, columns=['name', 'upvote', 'downvote'])
    df['name'] = df['name'].replace(model_names).replace('Anonymous Sparkle', 'Fish Speech v1.5')
    
    # Calculate total votes and win rate
    df['votes'] = df['upvote'] + df['downvote']
    df['win_rate'] = (df['upvote'] / df['votes'] * 100).round(1)
    
    # Remove models with no votes
    df = df[df['votes'] > 0]

    # Filter out rows with insufficient votes if not revealing preliminary results
    if not reveal_prelim:
        df = df[df['votes'] > 500]

    ## Calculate ELO SCORE (kept as secondary metric)
    df['elo'] = 1200
    for i in range(len(df)):
        for j in range(len(df)):
            if i != j:
                try:
                    expected_a = 1 / (1 + 10 ** ((df['elo'].iloc[j] - df['elo'].iloc[i]) / 400))
                    expected_b = 1 / (1 + 10 ** ((df['elo'].iloc[i] - df['elo'].iloc[j]) / 400))
                    actual_a = df['upvote'].iloc[i] / df['votes'].iloc[i] if df['votes'].iloc[i] > 0 else 0.5
                    actual_b = df['upvote'].iloc[j] / df['votes'].iloc[j] if df['votes'].iloc[j] > 0 else 0.5
                    df.iloc[i, df.columns.get_loc('elo')] += 32 * (actual_a - expected_a)
                    df.iloc[j, df.columns.get_loc('elo')] += 32 * (actual_b - expected_b)
                except Exception as e:
                    print(f"Error in ELO calculation for rows {i} and {j}: {str(e)}")
                    continue
    df['elo'] = round(df['elo'])

    # Sort based on user preference
    sort_column = 'elo' if sort_by_elo else 'win_rate'
    df = df.sort_values(by=sort_column, ascending=False)
    df['order'] = ['#' + str(i + 1) for i in range(len(df))]
    
    # Select and order columns for display
    df = df[['order', 'name', 'win_rate', 'votes', 'elo']]
    
    # Remove proprietary models if filter is enabled
    if hide_proprietary:
        df = df[~df['name'].isin(closed_source)]
    
    # Convert DataFrame to markdown table with CSS styling
    markdown_table = """
<style>
/* Reset any Gradio table styles */
.leaderboard-table, 
.leaderboard-table th, 
.leaderboard-table td {
    border: none !important;
    border-collapse: separate !important;
    border-spacing: 0 !important;
}

.leaderboard-container {
    background: var(--background-fill-primary);
    border: 1px solid var(--border-color-primary);
    border-radius: 12px;
    padding: 4px;
    margin: 10px 0;
    width: 100%;
    overflow-x: auto;  /* Enable horizontal scroll */
}

.leaderboard-scroll {
    max-height: 600px;
    overflow-y: auto;
    border-radius: 8px;
}

.leaderboard-table {
    width: 100%;
    border-spacing: 0;
    border-collapse: separate;
    font-size: 15px;
    line-height: 1.5;
    table-layout: auto;  /* Allow flexible column widths */
}

.leaderboard-table th {
    background: var(--background-fill-secondary);
    color: var(--body-text-color);
    font-weight: 600;
    text-align: left;
    padding: 12px 16px;
    position: sticky;
    top: 0;
    z-index: 1;
}

.leaderboard-table th:after {
    content: '';
    position: absolute;
    left: 0;
    bottom: 0;
    width: 100%;
    border-bottom: 1px solid var(--border-color-primary);
}

.leaderboard-table td {
    padding: 12px 16px;
    color: var(--body-text-color);
}

.leaderboard-table tr td {
    border-bottom: 1px solid var(--border-color-primary);
}

.leaderboard-table tr:last-child td {
    border-bottom: none;
}

.leaderboard-table tr:hover td {
    background: var(--background-fill-secondary);
}

/* Column-specific styles */
.leaderboard-table .col-rank {
    width: 70px;
    min-width: 70px;  /* Prevent rank from shrinking */
}

.leaderboard-table .col-model {
    min-width: 200px;  /* Minimum width before scrolling */
}

.leaderboard-table .col-winrate {
    width: 100px;
    min-width: 100px;  /* Prevent win rate from shrinking */
}

.leaderboard-table .col-votes {
    width: 100px;
    min-width: 100px;  /* Prevent votes from shrinking */
}

.leaderboard-table .col-arena {
    width: 100px;
    min-width: 100px;  /* Prevent arena score from shrinking */
}

.win-rate {
    display: inline-block;
    font-weight: 600;
    padding: 4px 8px;
    border-radius: 6px;
    min-width: 65px;
    text-align: center;
}

.win-rate-excellent {
    background-color: var(--color-accent);
    color: var(--color-accent-foreground);
}

.win-rate-good {
    background-color: var(--color-accent-soft);
    color: var(--body-text-color);
}

.win-rate-average {
    background-color: var(--background-fill-secondary);
    color: var(--body-text-color);
    border: 1px solid var(--border-color-primary);
}

.win-rate-below {
    background-color: var(--error-background-fill);
    color: var(--body-text-color);
}

.model-link {
    color: var(--body-text-color) !important;
    text-decoration: none !important;
    border-bottom: 2px dashed rgba(128, 128, 128, 0.3);
}

.model-link:hover {
    color: var(--color-accent) !important;
    border-bottom-color: var(--color-accent) !important;
}

.proprietary-badge {
    display: inline-block;
    font-size: 12px;
    padding: 2px 6px;
    border-radius: 4px;
    background-color: var(--background-fill-secondary);
    color: var(--body-text-color);
    margin-left: 6px;
    border: 1px solid var(--border-color-primary);
}
</style>
<div class="leaderboard-container">
<div class="leaderboard-scroll">
<table class="leaderboard-table">
<thead>
<tr>
<th class="col-rank">Rank</th>
<th class="col-model">Model</th>
<th class="col-winrate">Win Rate</th>
<th class="col-votes">Votes</th>
""" + ("""<th class="col-arena">Arena Score</th>""" if sort_by_elo else "") + """
</tr>
</thead>
<tbody>
"""
    
    def get_win_rate_class(win_rate):
        if win_rate >= 60:
            return "win-rate-excellent"
        elif win_rate >= 55:
            return "win-rate-good"
        elif win_rate >= 45:
            return "win-rate-average"
        else:
            return "win-rate-below"
    
    for _, row in df.iterrows():
        win_rate_class = get_win_rate_class(row['win_rate'])
        win_rate_html = f'<span class="win-rate {win_rate_class}">{row["win_rate"]}%</span>'
        
        # Add link to model name if available and proprietary badge if closed source
        model_name = row['name']
        original_model_name = model_name
        if model_name in model_links:
            model_name = f'<a href="{model_links[model_name]}" target="_blank" class="model-link">{model_name}</a>'
        
        if original_model_name in closed_source:
            model_name += '<span class="proprietary-badge">Proprietary</span>'
        
        markdown_table += f'''<tr>
            <td class="col-rank">{row['order']}</td>
            <td class="col-model">{model_name}</td>
            <td class="col-winrate">{win_rate_html}</td>
            <td class="col-votes">{row['votes']:,}</td>''' + (
            f'''<td class="col-arena">{int(row['elo'])}</td>''' if sort_by_elo else ""
        ) + "</tr>\n"
    
    markdown_table += "</tbody></table></div></div>"
    return markdown_table