from datetime import date, datetime

from sqlalchemy import delete, func, select
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import selectinload

from app.domain.entities.voucher import LedgerEntry, Voucher, VoucherEntry
from app.domain.enums import VoucherStatus, VoucherType
from app.domain.repositories.voucher_repository import VoucherRepository
from app.infrastructure.db.models import LedgerEntryModel, VoucherEntryModel, VoucherModel, new_id
from app.infrastructure.repositories.mysql_utils import to_decimal

VOUCHER_PREFIX = {
    VoucherType.JOURNAL: "JV",
    VoucherType.PAYMENT: "PV",
    VoucherType.RECEIPT: "RV",
    VoucherType.CONTRA: "CV",
    VoucherType.SALES: "SV",
    VoucherType.PURCHASE: "PU",
}


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

    def _to_voucher(self, row: VoucherModel) -> Voucher:
        entries = [
            VoucherEntry(
                line_number=entry.line_number,
                account_id=entry.account_id,
                account_code=entry.account_code,
                account_name=entry.account_name,
                description=entry.description,
                debit_amount=to_decimal(entry.debit_amount),
                credit_amount=to_decimal(entry.credit_amount),
            )
            for entry in (row.entries or [])
        ]
        return Voucher(
            id=row.id,
            company_id=row.company_id,
            voucher_number=row.voucher_number,
            voucher_type=VoucherType(row.voucher_type),
            voucher_date=row.voucher_date,
            reference=row.reference,
            narration=row.narration,
            status=VoucherStatus(row.status),
            entries=entries,
            total_debit=to_decimal(row.total_debit),
            total_credit=to_decimal(row.total_credit),
            posted_at=row.posted_at,
            cancelled_at=row.cancelled_at,
            created_at=row.created_at,
            updated_at=row.updated_at,
        )

    def _to_ledger(self, row: LedgerEntryModel) -> LedgerEntry:
        return LedgerEntry(
            id=row.id,
            company_id=row.company_id,
            account_id=row.account_id,
            account_code=row.account_code,
            account_name=row.account_name,
            voucher_id=row.voucher_id,
            voucher_number=row.voucher_number,
            voucher_date=row.voucher_date,
            entry_date=row.entry_date,
            description=row.description,
            debit_amount=to_decimal(row.debit_amount),
            credit_amount=to_decimal(row.credit_amount),
            running_balance=to_decimal(row.running_balance),
            created_at=row.created_at,
        )

    async def create(self, voucher: Voucher) -> Voucher:
        voucher_id = voucher.id or new_id()
        row = VoucherModel(
            id=voucher_id,
            company_id=voucher.company_id,
            voucher_number=voucher.voucher_number,
            voucher_type=voucher.voucher_type.value,
            voucher_date=voucher.voucher_date,
            reference=voucher.reference,
            narration=voucher.narration,
            status=voucher.status.value,
            total_debit=voucher.total_debit,
            total_credit=voucher.total_credit,
            posted_at=voucher.posted_at,
            cancelled_at=voucher.cancelled_at,
            created_at=voucher.created_at,
            updated_at=voucher.updated_at,
            entries=[
                VoucherEntryModel(
                    id=new_id(),
                    voucher_id=voucher_id,
                    line_number=entry.line_number,
                    account_id=entry.account_id,
                    account_code=entry.account_code,
                    account_name=entry.account_name,
                    description=entry.description,
                    debit_amount=entry.debit_amount,
                    credit_amount=entry.credit_amount,
                )
                for entry in voucher.entries
            ],
        )
        self._session.add(row)
        await self._session.commit()
        return await self.get_by_id(voucher_id)  # type: ignore[return-value]

    async def get_by_id(self, voucher_id: str, company_id: str | None = None) -> Voucher | None:
        query = (
            select(VoucherModel)
            .options(selectinload(VoucherModel.entries))
            .where(VoucherModel.id == voucher_id)
        )
        if company_id:
            query = query.where(VoucherModel.company_id == company_id)
        result = await self._session.execute(query)
        row = result.scalar_one_or_none()
        return self._to_voucher(row) if row else None

    async def get_by_number(self, company_id: str, voucher_number: str) -> Voucher | None:
        result = await self._session.execute(
            select(VoucherModel)
            .options(selectinload(VoucherModel.entries))
            .where(
                VoucherModel.company_id == company_id,
                VoucherModel.voucher_number == voucher_number,
            )
        )
        row = result.scalar_one_or_none()
        return self._to_voucher(row) if row else None

    def _build_filters(
        self,
        company_id: str,
        status: VoucherStatus | None,
        voucher_type: VoucherType | None,
        from_date: date | None,
        to_date: date | None,
    ):
        filters = [VoucherModel.company_id == company_id]
        if status:
            filters.append(VoucherModel.status == status.value)
        if voucher_type:
            filters.append(VoucherModel.voucher_type == voucher_type.value)
        if from_date:
            filters.append(VoucherModel.voucher_date >= from_date)
        if to_date:
            filters.append(VoucherModel.voucher_date <= to_date)
        return filters

    async def list_all(
        self,
        company_id: str,
        status: VoucherStatus | None = None,
        voucher_type: VoucherType | None = None,
        from_date: date | None = None,
        to_date: date | None = None,
        skip: int = 0,
        limit: int = 100,
    ) -> list[Voucher]:
        filters = self._build_filters(company_id, status, voucher_type, from_date, to_date)
        result = await self._session.execute(
            select(VoucherModel)
            .options(selectinload(VoucherModel.entries))
            .where(*filters)
            .order_by(VoucherModel.voucher_date.desc())
            .offset(skip)
            .limit(limit)
        )
        return [self._to_voucher(row) for row in result.scalars().unique().all()]

    async def count(
        self,
        company_id: str,
        status: VoucherStatus | None = None,
        voucher_type: VoucherType | None = None,
        from_date: date | None = None,
        to_date: date | None = None,
    ) -> int:
        filters = self._build_filters(company_id, status, voucher_type, from_date, to_date)
        result = await self._session.execute(
            select(func.count()).select_from(VoucherModel).where(*filters)
        )
        return int(result.scalar_one())

    async def update(self, voucher_id: str, voucher: Voucher) -> Voucher | None:
        result = await self._session.execute(
            select(VoucherModel)
            .options(selectinload(VoucherModel.entries))
            .where(VoucherModel.id == voucher_id)
        )
        row = result.scalar_one_or_none()
        if not row:
            return None

        row.company_id = voucher.company_id
        row.voucher_number = voucher.voucher_number
        row.voucher_type = voucher.voucher_type.value
        row.voucher_date = voucher.voucher_date
        row.reference = voucher.reference
        row.narration = voucher.narration
        row.status = voucher.status.value
        row.total_debit = voucher.total_debit
        row.total_credit = voucher.total_credit
        row.posted_at = voucher.posted_at
        row.cancelled_at = voucher.cancelled_at
        row.updated_at = datetime.utcnow()

        row.entries.clear()
        for entry in voucher.entries:
            row.entries.append(
                VoucherEntryModel(
                    id=new_id(),
                    voucher_id=voucher_id,
                    line_number=entry.line_number,
                    account_id=entry.account_id,
                    account_code=entry.account_code,
                    account_name=entry.account_name,
                    description=entry.description,
                    debit_amount=entry.debit_amount,
                    credit_amount=entry.credit_amount,
                )
            )

        await self._session.commit()
        return await self.get_by_id(voucher_id)

    async def get_next_voucher_number(self, company_id: str, voucher_type: VoucherType) -> str:
        prefix = VOUCHER_PREFIX[voucher_type]
        year = datetime.utcnow().year
        pattern = f"{prefix}-{year}-%"
        result = await self._session.execute(
            select(VoucherModel.voucher_number)
            .where(
                VoucherModel.company_id == company_id,
                VoucherModel.voucher_number.like(pattern),
            )
            .order_by(VoucherModel.voucher_number.desc())
            .limit(1)
        )
        latest = result.scalar_one_or_none()
        if not latest:
            return f"{prefix}-{year}-0001"
        last_number = int(str(latest).split("-")[-1])
        return f"{prefix}-{year}-{last_number + 1:04d}"

    async def create_ledger_entries(self, entries: list[LedgerEntry]) -> list[LedgerEntry]:
        if not entries:
            return []
        rows = [
            LedgerEntryModel(
                id=entry.id or new_id(),
                company_id=entry.company_id,
                account_id=entry.account_id,
                account_code=entry.account_code,
                account_name=entry.account_name,
                voucher_id=entry.voucher_id,
                voucher_number=entry.voucher_number,
                voucher_date=entry.voucher_date,
                entry_date=entry.entry_date,
                description=entry.description,
                debit_amount=entry.debit_amount,
                credit_amount=entry.credit_amount,
                running_balance=entry.running_balance,
                created_at=entry.created_at,
            )
            for entry in entries
        ]
        self._session.add_all(rows)
        await self._session.commit()
        for row in rows:
            await self._session.refresh(row)
        return [self._to_ledger(row) for row in rows]

    async def get_ledger_entries(
        self,
        company_id: str,
        account_id: str,
        from_date: date | None = None,
        to_date: date | None = None,
    ) -> list[LedgerEntry]:
        filters = [
            LedgerEntryModel.company_id == company_id,
            LedgerEntryModel.account_id == account_id,
        ]
        if from_date:
            filters.append(LedgerEntryModel.entry_date >= from_date)
        if to_date:
            filters.append(LedgerEntryModel.entry_date <= to_date)
        result = await self._session.execute(
            select(LedgerEntryModel)
            .where(*filters)
            .order_by(LedgerEntryModel.entry_date.asc(), LedgerEntryModel.created_at.asc())
        )
        return [self._to_ledger(row) for row in result.scalars().all()]

    async def delete_ledger_entries_by_voucher(self, voucher_id: str) -> None:
        await self._session.execute(
            delete(LedgerEntryModel).where(LedgerEntryModel.voucher_id == voucher_id)
        )
        await self._session.commit()