"""
RateLoader logic for processing price data and generating PL/SQL.

Implements the legacy "double putrate" rule exactly as specified in PRD §2.3.
Handles JSON parsing errors gracefully and executes PL/SQL blocks against Oracle.
"""

import json
from typing import Dict, List, Any, Optional
from core.oracle import OracleWrapper, create_oracle_wrapper
from core.logger import get_rateloader_logger


def build_plsql(prices: List[Dict[str, Any]]) -> str:
    """
    Build PL/SQL block from price data using the legacy "double putrate" rule.
    
    This function replicates the exact PL/SQL block construction as quoted in PRD §2.3:
    - Double m_rlputrate when guests == 1 (first with 0, then with actual guests)
    - Single m_rlputrate for other guest counts
    - m_rladdchild for each child rate
    
    Args:
        prices: List of price dictionaries with keys: date, category, ratecode, price, guests, children_rates
        
    Returns:
        PL/SQL block as string
    """
    logger = get_rateloader_logger()
    query = ""
    
    try:
        for price in prices:
            # Double putrate rule for guests == 1
            if price.get("guests") == 1:
                query += (
                    f"m_rlputrate(to_date('{price['date']}', 'yyyy-mm-dd'), "
                    f"'{price['category']}', '{price['ratecode']}', {price['price']}, 0);\n"
                )
            
            # Always add the main rate
            query += (
                f"m_rlputrate(to_date('{price['date']}', 'yyyy-mm-dd'), "
                f"'{price['category']}', '{price['ratecode']}', {price['price']}, {price['guests']});\n"
            )
            
            # Add child rates if present
            for child_rate in price.get("children_rates", []):
                query += (
                    f"m_rladdchild(to_date('{price['date']}', 'yyyy-mm-dd'), "
                    f"'{price['category']}', '{price['ratecode']}', {child_rate['price']}, "
                    f"'{child_rate['age_category']}');\n"
                )
        
        logger.debug(f"Generated PL/SQL block with {len(prices)} price entries")
        return query
        
    except KeyError as e:
        logger.error(f"Missing required field in price data: {str(e)}")
        raise
    except Exception as e:
        logger.error(f"Error building PL/SQL block: {str(e)}")
        raise


def process_prices(data: Dict[str, Any], oracle_connector: Optional[OracleWrapper] = None) -> bool:
    """
    Process price data by building and executing PL/SQL block.
    
    Args:
        data: Dictionary containing price data under 'prices' key
        oracle_connector: OracleWrapper instance. If None, creates a new one.
        
    Returns:
        True if processing succeeded, False otherwise
    """
    logger = get_rateloader_logger()
    
    try:
        # Extract prices from data
        if not isinstance(data, dict):
            logger.error("Invalid data format: expected dictionary")
            return False
            
        if "prices" not in data:
            logger.error("Missing 'prices' key in data")
            return False
            
        prices = data["prices"]
        if not isinstance(prices, list):
            logger.error("Invalid prices format: expected list")
            return False
            
        if not prices:
            logger.warning("Empty prices list - nothing to process")
            return True
        
        # Build PL/SQL block
        plsql_block = build_plsql(prices)
        logger.info(f"Built PL/SQL block with {len(prices)} price entries")
        
        # Execute PL/SQL block
        if oracle_connector is None:
            oracle_connector = create_oracle_wrapper()
            should_disconnect = True
        else:
            should_disconnect = False
            
        try:
            if not oracle_connector.is_connected() and not oracle_connector.connect():
                logger.error("Failed to connect to Oracle database")
                return False
                
            # Execute the PL/SQL block
            logger.debug("Executing PL/SQL block")
            cursor = oracle_connector.connection.cursor()
            cursor.execute(plsql_block)
            oracle_connector.connection.commit()
            cursor.close()
            
            logger.info(f"Successfully processed {len(prices)} price entries")
            return True
            
        except Exception as e:
            logger.error(f"Error executing PL/SQL block: {str(e)}")
            if oracle_connector.connection:
                try:
                    oracle_connector.connection.rollback()
                except:
                    pass
            return False
        finally:
            if should_disconnect and oracle_connector:
                oracle_connector.disconnect()
                
    except Exception as e:
        logger.error(f"Error processing prices: {str(e)}")
        return False


def parse_json_data(json_str: str) -> Optional[Dict[str, Any]]:
    """
    Parse JSON string and handle errors gracefully.
    
    Args:
        json_str: JSON string to parse
        
    Returns:
        Parsed dictionary or None if parsing failed
    """
    logger = get_rateloader_logger()
    
    try:
        data = json.loads(json_str)
        logger.debug("JSON data parsed successfully")
        return data
    except json.JSONDecodeError as e:
        logger.error(f"JSON parsing error: {str(e)}")
        return None
    except Exception as e:
        logger.error(f"Unexpected error parsing JSON: {str(e)}")
        return None


def validate_price_data(price: Dict[str, Any]) -> bool:
    """
    Validate a single price entry has all required fields.
    
    Args:
        price: Price dictionary to validate
        
    Returns:
        True if valid, False otherwise
    """
    required_fields = ["date", "category", "ratecode", "price", "guests"]
    
    for field in required_fields:
        if field not in price:
            return False
            
    # Validate children_rates if present
    if "children_rates" in price:
        children_rates = price["children_rates"]
        if not isinstance(children_rates, list):
            return False
            
        for child_rate in children_rates:
            if not isinstance(child_rate, dict):
                return False
            if "price" not in child_rate or "age_category" not in child_rate:
                return False
                
    return True