# this file stores methods for use in routes.py and other files
# currently we implement logic to pull the first 35 song dict from the database

import os
import sys
import pandas as pd

from utils.sme_ds_utilities import DatabaseUtils
from utils.config import Config  

parent_dir = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
utils_dir = os.path.join(parent_dir, "utils")
sys.path.append(utils_dir)

TOP_K = 2000 # controls how many cards are shown in the first 35 curated
TOP_K_SOURCE_TABLE = 'first_35.current_events' #test : sandbox.test_table_current_events_with_track_score
TOP_K_USE_DUMMY_DATA = False # if true, uses dummy data - for testing

#im temporarily throwing this in here to get curated version populated - will remove afterward and replace with kareem's query
def get_query_first_35_top_curated():
    """
    Builds a query to get the first 35 top tracks curated
    
    This pulls the latest anomaly_score for each track across sources,
    averages them, and ranks by anomaly score (interesting score)
    
    Return:
        str : query to pull the top tracks
    """

    query = f"""
        WITH latest_scores AS (
            SELECT 
                isrc_cd,
                product_name,
                artist_name,
                fin_label_parent_name,
                company_brand_name,
                source_of_stream,
                anomaly_score,
                artwork_url,
                streams,
                ROW_NUMBER() OVER (
                    PARTITION BY isrc_cd, source_of_stream
                    ORDER BY decay_day DESC
                ) AS rank
            FROM 
                {TOP_K_SOURCE_TABLE}
            WHERE
                source_of_stream IN ('spotify', 'spotify_lean_back', 'apple', 'apple_lean_back', 'total', 'total_lean_back')
                AND anomaly_score IS NOT NULL
        ),
        pivoted_scores AS (
            SELECT 
                isrc_cd,
                product_name,
                artist_name,
                MAX(fin_label_parent_name) as label_parent_name,
                MAX(company_brand_name) as label_filter_name,
                MAX(artwork_url) as artwork_url,
                SUM(CASE WHEN source_of_stream IN ('total', 'total_lean_forward', 'total_lean_back') THEN streams ELSE 0 END) as total_streams,
                MAX(CASE WHEN source_of_stream = 'spotify' THEN anomaly_score END) as spotify_lf,
                MAX(CASE WHEN source_of_stream = 'spotify_lean_back' THEN anomaly_score END) as spotify_lb,
                MAX(CASE WHEN source_of_stream = 'apple' THEN anomaly_score END) as apple_lf,
                MAX(CASE WHEN source_of_stream = 'apple_lean_back' THEN anomaly_score END) as apple_lb,
                MAX(CASE WHEN source_of_stream = 'total' THEN anomaly_score END) as total_lf,
                MAX(CASE WHEN source_of_stream = 'total_lean_back' THEN anomaly_score END) as total_lb
            FROM 
                latest_scores
            WHERE 
                rank = 1
            GROUP BY 
                isrc_cd, product_name, artist_name
        )
        SELECT 
            isrc_cd as id,
            product_name as track_name,
            artist_name,
            label_parent_name,
            label_filter_name,
            artwork_url,
            total_streams,
            (COALESCE(spotify_lf, 0) + COALESCE(spotify_lb, 0) + COALESCE(apple_lf, 0) + 
             COALESCE(apple_lb, 0) + COALESCE(total_lf, 0) + COALESCE(total_lb, 0)) / 
            NULLIF((CASE WHEN spotify_lf IS NOT NULL THEN 1 ELSE 0 END +
                    CASE WHEN spotify_lb IS NOT NULL THEN 1 ELSE 0 END +
                    CASE WHEN apple_lf IS NOT NULL THEN 1 ELSE 0 END +
                    CASE WHEN apple_lb IS NOT NULL THEN 1 ELSE 0 END +
                    CASE WHEN total_lf IS NOT NULL THEN 1 ELSE 0 END +
                    CASE WHEN total_lb IS NOT NULL THEN 1 ELSE 0 END), 0) as interesting_score,
            ABS((COALESCE(spotify_lf, 0) + COALESCE(spotify_lb, 0) + COALESCE(apple_lf, 0) + 
                 COALESCE(apple_lb, 0) + COALESCE(total_lf, 0) + COALESCE(total_lb, 0)) / 
                NULLIF((CASE WHEN spotify_lf IS NOT NULL THEN 1 ELSE 0 END +
                        CASE WHEN spotify_lb IS NOT NULL THEN 1 ELSE 0 END +
                        CASE WHEN apple_lf IS NOT NULL THEN 1 ELSE 0 END +
                        CASE WHEN apple_lb IS NOT NULL THEN 1 ELSE 0 END +
                        CASE WHEN total_lf IS NOT NULL THEN 1 ELSE 0 END +
                        CASE WHEN total_lb IS NOT NULL THEN 1 ELSE 0 END), 0)) as abs_interesting_score
        FROM 
            pivoted_scores
        WHERE 
            (CASE WHEN spotify_lf IS NOT NULL THEN 1 ELSE 0 END +
             CASE WHEN spotify_lb IS NOT NULL THEN 1 ELSE 0 END +
             CASE WHEN apple_lf IS NOT NULL THEN 1 ELSE 0 END +
             CASE WHEN apple_lb IS NOT NULL THEN 1 ELSE 0 END +
             CASE WHEN total_lf IS NOT NULL THEN 1 ELSE 0 END +
             CASE WHEN total_lb IS NOT NULL THEN 1 ELSE 0 END) > 0
        ORDER BY abs_interesting_score DESC
        LIMIT {TOP_K}
    """
    print(query)
    return query

