"""Database level utility class.

Used for constructing and seeding the test SQLite database.
"""

from datetime import datetime
import sys
import uuid

import sqlalchemy

from podcast import config
from podcast.connectors import mysql
from podcast.constants import episode_replication_status
from podcast.constants import stores
from podcast.models import ad_action
from podcast.models import ad_action_comment
from podcast.models import advertisement as advertisement_model
from podcast.models import campaign as campaign_model
from podcast.models import category
from podcast.models import episode
from podcast.models import episode_links as episode_links_model
from podcast.models import episode_replication_status as episode_replication_status_model
from podcast.models import insertion_point
from podcast.models import network
from podcast.models import order as order_model
from podcast.models import participant
from podcast.models import podcast
from podcast.models import podcast_links as podcast_links_model
from podcast.models import podcast_season
from podcast.models import show_family as show_family_model
from podcast.models import transcription as transcription_model
from podcast.models import user
from podcast.models import user_podcast_settings


podcast_data = [
    dict(
        id=1,
        network_id=1,
        title='title',
        description='description',
        slug='slug-123',
        copyright='copyright',
        email='email@domain.dom',
        link='http://domain.dom',
        explicit='clean',
        show_type='episodic',
        language='en',
        created_by=1,
        updated_by=1,
        owner='Bob',
        host='Tom',
        created_date=datetime.now(),
        updated_date=datetime.now(),
        megaphone_id='6bf5bea4-a1d4-11e6-8dea-b334e2aa4710',
        categories=[1, 2],
        uuid=str(uuid.uuid4()),
        apple_id=1234567891,
        show_family_id=1,
        feed_type='public-rss'
    ),
    dict(
        id=2,
        network_id=2,
        title='alpha title 1',
        description='<p>description</p> \r\n1. extraspaces\n\nwith \n \nnewlines. ',
        slug='sony12345',
        copyright='copyright',
        email='email@domain.dom',
        link='http://domain.dom',
        explicit='explicit',
        show_type='serial',
        owner='Sally',
        host='Betty',
        language='en',
        created_by=1,
        updated_by=1,
        created_date=datetime.now(),
        updated_date=datetime.now(),
        megaphone_id='6bf5bea4-a1d4-11e6-8dea-b334e2aa4720',
        categories=[3, 4],
        uuid=str(uuid.uuid4()),
        apple_id=1234567890,
        show_family_id=2,
        feed_type='private-rss'
    ),
    dict(
        id=3,
        network_id=1,
        title='title 3',
        description='description 3',
        slug='slug-3',
        copyright='copyright',
        email='email@domain.dom',
        link='http://domain.dom',
        explicit='clean',
        show_type='episodic',
        language='en',
        created_by=1,
        updated_by=1,
        owner='Bob',
        host='Tom',
        created_date=datetime.now(),
        updated_date=datetime.now(),
        megaphone_id='6bf5bea4-a1d4-11e6-8dea-b334e2aa4730',
        categories=[1, 2],
        uuid=str(uuid.uuid4()),
        apple_id=1234567833,
        show_family_id=1,
        feed_type='apple-subscription',
        channel_id='apple1234567833'
    ),
    dict(
        id=4,
        network_id=2,
        title='title 4',
        description='description 4',
        slug='slug-4',
        copyright='copyright',
        email='email@domain.dom',
        link='http://domain.dom',
        explicit='clean',
        show_type='episodic',
        language='en',
        created_by=1,
        updated_by=1,
        owner='Bob',
        host='Tom',
        created_date=datetime.now(),
        updated_date=datetime.now(),
        megaphone_id='6bf5bea4-a1d4-11e6-8dea-b334e2aa4740',
        categories=[1, 2],
        uuid=str(uuid.uuid4()),
        apple_id=1234567833,
        show_family_id=2,
        feed_type='youtube',
        channel_id='youtube4'
    )
]

