import gspread
from gspread_formatting import (
    ConditionalFormatRule,
    GridRange,
    BooleanRule,
    BooleanCondition,
    CellFormat,
    Color,
    format_cell_range,
    get_conditional_format_rules,
    TextFormat,
    numberFormat,
    batch_updater
)

import pandas as pd
import numpy as np

import json
import re
import time

import datetime as dt
from dateutil.relativedelta import relativedelta, FR
import pytz

import requests

import html
import boto3
import os
import sys

import long_term_growth

def setup_email_params():

    send_the_email = True
    send_only_to_myself = False

    client = boto3.client('ses',region_name='us-east-1')

    email_params = {
        'client':client,
        'send_the_email':send_the_email,
        'send_only_to_myself':send_only_to_myself
    }

    return email_params

def init_secrets():

    session = boto3.session.Session()
    client = session.client(
        service_name='secretsmanager',
        region_name='us-east-1'
    )

    sf_user = client.get_secret_value(SecretId="dev/awal-ar/SNOWFLAKE_USER")["SecretString"]
    sf_account = client.get_secret_value(SecretId="dev/awal-ar/SNOWFLAKE_ACCOUNT")["SecretString"]
    sf_warehouse = client.get_secret_value(SecretId="dev/awal-ar/SNOWFLAKE_WAREHOUSE")["SecretString"]
    sf_password = client.get_secret_value(SecretId="dev/awal-ar/PEM_KEY_PASSWORD")["SecretString"]
    sf_pem_key = client.get_secret_value(SecretId="dev/awal-ar/PEM_KEY")["SecretString"]

    gspread_creds = client.get_secret_value(SecretId="dev/awal-weekly-report/GSPREAD_CREDS")["SecretString"]
        
    from cryptography.hazmat.backends import default_backend
    from cryptography.hazmat.primitives import serialization

    p_key = serialization.load_pem_private_key(
        sf_pem_key.encode('utf-8').decode('unicode_escape').encode("utf-8"),
        password=sf_password.encode('utf-8'),
        backend=default_backend()
    )

    pkb = p_key.private_bytes(
        encoding=serialization.Encoding.DER,
        format=serialization.PrivateFormat.PKCS8,
        encryption_algorithm=serialization.NoEncryption())

    sf_secrets = {
        'sf_user':sf_user,
        'sf_account':sf_account,
        'sf_warehouse':sf_warehouse,
        'sf_password':sf_password,
        'pkb':pkb
    }

    gspread_creds = gspread_creds.encode('utf-8').decode('unicode_escape').encode("utf-8")
    gspread_creds = json.loads(gspread_creds)

    return sf_secrets,gspread_creds

def orcd_query(sql_query,sf_params):

    ctx = sf_params['snowflake'].connector.connect(
        user=sf_params['sf_user'],
        private_key=sf_params['pkb'],
        account=sf_params['sf_account'],
        warehouse=sf_params['sf_warehouse']
    )
    cs = ctx.cursor(sf_params['snowflake'].connector.DictCursor)
    try:
        cs.execute(sql_query)
        result = cs.fetchall()
    finally:
        cs.close()
    ctx.close()

    result = pd.DataFrame(result)

    return result

def upload_df_to_snowflake_table(df,table_name,sf_params):
    import streamlit # write_pandas won't run without streamlit package for some reason
    from snowflake.connector.pandas_tools import write_pandas

    ctx = sf_params['snowflake'].connector.connect(
        user=sf_params['sf_user'],
        private_key=sf_params['pkb'],
        account=sf_params['sf_account'],
        warehouse=sf_params['sf_warehouse'],
        database='awal',
        schema='awal_ar'
    )
    try:
        write_pandas(ctx, df, table_name, auto_create_table=True)
    finally:
        ctx.close()

def prep_and_send_email(email_params,destination_emails,email_subject,full_email):

    if email_params['send_the_email']:

        if email_params['send_only_to_myself']:
            destination_emails = ['joselyn.ho@awal.com']

        response = email_params['client'].send_email(
            Source='awalresearch@dev.theorchard.io',
            Destination={
                'ToAddresses': destination_emails,
            },
            Message={
                'Subject': {
                    'Data': email_subject,
                    'Charset': 'UTF-8'
                },
                'Body': {
                    'Html': {
                        'Data': full_email,
                        'Charset': 'UTF-8'
                    }
                }
            },
            SourceArn='arn:aws:ses:us-east-1:103233932089:identity/dev.theorchard.io',
            ReplyToAddresses=[
                'joselyn.ho@awal.com'
            ],
        )

        return response['ResponseMetadata']['HTTPStatusCode'] == 200

    else:
        # this is for local testing where no email is sent. Just opens a browser with the contents

        import webbrowser
        import os

        # Save the file
        file_path = os.path.abspath("preview_email.html")
        with open(file_path, "w", encoding="utf-8") as f:
            f.write(full_email)

        # Open it in the default web browser
        webbrowser.open(f"file://{file_path}")

def setup_logger():

    import logging

    logger = logging.getLogger()
    logger.setLevel(logging.INFO)

    # Add a logger handler if none exists
    if not logger.hasHandlers():
        handler = logging.StreamHandler()
        formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s')
        handler.setFormatter(formatter)
        logger.addHandler(handler)

    return logger

# Helper: Convert 0-based index to column letter (A, B, C, ..., AA, AB, etc.)
def col_num_to_letter(n):
    result = ''
    while n >= 0:
        result = chr(n % 26 + ord('A')) + result
        n = n // 26 - 1
    return result

def count_thursdays(start, end):
    return pd.date_range(start, end).weekday.tolist().count(3)

