from .base_view import BaseView
from flask import request, render_template, session, jsonify
import pandas as pd
import datetime as dt
import numpy as np
from millify import millify

# TODO: Could this be moved to a helper file or something?
def log_first35_visit(smeds, user_email, endpoint, geo_country=None):
    try:
        geo_val = f"'{geo_country}'" if geo_country else "NULL"
        smeds.update_db(
            f"""
            INSERT INTO first_35.endpoint_logs (user_email, endpoint, geo_country)
            VALUES ('{user_email}', '{endpoint}', {geo_val})
            """,
            'main'
        )
    except Exception:
        pass
    

# Track Review Views.
class TrackReviewView(BaseView):
    def get(self):
        
        try:
            user_email = session.get('email', '')
        except:
            user_email = 'tadas@sonymusic.com'
    

        # get iago data.
        iago_query = f"""
            select pfn_geo, product_name, project_artist_name, artist_name, product_family_no, geo_country, 
                country_name, rep_owner_name, fin_label_parent_name, era, summary, budget_allocation, 
                budget_details, insights_link, is_column_reporter, is_iago_reco, tadas_30day_score, is_big_jump
            FROM iago.historical
            WHERE tadas_30day_score >= 2
            AND vs_forecast_lift > 0.0
            AND report_date = (
                select MAX(report_date) 
                FROM iago.historical
            )
            AND is_iago_reco = 1
            order by tadas_30day_score desc
            --limit 200
            ;
        """
        iago_data = self.smeds.query_db(iago_query, 'main')
        iago_data.drop_duplicates(inplace=True)


        # filter out artists that are on the remove_artist table.
        q = """select artist_name from iago.remove_artist where user_email = '""" + user_email + """'"""
        remove_artist_df = self.smeds.query_db(q, 'main')
        if len(remove_artist_df) > 0:
            remove_artist_list = remove_artist_df['artist_name'].tolist()
            iago_data = iago_data[~iago_data['project_artist_name'].isin(remove_artist_list)]


        # Pull Artist Critical Event data:
        project_artist_name = iago_data['project_artist_name'].unique()
        news_query = f"""
                    select project_artist_name, gnews_url, final_category
                    FROM news.artist_news
                    WHERE final_category IN ('arrest', 'crime', 'death', 'video', 'release', 'tour', 'stage', 'award', 'election', 'politics', 'technology', 'controversy', 'health', 'philanthropy', 'illness')
                        AND query_end_date = (
                            select MAX(query_end_date)
                            FROM news.artist_news
                        )
                    """
        news_data = self.smeds.query_db(news_query, 'main')

        news_data = news_data[0:0]

        # Merge the news data with iago_data
        iago_data = pd.merge(iago_data, news_data, on='project_artist_name', how='left')

        # Update is_iago_reco based on news data
        iago_data['is_iago_reco'] = iago_data.apply(
            lambda row: 0 if row['final_category'] in ['Arrest', 'Death', 'Crime', 'Illness', 'Health', 'Controversy'] else row['is_iago_reco'], axis=1
        )

        #adding our holiday query :)
        holiday_query = f"""select product_family_no, geo_country, holiday FROM holidays.excluded_tracks WHERE trend_type='ephemeral'"""
        holiday_df = self.smeds.query_db(holiday_query, 'main')

        #getting our holiday dates
        holiday_dates_query = f"""select holiday, geo_country, holiday_date FROM holidays.holiday_list"""
        holiday_dates_df = self.smeds.query_db(holiday_dates_query, 'main')

        #lets merge these to get the dates for the holidays in our excluded tracks table
        final_holiday_df = pd.merge(holiday_df, holiday_dates_df, on=['holiday', 'geo_country'], how='left')
        
        #so we can filter this down to the qualifying holidays (one day before holiday or three days after the holiday)
        q = """select MAX(report_date) FROM iago.historical"""
        report_date = self.smeds.query_db(q, 'main')['max'].iloc[0]
        report_date = pd.to_datetime(report_date)
    
        final_holiday_df['holiday_date'] = pd.to_datetime(final_holiday_df['holiday_date'])

        final_holiday_df['date_difference'] = (report_date - final_holiday_df['holiday_date']).dt.days
        final_holiday_df = final_holiday_df[(final_holiday_df['date_difference'] >= -1) & (final_holiday_df['date_difference'] <= 3)]

        final_holiday_df['pfn_geo'] = final_holiday_df['product_family_no'].astype(str) + "_" + final_holiday_df['geo_country'].astype(str)

        iago_data = pd.merge(iago_data, final_holiday_df[['pfn_geo', 'holiday']], on='pfn_geo', how='left')
        iago_data['holiday'] = iago_data['holiday'].fillna("")
 
        # organize iago_data to match the frontend's expectation:
        iago_data = iago_data[['pfn_geo', 'product_name', 'artist_name', 'geo_country', 'country_name', 
                               'fin_label_parent_name', 'insights_link', 'era', 'summary', 'is_column_reporter',
                               'tadas_30day_score', 'is_iago_reco', 'budget_allocation', 'rep_owner_name',
                               'gnews_url', 'final_category', 'is_big_jump', 'holiday', 'budget_details']]

        iago_data['is_big_jump'][iago_data['is_big_jump'].isnull()] = 0.0
        iago_data = iago_data.sort_values(by=['is_iago_reco', 'is_big_jump', 'tadas_30day_score'], ascending=[False, True, False])
        
        iago_data['final_category'] = iago_data['final_category'].replace({pd.NA: ''})

        # get the user preferences so the filter is pre-populated.
        user_preference_q = """select rep_owner, era, geo_country from iago.email_notification_preferences
        where request_id = (select min(request_id) from iago.email_notification_preferences where email = '""" + user_email + """')"""
        user_preference_df = self.smeds.query_db(user_preference_q, 'main')
        if len(user_preference_df) > 0:
            user_preferences = {'rep_owner': user_preference_df['rep_owner'].max(),
                                'era': user_preference_df['era'].max(),
                                'geo_country': user_preference_df['geo_country'].max(),
                                'streamRange': '0'}
        else:
            user_preferences = {'rep_owner': "ALL",
                                'era': "ALL",
                                'geo_country': "ALL",
                                'streamRange': '0'}
            
        # get the user's past reactions for the tracks being shown so the UI can
        # color-code tabs and lock the feedback box for tracks they've already voted on.
        past_reactions = {}
        shown_pfn_geos = iago_data['pfn_geo'].dropna().unique().tolist()
        if len(shown_pfn_geos) > 0:
            pfn_geo_list_str = ",".join(["'" + str(p).replace("'", "''") + "'" for p in shown_pfn_geos])
            reactions_q = f"""
                SELECT pfn_geo, action
                FROM tadas.user_dashboard_v2
                WHERE user_email = '{user_email}'
                  AND pfn_geo IN ({pfn_geo_list_str})
                ORDER BY action_date ASC
            """
            reactions_df = self.smeds.query_db(reactions_q, 'main')
            if len(reactions_df) > 0:
                past_reactions = dict(zip(reactions_df['pfn_geo'], reactions_df['action']))

        return render_template('track_review_v4.html',
                         new_df=iago_data.values.tolist(),
                         timeseries_data={},
                         segments_data={},
                         user_email=user_email,
                         user_preferences=user_preferences,
                         past_reactions=past_reactions)

    def post(self):
        data = request.json
        pfn_geo = data.get('pfn_geo')
        geo_country = data.get('geo_country')
        fin_label = data.get('fin_label_parent_name')
        era = data.get('era')

        current_cutoff_date = (dt.datetime.now() - dt.timedelta(days=56)).strftime("%Y-%m-%d")
        current_report_date = (dt.datetime.now() - dt.timedelta(days=2)).strftime("%Y-%m-%d")

        if not pfn_geo:
            return jsonify({'error': 'pfn_geo is required'}), 400
        

        if fin_label == 'Santa Anna':
            timeseries_query = f"""
                SELECT report_date, streams, combined_forecast, vs_forecast_lift
                FROM tadas_santaanna.days_trending_beta
                WHERE pfn_geo = '{pfn_geo}'
                AND report_date >= '{current_cutoff_date}'
                ORDER BY report_date
            """
        else:
            timeseries_query = f""" 
                    SELECT report_date, streams, combined_forecast, vs_forecast_lift
                    FROM iago.days_trending
                    WHERE pfn_geo = '{pfn_geo}'
                    AND report_date >= '{current_cutoff_date}'
                    ORDER BY report_date
                """
        
        timeseries_df = self.smeds.query_db(timeseries_query, 'main')

        # lets add the segment lookup here.
        q = f"""select gender, age_range, lift_factor from iago.historical 
            where report_date = '{current_report_date}'
            and pfn_geo = '{pfn_geo}'"""
        segments_df = self.smeds.query_db(q)
        segments_df['lift_factor'] = segments_df['lift_factor'].fillna(0.0)
        segments_df['lift_factor'] = segments_df['lift_factor'].astype(float) * 100
        segments_df['lift_factor'] = segments_df['lift_factor'].round(0)
        segments_data = segments_df.to_dict('records')
        
        if not timeseries_df.empty:
            timeseries_df = timeseries_df.replace([float('inf'), -float('inf')], None)
            timeseries_df = timeseries_df.fillna(0)

            timeseries_df[['streams', 'combined_forecast']] = timeseries_df[['streams', 'combined_forecast']].round()
            
            timeseries_data = timeseries_df.to_dict('records')
            return jsonify({'timeseries_data': timeseries_data,
                            'segments_data': segments_data})
        else:
            return jsonify({'timeseries_data': []})
    
