from datetime import datetime, timezone
from typing import List

from sqlalchemy import bindparam, text
from sqlalchemy.orm import Session

from src.domain.entities.merchant_offer import DisbursementPhaseEntry, MerchantOffer, OfferLogEntry
from src.domain.interfaces.merchant_offer_repository import MerchantOfferRepositoryInterface
from src.infrastructure.models.merchant_offer_model import (
    DisbursementOffersModel,
    MerchantOfferModel,
    ProductOfferModel,
)


class SqlMerchantOfferRepository(MerchantOfferRepositoryInterface):
    """SQLAlchemy implementation of MerchantOfferRepositoryInterface."""

    def __init__(self, db: Session):
        self.db = db

    def _fetch_offer_logs(self, offer_ids: List[int]) -> dict:
        """Fetch all offer log entries with user names for the given offer IDs.

        Returns a dict of offer_id -> List[OfferLogEntry], ordered newest first.
        Uses a single JOIN query to avoid N+1.
        """
        if not offer_ids:
            return {}

        rows = self.db.execute(
            text("""
                SELECT mol.id, mol.offer_id, mol.created_at, mol.archived_by,
                       u.name AS archived_by_name
                FROM merchant_offers_lock mol
                LEFT JOIN users u ON u.id = mol.archived_by
                WHERE mol.offer_id IN :offer_ids
                ORDER BY mol.created_at DESC
            """).bindparams(bindparam("offer_ids", expanding=True)),
            {"offer_ids": offer_ids},
        ).fetchall()

        logs_by_offer: dict = {}
        for row in rows:
            entry = OfferLogEntry(
                id=row.id,
                offer_id=row.offer_id,
                created_at=row.created_at,
                archived_by=row.archived_by,
                archived_by_name=row.archived_by_name,
            )
            logs_by_offer.setdefault(row.offer_id, []).append(entry)

        return logs_by_offer

    def _fetch_disbursement_phases(self, offer_ids: List[int]) -> dict:
        """Fetch all disbursement phase entries for the given offer IDs.

        Returns a dict of offer_id -> List[DisbursementPhaseEntry], ordered by phase.
        Uses a single query to avoid N+1.
        """
        if not offer_ids:
            return {}

        rows = (
            self.db.query(DisbursementOffersModel)
            .filter(
                DisbursementOffersModel.offer_id.in_(offer_ids),
                DisbursementOffersModel.deleted_at.is_(None),
            )
            .order_by(DisbursementOffersModel.offer_id, DisbursementOffersModel.phase)
            .all()
        )

        phases_by_offer: dict = {}
        for row in rows:
            entry = DisbursementPhaseEntry(
                id=row.id,
                offer_id=row.offer_id,
                phase=row.phase,
                per_disbursement_amount=row.per_disbursement_amount,
                day_from_disbursement_date=row.day_from_disbursement_date,
                disbursement_at=row.disbursement_at,
            )
            phases_by_offer.setdefault(row.offer_id, []).append(entry)

        return phases_by_offer

    def _to_entity(
        self,
        model: MerchantOfferModel,
        offer_log: List[OfferLogEntry] = None,
        disbursement_phases: List[DisbursementPhaseEntry] = None,
    ) -> MerchantOffer:
        return MerchantOffer(
            id=model.id,
            merchant_id=model.merchant_id,
            interest_type_id=model.interest_type_id,
            payment_frequency=model.payment_frequency,
            tenure=model.tenure,
            advance_emi=model.advance_emi,
            interest_rate=model.interest_rate,
            subvention=model.subvention,
            processing_fee=model.processing_fee,
            processing_fee_type=model.processing_fee_type,
            advance_type=model.advance_type,
            status=model.status,
            reason=model.reason,
            disable_offer_status=model.disable_offer_status,
            subvention_type_id=model.subvention_type_id,
            monthly_irr=model.monthly_irr,
            annualised_irr=model.annualised_irr,
            disbursement_method=model.disbursement_method,
            disbursement_at=model.disbursement_at,
            processing_fee_first_emi=model.processing_fee_first_emi,
            offer_log=offer_log or [],
            disbursement_phases=disbursement_phases or [],
        )

    # ── Merchant Offers feature ──────────────────────────────────────────────

    def get_by_merchant(self, merchant_id: int) -> List[MerchantOffer]:
        """Return all non-soft-deleted offers for a merchant, with audit log and disbursement phases."""
        offers = (
            self.db.query(MerchantOfferModel)
            .filter(
                MerchantOfferModel.merchant_id == merchant_id,
                MerchantOfferModel.deleted_at.is_(None),
            )
            .all()
        )

        offer_ids = [o.id for o in offers]
        logs_by_offer = self._fetch_offer_logs(offer_ids)
        phases_by_offer = self._fetch_disbursement_phases(offer_ids)

        return [
            self._to_entity(
                offer,
                logs_by_offer.get(offer.id, []),
                phases_by_offer.get(offer.id, []),
            )
            for offer in offers
        ]

    def delete_by_id(self, offer_id: int) -> bool:
        """Soft-delete an offer by setting deleted_at. Returns True if found."""
        offer = (
            self.db.query(MerchantOfferModel)
            .filter(
                MerchantOfferModel.id == offer_id,
                MerchantOfferModel.deleted_at.is_(None),
            )
            .first()
        )
        if not offer:
            return False
        offer.deleted_at = datetime.now(timezone.utc)
        self.db.commit()
        return True

    # ── Products feature ─────────────────────────────────────────────────────

    def get_active_by_merchant(self, merchant_id: int) -> List[MerchantOffer]:
        """Return active (status=1, not disabled, not deleted) offers for products linking."""
        models = (
            self.db.query(MerchantOfferModel)
            .filter(
                MerchantOfferModel.merchant_id == merchant_id,
                MerchantOfferModel.status == 1,
                MerchantOfferModel.disable_offer_status == 1,
                MerchantOfferModel.deleted_at.is_(None),
            )
            .all()
        )
        return [self._to_entity(m) for m in models]

    def get_linked_offer_ids(self, product_id: int) -> List[int]:
        rows = (
            self.db.query(ProductOfferModel.offer_id)
            .filter(
                ProductOfferModel.product_id == product_id,
                ProductOfferModel.deleted_at.is_(None),
            )
            .all()
        )
        return [r.offer_id for r in rows]

    def link_offers(self, product_id: int, offer_ids: List[int], user_id: int) -> bool:
        self.db.query(ProductOfferModel).filter(
            ProductOfferModel.product_id == product_id,
            ProductOfferModel.deleted_at.is_(None),
        ).update({"deleted_at": datetime.now(timezone.utc)})

        for offer_id in offer_ids:
            link = ProductOfferModel(
                product_id=product_id,
                offer_id=offer_id,
                user_id=user_id,
            )
            self.db.add(link)

        self.db.commit()
        return True