from datetime import datetime, timezone
from typing import Dict, List, Optional, Tuple
from uuid import UUID

from fastapi import HTTPException, status
from sqlalchemy import and_, func, or_
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import Session, aliased
from sqlalchemy.sql.expression import nullslast

from src.apps.rollup.schemas.rollup import RollupOutput
from src.core.config import settings
from src.core.exceptions import APIException
from src.utils.constants import API_PREFIXES
from src.utils.pagination import QueryPaginator
from typing import List, Optional, Dict
from src.apps.web_crawler.models.web_crawler import WebCrawler
from src.apps.wine.retailer.models.retailer import Retailer
from src.apps.wine.vintage.models.vintage import Vintage
from src.apps.wine.bottle_size.models.bottle_size import BottleSize


async def create_rollup_output(
    web_crawler,
    retailer,
    row,
    vintage_map,
    bottle_size_map,
    mongo_id
) -> Optional[RollupOutput]:
    """
    Create a RollupOutput object for wine alerts with additional validations and transformations.

    Args:
        web_crawler (WebCrawler): The web crawler object.
        retailer (Retailer): The retailer object.
        row (List[str]): The row data.
        vintage_map (Dict[str, Vintage]): Map of vintage IDs to Vintage objects.
        bottle_size_map (Dict[str, BottleSize]): Map of bottle size IDs to BottleSize objects.
        mongo_id (str): The MongoDB ID.

    Returns:
        Optional[RollupOutput]: The created RollupOutput object or None if validation fails.
    """
    try:
        # print(f"Processing row: {row} with mongo_id: {mongo_id}")
        wine_id = row[0].strip() if len(row) > 0 else ""
        price = row[6].strip() if len(row) > 6 else ""
        bottle_size_str = row[4].strip() if len(row) > 4 else ""
        vintage_str = row[5].strip() if len(row) > 5 else ""

        if not wine_id or wine_id.lower() in ["?", "x", "z"]:
            wine_id = ""

        if not price:
            return None

        rollup_output = RollupOutput()

        rollup_output.wine_alert_id = wine_id[:9] if len(wine_id) > 9 else wine_id
        rollup_output.wine_id = mongo_id or ""

        if wine_id and len(wine_id) > 12:
            vintage = vintage_map.get(wine_id[9:12])
            rollup_output.vintage = vintage.name if vintage else ""
        else:
            parsed_vintage = int(vintage_str) if vintage_str.isdigit() else None
            rollup_output.vintage = "NV" if parsed_vintage and parsed_vintage < 1981 else vintage_str

        if not rollup_output.vintage:
            return None

        if wine_id and len(wine_id) >= 2:
            bottle_size = bottle_size_map.get(wine_id[-2:])
            rollup_output.bottle_size = bottle_size.name if bottle_size else ""
        else:
            rollup_output.bottle_size = bottle_size_str

        if not rollup_output.bottle_size:
            return None

        rollup_output.retailer = web_crawler.code or ""

        try:
            rollup_output.price = float(price)
            rollup_output.original_price = float(price)
        except ValueError:
            return None

        rollup_output.currency = web_crawler.currency_code or ""
        rollup_output.tax_notes = row[7].strip() if len(row) > 7 else ""
        rollup_output.wine_url = row[8].strip() if len(row) > 8 else retailer.web_url or ""
        rollup_output.actual_retailer_description = row[9].strip() if len(row) > 9 else ""
        rollup_output.override_price = ""
        rollup_output.auction = ""
        rollup_output.retailer_name = web_crawler.name or retailer.name or web_crawler.code or ""
        rollup_output.address = web_crawler.address or retailer.address or ""
        rollup_output.city = web_crawler.city or retailer.city or ""
        rollup_output.state = web_crawler.state or retailer.state or ""
        rollup_output.zip = web_crawler.zip or retailer.postal_code or ""
        rollup_output.country = web_crawler.country or retailer.country or ""
        rollup_output.ship_to_country = web_crawler.ship_to_country or retailer.ship_to or ""
        rollup_output.phone = web_crawler.phone or retailer.phone or ""
        rollup_output.url = retailer.web_url or ""
        rollup_output.listing = web_crawler.listing
        rollup_output.download = web_crawler.download
        rollup_output.your_code = row[12].strip() if len(row) > 12 else ""
        # print(f"Generated rollup output: {rollup_output}")
        return rollup_output
    except Exception as e:
        raise APIException(
            module="create_rollup_output",
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            error={"exception": str(e)},
            message="Failed to create rollup output"
        )