"""MySQL utility module."""

from pathlib import Path

from pymysql.cursors import Cursor

from src.connectors.mysql.connection import handle_mysql_errors


@handle_mysql_errors
def get_max_query_bytes(cursor: Cursor) -> int | None:
    """Fetch the max_allowed_packet size from the database server.

    Args:
        cursor: The db cursor.

    Returns:
        int: The size in bytes, or None if it could not be determined.

    Raises:
        TransientError: If database connection fails
        Exception: For other database errors or file access issues
    """
    cursor.execute("SHOW VARIABLES LIKE 'max_allowed_packet'")
    result = cursor.fetchone()

    return int(result[1]) if result else None


@handle_mysql_errors
def load_from_file(
    cursor: Cursor,
    file_path: str | Path,
    table_name: str,
    columns: list[str],
    has_header: bool = True,
) -> int:
    """Load data from local CSV file into MySQL table.

    Uses MySQL LOAD DATA LOCAL INFILE to bulk load CSV data from a local
    file path into the table.

    Note: Requires MySQL local_infile setting to be enabled.
    Note: Caller is responsible for committing the transaction.

    Args:
        cursor: The db cursor.
        table_name: Name of the table to load data into
        file_path: Absolute path to the local file
        columns: List of column names matching the CSV structure
        has_header: Whether the CSV file has a header row (Default: True)

    Returns:
        int: Number of rows loaded

    Raises:
        TransientError: If database connection fails
        Exception: For other database errors or file access issues
    """
    columns_clause = ', '.join([f'`{col}`' for col in columns])
    ignore_lines = int(has_header)

    query = f"""
        LOAD DATA LOCAL INFILE '{file_path}'
        INTO TABLE `{table_name}`
        CHARACTER SET utf8mb4
        FIELDS TERMINATED BY ','
        ENCLOSED BY '"'
        LINES TERMINATED BY '\n'
        IGNORE {ignore_lines} LINES
        ({columns_clause})
    """

    cursor.execute(query)
    return cursor.rowcount


@handle_mysql_errors
def load_from_s3(
    cursor: Cursor,
    s3_bucket: str,
    s3_key: str,
    table_name: str,
    columns: list[str],
    has_header: bool = True,
) -> int:
    """Load data from S3 CSV file into MySQL table.

    Uses MySQL LOAD DATA FROM S3 to bulk load CSV data directly from S3
    into the table.

    Note: Requires Aurora MySQL with appropriate S3 permissions configured.
    Note: Caller is responsible for committing the transaction.

    Args:
        cursor: The db cursor.
        s3_bucket: S3 bucket name
        s3_key: S3 key of the CSV file
        table_name: Name of the table to load data into
        columns: List of column names matching the CSV structure
        has_header: Whether the CSV file has a header row (Default: True)

    Returns:
        int: Number of rows loaded

    Raises:
        TransientError: If database connection fails
        Exception: For other database errors including S3 access issues
    """
    safe_key = s3_key.replace("'", "\\'")
    s3_uri = f's3://{s3_bucket}/{safe_key}'
    columns_clause = ', '.join([f'`{col}`' for col in columns])
    ignore_lines = int(has_header)

    query = f"""
        LOAD DATA FROM S3 '{s3_uri}'
        INTO TABLE `{table_name}`
        CHARACTER SET utf8mb4
        FIELDS TERMINATED BY ','
        ENCLOSED BY '"'
        LINES TERMINATED BY '\n'
        IGNORE {ignore_lines} LINES
        ({columns_clause})
    """

    cursor.execute(query)
    return cursor.rowcount