"""
Database operations utlity
"""
from sqlalchemy import MetaData
from sqlalchemy import Table
from bulkperformancerights import config
from bulkperformancerights.connectors import mysql
from bulkperformancerights.models.job_status import JobStatus
from bulkperformancerights.models.artist_info import ArtistInfo
from bulkperformancerights.models.country import Country
from bulkperformancerights.models.image_assets import ImageAssets
from bulkperformancerights.models.orchadmin_users import OrchadminUsers
from bulkperformancerights.models.release import Release
from bulkperformancerights.models.subaccount import Subaccount
from bulkperformancerights.models.track import Track
from bulkperformancerights.models.track_writer import TrackWriter
from bulkperformancerights.models.vendor import Vendor
from bulkperformancerights.models.vendor_logo import VendorLogo
from utils.seed import artist_info
from utils.seed import country
from utils.seed import image_assets
from utils.seed import orchadmin_users
from utils.seed import release
from utils.seed import subaccount
from utils.seed import track
from utils.seed import track_writer
from utils.seed import vendor
from utils.seed import vendor_logo



ar_session = mysql.art_relations_session()


def create_tables():
    """Create tables associated with this application

    If db schema does not match models, drop and recreate
    this is for DEV/TEST ONLY

    Note:
        you will lose your records
    """

    if config.environment == config.TEST_ENVIRONMENT:
        #  test environment never has any tables, in mem only
        mysql.BaseModel.metadata.create_all(mysql.engine)
        mysql.ArtRelationsBaseModel.metadata.drop_all(mysql.ar_engine)
        mysql.ArtRelationsBaseModel.metadata.create_all(mysql.ar_engine)
        seed_art_relations()
    elif config.environment == config.DEV_ENVIRONMENT:
        # convenience method will update your sqlite db to match models
        sync_status_db_with_models()
        mysql.ArtRelationsBaseModel.metadata.drop_all(mysql.ar_engine)
        mysql.ArtRelationsBaseModel.metadata.create_all(mysql.ar_engine)
        seed_art_relations()
    else:
        raise OSError('create_tables() should only be run against dev/test!')


def sync_status_db_with_models():
    """Keep database(dev) in sync with models

    This is purely for convenience and should only be used in DEV_ENVIRONMENT

        Todo(OrCharles):
            retool for multiple tables
    """

    try:
        job_status_in_db = Table(JobStatus.__table__.name, MetaData(),
                                 autoload=True, autoload_with=mysql.engine)
        job_status_db_def = [c.name for c in job_status_in_db.columns]
        job_status_model_def = [c.name for c in JobStatus.__table__.columns]
        # TODO(OrCharles) compare each column's type (or compare computed ddl)
        tbl_diff = list(set(job_status_model_def) - set(job_status_db_def))
    except:
        # TODO(OrCharles) figure out how to call exists() without exception
        # and re-do this logic
        tbl_diff = [0]
    if len(tbl_diff) > 0:
        print('first install or detected change, drop &/or create')
        try:
            JobStatus.__table__.create(bind=mysql.engine)
        except:
            print('exception caught, assuming exists drop&create')
            JobStatus.__table__.drop(bind=mysql.engine)
            JobStatus.__table__.create(bind=mysql.engine)


def seed_art_relations():
    """Helper method to seed data to dev/test art_relations

        enables local work and tests to run fully integrated against sqlite
        Todo(OrCharles):
            do away with the copy pasta
    """

    ar_session.execute(ArtistInfo.__table__.insert().values(artist_info.seed_data))
    ar_session.execute(Country.__table__.insert().values(country.seed_data))
    ar_session.execute(ImageAssets.__table__.insert().values(image_assets.seed_data))
    ar_session.execute(OrchadminUsers.__table__.insert().values(orchadmin_users.seed_data))
    ar_session.execute(Release.__table__.insert().values(release.seed_data))
    ar_session.execute(Subaccount.__table__.insert().values(subaccount.seed_data))
    ar_session.execute(TrackWriter.__table__.insert().values(track_writer.seed_data))
    ar_session.execute(Track.__table__.insert().values(track.seed_data))
    ar_session.execute(Vendor.__table__.insert().values(vendor.seed_data))
    ar_session.execute(VendorLogo.__table__.insert().values(vendor_logo.seed_data))
    ar_session.commit()