class CardInfoView(BaseView):

    def get(self, pfn_geo):
        
        card_info = self.get_card_data(pfn_geo)  
        # timeseries_data = self.get_timeseries_data(pfn_geo)
        iago_info = self.get_iago_data(pfn_geo)

        if not card_info:
            return "No card data found for this pfn_geo", 404

        # Prepare the data to be passed to the template
        return render_template('track_review_card_info.html', card_data=card_info, iago_info=iago_info)
        # , timeseries_data=timeseries_data)
    
    def convert_to_list(self, isrc_value):
        """ Helper function to convert ISRC codes to list if needed. """
        # Ensure that the ISRC code is a list (could be comma separated or a single value)
        if isinstance(isrc_value, str):
            return [x.strip() for x in isrc_value.split(',')]  # Split by commas if multiple ISRC codes
        return [isrc_value]  # Return as list even if it’s a single value

    def get_iago_data(self, pfn_geo):
        
        iago_q = """select summary 
            from iago.historical 
            where pfn_geo = '""" + pfn_geo + """'
                AND report_date = (
                        SELECT MAX(report_date) 
                        FROM iago.historical
                    );"""
        iago_df = self.smeds.query_db(iago_q, 'main')
        iago_df.drop_duplicates(inplace = True)
        if len(iago_df) > 0:
            iago_data = {'summary': iago_df['summary'].max()}
        else:
            iago_data = {'summary': 'Sorry.  Summary not available at this time.'}

        return iago_data

    def get_card_data(self, pfn_geo):
        
        # Define the tables to query
        tables = [
            'tadas_sandbox.yesterday_beta', 
            'tadas_frontline.yesterday_beta', 
            'tadas_orchard.yesterday_beta',
        ]
        
        card_data = None  # Initialize the card_data variable

        # Loop through the tables and fetch data for the given pfn_geo
        for table in tables:
            query = f"""
            SELECT * 
            FROM {table} 
            WHERE pfn_geo = '{pfn_geo}'
            AND tadas_version != 'Orchard';
            """
            # Query the database to get the card info
            card_df = self.smeds.query_db(query, 'main')

            if not card_df.empty:
                # Convert ISRC codes into a list (if applicable)
                card_df['isrc_cd'] = card_df['isrc_cd'].apply(self.convert_to_list)

                # If data is found, return it as a dictionary of records
                card_data = card_df.to_dict(orient='records')
                card_data = card_data[0] if card_data else {}
                break  # Stop after the first match
        
        return card_data


