"""Rights Attribute queries."""

from sqlalchemy import bindparam
from sqlalchemy.sql import text

from backend.connectors import mysql
from backend.constants.validation import VARIOUS_ARTISTS
from backend.models.rights_attribute import RightsAttributes
from backend.models.rights_attribute import TrackRightsAttributes
from backend.models.rights_attribute import TrackRightsAttributesEdits
from backend.utils import api as api_utils

_COMPILATION_ATTRIBUTE_ID = 2
_VARIOUS_ARTISTS = list(VARIOUS_ARTISTS)


class RightsAttributeQuery(object):
    """Handles Rights Attribute queries."""

    @classmethod
    @mysql.db_session_wrap
    def get_rights_attributes(cls, session):
        """Get all possible rights attributes from DB.

        Args:
            session (object): SQLAlchemy database session (optional)

        Returns:
            List of all possible rights attributes
        """
        rights_attributes = session.query(RightsAttributes) \
            .order_by(RightsAttributes.id).all()

        return api_utils.create_get_list_response(
            [ra.to_dict() for ra in rights_attributes])


    @classmethod
    @mysql.db_session_wrap
    def get_suggested_rights_attributes(cls, tuid, session):
        """Get all possible suggested rights attributes from DB.

        Args:
            tuid (int): unique id of track
            session (object): SQLAlchemy database session (optional)

        Returns:
            List of suggested rights attributes with reasons
        """
        # SQLite (unit tests) does not support CONCAT; use || instead
        dialect = session.bind.dialect.name
        if dialect == 'sqlite':
            keyword_concat = "'\\b' || rask.keyword || '\\b'"
            imprint_concat = "'\\b' || rasvik.imprint_keyword || '\\b'"
            performer_set_concat = "GROUP_CONCAT(ta2.name, '|')"
        else:
            keyword_concat = "CONCAT('\\\\b', rask.keyword, '\\\\b')"
            imprint_concat = "CONCAT('\\\\b', rasvik.imprint_keyword, '\\\\b')"
            performer_set_concat = "GROUP_CONCAT(ta2.name ORDER BY ta2.name SEPARATOR '|')"

        # Sub-query that counts distinct performer-artist sets across tracks in the
        # release. Returns > 1 only when at least two tracks have different performer
        # sets, i.e. a true compilation.
        distinct_performer_sets_subquery = (
            '(SELECT COUNT(DISTINCT performer_set) '
            'FROM ('
                'SELECT t2.id, '
                f'{performer_set_concat} AS performer_set '
                'FROM track t2 '
                'INNER JOIN track_artist ta2 '
                'ON t2.id = ta2.track_id AND ta2.type = \'performer\' '
                'WHERE t2.release_id = t.release_id '
                'GROUP BY t2.id'
            ') AS track_performer_sets) > 1'
        )

        # Each branch returns:
        # (ra_id, description, reason_type, keyword, matched_on,
        #  vendor_id, imprint_keyword, subaccount_id, subgenre_id, genre_id)
        single_track_guard = (
            f'AND (ra.id <> {_COMPILATION_ATTRIBUTE_ID} '
            'OR ('
            '(SELECT COUNT(*) FROM track t2 WHERE t2.release_id = t.release_id) > 1 '
            f'AND {distinct_performer_sets_subquery}) '
            ') '
        )

        suggestion_query = text(
            'SELECT ra.id, ra.description, \'keyword_match\', rask.keyword, \'track_name\', '
            '       NULL, NULL, NULL, NULL, NULL '
            'FROM track t '
            'INNER JOIN releases r ON t.release_id = r.release_id '
            'INNER JOIN rights_attributes_suggestion_keywords rask '
            f'ON LOWER(t.track_name) REGEXP {keyword_concat} '
            'INNER JOIN rights_attributes ra ON ra.id = rask.rights_attribute_id '
            f'WHERE t.id = :tuid AND ra.id <> 1 {single_track_guard}'

            'UNION ALL '

            'SELECT ra.id, ra.description, \'keyword_match\', rask.keyword, \'version\', '
            '       NULL, NULL, NULL, NULL, NULL '
            'FROM track t '
            'INNER JOIN rights_attributes_suggestion_keywords rask '
            f'ON LOWER(t.version) REGEXP {keyword_concat} '
            'INNER JOIN rights_attributes ra ON ra.id = rask.rights_attribute_id '
            f'WHERE t.id = :tuid AND ra.id <> 1 {single_track_guard}'

            'UNION ALL '

            'SELECT ra.id, ra.description, \'keyword_match\', rask.keyword, \'release_name\', '
            '       NULL, NULL, NULL, NULL, NULL '
            'FROM track t '
            'INNER JOIN releases r ON t.release_id = r.release_id '
            'INNER JOIN rights_attributes_suggestion_keywords rask '
            f'ON LOWER(r.release_name) REGEXP {keyword_concat} '
            'INNER JOIN rights_attributes ra ON ra.id = rask.rights_attribute_id '
            f'WHERE t.id = :tuid AND ra.id <> 1 {single_track_guard}'

            'UNION ALL '

            'SELECT ra.id, ra.description, \'keyword_match\', rask.keyword, \'artist_name\', '
            '       NULL, NULL, NULL, NULL, NULL '
            'FROM track t '
            'LEFT JOIN track_artist ta ON t.id = ta.track_id '
            'INNER JOIN rights_attributes_suggestion_keywords rask '
            f'ON LOWER(ta.name) REGEXP {keyword_concat} '
            'INNER JOIN rights_attributes ra ON ra.id = rask.rights_attribute_id '
            f'WHERE t.id = :tuid AND ra.id <> 1 {single_track_guard}'

            'UNION ALL '

            # label ID and imprint match
            'SELECT ra.id, ra.description, \'vendor_imprint_match\', NULL, NULL, '
            '       rasvik.vendor_id, rasvik.imprint_keyword, NULL, NULL, NULL '
            'FROM track t '
            'INNER JOIN releases r ON t.release_id = r.release_id '
            'INNER JOIN project p ON p.project_id = r.project_id '
            'INNER JOIN rights_attributes_suggestion_vendor_imprint_keywords rasvik '
            'ON rasvik.vendor_id = p.vendor_id '
            f'AND LOWER(r.label) REGEXP {imprint_concat} '
            'INNER JOIN rights_attributes ra ON ra.id = rasvik.rights_attribute_id '
            'WHERE t.id = :tuid AND ra.id <> 1 '

            'UNION ALL '

            # vendor and subaccount match
            'SELECT ra.id, ra.description, \'vendor_subaccount_match\', NULL, NULL, '
            '       rasvsk.vendor_id, NULL, rasvsk.subaccount_id, NULL, NULL '
            'FROM track t '
            'INNER JOIN releases r ON t.release_id = r.release_id '
            'INNER JOIN project p ON p.project_id = r.project_id '
            'INNER JOIN rights_attributes_suggestion_vendor_subaccount_keywords rasvsk '
            'ON rasvsk.vendor_id = p.vendor_id AND rasvsk.subaccount_id = p.subaccount_id '
            'INNER JOIN rights_attributes ra ON ra.id = rasvsk.rights_attribute_id '
            'WHERE t.id = :tuid AND ra.id <> 1 '

            'UNION ALL '

            # genre and subgenre match
            'SELECT ra.id, ra.description, \'genre_subgenre_match\', NULL, NULL, '
            '       NULL, NULL, NULL, rasgsk.subgenre_id, rasgsk.genre_id '
            'FROM track t '
            'INNER JOIN releases r ON t.release_id = r.release_id '
            'INNER JOIN release_subgenre rs ON rs.release_id = r.release_id '
            'INNER JOIN subgenre s ON rs.subgenre_id = s.orchard_id '
            'INNER JOIN rights_attributes_suggestion_genre_subgenre_keywords rasgsk '
            'ON s.orchard_id = rasgsk.subgenre_id '
            'AND (r.genre_id = rasgsk.genre_id OR rasgsk.genre_id IS NULL) '
            'INNER JOIN rights_attributes ra ON ra.id = rasgsk.rights_attribute_id '
            'WHERE t.id = :tuid AND ra.id <> 1 '

            'UNION ALL '

            # compilations: tracks in the release have different performer-artist sets
            f'SELECT {_COMPILATION_ATTRIBUTE_ID}, ra.description, \'compilation_check\', NULL, NULL, '
            '       NULL, NULL, NULL, NULL, NULL '
            'FROM track t '
            f'INNER JOIN rights_attributes ra ON ra.id = {_COMPILATION_ATTRIBUTE_ID} '
            'WHERE t.id = :tuid '
            'AND (SELECT COUNT(*) FROM track t2 WHERE t2.release_id = t.release_id) > 1 '
            f'AND {distinct_performer_sets_subquery} '

            'UNION ALL '

            # compilations: release-level or project-level artist is a "Various Artists" variant
            # Guard: must have > 1 track in release (single-track products cannot be compilations)
            f'SELECT {_COMPILATION_ATTRIBUTE_ID}, ra.description, \'compilation_check_various_artists\', NULL, NULL, '
            '       NULL, NULL, NULL, NULL, NULL '
            'FROM track t '
            'INNER JOIN releases r ON t.release_id = r.release_id '
            'LEFT JOIN release_artist ral '
            'ON ral.release_id = r.release_id AND ral.role = \'performer\' '
            'LEFT JOIN project p ON p.project_id = r.project_id '
            'LEFT JOIN artist_info ai ON ai.artist_id = p.artist_id '
            f'INNER JOIN rights_attributes ra ON ra.id = {_COMPILATION_ATTRIBUTE_ID} '
            'WHERE t.id = :tuid '
            'AND (SELECT COUNT(*) FROM track t2 WHERE t2.release_id = t.release_id) > 1 '
            'AND (LOWER(ral.artist_name) IN :various_artists '
            'OR LOWER(ai.name) IN :various_artists)'
        ).bindparams(bindparam('various_artists', expanding=True))

        rows = session.execute(
            suggestion_query,
            {'tuid': tuid, 'various_artists': _VARIOUS_ARTISTS},
        ).fetchall()
        return api_utils.create_get_list_response(
            cls._group_suggestions_with_reasons(rows))

    @staticmethod
    def _group_suggestions_with_reasons(rows):
        """Group raw suggestion rows into items with deduplicated reasons.

        Each row is (ra_id, description, reason_type, keyword, matched_on,
        vendor_id, imprint_keyword, subaccount_id, subgenre_id, genre_id).
        Returns items sorted by attribute_id with track_rights_attribute_id=0.
        """
        attr_map = {}
        seen = {}
        for (ra_id, description, reason_type, keyword, matched_on,
             vendor_id, imprint_keyword, subaccount_id, subgenre_id, genre_id) in rows:
            if ra_id not in attr_map:
                attr_map[ra_id] = {
                    'track_rights_attribute_id': 0,
                    'rights_attribute_id': ra_id,
                    'description': description,
                    'reasons': [],
                }
                seen[ra_id] = set()
            reason = {'type': reason_type}
            if reason_type == 'keyword_match':
                reason['keyword'] = keyword
                reason['on'] = matched_on
            elif reason_type == 'vendor_imprint_match':
                reason['vendor_id'] = vendor_id
                reason['imprint_keyword'] = imprint_keyword
            elif reason_type == 'vendor_subaccount_match':
                reason['vendor_id'] = vendor_id
                reason['subaccount_id'] = subaccount_id
            elif reason_type == 'genre_subgenre_match':
                reason['subgenre_id'] = subgenre_id
                if genre_id is not None:
                    reason['genre_id'] = genre_id

            key = tuple(reason.items())
            if key not in seen[ra_id]:
                seen[ra_id].add(key)
                attr_map[ra_id]['reasons'].append(reason)

        return [attr_map[ra_id] for ra_id in sorted(attr_map)]

    @classmethod
    @mysql.db_session_wrap
    def get_track_rights_attributes(cls, tuid, session):
        """Get a track's currently set rights attributes from DB.

        Args:
            tuid (int): unique id of track
            session (object): SQLAlchemy database session (optional)

        Returns:
            List of the track's currently set rights attributes, or None if
            tuid not found in the database
        """
        track_rights_attributes = session.query(TrackRightsAttributes) \
            .filter(TrackRightsAttributes.unique_track_id == tuid) \
            .order_by(TrackRightsAttributes.rights_attribute_id)
        if track_rights_attributes.count() != 0:
            return api_utils.create_get_list_response(
                [{'track_rights_attribute_id': tra.id,
                  'rights_attribute_id': tra.rights_attribute_id,
                  'description': tra.rights_attributes.description
                  }
                 for tra in track_rights_attributes])

        vendor_rights_attributes_query = text(
            'SELECT ra.description, ra.id '
            'FROM track t '
            'INNER JOIN releases r '
            'ON t.release_id = r.release_id '
            'INNER JOIN project p '
            'ON r.project_id = p.project_id '
            'INNER JOIN vendor_rights_attributes vra '
            'ON p.vendor_id = vra.vendor_id '
            'INNER JOIN rights_attributes ra '
            'ON vra.rights_attribute_id = ra.id '
            'WHERE t.id = :tuid '
            'GROUP BY 1, 2 '
            'ORDER BY 2'
        )
        vendor_rights_attributes = session.execute(
            vendor_rights_attributes_query, {'tuid': tuid}).fetchall()
        if len(vendor_rights_attributes) != 0:
            return api_utils.create_get_list_response(
                [{'track_rights_attribute_id': 0,
                  'rights_attribute_id': vra[1],
                  'description': vra[0],
                  'reasons': [{'type': 'entity_rules'}]}
                 for vra in vendor_rights_attributes])

        return api_utils.create_get_list_response([])

    @classmethod
    @mysql.db_session_wrap
    def get_track_rights_attributes_edits(cls, tuid, session):
        """Get a track's currently set rights attributes edits from DB.

        Args:
            tuid (int): unique id of track
            session (object): SQLAlchemy database session (optional)

        Returns:
            List of the track's currently set rights attributes edits, or None if
            tuid not found in the database
        """
        track_rights_attributes_edits = session.query(TrackRightsAttributesEdits) \
            .filter(TrackRightsAttributesEdits.unique_track_id == tuid) \
            .order_by(TrackRightsAttributesEdits.rights_attribute_id)

        return api_utils.create_get_list_response(
            [{'track_rights_attribute_id': trae.id,
              'rights_attribute_id': trae.rights_attribute_id,
              'description': trae.rights_attributes.description
              }
             for trae in track_rights_attributes_edits])