"""
Fee Settlement CRUD — DB operations only, no business logic.
"""
from datetime import date, datetime, timezone
from typing import List, Optional, Tuple

from sqlalchemy import func, select, update
from sqlalchemy.orm import Session, joinedload

from src.apps.admin.models.fee_settlement_run import FeeSettlementRun, SettlementRunStatus
from src.apps.admin.models.hw_platform_bank_account import HWPlatformBankAccount


# ─── Settlement Account ───────────────────────────────────────────────────────

def get_active_settlement_account(db: Session) -> Optional[HWPlatformBankAccount]:
    return db.execute(
        select(HWPlatformBankAccount).where(
            HWPlatformBankAccount.is_active == True,
            HWPlatformBankAccount.deleted_at.is_(None),
        )
    ).scalar_one_or_none()


def get_settlement_account_by_id(db: Session, account_id: int) -> Optional[HWPlatformBankAccount]:
    return db.execute(
        select(HWPlatformBankAccount).where(
            HWPlatformBankAccount.id == account_id,
            HWPlatformBankAccount.deleted_at.is_(None),
        )
    ).scalar_one_or_none()


def get_settlement_account_history(
    db: Session,
    page: int = 1,
    per_page: int = 20,
) -> Tuple[List[HWPlatformBankAccount], int]:
    q = select(HWPlatformBankAccount).where(
        HWPlatformBankAccount.deleted_at.is_(None),
    )
    total = db.execute(select(func.count()).select_from(q.subquery())).scalar_one()
    items = db.execute(
        q.order_by(HWPlatformBankAccount.created_at.desc())
        .offset((page - 1) * per_page)
        .limit(per_page)
    ).scalars().all()
    return list(items), total


def deactivate_all_settlement_accounts(db: Session) -> List[HWPlatformBankAccount]:
    """Set is_active=False on every non-deleted active account. Returns deactivated accounts for audit logging."""
    active = db.execute(
        select(HWPlatformBankAccount).where(
            HWPlatformBankAccount.is_active == True,
            HWPlatformBankAccount.deleted_at.is_(None),
        )
    ).scalars().all()
    db.execute(
        update(HWPlatformBankAccount)
        .where(HWPlatformBankAccount.deleted_at.is_(None))
        .values(is_active=False, updated_at=datetime.now(timezone.utc))
    )
    return list(active)


def create_settlement_account_record(
    db: Session,
    nickname: str,
    account_holder_name: str,
    bank_name: Optional[str],
    routing_number_enc: str,
    account_number_enc: str,
    account_number_last4: str,
    account_type: str,
    created_by_user_id: Optional[int],
) -> HWPlatformBankAccount:
    account = HWPlatformBankAccount(
        nickname=nickname,
        account_holder_name=account_holder_name,
        bank_name=bank_name,
        routing_number_enc=routing_number_enc,
        account_number_enc=account_number_enc,
        account_number_last4=account_number_last4,
        account_type=account_type,
        is_active=True,
        created_by_user_id=created_by_user_id,
    )
    db.add(account)
    db.flush()
    return account


def soft_delete_settlement_account(db: Session, account: HWPlatformBankAccount) -> None:
    account.deleted_at = datetime.now(timezone.utc)
    account.is_active = False


# ─── Settlement Run ───────────────────────────────────────────────────────────

def get_settlement_run_by_date(db: Session, settlement_date: date) -> Optional[FeeSettlementRun]:
    return db.execute(
        select(FeeSettlementRun).where(
            FeeSettlementRun.settlement_date == settlement_date,
        )
    ).scalar_one_or_none()


def get_settlement_runs(
    db: Session,
    page: int = 1,
    per_page: int = 20,
    status: Optional[str] = None,
    date_from: Optional[date] = None,
    date_to: Optional[date] = None,
) -> Tuple[List[FeeSettlementRun], int]:
    q = (
        select(FeeSettlementRun)
        .options(joinedload(FeeSettlementRun.settlement_account))
    )
    if status:
        q = q.where(FeeSettlementRun.status == status)
    if date_from:
        q = q.where(FeeSettlementRun.settlement_date >= date_from)
    if date_to:
        q = q.where(FeeSettlementRun.settlement_date <= date_to)

    total = db.execute(
        select(func.count()).select_from(
            select(FeeSettlementRun)
            .where(
                *([FeeSettlementRun.status == status] if status else []),
                *([FeeSettlementRun.settlement_date >= date_from] if date_from else []),
                *([FeeSettlementRun.settlement_date <= date_to] if date_to else []),
            )
            .subquery()
        )
    ).scalar_one()

    items = db.execute(
        q.order_by(FeeSettlementRun.settlement_date.desc())
        .offset((page - 1) * per_page)
        .limit(per_page)
    ).unique().scalars().all()
    return list(items), total


def get_settlement_run_by_id(db: Session, run_id: int) -> Optional[FeeSettlementRun]:
    return db.execute(
        select(FeeSettlementRun)
        .options(joinedload(FeeSettlementRun.settlement_account))
        .where(FeeSettlementRun.id == run_id)
    ).unique().scalar_one_or_none()


def create_settlement_run(
    db: Session,
    settlement_date: date,
    settlement_account_id: int,
    triggered_by: str,
) -> FeeSettlementRun:
    run = FeeSettlementRun(
        settlement_date=settlement_date,
        settlement_account_id=settlement_account_id,
        triggered_by=triggered_by,
        status=SettlementRunStatus.PENDING,
        transaction_count=0,
        total_fee_cents=0,
        retry_count=0,
    )
    db.add(run)
    db.flush()
    return run


def get_settlement_run_transactions(
    db: Session,
    run_id: int,
    page: int = 1,
    per_page: int = 50,
) -> Tuple[list, int]:
    from sqlalchemy.orm import joinedload as _jl
    from src.apps.transactions.models.transactions import Transactions
    from src.apps.merchants.models.merchant import Merchant

    q = (
        select(Transactions)
        .options(_jl(Transactions.merchant))
        .where(Transactions.fee_settlement_run_id == run_id)
    )
    total = db.execute(
        select(func.count()).select_from(
            select(Transactions).where(Transactions.fee_settlement_run_id == run_id).subquery()
        )
    ).scalar_one()

    rows = db.execute(
        q.order_by(Transactions.ocurred_at.desc())
        .offset((page - 1) * per_page)
        .limit(per_page)
    ).unique().scalars().all()
    return list(rows), total