import json
import os
import re
import time
from datetime import datetime

import docker
import pytest
import sqlalchemy
import yaml
from click.testing import CliRunner
from sqlalchemy import create_engine

from snowshu.adapters.target_adapters.postgres_adapter import PostgresAdapter
from snowshu.core.docker import SnowShuDocker
from snowshu.configs import (DEFAULT_PRESERVE_CASE,
                             DEFAULT_MAX_NUMBER_OF_OUTLIERS,
                             DOCKER_REMOUNT_DIRECTORY,
                             DOCKER_TARGET_CONTAINER,
                             PACKAGE_ROOT)
from snowshu.core.main import cli
from snowshu.core.models import Relation, Attribute, data_types
from snowshu.core.models.materializations import TABLE

""" End-To-End all inclusive test session"""
# 1. builds a new replica based on the template configs
# 2. launches the replica
# 3. Queries the replica
# 4. Spins down and cleans up

BASE_CONN = 'postgresql://snowshu:snowshu@integration-test:9999/{}'
INITIAL_INCREMENTAL_CONFIG_PATH = os.path.join(PACKAGE_ROOT, 
                                               'tests', 
                                               'assets', 
                                               'replica_test_incremental_config.yml')
CONFIGURATION_PATH = os.path.join(PACKAGE_ROOT, 'tests', 'assets', 'replica_test_config.yml')
SNOWSHU_META_STRING = BASE_CONN.format('snowshu')
SNOWSHU_DEVELOPMENT_STRING = BASE_CONN.format('snowshu_development')
DOCKER_SPIN_UP_TIMEOUT = 15


@pytest.fixture(scope="session", autouse=True)
def end_to_end(docker_flush_session):
    runner = CliRunner()

    create_result = runner.invoke(cli, ('create', '--replica-file', CONFIGURATION_PATH, '--barf'))
    if create_result.exit_code:
        print(create_result.exc_info)
        raise create_result.exception
    create_output = create_result.output.split('\n')
    client = docker.from_env()
    client.containers.run('snowshu_replica_integration-test',
                          ports={'9999/tcp': 9999},
                          name='integration-test',
                          network='snowshu',
                          detach=True)
    time.sleep(DOCKER_SPIN_UP_TIMEOUT)  # the replica needs a second to initialize
    return create_output


def any_appearance_of(string, strings):
    return any([string in line for line in strings])


def find_number_of_processed_relations(strings):
    regex = r"Identified a total of (.*?) relations to sample based on the specified configurations."
    for string in strings:
        substring = re.search(regex, string)
        if substring:
            found_relations_count = int(substring.group(1))
            break
    return found_relations_count if substring else 0


def test_reports_full_catalog_start(end_to_end):
    result_lines = end_to_end
    assert any_appearance_of('Building filtered catalog...', result_lines)


def test_finds_n_relations(end_to_end):
    result_lines = end_to_end
    assert find_number_of_processed_relations(result_lines) == 16, \
        "Number of found relations do not match the expected of 16 relations. Check database."


def test_replicates_order_items(end_to_end):
    result_lines = end_to_end
    assert any_appearance_of('Done replication of relation snowshu_development.source_system.order_items', result_lines)


@pytest.mark.skip
def test_snowshu_explain(end_to_end):
    runner = CliRunner()
    response = json.loads(runner.invoke(
        cli, ('explain', 'integration-test', '--json')))

    assert response['name'] == 'integration-test'
    assert response['image'] == 'postgres:12'
    assert response['target_adapter'] == 'postgres'
    assert response['source_adapter'] == 'snowflake'
    assert datetime(response['created_at']) < datetime.now()


def test_replica_meta(end_to_end):
    conn = create_engine(SNOWSHU_META_STRING)
    query_string = """
        SELECT * FROM SNOWSHU.SNOWSHU.REPLICA_META
    """
    with open(CONFIGURATION_PATH, 'r') as file_stream:
        expected_config = yaml.safe_load(file_stream)
    # default for preserve case and max_outliers are set after parsing
    expected_config["preserve_case"] = DEFAULT_PRESERVE_CASE
    expected_config["source"]["max_number_of_outliers"] = DEFAULT_MAX_NUMBER_OF_OUTLIERS

    query = conn.execute(query_string)
    results = query.fetchall()
    for record in results:
        assert record['created_at']
        # values from replica_test_config.yml
        assert record['name'] == 'integration-test'
        assert record['short_description'] == 'this is a sample with LIVE CREDS for integration'
        assert record['long_description'] == 'this is for testing against a live db'
        assert record['config_json'] == expected_config

    assert len(results) == 1


def test_polymorphic_parent_id(end_to_end):
    conn = create_engine(SNOWSHU_DEVELOPMENT_STRING)
    query_string = """
        SELECT * FROM SNOWSHU_DEVELOPMENT.POLYMORPHIC_DATA.PARENT_TABLE
    """
    query = conn.execute(query_string)
    results = query.fetchall()
    for record in results:
        assert record['id'] not in (13, 14)
    assert len(results) == 12


