"""Transaction repository"""

from typing import List, Optional
from datetime import datetime, timedelta
from sqlalchemy.orm import Session
from sqlalchemy import and_
from app.db.models import Transaction, TransactionType
from app.db.repositories.base import BaseRepository


class TransactionRepository(BaseRepository[Transaction]):
    """Transaction data access operations"""

    def __init__(self, session: Session):
        super().__init__(session, Transaction)

    def get_family_transactions(self, family_id: int, skip: int = 0, limit: int = 50) -> List[Transaction]:
        """Get transactions for family"""
        return (
            self.session.query(Transaction)
            .filter(Transaction.family_id == family_id)
            .order_by(Transaction.transaction_date.desc())
            .offset(skip)
            .limit(limit)
            .all()
        )

    def get_transactions_by_period(
        self, family_id: int, start_date: datetime, end_date: datetime
    ) -> List[Transaction]:
        """Get transactions within date range"""
        return (
            self.session.query(Transaction)
            .filter(
                and_(
                    Transaction.family_id == family_id,
                    Transaction.transaction_date >= start_date,
                    Transaction.transaction_date <= end_date
                )
            )
            .order_by(Transaction.transaction_date.desc())
            .all()
        )

    def get_transactions_by_category(
        self, family_id: int, category_id: int, start_date: datetime, end_date: datetime
    ) -> List[Transaction]:
        """Get transactions by category in date range"""
        return (
            self.session.query(Transaction)
            .filter(
                and_(
                    Transaction.family_id == family_id,
                    Transaction.category_id == category_id,
                    Transaction.transaction_date >= start_date,
                    Transaction.transaction_date <= end_date
                )
            )
            .all()
        )

    def get_user_transactions(self, user_id: int, days: int = 30) -> List[Transaction]:
        """Get user's recent transactions"""
        start_date = datetime.utcnow() - timedelta(days=days)
        return (
            self.session.query(Transaction)
            .filter(
                and_(
                    Transaction.user_id == user_id,
                    Transaction.transaction_date >= start_date
                )
            )
            .order_by(Transaction.transaction_date.desc())
            .all()
        )

    def sum_by_category(
        self, family_id: int, category_id: int, start_date: datetime, end_date: datetime
    ) -> float:
        """Calculate sum of transactions by category"""
        result = (
            self.session.query(Transaction)
            .filter(
                and_(
                    Transaction.family_id == family_id,
                    Transaction.category_id == category_id,
                    Transaction.transaction_date >= start_date,
                    Transaction.transaction_date <= end_date,
                    Transaction.transaction_type == TransactionType.EXPENSE
                )
            )
            .all()
        )
        return sum(t.amount for t in result)