from datetime import date, datetime
from decimal import Decimal

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

from app.domain.entities.chart_of_account import ChartOfAccount
from app.domain.enums import AccountNature, AccountType
from app.domain.repositories.chart_of_account_repository import ChartOfAccountRepository
from app.infrastructure.db.models import ChartOfAccountModel, new_id
from app.infrastructure.repositories.mysql_utils import to_decimal


class MySQLChartOfAccountRepository(ChartOfAccountRepository):
    def __init__(self, session: AsyncSession) -> None:
        self._session = session

    def _to_entity(self, row: ChartOfAccountModel) -> ChartOfAccount:
        return ChartOfAccount(
            id=row.id,
            company_id=row.company_id,
            code=row.code,
            name=row.name,
            account_type=AccountType(row.account_type),
            nature=AccountNature(row.nature),
            parent_id=row.parent_id,
            level=row.level,
            is_group=row.is_group,
            is_active=row.is_active,
            opening_balance=to_decimal(row.opening_balance),
            current_balance=to_decimal(row.current_balance),
            description=row.description,
            created_at=row.created_at,
            updated_at=row.updated_at,
        )

    async def create(self, account: ChartOfAccount) -> ChartOfAccount:
        row = ChartOfAccountModel(
            id=account.id or new_id(),
            company_id=account.company_id,
            code=account.code,
            name=account.name,
            account_type=account.account_type.value,
            nature=account.nature.value,
            parent_id=account.parent_id,
            level=account.level,
            is_group=account.is_group,
            is_active=account.is_active,
            opening_balance=account.opening_balance,
            current_balance=account.current_balance,
            description=account.description,
            created_at=account.created_at,
            updated_at=account.updated_at,
        )
        self._session.add(row)
        await self._session.commit()
        await self._session.refresh(row)
        return self._to_entity(row)

    async def get_by_id(self, account_id: str, company_id: str | None = None) -> ChartOfAccount | None:
        query = select(ChartOfAccountModel).where(ChartOfAccountModel.id == account_id)
        if company_id:
            query = query.where(ChartOfAccountModel.company_id == company_id)
        result = await self._session.execute(query)
        row = result.scalar_one_or_none()
        return self._to_entity(row) if row else None

    async def get_by_code(self, company_id: str, code: str) -> ChartOfAccount | None:
        result = await self._session.execute(
            select(ChartOfAccountModel).where(
                ChartOfAccountModel.company_id == company_id,
                ChartOfAccountModel.code == code,
            )
        )
        row = result.scalar_one_or_none()
        return self._to_entity(row) if row else None

    async def list_all(
        self,
        company_id: str,
        account_type: AccountType | None = None,
        is_active: bool | None = None,
        skip: int = 0,
        limit: int = 100,
    ) -> list[ChartOfAccount]:
        query = select(ChartOfAccountModel).where(ChartOfAccountModel.company_id == company_id)
        if account_type:
            query = query.where(ChartOfAccountModel.account_type == account_type.value)
        if is_active is not None:
            query = query.where(ChartOfAccountModel.is_active == is_active)
        result = await self._session.execute(
            query.order_by(ChartOfAccountModel.code.asc()).offset(skip).limit(limit)
        )
        return [self._to_entity(row) for row in result.scalars().all()]

    async def count(
        self,
        company_id: str,
        account_type: AccountType | None = None,
        is_active: bool | None = None,
    ) -> int:
        query = select(func.count()).select_from(ChartOfAccountModel).where(
            ChartOfAccountModel.company_id == company_id
        )
        if account_type:
            query = query.where(ChartOfAccountModel.account_type == account_type.value)
        if is_active is not None:
            query = query.where(ChartOfAccountModel.is_active == is_active)
        result = await self._session.execute(query)
        return int(result.scalar_one())

    async def update(self, account_id: str, account: ChartOfAccount) -> ChartOfAccount | None:
        row = await self._session.get(ChartOfAccountModel, account_id)
        if not row:
            return None
        row.company_id = account.company_id
        row.code = account.code
        row.name = account.name
        row.account_type = account.account_type.value
        row.nature = account.nature.value
        row.parent_id = account.parent_id
        row.level = account.level
        row.is_group = account.is_group
        row.is_active = account.is_active
        row.opening_balance = account.opening_balance
        row.current_balance = account.current_balance
        row.description = account.description
        row.updated_at = datetime.utcnow()
        await self._session.commit()
        await self._session.refresh(row)
        return self._to_entity(row)

    async def delete(self, account_id: str) -> bool:
        row = await self._session.get(ChartOfAccountModel, account_id)
        if not row:
            return False
        await self._session.delete(row)
        await self._session.commit()
        return True

    async def update_balance(self, account_id: str, debit: float, credit: float) -> None:
        row = await self._session.get(ChartOfAccountModel, account_id)
        if not row:
            return
        current = to_decimal(row.current_balance)
        debit_dec = to_decimal(debit)
        credit_dec = to_decimal(credit)
        if row.nature == "debit":
            new_balance = current + debit_dec - credit_dec
        else:
            new_balance = current + credit_dec - debit_dec
        row.current_balance = new_balance
        row.updated_at = datetime.utcnow()
        await self._session.commit()

    async def get_balances_as_of(self, company_id: str, as_of_date: date) -> list[ChartOfAccount]:
        return await self.list_all(company_id, is_active=True, skip=0, limit=10000)