from datetime import datetime
from uuid import UUID
from sqlalchemy.orm import Session
from fastapi import HTTPException, status
from src.core.exceptions import APIException
from src.apps.base.models.country import Country
from src.apps.wine.producer.models.producer import Producer
from src.utils.pagination import QueryPaginator
from src.apps.base.models.regions import Region
from src.apps.wine.wine.models.wine import WineDb,Wine,WineDuplication,WineNoise,WineKeyword
from src.apps.wine.retailer.models.retailer import RetailerWine,Retailer
from sqlalchemy import func, cast, Integer,and_,or_
from src.apps.wine.wine.schemas.wine_db import *
from src.utils.constants import API_PREFIXES
from src.apps.wine.producer.services.producer import get_producer_by_id
from src.apps.wine.wine.services.wine_db import update_wine as update_wine_db_service
from src.apps.wine.wine.schemas.wine_db import WineUpdateRequestSchema
from src.apps.wine.wine.schemas.wine import WineCreateSchema, WineOutputSchema, WineUpdateSchema
from src.apps.wine.wine.services.wine_duplication import create_wine_duplication,get_wine_duplications_for_wine
from src.apps.wine.wine.schemas.wine_duplication import WineDuplicationCreateSchema
from src.apps.wine.retailer.schemas.retailer_wine import RetailerWineCreateSchema
from src.apps.wine.retailer.services.retailer_wine import find_retailer_wine_by_sku_and_bottle_size,get_retailer_wines_by_wine_id,create_retailer_wine
from src.apps.wine.external_input.schemas.external_input import AcceptMatchSchema,AdditionMatchCheckSchema,UndoMatchSchema
from src.apps.wine.wine.services.wine import get_wines_for_wine_db


async def check_addition_matched(
    db: Session,
    payload: AdditionMatchCheckSchema,
):
    """
    Check if a wine with the given mongo_id already exists in the database.
    """
    try:
        query = db.query(Wine).filter(Wine.mongo_id == payload.mongo_id, Wine.deleted_at.is_(None))
        existing_wine = query.first()
        if not existing_wine:
            # check in wine_duplication
            wine_duplication = db.query(WineDuplication).filter(WineDuplication.mongo_id == payload.mongo_id, WineDuplication.deleted_at.is_(None)).first()
            
            wine_duplication_wine = db.query(Wine).filter(Wine.id == wine_duplication.wine_id, Wine.deleted_at.is_(None)).first() if wine_duplication else None
            existing_wine = wine_duplication_wine
            
        existing_wine = WineOutputSchema.model_validate(existing_wine) if existing_wine else {}
        return {
            "id" : existing_wine.id if existing_wine else None,
            "wine_db_id": existing_wine.wine_db.id if existing_wine else None,
            "literal": existing_wine.literal if existing_wine else None,
            "description": existing_wine.wine_db.wine_name if existing_wine else None
        }
    except Exception as e:
        raise APIException(
            module="check_addition_matched",
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            error={"exception": str(e)},
            message="Failed to check addition matched"
        )
        
async def undo_match(
    db: Session,
    payload: UndoMatchSchema,
):
    """
    Undo the match for a given wine by its ID.
    This involves deleting associated retailer wines, wine duplications, and the wine itself.
    """
    try:
        # get wine with id and mongo_id
        wine = db.query(Wine).filter(Wine.id == payload.wine_id, Wine.mongo_id == payload.mongo_id, Wine.deleted_at.is_(None)).first()
        if not wine:
            # get wine_duplication with mongo_id
            wine_duplication = db.query(WineDuplication).filter(WineDuplication.mongo_id == payload.mongo_id, WineDuplication.deleted_at.is_(None)).first()
            if not wine_duplication:
                raise APIException(
                    module="undo_match",
                    status_code=status.HTTP_404_NOT_FOUND,
                    error={"wine_id": payload.wine_id, "mongo_id": payload.mongo_id},
                    message="Wine or Wine Duplication not found"
                )
            
            # delete wine_duplication
            await soft_delete_wine_duplication(db, wine_duplication)
            
            wine = db.query(Wine).filter(Wine.id == wine_duplication.wine_id, Wine.deleted_at.is_(None)).first() if wine_duplication else None
        else:
            # update wines - clear mongo_id and wine_n_id
            wine = await clear_wine_match_identifiers(db, wine)

        existing_wine = WineOutputSchema.model_validate(wine) if wine else None
        
        return {
            "id" : existing_wine.id if existing_wine else None,
            "literal": existing_wine.literal if existing_wine else None,
            "wine_db_id": existing_wine.wine_db.id if existing_wine else None,
            "description": existing_wine.wine_db.wine_name if existing_wine else None
        }
    
    except Exception as e:
        raise APIException(
            module="undo_match",
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            error={"exception": str(e)},
            message="Failed to undo match"
        )
        
        
async def accept_match(
    db: Session,
    payload: AcceptMatchSchema,
):
    try:
        data = await update_wine_db_service(
            db=db,
            wine_id=payload.wine_db_id,
            payload=WineUpdateRequestSchema(
                mongo_id=payload.mongo_id,
                wine_n_id=payload.wine_n_id,
                vintage=payload.vintage,
                wine_literal=payload.wine_literal
            )
        )
        
        return {
            "id" : data.id if data else None,
            "literal": data.literal if data else None,
            "color": data.color if data else None,
            "pattern": data.wine_keyword.pattern if data and data.wine_keyword else None,
        }
        
    except Exception as e:
        raise APIException(
            module="accept_match",
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            error={"exception": str(e)},
            message="Failed to accept match"
        )


async def soft_delete_wine_duplication(db: Session, wine_duplication: WineDuplication) -> None:
    """
    Soft delete a wine duplication by setting its deleted_at timestamp.
    """
    try:
        wine_duplication.deleted_at = datetime.utcnow()
        db.commit()
        db.refresh(wine_duplication)
    except Exception as e:
        raise APIException(
            module="soft_delete_wine_duplication",
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            error={"exception": str(e)},
            message="Failed to delete wine duplication",
        )


async def clear_wine_match_identifiers(db: Session, wine: Wine) -> Wine:
    """
    Clear match identifiers (mongo_id, wine_n_id) for a wine and persist changes.
    """
    try:
        wine.mongo_id = None
        wine.wine_n_id = None
        db.commit()
        db.refresh(wine)
        return wine
    except Exception as e:
        raise APIException(
            module="clear_wine_match_identifiers",
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            error={"exception": str(e)},
            message="Failed to clear wine match identifiers",
        )