"""add_artist_moment_model

Revision ID: b3d99035eefa
Revises: 8a7f9e07d99b
Create Date: 2021-01-11 10:46:37.408653

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'b3d99035eefa'
down_revision = '8a7f9e07d99b'
branch_labels = None
depends_on = None

moments_groups = [
    {"id": 1, "name": "Appearance"},
    {"id": 2, "name": "Family Life"},
    {"id": 3, "name": "Traditions"},
]

moments_types = [
    {"id": 1, "name": "Online Appearance", "group_id": 1},
    {"id": 2, "name": "Festival Appearance", "group_id": 1},
    {"id": 3, "name": "Radio Appearance", "group_id": 1},
    {"id": 4, "name": "Pop Up Appearance", "group_id": 1},
    {"id": 5, "name": "TV Appearance", "group_id": 1},
    {"id": 6, "name": "Tour Announcement", "group_id": 1},
    {"id": 7, "name": "Tour Date", "group_id": 1},
    {"id": 8, "name": "Tour Support Date", "group_id": 1},
    {"id": 9, "name": "Brand Appearance", "group_id": 1},
    {"id": 10, "name": "Major Appearance", "group_id": 1},
    {"id": 11, "name": "Custom Appearance", "group_id": 1},

    {"id": 12, "name": "Marriage", "group_id": 2},
    {"id": 13, "name": "Child Birthday", "group_id": 2},
    {"id": 14, "name": "Child Announcement", "group_id": 2},
    {"id": 15, "name": "Celebration", "group_id": 2},
    {"id": 16, "name": "Custom Family", "group_id": 2},

    {"id": 17, "name": "Holiday", "group_id": 3},
    {"id": 18, "name": "Season", "group_id": 3},
    {"id": 19, "name": "Custom Tradition", "group_id": 3},
]

moments_categories = [
    {"id": 1, "name": "Personal Life"},
    {"id": 2, "name": "Achievements"},
    {"id": 3, "name": "Culture"},
    {"id": 4, "name": "Anniversary"},
    {"id": 5, "name": "Releases"},
    {"id": 6, "name": "Appearance"},
    {"id": 7, "name": "Family Life"},
    {"id": 8, "name": "Traditions"},
]

def get_table(bind, table_name: str):
    meta = sa.MetaData(bind=bind)
    meta.reflect()
    return meta.tables[table_name]

def upgrade():
    op.create_table('ArtistMomentCategory',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(), nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_ArtistMomentCategory'))
    )
    op.create_table('ArtistMomentTypeGroup',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(), nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_ArtistMomentTypeGroup'))
    )
    op.create_table('ArtistMomentType',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(), nullable=False),
    sa.Column('group_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['group_id'], ['ArtistMomentTypeGroup.id'], name=op.f('fk_ArtistMomentType_group_id_ArtistMomentTypeGroup')),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_ArtistMomentType'))
    )
    op.create_table('ArtistMoment',
    sa.Column('id', sa.BigInteger(), nullable=False),
    sa.Column('name', sa.String(), nullable=False),
    sa.Column('artist_id', sa.Integer(), nullable=False),
    sa.Column('project_id', sa.Integer(), nullable=True),
    sa.Column('date', sa.Date(), nullable=False),
    sa.Column('link', sa.String(), nullable=True),
    sa.Column('notes', sa.String(), nullable=True),
    sa.Column('type_id', sa.Integer(), nullable=True),
    sa.Column('category_id', sa.Integer(), nullable=True),
    sa.Column('created_at', sa.TIMESTAMP(timezone=True), server_default=sa.text('CURRENT_TIMESTAMP'), nullable=False),
    sa.Column('updated_at', sa.TIMESTAMP(timezone=True), server_default=sa.text('CURRENT_TIMESTAMP'), nullable=False),
    sa.Column('create_user_id', sa.Integer(), nullable=True),
    sa.Column('edit_user_id', sa.Integer(), nullable=True),
    sa.Column('metainfo', sa.JSON(), nullable=False, server_default='{}'),
    sa.ForeignKeyConstraint(['artist_id'], ['Artist.id'], name=op.f('fk_ArtistMoment_artist_id_Artist'), ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['category_id'], ['ArtistMomentCategory.id'], name=op.f('fk_ArtistMoment_category_id_ArtistMomentCategory')),
    sa.ForeignKeyConstraint(['create_user_id'], ['User.id'], name=op.f('fk_ArtistMoment_create_user_id_User')),
    sa.ForeignKeyConstraint(['edit_user_id'], ['User.id'], name=op.f('fk_ArtistMoment_edit_user_id_User')),
    sa.ForeignKeyConstraint(['project_id'], ['Project.id'], name=op.f('fk_ArtistMoment_project_id_Project')),
    sa.ForeignKeyConstraint(['type_id'], ['ArtistMomentType.id'], name=op.f('fk_ArtistMoment_type_id_ArtistMomentType')),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_ArtistMoment'))
    )
    op.create_table('ArtistMomentTerritory',
    sa.Column('artist_moment_id', sa.BigInteger(), nullable=False),
    sa.Column('territory_id', sa.BigInteger(), nullable=False),
    sa.ForeignKeyConstraint(['artist_moment_id'], ['ArtistMoment.id'], name=op.f('fk_ArtistMomentTerritory_artist_moment_id_ArtistMoment'), ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['territory_id'], ['Territory.id'], name=op.f('fk_ArtistMomentTerritory_territory_id_Territory'), ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('artist_moment_id', 'territory_id', name=op.f('pk_ArtistMomentTerritory'))
    )

    bind = op.get_bind()

    artist_moment_type_group_table = get_table(bind, "ArtistMomentTypeGroup")
    artist_moment_category_table = get_table(bind, "ArtistMomentCategory")
    artist_moment_type_table = get_table(bind, "ArtistMomentType")

    op.bulk_insert(artist_moment_type_group_table, moments_groups)
    op.bulk_insert(artist_moment_category_table, moments_categories)
    op.bulk_insert(artist_moment_type_table, moments_types)

    bind.execute('''
                CREATE TRIGGER set_timestamp
                BEFORE UPDATE ON "ArtistMoment"
                FOR EACH ROW
                EXECUTE PROCEDURE trigger_set_timestamp();
            ''')

def downgrade():
    op.drop_table('ArtistMomentTerritory')
    op.drop_table('ArtistMoment')
    op.drop_table('ArtistMomentType')
    op.drop_table('ArtistMomentTypeGroup')
    op.drop_table('ArtistMomentCategory')