podcast_season_data = [
    dict(
        id=1,
        podcast_id=1,
        number=1,
        name='s1',
        external_id='pod_1_season_1'
    ),
    dict(
        id=2,
        podcast_id=1,
        number=2,
        name='s2',
        external_id='pod_1_season_2'
    ),
    dict(
        id=3,
        podcast_id=1,
        number=3,
        name='s3',
        external_id='pod_1_season_3'
    ),
    dict(
        id=4,
        podcast_id=2,
        number=1,
        name='s1',
        external_id='pod_2_season_1'
    ),
    dict(
        id=5,
        podcast_id=2,
        number=4,
        name='s4',
        external_id='pod_2_season_4'
    ),
]


episode_data = [
    dict(
        id=1,
        podcast_id=1,
        title='title',
        description='<p>description.</p>',
        season_id=1,
        season_number=1,
        episode_number=1,
        episode_type='full',
        content='clean',
        created_by=1,
        updated_by=1,
        created_date=datetime.now(),
        updated_date=datetime.now(),
        published_date=datetime(2019, 6, 1, 8, 20, 20),
        megaphone_id='6bf5bea4-a1d4-11e6-8dea-b334e2aa4880',
        megaphone_uid='mpuid',
        draft=False,
        planned_pre_roll_count=1,
        planned_mid_roll_count=1,
        planned_post_roll_count=1,
        is_reviewed=True,
        apple_id=None
    ),
    dict(
        id=2,
        podcast_id=1,
        title='title2',
        description='description2',
        season_id=1,
        season_number=1,
        episode_number=2,
        uuid='abc',
        episode_type='full',
        content='clean',
        created_by=1,
        updated_by=2,
        created_date=datetime.now(),
        updated_date=datetime.now(),
        published_date=datetime(2018, 1, 1, 8, 20, 20),
        megaphone_id=None,
        megaphone_uid=None,
        draft=True,
        planned_pre_roll_count=0,
        planned_mid_roll_count=1,
        planned_post_roll_count=2,
        is_reviewed=True,
        apple_id=None
    ),
    dict(
        id=3,
        podcast_id=2,
        title='Owned by podcast 2',
        description='description3',
        season_id=1,
        season_number=1,
        episode_number=2,
        episode_type='full',
        content='clean',
        created_by=1,
        updated_by=2,
        created_date=datetime.now(),
        updated_date=datetime.now(),
        published_date=datetime(2018, 1, 1, 8, 20, 20),
        megaphone_id='6bf5bea4-a1d4-11e6-8dea-b334e2aa4811',
        megaphone_uid='mpuid2',
        draft=True,
        planned_pre_roll_count=0,
        planned_mid_roll_count=1,
        planned_post_roll_count=2,
        is_reviewed=True,
        apple_id=None
    ),
    dict(
        id=4,
        podcast_id=1,
        title='title four',
        description='description 4',
        season_id=1,
        season_number=1,
        episode_number=2,
        episode_type='full',
        content='clean',
        created_by=1,
        updated_by=2,
        created_date=datetime.now(),
        updated_date=datetime.now(),
        published_date=datetime(2018, 1, 1, 8, 20, 20),
        megaphone_id='6bf5bea4-a1d4-11e6-8dea-b334e2aa4814',
        megaphone_uid='mpuid4',
        draft=True,
        planned_pre_roll_count=0,
        planned_mid_roll_count=1,
        planned_post_roll_count=2,
        is_reviewed=False,
        apple_id=None
    )
]

category_data = [
    dict(
        id=1,
        parent_id=None,
        name='Active Category',
        active=1,
    ),
    dict(
        id=2,
        parent_id=None,
        name='Inactive Category',
        active=0,
    ),
    dict(
        id=3,
        parent_id=None,
        name='Active Category 2',
        active=1,
    )
]

insertion_point_data = [
    dict(
        id=1,
        episode_id=1,
        point_type='mid',
        timecode='3.14',
        count=1
    ),
    dict(
        id=2,
        episode_id=1,
        point_type='post',
        timecode='0',
        count=1
    ),
    dict(
        id=3,
        episode_id=1,
        point_type='pre',
        timecode='0',
        count=1
    )
]

network_data = [
    dict(
        id=1,
        name='Broccoli test',
        megaphone_id='64756b62-7e5c-4728-895a-2f42f8969e14'
    ),
    dict(
        id=2,
        name='Cowhead test',
        megaphone_id='74756b62-7e5c-4728-895a-2f42f8969e15'
    ),
    dict(
        id=3,
        name='Network with no podcast',
        megaphone_id='74756b62-7e5c-4728-895a-2f42f8969e16'
    )
]

