"""create_artist_teams

Revision ID: e01444cb82f7
Revises: 68c9fcfc5fa8
Create Date: 2022-06-03 13:00:49.777359

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql
from models.user_project import UserProjectRoles
from sqlalchemy.dialects.postgresql import insert
from migrations.utils import get_table
# revision identifiers, used by Alembic.

revision = 'e01444cb82f7'
down_revision = 'e34841cb0721'
branch_labels = None
depends_on = None


def upgrade():
    bind = op.get_bind()
    artist_team_table = get_table(bind, "ArtistTeam")
    artist_team_user_table = get_table(bind, "ArtistTeamUser")
    result = bind.execute("""
        select proj.id, proj.owner_id, A.id, proj.label_id from "Project" proj
        join "ProjectTargetItem" PTI on PTI.project_id = proj.id and PTI.entity_type = 0 and
                        PTI.add_type = 2 and
                        PTI.is_deleted is false
        join "Artist" A on A.id = PTI.entity_id and is_unknown is false
        left join "ArtistTeam" AT on AT.artist_id = A.id
        WHERE AT.id is null and proj.is_deleted is false and proj.owner_id is not null and proj.is_confidential is false
        GROUP BY proj.id, proj.owner_id, A.id, proj.label_id""").fetchall()

    def assign_user_to_team(user_id, artist_team_id, role):
        bind.execute(
            insert(artist_team_user_table)
            .values(artist_team_id=artist_team_id, user_id=user_id, role=role)
            .on_conflict_do_nothing()
        )

    def create_team(artist_id: int, label_id: int) -> int:
        return bind.execute(
            insert(artist_team_table)
            .values(artist_id=artist_id, label_id=label_id)
            .returning(artist_team_table.c.id)
            .on_conflict_do_nothing()
        ).fetchone()[0]

    for project in result:
        owner_id = project[1]
        label_id = project[3]
        artist_id = project[2]
        artist_team_id = create_team(artist_id, label_id)
        assign_user_to_team(
            owner_id, artist_team_id, 1)


def downgrade():
    pass