"""Utility functions to support database interactions in tests."""
import re
from copy import deepcopy
from functools import wraps
import sys

from backend import config
from backend.connectors import mysql
from backend.constants import mysql as mysql_const
from backend.models.audio_attribute import AudioAttributes
from backend.models.audio_attribute import TrackAudioAttributesEdits
from backend.models.audio_attribute import AudioAttributesSuggestionKeywords
from backend.models.audio_attribute import TrackAudioAttributes
from backend.models.audio_attribute import TrackAudioAttributesChangelog
from backend.models.genre import Genre
from backend.models.language import Language
from backend.models.performer import PerformerRole
from backend.models.rights_attribute import RightsAttributes
from backend.models.rights_attribute import TrackRightsAttributesEdits
from backend.models.rights_attribute import RightsAttributesSuggestionKeywords
from backend.models.rights_attribute import TrackRightsAttributes
from backend.models.rights_attribute import TrackRightsAttributesChangelog
from backend.models.rights_attribute import VendorRightsAttributes
from backend.models.subgenre import Subgenre, ReleaseSubgenre
from backend.models.track import ArtistInfo
from backend.models.track import Project
from backend.models.track import ReleaseArtist
from backend.models.track import Releases
from backend.models.track import Track
from backend.models.track import TrackArtist
from backend.models.track import TrackMasterRights
from backend.models.track import TrackProducerNationality
from backend.models.track import TrackPublisher
from backend.models.track import TrackWriter
from backend.models.track_sample import TrackSample
from backend.models.track_sample import TrackSampleArtists
from tests.testutils.fixtures import language_fixture
from tests.testutils.fixtures import track_sample_fixture
from tests.testutils.fixtures.performer_roles_fixture import performer_roles
from tests.testutils.seed.audio_attributes_seed import \
    audio_attributes_seed_data
from tests.testutils.seed.audio_attributes_suggestion_keywords_seed import \
    audio_attributes_suggestion_keywords_seed_data
from tests.testutils.seed.genre_seed import genre_seed_data
from tests.testutils.seed.artist_info_seed import artist_info_seed_data
from tests.testutils.seed.project_seed import project_seed_data
from tests.testutils.seed.release_artist_seed import release_artist_seed_data
from tests.testutils.seed.releases_seed import releases_seed_data
from tests.testutils.seed.rights_attribute_suggestion_keywords_seed import \
    rights_attributes_suggestion_keywords_seed_data
from tests.testutils.seed.rights_attributes_seed import \
    rights_attributes_seed_data
from tests.testutils.seed.track_artist_seed import track_artist_seed_data
from tests.testutils.seed.track_audio_attributes_changelog_seed import \
    track_audio_attributes_changelog_seed_data
from tests.testutils.seed.track_audio_attributes_seed import \
    track_audio_attributes_seed_data
from tests.testutils.seed.track_audio_attributes_edits_seed import \
    track_audio_attributes_edits_seed_data
from tests.testutils.seed.track_master_rights_seed import \
    track_master_rights_seed_data
from tests.testutils.seed.track_producer_natl_seed import \
    track_producer_natl_seed_data
from tests.testutils.seed.track_publisher_seed import track_publisher_seed_data
from tests.testutils.seed.track_rights_attributes_changelog_seed import \
    track_rights_attributes_changelog_seed_data
from tests.testutils.seed.track_rights_attributes_seed import \
    track_rights_attributes_seed_data
from tests.testutils.seed.track_rights_attributes_edits_seed import \
    track_rights_attributes_edits_seed_data
from tests.testutils.seed.track_seed import track_seed_data
from tests.testutils.seed.track_writer_seed import track_writer_seed_data
from tests.testutils.seed.vendor_rights_attributes import \
    vendor_rights_attributes_seed_data
from tests.testutils.seed.subgenre_seed import subgenre_seed_data
from tests.testutils.seed.release_subgenre_seed import release_subgenre_seed_data

from sqlalchemy import event
from sqlalchemy.engine import Engine