ad_action_data = [
    dict(
        id=1,
        title='Cool Ad',
        campaign_id='1',
        order_id='mp1',
        advertisement_id='mpad1',
        roll_type='mid',
        copy_url_path='fake/path.pdf',
        copy_filename='filename.pdf',
        copy_updated_date=datetime(2019, 1, 1),
        due_date=datetime(2019, 1, 1),
        is_deleted=False,
        created_by=1,
        created_date=datetime(2019, 1, 1),
        updated_date=datetime(2019, 1, 1),
        status='new',
        rejection_reason=None,
        is_archived_early=False,
        is_virus_free=True,
        updated_by=4,
        requires_approval=True,
        is_copy_updated=True
    ),
    dict(
        id=2,
        title='Cool Ad2',
        campaign_id='8',
        order_id='mp3',
        advertisement_id='mpad1',
        roll_type='mid',
        copy_url_path='fake/path2.pdf',
        copy_filename='filename.pdf',
        copy_updated_date=datetime(2019, 1, 1),
        due_date=datetime(2019, 1, 1),
        is_deleted=True,
        created_by=1,
        created_date=datetime(2019, 1, 1),
        updated_date=datetime(2019, 1, 1),
        status='new',
        rejection_reason=None,
        is_archived_early=False,
        is_virus_free=True,
        updated_by=4,
        requires_approval=True,
        is_copy_updated=False
    ),
    dict(
        id=3,
        title='Unowned By User',
        campaign_id='1',
        order_id='mp2',
        advertisement_id='mpad1',
        roll_type='mid',
        copy_url_path='fake/path2.pdf',
        copy_filename='filename.pdf',
        copy_updated_date=datetime(2019, 1, 1),
        due_date=datetime(2019, 1, 1),
        is_deleted=True,
        created_by=1,
        created_date=datetime(2019, 1, 1),
        updated_date=datetime(2019, 1, 1),
        status='new',
        rejection_reason=None,
        is_archived_early=False,
        is_virus_free=True,
        updated_by=2,
        requires_approval=True,
        is_copy_updated=False
    ),
    dict(
        id=4,
        title='Unowned By User',
        campaign_id='3',
        order_id='mp1',
        advertisement_id='mpad1',
        roll_type='mid',
        copy_url_path='copies/unique-ad_copy-filename.pdf',
        copy_filename='filename.pdf',
        copy_updated_date=datetime(2019, 1, 1),
        due_date=datetime(2019, 1, 1),
        is_deleted=False,
        created_by=2,
        created_date=datetime(2019, 1, 1),
        updated_date=datetime(2019, 1, 1),
        status='new',
        rejection_reason=None,
        is_archived_early=False,
        is_virus_free=False,
        updated_by=2,
        requires_approval=True,
        is_copy_updated=False
    ),
    dict(
        id=5,
        title='Unowned By User',
        campaign_id='2',
        order_id='mp1',
        advertisement_id='mpad1',
        roll_type='mid',
        copy_url_path='copies/unique-ad_copy-filename.pdf',
        copy_filename='filename.pdf',
        copy_updated_date=datetime(2019, 1, 1),
        due_date=datetime(2019, 1, 1),
        is_deleted=False,
        created_by=1,
        created_date=datetime(2019, 1, 1),
        updated_date=datetime(2019, 1, 1),
        status='completed',
        rejection_reason=None,
        is_archived_early=False,
        is_virus_free=True,
        updated_by=2,
        requires_approval=True,
        is_copy_updated=False
    ),
    dict(
        id=6,
        title='Ad By Deleted Assignee',
        campaign_id='1',
        order_id='mp1',
        advertisement_id='mpad2',
        roll_type='mid',
        copy_url_path='fake/path.pdf',
        copy_filename='filename.pdf',
        copy_updated_date=datetime(2019, 1, 1),
        due_date=datetime(2019, 1, 1),
        is_deleted=False,
        created_by=1,
        created_date=datetime(2019, 1, 1),
        updated_date=datetime(2019, 1, 1),
        status='new',
        rejection_reason=None,
        is_archived_early=False,
        is_virus_free=True,
        updated_by=2,
        requires_approval=False,
        is_copy_updated=False
    ),
    dict(
        id=7,
        title='Ad By Deleted Reporter',
        campaign_id='1',
        order_id='mp1',
        advertisement_id='mpad3',
        roll_type='mid',
        copy_url_path='fake/path.pdf',
        copy_filename='filename.pdf',
        copy_updated_date=datetime(2019, 1, 1),
        due_date=datetime(2019, 1, 1),
        is_deleted=False,
        created_by=9,
        created_date=datetime(2019, 1, 1),
        updated_date=datetime(2019, 1, 1),
        status='new',
        rejection_reason=None,
        is_archived_early=False,
        is_virus_free=True,
        updated_by=2,
        requires_approval=True,
        is_copy_updated=False
    ),
    dict(
        id=8,
        title='Ad having new ad from megaphone',
        campaign_id='campaign_id',
        order_id='order_id',
        advertisement_id='an-ad-id-pre',
        roll_type='pre',
        copy_url_path='fake/path.pdf',
        copy_filename='filename.pdf',
        copy_updated_date=datetime(2019, 1, 1),
        due_date=datetime(2019, 1, 1),
        is_deleted=False,
        created_by=9,
        created_date=datetime(2019, 1, 1),
        updated_date=datetime(2019, 1, 1),
        status='completed',
        rejection_reason=None,
        is_archived_early=False,
        is_virus_free=True,
        updated_by=2,
        requires_approval=True,
        is_copy_updated=False
    ),
    dict(
        id=9,
        title='test deleted order ad ',
        campaign_id='1',
        order_id='order_id',
        advertisement_id='an-ad-id-pre',
        roll_type='pre',
        copy_url_path='fake/path.pdf',
        copy_filename='filename.pdf',
        copy_updated_date=datetime(2019, 1, 1),
        due_date=datetime(2019, 1, 1),
        is_deleted=False,
        created_by=9,
        created_date=datetime(2019, 1, 1),
        updated_date=datetime(2019, 1, 1),
        status='completed',
        rejection_reason=None,
        is_archived_early=False,
        is_virus_free=True,
        updated_by=2,
        requires_approval=True,
        is_copy_updated=False
    )
]

