from bulkperformancerights.models import track


def test_tracks_owned_by_user(db_fixture):

    assert [1] == track.tracks_owned_by_user(db_fixture, 'vendor', 1)
    assert [2] == track.tracks_owned_by_user(db_fixture, 'subaccount', 1)


def test_track_ownership_sql():
    expected_vendor_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
            WHERE
                ai.vendor_id = 1
                '''
    vendor_sql = track.track_ownership_sql('vendor', 1)
    assert expected_vendor_sql == vendor_sql
    expected_subaccount_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
                    INNER JOIN
                subaccount s ON s.vendor_id = ai.vendor_id
            WHERE
                s.subaccount_id = 2
                '''
    subaccount_sql = track.track_ownership_sql('subaccount', 2)
    assert expected_subaccount_sql == subaccount_sql


def test_subaccount_tracks_for_template_sql():
    expected_subaccount_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 r.subaccount_id = 10
        GROUP BY t.id
        ORDER BY r.release_name, t.cd, t.track_id
        '''
    subaccount_sql = track.tracks_for_template_sql('subaccount', 10)
    assert subaccount_sql == expected_subaccount_sql


def test_vendor_tracks_for_template_sql():
    expected_vendor_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 ai.vendor_id = 20
        GROUP BY t.id
        ORDER BY r.release_name, t.cd, t.track_id
        '''
    vendor_sql = track.tracks_for_template_sql('vendor', 20)
    assert vendor_sql == expected_vendor_sql