"""SQL query management and loading utilities.

This module provides a centralized system for managing SQL queries used throughout
the adjustment file preparation pipeline. Queries are organized by database and
operation type, stored as .sql files, and loaded dynamically at runtime.

Query Organization:
    - art_relations/: Queries for UPC lookup from art_relations database
    - duckdb/: In-memory validation and transformation queries
    - royalty_accounting/: Reference data and staging operations

Query Enums:
    - ArtRelationsQuery: Art relations database queries
    - DuckDBQuery: DuckDB validation and transformation queries
    - RoyaltyAccountingQuery: MySQL reference data queries

Benefits:
    - SQL separated from Python code for easier maintenance
    - Version control for complex queries
    - Query reuse across different contexts
    - Easy query debugging and testing
    - Consistent query organization
"""

from enum import StrEnum
from functools import cache
from pathlib import Path

SQL_DIR = Path(__file__).parent


class DuckDBQuery(StrEnum):
    """DuckDB operation queries."""

    CreateUpcLookup = 'create_upc_lookup'
    GetUniqueUpcs = 'get_unique_upcs'
    GetValidationResults = 'get_validation_results'
    RegisterMacros = 'register_macros'
    ValidateAdjustments = 'validate_adjustments'


@cache
def load_sql(query_name: str) -> str:
    """Load SQL query from file.

    Args:
        query_name: Name of the SQL file (e.g., 'load_account_contracts')

    Returns:
        SQL query string
    """
    sql_path = SQL_DIR / f'{query_name}.sql'
    return sql_path.read_text()