import os
from jinja2 import Template
from djagitit import db


sqlDir =os.path.join(os.path.dirname(__file__), '..', 'sql')


def get_end_date():

    rdb = db.ReportingDB()
    q = """
    select distinct max(calendar_date) as end_date
    from common.dim_calendar c
    where week_end_date_thursday < dateadd('day', -2, getdate())
    """
    df = rdb.query(q)
    return df

def get_country_weekly_streams(country_code, client_key, partner_keys):

    rdb = db.ReportingDB()
    template_query = open(os.path.join(sqlDir, 'country_weekly_streams.sql')).read()
    query = Template(template_query).render(
        country_code=country_code, 
        client_key=client_key,
        partner_keys=partner_keys
        )
    df = rdb.query(query)
    
    return df


def get_country_weekly_top100_tracks(country_code, partner_keys):

    rdb = db.ReportingDB()
    template_query = open(os.path.join(sqlDir, 'country_weekly_top100_tracks.sql')).read()
    query = Template(template_query).render(
        country_code=country_code,
        partner_keys=partner_keys
        )
    df = rdb.query(query)

    df.reset_index(inplace=True)
    df.rename(columns={'index': 'Rank', 'isrc_cd': 'ISRC', 'artist_name': 'Artist', 'product_name': 'Track', 'streams': 'Streams'}, inplace=True)
    df['Rank'] = df['Rank']+1

    return df


def get_country_weekly_top100_projects(country_code, partner_keys):

    rdb = db.ReportingDB()
    template_query = open(os.path.join(sqlDir, 'country_weekly_top100_projects.sql')).read()
    query = Template(template_query).render(
        country_code=country_code,
        partner_keys=partner_keys
        )
    df = rdb.query(query)

    df.reset_index(inplace=True)
    df.rename(columns={'index': 'Rank', 'project_id': 'Project No', 'project_artist_name': 'Artist', 'project_name': 'Project', 'streams': 'Streams'}, inplace=True)
    df['Rank'] = df['Rank']+1

    return df


def get_country_weekly_top100_artists(country_code, partner_keys):

    rdb = db.ReportingDB()
    template_query = open(os.path.join(sqlDir, 'country_weekly_top100_artists.sql')).read()
    query = Template(template_query).render(
        country_code=country_code,
        partner_keys=partner_keys
        )
    df = rdb.query(query)

    df.reset_index(inplace=True)
    df.rename(columns={'index': 'Rank', 'primary_artist_name': 'Artist', 'streams': 'Streams'}, inplace=True)
    df['Rank'] = df['Rank']+1

    return df


def process_metrics(df):

    last_week  = df['week_ft'].max()
    prev_week = df['week_ft'].min()

    lw_streams = df.query('week_ft==@last_week')['streams'].sum()
    pw_streams = df.query('week_ft==@prev_week')['streams'].sum()

    lw_premium = df.query('week_ft==@last_week and type=="Premium"')['streams'].sum()
    pw_premium = df.query('week_ft==@prev_week and type=="Premium"')['streams'].sum()

    lw_free = df.query('week_ft==@last_week and type=="Free"')['streams'].sum()
    pw_free = df.query('week_ft==@prev_week and type=="Free"')['streams'].sum()

    label1 = f'Total Streams Last Week: {lw_streams:,} ({100*lw_premium/lw_streams:.2f}% Premium)'
    label2 = f'Total streams previous week: {pw_streams:,} ({100*pw_premium/pw_streams:.2f}% Premium)'
    label3 = f'VS Previous Week: {(lw_streams - pw_streams) / pw_streams * 100:.2f}%'

    vs_color = 'red' if (lw_streams - pw_streams) < 0 else 'green'

    metrics = {
        'last_week': last_week,
        'prev_week': prev_week,
        'lw_streams': lw_streams,
        'pw_streams': pw_streams,
        'lw_premium': lw_premium,
        'pw_premium': pw_premium,
        'lw_free': lw_free,
        'pw_free': pw_free,
        'label1': label1,
        'label2': label2,
        'label3': label3,
        'vs_color': vs_color
    }
    
    return metrics


def process_main_table(df):

    pivot_table = df.pivot_table(
        index='report_date', 
        columns='partner_name', 
        values='streams', 
        aggfunc='sum',
        fill_value=0
    )

    pivot_table.reset_index(inplace=True)

    pivot_table = pivot_table.rename(columns={'report_date': 'Date'})

    # Check if any Amazon-related columns exist and create Amazon column
    amazon_columns = ['Amazon Free', 'Amazon Streaming', 'Prime Music']
    existing_amazon_columns = [col for col in amazon_columns if col in pivot_table.columns]

    if existing_amazon_columns:
        pivot_table['Amazon'] = pivot_table[existing_amazon_columns].sum(axis=1)
        pivot_table.drop(existing_amazon_columns, axis=1, inplace=True)

    # Calculate column totals (excluding Date column)
    column_totals = pivot_table.select_dtypes(include=['number']).sum()

    # Sort columns by total in descending order
    sorted_columns = column_totals.sort_values(ascending=False).index.tolist()

    # Reorder columns: Date first, then sorted columns
    pivot_table = pivot_table[['Date'] + sorted_columns]

    # Add total column
    pivot_table['Total'] = pivot_table.select_dtypes(include=['number']).sum(axis=1)

    # Prepare data for htmltemplate
    columns = pivot_table.columns.tolist()
    data = list(pivot_table.iterrows())
    
    return columns, data