"""project_isrcs_view

Revision ID: e8289c21f9a1
Revises: 6d5d992b7d4a
Create Date: 2023-06-13 13:57:26.904193

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision = 'e8289c21f9a1'
down_revision = '6d5d992b7d4a'
branch_labels = None
depends_on = None


def upgrade():
    bind = op.get_bind()
    bind.execute('''
            CREATE MATERIALIZED VIEW projects_isrcs AS
                SELECT
                  CONCAT('"', coalesce("Project".name, "Project".ccp_title, "Project".prs_title), '"') as project_name,
                  "ProductTrack".isrc as project_isrc,
                  "Label".name as label_name,
                  "Label".country as label_country
                FROM "Project"
                JOIN "Label" ON "Project".label_id = "Label".id and ("Label".country = 'UK' or "Label".name = 'Columbia Records')
                LEFT OUTER JOIN "ProjectProductFamily" on "Project".id = "ProjectProductFamily".project_id
                LEFT OUTER JOIN "ProductFamily" on "ProjectProductFamily".product_family_id = "ProductFamily".id
                LEFT OUTER JOIN "ProductTrack" on "ProductFamily".id = "ProductTrack".product_id
                ORDER BY COALESCE("Project".name, "Project".ccp_title, "Project".prs_title);
        ''')
    bind.execute('''REFRESH MATERIALIZED VIEW projects_isrcs;''')


def downgrade():
    bind = op.get_bind()
    bind.execute('''DROP MATERIALIZED VIEW IF EXISTS projects_isrcs;''')