"""Helper class encapsulating SQL for apple_id_mapping table."""


class AppleIdMapping(object):
    """Generate insert and update queries on apple_id_mapping table."""

    def __init__(self, database_schema):
        """Initialize object with a database schema."""
        self.database_schema = database_schema

    def insert_new_entries(self):
        """Insert new entries in apple_id_mapping table.

        This only applies to iTunes workflow.
        """
        return """
          INSERT INTO {database_schema}.apple_id_mapping
          SELECT xx.*
          FROM (
               SELECT apple_id, '', '', '', '',
               UPPER(MAX(vendor_identifier)),
               UPPER(MAX(vendor_offer_code)), MIN(mp.album_or_track)
               FROM production.staging_raw_itunes i
                 INNER JOIN (
                     SELECT (
                        CASE WHEN (inputvalue='I' OR inputvalue='P')
                             THEN 'album'
                             ELSE 'track' END) AS album_or_track,
                             inputvalue
                     FROM map_transactiontype m
                     WHERE storeid = 1
               ) mp ON mp.inputvalue = i.product_type_identifier
               GROUP BY apple_id
          ) xx
          LEFT JOIN {database_schema}.apple_id_mapping a
              ON a.apple_id = xx.apple_id
          WHERE a.apple_id IS NULL""".format(
            database_schema=self.database_schema)

    def clean_blank_apple_release_id(self):
        """Update apple_release_id to be 0 instead of '' or NULL."""
        return """
            UPDATE {database_schema}.apple_id_mapping
            SET apple_release_id = 0
            WHERE apple_release_id = '' OR apple_release_id IS NULL
        """.format(
            database_schema=self.database_schema)

    def clean_blank_orchard_release_id(self):
        """Update orchard_release_id to be 0 instead of '' or NULL."""
        return """
            UPDATE {database_schema}.apple_id_mapping
            SET orchard_release_id = 0
            WHERE orchard_release_id = '' OR orchard_release_id IS NULL
        """.format(
            database_schema=self.database_schema)

    def clean_zero_apple_track_id(self):
        """Update apple_track_id to be '' instead of 0 or NULL."""
        return """
            UPDATE {database_schema}.apple_id_mapping
            SET apple_track_id = ''
            WHERE apple_track_id = 0 OR apple_track_id IS NULL
        """.format(
            database_schema=self.database_schema)

    def clean_zero_orchard_track_id(self):
        """Update orchard_track_id to be '' instead of 0 or NULL."""
        return """
            UPDATE {database_schema}.apple_id_mapping
            SET orchard_track_id = ''
            WHERE orchard_track_id = 0 OR orchard_track_id IS NULL
        """.format(
            database_schema=self.database_schema)

    def update_vendor_offer_code(self):
        """Update vendor_offer_code field in apple_id_mapping.

        (From the vendor_offer_code column in raw file).
        """
        return """
          UPDATE {database_schema}.apple_id_mapping
          SET vendor_offer_code = vendor_identifier
          WHERE (trim(vendor_offer_code) = '' OR vendor_offer_code IS NULL)
          AND (vendor_identifier is not null AND trim(vendor_identifier) <> '')
          AND {database_schema}.apple_id_mapping.album_or_track
          = 'track' """.format(
            database_schema=self.database_schema)

    def update_vendor_identifier(self):
        """Update vendor_identifier field in apple_id_mapping.

        (From the vendor_identifier column in raw file).
        """
        return """
          UPDATE {database_schema}.apple_id_mapping
          SET vendor_identifier = vendor_offer_code
          WHERE(vendor_offer_code <> ''
            AND vendor_offer_code is not null) AND (vendor_identifier
              IS NULL OR vendor_identifier = '')
            AND {database_schema}.apple_id_mapping.album_or_track
            = 'track' """.format(database_schema=self.database_schema)

    def update_apple_track_id(self, term):
        """Update apple_track_id column by joining mapping and dim tables.

        Update apple_track_id column in apple_id_mapping table by joining
        dim_track, ioda_track_mapping tables on all the terms in the '_'
        delimited vendor_offer_code and vendor_identifier column.
        """
        return """
          UPDATE {database_schema}.apple_id_mapping
          SET apple_track_id = (
                  CASE WHEN z.vendor_offer_code_isrc <> ''
                         THEN z.vendor_offer_code_isrc
                       WHEN z.vendor_offer_code_ioda_track_id <> ''
                         THEN z.vendor_offer_code_ioda_track_id
                       WHEN z.vendor_identifier_isrc <> ''
                         THEN z.vendor_identifier_isrc
                       WHEN z.vendor_identifier_ioda_track_id <> ''
                         THEN z.vendor_identifier_ioda_track_id
                  ELSE '' END)
          FROM (
            SELECT x.vendor_offer_code_isrc, y.vendor_offer_code_ioda_track_id,
              m.vendor_identifier_isrc, n.vendor_identifier_ioda_track_id,
              a.apple_id
            FROM {database_schema}.apple_id_mapping a
            LEFT JOIN (
                SELECT distinct isrc AS vendor_offer_code_isrc FROM dim_track
            ) x ON x.vendor_offer_code_isrc =
                split_part(a.vendor_offer_code,'_',{term})
            LEFT JOIN (
              SELECT distinct ioda_track_id AS vendor_offer_code_ioda_track_id
              FROM ioda_track_mapping
            ) y ON y.vendor_offer_code_ioda_track_id =
                split_part(a.vendor_offer_code,'_',{term})
            LEFT JOIN (
                SELECT distinct isrc AS vendor_identifier_isrc FROM dim_track
            ) m ON m.vendor_identifier_isrc =
                split_part(a.vendor_identifier,'_',{term})
            LEFT JOIN (
              SELECT distinct ioda_track_id AS vendor_identifier_ioda_track_id
              FROM ioda_track_mapping
            ) n ON n.vendor_identifier_ioda_track_id =
                split_part(a.vendor_identifier,'_',{term})
            WHERE (split_part(vendor_offer_code,'_',{term})!=''
                OR split_part(vendor_identifier,'_',{term})!='')
                AND album_or_track = 'track'
          ) z
          WHERE z.apple_id = {database_schema}.apple_id_mapping.apple_id
          AND {database_schema}.apple_id_mapping.apple_track_id = ''
          AND {database_schema}.apple_id_mapping.album_or_track
              = 'track' """.format(
            database_schema=self.database_schema, term=term)

    def update_apple_release_id(self, term):
        """Update apple_release_id column by joining mapping and dim tables.

        Update apple_release_id column in apple_id_mapping table by joining
        dim_release, ioda_release_mapping tables on all the terms in the '_'
        delimited vendor_offer_code and vendor_identifier column.
        """
        return """
          UPDATE {database_schema}.apple_id_mapping
          SET apple_release_id = CAST(z.final_release_id AS varchar)
          FROM (
              SELECT
              (CASE
                 WHEN CAST(x.vendor_offer_code_releaseid AS bigint) <> 0
                     THEN CAST(x.vendor_offer_code_releaseid AS bigint)
                 WHEN CAST(y.vendor_offer_code_ioda_release_id AS bigint) <> 0
                     THEN CAST(y.vendor_offer_code_ioda_release_id AS bigint)
                 WHEN CAST(m.vendor_identifier_releaseid AS bigint) <> 0
                     THEN CAST(m.vendor_identifier_releaseid AS bigint)
                 WHEN CAST(n.vendor_identifier_ioda_release_id AS bigint) <> 0
                     THEN CAST(n.vendor_identifier_ioda_release_id AS bigint)
                 ELSE 0 END) AS final_release_id,
              a.apple_id
              FROM {database_schema}.apple_id_mapping a
                LEFT JOIN (
                    SELECT distinct releaseid AS vendor_offer_code_releaseid
                    FROM dim_release
                ) x ON x.vendor_offer_code_releaseid =
                    split_part(a.vendor_offer_code,'_',{term})
                LEFT JOIN (
                  SELECT distinct ioda_release_id AS
                      vendor_offer_code_ioda_release_id
                  FROM ioda_release_mapping
                ) y ON y.vendor_offer_code_ioda_release_id =
                    split_part(a.vendor_offer_code,'_',{term})
                LEFT JOIN (
                    SELECT distinct releaseid AS vendor_identifier_releaseid
                    FROM dim_release
                ) m ON m.vendor_identifier_releaseid =
                    split_part(a.vendor_identifier,'_',{term})
                LEFT JOIN (
                  SELECT distinct ioda_release_id AS
                      vendor_identifier_ioda_release_id
                  FROM ioda_release_mapping
                ) n ON n.vendor_identifier_ioda_release_id =
                    split_part(a.vendor_identifier,'_',{term})
                WHERE (split_part(vendor_offer_code,'_',{term})!=''
                OR split_part(vendor_identifier,'_',{term})!='')
                AND album_or_track = 'track'
          ) z
          WHERE z.apple_id = {database_schema}.apple_id_mapping.apple_id
          AND {database_schema}.apple_id_mapping.apple_release_id = 0
          AND {database_schema}.apple_id_mapping.album_or_track
          = 'track'""".format(database_schema=self.database_schema, term=term)

    def update_apple_release_id_one_track(self):
        """Update apple_release_id by looking for tracks with non-shared isrc.

        Update apple_release_id by looking for the tracks whose isrc is not
        shared with other track. There is data which only have track id
        provided. Finding the upc is only possible if the isrc of these tracks
        is not shared by other tracks.
        """
        return """
          UPDATE {database_schema}.apple_id_mapping
          SET apple_release_id = yy.final_release_id
          FROM (
                SELECT (
                  CASE WHEN CAST(z.orchard_upc AS bigint) <> 0
                        THEN z.orchard_upc
                       WHEN CAST(zz.ioda_release_id AS bigint) <> 0
                        THEN zz.ioda_release_id
                       ELSE 0 END) AS final_release_id, a.apple_track_id
                FROM {database_schema}.apple_id_mapping a
                LEFT JOIN (
                    SELECT MIN(upc) AS orchard_upc, isrc AS orchard_isrc
                    FROM dim_track
                    WHERE (cd <> 0 AND track_id <> 0 AND track_unique_id <> 0)
                    GROUP BY isrc
                    HAVING COUNT(*) = 1
                ) z ON z.orchard_isrc = a.apple_track_id
                LEFT JOIN (
                    SELECT
                        MIN(ioda_release_id) AS ioda_release_id, ioda_track_id
                    FROM ioda_track_mapping
                    GROUP BY ioda_track_id
                    HAVING COUNT(*) = 1
                ) zz ON zz.ioda_track_id = a.apple_track_id
                WHERE a.apple_release_id = 0
          ) yy
          WHERE
            yy.apple_track_id =
                {database_schema}.apple_id_mapping.apple_track_id
           AND {database_schema}.apple_id_mapping.apple_release_id = 0
           AND {database_schema}.apple_id_mapping.album_or_track = 'track'
           """.format(database_schema=self.database_schema)

    def update_apple_release_id_from_upc(self):
        """Update apple_release_id from the upc provided by the raw file.

        Update apple_release_id from the upc provided by the raw file if
        the apple_release_id is still 0 after all the previous update. The
        reason for it to be still zero because vendor_identifer and
        vendor _offer_code columns are both blank in raw file. This only
        applies to iTunes workflow.
        """
        return """
          UPDATE {database_schema}.apple_id_mapping
          SET apple_release_id = zz.upc
          FROM (
              SELECT DISTINCT aa.upc, aa.apple_id
              FROM production.staging_raw_itunes aa
              INNER JOIN {database_schema}.apple_id_mapping bb ON
                  aa.apple_id = bb.apple_id
              WHERE bb.apple_release_id = 0 AND aa.upc is not null
          ) zz
          WHERE {database_schema}.apple_id_mapping.apple_id = zz.apple_id
          """.format(database_schema=self.database_schema)

    def update_apple_track_id_one_track(self):
        """Update apple track id for tracks with non-shared isrc.

        Update apple_track_id from the isrc which is not shared among tracks.
        """
        return """
          UPDATE {database_schema}.apple_id_mapping
          SET apple_track_id = yy.final_track_id
          FROM (
            SELECT (
                CASE
                WHEN z.orchard_isrc <> '' THEN z.orchard_isrc
                WHEN zz.ioda_track_id <> '' THEN zz.ioda_track_id
                ELSE '' END) AS final_track_id, a.apple_release_id
                FROM {database_schema}.apple_id_mapping a
                LEFT JOIN (
                     SELECT upc AS orchard_upc, MIN(isrc) AS orchard_isrc
                     FROM dim_track
                     WHERE cd <> 0 AND track_id <> 0 AND track_unique_id <> 0
                     GROUP BY upc
                     HAVING COUNT(*) = 1
                ) z ON z.orchard_upc = a.apple_release_id
                LEFT JOIN (
                     SELECT ioda_release_id AS ioda_release_id,
                         MIN(ioda_track_id) AS ioda_track_id
                     FROM ioda_track_mapping
                     GROUP BY ioda_release_id
                     HAVING COUNT(*) = 1
                    ) zz ON zz.ioda_release_id = a.apple_release_id
                    WHERE (a.apple_track_id = '' OR a.apple_track_id = 0)
                    AND vendor_offer_code <> ''
                    AND a.album_or_track = 'track'
          ) yy
          WHERE yy.apple_release_id =
                {database_schema}.apple_id_mapping.apple_release_id
            AND {database_schema}.apple_id_mapping.apple_track_id = ''
            AND {database_schema}.apple_id_mapping.album_or_track
                = 'track'""".format(database_schema=self.database_schema)

    def update_orchard_track_id(self):
        """Update orchard_track_id from dim and mapping table."""
        return """
          UPDATE {database_schema}.apple_id_mapping
          SET orchard_track_id = yy.final_orchard_track_id
          FROM (
                SELECT (
                  CASE WHEN z.isrc <> '' THEN z.isrc
                       WHEN zz.orchard_isrc <> '' THEN orchard_isrc
                       ELSE '' END) AS final_orchard_track_id, a.apple_track_id
                FROM {database_schema}.apple_id_mapping a
                LEFT JOIN (
                    SELECT distinct isrc
                    FROM dim_track
                    WHERE cd <> 0 AND track_id <> 0
                ) z ON z.isrc = a.apple_track_id
                LEFT JOIN (
                    SELECT orchard_isrc, ioda_track_id
                    FROM ioda_track_mapping
                    GROUP BY orchard_isrc, ioda_track_id
                ) zz ON zz.ioda_track_id = a.apple_track_id
          ) yy
          WHERE yy.apple_track_id =
               {database_schema}.apple_id_mapping.apple_track_id
             AND {database_schema}.apple_id_mapping.orchard_track_id = ''
             AND {database_schema}.apple_id_mapping.album_or_track
                 = 'track'""".format(database_schema=self.database_schema)

    def update_orchard_release_id(self):
        """Update orchard_release_id from dim and mapping table."""
        return """
          UPDATE {database_schema}.apple_id_mapping
          SET orchard_release_id = yy.final_orchard_release_id::bigint::varchar
          FROM (
                SELECT DISTINCT (
                  CASE
                    WHEN CAST(z.upc AS bigint) <> 0
                       THEN CAST(z.upc AS bigint)
                    WHEN CAST(zz.orchard_upc AS bigint) <> 0
                       THEN CAST(orchard_upc AS bigint)
                    ELSE 0 END) AS final_orchard_release_id, a.apple_release_id
                FROM {database_schema}.apple_id_mapping a
                LEFT JOIN (
                   SELECT distinct releaseid AS upc
                   FROM dim_release
                ) z ON z.upc = a.apple_release_id
                LEFT JOIN (
                   SELECT orchard_upc, ioda_release_id
                   FROM ioda_release_mapping
                   GROUP BY orchard_upc, ioda_release_id
                ) zz ON zz.ioda_release_id = a.apple_release_id
                WHERE a.album_or_track = 'track'
          ) yy
          WHERE yy.apple_release_id =
                {database_schema}.apple_id_mapping.apple_release_id
            AND {database_schema}.apple_id_mapping.orchard_release_id = 0
            AND {database_schema}.apple_id_mapping.album_or_track
                = 'track'""".format(database_schema=self.database_schema)

    def update_album_level_apple_release_id(self):
        """Update album level apple_release_id column.

        (By joining mapping and dim tables).

        Update apple_release_id column in apple_id_mapping table by joining
        dim_release, ioda_release_mapping tables on all the terms in the '_'
        delimited vendor_offer_code and vendor_identifier column.
        """
        return """
          UPDATE {database_schema}.apple_id_mapping
          SET apple_release_id = CAST(z.final_release_id AS varchar)
          FROM (
            SELECT (
              CASE
              WHEN CAST(x.vendor_offer_code_releaseid AS bigint) <> 0
                THEN CAST(x.vendor_offer_code_releaseid AS bigint)
              WHEN CAST(y.vendor_offer_code_ioda_release_id AS bigint) <> 0
                 THEN CAST(y.vendor_offer_code_ioda_release_id AS bigint)
              WHEN CAST(m.vendor_identifier_releaseid AS bigint) <> 0
                 THEN CAST(m.vendor_identifier_releaseid AS bigint)
              WHEN CAST(n.vendor_identifier_ioda_release_id AS bigint) <> 0
                 THEN CAST(n.vendor_identifier_ioda_release_id AS bigint)
                 ELSE 0 END) AS final_release_id,
              a.apple_id
              FROM {database_schema}.apple_id_mapping a
                LEFT JOIN (
                    SELECT distinct releaseid AS vendor_offer_code_releaseid
                    FROM dim_release
                ) x ON x.vendor_offer_code_releaseid =
                    split_part(a.vendor_offer_code,'_',1)
                LEFT JOIN (
                  SELECT distinct ioda_release_id AS
                      vendor_offer_code_ioda_release_id
                  FROM ioda_release_mapping
                ) y ON y.vendor_offer_code_ioda_release_id =
                    split_part(a.vendor_offer_code,'_',1)
                LEFT JOIN (
                    SELECT distinct releaseid AS vendor_identifier_releaseid
                    FROM dim_release
                ) m ON m.vendor_identifier_releaseid =
                    split_part(a.vendor_identifier,'_',1)
                LEFT JOIN (
                  SELECT distinct ioda_release_id AS
                      vendor_identifier_ioda_release_id
                  FROM ioda_release_mapping
                ) n ON n.vendor_identifier_ioda_release_id =
                    split_part(a.vendor_identifier,'_',1)
                WHERE (split_part(vendor_offer_code,'_',1)!=''
                       OR split_part(vendor_identifier,'_',1)!='')
                AND album_or_track = 'album'
          ) z WHERE z.apple_id = {database_schema}.apple_id_mapping.apple_id
            AND {database_schema}.apple_id_mapping.apple_release_id = 0
            AND {database_schema}.apple_id_mapping.album_or_track
            = 'album'""".format(database_schema=self.database_schema)

    def update_album_level_orchard_release_id(self):
        """Update album level orchard_release_id column.

        (By joining mapping and dim tables).

        Update orchard_release_id column in apple_id_mapping table by joining
        dim_release, ioda_release_mapping tables on all the terms in the '_'
        delimited vendor_offer_code and vendor_identifier column.
        """
        return """
          UPDATE {database_schema}.apple_id_mapping
          SET orchard_release_id = yy.final_orchard_release_id::bigint::varchar
          FROM (
            SELECT (
              CASE
              WHEN CAST(z.upc AS bigint) <> 0
                THEN CAST(z.upc AS bigint)
              WHEN CAST(zz.orchard_upc AS bigint) <> 0
                THEN CAST(orchard_upc AS bigint)
              ELSE 0 END) AS final_orchard_release_id, a.apple_release_id
            FROM {database_schema}.apple_id_mapping a
            LEFT JOIN (
                SELECT distinct releaseid AS upc FROM dim_release
            ) z ON z.upc = a.apple_release_id
            LEFT JOIN (
                SELECT orchard_upc, ioda_release_id FROM ioda_release_mapping
                GROUP BY orchard_upc, ioda_release_id
            ) zz ON zz.ioda_release_id = a.apple_release_id
            WHERE a.album_or_track = 'album'
          ) yy
          WHERE yy.apple_release_id
            = {database_schema}.apple_id_mapping.apple_release_id
          AND {database_schema}.apple_id_mapping.orchard_release_id = 0
          AND {database_schema}.apple_id_mapping.album_or_track
           = 'album'""".format(database_schema=self.database_schema)

    def update_season_pass_orchard_release_track_id(self):
        """Update orchard release track id from season pass special case.

        This update is for handling season pass has vendor identifier
        ORCHARD_{artist_id}.
        """
        return """
            UPDATE {database_schema}.apple_id_mapping
            SET orchard_release_id = m.upc,
            orchard_track_id = m.isrc
            FROM (
                SELECT dt.upc, dt.isrc, 'ORCHARD'||'_'||dr.artistid AS artistid
                FROM dim_release dr
                INNER JOIN dim_track dt ON dt.upc = dr.releaseid
                WHERE product_subtype_id = 45 AND dt.cd = 1 AND track_id = 1
            ) m
            WHERE m.artistid = apple_id_mapping.vendor_identifier""".format(
            database_schema=self.database_schema)

    def update_with_vendor_identifier_mapping(self):
        """Update orchard release id with the help of vendor_identifier_map.

        Some data with only isrc and no upc can be mapped by using the
        vendor_identifier_map table.
        """
        return """
            UPDATE {database_schema}.apple_id_mapping
            SET orchard_release_id = xx.upc
            FROM (
                SELECT vim.isrc, vim.upc
              FROM vendor_identifier_map vim
              GROUP BY vim.isrc, vim.upc
            ) xx
            WHERE (apple_id_mapping.orchard_release_id IS NULL OR
            apple_id_mapping.orchard_release_id = 0)
            AND apple_id_mapping.orchard_track_id = xx.isrc""".format(
            database_schema=self.database_schema)

    def update_ioda_video_mapping_case(self):
        """Update orchard release id and orchard track id.

        (With the help of ioda_video_mapping table).
        """
        return """
            UPDATE {database_schema}.apple_id_mapping
            SET orchard_release_id = xx.upc,
            orchard_track_id = xx.isrc
            FROM (
                SELECT dt.upc, dt.isrc, m.apple_id
                FROM {database_schema}.apple_id_mapping m
                INNER JOIN ioda_video_mapping ivm ON ivm.apple_id = m.apple_id
                INNER JOIN dim_track dt ON dt.upc = ivm.orchard_upc
                WHERE (m.orchard_release_id IS NULL OR m.orchard_release_id = 0
                 OR m.orchard_track_id IS NULL OR m.orchard_track_id = 0)
                AND dt.cd <> 0 AND dt.track_id <> 0
            ) xx
            WHERE xx.apple_id = apple_id_mapping.apple_id""".format(
            database_schema=self.database_schema)

    def update_ioda_tv_mapping_case(self):
        """Update orchard release id and orchard track id.

        (With the help of ioda_tv_mapping table).
        """
        return """
            UPDATE {database_schema}.apple_id_mapping
            SET orchard_release_id = xx.upc,
            orchard_track_id = xx.isrc
            FROM (
                SELECT dt.upc, dt.isrc, m.apple_id
                FROM {database_schema}.apple_id_mapping m
                INNER JOIN ioda_tv_mapping ivm
                    ON ivm.ioda_video_id = m.vendor_identifier
                INNER JOIN dim_track dt ON dt.upc = ivm.orchard_upc
                WHERE dt.cd <> 0 AND dt.track_id <> 0
            ) xx
            WHERE xx.apple_id = apple_id_mapping.apple_id
            AND (apple_id_mapping.orchard_release_id IS NULL
                OR apple_id_mapping.orchard_release_id = 0
                OR apple_id_mapping.orchard_track_id ='')""".format(
            database_schema=self.database_schema)

    def update_orchard_release_track_id_from_isrc(self):
        """Update orchard track and release id from isrc column.

        If no other column has isrc and upc information but the isrc column
        AND there is only one track for that isrc, we can get the upc also.
        """
        return """
            UPDATE {database_schema}.apple_id_mapping
            SET orchard_track_id = yy.isrc,
                orchard_release_id = yy.upc
            FROM (
                SELECT DISTINCT ss.apple_id, ss.isrc, xx.upc
                FROM production.staging_raw_itunes ss
                INNER JOIN (
                    SELECT COUNT(*), isrc, MIN(upc) AS upc
                    FROM dim_track
                    WHERE track_unique_id <> 0
                    GROUP BY isrc
                    HAVING COUNT(*) = 1
                ) xx ON xx.isrc = ss.isrc
            ) yy
            WHERE {database_schema}.apple_id_mapping.apple_id = yy.apple_id
            """.format(database_schema=self.database_schema)

    def update_apple_release_track_id_from_upc_isrc(self):
        """Match against upc and isrc.

        This only applies to iTunes workflow.
        """
        return """
            UPDATE {database_schema}.apple_id_mapping
            SET apple_release_id = xx.upc,
                apple_track_id = xx.isrc
            FROM (
                SELECT DISTINCT ss.upc, ss.isrc, ss.vendor_identifier,
                  ss.vendor_offer_code, m.orchard_release_id,
                  m.orchard_track_id, ss.apple_id
                FROM production.staging_raw_itunes ss
                INNER JOIN {database_schema}.apple_id_mapping m
                  ON m.apple_id = ss.apple_id
                INNER JOIN dim_track dt
                  ON dt.upc = ss.upc AND dt.isrc = ss.isrc
                WHERE (orchard_release_id = 0 OR orchard_track_id = '')
            ) xx
            WHERE {database_schema}.apple_id_mapping.apple_id = xx.apple_id
            """.format(database_schema=self.database_schema)

    def update_orchard_release_id_from_manufacturer_upc_in_vid(self):
        """Match against manufacturer_upc."""
        return """
        UPDATE {database_schema}.apple_id_mapping
        SET orchard_release_id = xx.releaseid
        FROM
        (
            SELECT dr.releaseid, m.apple_id
            FROM {database_schema}.apple_id_mapping m
            INNER JOIN dim_release dr
              ON dr.manufacturer_upc = m.vendor_identifier
            WHERE m.orchard_release_id = 0 AND m.orchard_track_id = ''
            AND m.apple_track_id = '' AND m.album_or_track = 'album'
            AND vendor_identifier <> ''
        ) xx
        WHERE xx.apple_id = {database_schema}.apple_id_mapping.apple_id
        """.format(database_schema=self.database_schema)

    def update_orchard_release_id_from_manufacturer_upc_in_upc(self):
        """Match against manufacturer_upc.

        This only apply to iTunes workflow.
        """
        return """
        UPDATE {database_schema}.apple_id_mapping
        SET orchard_release_id = xx.releaseid
        FROM
        (
            SELECT DISTINCT dr.releaseid, m.apple_id
            FROM {database_schema}.apple_id_mapping m
            INNER JOIN production.staging_raw_itunes ss
              ON ss.apple_id = m.apple_id
            INNER JOIN dim_release dr ON dr.manufacturer_upc = ss.upc
            WHERE m.orchard_release_id = 0 AND upc <> 0 AND upc is not null
        ) xx
        WHERE xx.apple_id = {database_schema}.apple_id_mapping.apple_id
        """.format(database_schema=self.database_schema)


