from __future__ import annotations

from collections.abc import Sequence
from datetime import date
from decimal import Decimal
from typing import Any

from sqlalchemy import case, func, insert, select
from sqlalchemy.ext.asyncio import AsyncSession

from app.db.models import (
    Currency,
    Operation,
    OperationCode,
    OperationType,
    Portfolio,
    Security,
)
from app.db.types import Quantity


def _sum_quantity(code: str) -> Any:
    # type_ — чтобы и SQLite вернул Decimal, а не float.
    return func.sum(
        case((OperationType.code == code, Operation.quantity), else_=0),
        type_=Quantity,
    )


class OperationRepository:
    def __init__(self, session: AsyncSession) -> None:
        self.session = session

    async def add_many(self, rows: Sequence[dict[str, Any]]) -> None:
        """Пакетная вставка (executemany): в день размещения в файле 1000+
        строк. Коммит — на вызывающей стороне, в одной транзакции с
        import_batch."""
        if rows:
            await self.session.execute(insert(Operation), list(rows))

    async def add(self, **values: Any) -> Operation:
        operation = Operation(**values)
        self.session.add(operation)
        await self.session.flush()
        return operation

    async def list_manual(
        self, limit: int
    ) -> list[tuple[Operation, str, str, str, str, str]]:
        """Ручные операции (без файла), новые сверху ->
        (операция, код типа, портфель, ISIN, краткое имя, валюта)."""
        result = await self.session.execute(
            select(
                Operation,
                OperationType.code,
                Portfolio.code,
                Security.isin,
                Security.name,
                Currency.code,
            )
            .join(OperationType, OperationType.id == Operation.operation_type_id)
            .join(Portfolio, Portfolio.id == Operation.portfolio_id)
            .join(Security, Security.id == Operation.security_id)
            .join(Currency, Currency.id == Operation.currency_id)
            .where(Operation.import_batch_id.is_(None))
            .order_by(Operation.id.desc())
            .limit(limit)
        )
        return [tuple(row) for row in result.all()]

    async def quantities_on(
        self, on_date: date, portfolio_id: int | None = None
    ) -> list[tuple[str, str, str, str, Decimal, Decimal]]:
        """Куплено и продано, шт, по портфелю и бумаге по сделкам с trade_date
        не позже даты -> (портфель, ISIN, краткое имя, валюта бумаги, куплено,
        продано). Пары без BUY/SELL не попадают."""
        bought = _sum_quantity(OperationCode.BUY)
        sold = _sum_quantity(OperationCode.SELL)
        stmt = (
            select(
                Portfolio.code,
                Security.isin,
                Security.name,
                Currency.code,
                bought,
                sold,
            )
            .join(OperationType, OperationType.id == Operation.operation_type_id)
            .join(Portfolio, Portfolio.id == Operation.portfolio_id)
            .join(Security, Security.id == Operation.security_id)
            .join(Currency, Currency.id == Security.currency_id)
            .where(
                OperationType.code.in_((OperationCode.BUY, OperationCode.SELL)),
                Operation.trade_date <= on_date,
            )
            .group_by(
                Portfolio.id,
                Portfolio.code,
                Security.isin,
                Security.name,
                Currency.code,
            )
            .order_by(Portfolio.id, Security.name, Security.isin)
        )
        if portfolio_id is not None:
            stmt = stmt.where(Operation.portfolio_id == portfolio_id)
        result = await self.session.execute(stmt)
        return [tuple(row) for row in result.all()]

    async def open_security_ids(self, on_date: date) -> list[int]:
        """Бумаги, у которых хоть в одном портфеле остаток на конец дня не 0
        (D10, D29). Отрицательный остаток — тоже открытая позиция."""
        balance = _sum_quantity(OperationCode.BUY) - _sum_quantity(OperationCode.SELL)
        result = await self.session.execute(
            select(Operation.security_id)
            .join(OperationType, OperationType.id == Operation.operation_type_id)
            .where(
                OperationType.code.in_((OperationCode.BUY, OperationCode.SELL)),
                Operation.trade_date <= on_date,
            )
            .group_by(Operation.portfolio_id, Operation.security_id)
            .having(balance != 0)
        )
        return sorted(set(result.scalars().all()))