def merge_organize_df(non_empty_dfs,today,most_recent_thursday,ps_benchmarks_full):

    if not non_empty_dfs:
        sys.exit()
    elif len(non_empty_dfs) == 1:
        df = non_empty_dfs[0]
    elif len(non_empty_dfs) == 2:
        df = non_empty_dfs[0].merge(non_empty_dfs[1],how='outer',on=['ISRC','ACTIVE_TRACK'],suffixes=(None,'_y'))
        df['ARTIST'] = df['ARTIST'].fillna(df['ARTIST_y'])
        df['TRACK'] = df['TRACK'].fillna(df['TRACK_y'])
        df['RELEASE_DATE'] = df['RELEASE_DATE'].fillna(df['RELEASE_DATE_y'])
        df = df.drop(df.columns[df.columns.str.endswith('_y')],axis=1)
        
    else:
        df = non_empty_dfs[0].merge(non_empty_dfs[1],how='outer',on=['ISRC','ACTIVE_TRACK'],suffixes=(None,'_y'))
        df['ARTIST'] = df['ARTIST'].fillna(df['ARTIST_y'])
        df['TRACK'] = df['TRACK'].fillna(df['TRACK_y'])
        df['RELEASE_DATE'] = df['RELEASE_DATE'].fillna(df['RELEASE_DATE_y'])
        df = df.drop(df.columns[df.columns.str.endswith('_y')],axis=1)
        
        # 2nd merge
        df = df.merge(non_empty_dfs[2],how='outer',on=['ISRC','ACTIVE_TRACK'],suffixes=(None,'_y'))
        df['ARTIST'] = df['ARTIST'].fillna(df['ARTIST_y'])
        df['TRACK'] = df['TRACK'].fillna(df['TRACK_y'])
        df['RELEASE_DATE'] = df['RELEASE_DATE'].fillna(df['RELEASE_DATE_y'])
        df = df.drop(df.columns[df.columns.str.endswith('_y')],axis=1)
        
    isrc_urls = '=HYPERLINK("' + 'https://insights.awal.com/song/' + df['ISRC'] + '", "' + df['ISRC'] + '")'
    active_track = df['ACTIVE_TRACK']
    df = df.drop(['ISRC','WEEK_ENDING','ACTIVE_TRACK'],axis=1)

    df = df.drop_duplicates()
    

    # calc save rate

    if ('SAVES_TW' in df.columns) and ('TW_LISTENERS' in df.columns):
        df['SAVE_RATE'] = df['SAVES_TW'] / df['TW_LISTENERS']

        if 'SAVES_LW' in df.columns:
            df = df.drop([
                'SAVES_LW','SKIP_RATE_PCT_CHG'
            ],axis=1)

        df = df.drop([
            'SAVES_TW','TW_LISTENERS','LW_LISTENERS'
        ],axis=1)
    elif ('SAVES_TW' in df.columns) and ('TW_LISTENERS' not in df.columns):
        df = df.rename(columns={'SAVES_TW':'N_SAVES'})

        if 'SAVES_LW' in df.columns:
            df = df.drop([
                'SAVES_LW','SKIP_RATE_PCT_CHG'
            ],axis=1)
    elif ('SAVES_TW' not in df.columns) and ('TW_LISTENERS' in df.columns):
        df = df.drop([
            'TW_LISTENERS','LW_LISTENERS'
        ],axis=1)

    if 'SPL' in df.columns:
        df = df.drop([
            'SPL_PCT_CHG','SPL_COL_PCT_CHG','SPL_PCT_CHG'
        ],axis=1)

    if 'PASSION_SCORE_PCT_CHG' in df.columns:
        df = df.drop([
            'PASSION_SCORE_PCT_CHG'
        ],axis=1)

    df['ISRC'] = isrc_urls
    df['ACTIVE_TRACK'] = active_track
    
    # number of weeks out
    df['END_DATE'] = today
    df['END_DATE'] = pd.to_datetime(df['END_DATE'])
    df['RELEASE_DATE'] = pd.to_datetime(df['RELEASE_DATE'])
    insert_loc = df.columns.get_loc('RELEASE_DATE') + 1

    # exclude songs if release date is after week-ending
    df = df.loc[df['RELEASE_DATE'].dt.date < most_recent_thursday]

    df['thursday_count'] = df.apply(lambda row: count_thursdays(row['RELEASE_DATE'], row['END_DATE']), axis=1)
    if today.weekday()==3:
        # if today is thursday, don't count today (this only applies to testing during the week)
        df['thursday_count'] = df['thursday_count'] -1
    week_number = np.where(
        df['RELEASE_DATE'].dt.weekday < 4, # midweek release
        df['thursday_count']-1,
        df['thursday_count']
    )
    df.insert(insert_loc,'W',week_number)
    df = df.drop('thursday_count',axis=1)

    df['RELEASE_DATE'] = df['RELEASE_DATE'].astype(str).replace('NaT', '')

    df = df.fillna(0)

    # Identify columns ending with 'pct_chg'
    pct_chg_cols = [col for col in df.columns if col.endswith('PCT_CHG')]

    # Loop through columns and insert color column after each
    for col in reversed(pct_chg_cols):  # reverse to avoid shifting column positions as we insert
        
        color_col = col + '_color'
        colors = np.where(
            df['W']<=1,
            'green',
            np.where(
                (df[col].round(2) > -.25) & (df[col].round(2) < .25),
                'yellow',
                np.where(
                    df[col].round(2) <= -.25,
                    'red',
                    np.where(
                        df[col].round(2) >= .25, 
                        'green',
                        ''
                    )
                )
            )
        )
        
        # Find the position to insert the column (right after the original column)
        insert_loc = df.columns.get_loc(col) + 1
        df.insert(insert_loc, color_col, colors)

    df = df.drop(df.columns[df.columns.str.endswith((
        'SKIP_RATE_PCT_CHG','SAVE_RATE_PCT_CHG','LW',
        'SPL_PCT_CHG','SPL_COL_PCT_CHG','PASSION_SCORE_PCT_CHG',
        'END_DATE'
    ))],axis=1)

    # passion score percentiles
    if ('PASSION_SCORE' in df.columns):
        temp_df_full = df.copy()
        temp_df_full['W'] = temp_df_full['W'].clip(upper=79) # because benchmarks only go up to week 79

        result = []

        for each_interval in ['PS7','PS3','PS2','PS1']:
            # by interval (3,2,1 is for mid week releases)
            temp_df = temp_df_full.loc[temp_df_full['PS_TYPE']==each_interval]

            ps_benchmarks = ps_benchmarks_full[['W','PASSION_SCORE_RANK',f"PASSION_SCORE_{each_interval}"]].copy()

            # ensure benchmark passion score column is numeric
            ps_benchmarks[f"PASSION_SCORE_{each_interval}"] = pd.to_numeric(
                ps_benchmarks[f"PASSION_SCORE_{each_interval}"],
                errors="coerce"
            )

            # by number of weeks out
            for week, g in temp_df.groupby("W"):
                
                rank_week = ps_benchmarks[ps_benchmarks["W"] == week].sort_values(f"PASSION_SCORE_{each_interval}")

                g["PASSION_SCORE"] = pd.to_numeric(g["PASSION_SCORE"], errors="coerce")
                g = g.sort_values("PASSION_SCORE")

                rank_week = rank_week.dropna(subset=[f"PASSION_SCORE_{each_interval}"])
                g = g.dropna(subset=["PASSION_SCORE"])

                # if no valid join keys remain, skip this week/interval
                if rank_week.empty or g.empty:
                    continue
                
                merged = pd.merge_asof(
                    g,
                    rank_week,
                    left_on="PASSION_SCORE",
                    right_on=f"PASSION_SCORE_{each_interval}",
                    direction="nearest"
                )
                
                result.append(merged)
        
        if result:
            temp_df_result = pd.concat(result)

            # bring back to original df
            df = df.merge(temp_df_result[['ISRC','PASSION_SCORE_RANK']],how='left',on='ISRC')
        else:
            df['PASSION_SCORE_RANK'] = 0

        df['PASSION_SCORE_RANK'] = df['PASSION_SCORE_RANK'].fillna(0)
        df = df.drop(columns='PS_TYPE')

        # Loop through columns and insert color column after each
        for col in [col for col in df.columns if col.endswith('PASSION_SCORE')]:

            color_col = col + '_color'
            colors = np.where(
                df['PASSION_SCORE_RANK']<40,
                'red',
                np.where(
                    (df['PASSION_SCORE_RANK'] >=40) & (df['PASSION_SCORE_RANK']<60),
                    'yellow',
                    np.where(
                        (df['PASSION_SCORE_RANK'] >=60) & (df['PASSION_SCORE_RANK']<85),
                        'green',
                        'dark green'
                    )
                )
            )
            
            # Find the position to insert the column (right after the original column)
            insert_loc = df.columns.get_loc(col) + 1
            # df.insert(insert_loc, triangle_col, triangles)
            df.insert(insert_loc, color_col, colors)

        df = df.drop(columns='PASSION_SCORE_RANK')


    if 'TW' in df.columns:
        df = df.sort_values('TW',ascending=False)

    return df