class AppleMusicAppleIdMapping(AppleIdMapping):
    """Subclass to use in apple_music workflow."""

    def __init__(self, database_schema):
        """Initialize object with a database schema."""
        super().__init__(database_schema)

    def insert_new_entries(self):
        """Insert new entries in apple_id_mapping table.

        This overrides insert_new_entries method in base class.
        """
        return """
          INSERT INTO {database_schema}.apple_id_mapping
          SELECT ss.apple_id, '','','',ss.isrc,ss.isrc,ss.isrc,'track'
          FROM (
              SELECT apple_identifier AS apple_id, MIN(isrc) AS isrc
              FROM staging_raw_apple_music_streams
              GROUP BY apple_identifier
          ) ss
          LEFT JOIN {database_schema}.apple_id_mapping am
            ON am.apple_id = ss.apple_id
          WHERE am.apple_id IS NULL""".format(
            database_schema=self.database_schema)

    def update_orchard_release_track_id_from_isrc(self):
        """Update orchard track and release id from isrc column.

        If no other column has isrc and upc information but the isrc column
        AND there is only one track for that isrc, we can get the upc also.
        This overrides update_orchard_release_track_id_from_isrc
        method in base class.
        """
        return """
            UPDATE {database_schema}.apple_id_mapping
            SET orchard_track_id = yy.isrc,
                orchard_release_id = yy.upc
            FROM (
                SELECT ss.apple_identifier, ss.isrc, xx.upc
                FROM staging_raw_apple_music_streams ss
                INNER JOIN (
                    SELECT COUNT(*), isrc, MIN(upc) AS upc
                    FROM dim_track
                    WHERE track_unique_id <> 0
                    GROUP BY isrc
                    HAVING COUNT(*) = 1
                ) xx ON xx.isrc = ss.isrc
            ) yy
            WHERE
            {database_schema}.apple_id_mapping.apple_id = yy.apple_identifier
            """.format(database_schema=self.database_schema)