# First 35 Views.
class First35StandardView(BaseView):

    def __init__(self):
        super().__init__()
        self.decay_df = None
        self.song_metadata = None
        self.decay_df_lf = None
        self.song_metadata_lf = None
        self.decay_df_lb = None
        self.song_metadata_lb = None
        self.common_song_ids = None
    

    def load_song_list(self, geo_country='US', domestic=False, label_filter='', data_type='', include_older=False, source_platform='spotify'):
        source_map = {
            'lf': f'{source_platform}_lean_forward',
            'lb': f'{source_platform}_lean_back',
            '':   source_platform
        }
        source = source_map.get(data_type, 'spotify')
        try:
            domestic_clause = f"AND isrc_cd ILIKE '{geo_country}%%' AND artist_name NOT ILIKE '%%mumford%%'" if domestic else "" #getting rid of mumford for GB
            if label_filter:
                labels = [l.strip().replace("'", "''") for l in label_filter.split('|||') if l.strip()]
                if len(labels) == 1:
                    label_clause = f"AND company_brand_name = '{labels[0]}'"
                elif len(labels) > 1:
                    labels_sql = "', '".join(labels)
                    label_clause = f"AND company_brand_name IN ('{labels_sql}')"
                else:
                    label_clause = ""
            else:
                label_clause = ""

            q = f"""
                with base AS (
                    select *,
                        MAX(decay_day) OVER (PARTITION BY isrc_cd) as max_decay_day
                    FROM first_35.current_events
                    WHERE source_of_stream = '{source}'
                    AND geo_country = '{geo_country}'
                    AND before_first_friday = FALSE
                    {'' if include_older else 'AND (older_than_35d IS NOT TRUE)'}
                    {domestic_clause}
                    {label_clause}
                )
                select
                    isrc_cd, artist_name, product_name, fin_label_parent_name,
                    company_brand_name, suppl_title, genre_name,
                    weight_artist, weight_genre, weight_market,
                    MAX(CASE WHEN decay_day = max_decay_day THEN anomaly_score END) as anomaly_score,
                    MAX(decay_day)                                                              as last_day,
                    COUNT(decay_day)                                                            as data_length,
                    SUM(streams)                                                                as total_streams,
                    SUM(CASE WHEN decay_day >= max_decay_day - 6 THEN streams END)             as weekly_streams,
                    AVG(CASE WHEN decay_day >= max_decay_day - 6 THEN streams END)             as avg_tw_streams,
                    SUM(CASE WHEN decay_day >= max_decay_day - 6 THEN expected_sales END) as weekly_expected,
                    SUM(CASE WHEN actual_decay_rate > upper_bound THEN 1 ELSE 0 END)           as positive_deviations,
                    SUM(CASE WHEN actual_decay_rate < lower_bound THEN 1 ELSE 0 END)           as negative_deviations,
                    SUM(CASE WHEN decay_day >= max_decay_day - 13
                        AND (actual_decay_rate > upper_bound OR actual_decay_rate < lower_bound)
                        THEN 1 ELSE 0 END)                                                      as recent_deviating_days,
                    BOOL_OR(CASE WHEN decay_day = max_decay_day
                        THEN (actual_decay_rate > upper_bound OR actual_decay_rate < lower_bound)
                        end)                                                                    as is_anomalous_today
                FROM base
                GROUP BY isrc_cd, artist_name, product_name, fin_label_parent_name,
                        company_brand_name, suppl_title, genre_name,
                        weight_artist, weight_genre, weight_market
                ORDER BY anomaly_score DESC NULLS LAST
            """

            result = self.smeds.query_db(q, 'main')

            if result.empty:
                return []

            result['anomaly_score']   = result['anomaly_score'].fillna(0.0)
            result['performance_category'] = 'stable'
            result.loc[result['anomaly_score'] >= 0.20,  'performance_category'] = 'overperforming'
            result.loc[result['anomaly_score'] <= -0.20, 'performance_category'] = 'underperforming'

            result['optimization_score'] = (result['anomaly_score'] * 100).round(0).astype(int)

            result['avg_tw_streams']        = result['avg_tw_streams'].fillna(0).astype(int)
            result['total_streams']         = result['total_streams'].fillna(0).astype(int)
            result['weekly_streams']        = result['weekly_streams'].fillna(0).astype(int)
            result['positive_deviations']   = result['positive_deviations'].fillna(0).astype(int)
            result['negative_deviations']   = result['negative_deviations'].fillna(0).astype(int)
            result['recent_deviating_days'] = result['recent_deviating_days'].fillna(0).astype(int)
            result['is_anomalous_today']    = result['is_anomalous_today'].fillna(False)
            result['data_length']           = result['data_length'].fillna(0).astype(int)
            result['last_day']              = result['last_day'].fillna(0).astype(int)

            result['avg_tw_streams_display'] = result['avg_tw_streams'].apply(lambda x: millify(x, precision=1))
            result['total_streams_display']  = result['total_streams'].apply(lambda x: millify(x, precision=1))

            result = result.rename(columns={
                'fin_label_parent_name': 'label_parent_name',
                'company_brand_name':    'label_filter_name',
            })

            result['total_deviating_days']  = result['positive_deviations'] + result['negative_deviations']
            result['predicted_cluster']     = 0
            result['is_non_friday_release'] = False
            result['is_anomalous_cluster']  = False
            result['deviation_percentage']  = 0.0
            result['max_consecutive_days']  = 0
            result['weekly_expected'] = result['weekly_expected'].fillna(0).astype(int)
            result['performance_ratio'] = (
                result['weekly_streams'] / result['weekly_expected']
            ).where(result['weekly_expected'] > 0, 1.0).round(2)
            result['song_id']               = result['isrc_cd']

            return result.to_dict('records')

        except Exception as e:
            print('load_song_list failed')
            import traceback
            traceback.print_exc()
            return []


    def _convert_to_json_serializable(self, obj):
        if isinstance(obj, dict):
            return {key: self._convert_to_json_serializable(value) for key, value in obj.items()}
        elif isinstance(obj, list):
            return [self._convert_to_json_serializable(item) for item in obj]
        elif isinstance(obj, np.ndarray):
            return self._convert_to_json_serializable(obj.tolist())
        elif isinstance(obj, (np.integer, np.int8, np.int16, np.int32, np.int64)):
            return int(obj)
        elif isinstance(obj, (np.floating, np.float16, np.float32, np.float64)):
            if np.isnan(obj) or np.isinf(obj):
                return None
            return float(obj)
        elif isinstance(obj, (np.bool_, np.bool8)):
            return bool(obj)
        elif isinstance(obj, np.str_):
            return str(obj)
        elif pd.isna(obj):
            return None
        else:
            if isinstance(obj, float) and (obj == float('inf') or obj == float('-inf')):
                return None
            return obj

    def get_song_data(self, song_id, data_type='', geo_country='US', source_platform='spotify'):
        if source_platform == 'apple':
            if data_type == 'lf':
                source = 'apple_lean_forward'
            elif data_type == 'lb':
                source = 'apple_lean_back'
            else:
                source = 'apple'
        
        elif source_platform == 'total':
            if data_type == 'lf':
                source = 'total_lean_forward'
            elif data_type == 'lb':
                source = 'total_lean_back'
            else:
                source = 'total'

        else:
            if data_type == 'lf':
                source = 'spotify_lean_forward'
            elif data_type == 'lb':
                source = 'spotify_lean_back'
            else:
                source = 'spotify'
    
        query = f"""
            SELECT * 
            FROM first_35.current_events
            WHERE isrc_cd = '{song_id}'
            AND source_of_stream = '{source}'
            AND geo_country = '{geo_country}'
            ORDER BY report_date
        """
        song_data = self.smeds.query_db(query, 'main')
        
        if song_data.empty:
            return None
        
        song_data = song_data.sort_values('report_date').reset_index(drop=True)

        anomalous_period_days = song_data[song_data['anomalous_period_start'] == True]['report_date'].astype(str).tolist()
        regular_period_days   = song_data[song_data['regular_period_start'] == True]['report_date'].astype(str).tolist()
        
        metadata = {
            'song_id':        song_id,
            'product_name':   song_data.iloc[0]['product_name'],
            'artist_name':    song_data.iloc[0]['artist_name'],
            'label_parent_name': song_data.iloc[0]['fin_label_parent_name'],
            'suppl_title':    song_data.iloc[0]['suppl_title'],
            'genre_name':     song_data.iloc[0]['genre_name'],
            'predicted_cluster': 0,
            'weight_artist':  song_data.iloc[0]['weight_artist'],
            'weight_genre':   song_data.iloc[0]['weight_genre'],
            'weight_market':  song_data.iloc[0]['weight_market'],
        }
        
        days             = song_data['report_date'].astype(str).tolist()
        actual_streams   = song_data['streams'].fillna(0).tolist()
        expected_streams = song_data['expected_sales'].where(pd.notna(song_data['expected_sales']), None).tolist()
        
        actual_decay_rates  = song_data['actual_decay_rate'].where(pd.notna(song_data['actual_decay_rate']), None).tolist()
        expected_decay_rates = song_data['pct_change_avg'].where(pd.notna(song_data['pct_change_avg']), None).tolist()
        upper_bounds        = song_data['upper_bound'].where(pd.notna(song_data['upper_bound']), None).tolist()
        lower_bounds        = song_data['lower_bound'].where(pd.notna(song_data['lower_bound']), None).tolist()
        data_length         = len(song_data)
        
        valid_data = song_data[song_data['before_first_friday'] == False]
        if not valid_data.empty:
            peak_value = float(valid_data['streams'].max())
            peak_idx   = valid_data['streams'].idxmax()
            peak_day   = int(valid_data.loc[peak_idx, 'decay_day'])
        else:
            peak_value = 0
            peak_day   = 0
        
        valid_decay_rates = [x for x in actual_decay_rates if x is not None]
        simple_decay_rate = sum(valid_decay_rates) / len(valid_decay_rates) if valid_decay_rates else 0.0

        recent_data = valid_data.tail(7)
        recent_actual   = recent_data['streams'].fillna(0).sum()
        recent_expected = recent_data['expected_sales'].fillna(0).sum()
        performance_ratio = round(recent_actual / recent_expected, 2) if recent_expected > 0 else 1.0
        
        if not valid_data.empty:
            last_valid_row    = valid_data.iloc[-1]
            optimization_score = float(last_valid_row['anomaly_score']) if pd.notna(last_valid_row['anomaly_score']) else 0.0
        else:
            optimization_score = 0.0

        if optimization_score >= 0.20:
            performance_category = 'overperforming'
        elif optimization_score <= -0.20:
            performance_category = 'underperforming'
        else:
            performance_category = 'stable'
        
        better_days, worse_days = [], []
        better_stream_values, worse_stream_values = [], []
        better_decay_values, worse_decay_values = [], []

        for i in range(len(actual_decay_rates)):
            if actual_decay_rates[i] is None:
                continue
            if i < len(upper_bounds) and i < len(lower_bounds):
                if upper_bounds[i] is not None and lower_bounds[i] is not None:
                    if actual_decay_rates[i] > upper_bounds[i]:
                        better_days.append(days[i])
                        better_stream_values.append(actual_streams[i])
                        better_decay_values.append(actual_decay_rates[i])
                    elif actual_decay_rates[i] < lower_bounds[i]:
                        worse_days.append(days[i])
                        worse_stream_values.append(actual_streams[i])
                        worse_decay_values.append(actual_decay_rates[i])

        simple_decay_rate = float(simple_decay_rate) if simple_decay_rate is not None else 0.0

        # first friday handling
        first_friday_row = song_data[song_data['is_first_friday'] == True]
        if not first_friday_row.empty:
            first_friday_date = first_friday_row.iloc[0]['report_date']
            if first_friday_date != song_data.iloc[0]['report_date']:
                first_friday_date = first_friday_date.strftime('%Y-%m-%d')
                song_data['streams'][pd.to_datetime(song_data['report_date']) < pd.to_datetime(first_friday_date)] = None
                actual_streams = song_data['streams'].tolist()
            else:
                first_friday_date = None
        else:
            first_friday_date = None
        
        response_data = {
            'song_id':            metadata['song_id'],
            'product_name':       metadata['product_name'],
            'artist_name':        metadata['artist_name'],
            'label_parent_name':  metadata['label_parent_name'],
            'suppl_title':        metadata['suppl_title'],
            'genre_name':         metadata['genre_name'],
            'predicted_cluster':  metadata['predicted_cluster'],
            'performance_category': performance_category,
            'performance_ratio':  round(performance_ratio, 2),
            'optimization_score': int(round(optimization_score * 100)),
            'artwork_url':        song_data.iloc[0]['artwork_url'] if 'artwork_url' in song_data.columns else None,

            'weight_artist':  metadata['weight_artist'],
            'weight_genre':   metadata['weight_genre'],
            'weight_market':  metadata['weight_market'],
            
            'days':         days,
            'limited_days': days[:data_length],
            'data_length':  data_length,
            
            'actual_streams':   actual_streams,
            'expected_streams': expected_streams,
            'song_peak_value':  int(peak_value),
            'peak_day':         peak_day,
            'simple_decay_rate': simple_decay_rate,
            
            'actual_decay_rates':  actual_decay_rates,
            'expected_decay_rates': expected_decay_rates,
            'upper_bounds':        upper_bounds,
            'lower_bounds':        lower_bounds,
            
            'better_days':          better_days,
            'worse_days':           worse_days,
            'better_stream_values': better_stream_values,
            'worse_stream_values':  worse_stream_values,
            'better_decay_values':  better_decay_values,
            'worse_decay_values':   worse_decay_values,
            'first_friday_date':    first_friday_date,
            
            'album_release_day':   0,
            'anomalous_periods':   anomalous_period_days,
            'normal_periods':      regular_period_days,
            'is_anomalous_cluster': False,
            'is_non_friday_release': bool(song_data['before_first_friday'].any())
        }

        # benchmarks --> putting this in a try except too 
        try:
            benchmark_q = f"""
                SELECT benchmark_isrc, assigned_by
                FROM day_0_35.first_35_benchmarks
                WHERE target_isrc = '{song_id}'
                AND geo_country = '{geo_country}'
                LIMIT 1
            """
            benchmark_df = self.smeds.query_db(benchmark_q, 'main')
            
            if benchmark_df.empty:
                response_data['has_custom_benchmark']  = False
                response_data['custom_benchmark_name'] = None
                response_data['benchmark_data']        = None
            else:
                benchmark_pfn = benchmark_df['benchmark_isrc'].iloc[0]
                
                pfn_info_query = f"""
                    SELECT DISTINCT 
                        project_artist_name as artist_name,
                        product_name
                    FROM first_35.artist_track_streams
                    WHERE product_family_no = '{benchmark_pfn}'
                    AND geo_country = '{geo_country}'
                    LIMIT 1
                """
                pfn_info_result = self.smeds.query_db(pfn_info_query, 'main')
                
                if pfn_info_result.empty:
                    artist_name  = "Unknown"
                    product_name = f"PFN: {benchmark_pfn}"
                else:
                    artist_name  = pfn_info_result['artist_name'].iloc[0]
                    name_value   = pfn_info_result['product_name'].iloc[0]
                    product_name = name_value if pd.notna(name_value) else f"PFN: {benchmark_pfn}"

                response_data['has_custom_benchmark']  = True
                response_data['custom_benchmark_name'] = f"{product_name} - {artist_name}"
                
                decay_rates_query = f"""
                    SELECT 
                        decay_day_2, decay_day_3, decay_day_4, decay_day_5, decay_day_6, decay_day_7, 
                        decay_day_8, decay_day_9, decay_day_10, decay_day_11, decay_day_12, decay_day_13, 
                        decay_day_14, decay_day_15, decay_day_16, decay_day_17, decay_day_18, decay_day_19, 
                        decay_day_20, decay_day_21, decay_day_22, decay_day_23, decay_day_24, decay_day_25, 
                        decay_day_26, decay_day_27, decay_day_28, decay_day_29, decay_day_30, decay_day_31, 
                        decay_day_32, decay_day_33, decay_day_34, decay_day_35
                    FROM first_35.artist_track_decay_rates
                    WHERE product_family_no = '{benchmark_pfn}'
                    AND geo_country = '{geo_country}'
                    LIMIT 1
                """
                decay_rates_df = self.smeds.query_db(decay_rates_query, 'main')
                
                target_first_friday_row = song_data[song_data['is_first_friday'] == True]

                if decay_rates_df.empty or target_first_friday_row.empty:
                    response_data['benchmark_data'] = None
                else:
                    target_first_friday_streams = float(target_first_friday_row.iloc[0]['streams'])
                    target_first_friday_date    = target_first_friday_row.iloc[0]['report_date']

                    benchmark_decay_rates      = [None]
                    expected_benchmark_streams = [target_first_friday_streams]
                    cumulative_multiplier      = 1.0

                    for day in range(2, 36):
                        rate = decay_rates_df.iloc[0][f'decay_day_{day}']
                        benchmark_decay_rates.append(float(rate) if pd.notna(rate) else None)
                        if pd.notna(rate):
                            cumulative_multiplier *= (1 - float(rate))
                            expected_benchmark_streams.append(target_first_friday_streams * cumulative_multiplier)
                        else:
                            expected_benchmark_streams.append(None)

                    benchmark_days = [
                        (target_first_friday_date + pd.Timedelta(days=i)).strftime('%Y-%m-%d')
                        for i in range(35)
                    ]
                    
                    response_data['benchmark_data'] = {
                        'days':                benchmark_days,
                        'scaled_streams':      expected_benchmark_streams,
                        'decay_rates':         benchmark_decay_rates,
                        'expected_decay_rates': benchmark_decay_rates,
                        'upper_bounds':        [None] * 35,
                        'lower_bounds':        [None] * 35,
                        'scaling_factor':      1.0
                    }

        except Exception as e:
            print(f'benchmark error: {e}')
            response_data['has_custom_benchmark']  = False
            response_data['custom_benchmark_name'] = None
            response_data['benchmark_data']        = None

        #gonna try adding tiktok wrapping it in a try except 
        try:
            tiktok_query = f"""
                SELECT 
                    report_date,
                    SUM(CASE WHEN source_of_stream = 'tiktok_creations' THEN streams ELSE 0 END) as tiktok_creations,
                    SUM(CASE WHEN source_of_stream = 'tiktok_views' THEN streams ELSE 0 END) as tiktok_views
                FROM first_35.current_events
                WHERE isrc_cd = '{song_id}'
                AND geo_country = '{geo_country}'
                AND source_of_stream IN ('tiktok_creations', 'tiktok_views')
                GROUP BY report_date
                ORDER BY report_date
            """
            tiktok_df = self.smeds.query_db(tiktok_query, 'main')

            if not tiktok_df.empty:
                tiktok_df['report_date'] = pd.to_datetime(tiktok_df['report_date'])
                response_data['tiktok_dates']     = tiktok_df['report_date'].dt.strftime('%Y-%m-%d').tolist()
                response_data['tiktok_creations'] = tiktok_df['tiktok_creations'].tolist()
                response_data['tiktok_views']     = tiktok_df['tiktok_views'].tolist()
            else:
                response_data['tiktok_dates']     = []
                response_data['tiktok_creations'] = []
                response_data['tiktok_views']     = []
        except Exception as e:
            print(f'tiktok error: {e}')
            response_data['tiktok_dates']     = []
            response_data['tiktok_creations'] = []
            response_data['tiktok_views']     = []
        

        # adding this weke projection here
        # take all streams since the previous friday (will always be multiple of 7)
        # then find decay until subsequent thursday, and then multiply out and sum the full week
        try:
            proj_q = f"""
                WITH latest AS (
                    SELECT streams, decay_day
                    FROM first_35.current_events
                    WHERE isrc_cd = '{song_id}'
                    AND geo_country = '{geo_country}'
                    AND source_of_stream = '{source}'
                    AND before_first_friday = false
                    ORDER BY decay_day DESC
                    LIMIT 1
                ),
                week_bounds AS (
                    SELECT
                        decay_day AS current_day,
                        streams AS current_streams,
                        (decay_day / 7) * 7 AS week_start,
                        LEAST((decay_day / 7) * 7 + 6, 34) AS week_end
                    FROM latest
                ),
                actuals AS (
                    SELECT SUM(streams) AS actual_sum
                    FROM first_35.current_events
                    WHERE isrc_cd = '{song_id}'
                    AND geo_country = '{geo_country}'
                    AND source_of_stream = '{source}'
                    AND before_first_friday = false
                    AND decay_day BETWEEN (SELECT week_start FROM week_bounds)
                                        AND (SELECT current_day FROM week_bounds)
                ),
                future_rates AS (
                    SELECT t.decay_day,
                        COALESCE(t.agm_decay, t.ag_decay, t.market_decay) AS rate
                    FROM first_35.track_decay_curves t
                    CROSS JOIN week_bounds wb
                    WHERE t.isrc_cd = '{song_id}'
                    AND t.decay_day BETWEEN wb.current_day + 1 AND wb.week_end
                    ORDER BY t.decay_day
                )
                SELECT
                    wb.current_day,
                    wb.current_streams,
                    a.actual_sum,
                    COUNT(fr.decay_day) AS days_to_project,
                    COALESCE(
                        json_agg(fr.rate ORDER BY fr.decay_day) FILTER (WHERE fr.decay_day IS NOT NULL),
                        '[]'::json
                    ) AS rates
                FROM week_bounds wb
                CROSS JOIN actuals a
                LEFT JOIN future_rates fr ON true
                GROUP BY wb.current_day, wb.current_streams, a.actual_sum
            """
            proj_df = self.smeds.query_db(proj_q, 'main')

            if not proj_df.empty and proj_df.iloc[0]['actual_sum'] is not None:
                row = proj_df.iloc[0]
                actual_sum = float(row['actual_sum'])
                rates = row['rates'] if row['rates'] else []
                projected = float(row['current_streams'])
                proj_sum = 0.0
                for rate in rates:
                    r = float(rate) if rate is not None else 0.0
                    projected = projected * (1 + r)
                    proj_sum += projected
                response_data['next_week_proj'] = millify(int(actual_sum + proj_sum), precision=1)
            else:
                response_data['next_week_proj'] = None
        except Exception as e:
            print(f'Next week projection failed: {e}')
            response_data['next_week_proj'] = None

        return response_data


    def get(self):
        geo_country     = request.args.get('geo_country', 'US')
        data_type       = request.args.get('data_type', '').lower()
        domestic        = request.args.get('domestic', '0') == '1'
        label_filter    = request.args.get('label', '')
        source_platform = request.args.get('source_platform', 'total')
        user_email      = session.get('email', 'unknown')

        is_fresh_visit = 'label' not in request.args

        if is_fresh_visit and user_email != 'unknown':
            try:
                pref_df = self.smeds.query_db(
                    f"SELECT label_filter, geo_country, domestic, data_type, source_platform FROM first_35.user_preferences WHERE user_email = '{user_email}'",
                    'main'
                )
                if not pref_df.empty:
                    label_filter    = pref_df['label_filter'].iloc[0] or ''
                    geo_country     = pref_df['geo_country'].iloc[0] or 'US'
                    domestic        = bool(pref_df['domestic'].iloc[0])
                    data_type       = pref_df['data_type'].iloc[0] or ''
                    source_platform = pref_df['source_platform'].iloc[0] or 'total'
            except Exception as e:
                print(f'Failed to load user preferences: {e}')

        if data_type not in ('lf', 'lb'):
            data_type = ''
        template_data_type = data_type if data_type else 'total'

        # Always split on ||| — commas can appear inside label names
        labels_list       = [l.strip() for l in label_filter.split('|||') if l.strip()]
        label_filter_sql  = '|||'.join(labels_list)   # load_song_list also splits on |||
        label_filter_pref = label_filter

        labels_q = f"""
            SELECT DISTINCT company_brand_name
            FROM first_35.current_events
            WHERE geo_country = '{geo_country}'
            AND company_brand_name IS NOT NULL
            ORDER BY company_brand_name
        """
        available_labels = self.smeds.query_db(labels_q, 'main')['company_brand_name'].tolist()

        try:
            max_date_df = self.smeds.query_db("SELECT MAX(report_date) as max_date FROM first_35.current_events", 'main')
            data_through = max_date_df['max_date'].iloc[0]
            data_through = pd.to_datetime(data_through).strftime('%B %d, %Y') if pd.notna(data_through) else 'N/A'
        except Exception:
            data_through = 'N/A'

        if not label_filter and not domestic:
            return render_template('first_35_standard.html',
                song_list=[],
                total_songs=0,
                data_type=template_data_type,
                geo_country=geo_country,
                domestic=domestic,
                label_filter=None,
                selected_labels=[],
                available_labels=available_labels,
                include_older=request.args.get('include_older', '0') == '1',
                source_platform=source_platform,
                data_through=data_through)

        include_older = request.args.get('include_older', '0') == '1'
        log_first35_visit(self.smeds, user_email, '/first_35', geo_country)

        if (label_filter or domestic) and user_email != 'unknown':
            try:
                self.smeds.update_db(f"""
                    INSERT INTO first_35.user_preferences 
                        (user_email, label_filter, geo_country, domestic, data_type, source_platform)
                    VALUES ('{user_email}', '{label_filter_pref}', '{geo_country}', {domestic}, '{data_type}', '{source_platform}')
                    ON CONFLICT (user_email) DO UPDATE SET
                        label_filter    = EXCLUDED.label_filter,
                        geo_country     = EXCLUDED.geo_country,
                        domestic        = EXCLUDED.domestic,
                        data_type       = EXCLUDED.data_type,
                        source_platform = EXCLUDED.source_platform
                """, 'main')
            except Exception as e:
                print(f'Failed to save user preferences: {e}')

        song_list = self.load_song_list(
            geo_country,
            domestic=domestic,
            label_filter=label_filter_sql,
            data_type=data_type,
            include_older=include_older,
            source_platform=source_platform
        )

        # selected_labels derived cleanly from labels_list — no comma re-split
        selected_labels = labels_list

        if not song_list:
            return render_template('first_35_standard.html',
                error="No results returned for the selected filters.",
                song_list=[],
                total_songs=0,
                data_type=template_data_type,
                geo_country=geo_country,
                domestic=domestic,
                label_filter=label_filter,
                selected_labels=selected_labels,
                available_labels=available_labels,
                include_older=include_older,
                source_platform=source_platform,
                data_through=data_through)

        return render_template('first_35_standard.html',
            song_list=song_list,
            total_songs=len(song_list),
            data_type=template_data_type,
            geo_country=geo_country,
            domestic=domestic,
            label_filter=label_filter,
            selected_labels=selected_labels,
            available_labels=available_labels,
            include_older=include_older,
            source_platform=source_platform,
            data_through=data_through)


    def post(self):
        data            = request.get_json()
        song_id         = data.get('song_id')
        data_type       = data.get('data_type', '')
        geo_country     = data.get('geo_country', 'US')
        source_platform = data.get('source_platform', 'spotify')

        if not song_id:
            return jsonify({'error': 'Song ID required'}), 400

        song_id = str(song_id)
        print(f"song_id: {song_id}, data_type: {data_type}, geo_country: {geo_country}, source_platform: {source_platform}")
        
        try:
            song_data = self.get_song_data(song_id, data_type, geo_country, source_platform)
            
            if song_data is None:
                return jsonify({'error': f'no data found for song {song_id}'}), 404
            
            return jsonify(self._convert_to_json_serializable(song_data))
            
        except Exception as e:
            import traceback
            traceback.print_exc()
            return jsonify({'error': str(e)}), 500

