from snowflake.connector import SnowflakeConnection

from snowflake_client import snowflake_client


def playlist_positions_over_time(args):
    isrc = args.get('isrc')
    playlist_id = args.get('playlist_id')
    client = args.get('sf_client')

    results = client.cursor().execute(
        f"select * from DEV_ENGINEERING.BWINTERFLOOD.playlist_placement_history where isrc = '{isrc}' and playlist_id = '{playlist_id}';")

    return results.fetchall()


def current_placements_in_playlist(args):
    playlist_id = args.get('playlist_id')
    client = args.get('sf_client')

    results = client.cursor().execute(
        f"select * from dev_engineering.bwinterflood.playlist_placements_current_global where playlist_id = '{playlist_id}';")

    return results.fetchall()

# with streams


def playlist_positions_over_time_with_streams(args):
    isrc = args.get('isrc')
    playlist_id = args.get('playlist_id')
    client = args.get('sf_client')

    playlist_positions = client.cursor().execute(
        f"select * from DEV_ENGINEERING.BWINTERFLOOD.playlist_placement_history where isrc = '{isrc}' and playlist_id = '{playlist_id}';")
    streaming = client.cursor().execute(
        f"select * from FACTS.PROD.STREAMS_BY_TRACK_PLAYLIST_COUNTRY_FEED_DISTRIBUTOR_DAILY where isrc = '{isrc}' and STORE_PLAYLIST_ID = '{playlist_id}';")
    return playlist_positions.fetchall(), streaming.fetchall()


def current_placements_in_playlist_with_streams(args):
    playlist_id = args.get('playlist_id')
    client = args.get('sf_client')

    results = client.cursor().execute(
        f"select h.playlist_id, streams_7_days, h.isrc, h.position, country_code from DEV_ENGINEERING.BWINTERFLOOD.PLAYLIST_PLACEMENTS_CURRENT_GLOBAL h join FACTS.PROD.STREAMS_BY_TRACK_PLAYLIST_COUNTRY_FEED_DISTRIBUTOR_ROLLUP s on s.STORE_PLAYLIST_ID = h.playlist_id and s.isrc = h.isrc where s.STORE_PLAYLIST_ID = '{playlist_id}'"
    )

    return results.fetchall()


def current_placements_by_artist(args, client: SnowflakeConnection):
    global_participant_id = args.get('global_participant_id')
    if not client:
        client = args.get('sf_client')

    results = client.cursor().execute(
        f"select * from dev_engineering.bwinterflood.playlist_placements_current_global where global_participant_id = '{global_participant_id}';")

    return results.fetchall()


def current_placements_given_isrc(args):
    client = args.get('sf_client')
    isrc = args.get('isrc')

    results = client.cursor().execute(
        f"""
        SELECT * FROM (SELECT
            playlist_id,
            isrc,
            position,
            timestamp,
            ROW_NUMBER() OVER (PARTITION BY playlist_id, position ORDER BY timestamp DESC) AS row_num
            FROM dev_engineering.bwinterflood.playlist_placements_current_global) AS x
        WHERE x.row_num = 1 AND x.isrc = '{isrc}';
        """
    )
    placements_result = results.fetchall()

    playlist_ids = [result[0] for result in placements_result]

    playlist_ids_str = ','.join(['\'{}\''.format(pid) for pid in playlist_ids])

    results = client.cursor().execute(
        f"""
    SELECT store_playlist_id, SUM(streams) as total_streams
    FROM FACTS.PROD.STREAMS_BY_TRACK_PLAYLIST_COUNTRY_FEED_DISTRIBUTOR_DAILY
    WHERE store_playlist_id IN ({playlist_ids_str}) AND isrc = '{isrc}'
    GROUP BY store_playlist_id;
    """
    )
    streams_result = results.fetchall()
    return placements_result, streams_result


if __name__ == '__main__':
    sf = snowflake_client()
    # from utils import time_n_calls
    # print(time_n_calls('snowflake', 'playlist_positions_over_time',
    #                    {'isrc': 'USUG11904206',
    #                     'playlist_id': '37i9dQZF1DXcBWIGoYBM5M'}, sf, n=10))

    # print(playlist_positions_over_time(
    #     {'isrc': 'USUG11904206', 'playlist_id': '37i9dQZF1DXcBWIGoYBM5M'}, sf))
    # print(current_placements_in_playlist(
    #     {'playlist_id': '37i9dQZF1DX10zKzsJ2jva'}, sf))

    # print(current_placements_by_artist(
    #     {'global_participant_id': 'f67b0892-f0bc-4575-b23e-59566ccb44bc'}, sf))

    # print(playlist_positions_over_time_with_streams(
    #     {'isrc': 'USUG11904206', 'playlist_id': '37i9dQZF1DXcBWIGoYBM5M', 'sf_client': sf}))
    # print(playlist_positions_over_time_with_streams(
    # {'isrc': 'QMBZ92051791', 'playlist_id': '37i9dQZF1DXcBWIGoYBM5M',
    # 'sf_client': sf}))
    # print(current_placements_in_playlist_with_streams(
    #     {'playlist_id': '37i9dQZF1DX10zKzsJ2jva', 'sf_client': sf}))
    current_placements_given_isrc({'isrc': 'QMFME2004132', 'sf_client': sf})