import hashlib
import uuid
from datetime import datetime, timezone
from typing import Any, Optional
from sqlalchemy import func, select, update
from sqlalchemy.ext.asyncio import AsyncSession
from modules.billing.models import (
    Plan,
    Subscription,
    PaymentOrder,
    PaymentWebhookEvent,
    Invoice,
    Refund,
    Wallet,
    WalletTransaction,
    Coupon,
    CouponRedemption,
    Referral,
    TenantUsage,
)
from modules.bots.models import Bot
from modules.emojis.models import CustomEmoji


class BillingRepository:
    def __init__(self, db: AsyncSession):
        self.db = db

    # --- Plans ---
    async def list_active_plans(self) -> list[Plan]:
        res = await self.db.execute(select(Plan).where(Plan.is_active.is_(True)).order_by(Plan.price_amount.asc()))
        return list(res.scalars().all())

    async def get_plan_by_id(self, plan_id: str) -> Optional[Plan]:
        res = await self.db.execute(select(Plan).where(Plan.id == plan_id))
        return res.scalar_one_or_none()

    async def upsert_plan(self, plan: Plan) -> Plan:
        existing = await self.get_plan_by_id(plan.id)
        if existing:
            for k, v in plan.__dict__.items():
                if not k.startswith("_") and k != "id":
                    setattr(existing, k, v)
            await self.db.commit()
            await self.db.refresh(existing)
            return existing
        self.db.add(plan)
        await self.db.commit()
        await self.db.refresh(plan)
        return plan

    # --- Subscriptions ---
    async def get_tenant_subscription(self, tenant_id: uuid.UUID) -> Optional[Subscription]:
        res = await self.db.execute(
            select(Subscription).where(Subscription.tenant_id == tenant_id)
        )
        return res.scalar_one_or_none()

    async def create_subscription(
        self,
        tenant_id: uuid.UUID,
        plan_id: str,
        status: str,
        current_period_start: datetime,
        current_period_end: datetime,
        gateway_provider: str = "manual",
        trial_start: Optional[datetime] = None,
        trial_end: Optional[datetime] = None,
        grace_period_ends_at: Optional[datetime] = None,
    ) -> Subscription:
        sub = Subscription(
            id=uuid.uuid4(),
            tenant_id=tenant_id,
            plan_id=plan_id,
            status=status,
            current_period_start=current_period_start,
            current_period_end=current_period_end,
            gateway_provider=gateway_provider,
            trial_start=trial_start,
            trial_end=trial_end,
            grace_period_ends_at=grace_period_ends_at,
        )
        self.db.add(sub)
        await self.db.commit()
        await self.db.refresh(sub)
        return sub

    async def update_subscription(
        self,
        subscription: Subscription,
        **updates: Any,
    ) -> Subscription:
        for k, v in updates.items():
            setattr(subscription, k, v)
        await self.db.commit()
        await self.db.refresh(subscription)
        return subscription

    # --- Orders ---
    async def create_order(
        self,
        tenant_id: uuid.UUID,
        order_number: str,
        amount: int,
        currency: str,
        provider: str,
        idempotency_key: str,
        purpose: str = "subscription",
        plan_id: Optional[str] = None,
        gateway_order_id: Optional[str] = None,
        metadata_json: Optional[dict[str, Any]] = None,
    ) -> PaymentOrder:
        order = PaymentOrder(
            id=uuid.uuid4(),
            tenant_id=tenant_id,
            order_number=order_number,
            amount=amount,
            currency=currency,
            status="created",
            provider=provider,
            idempotency_key=idempotency_key,
            purpose=purpose,
            plan_id=plan_id,
            gateway_order_id=gateway_order_id,
            metadata_json=metadata_json or {},
        )
        self.db.add(order)
        await self.db.commit()
        await self.db.refresh(order)
        return order

    async def get_order_by_id(self, order_id: uuid.UUID) -> Optional[PaymentOrder]:
        res = await self.db.execute(select(PaymentOrder).where(PaymentOrder.id == order_id))
        return res.scalar_one_or_none()

    async def get_order_by_idempotency_key(self, idempotency_key: str) -> Optional[PaymentOrder]:
        res = await self.db.execute(select(PaymentOrder).where(PaymentOrder.idempotency_key == idempotency_key))
        return res.scalar_one_or_none()

    async def get_order_by_gateway_order_id(self, gateway_order_id: str) -> Optional[PaymentOrder]:
        res = await self.db.execute(select(PaymentOrder).where(PaymentOrder.gateway_order_id == gateway_order_id))
        return res.scalar_one_or_none()

    async def update_order(
        self,
        order: PaymentOrder,
        **updates: Any,
    ) -> PaymentOrder:
        for k, v in updates.items():
            setattr(order, k, v)
        await self.db.commit()
        await self.db.refresh(order)
        return order

    # --- Webhook Events (Duplicate Event Protection) ---
    async def get_webhook_event(self, gateway_provider: str, event_id: str) -> Optional[PaymentWebhookEvent]:
        res = await self.db.execute(
            select(PaymentWebhookEvent).where(
                PaymentWebhookEvent.gateway_provider == gateway_provider,
                PaymentWebhookEvent.event_id == event_id,
            )
        )
        return res.scalar_one_or_none()

    async def record_webhook_event(
        self,
        gateway_provider: str,
        event_id: str,
        event_type: str,
        payload: dict[str, Any],
        status: str = "processed",
        error_message: Optional[str] = None,
    ) -> PaymentWebhookEvent:
        event = PaymentWebhookEvent(
            id=uuid.uuid4(),
            gateway_provider=gateway_provider,
            event_id=event_id,
            event_type=event_type,
            payload=payload,
            status=status,
            error_message=error_message,
        )
        self.db.add(event)
        await self.db.commit()
        await self.db.refresh(event)
        return event

    # --- Invoices ---
    async def create_invoice(
        self,
        tenant_id: uuid.UUID,
        invoice_number: str,
        amount_due: int,
        amount_paid: int,
        currency: str,
        status: str,
        due_date: datetime,
        billing_email: str,
        billing_name: str,
        items: list[dict[str, Any]],
        subtotal: int,
        tax_amount: int,
        discount_amount: int,
        subscription_id: Optional[uuid.UUID] = None,
        order_id: Optional[uuid.UUID] = None,
        paid_at: Optional[datetime] = None,
    ) -> Invoice:
        invoice = Invoice(
            id=uuid.uuid4(),
            tenant_id=tenant_id,
            invoice_number=invoice_number,
            amount_due=amount_due,
            amount_paid=amount_paid,
            amount_remaining=amount_due - amount_paid,
            currency=currency,
            status=status,
            due_date=due_date,
            paid_at=paid_at,
            billing_email=billing_email,
            billing_name=billing_name,
            items=items,
            subtotal=subtotal,
            tax_amount=tax_amount,
            discount_amount=discount_amount,
            subscription_id=subscription_id,
            order_id=order_id,
        )
        self.db.add(invoice)
        await self.db.commit()
        await self.db.refresh(invoice)
        return invoice

    async def list_tenant_invoices(self, tenant_id: uuid.UUID) -> list[Invoice]:
        res = await self.db.execute(
            select(Invoice).where(Invoice.tenant_id == tenant_id).order_by(Invoice.created_at.desc())
        )
        return list(res.scalars().all())

    async def get_invoice_by_id(self, invoice_id: uuid.UUID, tenant_id: Optional[uuid.UUID] = None) -> Optional[Invoice]:
        stmt = select(Invoice).where(Invoice.id == invoice_id)
        if tenant_id:
            stmt = stmt.where(Invoice.tenant_id == tenant_id)
        res = await self.db.execute(stmt)
        return res.scalar_one_or_none()

    # --- Refunds ---
    async def create_refund(
        self,
        tenant_id: uuid.UUID,
        order_id: uuid.UUID,
        amount: int,
        currency: str,
        reason: str,
        provider: str,
        status: str = "processed",
        gateway_refund_id: Optional[str] = None,
        invoice_id: Optional[uuid.UUID] = None,
    ) -> Refund:
        refund = Refund(
            id=uuid.uuid4(),
            tenant_id=tenant_id,
            order_id=order_id,
            invoice_id=invoice_id,
            amount=amount,
            currency=currency,
            reason=reason,
            status=status,
            provider=provider,
            gateway_refund_id=gateway_refund_id,
        )
        self.db.add(refund)
        await self.db.commit()
        await self.db.refresh(refund)
        return refund

    # --- Wallet & Immutable Ledger ---
    async def get_or_create_wallet(self, tenant_id: uuid.UUID) -> Wallet:
        res = await self.db.execute(select(Wallet).where(Wallet.tenant_id == tenant_id).with_for_update())
        wallet = res.scalar_one_or_none()
        if not wallet:
            wallet = Wallet(id=uuid.uuid4(), tenant_id=tenant_id, balance=0, currency="INR")
            self.db.add(wallet)
            await self.db.commit()
            await self.db.refresh(wallet)
        return wallet

    async def record_wallet_transaction(
        self,
        tenant_id: uuid.UUID,
        wallet: Wallet,
        transaction_type: str,
        amount: int,
        balance_after: int,
        reference_type: str,
        description: str,
        reference_id: Optional[str] = None,
    ) -> WalletTransaction:
        now_utc = datetime.now(timezone.utc).isoformat()
        checksum_payload = f"{wallet.id}:{transaction_type}:{amount}:{balance_after}:{now_utc}"
        checksum = hashlib.sha256(checksum_payload.encode("utf-8")).hexdigest()

        tx = WalletTransaction(
            id=uuid.uuid4(),
            tenant_id=tenant_id,
            wallet_id=wallet.id,
            transaction_type=transaction_type,
            amount=amount,
            balance_after=balance_after,
            reference_type=reference_type,
            reference_id=reference_id,
            description=description,
            checksum=checksum,
        )
        wallet.balance = balance_after
        self.db.add(tx)
        await self.db.commit()
        await self.db.refresh(tx)
        return tx

    async def get_wallet_transactions(self, wallet_id: uuid.UUID) -> list[WalletTransaction]:
        res = await self.db.execute(
            select(WalletTransaction)
            .where(WalletTransaction.wallet_id == wallet_id)
            .order_by(WalletTransaction.created_at.desc())
        )
        return list(res.scalars().all())

    # --- Coupons ---
    async def get_coupon_by_code(self, code: str) -> Optional[Coupon]:
        res = await self.db.execute(select(Coupon).where(Coupon.code == code.upper()))
        return res.scalar_one_or_none()

    async def create_coupon(self, coupon: Coupon) -> Coupon:
        self.db.add(coupon)
        await self.db.commit()
        await self.db.refresh(coupon)
        return coupon

    async def record_coupon_redemption(
        self,
        tenant_id: uuid.UUID,
        coupon_id: uuid.UUID,
        order_id: uuid.UUID,
        discount_applied: int,
    ) -> CouponRedemption:
        redemption = CouponRedemption(
            id=uuid.uuid4(),
            tenant_id=tenant_id,
            coupon_id=coupon_id,
            order_id=order_id,
            discount_applied=discount_applied,
        )
        self.db.add(redemption)
        # Increment coupon times_redeemed
        await self.db.execute(
            update(Coupon)
            .where(Coupon.id == coupon_id)
            .values(times_redeemed=Coupon.times_redeemed + 1)
        )
        await self.db.commit()
        await self.db.refresh(redemption)
        return redemption

    # --- Referrals ---
    async def get_referral_by_referred_tenant(self, referred_tenant_id: uuid.UUID) -> Optional[Referral]:
        res = await self.db.execute(
            select(Referral).where(Referral.referred_tenant_id == referred_tenant_id)
        )
        return res.scalar_one_or_none()

    async def count_tenant_referrals(self, referrer_tenant_id: uuid.UUID) -> tuple[int, int]:
        total_res = await self.db.execute(
            select(func.count(Referral.id)).where(Referral.referrer_tenant_id == referrer_tenant_id)
        )
        total = total_res.scalar() or 0

        rewarded_res = await self.db.execute(
            select(func.coalesce(func.sum(Referral.reward_amount), 0)).where(
                Referral.referrer_tenant_id == referrer_tenant_id,
                Referral.status == "rewarded",
            )
        )
        rewarded = rewarded_res.scalar() or 0
        return total, rewarded

    async def create_referral(
        self,
        referrer_tenant_id: uuid.UUID,
        referred_tenant_id: uuid.UUID,
        referral_code: str,
    ) -> Referral:
        ref = Referral(
            id=uuid.uuid4(),
            referrer_tenant_id=referrer_tenant_id,
            referred_tenant_id=referred_tenant_id,
            referral_code=referral_code,
            status="pending",
        )
        self.db.add(ref)
        await self.db.commit()
        await self.db.refresh(ref)
        return ref

    # --- Entitlement & Usage Counts ---
    async def count_active_bots(self, tenant_id: uuid.UUID) -> int:
        res = await self.db.execute(
            select(func.count(Bot.id)).where(
                Bot.tenant_id == tenant_id,
                Bot.status != "deleted",
            )
        )
        return res.scalar() or 0

    async def count_custom_emojis(self, tenant_id: Optional[uuid.UUID] = None) -> int:
        res = await self.db.execute(select(func.count(CustomEmoji.id)))
        return res.scalar() or 0