class First35BenchmarkView(BaseView):
    def get(self):
        geo_country = request.args.get('geo_country', 'US')

        #im going to get all tracks here
        tracks_query = f"""
            SELECT DISTINCT 
                isrc_cd,
                artist_name,
                product_name,
                fin_label_parent_name
            FROM first_35.current_events
            WHERE source_of_stream = 'spotify'
            AND geo_country = '{geo_country}'
            ORDER BY artist_name, product_name
        """
        tracks_df = self.smeds.query_db(tracks_query, 'main')
        
        #getting all the benchmarks here
        benchmarks_query = """
            SELECT 
            b.target_isrc,
            b.geo_country,
            b.benchmark_isrc,
            COALESCE(ce.product_name, ats.product_name, 'Unknown') as benchmark_name,
            COALESCE(ce.artist_name, ats.project_artist_name, 'Unknown') as benchmark_artist
            FROM day_0_35.first_35_benchmarks b
            LEFT JOIN (
                SELECT DISTINCT ON (isrc_cd) 
                    isrc_cd, product_name, artist_name
                FROM first_35.current_events
                WHERE source_of_stream = 'spotify'
                ORDER BY isrc_cd, streams DESC NULLS LAST
            ) ce ON b.benchmark_isrc = ce.isrc_cd
            LEFT JOIN (
                SELECT DISTINCT ON (product_family_no)
                    product_family_no, product_name, project_artist_name
                FROM first_35.artist_track_streams
                ORDER BY product_family_no, day_35 DESC NULLS LAST
            ) ats ON b.benchmark_isrc = ats.product_family_no::text
        """
        benchmarks_df = self.smeds.query_db(benchmarks_query, 'main')
        
        #pivoting by geo here - we can get rid of this if we wnat to just start iwth US
        for geo in benchmarks_df['geo_country'].unique():
            geo_lower = geo.lower()
            geo_benchmarks = benchmarks_df[benchmarks_df['geo_country'] == geo].set_index('target_isrc')
            tracks_df[f'benchmark_name_{geo_lower}'] = tracks_df['isrc_cd'].map(
                geo_benchmarks['benchmark_name'] if 'benchmark_name' in geo_benchmarks.columns else pd.Series()
            )
            tracks_df[f'benchmark_artist_{geo_lower}'] = tracks_df['isrc_cd'].map(
                geo_benchmarks['benchmark_artist'] if 'benchmark_artist' in geo_benchmarks.columns else pd.Series()
            )
        
        tracks_list = tracks_df.to_dict('records')

        user_email = session.get('email', 'unknown')
        log_first35_visit(self.smeds, user_email, '/first_35/benchmarks')
        
        return render_template('first_35_manual_benchmark.html', 
                       tracks=tracks_list,
                       geo_country=geo_country,
                       domestic=False)
    
