"""Conftest."""

import pytest
import paramiko

import io

from lambdacommon import util
from vector_utils.connections import connection_info
from tests import fixtures


def reset_dd_db():
    """Reset the database to a clean state."""
    with util.dd_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute('SET FOREIGN_KEY_CHECKS=0')
            cursor.execute('TRUNCATE TABLE delivery_batch')
            cursor.execute('TRUNCATE TABLE delivery_batch_detail')
            cursor.execute('TRUNCATE TABLE job_priority')
            cursor.execute('TRUNCATE TABLE customer_master_master')
            cursor.execute('TRUNCATE TABLE encoding_queue')
            cursor.execute('TRUNCATE TABLE encoding_queue_detail')
            cursor.execute('TRUNCATE TABLE dms_delivery_spec')
            cursor.execute('TRUNCATE TABLE physical_location')
            cursor.execute('TRUNCATE TABLE asset_type')
            cursor.execute('TRUNCATE TABLE storage')
            cursor.execute('TRUNCATE TABLE storage_drive')
            cursor.execute('TRUNCATE TABLE storage_drive_asset_folder')
            cursor.execute('SET FOREIGN_KEY_CHECKS=1')
        conn.commit()


def reset_ar_db():
    """Reset the database to a clean state."""
    with util.ar_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute('SET FOREIGN_KEY_CHECKS=0')
            cursor.execute('TRUNCATE TABLE product_type')
            cursor.execute('TRUNCATE TABLE project')
            cursor.execute('TRUNCATE TABLE artist_info')
            cursor.execute('TRUNCATE TABLE contact')
            cursor.execute('TRUNCATE TABLE customer_master')
            cursor.execute('TRUNCATE TABLE customer_master_master')
            cursor.execute('TRUNCATE TABLE customer_master_master_contact')
            cursor.execute('TRUNCATE TABLE releases')
            cursor.execute('TRUNCATE TABLE release_exclusive')
            cursor.execute('TRUNCATE TABLE track')
            cursor.execute('TRUNCATE TABLE vendor')
            cursor.execute('TRUNCATE TABLE vend_contact')
            cursor.execute('TRUNCATE TABLE parent_company')
            cursor.execute('TRUNCATE TABLE company_brand')
            cursor.execute('SET FOREIGN_KEY_CHECKS=1')
        conn.commit()


@pytest.fixture
def seed_customer_master_master_table():
    """Seed customer_master_master table."""
    with util.dd_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(fixtures.SQL_SEED_CUSTOMER_MASTER_MASTER)
        conn.commit()
    yield
    reset_dd_db()


@pytest.fixture
def seed_customer_master_master_table_ar():
    """Seed customer_master_master table."""
    with util.ar_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(fixtures.SQL_SEED_CUSTOMER_MASTER_MASTER_AR)
        conn.commit()
    yield
    reset_ar_db()


@pytest.fixture
def seed_batch_delivery_jobs(seed_all_tables_with_all_seeds):
    """Seed batch delivery jobs table.

    This must be called separately and modifies the data to make it batched.
    """
    with util.dd_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(fixtures.SQL_SEED_BATCH_DELIVERY_JOBS_1)
            cursor.execute(fixtures.SQL_SEED_BATCH_DELIVERY_JOBS_2)
            cursor.execute(fixtures.SQL_SEED_BATCH_DELIVERY_JOBS_3)
            cursor.execute(fixtures.SQL_SEED_BATCH_DELIVERY_JOBS_4)
        conn.commit()
    yield


@pytest.fixture
def seed_nonbatch_delivery_jobs(seed_all_tables_with_all_seeds):
    """Seed batch delivery jobs table.

    This must be called separately and modifies the data to make it batched.
    """
    with util.dd_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(fixtures.SQL_SEED_NONBATCH_DELIVERY_JOBS_1)
            cursor.execute(fixtures.SQL_SEED_NONBATCH_DELIVERY_JOBS_2)
        conn.commit()
    yield


@pytest.fixture
def seed_all_tables_with_all_seeds():
    """Seed all of the tables."""
    with util.dd_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(fixtures.SQL_SEED_CUSTOMER_MASTER_MASTER)
            cursor.execute(fixtures.SQL_SEED_ENCODING_QUEUE_TABLE)
            cursor.execute(fixtures.SQL_SEED_ENCODING_QUEUE_DETAIL_TABLE)
            cursor.execute(fixtures.SQL_SEED_DMS_DELIVERY_SPEC_TABLE)
        conn.commit()
    yield
    reset_dd_db()