def test_polymorphic_child_id(end_to_end):
    conn = create_engine(SNOWSHU_DEVELOPMENT_STRING)
    query_string = """
        SELECT * FROM SNOWSHU_DEVELOPMENT.POLYMORPHIC_DATA.PARENT_TABLE_2
    """
    query = conn.execute(query_string)
    results = query.fetchall()
    for record in results:
        assert record['id'] not in (13, 14)
    assert len(results) == 12


def test_polymorphic_child_tables(end_to_end):
    conn = create_engine(SNOWSHU_DEVELOPMENT_STRING)
    for val in ['0', '1', '2']:
        query_string = f"""
            SELECT * FROM SNOWSHU_DEVELOPMENT.POLYMORPHIC_DATA.CHILD_TYPE_{val}_ITEMS
        """
        query = conn.execute(query_string)
        results = query.fetchall()
        assert len(results) == 4


def test_bidirectional(end_to_end):
    print('test_bidirectional')
    conn = create_engine(SNOWSHU_DEVELOPMENT_STRING)
    query = """
SELECT 
    COUNT(*) 
FROM 
    SNOWSHU_DEVELOPMENT.SOURCE_SYSTEM.USER_COOKIES uc
FULL OUTER JOIN
     SNOWSHU_DEVELOPMENT.SOURCE_SYSTEM.USERS u
ON 
    uc.user_id=u.id
WHERE 
    uc.user_id IS NULL
OR
    u.id IS NULL
"""
    q = conn.execute(query)
    count = q.fetchall()[0][0]
    assert count == 0


def test_directional(end_to_end):
    print('test_directional')
    conn = create_engine(SNOWSHU_DEVELOPMENT_STRING)
    query = """
WITH
joined_roots AS (
SELECT 
    oi.id AS oi_id
    ,oi.order_id AS oi_order_id
    ,o.id AS o_id
FROM 
    SNOWSHU_DEVELOPMENT.SOURCE_SYSTEM.ORDER_ITEMS oi
FULL OUTER JOIN
     SNOWSHU_DEVELOPMENT.SOURCE_SYSTEM.ORDERS o
ON 
    oi.order_id = o.id
)
SELECT 
    (SELECT COUNT(*) FROM joined_roots WHERE oi_id is null) AS upstream_missing
    ,(SELECT COUNT(*) FROM joined_roots WHERE o_id is null) AS downstream_missing
"""
    q = conn.execute(query)
    upstream_missing, downstream_missing = q.fetchall()[0]
    assert upstream_missing > 0
    # it is statistically very unlikely that NONE of the upstreams without a downstream will be included.
    assert downstream_missing == 0


def test_view(end_to_end):
    conn = create_engine(SNOWSHU_DEVELOPMENT_STRING)
    query = """
SELECT 
    (SELECT COUNT(*) FROM SNOWSHU_DEVELOPMENT.SOURCE_SYSTEM.ORDER_ITEMS_VIEW) /
    (SELECT COUNT(*) FROM SNOWSHU_DEVELOPMENT.SOURCE_SYSTEM.ORDER_ITEMS) AS delta
"""
    q = conn.execute(query)
    assert len(set(q.fetchall()[0])) == 1


def test_cross_database_query(end_to_end):
    conn = create_engine(SNOWSHU_DEVELOPMENT_STRING)
    query = 'SELECT COUNT(*) FROM snowshu__snowshu.replica_meta'
    q = conn.execute(query)
    assert len(set(q.fetchall()[0])) == 1


def test_applies_emulation_function(end_to_end):
    conn = create_engine(SNOWSHU_DEVELOPMENT_STRING)
    query = 'SELECT ANY_VALUE(id) FROM SNOWSHU_DEVELOPMENT.SOURCE_SYSTEM.ORDER_ITEMS'
    q = conn.execute(query)
    assert int(q.fetchall()[0][0]) > 0


def test_applies_uuid_emulation_function(end_to_end):
    conn = create_engine(SNOWSHU_DEVELOPMENT_STRING)
    query = 'SELECT UUID_STRING()'
    q = conn.execute(query)
    assert re.match('[0-9A-Fa-f-]{36}', q.fetchall()[0][0])


def test_applies_pg_extensions(end_to_end):
    conn = create_engine(SNOWSHU_DEVELOPMENT_STRING)
    query = "SELECT CASE WHEN 'My_Cased_String'::citext = 'my_cased_string'::citext THEN 'SUCCESS' ELSE 'FAIL' END"
    q = conn.execute(query)
    assert q.fetchall()[0][0] == 'SUCCESS'


