from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import selectinload
from database.models import Category, Product

async def get_all_categories(session: AsyncSession) -> list[Category]:
    stmt = select(Category)
    result = await session.execute(stmt)
    return list(result.scalars().all())

async def get_parent_categories(session: AsyncSession) -> list[Category]:
    stmt = select(Category).where(Category.parent_id == None)
    result = await session.execute(stmt)
    return list(result.scalars().all())

async def get_subcategories(session: AsyncSession, parent_id: int) -> list[Category]:
    stmt = select(Category).where(Category.parent_id == parent_id)
    result = await session.execute(stmt)
    return list(result.scalars().all())

async def get_products_by_category(session: AsyncSession, category_id: int) -> list[Product]:
    stmt = select(Product).where(Product.category_id == category_id)
    result = await session.execute(stmt)
    return list(result.scalars().all())

async def get_product_by_id(session: AsyncSession, product_id: int) -> Product | None:
    stmt = select(Product).where(Product.id == product_id).options(selectinload(Product.category))
    result = await session.execute(stmt)
    return result.scalar_one_or_none()

async def upsert_category(session: AsyncSession, name: str, parent_id: int = None) -> Category:
    stmt = select(Category).where(Category.name == name)
    result = await session.execute(stmt)
    cat = result.scalar_one_or_none()
    if not cat:
        cat = Category(name=name, parent_id=parent_id)
        session.add(cat)
        await session.flush()
    else:
        if parent_id is not None:
            cat.parent_id = parent_id
            await session.flush()
    return cat

async def upsert_product(session: AsyncSession, category_id: int, name: str, price: float, description: str = None, stock: int = -1) -> Product:
    stmt = select(Product).where(Product.name == name, Product.category_id == category_id)
    result = await session.execute(stmt)
    prod = result.scalar_one_or_none()
    
    if prod:
        prod.price = price
        if description:
            prod.description = description
        prod.stock = stock
    else:
        prod = Product(
            category_id=category_id,
            name=name,
            price=price,
            description=description,
            stock=stock
        )
        session.add(prod)
    
    await session.commit()
    return prod

from database.models import GiftCard

async def get_and_consume_gift_card(session: AsyncSession, product_id: int, user_id: int) -> str | None:
    stmt = select(GiftCard).where(GiftCard.product_id == product_id, GiftCard.is_sold == False).limit(1)
    result = await session.execute(stmt)
    gc = result.scalar_one_or_none()
    
    if gc:
        gc.is_sold = True
        gc.sold_to_user_id = user_id
        await session.commit()
        return gc.code
    return None

async def delete_category(session: AsyncSession, category_id: int) -> bool:
    cat = await session.get(Category, category_id)
    if cat:
        await session.delete(cat)
        await session.commit()
        return True
    return False

async def update_category_name(session: AsyncSession, category_id: int, new_name: str) -> bool:
    cat = await session.get(Category, category_id)
    if cat:
        cat.name = new_name
        await session.commit()
        return True
    return False

async def delete_product(session: AsyncSession, product_id: int) -> bool:
    prod = await session.get(Product, product_id)
    if prod:
        await session.delete(prod)
        await session.commit()
        return True
    return False

async def update_product_field(session: AsyncSession, product_id: int, field: str, value: any) -> bool:
    prod = await session.get(Product, product_id)
    if prod:
        setattr(prod, field, value)
        await session.commit()
        return True
    return False