from decimal import Decimal
from typing import Iterable, List, Optional

from aleph_message.models import PaymentType
from sqlalchemy import asc, delete, func, select
from sqlalchemy.dialects.postgresql import insert
from sqlalchemy.sql import Insert

from aleph.db.models import ChainTxDb, message_confirmations
from aleph.db.models.account_costs import AccountCostsDb
from aleph.db.models.messages import MessageStatusDb
from aleph.toolkit.costs import format_cost
from aleph.types.db_session import DbSession
from aleph.types.message_status import MessageStatus


def get_total_cost_for_address(
    session: DbSession,
    address: str,
    payment_type: Optional[PaymentType] = PaymentType.hold,
) -> Decimal:
    if payment_type == PaymentType.hold:
        total_prop = AccountCostsDb.cost_hold
    elif payment_type == PaymentType.superfluid:
        total_prop = AccountCostsDb.cost_stream
    elif payment_type == PaymentType.credit:
        total_prop = AccountCostsDb.cost_credit
    else:
        total_prop = AccountCostsDb.cost_hold

    select_stmt = (
        select(func.sum(total_prop))
        .select_from(AccountCostsDb)
        .where(
            (AccountCostsDb.owner == address)
            & (AccountCostsDb.payment_type == payment_type)
        )
    )

    total_cost = session.execute(select_stmt).scalar()
    return format_cost(Decimal(total_cost or 0))


def get_total_costs_for_address_grouped_by_message(
    session: DbSession,
    address: str,
    payment_type: Optional[PaymentType] = PaymentType.hold,
):
    if payment_type == PaymentType.hold:
        total_prop = AccountCostsDb.cost_hold
    elif payment_type == PaymentType.superfluid:
        total_prop = AccountCostsDb.cost_stream
    elif payment_type == PaymentType.credit:
        total_prop = AccountCostsDb.cost_credit
    else:
        total_prop = AccountCostsDb.cost_hold

    id_field = func.min(AccountCostsDb.id)

    select_stmt = (
        select(
            AccountCostsDb.item_hash, ChainTxDb.height, func.sum(total_prop), id_field
        )
        .select_from(AccountCostsDb)
        .join(
            message_confirmations,
            message_confirmations.c.item_hash == AccountCostsDb.item_hash,
        )
        .join(ChainTxDb, message_confirmations.c.tx_hash == ChainTxDb.hash)
        .where(
            (AccountCostsDb.owner == address)
            & (AccountCostsDb.payment_type == payment_type)
        )
        .group_by(AccountCostsDb.item_hash, ChainTxDb.height)
        .order_by(asc(id_field))
    )

    return (session.execute(select_stmt)).all()


def get_message_costs(session: DbSession, item_hash: str) -> Iterable[AccountCostsDb]:
    select_stmt = select(AccountCostsDb).where(AccountCostsDb.item_hash == item_hash)
    return (session.execute(select_stmt)).scalars().all()


def make_costs_upsert_query(costs: List[AccountCostsDb]) -> Insert:
    costs_dict = [
        cost.to_dict(
            exclude={
                "id",
            }
        )
        for cost in costs
    ]

    upsert_stmt = insert(AccountCostsDb).values(costs_dict)

    return upsert_stmt.on_conflict_do_update(
        constraint="account_costs_owner_item_hash_type_name_key",
        set_={
            "cost_hold": upsert_stmt.excluded.cost_hold,
            "cost_stream": upsert_stmt.excluded.cost_stream,
            "cost_credit": upsert_stmt.excluded.cost_credit,
        },
    )


def delete_costs_for_message(session: DbSession, item_hash: str) -> None:
    delete_stmt = delete(AccountCostsDb).where(AccountCostsDb.item_hash == item_hash)
    session.execute(delete_stmt)


def delete_costs_for_forgotten_and_deleted_messages(session: DbSession) -> None:
    delete_stmt = (
        delete(AccountCostsDb)
        .where(AccountCostsDb.item_hash == MessageStatusDb.item_hash)
        .where(
            (MessageStatusDb.status == MessageStatus.FORGOTTEN)
            | (MessageStatusDb.status == MessageStatus.REMOVED)
        )
        .execution_options(synchronize_session=False)
    )
    session.execute(delete_stmt)
