"""Business rule validation for adjustment files via DuckDB SQL queries.

Validates adjustment rows against reference data including accounts, contracts, payment terms,
UPCs, statement periods, currencies, and adjustment types. Executes comprehensive SQL-based
validation and returns aggregated results with valid/invalid row counts and financial totals.

Validation covers account existence, contract assignments, UPC formats, period relationships,
currency codes, comment lengths, and distribution types. All data must be pre-loaded into DuckDB.
"""

from __future__ import annotations

from pydantic import BaseModel, ConfigDict, Field

from config import config
from src.connectors.duckdb import DuckDBConnection
from src.constants import ValidationRules
from src.infra.log import logger
from src.sql import DuckDBQuery, load_sql
from src.utils.db_utils import get_result_dict


class ValidationResult(BaseModel):
    """Validation results with row counts, error totals, and financial amount summaries."""

    model_config = ConfigDict(extra='ignore')

    invalid_rows: int = Field(
        ...,
        description='Rows that failed one or more validation rules.',
    )
    valid_rows: int = Field(..., description='Rows that passed all validation rules.')
    total_errors: int = Field(..., description='Total count of failed validations.')
    total_amount: float = Field(
        ...,
        description='Sum of normalized and validated amounts.',
    )
    total_amount_raw: float = Field(
        ...,
        description='Sum of raw amounts before normalization.',
    )


class DuckDBValidator:
    """Executes SQL-based business rule validation against DuckDB-loaded adjustment data.

    Assumes all adjustment rows and reference tables are pre-loaded into DuckDB.
    """

    def __init__(self, duck_conn: DuckDBConnection) -> None:
        """Initialize validator with DuckDB connection."""
        self._duck_conn = duck_conn

    def validate(self, batch_id: int) -> ValidationResult:
        """Execute SQL validation queries and return aggregated results.

        Args:
            batch_id: Unique identifier for this batch.

        Returns:
            Validation results with valid/invalid row counts and amount totals.
        """
        with self._duck_conn.cursor() as cursor:
            logger.info('Validating adjustments')
            cursor.execute(
                load_sql(DuckDBQuery.ValidateAdjustments),
                {
                    'batch_id': batch_id,
                    'account_expense_type': ValidationRules.ACCOUNT_EXPENSE_ADJUSTMENT_TYPE,
                    'action_status_complete': ValidationRules.ACTION_STATUS_COMPLETE,
                    'comment_max_length': config.policy.MAX_COMMENT_LEN,
                    'valid_distribution_types': list(
                        ValidationRules.DISTRIBUTION_TYPES
                    ),
                    'valid_statement_period_status': ValidationRules.STATEMENT_PERIOD_STATUS,
                    'upc_min_length': ValidationRules.UPC_MIN_LENGTH,
                    'upc_max_length': ValidationRules.UPC_MAX_LENGTH,
                },
            )
            logger.info('Adjustments validated')

            query = load_sql(DuckDBQuery.GetValidationResults)
            cursor.execute(query)
            result = get_result_dict(cursor)

        return ValidationResult.model_validate(result)