"""QR Code service — upsert, enqueue generation, presigned download."""
from __future__ import annotations

import asyncio
from typing import Optional
from urllib.parse import quote
from uuid import UUID

from sqlalchemy import select, func
from sqlalchemy.dialects.postgresql import insert as pg_insert
from sqlalchemy.ext.asyncio import AsyncSession

from src.core.config import settings
from src.core.exceptions import NotFoundError, RateLimitError
from src.apps.settings.models.qr_code import QRCode
from src.apps.tenants.models.account import Account

_BULK_HEADSTONE_RATE_WINDOW_SECONDS = 24 * 60 * 60
_BULK_HEADSTONE_RATE_LIMIT = 1


class QRCodeService:

    @staticmethod
    async def get_list(
        db: AsyncSession,
        tenant_id: str,
        qr_type: Optional[str] = None,
        page: int = 1,
        page_size: int = 20,
        sort_by: str = "qr_type",
        order: str = "asc",
        include_inactive: bool = False,
    ) -> tuple[list[QRCode], int]:
        filters = [QRCode.tenant_id == tenant_id]
        if not include_inactive:
            filters.append(QRCode.is_active.is_(True))
        if qr_type:
            # Accept a comma-separated list (e.g. "plot,headstone") so callers
            # can fetch the individually-generated (non-system) QR types in a
            # single request without a dedicated multi-type endpoint.
            types = [t.strip() for t in qr_type.split(",") if t.strip()]
            filters.append(QRCode.qr_type.in_(types) if len(types) > 1 else QRCode.qr_type == types[0])

        total = (
            await db.execute(
                select(func.count()).select_from(QRCode).where(*filters)
            )
        ).scalar_one()

        # "generated_at" sort falls back to created_at for rows that haven't
        # finished generating yet, so freshly-enqueued QRs still surface near
        # the top of a "recently generated" view instead of clumping at
        # whichever end NULLs sort to.
        if sort_by == "generated_at":
            order_col = func.coalesce(QRCode.generated_at, QRCode.created_at)
        elif sort_by == "created_at":
            order_col = QRCode.created_at
        else:
            order_col = QRCode.qr_type
        order_by_clause = [order_col.desc(), QRCode.reference_id] if order == "desc" else [order_col, QRCode.reference_id]

        offset = (page - 1) * page_size
        result = await db.execute(
            select(QRCode)
            .where(*filters)
            .order_by(*order_by_clause)
            .offset(offset)
            .limit(page_size)
        )
        items = list(result.scalars().all())
        return items, total

    @staticmethod
    async def generate_one(
        db: AsyncSession,
        tenant_id: str,
        qr_type: str,
        reference_id: str,
        display_label: Optional[str] = None,
        arq_redis=None,
    ) -> dict:
        # For plot and headstone types, validate the plot exists
        memorial_slug: Optional[str] = None
        if qr_type in ("plot", "headstone"):
            from src.apps.plots.models.plot import Plot
            plot_stmt = select(Plot).where(
                Plot.tenant_id == tenant_id,
                Plot.plot_ref == reference_id,
            )
            plot_result = await db.execute(plot_stmt)
            plot = plot_result.scalar_one_or_none()
            if not plot:
                raise NotFoundError("Plot not found")

            if qr_type == "headstone":
                memorial_slug = await QRCodeService._resolve_memorial_slug(
                    db, tenant_id, plot.id
                )
                if not memorial_slug:
                    raise NotFoundError("No memorial exists for this plot")

        # Get tenant subdomain for content_url
        acct_result = await db.execute(
            select(Account).where(Account.id == tenant_id)
        )
        account = acct_result.scalar_one_or_none()
        subdomain = account.subdomain if account else str(tenant_id)

        content_url = QRCodeService.build_content_url(
            subdomain, qr_type, reference_id, memorial_slug=memorial_slug
        )

        # Upsert: INSERT … ON CONFLICT DO UPDATE — only set content_url on INSERT
        stmt = (
            pg_insert(QRCode)
            .values(
                tenant_id=tenant_id,
                qr_type=qr_type,
                reference_id=reference_id,
                display_label=display_label,
                content_url=content_url,
                is_active=True,
            )
            .on_conflict_do_update(
                constraint="uq_qr_codes_tenant_type_ref",
                set_={
                    "display_label": display_label,
                    "is_active": True,
                },
            )
            .returning(QRCode.id)
        )
        result = await db.execute(stmt)
        qr_id = result.scalar_one()
        await db.flush()

        job_id = None
        if arq_redis is not None:
            job = await arq_redis.enqueue_job("generate_qr_code", str(qr_id))
            job_id = job.job_id if job else None

        return {"qr_code_id": str(qr_id), "job_id": job_id}

    @staticmethod
    async def regenerate_all(
        db: AsyncSession,
        tenant_id: str,
        qr_type: Optional[str] = None,
        arq_redis=None,
    ) -> dict:
        filters = [QRCode.tenant_id == tenant_id, QRCode.is_active.is_(True)]
        if qr_type:
            filters.append(QRCode.qr_type == qr_type)

        result = await db.execute(select(QRCode.id).where(*filters))
        ids = [str(row[0]) for row in result.all()]

        enqueued = 0
        if arq_redis is not None:
            for qr_id in ids:
                await arq_redis.enqueue_job("generate_qr_code", qr_id)
                enqueued += 1

        return {"enqueued": enqueued}

    @staticmethod
    async def get_download_url(
        db: AsyncSession,
        tenant_id: str,
        qr_code_id: UUID,
        file_format: Optional[str] = None,
    ) -> str:
        result = await db.execute(
            select(QRCode).where(
                QRCode.id == qr_code_id,
                QRCode.tenant_id == tenant_id,
                QRCode.is_active.is_(True),
            )
        )
        qr = result.scalar_one_or_none()
        if not qr:
            raise NotFoundError("QR code not found")

        # Default (no format specified) prefers the printable PDF for the
        # "Download" action; browsers can't render a PDF inline via <img>, so
        # the preview thumbnail explicitly asks for the SVG instead.
        if file_format == "svg":
            s3_key = qr.svg_s3_key or qr.pdf_s3_key
        else:
            s3_key = qr.pdf_s3_key or qr.svg_s3_key
        if not s3_key:
            raise NotFoundError("QR code file not yet generated")

        import boto3

        def _presign():
            s3 = boto3.client(
                "s3",
                region_name=settings.AWS_REGION,
                aws_access_key_id=settings.AWS_ACCESS_KEY_ID,
                aws_secret_access_key=settings.AWS_SECRET_ACCESS_KEY,
            )
            return s3.generate_presigned_url(
                "get_object",
                Params={"Bucket": settings.S3_BUCKET, "Key": s3_key},
                ExpiresIn=900,
            )

        url: str = await asyncio.to_thread(_presign)
        return url

    @staticmethod
    async def deactivate(db: AsyncSession, tenant_id: str, qr_code_id: UUID) -> QRCode:
        """Soft-delete: sets is_active=False. Per this feature's security
        checklist, qr_codes rows are never hard-deleted — deactivated rows
        are excluded from list/download/regenerate-all going forward but
        remain in the table for audit purposes."""
        result = await db.execute(
            select(QRCode).where(
                QRCode.id == qr_code_id,
                QRCode.tenant_id == tenant_id,
            )
        )
        qr = result.scalar_one_or_none()
        if not qr:
            raise NotFoundError("QR code not found")
        qr.is_active = False
        await db.flush()
        return qr

    @staticmethod
    def build_content_url(
        subdomain: str,
        qr_type: str,
        reference_id: str,
        memorial_slug: Optional[str] = None,
    ) -> str:
        base = f"https://{subdomain}.{settings.APP_DOMAIN}"
        if qr_type == "entrance":
            return f"{base}/"
        if qr_type == "section":
            return f"{base}/find?section={quote(reference_id, safe='')}"
        if qr_type == "plot":
            return f"{base}/find?plot={quote(reference_id, safe='')}"
        if qr_type == "headstone":
            if not memorial_slug:
                raise NotFoundError("No memorial exists for this plot")
            return f"{base}/memorial/{quote(memorial_slug, safe='')}"
        if qr_type == "contract":
            return f"{base}/contract/{quote(reference_id, safe='')}"
        return f"{base}/"

    @staticmethod
    async def _resolve_memorial_slug(
        db: AsyncSession, tenant_id: str, plot_id: UUID
    ) -> Optional[str]:
        """Plot -> Record (active) -> Memorial join, scoped to tenant."""
        from src.apps.records.models.record import Record
        from src.apps.memorials.models.memorial import Memorial

        result = await db.execute(
            select(Memorial.slug)
            .join(Record, Record.id == Memorial.record_id)
            .where(
                Record.plot_id == plot_id,
                Record.tenant_id == tenant_id,
                Record.deleted_at.is_(None),
                Memorial.tenant_id == tenant_id,
            )
        )
        return result.scalars().first()

    @staticmethod
    async def check_bulk_headstone_rate_limit(tenant_id: str) -> None:
        """Redis incr/expire counter: 1 bulk headstone export per 24h per tenant.

        Follows the same fail-open-if-Redis-unavailable pattern as
        section_service._check_autogen_rate_limit — this is an internal
        convenience limiter (not a security control gating outbound traffic),
        so it should not block the feature entirely if Redis is down.
        """
        from src.database.session import get_redis

        client = await get_redis()
        if client is None:
            return
        try:
            key = f"bulk_headstone_rl:{tenant_id}"
            count = await client.incr(key)
            if count == 1:
                await client.expire(key, _BULK_HEADSTONE_RATE_WINDOW_SECONDS)
            if count > _BULK_HEADSTONE_RATE_LIMIT:
                ttl = await client.ttl(key)
                raise RateLimitError(
                    "Bulk headstone QR export already run in the last 24 hours — "
                    "please try again later.",
                    retry_after=max(int(ttl), 1),
                )
        finally:
            try:
                await client.aclose()
            except Exception:
                pass

    @staticmethod
    async def bulk_headstone_export(
        db: AsyncSession,
        tenant_id: str,
        arq_redis=None,
    ) -> dict:
        """Upsert a 'headstone' qr_codes row for every occupied plot that has
        an active memorial, and enqueue generation for each.
        """
        from src.apps.plots.models.plot import Plot
        from src.apps.records.models.record import Record
        from src.apps.memorials.models.memorial import Memorial

        acct_result = await db.execute(
            select(Account).where(Account.id == tenant_id)
        )
        account = acct_result.scalar_one_or_none()
        subdomain = account.subdomain if account else str(tenant_id)

        rows_result = await db.execute(
            select(Plot.plot_ref, Memorial.slug)
            .select_from(Plot)
            .join(Record, Record.plot_id == Plot.id)
            .join(Memorial, Memorial.record_id == Record.id)
            .where(
                Plot.tenant_id == tenant_id,
                Plot.status == "occupied",
                Record.tenant_id == tenant_id,
                Record.deleted_at.is_(None),
                Memorial.tenant_id == tenant_id,
            )
        )
        rows = rows_result.all()

        plot_count = len(rows)
        enqueued = 0
        for plot_ref, memorial_slug in rows:
            content_url = QRCodeService.build_content_url(
                subdomain, "headstone", plot_ref, memorial_slug=memorial_slug
            )
            stmt = (
                pg_insert(QRCode)
                .values(
                    tenant_id=tenant_id,
                    qr_type="headstone",
                    reference_id=plot_ref,
                    content_url=content_url,
                    is_active=True,
                )
                .on_conflict_do_update(
                    constraint="uq_qr_codes_tenant_type_ref",
                    set_={"is_active": True},
                )
                .returning(QRCode.id)
            )
            result = await db.execute(stmt)
            qr_id = result.scalar_one()

            if arq_redis is not None:
                await arq_redis.enqueue_job("generate_qr_code", str(qr_id))
                enqueued += 1

        await db.flush()

        return {"enqueued": enqueued, "plot_count": plot_count}