def test_data_types(end_to_end):
    conn = create_engine(SNOWSHU_DEVELOPMENT_STRING)
    query = """
SELECT 
    COLUMN_NAME,
    DATA_TYPE
FROM 
    SNOWSHU_DEVELOPMENT.information_schema.columns 
WHERE 
    TABLE_SCHEMA = 'tests_data' 
AND 
    TABLE_NAME='data_types'"""

    q = conn.execute(query)
    type_mappings = q.fetchall()
    EXPECTED_DATA_TYPES = {
        "array_col": "json",
        "bigint_col": "bigint",
        "binary_col": "bytea",
        "boolean_col": "boolean",
        "char_col": "character varying",
        "character_col": "character varying",
        "date_col": "date",
        "datetime_col": "timestamp without time zone",
        "decimal_col": "bigint",
        "double_col": "double precision",
        "doubleprecision_col": "double precision",
        "float_col": "double precision",
        "float4_col": "double precision",
        "float8_col": "double precision",
        "int_col": "bigint",
        "integer_col": "bigint",
        "number_col": "bigint",
        "numeric_col": "bigint",
        "object_col": "json",
        "real_col": "double precision",
        "smallint_col": "bigint",
        "string_col": "character varying",
        "text_col": "character varying",
        "time_col": "time without time zone",
        "timestamp_col": "timestamp without time zone",
        "timestamp_ntz_col": "timestamp without time zone",
        "timestamp_ltz_col": "timestamp with time zone",
        "timestamp_tz_col": "timestamp with time zone",
        "varbinary_col": "bytea",
        "varchar_col": "character varying",
        "variant_col": "json"
    }
    assert {t[0]: t[1] for t in type_mappings} == EXPECTED_DATA_TYPES


def test_casing(end_to_end):
    conn = create_engine(SNOWSHU_DEVELOPMENT_STRING)
    query = """
        SELECT 
            COLUMN_NAME,
            DATA_TYPE
        FROM 
            SNOWSHU_DEVELOPMENT.information_schema.columns 
        WHERE 
            TABLE_SCHEMA = 'tests_data' 
        AND 
            TABLE_NAME='case_testing'
    """

    q = conn.execute(query)
    type_mappings = q.fetchall()
    EXPECTED_DATA_TYPES = {
        "lower_col": "character varying",
        "upper_col": "character varying",  # fully upper-case should map to all lower (snowflake -> postgres)
        "CamelCasedCol": "character varying",
        "quoted_upper_col": "character varying",  # fully upper-case should map to all lower (snowflake -> postgres)
        "1": "character varying",
        "Spaces Col": "character varying",
        "UNIFORM SPACE": "character varying",
        "uniform lower": "character varying",
        "Snake_Case_Camel_Col": "character varying",
    }
    assert {t[0]: t[1] for t in type_mappings} == EXPECTED_DATA_TYPES


def test_get_relations_from_database(end_to_end):
    adapter = PostgresAdapter(replica_metadata={})
    if adapter.target != "localhost":
        adapter._credentials.host = 'integration-test'

    config_patterns = [
        dict(database="snowshu",
             schema=".*",
             name=".*")
    ]

    attributes = [
        Attribute('created_at', data_types.TIMESTAMP_TZ),
        Attribute('config_json', data_types.JSON),
        Attribute('name', data_types.VARCHAR),
        Attribute('short_description', data_types.VARCHAR),
        Attribute('long_description', data_types.VARCHAR)
    ]
    relation = Relation("snowshu", "snowshu", "replica_meta", TABLE, attributes)

    catalog = adapter.build_catalog(config_patterns, thread_workers=1)
    relations = []
    for rel in catalog:
        relations.append(rel.__dict__.items())
    assert relation.__dict__.items() in relations


def test_x_db_incremental_import(end_to_end):
    adapter = PostgresAdapter(replica_metadata={})
    if adapter.target != "localhost":
        adapter._credentials.host = 'integration-test'

    def successfully_enabled_without_errors(adapter):
        try:
            adapter.enable_cross_database()
            adapter.enable_cross_database()
            return True
        except sqlalchemy.exc.ProgrammingError:
            return False

    assert successfully_enabled_without_errors(adapter)


def test_using_different_image(end_to_end):
    shdocker = SnowShuDocker()
    target_adapter = PostgresAdapter(replica_metadata={})

    envars = ['POSTGRES_USER=snowshu',
              'POSTGRES_PASSWORD=snowshu',
              'POSTGRES_DB=snowshu',
              f'PGDATA=/{DOCKER_REMOUNT_DIRECTORY}']
    target_container = shdocker.get_stopped_container(
        'snowshu_replica_integration-test',
        target_adapter.DOCKER_START_COMMAND,
        envars,
        9900,
        name=DOCKER_TARGET_CONTAINER,
        labels=dict(
            snowshu_replica='true',
            target_adapter=target_adapter.CLASSNAME,
            source_adapter='SnowflakeAdapter'))
    assert target_container.status == 'created'
    assert target_container.image.tags[0] == 'snowshu_replica_integration-test:latest'
    target_container.start()
    target_container.reload()
    assert target_container.status == 'running'
    target_container.remove(force=True)