from datetime import datetime, timezone, timedelta
from typing import Optional, List
from sqlalchemy import select, update, case
from sqlalchemy.orm import Session

from src.apps.developer.models.merchant_api_key import MerchantApiKey
from src.apps.developer.models.merchant_webhook_endpoint import MerchantWebhookEndpoint
from src.apps.developer.models.merchant_webhook_delivery import MerchantWebhookDelivery
from src.apps.developer.models.api_request_log import ApiRequestLog


# ─── API Key CRUD ────────────────────────────────────────────────────────────

def create_api_key(db: Session, **kwargs) -> MerchantApiKey:
    obj = MerchantApiKey(**kwargs)
    db.add(obj)
    db.commit()
    db.refresh(obj)
    return obj


def get_api_keys_for_merchant(db: Session, merchant_id: int) -> List[MerchantApiKey]:
    return db.execute(
        select(MerchantApiKey)
        .where(MerchantApiKey.merchant_id == merchant_id)
        .order_by(MerchantApiKey.created_at.desc())
    ).scalars().all()


def get_api_key_by_id(db: Session, key_id: int, merchant_id: int) -> Optional[MerchantApiKey]:
    return db.execute(
        select(MerchantApiKey).where(
            MerchantApiKey.id == key_id,
            MerchantApiKey.merchant_id == merchant_id,
        )
    ).scalar_one_or_none()


def get_api_key_by_id_admin(db: Session, key_id: int) -> Optional[MerchantApiKey]:
    return db.execute(
        select(MerchantApiKey).where(MerchantApiKey.id == key_id)
    ).scalar_one_or_none()


def get_api_keys_for_merchant_admin(db: Session, merchant_id: int) -> List[MerchantApiKey]:
    return db.execute(
        select(MerchantApiKey)
        .where(MerchantApiKey.merchant_id == merchant_id)
        .order_by(MerchantApiKey.created_at.desc())
    ).scalars().all()


def update_api_key(db: Session, key: MerchantApiKey, **kwargs) -> MerchantApiKey:
    for k, v in kwargs.items():
        setattr(key, k, v)
    db.commit()
    db.refresh(key)
    return key


def revoke_api_key(db: Session, key: MerchantApiKey) -> MerchantApiKey:
    key.is_active = False
    key.revoked_at = datetime.now(timezone.utc)
    db.commit()
    db.refresh(key)
    return key


# ─── Webhook CRUD ─────────────────────────────────────────────────────────────

def create_webhook_endpoint(db: Session, **kwargs) -> MerchantWebhookEndpoint:
    obj = MerchantWebhookEndpoint(**kwargs)
    db.add(obj)
    db.commit()
    db.refresh(obj)
    return obj


def get_webhook_endpoints_for_merchant(db: Session, merchant_id: int) -> List[MerchantWebhookEndpoint]:
    return db.execute(
        select(MerchantWebhookEndpoint)
        .where(MerchantWebhookEndpoint.merchant_id == merchant_id)
        .order_by(MerchantWebhookEndpoint.created_at.desc())
    ).scalars().all()


def get_webhook_endpoint_by_id(db: Session, endpoint_id: int, merchant_id: int) -> Optional[MerchantWebhookEndpoint]:
    return db.execute(
        select(MerchantWebhookEndpoint).where(
            MerchantWebhookEndpoint.id == endpoint_id,
            MerchantWebhookEndpoint.merchant_id == merchant_id,
        )
    ).scalar_one_or_none()


def update_webhook_endpoint(db: Session, endpoint: MerchantWebhookEndpoint, **kwargs) -> MerchantWebhookEndpoint:
    for k, v in kwargs.items():
        setattr(endpoint, k, v)
    db.commit()
    db.refresh(endpoint)
    return endpoint


def delete_webhook_endpoint(db: Session, endpoint: MerchantWebhookEndpoint) -> None:
    db.delete(endpoint)
    db.commit()


def get_webhook_deliveries(
    db: Session, endpoint_id: int, merchant_id: int, page: int = 1, page_size: int = 20
) -> tuple[List[MerchantWebhookDelivery], int]:
    from sqlalchemy import func
    query = select(MerchantWebhookDelivery).where(
        MerchantWebhookDelivery.webhook_endpoint_id == endpoint_id,
        MerchantWebhookDelivery.merchant_id == merchant_id,
    ).order_by(
        MerchantWebhookDelivery.created_at.desc(),
        # Within the same second: transaction events before payment_request events
        case(
            (MerchantWebhookDelivery.event_type == "transaction.completed", 0),
            (MerchantWebhookDelivery.event_type == "transaction.failed", 0),
            (MerchantWebhookDelivery.event_type == "transaction.refunded", 0),
            else_=1,
        ).asc(),
    )

    total = db.execute(
        select(func.count()).select_from(query.subquery())
    ).scalar_one()

    items = db.execute(
        query.offset((page - 1) * page_size).limit(page_size)
    ).scalars().all()

    return items, total


# ─── API Request Logs CRUD ────────────────────────────────────────────────────

def create_api_request_log(db: Session, **kwargs) -> ApiRequestLog:
    obj = ApiRequestLog(**kwargs)
    db.add(obj)
    db.commit()
    db.refresh(obj)
    return obj


def get_api_request_logs(
    db: Session,
    merchant_id: int,
    api_key_id: Optional[int] = None,
    status_code: Optional[int] = None,
    method: Optional[str] = None,
    date_from: Optional[datetime] = None,
    date_to: Optional[datetime] = None,
    page: int = 1,
    page_size: int = 50,
) -> tuple[List[ApiRequestLog], int]:
    from sqlalchemy import func

    cutoff = datetime.now(timezone.utc) - timedelta(days=30)
    query = select(ApiRequestLog).where(
        ApiRequestLog.merchant_id == merchant_id,
        ApiRequestLog.created_at >= cutoff,
    )

    if api_key_id is not None:
        query = query.where(ApiRequestLog.api_key_id == api_key_id)
    if status_code is not None:
        query = query.where(ApiRequestLog.status_code == status_code)
    if method is not None:
        query = query.where(ApiRequestLog.method == method.upper())
    if date_from is not None:
        query = query.where(ApiRequestLog.created_at >= date_from)
    if date_to is not None:
        query = query.where(ApiRequestLog.created_at <= date_to)

    query = query.order_by(ApiRequestLog.created_at.desc())

    total = db.execute(
        select(func.count()).select_from(query.subquery())
    ).scalar_one()

    items = db.execute(
        query.offset((page - 1) * page_size).limit(page_size)
    ).scalars().all()

    return items, total