class First35ProView(BaseView):
    def __init__(self):
        super().__init__()
        self.all_sources_data = {}
    
    def load_all_sources(self, geo_country='US'):
        try:
            sources = [
                'spotify',
                'spotify_lean_forward', 
                'spotify_lean_back',
                'apple',
                'apple_lean_forward',
                'apple_lean_back',
                'total',
                'total_lean_forward',
                'total_lean_back'
            ]
            
            for source in sources:
                query = f"""
                SELECT 
                    report_date,
                    isrc_cd,
                    geo_country,
                    source_of_stream,
                    streams,
                    artist_name,
                    product_name,
                    fin_label_parent_name,
                    company_brand_name,
                    anomaly_score
                FROM first_35.current_events
                WHERE source_of_stream = '{source}'
                AND geo_country = '{geo_country}'
                AND (older_than_35d IS NOT TRUE)
                ORDER BY isrc_cd, report_date
                """
                
                df = self.smeds.query_db(query, 'main')
                self.all_sources_data[source] = df
                print(f"Loaded {len(df)} rows for {source}")
                
        except Exception as e:
            print(f'Pro version error: {e}')
            import traceback
            traceback.print_exc()

    def get_song_list_pro(self, geo_country='US', domestic=False):
        if 'total' not in self.all_sources_data or self.all_sources_data['total'].empty:
            return []
        
        total_df = self.all_sources_data['total']

        unique_songs = total_df['isrc_cd'].unique()
        if domestic:
            unique_songs = [s for s in unique_songs if str(s)[:2].upper() == geo_country.upper() and not total_df[total_df['isrc_cd'] == s]['artist_name'].str.lower().str.contains('mumford').any()]
        
        total_full_query = f"""
            SELECT 
                isrc_cd,
                streams,
                expected_sales
            FROM first_35.current_events
            WHERE source_of_stream = 'total'
            AND geo_country = '{geo_country}'
            ORDER BY isrc_cd, report_date
        """
        total_full_df = self.smeds.query_db(total_full_query, 'main')

        track_score_query = f"""
            SELECT DISTINCT ON (isrc_cd)
                isrc_cd,
                track_score_display
            FROM first_35.current_events
            WHERE source_of_stream = 'total'
            AND geo_country = '{geo_country}'
            AND track_score_display IS NOT NULL
            ORDER BY isrc_cd, decay_day DESC
        """
        track_score_df = self.smeds.query_db(track_score_query, 'main')
        track_score_lookup = track_score_df.set_index('isrc_cd')['track_score_display'].to_dict()
        
        latest_scores = {}

        for source_name in ['spotify', 'spotify_lean_forward', 'spotify_lean_back', 'apple', 'apple_lean_forward', 'apple_lean_back', 'total', 'total_lean_forward', 'total_lean_back']:
            if source_name in self.all_sources_data:
                df = self.all_sources_data[source_name]
                latest = df.groupby('isrc_cd').last()
                latest_scores[source_name] = latest['anomaly_score'].to_dict()
            else:
                latest_scores[source_name] = {}
        
        song_list = []

        # next week projections for all isrcs
        proj_lookup = {}
        try:
            bulk_proj_q = f"""
                WITH latest AS (
                    SELECT DISTINCT ON (isrc_cd)
                        isrc_cd, streams, decay_day
                    FROM first_35.current_events
                    WHERE geo_country = '{geo_country}'
                    AND source_of_stream = 'total'
                    AND before_first_friday = false
                    ORDER BY isrc_cd, decay_day DESC
                ),
                week_bounds AS (
                    SELECT
                        isrc_cd,
                        decay_day AS current_day,
                        streams AS current_streams,
                        (decay_day / 7) * 7 AS week_start,
                        LEAST((decay_day / 7) * 7 + 6, 34) AS week_end
                    FROM latest
                ),
                actuals AS (
                    SELECT ce.isrc_cd, SUM(ce.streams) AS actual_sum
                    FROM first_35.current_events ce
                    JOIN week_bounds wb ON wb.isrc_cd = ce.isrc_cd
                    WHERE ce.geo_country = '{geo_country}'
                    AND ce.source_of_stream = 'total'
                    AND ce.before_first_friday = false
                    AND ce.decay_day BETWEEN wb.week_start AND wb.current_day
                    GROUP BY ce.isrc_cd
                ),
                future_rates AS (
                    SELECT t.isrc_cd, t.decay_day,
                        COALESCE(t.agm_decay, t.ag_decay, t.market_decay) AS rate
                    FROM first_35.track_decay_curves t
                    JOIN week_bounds wb ON wb.isrc_cd = t.isrc_cd
                    WHERE t.decay_day BETWEEN wb.current_day + 1 AND wb.week_end
                )
                SELECT
                    wb.isrc_cd,
                    wb.current_streams,
                    a.actual_sum,
                    COALESCE(
                        json_agg(fr.rate ORDER BY fr.decay_day) FILTER (WHERE fr.decay_day IS NOT NULL),
                        '[]'::json
                    ) AS rates
                FROM week_bounds wb
                JOIN actuals a ON a.isrc_cd = wb.isrc_cd
                LEFT JOIN future_rates fr ON fr.isrc_cd = wb.isrc_cd
                GROUP BY wb.isrc_cd, wb.current_streams, a.actual_sum
            """
            bulk_proj_df = self.smeds.query_db(bulk_proj_q, 'main')
            for _, row in bulk_proj_df.iterrows():
                try:
                    actual_sum = float(row['actual_sum'])
                    rates = row['rates'] if row['rates'] else []
                    projected = float(row['current_streams'])
                    proj_sum = 0.0
                    for rate in rates:
                        r = float(rate) if rate is not None else 0.0
                        projected = projected * (1 + r)
                        proj_sum += projected
                    proj_lookup[row['isrc_cd']] = millify(int(actual_sum + proj_sum), precision=1)
                except Exception:
                    pass
        except Exception as e:
            print(f'Bulk projection failed: {e}')
        
        for song_id in unique_songs:
            song_rows = total_df[total_df['isrc_cd'] == song_id]
            first_row = song_rows.iloc[0]
            
            total_streams = int(song_rows['streams'].sum())
            total_streams_display = millify(total_streams, precision=1) if total_streams > 0 else '0'
            
            song_full_data = total_full_df[total_full_df['isrc_cd'] == song_id]
            recent = song_full_data.tail(7)
            weekly_actual   = recent['streams'].sum()
            weekly_expected = recent['expected_sales'].sum()
            performance_ratio = round(weekly_actual / weekly_expected, 2) if weekly_expected > 0 else 1.0
            
            spotify_lf = latest_scores['spotify'].get(song_id)
            spotify_lb = latest_scores['spotify_lean_back'].get(song_id)
            apple_lf = latest_scores['apple'].get(song_id)
            apple_lb = latest_scores['apple_lean_back'].get(song_id)
            total_lf = latest_scores['total_lean_forward'].get(song_id)
            total_lb = latest_scores['total_lean_back'].get(song_id)

            def _safe_score(val):
                if val is None or not pd.notna(val): return None
                f = float(val)
                if f == float('inf') or f == float('-inf') or f != f: return None
                return int(round(f * 100))

            spotify_lf = _safe_score(spotify_lf)
            spotify_lb = _safe_score(spotify_lb)
            apple_lf   = _safe_score(apple_lf)
            apple_lb   = _safe_score(apple_lb)
            total_lf   = _safe_score(total_lf)
            total_lb   = _safe_score(total_lb)
                        
            raw_score = track_score_lookup.get(song_id)
            if raw_score is None:
                interesting_score = 0
            elif raw_score == float('inf'):
                interesting_score = 100
            elif raw_score == float('-inf'):
                interesting_score = -100
            else:
                interesting_score = int(raw_score * 100)

            raw_product_name = str(first_row['product_name'])
            suppl = first_row.get('suppl_title')
            display_product_name = f"{raw_product_name} - {suppl}" if pd.notna(suppl) and str(suppl).strip() else raw_product_name
            
            song_list.append({
                'song_id': str(song_id),
                'product_name': str(first_row['product_name']),
                'artist_name': str(first_row['artist_name']),
                'label_parent_name': str(first_row['fin_label_parent_name']),
                'label_filter_name': str(first_row['company_brand_name']),
                'total_streams': total_streams,
                'total_streams_display': total_streams_display,
                'interesting_score': interesting_score,
                'performance_ratio': performance_ratio, 
                'spotify_lf': spotify_lf,
                'spotify_lb': spotify_lb,
                'apple_lf': apple_lf,
                'apple_lb': apple_lb,
                'total_lf': total_lf,
                'total_lb': total_lb,
                 'next_week_proj': proj_lookup.get(str(song_id), '-')
            })
        
        return song_list
    
    def get(self):
        geo_country = request.args.get('geo_country', 'US')
        domestic    = request.args.get('domestic', '0') == '1'
        user_email  = session.get('email', 'unknown')
        saved_label_filter = ''

        is_fresh_visit = 'geo_country' not in request.args

        if is_fresh_visit and user_email != 'unknown':
            try:
                pref_df = self.smeds.query_db(
                    f"SELECT geo_country, domestic, label_filter FROM first_35.user_preferences WHERE user_email = '{user_email}'",
                    'main'
                )
                if not pref_df.empty:
                    geo_country        = pref_df['geo_country'].iloc[0] or 'US'
                    domestic           = bool(pref_df['domestic'].iloc[0])
                    saved_label_filter = pref_df['label_filter'].iloc[0] or ''
            except Exception as e:
                print(f'Failed to load user preferences: {e}')

        label_from_url = request.args.get('label', '')
        if label_from_url:
            saved_label_filter = label_from_url

        if user_email != 'unknown':
            try:
                self.smeds.update_db(f"""
                    INSERT INTO first_35.user_preferences
                        (user_email, label_filter, geo_country, domestic, data_type, source_platform)
                    VALUES ('{user_email}', '{saved_label_filter}', '{geo_country}', {domestic}, '', 'total')
                    ON CONFLICT (user_email) DO UPDATE SET
                        geo_country  = EXCLUDED.geo_country,
                        domestic     = EXCLUDED.domestic,
                        label_filter = EXCLUDED.label_filter
                """, 'main')
            except Exception as e:
                print(f'Failed to save user preferences: {e}')

        log_first35_visit(self.smeds, user_email, '/first_35_pro', geo_country)

        self.load_all_sources(geo_country)
        song_list = self.get_song_list_pro(geo_country, domestic=domestic)

        labels_q = f"""
            SELECT DISTINCT company_brand_name
            FROM first_35.current_events
            WHERE geo_country = '{geo_country}'
            AND company_brand_name IS NOT NULL
            ORDER BY company_brand_name
        """
        available_labels = self.smeds.query_db(labels_q, 'main')['company_brand_name'].tolist()

        return render_template('first_35_pro.html',
                        song_list=song_list,
                        total_songs=len(song_list),
                        geo_country=geo_country,
                        domestic=domestic,
                        saved_label_filter=saved_label_filter,
                        available_labels=available_labels)

