"""Adjustment file loader service.

Handles ingestion of adjustment files into DuckDB with format detection, column validation,
row count enforcement, and text normalization for consistent downstream processing.
"""

from __future__ import annotations

from pathlib import Path

from abacus_common_logic.utils.profiling import profile

from src.connectors.duckdb import DuckDBConnection
from src.connectors.duckdb.utils import (
    create_table_from_file,
    get_file_columns,
    normalize_table_rows,
)
from src.constants import AdjustmentInputSchema
from src.enums import DuckDBTable
from src.errors import (
    EmptyFileError,
    MissingHeadersError,
    RowCountExceededError,
)
from src.infra.log import logger
from src.schemas import FileMetadata
from src.utils.db_utils import count_table_rows
from src.utils.file_utils import get_file_metadata


class AdjustmentFileLoader:
    """Loads adjustment files into DuckDB with validation and normalization.

    Detects file format (CSV, Excel, Parquet), validates headers against schema,
    enforces row count limits, and normalizes text data for consistent processing.
    """

    def __init__(
        self,
        duck_conn: DuckDBConnection,
        max_rows: int | None = None,
        max_str_len: int | None = None,
    ) -> None:
        """Initialize the loader with DuckDB connection and optional limits."""
        self._duck_conn = duck_conn
        self._table_name = DuckDBTable.ADJUSTMENT_FILE
        self._max_rows = max_rows
        self._max_str_len = max_str_len

        if max_rows is not None and max_rows < 1:
            raise ValueError('max_rows must be positive')

        if max_str_len is not None and max_str_len < 1:
            raise ValueError('max_str_len must be positive')

    @profile(logger=logger)
    def load(self, file_path: str | Path) -> str:
        """Load, validate structure, and normalize a file into DuckDB.

        Args:
            file_path: Path to the input file.

        Returns:
            The name of the DuckDB table where data was loaded.

        Raises:
            MissingHeadersError: If required columns are missing.
            EmptyFileError: If the file is empty.
            RowCountExceededError: If the file is too large.
            FileParsingError: If file cannot be read.
            InvalidFileTypeError: If format is unsupported.
        """
        metadata = get_file_metadata(file_path)
        alias_map = self._validate_file_columns(metadata)
        self._load_file_to_table(metadata, alias_map)
        self._validate_row_count()
        self._normalize_file()

        return DuckDBTable.ADJUSTMENT_FILE

    def _load_file_to_table(
        self, metadata: FileMetadata, alias_map: dict[str, str | None]
    ) -> None:
        """Load file into DuckDB table with column mapping applied."""
        logger.info(f'Loading file into DuckDB table: {self._table_name}')
        with self._duck_conn.cursor() as cursor:
            create_table_from_file(cursor, self._table_name, metadata, alias_map)
        logger.info('DuckDB table loaded')

    def _validate_row_count(self) -> None:
        """Validate table is non-empty and within row count limits."""
        logger.info('Checking row count')
        with self._duck_conn.cursor() as cursor:
            row_count = count_table_rows(cursor, self._table_name)
        logger.info(f'Row count: {row_count}')

        if row_count < 1:
            raise EmptyFileError('File cannot be empty')

        if self._max_rows is not None and row_count > self._max_rows:
            raise RowCountExceededError(
                f'Row count exceeds maximum: {row_count} > {self._max_rows}'
            )

    def _normalize_file(self) -> None:
        """Trim and truncate text columns for consistent processing."""
        if self._max_str_len is None:
            return
        logger.info('Normalizing adjustments')
        max_len = self._max_str_len + 1
        with self._duck_conn.cursor() as cursor:
            normalize_table_rows(cursor, self._table_name, max_len)
        logger.info('Adjustments normalized')

    def _validate_file_columns(self, metadata: FileMetadata) -> dict[str, str | None]:
        """Validate file headers against schema and return column mapping."""
        logger.info('Getting file columns')
        with self._duck_conn.cursor() as cursor:
            file_headers = get_file_columns(cursor, metadata)
        normalized_to_file_header = {self._normalize_header(h): h for h in file_headers}

        logger.info('Validating file columns')
        missing_headers: list[str] = []
        alias_map: dict[str, str | None] = {}
        for mapping in AdjustmentInputSchema.COLUMNS:
            alias_map[mapping.column_name] = None
            normalized_expected = self._normalize_header(mapping.display_name)
            if normalized_expected in normalized_to_file_header:
                file_header = normalized_to_file_header[normalized_expected]
                alias_map[mapping.column_name] = file_header
            elif mapping.required:
                missing_headers.append(mapping.display_name)

        if not any(alias_map.values()):
            raise MissingHeadersError('No valid columns found')

        if missing_headers:
            raise MissingHeadersError(f'Missing required columns: {missing_headers}')

        return alias_map

    def _normalize_header(self, value: str) -> str:
        """Normalize column header for consistent comparisons."""
        return value.strip().lower()