"""Artist Links Model.

This model represents an artist link

"""

import pymysql
import sqlalchemy
from flask import g
from participant.connectors import mysql
from sqlalchemy import text
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func

VARIOUS_ARTISTS = [
    'Various Artists',
    'فنانون متنوعون',
    '群星',
    '群星',
    'Verschillende artiesten',
    'Eri esittäjiä',
    'Multi-interprètes',
    'Verschiedene Interpreten',
    'Διάφοροι καλλιτέχνες',
    'אמנים שונים',
    'Artisti Vari',
    'Vários intérpretes',
    'Разные исполнители',
    'Varios Artistas',
    'Blandade Artister',
    'รวมศิลปิน',
    'Çeşitli Sanatçılar',
    'Various',
    'Various writers',
]


class Subaccount(mysql.BaseModel):
    """Artist Links model."""

    __tablename__ = 'subaccount'

    subaccount_id = sqlalchemy.Column(
        'subaccount_id',
        sqlalchemy.Integer,
        primary_key=True,
        autoincrement=True,
    )
    name = sqlalchemy.Column('subaccount_name', sqlalchemy.VARCHAR(255), nullable=False)


class ArtistLinks(mysql.BaseModel):
    """Artist Links model."""

    __tablename__ = 'participant_external_link'

    id = sqlalchemy.Column(  # noqa
        'participant_external_link_id',
        sqlalchemy.Integer,
        primary_key=True,
        autoincrement=True,
    )
    vendor_id = sqlalchemy.Column(sqlalchemy.Integer)
    subaccount_id = sqlalchemy.Column(
        sqlalchemy.Integer, sqlalchemy.ForeignKey('subaccount.subaccount_id')
    )
    store_id = sqlalchemy.Column(sqlalchemy.Integer)
    orchard_artist_name = sqlalchemy.Column(sqlalchemy.VARCHAR(255), nullable=False)
    store_artist_id = sqlalchemy.Column(sqlalchemy.VARCHAR(255), nullable=False)
    created_at = sqlalchemy.Column(sqlalchemy.DateTime, default=func.now())
    last_updated = sqlalchemy.Column(
        sqlalchemy.DateTime, default=func.now(), onupdate=func.now()
    )
    subaccount = relationship(Subaccount, lazy='joined')

    def to_dict(self):
        """Return the object as dictionary."""
        return dict(
            id=self.id,
            vendor_id=self.vendor_id,
            subaccount_id=self.subaccount_id,
            store_id=self.store_id,
            orchard_artist_name=self.orchard_artist_name,
            store_artist_id=self.store_artist_id,
            subaccount_name=self.subaccount.name if self.subaccount else None,
        )


def get_artist_links(vendor_id, subaccount_id):
    """Return all artist links for vendor/subaccount.

    Args:
        vendor_id (int): the vendors id
        subaccount_id (int): the subaccount id
    Returns:
        list of dicts: array of artist links

    """
    with mysql.ar_db_session(read_only=True) as session:
        result = None
        if vendor_id > 0:
            result = session.execute(
                text(
                    """
            SELECT
            participant_external_link.participant_external_link_id,
            participant_external_link.store_id,
            participant_external_link.orchard_artist_name,
            participant_external_link.store_artist_id,
            subaccount_1.subaccount_name
            FROM participant_external_link LEFT OUTER JOIN subaccount AS subaccount_1
            ON subaccount_1.subaccount_id = participant_external_link.subaccount_id
            WHERE participant_external_link.vendor_id = :vendor_id
            """
                ),
                {'vendor_id': vendor_id},
            )
        elif subaccount_id > 0:
            result = session.execute(
                text(
                    """
            SELECT
            participant_external_link.participant_external_link_id,
            participant_external_link.store_id,
            participant_external_link.orchard_artist_name,
            participant_external_link.store_artist_id,
            subaccount_1.subaccount_name
            FROM participant_external_link LEFT OUTER JOIN subaccount AS subaccount_1
            ON subaccount_1.subaccount_id = participant_external_link.subaccount_id
            WHERE participant_external_link.subaccount_id = :subaccount_id
            """
                ),
                {'subaccount_id': subaccount_id},
            )

        return [
            {
                'id': x[0],
                'store_id': x[1],
                'orchard_artist_name': x[2],
                'store_artist_id': x[3],
                'subaccount_name': x[4],
            }
            for x in result.fetchall()
            if x[2] not in VARIOUS_ARTISTS
        ]


def update_artist_links(data):
    """Update artist links.

    Args:
        data (list of dicts): [{'id': 227553, 'store_artist_id': '1173376167'}, ....]

    """
    with mysql.ar_db_session() as session:
        _raw_update(session, data)


def get_artist_links_by_ids(ids):
    """Get artist links by ids.

    Args:
        ids (int): the ids of the rows to pull back
    Returns:
        list of dicts: array of artist links

    """
    with mysql.ar_db_session(read_only=True) as session:
        results = session.query(ArtistLinks).filter(ArtistLinks.id.in_(ids)).all()
        return [x.to_dict() for x in results]


def _raw_update(session, data):
    return session.execute(_get_query(data))


def _log_missing_id(row):
    g.log.info('missing id {}'.format(row))


def _get_query(data):
    rows = []
    for row in data:
        if 'id' in row:
            rows.append(_get_raw_insert_row(row))
        else:
            _log_missing_id(row)

    return """
        INSERT INTO participant_external_link (
        participant_external_link_id,
        store_artist_id,
        vendor_id,
        store_id,
        created_at,
        last_updated
        )
        VALUES
        {}
        ON DUPLICATE KEY UPDATE
            store_artist_id = VALUES(store_artist_id),
            last_updated = NOW();
        """.format(
        ','.join(rows)
    )


def _get_raw_insert_row(row):
    if row['store_artist_id'] is None:
        return '({}, NULL, 1, 1, NOW(), NOW())'.format(int(row['id']))

    return '({}, "{}",1, 1, NOW(), NOW())'.format(
        int(row['id']), pymysql.converters.escape_string(row['store_artist_id'])
    )