"""Artist last level genre

Revision ID: 4df3ee92e98f
Revises: e74c676f4fd3
Create Date: 2019-05-29 20:05:54.378677

"""
from alembic import op
import sqlalchemy as sa

from models.artists import Artist
from models.projects import Project
from models.records import Record

class Campaign(Project):
    __tablename__ = "Campaign"

# revision identifiers, used by Alembic.
revision = '4df3ee92e98f'
down_revision = 'e74c676f4fd3'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column(
        'Artist',
        sa.Column(
            'last_artist_level_id',
            sa.Integer(),
            nullable=False,
            server_default='1'
        )
    )
    op.alter_column('Artist', 'last_artist_level_id', server_default=None)
    op.add_column(
        'Artist',
        sa.Column(
            'last_genre_id',
            sa.Integer(),
            nullable=False,
            server_default='1'
        )
    )
    op.alter_column('Artist', 'last_genre_id', server_default=None)
    op.create_foreign_key(
        op.f('fk_Artist_last_artist_level_id_ArtistLevel'),
        'Artist',
        'ArtistLevel',
        ['last_artist_level_id'],
        ['id']
    )
    op.create_foreign_key(
        op.f('fk_Artist_last_genre_id_Genre'),
        'Artist',
        'Genre',
        ['last_genre_id'],
        ['id']
    )

    bind = op.get_bind()
    session = sa.orm.Session(bind=bind)

    exists_subq = (
        session.query(Campaign)
        .join(Campaign.record)
        .filter(Record.artist_id == Artist.id)
        .exists()
    )
    artist_level_subq = (
        session.query(Campaign.artist_level_id)
        .join(Campaign.record)
        .filter(Record.artist_id == Artist.id)
        .order_by(Record.id.desc(), Campaign.id.desc())
        .limit(1)
        .correlate(Artist)
        .as_scalar()
    )
    genre_subq = (
        session.query(Campaign.genre_id)
        .join(Campaign.record)
        .filter(Record.artist_id == Artist.id)
        .order_by(Record.id.desc(), Campaign.id.desc())
        .limit(1)
        .correlate(Artist)
        .as_scalar()
    )
    (
        session.query(Artist)
        .filter(exists_subq)
        .update(
            {
                Artist.last_artist_level_id: artist_level_subq,
                Artist.last_genre_id: genre_subq
            },
            synchronize_session=False
        )
    )
    session.commit()
    session.close()

    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(
        op.f('fk_Artist_last_genre_id_Genre'),
        'Artist',
        type_='foreignkey'
    )
    op.drop_constraint(
        op.f('fk_Artist_last_artist_level_id_ArtistLevel'),
        'Artist',
        type_='foreignkey'
    )
    op.drop_column('Artist', 'last_genre_id')
    op.drop_column('Artist', 'last_artist_level_id')
    # ### end Alembic commands ###