import gspread
from gspread_formatting import (
    ConditionalFormatRule,
    GridRange,
    BooleanRule,
    BooleanCondition,
    CellFormat,
    Color,
    format_cell_range,
    get_conditional_format_rules,
    TextFormat,
    numberFormat,
    batch_updater
)
from gspread.utils import rowcol_to_a1
from google.oauth2.service_account import Credentials
from googleapiclient.discovery import build

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

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

# Define tiers
def get_multiplier(value):
    if value < 25000:
        return 1.5
    elif value < 50000:
        return 1.4
    elif value < 100000:
        return 1.3
    elif value < 250000:
        return 1.2
    elif value < 500000:
        return 1.1
    elif value < 1000000:
        return 1.05
    else:
        return 1.01

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 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

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

    SCOPES = [
        "https://www.googleapis.com/auth/spreadsheets",
        "https://www.googleapis.com/auth/drive"
    ]

    creds = Credentials.from_service_account_info(
        gspread_creds, 
        scopes=SCOPES
    )

    gc = gspread.authorize(creds)
    drive_service = build("drive", "v3", credentials=creds)

    FOLDER_ID = "1xFJaIVUWpan-BPIVND4Y2i0dqBYyyEtq"

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

    f_name = f'Core Artist Targets - week ending {most_recent_thursday.strftime("%m/%d/%y")}'

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

    # READ/ORGANIZE DATA

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

    df = orcd_query("""
    select * from AWAL.AWAL_AR.CORE_ARTIST_TARGETS order by avg_weekly_streams_full desc
    """,sf_params)
    
    df['EARLIEST_RELEASE_DATE'] = pd.to_datetime(df['EARLIEST_RELEASE_DATE'])
    df['LATEST_RELEASE_DATE'] = pd.to_datetime(df['LATEST_RELEASE_DATE'])
    df['WEEK_ENDING'] = pd.to_datetime(df['WEEK_ENDING'])

    # replace problematic characters
    df = df.replace(r'\+', '', regex=True)
    df['ARTIST_NAME'] = df['ARTIST_NAME'].str.replace('"', "''", regex=False)

    # convert dates to strings
    df["EARLIEST_RELEASE_DATE"] = df["EARLIEST_RELEASE_DATE"].dt.strftime("%Y-%m-%d").fillna("")
    df["LATEST_RELEASE_DATE"] = df["LATEST_RELEASE_DATE"].dt.strftime("%Y-%m-%d").fillna("")
    df["WEEK_ENDING"] = df["WEEK_ENDING"].dt.strftime("%Y-%m-%d").fillna("")

    df = df.fillna('N/A')

    # Luminate link
    df['ARTIST_NAME'] = '=HYPERLINK("' + 'https://app.luminatedata.com/artist/' + df['ARTIST_ID'] + '?g=AA&m=VVNNLy9OQVRJT05BTA%3D%3D&d=YTD&dct=NONE&a=ST&b=CM&stf=Q018U0UsSU5ULFBDfENOLFZJfENDfC8%3D&ssf=Lw%3D%3D&psf=Lw%3D%3D&stgl=R0xCLVBWL1NFfEJTLENOfEJTLENNfEJT&psgl=R0xCLVNULw%3D%3D", "' + df['ARTIST_NAME'] + '")'
    df = df.drop(columns='ARTIST_ID')

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

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

    logger.info('Creating spreadsheet...')

    spreadsheet = gc.create(f_name)
    sheet_id = spreadsheet.id

    # Move file into folder

    # Retrieve current parents
    file = drive_service.files().get(
        fileId=sheet_id,
        fields="parents"
    ).execute()
    
    previous_parents = ",".join(file.get("parents"))
    
    drive_service.files().update(
        fileId=sheet_id,
        addParents=FOLDER_ID,
        removeParents=previous_parents,
        fields="id, parents"
    ).execute()

    sh = gc.open_by_key(sheet_id)

    logger.info('Creating new tab...')
    worksheet = sh.add_worksheet(title='Artists', rows=f"{len(df)+3}", cols="15")

    data = [df.columns.tolist()] + df.values.tolist()    
    worksheet.update(f'A1', data, value_input_option="USER_ENTERED")

    BATCH_LIMIT = 50000

    logger.info('Formatting data columns...')

    requests = []
    requests_sh = []

    headers = worksheet.row_values(1)

    num_comma_cols = [i for i, col in enumerate(headers) if col.startswith(('AVG_WEEKLY'))]
    
    # Apply number 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}1:{col_letter}"  
        
        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": 120
                },
                "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})

    

    ##

    # Modify headers

    updated_headers = [
        "ARTIST" if header.endswith("ARTIST_NAME") else
        "COUNTRY" if header.endswith("COUNTRY_OF_ORIGIN") else
        "EARLIEST\nRELEASE" if header.endswith("EARLIEST_RELEASE_DATE") else
        "LATEST\nRELEASE" if header.endswith("LATEST_RELEASE_DATE") else
        "LATEST DISTRO" if header.endswith("LATEST_DISTRO") else
        "DISTRO LIST" if header.endswith("DISTRO_LIST") else
        "AVAILABLE\nSONGS" if header.endswith("AVAILABLE_SONG_COUNT") else
        "AVG WKLY\nSTREAMS FL" if header.endswith("AVG_WEEKLY_STREAMS_FRONTLINE") else
        "AVG WKLY\nSTREAMS ALL" if header.endswith("AVG_WEEKLY_STREAMS_FULL") else
        "CATALOG\nEST. AGE" if header.endswith("WEIGHTED_CATALOG_AGE") else
        "WEEK\nENDING" if header.endswith("WEEK_ENDING") else
        header
        for header in headers
    ]

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

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

    headers = worksheet.row_values(1)

    requests = []
    requests_sh = []
        
    col_indices = [i for i, col in enumerate(headers)]

    for col_index in col_indices:
        # bold and center align.
        requests_sh.append({
            "repeatCell": {
                "range": {
                    "sheetId": worksheet._properties['sheetId'],
                    "startRowIndex": 0,
                    "endRowIndex": 1,
                    "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(('ARTIST','LATEST DISTRO','DISTRO LIST'))]

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

    # remove underline in whole sheet

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

    rows = len(df) + 5
    end_col_letter = col_num_to_letter(week_ending_col[0])
    col_range = f"A1:{end_col_letter}{rows}"
    requests.append((col_range, CellFormat(textFormat=TextFormat(underline=False))))

    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)

    logger.info('Final formatting...')

    # Freeze rows/columns
    freeze_request = {
        "requests": [{
            "updateSheetProperties": {
                "properties": {
                    "sheetId": worksheet._properties['sheetId'],
                    "gridProperties": {
                        "frozenRowCount": 1,
                        "frozenColumnCount": 0
                    }
                },
                "fields": "gridProperties.frozenRowCount,gridProperties.frozenColumnCount"
            }
        }]
    }
    sh.batch_update(freeze_request)

    # Add a filter to all columns in header

    n_columns = len(df.columns)

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

    logger.info('Artist sheet complete')
    

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

    # READ/ORGANIZE DATA

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

    logger.info('Starting song list sheet...')

    df = orcd_query("""
    select
        ARTIST_ID,
        ARTIST_NAME,
        SONG_ID,
        TITLE,
        RELEASE_DATE,
        LABEL,
        DISTRIBUTOR,
        AVG_WEEKLY_STREAMS,
        WEEK_ENDING
    from AWAL.AWAL_AR.CORE_ARTIST_TARGETS_SONG_LIST 
    order by artist_name asc,AVG_WEEKLY_STREAMS desc
    """,sf_params)

    df['WEEK_ENDING'] = pd.to_datetime(df['WEEK_ENDING'])
    df['RELEASE_DATE'] = pd.to_datetime(df['RELEASE_DATE'])

    # replace problematic characters
    df = df.replace(r'\+', '', regex=True)
    df['ARTIST_NAME'] = df['ARTIST_NAME'].str.replace('"', "''", regex=False)
    df['TITLE'] = df['TITLE'].str.replace('"', "''", regex=False)

    # convert dates to strings
    df["WEEK_ENDING"] = df["WEEK_ENDING"].dt.strftime("%Y-%m-%d").fillna("")
    df["RELEASE_DATE"] = df["RELEASE_DATE"].dt.strftime("%Y-%m-%d").fillna("")

    df = df.fillna('N/A')

    # Luminate link
    df['ARTIST_NAME'] = '=HYPERLINK("' + 'https://app.luminatedata.com/artist/' + df['ARTIST_ID'] + '?g=AA&m=VVNNLy9OQVRJT05BTA%3D%3D&d=YTD&dct=NONE&a=ST&b=CM&stf=Q018U0UsSU5ULFBDfENOLFZJfENDfC8%3D&ssf=Lw%3D%3D&psf=Lw%3D%3D&stgl=R0xCLVBWL1NFfEJTLENOfEJTLENNfEJT&psgl=R0xCLVNULw%3D%3D", "' + df['ARTIST_NAME'] + '")'
    df['TITLE'] = '=HYPERLINK("' + 'https://app.luminatedata.com/song/' + df['SONG_ID'] + '?g=AA&d=YTD&stf=Q018U0UsSU5ULFBDfENOLFZJfENDfC8%3D&ssf=Lw%3D%3D&psf=Lw%3D%3D&a=ST&b=CM&sd=2026-01-02&ed=2026-06-20&stgl=R0xCLVBWL1NFfEJTLENOfEJTLENNfEJT&psgl=R0xCLVNULw%3D%3D&ga=&dct=NONE&acf=L0FV&m=VVNNLy9OQVRJT05BTA%3D%3D", "' + df['TITLE'] + '")'
    
    df = df.drop(columns=['ARTIST_ID','SONG_ID'])

    logger.info('Creating new tab...')
    worksheet = sh.add_worksheet(title='Song List', rows=f"{len(df)+3}", cols="10")


    logger.info('Uploading data as chunks...') 

    chunk_size = 10000

    # Header
    worksheet.update(
        "A1",
        [df.columns.tolist()],
        value_input_option="USER_ENTERED"
    )

    for start in range(0, len(df), chunk_size):
        chunk = df.iloc[start:start + chunk_size].values.tolist()

        cell = rowcol_to_a1(start + 2, 1)

        worksheet.update(
            cell,
            chunk,
            value_input_option="USER_ENTERED"
        )

        logger.info(f"Uploaded {min(start + chunk_size, len(df)):,} rows")

    

    logger.info('Formatting data columns...')

    requests = []
    requests_sh = []

    headers = worksheet.row_values(1)

    num_comma_cols = [i for i, col in enumerate(headers) if col.startswith(('AVG_WEEKLY'))]
    
    # Apply number 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}1:{col_letter}"  
        
        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": 100
                },
                "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})

    

    ##

    # Modify headers

    updated_headers = [
        "ARTIST" if header.endswith("ARTIST_NAME") else
        "AVG WKLY\nSTREAMS" if header.endswith("AVG_WEEKLY_STREAMS") else
        "RELEASE\nDATE" if header.endswith("DATE") else
        "WEEK\nENDING" if header.endswith("WEEK_ENDING") else
        header
        for header in headers
    ]

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

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

    headers = worksheet.row_values(1)

    requests = []
    requests_sh = []
        
    col_indices = [i for i, col in enumerate(headers)]

    for col_index in col_indices:
        # bold and center align.
        requests_sh.append({
            "repeatCell": {
                "range": {
                    "sheetId": worksheet._properties['sheetId'],
                    "startRowIndex": 0,
                    "endRowIndex": 1,
                    "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(('ARTIST','DISTRIBUTOR','LABEL','TITLE'))]

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

    # remove underline in whole sheet

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

    rows = len(df) + 5
    end_col_letter = col_num_to_letter(week_ending_col[0])
    col_range = f"A1:{end_col_letter}{rows}"
    requests.append((col_range, CellFormat(textFormat=TextFormat(underline=False))))

    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)

    logger.info('Final formatting...')

    # Freeze rows/columns
    freeze_request = {
        "requests": [{
            "updateSheetProperties": {
                "properties": {
                    "sheetId": worksheet._properties['sheetId'],
                    "gridProperties": {
                        "frozenRowCount": 1,
                        "frozenColumnCount": 0
                    }
                },
                "fields": "gridProperties.frozenRowCount,gridProperties.frozenColumnCount"
            }
        }]
    }
    sh.batch_update(freeze_request)

    # Add a filter to all columns in header

    n_columns = len(df.columns)

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

    logger.info('Song list sheet complete')


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

    logger.info('Finished')

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