"""yt_video_regressions

Revision ID: 4f54faf809b3
Revises: d442e6552298
Create Date: 2020-09-01 13:14:31.570508

"""

# revision identifiers, used by Alembic.
revision = '4f54faf809b3'
down_revision = 'd442e6552298'
branch_labels = None
depends_on = None

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


def upgrade():
    # We need a function marked immutable in order to use in generated columns. This
    op.execute('''
create function iso_interval_to_seconds(text) returns integer
  immutable
  strict
  language sql
as
$$
select round(extract(epoch from $1::interval))::integer
$$;

    ''')
    op.add_column('yt_videos', sa.Column('api_data', postgresql.JSONB(), nullable=True))
    op.add_column('yt_videos', sa.Column('duration', sa.Integer(), sa.Computed("""iso_interval_to_seconds(api_data->'contentDetails'->>'duration')"""), nullable=True))
    op.add_column('yt_videos', sa.Column('is_licensed', sa.Boolean(), sa.Computed("""(api_data->'contentDetails'->>'licensedContent') = 'true'"""), nullable=True))
    op.add_column('yt_videos', sa.Column('description', sa.Text(), sa.Computed("""api_data->'snippet'->>'description'"""), nullable=True))
    op.add_column('yt_videos', sa.Column('text_language', sa.Text(), sa.Computed("""api_data->'snippet'->>'defaultLanguage'"""), nullable=True))
    op.add_column('yt_videos', sa.Column('audio_language', sa.Text(), sa.Computed("""api_data->'snippet'->>'defaultAudioLanguage'"""), nullable=True))


def downgrade():
    op.drop_column('yt_videos', 'api_data')
    op.execute('drop function iso_interval_to_seconds(text)')