campaign_data = [
    dict(
        id=1,
        name='Random Campaign title',
        megaphone_id='1',
        advertiser_name='Sarahs Business',
        is_deleted=False
    ),
    dict(
        id=2,
        name='Another Campaign title',
        megaphone_id='2',
        advertiser_name='Tonk Bonkleys Hair gel',
        is_deleted=False
    ),
    dict(
        id=3,
        name='Third Campaign title',
        megaphone_id='3',
        advertiser_name='Tonk Bonkleys Hair gel',
        is_deleted=True
    )
]

user_data = [
    dict(
        id=1,
        uuid='auth0-test-user-1',
        email='user_email@theorchard.com',
        name='username',
        active=True,
        language='en',
        profile_id=1,
        organization='orchard',
        role='producer',
        all_networks=False,
    ),
    dict(
        id=2,
        uuid='Billy',
        email='user_admin@theorchard.com',
        name='useradmin',
        active=True,
        language='en',
        profile_id=2,
        organization='sme',
        role='admin',
        all_networks=True,
    ),
    dict(
        id=3,
        uuid='SammyUnactive',
        email='sammy@theorchard.com',
        name='Sammy Tinkle',
        active=False,
        language='en',
        profile_id=3,
        organization='sme',
        role='admin',
        all_networks=True,
    ),
    dict(
        id=4,
        uuid='podcast-admin',
        email='podcast-admin@theorchard.com',
        name='podcast-admin',
        active=True,
        language='en',
        profile_id=0,
        organization='orchard',
        role='admin',
        all_networks=True,
    ),
    dict(
        id=5,
        uuid='auth0-not-admin-user',
        email='not-admin-user@theorchard.com',
        name='not-admin-user',
        active=True,
        language='en',
        profile_id=0,
        organization='orchard',
        role='producer',
        all_networks=False,
    ),
    dict(
        id=6,
        uuid='auth0-network-admin-user',
        email='auth0-network-admin-user@theorchard.com',
        name='network-admin-user',
        active=True,
        language='en',
        profile_id=4,
        organization='orchard',
        role='network-admin',
        all_networks=False,
    ),
    dict(
        id=7,
        uuid='just-some-user',
        email='just-some-user@theorchard.com',
        name='user-other-network',
        active=True,
        language='en',
        profile_id=5,
        organization='orchard',
        role='producer',
        all_networks=False,
    ),
    dict(
        id=8,
        uuid='two-network-user',
        email='two-network-user@theorchard.com',
        name='two-network-user',
        active=True,
        language='en',
        profile_id=6,
        organization='orchard',
        role='producer',
        all_networks=False,
    ),
    dict(
        id=9,
        uuid='deleted-user',
        email='deleted-user@theorchard.com',
        name='deleted-user',
        active=False,
        language='en',
        profile_id=6,
        organization='orchard',
        role='producer',
        all_networks=False,
    ),
    dict(
        id=10,
        uuid='oa:9',
        email='automation@theorchard.com',
        name='automation-admin',
        active=True,
        language='en',
        profile_id=2,
        organization='orchard',
        role='admin',
        all_networks=True,
    ),
    dict(
        id=11,
        uuid='podcast-level-user',
        email='podcast-level-user@theorchard.com',
        name='podcast-level-user',
        active=True,
        language='en',
        profile_id=6,
        organization='orchard',
        role='producer',
        all_networks=False,
    ),
    dict(
        id=12,
        uuid='network-level-user',
        email='network-level-user@theorchard.com',
        name='network-level-user',
        active=True,
        language='en',
        profile_id=6,
        organization='orchard',
        role='network-admin',
        all_networks=False,
    ),
    dict(
        id=13,
        uuid='network-admin-all',
        email='network-admin-all@theorchard.com',
        name='network-admin-all',
        active=True,
        language='en',
        profile_id=6,
        organization='orchard',
        role='network-admin',
        all_networks=True,
    ),
    dict(
        id=14,
        uuid='read-level-producer',
        email='read-level-producer@theorchard.com',
        name='read-level-producer',
        active=True,
        language='en',
        profile_id=6,
        organization='orchard',
        role='read-only-producer',
        all_networks=True,
    )
]