# def get_query_first_35_top_curated():
#     """
#     Builds a query to get the first 35 top tracks curated

#     This pulls the latest decay day for each track, geo, then pulls the 
#     top k tracks based on the track_score

#     Return:
#         str : query to pull the top tracks
#     """

#     query = f"""
#         WITH ranked_data AS (
#             SELECT 
#                 isrc_cd,
#                 track_score_display,
#                 product_name,
#                 artist_name,
#                 geo_country,
#                 decay_day,
#                 ROW_NUMBER() OVER (
#                     PARTITION BY geo_country, isrc_cd 
#                     ORDER BY decay_day DESC
#                 ) AS rank
#             FROM 
#                 {TOP_K_SOURCE_TABLE}
#             WHERE
#                 source_of_stream = 'total'
#                 AND track_score_display IS NOT NULL  -- ADD THIS LINE
#         )
#         SELECT 
#             isrc_cd as id,
#             track_score_display as interesting_score,
#             abs(track_score_display) as abs_interesting_score,
#             product_name as track_name,
#             artist_name
#         FROM 
#             ranked_data
#         WHERE 
#             rank = 1
#         ORDER BY abs_interesting_score DESC
#         LIMIT {TOP_K}
#     """
#     print(query)
#     return query

# def get_song_dict_with_scores():
#     """
#     Builds and returns a song dictionary with scores

#     Returns:
#         dict: Dict with the song data
#     """

#     smeds = DatabaseUtils(Config.DB_SOURCE)

#     formatter = lambda value : round(value * 100.0) # forematter for the interesting score

#     if TOP_K_USE_DUMMY_DATA:
#         song_dict = get_default_song_dict()
#     else:
            
#         query = get_query_first_35_top_curated()

#         try:
#             song_data = smeds.query_db(query, 'main')

#             song_dict = [
#                 {
#                     'id': row['id'],
#                     'track_name': row['track_name'],
#                     'artist_name': row['artist_name'],
#                     'interesting_score': formatter(row['interesting_score']),
#                     'artwork_url': row['artwork_url'] if pd.notna(row['artwork_url']) else None,
#                     'explanation': [
#                         'Eius dolorem dolorem labore neque',
#                         'Lorem ipsum dolorem'
#                     ]
#                 }
#                 for _, row in song_data.iterrows()
#             ]

#             return song_dict

#         except Exception as e:
#             print(f"Error fetching song data: {e}")
#             # TODO improve this default.., we wouldn't want to show this, but for initial testing OK
#             song_dict = get_default_song_dict()
    
#     return song_dict


def get_default_song_dict():
    """
    Builds and returns a default dictionary with dummy data

    Deprecated - not used - but kept for testing purposes

    Returns:
        dict: Dict with the song data
    """

    song_dict = [
        {
            'id': 1,
            'track_name': "Midnight Satellites",
            'artist_name': "Neon Atlas",
            'interesting_score': 87,
            'explanation': [
                "This track aligns strongly with your recent listening habits.",
                "The production style overlaps with several artists you replay often.",
                "Its tempo and harmonic structure suggest high repeat potential."
            ]
        },
        {
            'id': 2,
            'track_name': "Glass Skyline",
            'artist_name': "Echo District",
            'interesting_score': 74,
            'explanation': [
                "You tend to favor artists experimenting with layered synths.",
                "This track is trending upward among listeners with similar taste profiles.",
                "Its chorus structure suggests strong long-term engagement."
            ]
        }
    ]
    
    return song_dict


def get_song_dict_with_scores():
    """
    Builds and returns a song dictionary with scores

    Returns:
        dict: Dict with the song data
    """

    smeds = DatabaseUtils(Config.DB_SOURCE)

    formatter = lambda value : round(value * 100.0)

    if TOP_K_USE_DUMMY_DATA:
        song_dict = get_default_song_dict()
    else:
            
        query = get_query_first_35_top_curated()

        try:
            song_data = smeds.query_db(query, 'main')
            
            print(f"Query returned {len(song_data)} rows")
            print(song_data.head())

            song_dict = [
                {
                    'id': row['id'],
                    'track_name': row['track_name'],
                    'artist_name': row['artist_name'],
                    'label_parent_name': row['label_parent_name'] if pd.notna(row['label_parent_name']) else None,
                    'label_filter_name': row['label_filter_name'] if pd.notna(row['label_filter_name']) else None,  # ADD THIS
                    'interesting_score': formatter(row['interesting_score']),
                    'artwork_url': row['artwork_url'] if row['artwork_url'] else None,
                    'explanation': [
                        'Eius dolorem dolorem labore neque',
                        'Lorem ipsum dolorem'
                    ]
                }
                for _, row in song_data.iterrows()
            ]
                        
            print(f"Built song_dict with {len(song_dict)} items")
            return song_dict

        except Exception as e:
            print(f"Error fetching song data: {e}")
            song_dict = get_default_song_dict()
    
    return song_dict