class First35AlbumsView(First35StandardView):
    # hardcoding these right now
    ALBUM_NAME   = "Dandelion"
    ARTIST_FILTER = "Ella Langley"

    def load_album_tracks(self, geo_country='US', source='spotify', release_id=None):
        try:
            if release_id:
                filter_clause = f"AND ce.isrc_cd IN (SELECT isrc_cd FROM first_35.current_release_info WHERE release_id = '{release_id}')"
            else:
                filter_clause = f"AND LOWER(ce.artist_name) LIKE '%%{self.ARTIST_FILTER.lower()}%%'"

            q = f"""
                WITH base AS (
                    SELECT ce.*,
                        MAX(ce.decay_day) OVER (PARTITION BY ce.isrc_cd) AS max_decay_day,
                        ri.track_no
                    FROM first_35.current_events ce
                    LEFT JOIN first_35.current_release_info ri
                        ON ri.isrc_cd = ce.isrc_cd
                        AND ri.release_id = '{release_id}'
                    WHERE ce.source_of_stream = '{source}'
                    AND ce.geo_country = '{geo_country}'
                    {filter_clause}
                )
                SELECT
                    isrc_cd,
                    artist_name,
                    product_name,
                    fin_label_parent_name,
                    company_brand_name,
                    suppl_title,
                    genre_name,
                    artwork_url,
                    MIN(track_no) AS track_no,
                    MAX(decay_day) AS last_day,
                    COUNT(decay_day) AS data_length,
                    SUM(streams) AS total_streams,
                    SUM(CASE WHEN decay_day >= max_decay_day - 6 THEN streams END) AS weekly_streams,
                    MAX(CASE WHEN decay_day = max_decay_day THEN anomaly_score END) AS anomaly_score
                FROM base
                GROUP BY
                    isrc_cd, artist_name, product_name,
                    fin_label_parent_name, company_brand_name,
                    suppl_title, genre_name, artwork_url
                ORDER BY track_no ASC NULLS LAST
            """
            df = self.smeds.query_db(q, 'main')

            if df.empty:
                return []

            df['anomaly_score'] = df['anomaly_score'].fillna(0.0)
            df['total_streams'] = df['total_streams'].fillna(0).astype(int)
            df['weekly_streams'] = df['weekly_streams'].fillna(0).astype(int)
            df['last_day'] = df['last_day'].fillna(0).astype(int)
            df['data_length'] = df['data_length'].fillna(0).astype(int)
            df['total_streams_display'] = df['total_streams'].apply(lambda x: millify(x, precision=1))

            df['performance_category'] = 'stable'
            df.loc[df['anomaly_score'] >= 0.20,  'performance_category'] = 'overperforming'
            df.loc[df['anomaly_score'] <= -0.20, 'performance_category'] = 'underperforming'
            df['song_id'] = df['isrc_cd']

            return df.to_dict('records')

        except Exception as e:
            print(f'AlbumsView.load_album_tracks failed: {e}')
            import traceback
            traceback.print_exc()
            return []

    def get(self):
        geo_country = request.args.get('geo_country', 'US')
        release_id  = request.args.get('release_id', None)
        source = request.args.get('source', 'total') or 'total'
        user_email  = session.get('email', 'unknown')
        log_first35_visit(self.smeds, user_email, '/albums', geo_country)

        # getting all the releases for dropdown
        releases_q = """
            SELECT DISTINCT release_id, release_name, release_date, track_count
            FROM first_35.current_release_info
            WHERE track_type = 'album_track'
            AND (release_version IS NULL OR release_version != 'Apple / Amazon Only')
            ORDER BY release_name, release_date DESC
        """
        releases_df = self.smeds.query_db(releases_q, 'main')
        releases_df['release_date_str'] = pd.to_datetime(releases_df['release_date']).dt.strftime('%b %d, %Y')
        available_releases = releases_df.to_dict('records')

        if not release_id:
            default_q = f"""
                SELECT ri.release_id
                FROM first_35.current_release_info ri
                JOIN (
                    SELECT DISTINCT isrc_cd, artist_name
                    FROM first_35.current_events
                    WHERE source_of_stream = '{source}'
                ) ce ON ce.isrc_cd = ri.isrc_cd
                WHERE ri.track_type = 'album_track'
                AND LOWER(ri.release_name) LIKE '%%{self.ALBUM_NAME.lower()[:20]}%%'
                ORDER BY ri.release_date DESC
                LIMIT 1
            """
            default_df = self.smeds.query_db(default_q, 'main')
            if not default_df.empty:
                release_id = str(default_df['release_id'].iloc[0])
        
        if not release_id:
            return render_template('first_35_albums.html',
                available_releases=available_releases,
                tracks=[], track_count=0, total_streams=0,
                avg_score=0, best_track=None, worst_track=None,
                geo_country=geo_country, daily_streams=[],
                album_name=None, artist_name=None,
                release_id=None, release_date_str=None)
        
        release_row = releases_df[releases_df['release_id'].astype(str) == str(release_id)]
        if release_row.empty:
            # release_id was filtered out (e.g. Apple/Amazon Only) — treat as if none selected
            release_id = None

        if not release_id or release_row.empty:
            return render_template('first_35_albums.html',
                available_releases=available_releases,
                tracks=[], track_count=0, total_streams=0,
                avg_score=0, best_track=None, worst_track=None,
                geo_country=geo_country, daily_streams=[],
                album_name=None, artist_name=None,
                release_id=None, release_date_str=None)

        album_name       = release_row['release_name'].iloc[0]
        release_date_str = release_row['release_date_str'].iloc[0]
        
        # okay i need to get artist name and artwork here
        meta_q = f"""
            SELECT ce.artist_name, ce.artwork_url
            FROM first_35.current_events ce
            JOIN (
                SELECT isrc_cd
                FROM first_35.current_release_info
                WHERE release_id = '{release_id}'
                AND track_type = 'album_track'
                LIMIT 1
            ) ri ON ri.isrc_cd = ce.isrc_cd
            WHERE ce.source_of_stream = 'spotify'
            LIMIT 1
        """
        meta_df = self.smeds.query_db(meta_q, 'main')
        artist_name = meta_df['artist_name'].iloc[0] if not meta_df.empty else 'Unknown'
        artwork_url = meta_df['artwork_url'].iloc[0] if not meta_df.empty and 'artwork_url' in meta_df.columns else None
        release_date = release_row['release_date'].iloc[0]

        daily_query = f"""
            SELECT
                report_date,
                SUM(streams) AS total_streams
            FROM first_35.current_release_streams_agg
            WHERE release_id = '{release_id}'
            AND geo_country = '{geo_country}'
            AND source_of_stream = '{source}'
            -- AND before_first_friday = FALSE
            AND report_date >= '{release_date}'
            GROUP BY report_date
            ORDER BY report_date
        """
        daily_df = self.smeds.query_db(daily_query, 'main')


        benchmark_query = f"""
            SELECT decay_day, projected_streams
            FROM first_35.current_release_decay_agg
            WHERE release_id = '{release_id}'
            AND geo_country = '{geo_country}'
            AND source_of_stream = '{source}'
            ORDER BY decay_day
        """
        benchmark_df = self.smeds.query_db(benchmark_query, 'main')
        benchmark_streams = []
        first_friday_str = None
        show_first_friday_line = False
        if not benchmark_df.empty:
            release_date_dt = pd.to_datetime(release_date)
            days_to_friday = (4 - release_date_dt.weekday()) % 7
            first_friday_dt = release_date_dt + pd.Timedelta(days=days_to_friday)
            first_friday_str = first_friday_dt.strftime('%Y-%m-%d')
            show_first_friday_line = 1 <= days_to_friday <= 3

            benchmark_df['report_date'] = benchmark_df['decay_day'].apply(
                lambda d: (first_friday_dt + pd.Timedelta(days=d)).strftime('%Y-%m-%d')
            )
            if not daily_df.empty:
                last_actual_date = daily_df['report_date'].astype(str).max()
                benchmark_df = benchmark_df[benchmark_df['report_date'] <= last_actual_date]
                benchmark_streams = benchmark_df[['report_date', 'projected_streams']].to_dict('records')

        daily_streams = []
        if not daily_df.empty:
            daily_df['report_date'] = daily_df['report_date'].astype(str)
            daily_streams = daily_df.to_dict('records')
            total_streams = millify(int(daily_df['total_streams'].sum()), precision=1)
        else:
            total_streams = '0'

        tracks = self.load_album_tracks(geo_country, source=source, release_id=release_id)

        track_count_val = int(release_row['track_count'].iloc[0]) if not release_row.empty else len(tracks)

        if tracks:
            avg_score   = round(sum(t['anomaly_score'] for t in tracks) / len(tracks), 3)
            best_track  = max(tracks, key=lambda t: t['anomaly_score'])
            worst_track = min(tracks, key=lambda t: t['anomaly_score'])
        else:
            avg_score   = 0
            best_track  = None
            worst_track = None

        score_query = f"""
            SELECT source_of_stream, interesting_score_pct
            FROM first_35.current_release_decay_agg crda
            WHERE release_id = '{release_id}'
            AND geo_country = '{geo_country}'
            AND source_of_stream IN ('total', 'spotify', 'apple', 'spotify_lean_forward', 'spotify_lean_back', 'apple_lean_forward', 'apple_lean_back')
            AND actual_streams IS NOT NULL
            AND decay_day = (
                SELECT MAX(decay_day)
                FROM first_35.current_release_decay_agg
                WHERE release_id = '{release_id}'
                AND geo_country = '{geo_country}'
                AND source_of_stream = crda.source_of_stream
                AND actual_streams IS NOT NULL
            )
        """
        score_df = self.smeds.query_db(score_query, 'main')

        def get_score(src):
            rows = score_df[score_df['source_of_stream'] == src]
            return int(rows['interesting_score_pct'].iloc[0]) if not rows.empty else 0

        sp_score           = get_score('spotify')
        ap_score           = get_score('apple')
        avg_score          = get_score(source)
        if source == 'total':
            combined_avg_score = get_score('total')
        else:
            combined_avg_score = int(round((sp_score + ap_score) / 2)) if sp_score and ap_score else int(sp_score or ap_score)

        source_display_map = {
            'total':                'Total',
            'spotify':              'Spotify',
            'apple':                'Apple',
            'spotify_lean_forward': 'SP Lean Fwd',
            'spotify_lean_back':    'SP Lean Back',
            'apple_lean_forward':   'AP Lean Fwd',
            'apple_lean_back':      'AP Lean Back',
        }
        source_display_name = source_display_map.get(source, source)

        return render_template(
            'first_35_albums.html',
            album_name         = album_name,
            artist_name        = artist_name,
            artwork_url        = artwork_url,
            release_id         = release_id,
            release_date_str   = release_date_str,
            tracks             = tracks,
            track_count        = len(tracks),
            total_streams      = total_streams,
            avg_score          = avg_score,
            best_track         = best_track,
            worst_track        = worst_track,
            geo_country        = geo_country,
            daily_streams      = daily_streams,
            source             = source,
            benchmark_streams = benchmark_streams,
            release_date_iso = str(release_row['release_date'].iloc[0]),
            available_releases = available_releases,
            combined_avg_score  = combined_avg_score,  
            source_display_name = source_display_name,
            first_friday=first_friday_str,
            show_first_friday_line=show_first_friday_line
        ) 

# TODO: Move ROAS Views here.