ad_action_assignees = [
    dict(user_id=1, ad_action_id=1),
    dict(user_id=1, ad_action_id=2),
    dict(user_id=1, ad_action_id=3),
    dict(user_id=2, ad_action_id=4),
    dict(user_id=1, ad_action_id=5),
    dict(user_id=9, ad_action_id=6),
    dict(user_id=1, ad_action_id=7)
]

ad_action_comments = [
    dict(
        id=1,
        ad_action_id=1,
        comment='first test comment',
        created_by=1,
        created_date=datetime(2019, 1, 1),
        updated_by=1,
        updated_date=None,
        is_deleted=False
    ),
    dict(
        id=2,
        ad_action_id=1,
        comment='second test comment',
        created_by=2,
        created_date=datetime(2019, 1, 1),
        updated_by=2,
        updated_date=None,
        is_deleted=False
    ),
    dict(
        id=3,
        ad_action_id=1,
        comment='third test comment',
        created_by=6,
        created_date=datetime(2019, 1, 1),
        updated_by=6,
        updated_date=None,
        is_deleted=False
    ),
    dict(
        id=4,
        ad_action_id=1,
        comment='deleted test comment',
        created_by=6,
        created_date=datetime(2019, 1, 1),
        updated_by=6,
        updated_date=None,
        is_deleted=True
    )
]

user_networks = [
    dict(user_id=6, network_id=1),
    dict(user_id=1, network_id=1),
    dict(user_id=7, network_id=2),
    dict(user_id=8, network_id=1),
    dict(user_id=8, network_id=2),
    dict(user_id=12, network_id=3)
]

user_podcasts = [
    dict(user_id=11, podcast_id=1),
    dict(user_id=6, podcast_id=2)
]

user_show_families = [
    dict(user_id=11, show_family_id=1)
]

