from loguru import logger
import pandas as pd
import numpy as np
from jinja2 import Template
from datetime import date, timedelta

from djagitit import db
from djagitit.api import spotify

import config
import utils


def get_raw_data(mode):
    rdb = db.ReportingDB()

    if mode=='daily':
        old_date = (date.today()-timedelta(days=1)).strftime('%Y-%m-%d')
    elif mode in ['weekly', 'weekend']:
        offset = (date.today().weekday() - 4) % 7 or 7
        old_date = (date.today()-timedelta(days=offset)).strftime('%Y-%m-%d')
    else:
        raise ValueError(f"Invalid mode: {mode}")

    tracks_df = rdb.query(
        Template(open('../sql/new_tracks.sql').read()).render(old_date=old_date)
    )
    if not isinstance(tracks_df, pd.DataFrame):
        logger.error("Failed to query new_tracks")
        raise ValueError("Failed to query new_tracks")
    
    playlists_df = rdb.query(
        Template(open('../sql/playlists.sql').read()).render()
    )
    if not isinstance(playlists_df, pd.DataFrame):
        logger.error("Failed to query playlists")
        raise ValueError("Failed to query playlists")

    distro_df = rdb.query(
        Template(open('../sql/distro.sql').read()).render()
    )
    if not isinstance(distro_df, pd.DataFrame):
        logger.error("Failed to query distro")
        raise ValueError("Failed to query distro")

    return tracks_df, playlists_df, distro_df


def get_spotify_metadata(tracks_df):
    sp = spotify.connect_spotify()
    df = pd.DataFrame()

    for isrc in tracks_df['isrc'].drop_duplicates():
        try:
            track = sp.search(q=f'isrc:{isrc}', type='track', limit=1)
            items = track.get('tracks', {}).get('items', [])
            track_id = items[0].get('id')
            release_date = items[0].get('album', {}).get('release_date')
            album_id = items[0].get('album', {}).get('id')
            album = sp.album(album_id)
            label = album.get('label')
            copyrights = album.get('copyrights', [])
            copyright = None
            for c in copyrights:
                if c.get('type') == 'P':
                    copyright = c.get('text')
                    break

            tmp_df = pd.DataFrame(
                {
                    'isrc': isrc,
                    'track_id': track_id,
                    'release_date': release_date,
                    'label': label,
                    'copyright': copyright
                },
                index=[0]
            )
            df = pd.concat([df, tmp_df])
        except Exception as e:
            logger.error(f"Failed to fetch Spotify metadata for ISRC {isrc}: {e}")

    return df


def split_playlists_df(playlists_df, tracks_df):

    try:
        updated_playlists_df = playlists_df[
            playlists_df['playlist_id'].isin(tracks_df['playlist_id'])
            ]
    except Exception as e:
        logger.error(f"Failed to build updated_playlists_df dataframe: {e}")
        raise ValueError("Failed to build updated_playlists_df dataframe")
    
    try:
        no_news_df = playlists_df[
            ~playlists_df['playlist_id'].isin(tracks_df['playlist_id'])
            ]
    except Exception as e:
        logger.error(f"Failed to build no_news_df dataframe: {e}")
        raise ValueError("Failed to build no_news_df dataframe")
    
    return updated_playlists_df, no_news_df


def enrich_tracks_df(tracks_df, spotify_metadata_df):

    try:
        check_dict = config.Params.distributors_dict
        df = pd.merge(tracks_df, spotify_metadata_df, on='isrc', how='left')

        df['signed'] = (
            df[['copyright', 'label', 'distributor']]
            .fillna('')
            .apply(lambda row: any(
                any(word in str(row[col]).lower() for word in check_dict)
                for col in ['copyright', 'label', 'distributor']
            ), axis=1)
        )

        df['img64'] = df.apply(
            lambda x: utils.generate_img64_from_html(
                Template(open('../html/track.html')
                    .read()
                    ).render(track = x.to_dict()),
            ), axis=1)

        df.sort_values(['position'], ascending=True, inplace=True)

    except Exception as e:
        logger.error(f"Failed to enrich tracks dataframe: {e}")
        raise ValueError("Failed to enrich tracks dataframe")

    return df