from sqlalchemy.orm import Session
from sqlalchemy import text
from typing import List, Optional
from app.models.card import Card
from app.schemas.card import CardCreate


class CardService:
    @staticmethod
    def create(db: Session, obj_in: CardCreate) -> Card:
        db_obj = Card(**obj_in.dict())
        db.add(db_obj)
        db.commit()
        db.refresh(db_obj)
        return db_obj

    @staticmethod
    def get_paginated(db: Session, page: int, size: int, culture: str):
        offset = page * size
        return (
            db.query(Card)
            .filter(Card.culture == culture)
            .order_by(Card.created_at.desc())
            .offset(offset)
            .limit(size)
            .all()
        )

    @staticmethod
    def get_by_keywords(db: Session, keywords: List[str], page: int, size: int, culture: str):
        offset = page * size
        query = db.query(Card).filter(Card.culture == culture)

        # 多关键词模糊查询
        for kw in keywords:
            like_str = f"%{kw.strip()}%"
            query = query.filter(
                (Card.title.like(like_str)) |
                (Card.content.like(like_str)) |
                (Card.label.like(like_str))
            )

        return (
            query.order_by(Card.created_at.desc())
            .offset(offset)
            .limit(size)
            .all()
        )