@pytest.fixture
def seed_renew_script():
    """Seed renew script."""
    with util.dd_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(fixtures.SQL_SEED_CUSTOMER_MASTER_MASTER)
            cursor.execute(fixtures.SQL_SEED_ENCODING_QUEUE_TABLE)
            cursor.execute(fixtures.SQL_SEED_ENCODING_QUEUE_DETAIL_TABLE)
            cursor.execute(fixtures.SQL_SEED_DMS_DELIVERY_SPEC_TABLE)
            cursor.execute(fixtures.SQL_SEED_NONBATCH_DELIVERY_JOBS_1)
            cursor.execute(fixtures.SQL_SEED_NONBATCH_DELIVERY_JOBS_2)
            cursor.execute(fixtures.SQL_SET_NON_BATCH_DELIVERY_ITEM_TO_NEW)
        conn.commit()
    yield
    reset_dd_db()


@pytest.fixture
def seed_find_batch_to_close():
    """Seed find_batch_to_close script."""
    with util.dd_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(fixtures.SQL_SEED_CUSTOMER_MASTER_MASTER)
            cursor.execute(fixtures.SQL_SEED_ENCODING_QUEUE_TABLE)
            cursor.execute(fixtures.SQL_SEED_ENCODING_QUEUE_DETAIL_TABLE)
            cursor.execute(fixtures.SQL_SEED_DMS_DELIVERY_SPEC_TABLE)
            cursor.execute(fixtures.SQL_SEED_BATCH_DELIVERY_JOBS_1)
            cursor.execute(fixtures.SQL_SEED_BATCH_DELIVERY_JOBS_2)
            cursor.execute(fixtures.SQL_SEED_BATCH_DELIVERY_JOBS_3)
            cursor.execute(fixtures.SQL_SEED_BATCH_DELIVERY_JOBS_4)
            cursor.execute(fixtures.SQL_SET_BATCH_DELIVERY_ITEM_TO_DELIVERED)
        conn.commit()
    yield
    reset_dd_db()


@pytest.fixture
def seed_find_batch_to_close_no_batches_to_close():
    """Seed find_batch_to_close script."""
    with util.dd_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(fixtures.SQL_SEED_CUSTOMER_MASTER_MASTER)
            cursor.execute(fixtures.SQL_SEED_ENCODING_QUEUE_TABLE)
            cursor.execute(fixtures.SQL_SEED_ENCODING_QUEUE_DETAIL_TABLE)
            cursor.execute(fixtures.SQL_SEED_DMS_DELIVERY_SPEC_TABLE)
            cursor.execute(fixtures.SQL_SEED_BATCH_DELIVERY_JOBS_1)
            cursor.execute(fixtures.SQL_SEED_BATCH_DELIVERY_JOBS_2)
            cursor.execute(fixtures.SQL_SEED_BATCH_DELIVERY_JOBS_3)
            cursor.execute(fixtures.SQL_SEED_BATCH_DELIVERY_JOBS_4)
            cursor.execute(fixtures.SQL_SET_BATCH_DELIVERY_ITEM_TO_DELIVERING)
        conn.commit()
    yield
    reset_dd_db()


@pytest.fixture
def dd_conn_info_valid():
    return {
        'password': 'dd_pass',
        'user': 'dd_user',
        'port': 3306,
        'database': 'direct_delivery',
        'host': 'mysql-dd'
    }


@pytest.fixture
def dd_conn_info_invalid():
    return {
        'password': 'invalid_password',
        'user': 'root',
        'port': 3306,
        'database': 'direct_delivery',
        'host': 'mysql-dd'
    }


@pytest.fixture
def ar_conn_info_valid():
    return {
        'password': 'ar_pass',
        'user': 'ar_user',
        'port': 3306,
        'database': 'art_relations',
        'host': 'mysql-ar'
    }


