from logging import getLogger
from uuid import uuid4
from pandas import DataFrame
from sqlalchemy.orm import Session

from app.db.utils import db_transaction
from app.db.models import CardManabox, CardTCGPlayer, StagedFileProduct, TCGPlayerGroups
from app.services.util._dataframe import ManaboxRow, DataframeUtil
from app.services.file import FileService
from app.services.tcgplayer import TCGPlayerService
from app.services.storage import StorageService

logger = getLogger(__name__)


class ProductService:
    def __init__(
        self,
        db: Session,
        file_service: FileService,
        tcgplayer_service: TCGPlayerService,
        storage_service: StorageService,
    ):
        self.db = db
        self.file_service = file_service
        self.tcgplayer_service = tcgplayer_service
        self.storage_service = storage_service
        self.df_util = DataframeUtil()

    def create_staged_file_product(
        self, file_id: str, card_manabox: CardManabox, row: ManaboxRow
    ) -> StagedFileProduct:
        """Create a staged file product entry.

        Args:
            file_id: The ID of the file being processed
            card_manabox: The Manabox card details
            row: The row data from the Manabox file

        Returns:
            The created staged file product
        """
        staged_product = StagedFileProduct(
            id=str(uuid4()),
            file_id=file_id,
            product_id=card_manabox.product_id,
            quantity=row.quantity,
        )
        with db_transaction(self.db):
            self.db.add(staged_product)
        return staged_product

    def create_card_manabox(
        self, manabox_row: ManaboxRow, card_tcgplayer: CardTCGPlayer
    ) -> CardManabox:
        """Create a Manabox card entry.

        Args:
            manabox_row: The row data from the Manabox file
            card_tcgplayer: The TCGPlayer card details

        Returns:
            The created Manabox card
        """
        if not card_tcgplayer:
            group = (
                self.db.query(TCGPlayerGroups)
                .filter(TCGPlayerGroups.abbreviation == manabox_row.set_code)
                .first()
            )
            card_tcgplayer = self.tcgplayer_service.get_card_tcgplayer_from_manabox_row(
                manabox_row, group.group_id
            )

        card_manabox = CardManabox(
            product_id=card_tcgplayer.product_id,
            name=manabox_row.name,
            set_code=manabox_row.set_code,
            set_name=manabox_row.set_name,
            collector_number=manabox_row.collector_number,
            foil=manabox_row.foil,
            rarity=manabox_row.rarity,
            manabox_id=manabox_row.manabox_id,
            scryfall_id=manabox_row.scryfall_id,
            condition=manabox_row.condition,
            language=manabox_row.language,
        )

        with db_transaction(self.db):
            self.db.add(card_manabox)
            return card_manabox

    def card_manabox_lookup_create_if_not_exist(
        self, manabox_row: ManaboxRow
    ) -> CardManabox:
        """Lookup a Manabox card or create it if it doesn't exist.

        Args:
            manabox_row: The row data from the Manabox file

        Returns:
            The existing or newly created Manabox card
        """
        card_manabox = (
            self.db.query(CardManabox)
            .filter(
                CardManabox.name == manabox_row.name,
                CardManabox.set_code == manabox_row.set_code,
                CardManabox.set_name == manabox_row.set_name,
                CardManabox.collector_number == manabox_row.collector_number,
                CardManabox.foil == manabox_row.foil,
                CardManabox.rarity == manabox_row.rarity,
                CardManabox.manabox_id == manabox_row.manabox_id,
                CardManabox.scryfall_id == manabox_row.scryfall_id,
                CardManabox.condition == manabox_row.condition,
                CardManabox.language == manabox_row.language,
            )
            .first()
        )

        if not card_manabox:
            logger.debug(f"card_manabox not found for row: {manabox_row.__dict__}")
            group = (
                self.db.query(TCGPlayerGroups)
                .filter(TCGPlayerGroups.abbreviation == manabox_row.set_code)
                .first()
            )
            if not group:
                logger.error(f"Group not found for set code: {manabox_row.set_code}")
                logger.error(f"Row data: {manabox_row.__dict__}")
                return None

            card_tcgplayer = self.tcgplayer_service.get_card_tcgplayer_from_manabox_row(
                manabox_row, group.group_id
            )
            if not card_tcgplayer:
                logger.error(f"Card not found for row: {manabox_row.__dict__}")
                return None
            card_manabox = self.create_card_manabox(manabox_row, card_tcgplayer)

        return card_manabox

    def process_manabox_df(self, df: DataFrame, file_id: str) -> None:
        """Process a Manabox dataframe.

        Args:
            df: The Manabox dataframe to process
            file_id: The ID of the file being processed
        """
        for _, row in df.iterrows():
            manabox_row = ManaboxRow(row)
            card_manabox = self.card_manabox_lookup_create_if_not_exist(manabox_row)
            if not card_manabox:
                continue
            self.create_staged_file_product(file_id, card_manabox, row)

    def bg_process_manabox_file(self, file_id: str) -> None:
        """Process a Manabox file in the background.

        Args:
            file_id: The ID of the file to process

        Raises:
            Exception: If there's an error during processing
        """
        try:
            manabox_file = self.file_service.get_file(file_id)
            manabox_df = self.df_util.file_to_df(manabox_file)
            self.process_manabox_df(manabox_df, file_id)

            with db_transaction(self.db):
                manabox_file.status = "completed"

        except Exception as e:
            with db_transaction(self.db):
                manabox_file.status = "error"
            raise e

        try:
            self.storage_service.store_staged_products_for_file(file_id)
        except Exception as e:
            logger.error(f"Error creating storage records: {str(e)}")
            raise e