"""Get track and release information required by hfa."""
HFA_ELIGIBLE_TRACKS = """
SELECT
    result_tracks.track_id,
    result_tracks.track_isrc,
    result_tracks.upc,
    result_tracks.release_id,
    result_tracks.vendor_id,
    dfh.hfa_configuration_code,
    result_tracks.trans_type
FROM
    distribution_format_hfa dfh
    LEFT JOIN (
        SELECT
            t.id AS track_id,
            t.isrc AS track_isrc,
            r.upc AS upc,
            r.release_id AS release_id,
            v.vendor_id,
            IF(ISNULL(pp.release_id), 'digital', 'physical') AS trans_type
        FROM
            releases r
            LEFT JOIN product_physical pp ON r.release_id = pp.release_id
            INNER JOIN artist_info ai ON ai.artist_id = r.artist_id
            INNER JOIN vendor v ON v.vendor_id = ai.vendor_id
            INNER JOIN track t ON t.release_id = r.release_id
        WHERE
            r.ingestion_completed >= DATE(SUBDATE(NOW(), 1))
            AND r.ingestion_completed < DATE(SUBDATE(NOW(), 0))
            AND v.status = 'signed'
            AND r.release_status = 'in_content'
            AND t.third_party_publisher = 'Y'
            AND r.ingestion_completed IS NOT NULL
        GROUP BY
            t.track_name,
            t.id,
            r.release_name,
            v.status,
            r.release_status,
            t.third_party_publisher,
            t.mechanical,
            r.ingestion_completed,
            v.vendor_id,
            r.subaccount_id
        ORDER BY
            t.track_name,
            t.id,
            r.release_name,
            v.status,
            r.release_status,
            t.third_party_publisher,
            t.mechanical,
            r.ingestion_completed,
            v.vendor_id,
            r.subaccount_id
    ) result_tracks ON result_tracks.trans_type = dfh.context_type
WHERE track_id IS NOT NULL
UNION ALL
(
    SELECT
        t.id AS track_id,
        t.isrc AS track_isrc,
        r.upc AS upc,
        r.`release_id` AS release_id,
        v.vendor_id,
        'RT' AS hfa_configuration_code,
        'digital' AS trans_type
    FROM
        releases r
        INNER JOIN artist_info ai ON ai.artist_id = r.artist_id
        INNER JOIN vendor v ON v.vendor_id = ai.vendor_id
        INNER JOIN track t ON t.release_id = r.release_id
        INNER JOIN track_clips tc ON tc.cd = t.cd
        AND tc.upc = t.upc
        AND tc.track_id = t.track_id
    WHERE
        tc.date_created >= DATE(SUBDATE(NOW(), 1))
        AND tc.date_created < DATE(SUBDATE(NOW(), 0))
        AND v.status = 'signed'
        AND r.release_status = 'in_content'
        AND t.third_party_publisher = 'Y'
        AND r.ingestion_completed IS NOT NULL
    GROUP BY
        t.track_name,
        t.id,
        r.release_name,
        v.status,
        r.release_status,
        t.third_party_publisher,
        t.mechanical,
        r.ingestion_completed,
        v.vendor_id,
        r.subaccount_id
    ORDER BY
        t.track_name,
        t.id,
        r.release_name,
        v.status,
        r.release_status,
        t.third_party_publisher,
        t.mechanical,
        r.ingestion_completed,
        v.vendor_id,
        r.subaccount_id
)
"""