@pytest.fixture
def seed_get_product_ids_by_upcs():
    """Seed get_product_ids_by_upcs script."""
    with util.ar_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(fixtures.SQL_SEED_PRODUCT_TYPE)
            cursor.execute(fixtures.SQL_SEED_PROJECT)
            cursor.execute(fixtures.SQL_SEED_RELEASES)
        conn.commit()
    yield
    reset_ar_db()


@pytest.fixture
def seed_get_open_batch_count():
    """See get_open_batch_count."""
    with util.dd_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(fixtures.SQL_SEED_ENCODING_QUEUE_TABLE)
            cursor.execute(fixtures.SQL_SEED_ENCODING_QUEUE_DETAIL_TABLE)
            cursor.execute(fixtures.SQL_SEED_OPEN_BATCHES)
            cursor.execute(fixtures.SQL_SEED_OPEN_BATCHES_DETAILS)
        conn.commit()
    yield
    reset_dd_db()


@pytest.fixture
def seed_get_encoded_jobs_info():
    """Seed get_encoded_jobs_info."""
    with util.dd_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(fixtures.SQL_SEED_CUSTOMER_MASTER_MASTER)
            cursor.execute(fixtures.SQL_SEED_ENCODING_QUEUE_TABLE)
            cursor.execute(fixtures.SQL_SEED_ENCODING_QUEUE_DETAIL_TABLE)
            cursor.execute(fixtures.SQL_SEED_DMS_DELIVERY_SPEC_TABLE)
            cursor.execute(fixtures.SQL_SEED_BATCH_DELIVERY_JOBS_1)
            cursor.execute(fixtures.SQL_SEED_BATCH_DELIVERY_JOBS_2)
            cursor.execute(fixtures.SQL_SEED_BATCH_DELIVERY_JOBS_3)
            cursor.execute(fixtures.SQL_SEED_BATCH_DELIVERY_JOBS_4)
            cursor.execute(fixtures.SQL_SET_BATCH_DELIVERY_ITEMS_TO_ENCODED)
        conn.commit()
    yield
    reset_dd_db()


@pytest.fixture
def seed_get_encoded_jobs_info_empty():
    """Seed get_encoded_jobs_info_empty."""
    with util.dd_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(fixtures.SQL_SEED_CUSTOMER_MASTER_MASTER)
            cursor.execute(fixtures.SQL_SEED_ENCODING_QUEUE_TABLE)
            cursor.execute(fixtures.SQL_SEED_ENCODING_QUEUE_DETAIL_TABLE)
            cursor.execute(fixtures.SQL_SEED_DMS_DELIVERY_SPEC_TABLE)
            cursor.execute(fixtures.SQL_SEED_BATCH_DELIVERY_JOBS_1)
            cursor.execute(fixtures.SQL_SEED_BATCH_DELIVERY_JOBS_2)
            cursor.execute(fixtures.SQL_SEED_BATCH_DELIVERY_JOBS_3)
            cursor.execute(fixtures.SQL_SEED_BATCH_DELIVERY_JOBS_4)
        conn.commit()
    yield
    reset_dd_db()


@pytest.fixture
def seed_create_delivery_batch():
    """Seed create_delivery_batch."""
    with util.dd_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(fixtures.SQL_SEED_CUSTOMER_MASTER_MASTER)
            cursor.execute(fixtures.SQL_SEED_ENCODING_QUEUE_TABLE)
            cursor.execute(fixtures.SQL_SEED_ENCODING_QUEUE_DETAIL_TABLE)
            cursor.execute(fixtures.SQL_SEED_DMS_DELIVERY_SPEC_TABLE)
            cursor.execute(fixtures.SQL_SEED_BATCH_DELIVERY_JOBS_1)
            cursor.execute(fixtures.SQL_SET_BATCH_DELIVERY_ITEMS_TO_ENCODED)
        conn.commit()
    yield
    reset_dd_db()


@pytest.fixture
def seed_create_delivery_batch_no_batch_created():
    """Seed create_delivery_batch_no_batch_created."""
    with util.dd_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(fixtures.SQL_SEED_CUSTOMER_MASTER_MASTER)
            cursor.execute(fixtures.SQL_SEED_ENCODING_QUEUE_TABLE)
            cursor.execute(fixtures.SQL_SEED_ENCODING_QUEUE_DETAIL_TABLE)
            cursor.execute(fixtures.SQL_SEED_DMS_DELIVERY_SPEC_TABLE)
            cursor.execute(fixtures.SQL_SEED_BATCH_DELIVERY_JOBS_1)
        conn.commit()
    yield
    reset_dd_db()