user_podcast_favorite = [
    dict(user_id=6, podcast_id=1),
    dict(user_id=9, podcast_id=1),
    dict(user_id=5, podcast_id=2)
]

user_settings = [
    dict(user_id=1, ad_notifications=True, podcast_notifications=False),
    dict(user_id=2, ad_notifications=True, podcast_notifications=True),
    dict(user_id=3, ad_notifications=False, podcast_notifications=True),
    dict(user_id=5, ad_notifications=False, podcast_notifications=False),
    dict(user_id=6, ad_notifications=True, podcast_notifications=True),
    dict(user_id=7, ad_notifications=True, podcast_notifications=True),
    dict(user_id=8, ad_notifications=True, podcast_notifications=True)
]

participants = [
    dict(podcast_id=1, name='Bill', role='other', participant_id=1)
]

orders = [
    dict(
        id=1, megaphone_id='mp1', name='order 1', campaign_megaphone_id='1',
        start_at=datetime(2016, 1, 1), end_at=datetime(2050, 1, 1), enabled=1,
        pos=1, is_deleted=False
    ),
    dict(
        id=2, megaphone_id='mp2', name='order 2', campaign_megaphone_id='1',
        start_at=datetime(2016, 1, 1), end_at=datetime(2050, 1, 1), enabled=1,
        pos=2, is_deleted=False
    ),
    dict(
        id=3, megaphone_id='mp3', name='order 3', campaign_megaphone_id='1',
        start_at=datetime(2016, 1, 1), end_at=datetime(2050, 1, 1), enabled=1,
        pos=2, is_deleted=False
    ),
    dict(
        id=4, megaphone_id='mp4', name='order 4', campaign_megaphone_id='1',
        start_at=datetime(2016, 1, 1), end_at=datetime(2050, 1, 1), enabled=1,
        pos=2, is_deleted=True
    )
]

order_networks = [
    dict(campaign_order_id=1, network_id=1),
    dict(campaign_order_id=1, network_id=2),
    dict(campaign_order_id=2, network_id=2),
    dict(campaign_order_id=3, network_id=2)
]

advertisements = [
    dict(
        id=1, megaphone_id='mpad1', insertion_point='pre', pos=0,
        audio_file='', campaign_order_megaphone_id='mp1', name='advertisement 1'
    ),
    dict(
        id=2, megaphone_id='mpad2', insertion_point='mid', pos=1,
        audio_file='yow', campaign_order_megaphone_id='mp1', name='advertisement 2'
    ),
    dict(
        id=3, megaphone_id='mpad3', insertion_point='pre', pos=0,
        audio_file='yow', campaign_order_megaphone_id='mp2', name='advertisement 3'
    ),
    dict(
        id=4, megaphone_id='mpad4', insertion_point='mid', pos=1,
        audio_file='yow', campaign_order_megaphone_id='mp2', name='advertisement 4'
    ),
    dict(
        id=5, megaphone_id='mpad5', insertion_point='post', pos=1,
        audio_file='yow', campaign_order_megaphone_id='mp2', name='advertisement 5'
    ),
    dict(
        id=6, megaphone_id='mpad6', insertion_point='mid', pos=1,
        audio_file='yow', campaign_order_megaphone_id='mp3', name='advertisement 6'
    )
]

transcriptions = [
    dict(
        id=1,
        episode_id=1,
        created_by=1,
        updated_by=2,
        created_date=datetime(2019, 1, 1),
        updated_date=datetime(2019, 1, 1),
        transcript=None,
        is_transcript_processing=False
    )
]


stores = [
    dict(
        id=1,
        name=stores.APPLE_PODCAST,
    ),
    dict(
        id=2,
        name=stores.SPOTIFY,
    ),
    dict(
        id=3,
        name=stores.STITCHER,
    ),
    dict(
        id=4,
        name=stores.CASTBOX,
    ),
    dict(
        id=5,
        name=stores.POCKET_CASTS
    ),
    dict(
        id=6,
        name=stores.RADIO_PUBLIC
    ),
    dict(
        id=7,
        name=stores.PODCAST_ADDICT
    ),
    dict(
        id=8,
        name=stores.PLAYER_FM
    ),
    dict(
        id=9,
        name=stores.GOOGLE_PODCAST
    ),
    dict(
        id=10,
        name=stores.DEEZER
    ),
    dict(
        id=11,
        name=stores.IHEART
    ),
    dict(
        id=12,
        name=stores.WEBSITE_EMBED_CODE
    ),
    dict(
        id=13,
        name=stores.PANDORA
    )
]

