from typing import List, Optional
from datetime import datetime

from sqlalchemy.orm import Session

from src.domain.entities.product import Product
from src.domain.interfaces.product_repository import ProductRepositoryInterface
from src.infrastructure.models.product_model import ProductModel
from src.infrastructure.models.merchant_offer_model import ProductOfferModel


class SqlProductRepository(ProductRepositoryInterface):
    """SQLAlchemy implementation of ProductRepositoryInterface."""

    def __init__(self, db: Session):
        self.db = db

    def _linked_offer_ids(self, product_id: int) -> List[int]:
        rows = (
            self.db.query(ProductOfferModel.offer_id)
            .filter(ProductOfferModel.product_id == product_id)
            .filter(ProductOfferModel.deleted_at == None)
            .all()
        )
        return [r.offer_id for r in rows]

    def _to_entity(self, model: ProductModel) -> Product:
        return Product(
            id=model.id,
            merchant_id=model.merchant_id,
            name=model.name or '',
            code=model.code or '',
            warranty=model.warranty or '',
            description=model.description or '',
            amount=model.amount or '',
            center_id=model.center_id,
            linked_offer_ids=self._linked_offer_ids(model.id),
        )

    def get_by_merchant(self, merchant_id: int) -> List[Product]:
        models = (
            self.db.query(ProductModel)
            .filter(ProductModel.merchant_id == merchant_id)
            .filter(ProductModel.deleted_at == None)
            .all()
        )
        return [self._to_entity(m) for m in models]

    def get_by_id(self, product_id: int) -> Optional[Product]:
        model = (
            self.db.query(ProductModel)
            .filter(ProductModel.id == product_id)
            .filter(ProductModel.deleted_at == None)
            .first()
        )
        return self._to_entity(model) if model else None

    def create(self, merchant_id: int, data: dict, user_id: int) -> Product:
        model = ProductModel(
            merchant_id=merchant_id,
            user_id=user_id,
            name=data.get('name', ''),
            code=data.get('code', ''),
            warranty=data.get('warranty', ''),
            description=data.get('description', ''),
            amount=data.get('amount', ''),
            center_id=data.get('center_id'),
        )
        self.db.add(model)
        self.db.commit()
        self.db.refresh(model)
        return self._to_entity(model)

    def update(self, product_id: int, data: dict) -> Optional[Product]:
        model = (
            self.db.query(ProductModel)
            .filter(ProductModel.id == product_id)
            .filter(ProductModel.deleted_at == None)
            .first()
        )
        if not model:
            return None
        for key, value in data.items():
            if value is not None and hasattr(model, key):
                setattr(model, key, value)
        self.db.commit()
        self.db.refresh(model)
        return self._to_entity(model)

    def delete(self, product_id: int) -> bool:
        model = (
            self.db.query(ProductModel)
            .filter(ProductModel.id == product_id)
            .filter(ProductModel.deleted_at == None)
            .first()
        )
        if not model:
            return False
        model.deleted_at = datetime.utcnow()
        self.db.commit()
        return True