from connectors import snowflakedb
from snowflake.connector import DictCursor

import config
import json
import os
import pycurl


def handler():
    """
        Get all blank and track duration mismatch records
    """

    conn = snowflakedb.get_snowflake_context()
    cursor = conn.cursor(DictCursor)
    query = ("""
        SELECT r.upc, t.track_id, t.cd
        FROM ART_RELATIONS.RELEASES r
        INNER JOIN ART_RELATIONS.ARTIST_INFO ai ON ai.artist_id = r.artist_id
        AND r.not_for_distribution = 'N'
        INNER JOIN ART_RELATIONS.VENDOR v ON v.vendor_id = ai.vendor_id
        INNER JOIN ART_RELATIONS.TRACK t ON t.upc = r.upc
        INNER JOIN (
            DIRECT_DELIVERY.ASSET a
            INNER JOIN DIRECT_DELIVERY.ASSET_LOCATION aloc
                ON aloc.asset_id = a.asset_id
            INNER JOIN DIRECT_DELIVERY.ASSET_LOCATION_DETAIL ald
                ON ald.asset_location_id = aloc.asset_location_id
            INNER JOIN DIRECT_DELIVERY.STORAGE_DRIVE sd
                ON sd.storage_drive_id = aloc.storage_drive_id
            INNER JOIN DIRECT_DELIVERY.STORAGE s
                ON s.storage_id = sd.storage_id AND s.physical_location_id = 1
        ) ON a.upc = r.upc
            AND ald.filename =
                CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(t.upc,'_'),t.cd),'_'),t.track_id),'.wav')
            AND a.asset_type_id = 1
        WHERE
            (
                (
                    COALESCE(t.length_minute, 0) * 60
                ) + COALESCE(t.length_seconds, 0) = 0
                OR COALESCE(ald.duration, 0) = 0
                OR
                (
                    ABS(
                        (
                            (
                                COALESCE(t.length_minute, 0) * 60
                            ) + COALESCE(t.length_seconds, 0)
                        ) - COALESCE(ald.duration, 0)
                    ) > {}
                )
            )
            AND r.release_status = 'in_content'
            AND r.deletions <> 'Y'
            AND v.vendor_id NOT IN (7123, 25824,16055)
            AND t.track_type = 'music'
            AND YEAR(r.release_date) = YEAR(CURRENT_DATE())
        GROUP BY r.upc, r.release_status, v.owner, v.vendor_id,
            t.length_minute,t.length_seconds, ald.duration, t.track_id, t.cd
        ORDER BY r.upc
    """).format(config.ACCEPTED_DIFFERENCE)

    try:
        cursor.execute("USE warehouse {} ".format(os.getenv('SNOWFLAKE_WAREHOUSE')))

        cursor.execute(query)
        message = ''
        for row in cursor:
            message += "Duration for track number: {}, cd: {} and upc: {} " \
                       "is not matching \r\n"\
                .format(row['TRACK_ID'], row['CD'], row['UPC'])

        data = {
            "channel": os.getenv('CHANNEL_NAME'),
            "username": "jenkins",
            "attachments": [
                {
                    "color": "#DC143C",
                    "pretext": "Track duration mismatch",
                    "title": "Notes",
                    "text": message
                }
            ]
        }
        send_message(data)
    finally:
        cursor.close()


def send_message(message):
    """
        Sends an alert message of track duration
        mismatch to given slack channel
    """
    slack_url = os.getenv('SLACK_URL')
    slack_payload = json.dumps(message)
    curl = pycurl.Curl()
    curl.setopt(curl.HTTPHEADER, ['Content-Type: application/json'])
    curl.setopt(curl.URL, slack_url)
    curl.setopt(curl.POSTFIELDS, slack_payload)
    curl.setopt(curl.VERBOSE, True)
    curl.perform()


if __name__ == '__main__':
    handler()