def upload_organize_google_sheet(gc,df,is_test,which_type,logger,most_recent_thursday):

    BOLD_HORIZONTAL = "userEnteredFormat.textFormat.bold,userEnteredFormat.horizontalAlignment"

    logger.info('Connecting to google sheets...')

    if which_type=='PRIORITY':
        doc_name = 'AWAL Weekly Priority Tracks (Spotify)'
    elif which_type=='ALL':
        doc_name = 'AWAL Weekly All Tracks (Spotify)'
        if 'TW' in df.columns:
            df = df.loc[df['TW']>=10000]

    sh = gc.open(doc_name)    

    logger.info('Create sheet / initial upload...')

    # create new sheet only if does not exist

    if is_test:
        sheet_name = f'[TEST] W/E {most_recent_thursday.strftime("%m/%d/%y")}'
    else:
        sheet_name = f'W/E {most_recent_thursday.strftime("%m/%d/%y")}'
        
    sheet_exists = True
    sheet_counter = 1

    # create a sheet name that doesn't yet exist
    while sheet_exists:
        try:
            worksheet = sh.worksheet(sheet_name) # if this succeeds then need a new sheet name
            sheet_name = sheet_name + f' ({sheet_counter})'
            sheet_counter = sheet_counter+1
        except gspread.exceptions.WorksheetNotFound:
            sheet_exists = False

        time.sleep(3)

    # create new worksheet
    worksheet = sh.add_worksheet(title=sheet_name, rows="100", cols="30")

    data = [df.columns.tolist()] + df.values.tolist()

    # Upload
    worksheet.update('A1', data, value_input_option="USER_ENTERED")

    BATCH_LIMIT = 50000
    rules = get_conditional_format_rules(worksheet)

    if len([col for col in df.columns if col.endswith('color')])>0:
        logger.info('Coloring...')

        headers = worksheet.row_values(1)
        
        # Find indices of "color" columns and their matching "trend" columns
        color_trend_pairs = []
        for i, header in enumerate(headers):
            if header.lower().endswith("color"):
                color_trend_pairs.append((i, i - 1))
        
        # Define color mapping
        color_map = {
            "red": Color(1, 0.8, 0.8),     # light red
            "yellow": Color(1, 1, 0.6),    # light yellow
            "green": Color(0.8, 1, 0.8),   # light green
            "dark green": Color(94/255, 193/255, 117/255) # darker green
        }
        
        requests = []
        # with batch_updater(sh) as batch:
        # Start from row 2 (row 1 is headers)
        for row_index, row in enumerate(data[1:], start=2):  # row_index in sheet (1-based)
            for color_col_idx, trend_col_idx in color_trend_pairs:
                if color_col_idx < len(row):
                    color_name = row[color_col_idx].strip().lower()
                    if color_name in color_map:
                        cell_address = gspread.utils.rowcol_to_a1(row_index, trend_col_idx + 1)
                        fmt = CellFormat(backgroundColor=color_map[color_name])
                        requests.append((cell_address, fmt))

                        if len(requests) >= BATCH_LIMIT:
                            with batch_updater(sh) as batch:
                                for addr, fmt in requests:
                                    batch.format_cell_range(worksheet, addr, fmt)
                            requests = []

        # Flush last batch
        if requests:
            with batch_updater(sh) as batch:
                for addr, fmt in requests:
                    batch.format_cell_range(worksheet, addr, fmt)
        
        # delete columns
        columns_to_delete = [i+1 for i, col in enumerate(headers) if col.lower().endswith("color")]
        
        # Delete columns from right to left
        for col_index in sorted(columns_to_delete, reverse=True):
            worksheet.delete_columns(col_index)
        
    logger.info('Formatting data columns...')

    requests = []
    requests_sh = []

    headers = worksheet.row_values(1)

    # format percent columns and rename them
    pct_chg_cols = [i for i, col in enumerate(headers) if col.endswith(('PCT','PCT_CHG','RATE'))]
    skip_rate_col = [i for i, col in enumerate(headers) if col.endswith('SKIP_RATE')]
    save_rate_col = [i for i, col in enumerate(headers) if col.endswith('SAVE_RATE')]
    
    # Apply percent format to each matching column
    for col_index in pct_chg_cols:
        col_letter = col_num_to_letter(col_index)
        col_range = f"{col_letter}2:{col_letter}"  # skip header (start from row 2)
        
        requests.append((col_range, CellFormat(numberFormat=numberFormat(type='PERCENT', pattern='0%'))))

        if len(requests) >= BATCH_LIMIT:
            with batch_updater(sh) as batch:
                for addr, fmt in requests:
                    batch.format_cell_range(worksheet, addr, fmt)
            requests = []

        # col width
        requests_sh.append({
            "updateDimensionProperties": {
                "range": {
                    "sheetId": worksheet._properties['sheetId'],
                    "dimension": "COLUMNS",
                    "startIndex": col_index,
                    "endIndex": col_index+1
                },
                "properties": {
                    "pixelSize": 50
                },
                "fields": "pixelSize"
            }
        })

        if col_index in save_rate_col:

            thresholds = [
                (.17, Color(94/255, 193/255, 117/255)),   # darker green: great (90th percentile)
                (.12, Color(0.8, 1, 0.8)),        # light green: good (above avg range)
                (.06, Color(1, 1, 0.6)),          # light yellow: within avg
                (0, Color(1, 0.8, 0.8))         # light red: below avg
            ]

            for threshold, color in thresholds:

                rule = ConditionalFormatRule(
                    ranges=[GridRange.from_a1_range(col_range, worksheet)],
                    booleanRule=BooleanRule(
                        condition=BooleanCondition('CUSTOM_FORMULA', [f'=AND(${col_letter}2<>"", ROUND(${col_letter}2,2)>={threshold})']),
                        format=CellFormat(backgroundColor=color)
                    )
                )
                rules.append(rule)
            
            
        elif col_index in skip_rate_col:

            thresholds = [
                (.2, Color(94/255, 193/255, 117/255)),             # darker green: great (90th percentile)
                (.24, Color(0.8, 1, 0.8)),        # light green: good (above avg range)
                (.34, Color(1, 1, 0.6)),          # light yellow: within avg
                (100, Color(1, 0.8, 0.8))         # light red: below avg -- set artificial limit here bc the limit does not exist
            ]

            for threshold, color in thresholds:

                rule = ConditionalFormatRule(
                    ranges=[GridRange.from_a1_range(col_range, worksheet)],
                    booleanRule=BooleanRule(
                        condition=BooleanCondition('CUSTOM_FORMULA', [f'=AND(ROUND(${col_letter}2,2)<={threshold},ROUND(${col_letter}2,2)>0)']),
                        format=CellFormat(backgroundColor=color)
                    )
                )
                rules.append(rule)

    if len(requests_sh) > 0:
        sh.batch_update({"requests": requests_sh})

    # Flush last batch
    if requests:
        with batch_updater(sh) as batch:
            for addr, fmt in requests:
                batch.format_cell_range(worksheet, addr, fmt)

    requests = []
    requests_sh = []

    num_comma_cols = [i for i, col in enumerate(headers) if col.endswith(('TW','ACTIVE','PLAYLIST','RADIO','SEARCH','DISCOVERY'))]
    
    # Apply percent format to each matching column
    for col_index in num_comma_cols:
        col_letter = col_num_to_letter(col_index)
        col_range = f"{col_letter}2:{col_letter}"  # skip header (start from row 2)
        
        requests.append((col_range, CellFormat(numberFormat=numberFormat(type='NUMBER', pattern='#,##0'))))
        if len(requests) >= BATCH_LIMIT:
            with batch_updater(sh) as batch:
                for addr, fmt in requests:
                    batch.format_cell_range(worksheet, addr, fmt)
            requests = []

        # col width
        requests_sh.append({
            "updateDimensionProperties": {
                "range": {
                    "sheetId": worksheet._properties['sheetId'],
                    "dimension": "COLUMNS",
                    "startIndex": col_index,
                    "endIndex": col_index+1
                },
                "properties": {
                    "pixelSize": 80
                },
                "fields": "pixelSize"
            }
        })

    # Flush last batch
    if requests:
        with batch_updater(sh) as batch:
            for addr, fmt in requests:
                batch.format_cell_range(worksheet, addr, fmt)

        
    if len(requests_sh) > 0:
        sh.batch_update({"requests": requests_sh})

    requests = []
    requests_sh = []

    others = [i for i, col in enumerate(headers) if col.endswith(('SPL','SPL_COL','PASSION_SCORE'))]
    spl_col = [i for i, col in enumerate(headers) if col.endswith('SPL')]
    splcol_col = [i for i, col in enumerate(headers) if col.endswith('SPL_COL')]
    passion_col = [i for i, col in enumerate(headers) if col.endswith('PASSION_SCORE')]

    for col_index in others:
        col_letter = col_num_to_letter(col_index)
        col_range = f"{col_letter}2:{col_letter}"  # skip header (start from row 2)
        

        requests.append((col_range, CellFormat(numberFormat=numberFormat(type='NUMBER', pattern='0.00'))))

        if len(requests) >= BATCH_LIMIT:
            with batch_updater(sh) as batch:
                for addr, fmt in requests:
                    batch.format_cell_range(worksheet, addr, fmt)
            requests = []

        # col width
        requests_sh.append({
            "updateDimensionProperties": {
                "range": {
                    "sheetId": worksheet._properties['sheetId'],
                    "dimension": "COLUMNS",
                    "startIndex": col_index,
                    "endIndex": col_index+1
                },
                "properties": {
                    "pixelSize": 75
                },
                "fields": "pixelSize"
            }
        })

        if col_index in spl_col:

            thresholds = [
                (2.2, Color(94/255, 193/255, 117/255)),            # darker green: great (90th percentile)
                (1.9, Color(0.8, 1, 0.8)),        # light green: good (above avg range)
                (1.5, Color(1, 1, 0.6)),          # light yellow: within avg
                (0.0001, Color(1, 0.8, 0.8))      # light red: below avg
            ]

            for threshold, color in thresholds:

                rule = ConditionalFormatRule(
                    ranges=[GridRange.from_a1_range(col_range, worksheet)],
                    booleanRule=BooleanRule(
                        condition=BooleanCondition('CUSTOM_FORMULA', [f'=ROUND(${col_letter}2,2)>={threshold}']),
                        format=CellFormat(backgroundColor=color)
                    )
                )
                rules.append(rule)

        elif col_index in splcol_col:

            thresholds = [
                (2.9, Color(94/255, 193/255, 117/255)),            # darker green: great (90th percentile)
                (2.2, Color(0.8, 1, 0.8)),        # light green: good (above avg range)
                (1.6, Color(1, 1, 0.6)),          # light yellow: within avg
                (0.0001, Color(1, 0.8, 0.8))      # light red: below avg
            ]

            for threshold, color in thresholds:

                rule = ConditionalFormatRule(
                    ranges=[GridRange.from_a1_range(col_range, worksheet)],
                    booleanRule=BooleanRule(
                        condition=BooleanCondition('CUSTOM_FORMULA', [f'=ROUND(${col_letter}2,2)>={threshold}']),
                        format=CellFormat(backgroundColor=color)
                    )
                )
                rules.append(rule)


    # Flush last batch
    if requests:
        with batch_updater(sh) as batch:
            for addr, fmt in requests:
                batch.format_cell_range(worksheet, addr, fmt)

    if len(requests_sh) > 0:
        sh.batch_update({"requests": requests_sh})

    ## save any conditional formatting rules ##

    rules.save()

    ##

    # Modify headers

    updated_headers = [
        "%L" if header.endswith("DISCOVERY_PCT") else
        "%" if header.endswith("PCT") else
        "CHG" if header.endswith("PCT_CHG") else
        "RELEASE" if header.endswith("RELEASE_DATE") else
        "PASSION" if header.endswith("PASSION_SCORE") else
        "SKIPS" if header.endswith("SKIP_RATE") else
        "SAVES" if header.endswith("SAVE_RATE") else
        "ACTIVE TRACK" if header.endswith("ACTIVE_TRACK") else
        header
        for header in headers
    ]

    worksheet.update('1:1', [updated_headers])

    logger.info('Formatting headers...')

    headers = worksheet.row_values(1)

    requests = []
    sh_requests = []

    # Find indices of each section if exists
    indices = [i for i, col in enumerate(headers) if col.endswith(('TW','ACTIVE','PLAYLIST','RADIO','SEARCH','DISCOVERY','SPL','SPL_COL','PASSION','SKIPS','SAVES'))]

    # Insert a new row at the top (at index 1)
    worksheet.insert_row([''], index=1)

    for col_index in indices:
        col_letter = col_num_to_letter(col_index)

        # get header text
        text_value = worksheet.acell(f"{col_letter}2").value

        if text_value in ['SPL','SPL_COL','PASSION','SKIPS','SAVES']:
            merge_width = 1
        elif text_value in ['TW']:
            merge_width = 2
        else:
            merge_width = 3

        if text_value == 'SPL_COL':
            text_value = 'SPL COL'
        
        # Add the new header text
        requests.append({
            'range': f'{col_letter}1',
            'values': [[text_value]],
        })

        # replace old header
        if text_value in ['SKIPS','SAVES']:
            requests.append({
                'range': f'{col_letter}2',
                'values': [['%']],
            })
        else:
            requests.append({
                'range': f'{col_letter}2',
                'values': [['#']],
            })

        # apply border to the left
        sh_requests.append({
            "updateBorders": {
                "range": {
                    "sheetId": worksheet._properties['sheetId'],
                    "startRowIndex": 0,         
                    "endRowIndex": 1000,        
                    "startColumnIndex": col_index,
                    "endColumnIndex": col_index + 1
                },
                "left": {
                    "style": "SOLID",
                    "width": 2,
                    "color": {"red": 0.8, "green": 0.8, "blue": 0.8}
                }
            }
        })

        # bold and center align - HEADER
        sh_requests.append({
            "repeatCell": {
                "range": {
                    "sheetId": worksheet._properties['sheetId'],
                    "startRowIndex": 0,
                    "endRowIndex": 1,
                    "startColumnIndex": col_index,
                    "endColumnIndex": col_index+merge_width
                },
                "cell": {
                    "userEnteredFormat": {
                        "horizontalAlignment": "CENTER",
                        "textFormat": {
                            "bold": True
                        }
                    }
                },
                "fields": BOLD_HORIZONTAL
            }
        })

        # add color to header
        sh_requests.append({
            "updateCells": {
                "range": {
                    "sheetId": worksheet._properties['sheetId'],
                    "startRowIndex": 0,   
                    "endRowIndex": 1,
                    "startColumnIndex": col_index,
                    "endColumnIndex": col_index+merge_width
                },
                "rows": [
                    {
                        "values": [
                            {
                                "userEnteredFormat": {
                                    "backgroundColor": {
                                        "red": 0.6431,
                                        "green": 0.7608,
                                        "blue": 0.9569
                                    }
                                }
                            }
                        ]
                    }
                ],
                "fields": "userEnteredFormat.backgroundColor"
            }
        })

        # merge cells
        sh_requests.append({
            "mergeCells": {
                "range": {
                    "sheetId": worksheet._properties['sheetId'],
                    "startRowIndex": 0,
                    "endRowIndex": 1,
                    "startColumnIndex": col_index,
                    "endColumnIndex": col_index+merge_width
                },
                "mergeType": "MERGE_ALL"
            }
        })

        # bold and center align - SUBHEADER
        sh_requests.append({
            "repeatCell": {
                "range": {
                    "sheetId": worksheet._properties['sheetId'],
                    "startRowIndex": 1,
                    "endRowIndex": 2,
                    "startColumnIndex": col_index,
                    "endColumnIndex": col_index+merge_width
                },
                "cell": {
                    "userEnteredFormat": {
                        "horizontalAlignment": "CENTER",
                        "textFormat": {
                            "bold": True
                        }
                    }
                },
                "fields": BOLD_HORIZONTAL
            }
        })

        if text_value in ['SKIPS','SAVES']:

            # col width
            sh_requests.append({
                "updateDimensionProperties": {
                    "range": {
                        "sheetId": worksheet._properties['sheetId'],
                        "dimension": "COLUMNS",
                        "startIndex": col_index,
                        "endIndex": col_index+1
                    },
                    "properties": {
                        "pixelSize": 75
                    },
                    "fields": "pixelSize"
                }
            })
            
    worksheet.batch_update(requests)
    if len(sh_requests) > 0:
        sh.batch_update({"requests": sh_requests})

    logger.info('Formatting subheaders...')

    # SUBHEADERS

    requests = []

    headers = worksheet.row_values(2)
        
    col_index = [i for i, col in enumerate(headers) if col.endswith(('ARTIST'))][0]

    # col width
    requests.append({
        "updateDimensionProperties": {
            "range": {
                "sheetId": worksheet._properties['sheetId'],
                "dimension": "COLUMNS",
                "startIndex": col_index,
                "endIndex": col_index+1
            },
            "properties": {
                "pixelSize": 100
            },
            "fields": "pixelSize"
        }
    })

    # bold and center align.
    requests.append({
        "repeatCell": {
            "range": {
                "sheetId": worksheet._properties['sheetId'],
                "startRowIndex": 1,
                "endRowIndex": 2,
                "startColumnIndex": col_index,
                "endColumnIndex": col_index+1
            },
            "cell": {
                "userEnteredFormat": {
                    "horizontalAlignment": "CENTER",
                    "textFormat": {
                        "bold": True
                    }
                }
            },
            "fields": BOLD_HORIZONTAL
        }
    })

    col_indices = [i for i, col in enumerate(headers) if col.endswith(('TRACK'))]

    for col_index in col_indices:
        # col width
        requests.append({
            "updateDimensionProperties": {
                "range": {
                    "sheetId": worksheet._properties['sheetId'],
                    "dimension": "COLUMNS",
                    "startIndex": col_index,
                    "endIndex": col_index+1
                },
                "properties": {
                    "pixelSize": 150
                },
                "fields": "pixelSize"
            }
        })

        # bold and center align.
        requests.append({
            "repeatCell": {
                "range": {
                    "sheetId": worksheet._properties['sheetId'],
                    "startRowIndex": 1,
                    "endRowIndex": 2,
                    "startColumnIndex": col_index,
                    "endColumnIndex": col_index+1
                },
                "cell": {
                    "userEnteredFormat": {
                        "horizontalAlignment": "CENTER",
                        "textFormat": {
                            "bold": True
                        }
                    }
                },
                "fields": BOLD_HORIZONTAL
            }
        })

    col_index = [i for i, col in enumerate(headers) if col.startswith(('RELEASE'))][0]

    # col width
    requests.append({
        "updateDimensionProperties": {
            "range": {
                "sheetId": worksheet._properties['sheetId'],
                "dimension": "COLUMNS",
                "startIndex": col_index,
                "endIndex": col_index+1
            },
            "properties": {
                "pixelSize": 80
            },
            "fields": "pixelSize"
        }
    })

    # bold and center align.
    requests.append({
        "repeatCell": {
            "range": {
                "sheetId": worksheet._properties['sheetId'],
                "startRowIndex": 1,
                "endRowIndex": 2,
                "startColumnIndex": col_index,
                "endColumnIndex": col_index+1
            },
            "cell": {
                "userEnteredFormat": {
                    "horizontalAlignment": "CENTER",
                    "textFormat": {
                        "bold": True
                    }
                }
            },
            "fields": BOLD_HORIZONTAL
        }
    })

    col_index = [i for i, col in enumerate(headers) if col.startswith(('W'))][0]

    # col width
    requests.append({
        "updateDimensionProperties": {
            "range": {
                "sheetId": worksheet._properties['sheetId'],
                "dimension": "COLUMNS",
                "startIndex": col_index,
                "endIndex": col_index+1
            },
            "properties": {
                "pixelSize": 35
            },
            "fields": "pixelSize"
        }
    })

    # bold and center align.
    requests.append({
        "repeatCell": {
            "range": {
                "sheetId": worksheet._properties['sheetId'],
                "startRowIndex": 1,
                "endRowIndex": 2,
                "startColumnIndex": col_index,
                "endColumnIndex": col_index+1
            },
            "cell": {
                "userEnteredFormat": {
                    "horizontalAlignment": "CENTER",
                    "textFormat": {
                        "bold": True
                    }
                }
            },
            "fields": BOLD_HORIZONTAL
        }
    })

    # add color to whole SUBHEADER
    requests.append({
        "updateCells": {
            "range": {
                "sheetId": worksheet._properties['sheetId'],
                "startRowIndex": 1,   
                "endRowIndex": 2,
                "startColumnIndex": 0,
                "endColumnIndex": len(headers)
            },
            "rows": [
                {
                    "values": [
                        {
                            "userEnteredFormat": {
                                "backgroundColor": {
                                    "red": 0.7882,
                                    "green": 0.8549,
                                    "blue": 0.9725
                                }
                            }
                        }
                    ]* len(headers)
                }
            ],
            "fields": "userEnteredFormat.backgroundColor"
        }
    })

    sh.batch_update({"requests": requests})

    df = df.reset_index(drop=True)

    ## for new releases, replace percent changes with "NEW"

    new_releases = df.loc[df['W']<=1].index
    # Identify columns ending with 'CHG'
    chg_columns = [i for i, col in enumerate(headers) if col.endswith('CHG')]

    if len(new_releases)>0:
        # Loop through each new row and update the CHG columns
        cells_to_update = []
        sh_requests = []
        for row in new_releases:
            for col in chg_columns:
                cells_to_update.append({
                    'range': gspread.utils.rowcol_to_a1(row+3, col+1),
                    'values': [['NEW']]
                })

                sh_requests.append({
                    "repeatCell": {
                        "range": {
                            "sheetId": worksheet._properties['sheetId'],
                            "startRowIndex": row+2,
                            "endRowIndex": row+2+1,
                            "startColumnIndex": col,
                            "endColumnIndex": col+1
                        },
                        "cell": {
                            "userEnteredFormat": {
                                "horizontalAlignment": "CENTER",
                                "textFormat": {
                                    "bold": True
                                }
                            }
                        },
                        "fields": BOLD_HORIZONTAL
                    }
                })
        worksheet.batch_update(cells_to_update)
        if len(sh_requests) > 0:
            sh.batch_update({"requests": sh_requests})

    # Freeze rows/columns
    n_cols_to_freeze = sum([
        'ARTIST' in df.columns,
        'TRACK' in df.columns,
        'RELEASE_DATE' in df.columns,
        'W' in df.columns
    ])
    freeze_request = {
        "requests": [{
            "updateSheetProperties": {
                "properties": {
                    "sheetId": worksheet._properties['sheetId'],
                    "gridProperties": {
                        "frozenRowCount": 2,
                        "frozenColumnCount": n_cols_to_freeze
                    }
                },
                "fields": "gridProperties.frozenRowCount,gridProperties.frozenColumnCount"
            }
        }]
    }
    sh.batch_update(freeze_request)

    # Add a filter to all columns in subheader

    n_columns = len(df.drop(df.columns[df.columns.str.endswith('_color')],axis=1).columns)

    filter_request = {
        "setBasicFilter": {
            "filter": {
                "range": {
                    "sheetId": worksheet._properties['sheetId'],
                    "startRowIndex": 1,
                    "startColumnIndex": 0,
                    "endColumnIndex": n_columns
                }
            }
        }
    }
    sh.batch_update({"requests": [filter_request]})

    # Move new sheet to the first sheet

    my_worksheet = sh.worksheet(sheet_name)

    # Get a list of all worksheets
    worksheets = sh.worksheets()

    # Create a new list with your target worksheet first,
    # followed by all other worksheets except that one.
    ordered = [my_worksheet] + [ws for ws in worksheets if ws.id != my_worksheet.id]

    # Apply the new order
    sh.reorder_worksheets(ordered)

    logger.info('Sheet complete')