def create_tables():
    """Create the track and related tables."""
    with mysql.db_session() as session:
        exit_if_not_test_environment(session)
    mysql.BaseModel.metadata.create_all(mysql.db_engine)
    mysql.OwsTrackBaseModel.metadata.create_all(mysql.ows_track_db_engine)


def seed_tables():
    """Seed the track and related tables."""
    with mysql.db_session() as session:
        exit_if_not_test_environment(session)
        session.bulk_insert_mappings(Track, track_seed_data)
        session.bulk_insert_mappings(
            TrackMasterRights, track_master_rights_seed_data)
        session.bulk_insert_mappings(
            TrackProducerNationality, track_producer_natl_seed_data)
        session.bulk_insert_mappings(
            TrackArtist, track_artist_seed_data)
        session.bulk_insert_mappings(
            TrackPublisher, track_publisher_seed_data)
        session.bulk_insert_mappings(
            TrackWriter, track_writer_seed_data)
        session.bulk_insert_mappings(
            AudioAttributes, audio_attributes_seed_data)
        session.bulk_insert_mappings(
            AudioAttributesSuggestionKeywords,
            audio_attributes_suggestion_keywords_seed_data)
        session.bulk_insert_mappings(
            TrackAudioAttributesChangelog,
            track_audio_attributes_changelog_seed_data)
        session.bulk_insert_mappings(
            TrackAudioAttributes, track_audio_attributes_seed_data)
        session.bulk_insert_mappings(
            TrackAudioAttributesEdits, track_audio_attributes_edits_seed_data)
        session.bulk_insert_mappings(
            VendorRightsAttributes, vendor_rights_attributes_seed_data)
        session.bulk_insert_mappings(
            ArtistInfo, artist_info_seed_data)
        session.bulk_insert_mappings(
            Releases, releases_seed_data)
        session.bulk_insert_mappings(
            ReleaseArtist, release_artist_seed_data)
        session.bulk_insert_mappings(
            Project, project_seed_data)
        session.bulk_insert_mappings(
            RightsAttributes, rights_attributes_seed_data
        )
        session.bulk_insert_mappings(
            RightsAttributesSuggestionKeywords,
            rights_attributes_suggestion_keywords_seed_data
        )
        session.bulk_insert_mappings(
            TrackRightsAttributesChangelog,
            track_rights_attributes_changelog_seed_data
        )
        session.bulk_insert_mappings(
            TrackRightsAttributes,
            track_rights_attributes_seed_data
        )
        session.bulk_insert_mappings(
            TrackRightsAttributesEdits,
            track_rights_attributes_edits_seed_data
        )
        session.bulk_insert_mappings(
            Genre, genre_seed_data)
        session.bulk_insert_mappings(
            Subgenre, subgenre_seed_data)
        session.bulk_insert_mappings(
            ReleaseSubgenre, release_subgenre_seed_data)

    with mysql.ows_track_db_session() as session:
        session.bulk_insert_mappings(PerformerRole, performer_roles)
        session.bulk_insert_mappings(
            TrackSample, [track_sample_fixture.track_sample_with_tuid])
        session.bulk_insert_mappings(
            TrackSampleArtists, track_sample_fixture.sample_artist_with_sample)


def seed_track_sample_tables(tuid=None):
    """Seed the track sample and artist tables."""
    with mysql.ows_track_db_session() as session:
        sample = deepcopy(track_sample_fixture.track_sample_with_tuid)
        if tuid:
            sample['unique_track_id'] = tuid
        session.bulk_insert_mappings(TrackSample, [sample])
        session.bulk_insert_mappings(
            TrackSampleArtists, track_sample_fixture.sample_artist_with_sample)


def drop_tables():
    """Drop track and related tables."""
    with mysql.db_session() as session:
        exit_if_not_test_environment(session)
    mysql.BaseModel.metadata.drop_all(mysql.db_engine)
    mysql.OwsTrackBaseModel.metadata.drop_all(mysql.ows_track_db_engine)


