"""Test MySQL."""

import os
import pathlib

import pytest

from accounting_run_utils.connectors import mysql


@pytest.fixture()
def mock_table_name() -> str:
    return "test_table"


@pytest.fixture()
def mock_table_name_with_subpartitions() -> str:
    return "test_table_with_subpartitions"


@pytest.fixture()
def mock_partition_count() -> int:
    return 8


@pytest.fixture()
def mock_period_id() -> int:
    return 300


@pytest.fixture()
def mock_row_count() -> int:
    return 1000


@pytest.fixture()
def mock_sql_sleep() -> str:
    sql = "SELECT SLEEP(1);"
    return sql


@pytest.fixture()
def mock_sql_with_partition(mock_table_name: str) -> str:
    sql = f"SELECT * FROM {mock_table_name} PARTITION (PARTITION_NAME);"
    with open("./tests/fixture_data/mock_sql_with_partition.sql", "w") as file:
        file.write(sql)
    file.close()
    return file.name


@pytest.fixture()
def mock_sql_without_partition(mock_table_name: str) -> str:
    sql = f"SELECT * FROM {mock_table_name};"
    with open(
        "./tests/fixture_data/mock_sql_without_partition.sql", "w"
    ) as file:
        file.write(sql)
    file.close()
    return file.name


@pytest.fixture()
def mock_create_mysql_table(
    mock_table_name: str, mock_partition_count: int
) -> str:
    column_name = "name"
    connection = mysql.get_mysql_connection()
    with connection.cursor() as cursor:
        cursor.execute(f"DROP TABLE IF EXISTS {mock_table_name};")
        cursor.execute(
            f"CREATE TABLE IF NOT EXISTS {mock_table_name} ({column_name} VARCHAR(255) PRIMARY KEY) PARTITION BY KEY({column_name}) PARTITIONS {mock_partition_count};"
        )
    connection.close()
    return column_name


@pytest.fixture()
def mock_create_mysql_table_with_subpartitions(
    mock_table_name_with_subpartitions: str,
    mock_partition_count: int,
    mock_period_id: int,
) -> str:
    column_name = "id_one"
    subpartition_column_name = "id_two"
    connection = mysql.get_mysql_connection()
    with connection.cursor() as cursor:
        cursor.execute(
            f"DROP TABLE IF EXISTS {mock_table_name_with_subpartitions};"
        )
        range_limit = mock_period_id + 1
        cursor.execute(
            f"CREATE TABLE IF NOT EXISTS {mock_table_name_with_subpartitions} ({column_name} smallint unsigned, {subpartition_column_name} smallint unsigned) PARTITION BY RANGE({column_name}) SUBPARTITION BY KEY ({subpartition_column_name}) SUBPARTITIONS {mock_partition_count} (PARTITION M1 VALUES LESS THAN ({range_limit}) ENGINE = InnoDB);"
        )
    connection.close()
    return column_name


@pytest.fixture()
def mock_load_data_file(mock_row_count: int) -> str:
    file_name = "test_file"
    with open(f"./tests/fixture_data/{file_name}", "w") as file:
        [file.write(f"name{num}\n") for num in range(mock_row_count)]
    file.close()
    return file_name


def test_get_mysql_connection() -> None:
    connection = mysql.get_mysql_connection()
    assert connection.db.decode() == os.getenv("MYSQL_DATABASE")
    assert connection.user.decode() == os.getenv("MYSQL_USER")
    assert connection.password.decode() == os.getenv("MYSQL_PASSWORD")
    assert connection.host == os.getenv("MYSQL_HOST")


def test_get_table_partition_count(
    mock_create_mysql_table: str,
    mock_table_name: str,
    mock_partition_count: int,
) -> None:
    connection = mysql.get_mysql_connection()
    partitions = mysql.get_table_partition_count(
        connection.db.decode(), mock_table_name
    )
    assert len(partitions) == mock_partition_count
    partition_list = ["p0", "p1", "p2", "p3", "p4", "p5", "p6", "p7"]
    assert [partition in partitions for partition in partition_list]


def test_get_table_subpartition_count_for_period_id(
    mock_create_mysql_table_with_subpartitions: str,
    mock_table_name_with_subpartitions: str,
    mock_partition_count: int,
    mock_period_id: int,
) -> None:
    connection = mysql.get_mysql_connection()
    partitions = mysql.get_table_subpartition_count_for_period_id(
        connection.db.decode(),
        mock_table_name_with_subpartitions,
        str(mock_period_id),
    )
    print(partitions)
    assert len(partitions) == mock_partition_count
    partition_list = [
        "M1sp0",
        "M1sp1",
        "M1sp2",
        "M1sp3",
        "M1sp4",
        "M1sp5",
        "M1sp6",
        "M1sp7",
    ]
    assert [partition in partitions for partition in partition_list]


def test_load_data(
    mock_create_mysql_table: str,
    mock_table_name: str,
    mock_load_data_file: str,
    mock_row_count: int,
) -> None:
    connection = mysql.get_mysql_connection()
    database = connection.db.decode()
    sql = f"LOAD DATA INFILE '/var/lib/mysql-files/{mock_load_data_file}' INTO TABLE {database}.{mock_table_name} ({mock_create_mysql_table});"
    updated_rows = mysql.load_data(sql, mock_create_mysql_table)
    assert updated_rows == mock_row_count


def test_run_query_with_no_result(
    caplog,
    mock_sql_sleep: str,
) -> None:
    caplog.clear()
    mysql.run_query_with_no_result(mock_sql_sleep)
    assert f"Running query {mock_sql_sleep}" in caplog.records[0].message
    assert f"Query time for {mock_sql_sleep}" in caplog.records[1].message


def test_prepare_partitioned_query_failure(
    mock_sql_without_partition: str,
) -> None:
    partition = "p0"
    with pytest.raises(ValueError):
        _sql = mysql.prepare_partitioned_query(
            mock_sql_without_partition, partition
        )


def test_prepare_partitioned_query_success(
    mock_sql_with_partition: str,
) -> None:
    partition = "p0"
    sql = mysql.prepare_partitioned_query(mock_sql_with_partition, partition)
    assert f"PARTITION ({partition})" in sql


def test_select_data_into_outfile(
    mock_create_mysql_table: str,
    mock_table_name: str,
    mock_load_data_file: str,
    mock_row_count: int,
) -> None:
    test_load_data(
        mock_create_mysql_table,
        mock_table_name,
        mock_load_data_file,
        mock_row_count,
    )
    connection = mysql.get_mysql_connection()
    database = connection.db.decode()
    outfile_name = "outfile"
    pathlib.Path(f"./tests/fixture_data/{outfile_name}").unlink(missing_ok=True)
    sql = f"SELECT * INTO OUTFILE '/var/lib/mysql-files/{outfile_name}' FIELDS ESCAPED BY '\\\\' TERMINATED BY '\\t' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\\n' FROM {database}.{mock_table_name};"
    updated_rows = mysql.select_data_into_outfile(
        sql, f"/var/lib/mysql-files/{outfile_name}"
    )
    assert updated_rows == mock_row_count