podcast_links = [
    dict(
        id=1,
        store_id=1,
        podcast_id=1,
        link='https://www.link.com',
    ),
    dict(
        id=2,
        store_id=2,
        podcast_id=1,
        link='https://www.spotify.com/abc',
    ),
    dict(
        id=3,
        store_id=2,
        podcast_id=2,
        link='https://www.spotify.com/xyz',
    )
]

episode_links = [
    dict(
        id=1,
        store_id=2,
        podcast_id=2,
        episode_id=3,
        link='https://www.spotify.com/xyz/episode/123',
    ),
    dict(
        id=2,
        store_id=1,
        podcast_id=1,
        episode_id=1,
        link='https://apple-link/xyz/12434/episode/=i?1234567890122',
    )
]

order_episodes = [
    dict(
        campaign_order_id='mp1',
        episode_id=1,
    ),
    dict(
        campaign_order_id='mp2',
        episode_id=1,
    ),
    dict(
        campaign_order_id='mp4',
        episode_id=1,
    )
]

order_podcasts = [
    dict(
        campaign_order_id='mp1',
        podcast_id=2,
    ),
    dict(
        campaign_order_id='mp3',
        podcast_id=2,
    ),
]

show_family_data = [
    dict(
        network_id=1,
        title='show family title 1',
        created_by=1,
        updated_by=1,
        created_date=datetime(2023, 1, 1),
        updated_date=datetime(2023, 1, 1),
        is_deleted=0
    ),
    dict(
        network_id=2,
        title='second show family',
        created_by=1,
        updated_by=1,
        created_date=datetime(2023, 2, 2),
        updated_date=datetime(2023, 2, 2),
        is_deleted=0
    ),
    dict(
        network_id=1,
        title='show family title 3',
        created_by=1,
        updated_by=1,
        created_date=datetime(2023, 3, 3),
        updated_date=datetime(2023, 3, 3),
        is_deleted=1
    ),
    dict(
        network_id=1,
        title='4th show family',
        created_by=1,
        updated_by=1,
        created_date=datetime(2023, 4, 4),
        updated_date=datetime(2023, 4, 4),
        is_deleted=0
    )
]

episode_replication_status_data = [
    dict(
        id=1,
        original_podcast_id=1,
        podcast_id=4,
        original_episode_id=4,
        episode_id=5,
        replication_type='bulk',
        status=episode_replication_status.INITIATED,
        is_viewed=0
    ),
    dict(
        id=2,
        original_podcast_id=2,
        original_episode_id=3,
        podcast_id=5,
        episode_id=None,
        replication_type='single',
        status=episode_replication_status.EPISODE_CREATION_FAILED,
        is_viewed=0
    ),
    dict(
        id=3,
        original_podcast_id=2,
        original_episode_id=3,
        podcast_id=6,
        episode_id=None,
        replication_type='single',
        status=episode_replication_status.INITIATED,
        is_viewed=0
    ),
    dict(
        id=4,
        original_podcast_id=1,
        podcast_id=7,
        original_episode_id=4,
        episode_id=6,
        replication_type='bulk',
        status=episode_replication_status.EPISODE_CREATED,
        is_viewed=0
    ),
    dict(
        id=5,
        original_podcast_id=1,
        podcast_id=4,
        original_episode_id=4,
        episode_id=7,
        replication_type='bulk',
        status=episode_replication_status.EPISODE_CREATION_FAILED,
        is_viewed=0
    ),
    dict(
        id=6,
        original_podcast_id=1,
        podcast_id=4,
        original_episode_id=4,
        episode_id=7,
        replication_type='single',
        status=episode_replication_status.EPISODE_CREATION_FAILED,
        is_viewed=0
    ),
    dict(
        id=7,
        original_podcast_id=2,
        podcast_id=4,
        original_episode_id=4,
        episode_id=7,
        replication_type='single',
        status=episode_replication_status.EPISODE_CREATION_FAILED,
        is_viewed=1
    ),
    dict(
        id=8,
        original_podcast_id=1,
        podcast_id=4,
        original_episode_id=4,
        episode_id=6,
        replication_type='bulk',
        status=episode_replication_status.INITIATED,
        is_viewed=1
    )
]