def merge_model_objects(model_objects, db_name=mysql_const.ART_RELATIONS):
    """Merge list of SQLAlchemy Model objects into database."""
    with mysql.db_session(db_name) as session:
        exit_if_not_test_environment(session)
        for model_object in model_objects:
            session.merge(model_object)


def merge_ows_track_model_objects(model_objects):
    """Merge list of SQLAlchemy Model objects into track model database."""
    return merge_model_objects(model_objects, db_name=mysql_const.OWS_TRACK)


def test_schema(function):
    """Create and tear down the test DB schema around a function call.

    Args:
        Function (func): the function to be called after creating the test
        schema.

    Returns:
        Function: The decorated function.
    """
    @wraps(function)
    def call_function_within_db_context(*args, **kwargs):
        create_tables()
        seed_tables()

        try:
            function_return = function(*args, **kwargs)
        finally:
            drop_tables()

        return function_return

    return call_function_within_db_context


def test_schema_no_seed(function):
    """Create and tear down the test DB schema around a function call.

    Args:
        Function (func): the function to be called after creating the test
        schema.

    Returns:
        Function: The decorated function.
    """
    @wraps(function)
    def call_function_within_db_context(*args, **kwargs):
        create_tables()

        # Seeding is done for read-only tables
        with mysql.ows_track_db_session() as session:
            session.bulk_insert_mappings(PerformerRole, performer_roles)
        with mysql.db_session() as session:
            session.bulk_insert_mappings(Language, language_fixture.language)

        try:
            function_return = function(*args, **kwargs)
        finally:
            drop_tables()

        return function_return

    return call_function_within_db_context


# MySQL is only allowed for tests when pointed at a local/container instance,
# never a real QA/Prod host. This preserves the original safety intent of
# never running create_all/drop_all against a live database.
TEST_MYSQL_HOSTS = frozenset({'127.0.0.1', 'localhost', 'mysql-test'})


def exit_if_not_test_environment(session):
    """For safety, only run tests in the test environment against a test DB.

    Tests may point at SQLite (default) or a local/container MySQL. Exit
    immediately otherwise.
    """
    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        sys.exit('Environment must be set to {}.'.format(
            config.TEST_ENVIRONMENT))

    url = session.bind.url
    drivername = url.drivername
    if 'sqlite' in drivername:
        return
    if 'mysql' in drivername and (url.host or '') in TEST_MYSQL_HOSTS:
        return

    sys.exit('Tests must point to sqlite or a local/container mysql database.')


@event.listens_for(Engine, "connect")
def sqlite_regexp(connection, record):
    # MySQL has a native REGEXP operator, so the shim is only needed for
    # SQLite. create_function is a SQLite-only DBAPI method.
    if not hasattr(connection, 'create_function'):
        return

    def regexp(expr, item):
        if item is None:
            return False
        reg = re.compile(expr, re.IGNORECASE)
        return reg.search(item) is not None
    connection.create_function("REGEXP", 2, regexp)


@event.listens_for(Engine, "connect")
def relax_mysql_test_session(connection, record):
    # The historical test backend (SQLite) enforces neither foreign keys nor
    # strict typing, so the minimal seed/fixture data is not referentially
    # complete and some fixtures use loose values (e.g. a string in an integer
    # column). Match that permissiveness on the test MySQL so the suite
    # exercises the MySQL dialect (CONCAT, native REGEXP, column types, the
    # query planner, ...) without rewriting hundreds of fixtures. Only applies
    # to the test MySQL: this module is imported by tests only, and SQLite
    # connections are skipped.
    if hasattr(connection, 'create_function'):
        return  # SQLite connection
    cursor = connection.cursor()
    # Disable FK enforcement and strict mode (also clears ONLY_FULL_GROUP_BY)
    # to mirror SQLite's permissiveness.
    cursor.execute('SET SESSION foreign_key_checks=0')
    cursor.execute("SET SESSION sql_mode=''")
    cursor.close()