from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import String
from bulkperformancerights.connectors import mysql


class Track(mysql.ArtRelationsBaseModel):
    __tablename__ = 'track'
    id = Column(Integer, primary_key=True)
    name = Column(String(80), unique=True)
    release_id = Column(Integer)
    recording_country = Column(Integer)


def tracks_owned_by_user(session, user_type, user_id):
    """Track ids represented as list

    Track 'ownership' is a relationship between the following tables
    in art_relations:

    track -> release -> artist_info -> vendor -> vend_contact

    Conditional Cache decorator is used to allow environment specific
    toggling of whether the cache(memcache at this time) is enabled

    Args:
        session(Session): sqlalchemy session connected to art_relations
        (str): user_type
        (int): user_id
    Return:
        (list): track ids owned by the specified user
    """
    sql = track_ownership_sql(user_type, user_id)
    user_owned_tracks = session.execute(sql)
    return [c for (c, ) in user_owned_tracks]


def track_ownership_sql(user_type, user_id):
    """SQL to get tracks owned by user

    Args:
        (str): user_type
        (int): user_id
    Return:
        (str): SQL for either subaccount or vendor user's tracks
    """

    sql = '''
            SELECT
                t.id
            FROM
                track t
                    INNER JOIN
                releases r ON r.release_id = t.release_id
                    INNER JOIN
                artist_info ai ON ai.artist_id = r.artist_id'''
    if user_type == 'subaccount':
        sql = sql + '''
                    INNER JOIN
                subaccount s ON s.vendor_id = ai.vendor_id
            WHERE
                s.subaccount_id = {user_id}
                '''
    else:
        sql = sql + '''
            WHERE
                ai.vendor_id = {user_id}
                '''
    return sql.format(user_id=user_id)


def tracks_for_template(session, user_type, user_id):
    """
    Args:
        (str): user_type
        (int): user_id
    Return:
        (ResultProxy): result of the select operation
    """

    sql = tracks_for_template_sql(user_type, user_id)
    return session.execute(sql)


def tracks_for_template_sql(user_type, user_id):
    """SQL for tracks to generate user input sheet

    Args:
        (str): user_type
        (int): user_id
    Return:
        (str): SQL for either subaccount or vendor user's tracks
    """

    sql = '''
        SELECT
            r.release_name AS 'Release Name',
            ai.name AS 'Release Artist',
            r.display_upc AS UPC,
            IFNULL(s.subaccount_name, ' ') AS 'Subaccount Name',
            IFNULL(r.label, ' ') AS 'Imprint',
            t.track_name AS 'Song Title',
            IFNULL(
                GROUP_CONCAT(
                    DISTINCT(ta.name)
                    ORDER BY ta.track_id SEPARATOR '|')
                , ai.name) AS 'Track Artist',
            t.isrc AS 'ISRC',
            t.cd AS 'Volume',
            t.track_id AS 'Track #',
            r.vendor_catalog_number AS 'Vendor Catalog #',
            t.p_line AS '(P) Line',
            t.track_type AS 'Track Type',
            CASE
              WHEN tmr.is_owner is null then ''
              WHEN tmr.is_owner = 1 then 'Yes'
              WHEN tmr.is_owner <> 1 then 'No'
            END AS 'Master Rights',
            c1.name  AS 'Country of Recording',
            c2.name AS 'Nationality of 1st Producer',
            GROUP_CONCAT(
                DISTINCT(tw.writer_name)
                ORDER BY tw.unique_track_id, tw.track_writer_id SEPARATOR
                    '|') AS 'Songwriter(s)',
            t.id AS 'Orchard Track Unique ID'
        FROM track t
        INNER JOIN releases r ON r.`release_id` = t.`release_id`
        INNER JOIN artist_info ai ON ai.`artist_id` = r.`artist_id`
        LEFT JOIN subaccount s on s.subaccount_id = r.subaccount_id
        LEFT JOIN track_artist ta ON ta.`track_id` = t.`id`
        LEFT JOIN track_producer_nationality tpn ON tpn.track_id = t.id
        LEFT JOIN track_writer tw ON tw.`unique_track_id` = t.`id`
        LEFT JOIN track_master_rights tmr ON tmr.track_id = t.id
        LEFT JOIN country c1 ON c1.id = t.recording_country
        LEFT JOIN country c2 ON c2.id = tpn.nationality_country_id
        LEFT JOIN youtube_channel_upc youtube ON youtube.`upc` = r.`upc`
        WHERE 1
        AND youtube.`youtube_channel_upc_id` IS NULL
        AND r.deletions = 'N'
        AND (tpn.`id` IS NULL
            OR t.recording_country IS NULL
            OR tw.writer_name IS NULL
            OR tmr.is_owner IS NULL
            OR TRIM(tmr.is_owner) = '')
        AND {ownership_clause}
        GROUP BY t.id
        ORDER BY r.release_name, t.cd, t.track_id
        '''
    if user_type == 'subaccount':
        ownership_clause = 'r.subaccount_id = {}'.format(
            user_id, user_id)
    else:
        ownership_clause = 'ai.vendor_id = {}'.format(
            user_id, user_id)
    return sql.format(ownership_clause=ownership_clause)