def create_tables():
    """Create tables defined in podcast.models."""
    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        return

    if config.PODCAST_DB_URL != 'sqlite://':
        return

    mysql.BaseModel.metadata.create_all(mysql.podcast_db_engine)


def insert_episodes(session, data, commit=True):
    """Insert test data into episodes table."""
    session.execute(episode.Episode.__table__.insert().values(data))
    session.commit()


def seed_data():
    """Insert test data into podcast table."""
    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        return

    if config.PODCAST_DB_URL != 'sqlite://':
        return

    with mysql.pod_db_session() as session:
        try:
            session.execute(network.Network.__table__.insert().values(
                network_data))
            session.execute(podcast.Podcast.__table__.insert().values(
                podcast_data))
            session.execute(podcast_season.PodcastSeason.__table__.insert().values(
                podcast_season_data))
            insert_episodes(session, episode_data, commit=False)
            session.execute(category.Category.__table__.insert().values(
                category_data))
            session.execute(insertion_point.InsertionPoint.__table__.insert().values(
                insertion_point_data
            ))
            session.execute(ad_action.AdAction.__table__.insert().values(
                ad_action_data
            ))
            session.execute(user.User.__table__.insert().values(
                user_data
            ))
            session.execute(user.user_network_table.insert().values(
                user_networks
            ))
            session.execute(user.user_podcast_table.insert().values(
                user_podcasts
            ))
            session.execute(user.user_show_family_table.insert().values(
                user_show_families
            ))
            session.execute(user.user_podcast_favorite_table.insert().values(
                user_podcast_favorite
            ))
            session.execute(participant.Participant.__table__.insert().values(
                participants
            ))
            session.execute(user_podcast_settings.UserPodcastSettings.__table__.insert().values(
                user_settings
            ))
            session.execute(campaign_model.Campaign.__table__.insert().values(
                campaign_data
            ))
            session.execute(order_model.Order.__table__.insert().values(
                orders
            ))
            session.execute(order_model.order_network_table.insert().values(
                order_networks
            ))
            session.execute(order_model.order_episode_table.insert().values(
                order_episodes
            ))
            session.execute(order_model.order_podcast_table.insert().values(
                order_podcasts
            ))
            session.execute(advertisement_model.Advertisement.__table__.insert().values(
                advertisements
            ))
            session.execute(transcription_model.Transcription.__table__.insert().values(
                transcriptions
            ))
            session.execute(podcast_links_model.Store.__table__.insert().values(
                stores
            ))
            session.execute(podcast_links_model.PodcastLink.__table__.insert().values(
                podcast_links
            ))
            session.execute(episode_links_model.EpisodeLink.__table__.insert().values(
                episode_links
            ))
            session.execute(ad_action.ad_action_assignee_table.insert().values(
                ad_action_assignees
            ))
            session.execute(ad_action_comment.AdActionComment.__table__.insert().values(
                ad_action_comments
            ))
            session.execute(show_family_model.ShowFamily.__table__.insert().values(
                show_family_data
            ))
            session.execute(episode_replication_status_model.EpisodeReplicationStatus.__table__.insert().values(
                episode_replication_status_data
            ))
        except sqlalchemy.exc.CompileError as error:
            print('------ ERROR: Seeding error: SQL compile:', error)
        except sqlalchemy.exc.IntegrityError as error:
            print('------ ERROR: Seeding error: Integrity error:', error)
        except Exception:
            print('------ ERROR: Unexpected error while seeding:', sys.exc_info()[0])

        session.commit()


def drop_tables():
    """Delete tables defined in podcast.models."""
    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        return

    if config.PODCAST_DB_URL != 'sqlite://':
        return

    mysql.BaseModel.metadata.drop_all(mysql.podcast_db_engine)