###################################################
###################################################

def handler(event,context):
    is_test = False

    logger = setup_logger()
    logger.info('Starting handler function...')

    import snowflake.connector    

    sf_params,gspread_creds = init_secrets()
    sf_params['snowflake'] = snowflake

    email_params = setup_email_params()

    gc = gspread.service_account_from_dict(gspread_creds)

    ###################################################
    ###################################################

    # READ/ORGANIZE DATA

    ###################################################
    ###################################################

    # destination_email_list = orcd_query('select * from awal.awal_ar.email_alerts',sf_params)

    et_timezone = pytz.timezone('US/Eastern')
    today = dt.datetime.now(et_timezone).date()
    most_recent_thursday = today - relativedelta(weekday=FR(-1)) - relativedelta(days=1)

    # gather data

    logger.info('Read data...')

    df_main = orcd_query(f"""
    select 
        f.* ,
        CASE 
            WHEN p.isrc IS NOT NULL THEN 1 
            ELSE 0 
        END AS ACTIVE_TRACK
    from awal.awal_ar.full_roster_spotify_stats f
    left join awal.awal_ar.priority_tracks p on p.isrc=f.isrc
    where f.week_ending like $${most_recent_thursday}$$
    """,sf_params)
    # df_main_priority = df_main.loc[df_main['IS_PRIORITY']==1]
    # df_main = df_main.drop('IS_PRIORITY',axis=1)
    # df_main_priority = df_main_priority.drop('IS_PRIORITY',axis=1)

    # 7 day passion score runs through friday of the following week as a buffer day in order to get full 7 days for new releases.
    # if 7day score not available, use next highest (3 day, 2 day, 1 day etc). this applies to mid week releases.
    # benchmarks provided by SME
    df_passion_score = orcd_query("""
        with all_songs as (
        select 
            da.artistname,
            s.track_name trackname,
            s.isrc,
            dr.releaseid as upc,
            min(cast(dr.releasedate as date)) releasedate
        from FACTS.PROD.STAGING_RAW_SPOTIFY_V2 s
        join FACTS.PROD.DIM_TRACK dt on dt.isrc=s.isrc
        join FACTS.PROD.DIM_RELEASE dr on dr.releaseid=dt.upc
        join FACTS.PROD.DIM_ARTIST da on da.artistid=dr.artistid
        join FACTS.PROD.DIM_LABEL dl on dl.labelid=dt.labelid
        join FACTS.PROD.SERVICE_TIER st on st.uuid=dl.servicetierid
        full join INTELLIGENCE.DBT_PROD.SUMMARY_STREAMS ss on ss.isrc=dt.isrc and ss.upc=dr.releaseid -- this is for the purpose of checking release dates
        where 1=1
            and s.licensor like 'theorchard'
            and dl.brandid='31f4f0f0-cbb4-4a2c-9eb0-d7288c5a2588'
            and st.name in ('premium-services','tier-1','tier-2','tier-3')
            and dr.product_type='digital'
            and dr.physical_product_type not like 'Music Video'
            and s.download_date between DATEADD(DAY, -7, PREVIOUS_DAY(CURRENT_DATE(), 'Friday')) and PREVIOUS_DAY(CURRENT_DATE(), 'Thursday')
        
            -- for checking release dates
            and ss.download_activity_date > DATEADD(DAY, -7, PREVIOUS_DAY(CURRENT_DATE(), 'Friday'))
            and ss.store='Spotify'
            and ss.isrc is not null
        group by all
    ),
    songs_released as (
        select 
            distinct * from all_songs 
            --where year(releasedate) >= 2018
    ),
    upcs as (
        select distinct upc from songs_released
    ),
    -- clean up duplicate isrc's with multiple tracknames (pick the earliest trackname)
    songs_released_cleaned as (
        SELECT *
        FROM (
            SELECT *,
                ROW_NUMBER() OVER (PARTITION BY isrc ORDER BY releasedate ASC) AS rn
            FROM songs_released
        ) t
        WHERE rn = 1
    ),

    -- 7 day passion score
    ps7 as (
        select 
            s.isrc,
            'PS7' as ps_type,
            max(s.download_date) week_ending,
            Avg(s.passion) As passion
        from all_songs 
        join FACTS.PROD.STAGING_RAW_SPOTIFY_TRACK_PASSION s on s.isrc=all_songs.isrc
        where 1=1
            and s.download_date between DATEADD(DAY, -7, PREVIOUS_DAY(CURRENT_DATE(), 'Friday')) and PREVIOUS_DAY(CURRENT_DATE(), 'Friday')

            and (s.DOWNLOAD_DATE - s.first_lean_forward_date) = 7
        group by 1,2
    ),
    ps3 as (
        select 
            s.isrc,
            'PS3' as ps_type,
            max(s.download_date) week_ending,
            Avg(s.passion) As passion
        from all_songs 
        join FACTS.PROD.STAGING_RAW_SPOTIFY_TRACK_PASSION s on s.isrc=all_songs.isrc
        where 1=1
            and s.download_date between DATEADD(DAY, -7, PREVIOUS_DAY(CURRENT_DATE(), 'Friday')) and PREVIOUS_DAY(CURRENT_DATE(), 'Friday')

            and (s.DOWNLOAD_DATE - s.first_lean_forward_date) = 3
        group by 1,2
    ),
    ps2 as (
        select 
            s.isrc,
            'PS2' as ps_type,
            max(s.download_date) week_ending,
            Avg(s.passion) As passion
        from all_songs 
        join FACTS.PROD.STAGING_RAW_SPOTIFY_TRACK_PASSION s on s.isrc=all_songs.isrc
        where 1=1
            and s.download_date between DATEADD(DAY, -7, PREVIOUS_DAY(CURRENT_DATE(), 'Friday')) and PREVIOUS_DAY(CURRENT_DATE(), 'Friday')

            and (s.DOWNLOAD_DATE - s.first_lean_forward_date) = 2
        group by 1,2
    ),
    ps1 as (
        select 
            s.isrc,
            'PS1' as ps_type,
            max(s.download_date) week_ending,
            Avg(s.passion) As passion
        from all_songs 
        join FACTS.PROD.STAGING_RAW_SPOTIFY_TRACK_PASSION s on s.isrc=all_songs.isrc
        where 1=1
            and s.download_date between DATEADD(DAY, -7, PREVIOUS_DAY(CURRENT_DATE(), 'Friday')) and PREVIOUS_DAY(CURRENT_DATE(), 'Friday')

            and (s.DOWNLOAD_DATE - s.first_lean_forward_date) = 1
        group by 1,2
    ),

    -- TW --

    tw as (
        select 
            a.isrc,
            coalesce(ps7.ps_type,ps3.ps_type,ps2.ps_type,ps1.ps_type) ps_type,
            coalesce(ps7.week_ending,ps3.week_ending,ps2.week_ending,ps1.week_ending) week_ending,
            coalesce(ps7.passion,ps3.passion,ps2.passion,ps1.passion) As passion
        from all_songs a
        left join ps7 on ps7.isrc=a.isrc
        left join ps3 on ps3.isrc=a.isrc
        left join ps2 on ps2.isrc=a.isrc
        left join ps1 on ps1.isrc=a.isrc
    )
        
    select 
        distinct

        tm.artistname artist,
        tm.trackname track,
        tm.releasedate release_date,
        tm.isrc,
        
        tw.week_ending,
        tw.ps_type,
        tw.passion passion_score,

        CASE 
            WHEN p.isrc IS NOT NULL THEN 1 
            ELSE 0 
        END AS ACTIVE_TRACK
        
    from songs_released_cleaned tm
    join tw on tw.isrc=tm.isrc
    left join awal.awal_ar.priority_tracks p on p.isrc=tw.isrc
    where passion is not null
    ;
    """,sf_params)
    df_passion_score['PASSION_SCORE'] = df_passion_score['PASSION_SCORE'].astype(float)
    

    df_skips_saves = orcd_query(f"""
    select 
        f.* ,
        CASE 
            WHEN p.isrc IS NOT NULL THEN 1 
            ELSE 0 
        END AS ACTIVE_TRACK
    from awal.awal_ar.full_roster_spotify_skips_saves f
    left join awal.awal_ar.priority_tracks p on p.isrc=f.isrc
    where week_ending like $${most_recent_thursday}$$
    """,sf_params)

    ps_benchmarks_full = orcd_query(f"""
    select 
        W,
        PASSION_SCORE_PS7,PASSION_SCORE_PS3,PASSION_SCORE_PS2,PASSION_SCORE_PS1,
        RANK as PASSION_SCORE_RANK 
    from awal.awal_ar.passion_score_benchmarks
    """,sf_params)  


    ################################################

    logger.info('ALL tracks...')

    dfs = [df_main.copy(), df_passion_score.copy(), df_skips_saves.copy()]
    non_empty_dfs = [df for df in dfs if not df.empty]

    logger.info('Merging/Organizing...')

    df = merge_organize_df(non_empty_dfs,today,most_recent_thursday,ps_benchmarks_full)

    upload_organize_google_sheet(gc,df,is_test,'ALL',logger,most_recent_thursday)


    ################################################

    logger.info('Finished weekly tracks report')

    logger.info('Running long term growth report...')

    try:
        long_term_growth.handler(event,context)
    
        logger.info('Finished')
    except Exception as e:
        logger.info(e)
        logger.info('Long term growth report failed')

    return {
        "statusCode": 200,
        "message": "Handler function ran successfully"
    }