@pytest.fixture(scope='module')
def ftp_server(ftpserver):
    """FTP Server Fixture."""
    yield ftpserver
    ftpserver.stop()


@pytest.fixture
def sftp_server_and_conn_obj(sftpserver):
    return sftpserver, connection_info.ConnectionInfo({
        'connection_type': 'sftp',
        'authenticate_type': 'password',
        'user_name': 'username',
        'password': 'password',
        'port': sftpserver.port,
        'domain_name': sftpserver.host
    })


@pytest.fixture
def private_key():
    file_obj = io.StringIO('')
    pkey = paramiko.RSAKey.generate(1024)
    pkey.write_private_key(file_obj, password=None)
    return file_obj.getvalue()


@pytest.fixture
def s3_conn_info():
    return {
        'connection_type': 's3',
        'authenticate_type': 'password',
        'aws_s3_region': 'us-east-1',
        'aws_s3_signature_version': 'v2',
        'user_name': 'username',
        'password': 'password',
        'domain_name': 'test_bucket'
    }


@pytest.fixture
def gcs_conn_info(private_key):
    return {
        'connection_type': 'gcs',
        'authenticate_type': 'service_account',
        'domain_name': 'test_bucket',
        'gcs_config_file': {
            'project_id': 'test-project-id',
            'type': 'service_account',
            'private_key_id': 'test-private-key-id',
            'private_key': private_key,
            'client_email': 'test',
            'client_id': 'test',
        }
    }


@pytest.fixture
def local_test_files(tmpdir):
    file_names = ['file_1.txt', 'file_2.txt', 'file_3.txt']
    for file_name in file_names:
        f = tmpdir.join(file_name)
        f.write('content')
    return ['{}/{}'.format(tmpdir, x) for x in file_names]


@pytest.fixture
def seed_get_release_priority_info():
    with util.ar_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(fixtures.SQL_SEED_PARENT_COMPANY)
            cursor.execute(fixtures.SQL_SEED_COMPANY_BRAND)
            cursor.execute(fixtures.SQL_SEED_VENDOR)
            cursor.execute(fixtures.SQL_SEED_PRODUCT_TYPE)
            cursor.execute(fixtures.SQL_SEED_PROJECT)
            cursor.execute(fixtures.SQL_SEED_RELEASES)
            cursor.execute(fixtures.SQL_SEED_ARTIST_INFO)
            cursor.execute(fixtures.SQL_SEED_CONTACT)
            cursor.execute(fixtures.SQL_SEED_VEND_CONTACT)
        conn.commit()
    yield
    reset_ar_db()


@pytest.fixture
def seed_customer_master():
    with util.ar_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(fixtures.SQL_SEED_CUSTOMER_MASTER)
        conn.commit()
    yield
    reset_ar_db()


@pytest.fixture
def seed_customer_master_master_contact():
    with util.ar_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(fixtures.SQL_SEED_CUSTOMER_MASTER_MASTER_AR)
            cursor.execute(fixtures.SQL_SEED_CUSTOMER_MASTER_MASTER_CONTACT)
        conn.commit()
    yield
    reset_ar_db()


@pytest.fixture
def seed_track(seed_get_release_priority_info):
    with util.ar_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(fixtures.SQL_SEED_TRACK)
        conn.commit()
    yield


@pytest.fixture
def seed_get_release_artist_genre_info(seed_get_release_priority_info):
    with util.ar_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(fixtures.SQL_SEED_RELEASE_EXCLUSIVE)
        conn.commit()
    yield


@pytest.fixture
def seed_storage_drives():
    with util.dd_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(fixtures.SQL_SEED_ASSET_TYPE)
            cursor.execute(fixtures.SQL_SEED_PHYSICAL_LOCATION)
            cursor.execute(fixtures.SQL_SEED_STORAGE)
            cursor.execute(fixtures.SQL_SEED_STORAGE_DRIVE)
            cursor.execute(fixtures.SQL_SEED_STORAGE_DRIVE_ASSET_FOLDER)
        conn.commit()
    yield
    reset_dd_db()