#! /usr/bin/env python

"""Dump spotify tracklists data to history table."""


import argparse
from datetime import datetime

from garcon_contrib.snowflake import garcon_snowflake

from snowflake_etl.conf.config import SF_CONFIG


def main():
    """Main."""

    parser = argparse.ArgumentParser(
        description='fact_analytics sanity check')

    parser.add_argument(
        '-d', '--snapshot-timestamp', help=(
            'All rows will be tagged by this timestamp on '
            'insertion to history table'))
    args = parser.parse_args()

    snapshot_timestamp = args.snapshot_timestamp or datetime.today().strftime(
        '%Y-%m-%d %H:%M:%S')
    print('Snapshoting tracklists at: %s' % snapshot_timestamp)

    result = garcon_snowflake.execute_with_py_conn("""
        INSERT INTO {db}.{schema}.ORCHTBLSPOTIFYPLAYLISTTRACKLISTHISTORY (
            // the latest snapshot that we've dumped to the history table.
            // it's used in FULL OUTER JOIN with the current tracklists state.
            // if the current version of a playlist is missing a track but the
            // track is found on the latest snapshot we'll add such rows to the
            // history table marking them with -1 position (deleted)
            WITH latest_snapshot AS (
                SELECT *,
                       ROW_NUMBER() OVER (
                          PARTITION BY playlistid, trackid
                          ORDER BY playlistindex NULLS LAST) AS rn
                FROM ORCHTBLSPOTIFYPLAYLISTTRACKLISTHISTORY
                WHERE snapshot_timestamp = (SELECT MAX(snapshot_timestamp)
                FROM ORCHTBLSPOTIFYPLAYLISTTRACKLISTHISTORY)
                      AND playlistindex <> -1  // do not count deleted tracks
            ), current_snapshot AS (
                SELECT
                    lspl.added_at AS added,
                    lspl.start_sys_period AS earliestadded,
                    spl.playlist_id AS playlistid,
                    lspl.position_latest AS playlistindex,
                    TO_TIMESTAMP_NTZ(lspl.modified_at) AS timestamp,
                    sp.spotify_track_id AS trackid,
                    // if a track appears on a playlist multiple times we give
                    // every occurrence a number so that further we can use
                    // this number when joinomg predicate and track situations
                    // when only some occurrences were removed (and the other
                    // remain)
                    ROW_NUMBER() OVER (
                        PARTITION BY playlistid, trackid
                        ORDER BY playlistindex NULLS LAST) AS rn
                FROM chartmetric.raw_data.l_spotify_playlist lspl
                LEFT JOIN chartmetric.raw_data.spotify_playlist spl
                    ON lspl.spotify_playlist = spl.id
                LEFT JOIN chartmetric.raw_data.spotify sp
                    ON lspl.spotify = sp.id
            )
            SELECT '{snapshot_timestamp}' AS SNAPSHOT_TIMESTAMP,
               COALESCE(tl.added,         hist.added)         AS added,
               COALESCE(tl.earliestadded, hist.earliestadded) AS earliestadded,
               COALESCE(tl.playlistid,    hist.playlistid)    AS playlistid,
               // mark with -1 if deleted
               COALESCE(tl.playlistindex, -1)                 AS playlistindex,
               COALESCE(tl.timestamp,     hist.fetch_timestamp) AS timestamp,
               COALESCE(tl.trackid,       hist.trackid)       AS trackid,
               COALESCE(tl.rn,            hist.rn)       AS rel_playlistindex
            FROM current_snapshot tl
            FULL OUTER JOIN latest_snapshot hist
                ON tl.playlistid = hist.playlistid
                    AND tl.trackid = hist.trackid
                    AND tl.rn = hist.rn // the same track may get added multiple times to the same playlist
            WHERE (tl.trackid IS NOT NULL AND hist.trackid IS NOT NULL)
            AND (tl.playlistid IS NOT NULL AND hist.playlistid IS NOT NULL)
        );
        """.format(
            db=SF_CONFIG['db'],
            schema=SF_CONFIG['schema'],
        snapshot_timestamp=snapshot_timestamp), garcon_snowflake.FetchEnum.ALL,
        sf_config=SF_CONFIG)['results']

    (rows_inserted, *_), *_ = result
    print('Rows inserted: %s' % rows_inserted)


if __name__ == '__main__':
    main()