from datetime import datetime
from decimal import Decimal
import json

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

from app.domain.entities.purchase import (
    GoodsReceipt,
    GoodsReceiptLine,
    PurchaseOrder,
    PurchaseOrderLine,
    PurchasePayment,
    PurchasePaymentAllocation,
    Vendor,
    VendorBill,
    VendorBillLine,
)
from app.domain.enums import (
    AmountType,
    GrnStatus,
    PaymentTerms,
    PreferredPaymentMethod,
    PurchaseOrderStatus,
    PurchasePaymentStatus,
    PurchaseType,
    VendorBillStatus,
    VendorBusinessType,
)
from app.domain.repositories.purchase_repository import PurchaseRepository
from app.infrastructure.db.models import (
    BaseUnitModel,
    GoodsReceiptLineModel,
    GoodsReceiptModel,
    ItemModel,
    PurchaseOrderLineModel,
    PurchaseOrderModel,
    PurchasePaymentAllocationModel,
    PurchasePaymentModel,
    VendorBillLineModel,
    VendorBillModel,
    VendorModel,
    WarehouseModel,
    new_id,
)
from app.infrastructure.repositories.mysql_utils import to_decimal


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

    # ---- mappers ----
    def _to_vendor(
        self,
        row: VendorModel,
        warehouse_code: str | None = None,
        warehouse_name: str | None = None,
    ) -> Vendor:
        return Vendor(
            id=row.id,
            company_id=row.company_id,
            code=row.code,
            name=row.name,
            business_type=VendorBusinessType(row.business_type) if row.business_type else None,
            contact_person=row.contact_person or "",
            designation=row.designation,
            phone=row.phone or "",
            alternate_phone=row.alternate_phone,
            email=row.email,
            website=row.website,
            ntn=row.ntn,
            sales_tax_no=row.sales_tax_no,
            address_line1=row.address_line1 or row.address or "",
            address_line2=row.address_line2,
            city=row.city or "",
            state_province=row.state_province,
            country=row.country or "Pakistan",
            postal_code=row.postal_code,
            notes=row.notes,
            payment_terms=PaymentTerms(row.payment_terms) if row.payment_terms else None,
            payment_terms_days=row.payment_terms_days,
            credit_limit=to_decimal(row.credit_limit),
            opening_balance=to_decimal(row.opening_balance),
            currency=row.currency or "PKR",
            po_prefix=row.po_prefix,
            default_warehouse_id=row.default_warehouse_id,
            default_warehouse_code=warehouse_code,
            default_warehouse_name=warehouse_name,
            preferred_payment_method=(
                PreferredPaymentMethod(row.preferred_payment_method)
                if row.preferred_payment_method
                else None
            ),
            logo=row.logo,
            address=row.address,
            account_id=row.account_id,
            is_active=row.is_active,
            created_at=row.created_at,
            updated_at=row.updated_at,
        )

    def _vendor_select_query(self):
        return (
            select(
                VendorModel,
                WarehouseModel.code.label("joined_warehouse_code"),
                WarehouseModel.name.label("joined_warehouse_name"),
            )
            .outerjoin(
                WarehouseModel,
                WarehouseModel.id == VendorModel.default_warehouse_id,
            )
        )

    def _vendor_from_row(self, row) -> Vendor:
        return self._to_vendor(
            row.VendorModel,
            warehouse_code=row.joined_warehouse_code,
            warehouse_name=row.joined_warehouse_name,
        )

    def _to_po_line(
        self,
        row: PurchaseOrderLineModel,
        *,
        item_sku: str | None = None,
        item_name: str | None = None,
        base_unit_code: str | None = None,
        base_unit_name: str | None = None,
    ) -> PurchaseOrderLine:
        return PurchaseOrderLine(
            id=row.id,
            purchase_order_id=row.purchase_order_id,
            line_number=row.line_number,
            item_id=row.item_id,
            item_sku=item_sku,
            item_name=item_name,
            description=row.description,
            base_unit_id=row.base_unit_id,
            base_unit_code=base_unit_code,
            base_unit_name=base_unit_name,
            quantity=to_decimal(row.quantity),
            received_quantity=to_decimal(row.received_quantity),
            billed_quantity=to_decimal(row.billed_quantity),
            unit_price=to_decimal(row.unit_price),
            discount_type=AmountType(row.discount_type or AmountType.PERCENT.value),
            discount_value=to_decimal(getattr(row, "discount_value", 0) or 0),
            discount_amount=to_decimal(getattr(row, "discount_amount", 0) or 0),
            tax_type=AmountType(row.tax_type or AmountType.PERCENT.value),
            tax_rate=to_decimal(row.tax_rate),
            tax_amount=to_decimal(getattr(row, "tax_amount", 0) or 0),
            line_total=to_decimal(row.line_total),
        )

    def _parse_attachments(self, raw: str | None) -> list[str]:
        if not raw:
            return []
        try:
            data = json.loads(raw)
            if isinstance(data, list):
                return [str(x) for x in data]
        except (TypeError, ValueError):
            pass
        return []

    def _dump_attachments(self, attachments: list[str] | None) -> str | None:
        if not attachments:
            return None
        return json.dumps(attachments)

    def _to_po(
        self,
        row: PurchaseOrderModel,
        *,
        vendor_code: str | None = None,
        vendor_name: str | None = None,
        vendor_email: str | None = None,
        vendor_phone: str | None = None,
        warehouse_code: str | None = None,
        warehouse_name: str | None = None,
        line_meta: dict[str, dict] | None = None,
    ) -> PurchaseOrder:
        meta = line_meta or {}
        lines = []
        for line in row.lines or []:
            info = meta.get(line.id or "", {})
            lines.append(
                self._to_po_line(
                    line,
                    item_sku=info.get("item_sku"),
                    item_name=info.get("item_name"),
                    base_unit_code=info.get("base_unit_code"),
                    base_unit_name=info.get("base_unit_name"),
                )
            )
        return PurchaseOrder(
            id=row.id,
            company_id=row.company_id,
            po_number=row.po_number,
            vendor_id=row.vendor_id,
            vendor_code=vendor_code,
            vendor_name=vendor_name,
            vendor_email=vendor_email,
            vendor_phone=vendor_phone,
            order_date=row.order_date,
            expected_date=row.expected_date,
            delivery_date=row.delivery_date,
            payment_terms=PaymentTerms(row.payment_terms) if row.payment_terms else None,
            currency=row.currency,
            warehouse_id=row.warehouse_id,
            warehouse_code=warehouse_code,
            warehouse_name=warehouse_name,
            ship_to=row.ship_to,
            purchase_type=PurchaseType(row.purchase_type) if row.purchase_type else None,
            reference_number=row.reference_number,
            department=row.department,
            notes=row.notes,
            terms_and_conditions=row.terms_and_conditions,
            attachments=self._parse_attachments(row.attachments),
            status=PurchaseOrderStatus(row.status),
            subtotal=to_decimal(row.subtotal),
            discount_amount=to_decimal(getattr(row, "discount_amount", 0) or 0),
            tax_amount=to_decimal(row.tax_amount),
            total_amount=to_decimal(row.total_amount),
            created_by=row.created_by,
            approved_at=row.approved_at,
            cancelled_at=row.cancelled_at,
            lines=lines,
            created_at=row.created_at,
            updated_at=row.updated_at,
        )

    async def _load_po_line_meta(self, lines: list[PurchaseOrderLineModel]) -> dict[str, dict]:
        if not lines:
            return {}
        item_ids = {line.item_id for line in lines}
        unit_ids = {line.base_unit_id for line in lines if line.base_unit_id}
        items: dict[str, ItemModel] = {}
        units: dict[str, BaseUnitModel] = {}
        if item_ids:
            result = await self._session.execute(
                select(ItemModel).where(ItemModel.id.in_(item_ids))
            )
            items = {row.id: row for row in result.scalars().all()}
        if unit_ids:
            result = await self._session.execute(
                select(BaseUnitModel).where(BaseUnitModel.id.in_(unit_ids))
            )
            units = {row.id: row for row in result.scalars().all()}
        meta: dict[str, dict] = {}
        for line in lines:
            item = items.get(line.item_id)
            unit = units.get(line.base_unit_id) if line.base_unit_id else None
            meta[line.id or ""] = {
                "item_sku": item.sku if item else None,
                "item_name": item.name if item else None,
                "base_unit_code": unit.code if unit else None,
                "base_unit_name": unit.name if unit else None,
            }
        return meta

    async def _hydrate_po(self, row: PurchaseOrderModel) -> PurchaseOrder:
        vendor = await self._session.get(VendorModel, row.vendor_id)
        warehouse = (
            await self._session.get(WarehouseModel, row.warehouse_id)
            if row.warehouse_id
            else None
        )
        line_meta = await self._load_po_line_meta(list(row.lines or []))
        return self._to_po(
            row,
            vendor_code=vendor.code if vendor else None,
            vendor_name=vendor.name if vendor else None,
            vendor_email=vendor.email if vendor else None,
            vendor_phone=vendor.phone if vendor else None,
            warehouse_code=warehouse.code if warehouse else None,
            warehouse_name=warehouse.name if warehouse else None,
            line_meta=line_meta,
        )

    def _to_grn_line(self, row: GoodsReceiptLineModel) -> GoodsReceiptLine:
        return GoodsReceiptLine(
            id=row.id,
            goods_receipt_id=row.goods_receipt_id,
            purchase_order_line_id=row.purchase_order_line_id,
            line_number=row.line_number,
            item_id=row.item_id,
            quantity_ordered=to_decimal(row.quantity_ordered),
            quantity_received=to_decimal(row.quantity_received),
            unit_cost=to_decimal(row.unit_cost),
            notes=row.notes,
        )

    def _to_grn(self, row: GoodsReceiptModel) -> GoodsReceipt:
        return GoodsReceipt(
            id=row.id,
            company_id=row.company_id,
            grn_number=row.grn_number,
            purchase_order_id=row.purchase_order_id,
            vendor_id=row.vendor_id,
            receipt_date=row.receipt_date,
            status=GrnStatus(row.status),
            notes=row.notes,
            confirmed_at=row.confirmed_at,
            lines=[self._to_grn_line(line) for line in (row.lines or [])],
            created_at=row.created_at,
            updated_at=row.updated_at,
        )

    def _to_bill_line(self, row: VendorBillLineModel) -> VendorBillLine:
        return VendorBillLine(
            id=row.id,
            vendor_bill_id=row.vendor_bill_id,
            line_number=row.line_number,
            item_id=row.item_id,
            account_id=row.account_id,
            description=row.description,
            quantity=to_decimal(row.quantity),
            unit_price=to_decimal(row.unit_price),
            tax_rate=to_decimal(row.tax_rate),
            line_total=to_decimal(row.line_total),
            purchase_order_line_id=row.purchase_order_line_id,
            goods_receipt_line_id=row.goods_receipt_line_id,
        )

    def _to_bill(self, row: VendorBillModel) -> VendorBill:
        return VendorBill(
            id=row.id,
            company_id=row.company_id,
            bill_number=row.bill_number,
            vendor_invoice_number=row.vendor_invoice_number,
            vendor_id=row.vendor_id,
            purchase_order_id=row.purchase_order_id,
            goods_receipt_id=row.goods_receipt_id,
            bill_date=row.bill_date,
            due_date=row.due_date,
            status=VendorBillStatus(row.status),
            ap_account_id=row.ap_account_id,
            notes=row.notes,
            subtotal=to_decimal(row.subtotal),
            tax_amount=to_decimal(row.tax_amount),
            total_amount=to_decimal(row.total_amount),
            amount_paid=to_decimal(row.amount_paid),
            voucher_id=row.voucher_id,
            posted_at=row.posted_at,
            lines=[self._to_bill_line(line) for line in (row.lines or [])],
            created_at=row.created_at,
            updated_at=row.updated_at,
        )

    def _to_payment_alloc(self, row: PurchasePaymentAllocationModel) -> PurchasePaymentAllocation:
        return PurchasePaymentAllocation(
            id=row.id,
            purchase_payment_id=row.purchase_payment_id,
            vendor_bill_id=row.vendor_bill_id,
            amount=to_decimal(row.amount),
        )

    def _to_payment(self, row: PurchasePaymentModel) -> PurchasePayment:
        return PurchasePayment(
            id=row.id,
            company_id=row.company_id,
            payment_number=row.payment_number,
            vendor_id=row.vendor_id,
            payment_date=row.payment_date,
            status=PurchasePaymentStatus(row.status),
            payment_method=row.payment_method,
            bank_account_id=row.bank_account_id,
            ap_account_id=row.ap_account_id,
            reference=row.reference,
            notes=row.notes,
            total_amount=to_decimal(row.total_amount),
            voucher_id=row.voucher_id,
            posted_at=row.posted_at,
            allocations=[self._to_payment_alloc(a) for a in (row.allocations or [])],
            created_at=row.created_at,
            updated_at=row.updated_at,
        )

    async def _next_doc_number(
        self,
        company_id: str,
        model,
        company_col,
        number_col,
        prefix: str,
    ) -> str:
        year = datetime.utcnow().year
        pattern = f"{prefix}-{year}-%"
        result = await self._session.execute(
            select(number_col)
            .where(company_col == company_id, number_col.like(pattern))
            .order_by(number_col.desc())
            .limit(1)
        )
        last = result.scalar_one_or_none()
        seq = 1
        if last:
            try:
                seq = int(str(last).rsplit("-", 1)[-1]) + 1
            except ValueError:
                seq = 1
        return f"{prefix}-{year}-{seq:05d}"

    def _po_line_model(self, order_id: str, line: PurchaseOrderLine) -> PurchaseOrderLineModel:
        return PurchaseOrderLineModel(
            id=line.id or new_id(),
            purchase_order_id=order_id,
            line_number=line.line_number,
            item_id=line.item_id,
            description=line.description,
            base_unit_id=line.base_unit_id,
            quantity=line.quantity,
            received_quantity=line.received_quantity,
            billed_quantity=line.billed_quantity,
            unit_price=line.unit_price,
            discount_type=line.discount_type.value,
            discount_value=line.discount_value,
            discount_amount=line.discount_amount,
            tax_type=line.tax_type.value,
            tax_rate=line.tax_rate,
            tax_amount=line.tax_amount,
            line_total=line.line_total,
        )

    # ---- Vendors ----
    async def create_vendor(self, vendor: Vendor) -> Vendor:
        row = VendorModel(
            id=vendor.id or new_id(),
            company_id=vendor.company_id,
            code=vendor.code,
            name=vendor.name,
            business_type=vendor.business_type.value if vendor.business_type else None,
            contact_person=vendor.contact_person,
            designation=vendor.designation,
            phone=vendor.phone,
            alternate_phone=vendor.alternate_phone,
            email=vendor.email,
            website=vendor.website,
            ntn=vendor.ntn,
            sales_tax_no=vendor.sales_tax_no,
            address_line1=vendor.address_line1,
            address_line2=vendor.address_line2,
            city=vendor.city,
            state_province=vendor.state_province,
            country=vendor.country,
            postal_code=vendor.postal_code,
            notes=vendor.notes,
            payment_terms=vendor.payment_terms.value if vendor.payment_terms else None,
            payment_terms_days=vendor.payment_terms_days,
            credit_limit=vendor.credit_limit,
            opening_balance=vendor.opening_balance,
            currency=vendor.currency,
            po_prefix=vendor.po_prefix,
            default_warehouse_id=vendor.default_warehouse_id,
            preferred_payment_method=(
                vendor.preferred_payment_method.value if vendor.preferred_payment_method else None
            ),
            logo=vendor.logo,
            address=vendor.address or vendor.address_line1,
            account_id=vendor.account_id,
            is_active=vendor.is_active,
            created_at=vendor.created_at,
            updated_at=vendor.updated_at,
        )
        self._session.add(row)
        await self._session.commit()
        return await self.get_vendor(row.id, vendor.company_id) or self._to_vendor(row)

    async def get_vendor(self, vendor_id: str, company_id: str) -> Vendor | None:
        result = await self._session.execute(
            self._vendor_select_query().where(
                VendorModel.id == vendor_id,
                VendorModel.company_id == company_id,
            )
        )
        row = result.first()
        return self._vendor_from_row(row) if row else None

    async def get_vendor_by_code(self, company_id: str, code: str) -> Vendor | None:
        result = await self._session.execute(
            self._vendor_select_query().where(
                VendorModel.company_id == company_id,
                VendorModel.code == code,
            )
        )
        row = result.first()
        return self._vendor_from_row(row) if row else None

    async def get_next_vendor_code(self, company_id: str) -> str:
        result = await self._session.execute(
            select(VendorModel.code).where(
                VendorModel.company_id == company_id,
                VendorModel.code.like("VEN-%"),
            )
        )
        max_num = 0
        for (code,) in result.all():
            suffix = code.rsplit("-", 1)[-1]
            if suffix.isdigit():
                max_num = max(max_num, int(suffix))
        return f"VEN-{max_num + 1:05d}"

    async def list_vendors(
        self, company_id: str, is_active: bool | None = None, skip: int = 0, limit: int = 100
    ) -> list[Vendor]:
        query = self._vendor_select_query().where(VendorModel.company_id == company_id)
        if is_active is not None:
            query = query.where(VendorModel.is_active == is_active)
        result = await self._session.execute(
            query.order_by(VendorModel.code.asc()).offset(skip).limit(limit)
        )
        return [self._vendor_from_row(row) for row in result.all()]

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

    async def update_vendor(self, vendor_id: str, vendor: Vendor) -> Vendor | None:
        row = await self._session.get(VendorModel, vendor_id)
        if not row:
            return None
        row.code = vendor.code
        row.name = vendor.name
        row.business_type = vendor.business_type.value if vendor.business_type else None
        row.contact_person = vendor.contact_person
        row.designation = vendor.designation
        row.phone = vendor.phone
        row.alternate_phone = vendor.alternate_phone
        row.email = vendor.email
        row.website = vendor.website
        row.ntn = vendor.ntn
        row.sales_tax_no = vendor.sales_tax_no
        row.address_line1 = vendor.address_line1
        row.address_line2 = vendor.address_line2
        row.city = vendor.city
        row.state_province = vendor.state_province
        row.country = vendor.country
        row.postal_code = vendor.postal_code
        row.notes = vendor.notes
        row.payment_terms = vendor.payment_terms.value if vendor.payment_terms else None
        row.payment_terms_days = vendor.payment_terms_days
        row.credit_limit = vendor.credit_limit
        row.opening_balance = vendor.opening_balance
        row.currency = vendor.currency
        row.po_prefix = vendor.po_prefix
        row.default_warehouse_id = vendor.default_warehouse_id
        row.preferred_payment_method = (
            vendor.preferred_payment_method.value if vendor.preferred_payment_method else None
        )
        row.logo = vendor.logo
        row.address = vendor.address or vendor.address_line1
        row.account_id = vendor.account_id
        row.is_active = vendor.is_active
        row.updated_at = datetime.utcnow()
        await self._session.commit()
        return await self.get_vendor(vendor_id, vendor.company_id)

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

    # ---- Purchase orders ----
    async def create_purchase_order(self, order: PurchaseOrder) -> PurchaseOrder:
        order_id = order.id or new_id()
        row = PurchaseOrderModel(
            id=order_id,
            company_id=order.company_id,
            po_number=order.po_number,
            vendor_id=order.vendor_id,
            order_date=order.order_date,
            expected_date=order.expected_date,
            delivery_date=order.delivery_date,
            payment_terms=order.payment_terms.value if order.payment_terms else None,
            status=order.status.value,
            currency=order.currency,
            warehouse_id=order.warehouse_id,
            ship_to=order.ship_to,
            purchase_type=order.purchase_type.value if order.purchase_type else None,
            reference_number=order.reference_number,
            department=order.department,
            notes=order.notes,
            terms_and_conditions=order.terms_and_conditions,
            attachments=self._dump_attachments(order.attachments),
            subtotal=order.subtotal,
            discount_amount=order.discount_amount,
            tax_amount=order.tax_amount,
            total_amount=order.total_amount,
            created_by=order.created_by,
            approved_at=order.approved_at,
            cancelled_at=order.cancelled_at,
            created_at=order.created_at,
            updated_at=order.updated_at,
            lines=[self._po_line_model(order_id, line) for line in order.lines],
        )
        self._session.add(row)
        await self._session.commit()
        return await self.get_purchase_order(order_id, order.company_id)  # type: ignore[return-value]

    async def get_purchase_order(self, order_id: str, company_id: str) -> PurchaseOrder | None:
        result = await self._session.execute(
            select(PurchaseOrderModel)
            .options(selectinload(PurchaseOrderModel.lines))
            .where(
                PurchaseOrderModel.id == order_id,
                PurchaseOrderModel.company_id == company_id,
            )
        )
        row = result.scalar_one_or_none()
        return await self._hydrate_po(row) if row else None

    async def list_purchase_orders(
        self,
        company_id: str,
        status: PurchaseOrderStatus | None = None,
        vendor_id: str | None = None,
        skip: int = 0,
        limit: int = 100,
    ) -> list[PurchaseOrder]:
        query = (
            select(PurchaseOrderModel)
            .options(selectinload(PurchaseOrderModel.lines))
            .where(PurchaseOrderModel.company_id == company_id)
        )
        if status:
            query = query.where(PurchaseOrderModel.status == status.value)
        if vendor_id:
            query = query.where(PurchaseOrderModel.vendor_id == vendor_id)
        result = await self._session.execute(
            query.order_by(PurchaseOrderModel.order_date.desc()).offset(skip).limit(limit)
        )
        return [await self._hydrate_po(row) for row in result.scalars().all()]

    async def count_purchase_orders(
        self,
        company_id: str,
        status: PurchaseOrderStatus | None = None,
        vendor_id: str | None = None,
    ) -> int:
        query = select(func.count()).select_from(PurchaseOrderModel).where(
            PurchaseOrderModel.company_id == company_id
        )
        if status:
            query = query.where(PurchaseOrderModel.status == status.value)
        if vendor_id:
            query = query.where(PurchaseOrderModel.vendor_id == vendor_id)
        result = await self._session.execute(query)
        return int(result.scalar_one())

    async def update_purchase_order(
        self, order_id: str, order: PurchaseOrder, *, replace_lines: bool = False
    ) -> PurchaseOrder | None:
        result = await self._session.execute(
            select(PurchaseOrderModel)
            .options(selectinload(PurchaseOrderModel.lines))
            .where(PurchaseOrderModel.id == order_id)
        )
        row = result.scalar_one_or_none()
        if not row:
            return None

        row.vendor_id = order.vendor_id
        row.order_date = order.order_date
        row.expected_date = order.expected_date
        row.delivery_date = order.delivery_date
        row.payment_terms = order.payment_terms.value if order.payment_terms else None
        row.status = order.status.value
        row.currency = order.currency
        row.warehouse_id = order.warehouse_id
        row.ship_to = order.ship_to
        row.purchase_type = order.purchase_type.value if order.purchase_type else None
        row.reference_number = order.reference_number
        row.department = order.department
        row.notes = order.notes
        row.terms_and_conditions = order.terms_and_conditions
        row.attachments = self._dump_attachments(order.attachments)
        row.subtotal = order.subtotal
        row.discount_amount = order.discount_amount
        row.tax_amount = order.tax_amount
        row.total_amount = order.total_amount
        row.created_by = order.created_by
        row.approved_at = order.approved_at
        row.cancelled_at = order.cancelled_at
        row.updated_at = datetime.utcnow()

        if replace_lines:
            row.lines.clear()
            await self._session.flush()
            for line in order.lines:
                row.lines.append(self._po_line_model(order_id, line))

        await self._session.commit()
        return await self.get_purchase_order(order_id, order.company_id)

    async def update_po_line_received_quantity(
        self, line_id: str, received_quantity: Decimal
    ) -> None:
        row = await self._session.get(PurchaseOrderLineModel, line_id)
        if not row:
            return
        row.received_quantity = received_quantity
        await self._session.commit()

    async def get_next_po_number(self, company_id: str) -> str:
        return await self._next_doc_number(
            company_id,
            PurchaseOrderModel,
            PurchaseOrderModel.company_id,
            PurchaseOrderModel.po_number,
            "PO",
        )

    # ---- Goods receipts ----
    async def create_goods_receipt(self, receipt: GoodsReceipt) -> GoodsReceipt:
        receipt_id = receipt.id or new_id()
        row = GoodsReceiptModel(
            id=receipt_id,
            company_id=receipt.company_id,
            grn_number=receipt.grn_number,
            purchase_order_id=receipt.purchase_order_id,
            vendor_id=receipt.vendor_id,
            receipt_date=receipt.receipt_date,
            status=receipt.status.value,
            notes=receipt.notes,
            confirmed_at=receipt.confirmed_at,
            created_at=receipt.created_at,
            updated_at=receipt.updated_at,
            lines=[
                GoodsReceiptLineModel(
                    id=line.id or new_id(),
                    goods_receipt_id=receipt_id,
                    purchase_order_line_id=line.purchase_order_line_id,
                    line_number=line.line_number,
                    item_id=line.item_id,
                    quantity_ordered=line.quantity_ordered,
                    quantity_received=line.quantity_received,
                    unit_cost=line.unit_cost,
                    notes=line.notes,
                )
                for line in receipt.lines
            ],
        )
        self._session.add(row)
        await self._session.commit()
        return await self.get_goods_receipt(receipt_id, receipt.company_id)  # type: ignore[return-value]

    async def get_goods_receipt(self, receipt_id: str, company_id: str) -> GoodsReceipt | None:
        result = await self._session.execute(
            select(GoodsReceiptModel)
            .options(selectinload(GoodsReceiptModel.lines))
            .where(
                GoodsReceiptModel.id == receipt_id,
                GoodsReceiptModel.company_id == company_id,
            )
        )
        row = result.scalar_one_or_none()
        return self._to_grn(row) if row else None

    async def list_goods_receipts(
        self,
        company_id: str,
        status: GrnStatus | None = None,
        purchase_order_id: str | None = None,
        skip: int = 0,
        limit: int = 100,
    ) -> list[GoodsReceipt]:
        query = (
            select(GoodsReceiptModel)
            .options(selectinload(GoodsReceiptModel.lines))
            .where(GoodsReceiptModel.company_id == company_id)
        )
        if status:
            query = query.where(GoodsReceiptModel.status == status.value)
        if purchase_order_id:
            query = query.where(GoodsReceiptModel.purchase_order_id == purchase_order_id)
        result = await self._session.execute(
            query.order_by(GoodsReceiptModel.receipt_date.desc()).offset(skip).limit(limit)
        )
        return [self._to_grn(row) for row in result.scalars().all()]

    async def count_goods_receipts(
        self,
        company_id: str,
        status: GrnStatus | None = None,
        purchase_order_id: str | None = None,
    ) -> int:
        query = select(func.count()).select_from(GoodsReceiptModel).where(
            GoodsReceiptModel.company_id == company_id
        )
        if status:
            query = query.where(GoodsReceiptModel.status == status.value)
        if purchase_order_id:
            query = query.where(GoodsReceiptModel.purchase_order_id == purchase_order_id)
        result = await self._session.execute(query)
        return int(result.scalar_one())

    async def update_goods_receipt(
        self, receipt_id: str, receipt: GoodsReceipt
    ) -> GoodsReceipt | None:
        result = await self._session.execute(
            select(GoodsReceiptModel)
            .options(selectinload(GoodsReceiptModel.lines))
            .where(GoodsReceiptModel.id == receipt_id)
        )
        row = result.scalar_one_or_none()
        if not row:
            return None
        row.status = receipt.status.value
        row.notes = receipt.notes
        row.receipt_date = receipt.receipt_date
        row.confirmed_at = receipt.confirmed_at
        row.updated_at = datetime.utcnow()
        await self._session.commit()
        return await self.get_goods_receipt(receipt_id, receipt.company_id)

    async def get_next_grn_number(self, company_id: str) -> str:
        return await self._next_doc_number(
            company_id,
            GoodsReceiptModel,
            GoodsReceiptModel.company_id,
            GoodsReceiptModel.grn_number,
            "GRN",
        )

    # ---- Vendor bills ----
    async def create_vendor_bill(self, bill: VendorBill) -> VendorBill:
        bill_id = bill.id or new_id()
        row = VendorBillModel(
            id=bill_id,
            company_id=bill.company_id,
            bill_number=bill.bill_number,
            vendor_invoice_number=bill.vendor_invoice_number,
            vendor_id=bill.vendor_id,
            purchase_order_id=bill.purchase_order_id,
            goods_receipt_id=bill.goods_receipt_id,
            bill_date=bill.bill_date,
            due_date=bill.due_date,
            status=bill.status.value,
            ap_account_id=bill.ap_account_id,
            notes=bill.notes,
            subtotal=bill.subtotal,
            tax_amount=bill.tax_amount,
            total_amount=bill.total_amount,
            amount_paid=bill.amount_paid,
            voucher_id=bill.voucher_id,
            posted_at=bill.posted_at,
            created_at=bill.created_at,
            updated_at=bill.updated_at,
            lines=[
                VendorBillLineModel(
                    id=line.id or new_id(),
                    vendor_bill_id=bill_id,
                    line_number=line.line_number,
                    item_id=line.item_id,
                    account_id=line.account_id,
                    description=line.description,
                    quantity=line.quantity,
                    unit_price=line.unit_price,
                    tax_rate=line.tax_rate,
                    line_total=line.line_total,
                    purchase_order_line_id=line.purchase_order_line_id,
                    goods_receipt_line_id=line.goods_receipt_line_id,
                )
                for line in bill.lines
            ],
        )
        self._session.add(row)
        await self._session.commit()
        return await self.get_vendor_bill(bill_id, bill.company_id)  # type: ignore[return-value]

    async def get_vendor_bill(self, bill_id: str, company_id: str) -> VendorBill | None:
        result = await self._session.execute(
            select(VendorBillModel)
            .options(selectinload(VendorBillModel.lines))
            .where(
                VendorBillModel.id == bill_id,
                VendorBillModel.company_id == company_id,
            )
        )
        row = result.scalar_one_or_none()
        return self._to_bill(row) if row else None

    async def list_vendor_bills(
        self,
        company_id: str,
        status: VendorBillStatus | None = None,
        vendor_id: str | None = None,
        skip: int = 0,
        limit: int = 100,
    ) -> list[VendorBill]:
        query = (
            select(VendorBillModel)
            .options(selectinload(VendorBillModel.lines))
            .where(VendorBillModel.company_id == company_id)
        )
        if status:
            query = query.where(VendorBillModel.status == status.value)
        if vendor_id:
            query = query.where(VendorBillModel.vendor_id == vendor_id)
        result = await self._session.execute(
            query.order_by(VendorBillModel.bill_date.desc()).offset(skip).limit(limit)
        )
        return [self._to_bill(row) for row in result.scalars().all()]

    async def count_vendor_bills(
        self,
        company_id: str,
        status: VendorBillStatus | None = None,
        vendor_id: str | None = None,
    ) -> int:
        query = select(func.count()).select_from(VendorBillModel).where(
            VendorBillModel.company_id == company_id
        )
        if status:
            query = query.where(VendorBillModel.status == status.value)
        if vendor_id:
            query = query.where(VendorBillModel.vendor_id == vendor_id)
        result = await self._session.execute(query)
        return int(result.scalar_one())

    async def update_vendor_bill(self, bill_id: str, bill: VendorBill) -> VendorBill | None:
        result = await self._session.execute(
            select(VendorBillModel)
            .options(selectinload(VendorBillModel.lines))
            .where(VendorBillModel.id == bill_id)
        )
        row = result.scalar_one_or_none()
        if not row:
            return None
        row.vendor_invoice_number = bill.vendor_invoice_number
        row.bill_date = bill.bill_date
        row.due_date = bill.due_date
        row.status = bill.status.value
        row.ap_account_id = bill.ap_account_id
        row.notes = bill.notes
        row.subtotal = bill.subtotal
        row.tax_amount = bill.tax_amount
        row.total_amount = bill.total_amount
        row.amount_paid = bill.amount_paid
        row.voucher_id = bill.voucher_id
        row.posted_at = bill.posted_at
        row.updated_at = datetime.utcnow()
        await self._session.commit()
        return await self.get_vendor_bill(bill_id, bill.company_id)

    async def get_next_bill_number(self, company_id: str) -> str:
        return await self._next_doc_number(
            company_id,
            VendorBillModel,
            VendorBillModel.company_id,
            VendorBillModel.bill_number,
            "BILL",
        )

    # ---- Payments ----
    async def create_payment(self, payment: PurchasePayment) -> PurchasePayment:
        payment_id = payment.id or new_id()
        row = PurchasePaymentModel(
            id=payment_id,
            company_id=payment.company_id,
            payment_number=payment.payment_number,
            vendor_id=payment.vendor_id,
            payment_date=payment.payment_date,
            status=payment.status.value,
            payment_method=payment.payment_method,
            bank_account_id=payment.bank_account_id,
            ap_account_id=payment.ap_account_id,
            reference=payment.reference,
            notes=payment.notes,
            total_amount=payment.total_amount,
            voucher_id=payment.voucher_id,
            posted_at=payment.posted_at,
            created_at=payment.created_at,
            updated_at=payment.updated_at,
            allocations=[
                PurchasePaymentAllocationModel(
                    id=alloc.id or new_id(),
                    purchase_payment_id=payment_id,
                    vendor_bill_id=alloc.vendor_bill_id,
                    amount=alloc.amount,
                )
                for alloc in payment.allocations
            ],
        )
        self._session.add(row)
        await self._session.commit()
        return await self.get_payment(payment_id, payment.company_id)  # type: ignore[return-value]

    async def get_payment(self, payment_id: str, company_id: str) -> PurchasePayment | None:
        result = await self._session.execute(
            select(PurchasePaymentModel)
            .options(selectinload(PurchasePaymentModel.allocations))
            .where(
                PurchasePaymentModel.id == payment_id,
                PurchasePaymentModel.company_id == company_id,
            )
        )
        row = result.scalar_one_or_none()
        return self._to_payment(row) if row else None

    async def list_payments(
        self,
        company_id: str,
        status: PurchasePaymentStatus | None = None,
        vendor_id: str | None = None,
        skip: int = 0,
        limit: int = 100,
    ) -> list[PurchasePayment]:
        query = (
            select(PurchasePaymentModel)
            .options(selectinload(PurchasePaymentModel.allocations))
            .where(PurchasePaymentModel.company_id == company_id)
        )
        if status:
            query = query.where(PurchasePaymentModel.status == status.value)
        if vendor_id:
            query = query.where(PurchasePaymentModel.vendor_id == vendor_id)
        result = await self._session.execute(
            query.order_by(PurchasePaymentModel.payment_date.desc()).offset(skip).limit(limit)
        )
        return [self._to_payment(row) for row in result.scalars().all()]

    async def count_payments(
        self,
        company_id: str,
        status: PurchasePaymentStatus | None = None,
        vendor_id: str | None = None,
    ) -> int:
        query = select(func.count()).select_from(PurchasePaymentModel).where(
            PurchasePaymentModel.company_id == company_id
        )
        if status:
            query = query.where(PurchasePaymentModel.status == status.value)
        if vendor_id:
            query = query.where(PurchasePaymentModel.vendor_id == vendor_id)
        result = await self._session.execute(query)
        return int(result.scalar_one())

    async def update_payment(
        self, payment_id: str, payment: PurchasePayment
    ) -> PurchasePayment | None:
        result = await self._session.execute(
            select(PurchasePaymentModel)
            .options(selectinload(PurchasePaymentModel.allocations))
            .where(PurchasePaymentModel.id == payment_id)
        )
        row = result.scalar_one_or_none()
        if not row:
            return None
        row.payment_date = payment.payment_date
        row.status = payment.status.value
        row.payment_method = payment.payment_method
        row.bank_account_id = payment.bank_account_id
        row.ap_account_id = payment.ap_account_id
        row.reference = payment.reference
        row.notes = payment.notes
        row.total_amount = payment.total_amount
        row.voucher_id = payment.voucher_id
        row.posted_at = payment.posted_at
        row.updated_at = datetime.utcnow()
        await self._session.commit()
        return await self.get_payment(payment_id, payment.company_id)

    async def get_next_payment_number(self, company_id: str) -> str:
        return await self._next_doc_number(
            company_id,
            PurchasePaymentModel,
            PurchasePaymentModel.company_id,
            PurchasePaymentModel.payment_number,
            "PAY",
        )