"""Templates domain — data access layer."""
from __future__ import annotations

from typing import Optional
from uuid import UUID

from sqlalchemy import delete, select
from sqlalchemy.ext.asyncio import AsyncSession

from src.domain.templates.models import Template, TemplatePlanAccess


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

    async def get_by_id(self, template_id: UUID) -> Optional[Template]:
        result = await self._db.execute(
            select(Template).where(Template.id == template_id).where(Template.deleted_at.is_(None))
        )
        return result.scalar_one_or_none()

    async def get_by_ref(self, ref: str) -> Optional[Template]:
        result = await self._db.execute(
            select(Template).where(Template.ref == ref).where(Template.deleted_at.is_(None))
        )
        return result.scalar_one_or_none()

    async def get_by_github_repo(self, github_repo: str) -> list[Template]:
        """Return all active templates backed by this GitHub repo (owner/repo)."""
        result = await self._db.execute(
            select(Template)
            .where(Template.github_repo == github_repo)
            .where(Template.deleted_at.is_(None))
        )
        return list(result.scalars().all())

    async def get_default_for_framework(self, framework: str, plan_slug: str) -> Optional[Template]:
        """Return Core's active default template for a framework and plan."""
        result = await self._db.execute(
            select(Template)
            .where(Template.framework == framework)
            .where(Template.is_default.is_(True))
            .where(Template.is_active.is_(True))
            .where(Template.deleted_at.is_(None))
            .order_by(Template.updated_at.desc(), Template.created_at.desc())
        )
        for template in result.scalars().all():
            if await self.plan_access_allows(template.id, plan_slug):
                return template
        return None

    async def list_all(self, *, include_inactive: bool = False) -> list[Template]:
        q = select(Template).where(Template.deleted_at.is_(None))
        if not include_inactive:
            q = q.where(Template.is_active.is_(True))
        q = q.order_by(Template.created_at.desc())
        result = await self._db.execute(q)
        return list(result.scalars().all())

    async def plan_access_allows(self, template_id: UUID, plan_slug: str) -> bool:
        """Whether ``plan_slug`` may use this template (same rules as :meth:`list_for_plan`).

        No rows in ``template_plan_access`` → all plans. Otherwise the slug must appear
        in at least one row.
        """
        result = await self._db.execute(
            select(TemplatePlanAccess.plan_slug).where(TemplatePlanAccess.template_id == template_id)
        )
        slugs = list(result.scalars().all())
        if not slugs:
            return True
        return plan_slug in slugs

    async def list_for_plan(self, plan_slug: str) -> list[Template]:
        """Return active templates available to ``plan_slug``.

        Includes templates with no plan restrictions (all-plans) and templates
        explicitly restricted to this plan slug.
        """
        # Fetch templates that either have NO plan_access rows OR have a row
        # matching plan_slug.  Use two queries + merge in Python (small catalog).
        no_restriction_q = (
            select(Template)
            .where(Template.deleted_at.is_(None))
            .where(Template.is_active.is_(True))
            .where(
                ~Template.id.in_(
                    select(TemplatePlanAccess.template_id).distinct()
                )
            )
        )
        plan_specific_q = (
            select(Template)
            .join(TemplatePlanAccess, TemplatePlanAccess.template_id == Template.id)
            .where(Template.deleted_at.is_(None))
            .where(Template.is_active.is_(True))
            .where(TemplatePlanAccess.plan_slug == plan_slug)
        )
        r1 = await self._db.execute(no_restriction_q)
        r2 = await self._db.execute(plan_specific_q)
        seen: set[UUID] = set()
        templates: list[Template] = []
        for t in list(r1.scalars().all()) + list(r2.scalars().all()):
            if t.id not in seen:
                seen.add(t.id)
                templates.append(t)
        templates.sort(key=lambda t: t.created_at, reverse=True)
        return templates

    async def list_showcased(self) -> list[Template]:
        """Return public-ready templates in their configured marketing order."""
        result = await self._db.execute(
            select(Template)
            .where(Template.deleted_at.is_(None))
            .where(Template.is_active.is_(True))
            .where(Template.is_showcased.is_(True))
            .where(Template.deploy_status == "live")
            .where(Template.demo_url.is_not(None))
            .order_by(
                Template.showcase_rank.asc().nulls_last(),
                Template.created_at.desc(),
            )
        )
        return list(result.scalars().all())

    async def get_showcased_by_ref(self, ref: str) -> Optional[Template]:
        """Return one public-ready template by its stable marketing reference."""
        result = await self._db.execute(
            select(Template)
            .where(Template.ref == ref)
            .where(Template.deleted_at.is_(None))
            .where(Template.is_active.is_(True))
            .where(Template.is_showcased.is_(True))
            .where(Template.deploy_status == "live")
            .where(Template.demo_url.is_not(None))
        )
        return result.scalar_one_or_none()

    async def create(
        self,
        *,
        name: str,
        ref: str,
        github_repo: str,
        framework: str,
        demo_subdomain: str,
        dev_subdomain: str,
        description: Optional[str] = None,
        thumbnail_url: Optional[str] = None,
        tags: Optional[list] = None,
        github_installation_id: int = 0,
        main_branch: str = "main",
        dev_branch: str = "development",
        starter_branch: str = "starter",
        is_active: bool = False,
        is_default: bool = False,
        is_showcased: bool = False,
        showcase_rank: Optional[int] = None,
        created_by: Optional[UUID] = None,
    ) -> Template:
        t = Template(
            name=name,
            ref=ref,
            github_repo=github_repo,
            framework=framework,
            demo_subdomain=demo_subdomain,
            dev_subdomain=dev_subdomain,
            description=description,
            thumbnail_url=thumbnail_url,
            tags=tags,
            github_installation_id=github_installation_id,
            main_branch=main_branch,
            dev_branch=dev_branch,
            starter_branch=starter_branch,
            is_active=is_active,
            is_default=is_default,
            is_showcased=is_showcased,
            showcase_rank=showcase_rank,
            created_by=created_by,
            deploy_status="pending",
        )
        self._db.add(t)
        await self._db.flush()
        await self._db.refresh(t)
        return t

    async def update(
        self,
        template_id: UUID,
        *,
        name: Optional[str] = None,
        description: Optional[str] = None,
        thumbnail_url: Optional[str] = None,
        tags: Optional[list] = None,
        framework: Optional[str] = None,
        main_branch: Optional[str] = None,
        dev_branch: Optional[str] = None,
        starter_branch: Optional[str] = None,
        starter_sha: Optional[str] = None,
        is_active: Optional[bool] = None,
        is_default: Optional[bool] = None,
        is_showcased: Optional[bool] = None,
        showcase_rank: Optional[int] = None,
        clear_description: bool = False,
        clear_thumbnail_url: bool = False,
        clear_tags: bool = False,
        clear_showcase_rank: bool = False,
        deploy_status: Optional[str] = None,
        demo_url: Optional[str] = None,
        dev_url: Optional[str] = None,
        cf_pages_project_name: Optional[str] = None,
    ) -> Optional[Template]:
        t = await self.get_by_id(template_id)
        if not t:
            return None
        if name is not None:
            t.name = name
        if description is not None or clear_description:
            t.description = description
        if thumbnail_url is not None or clear_thumbnail_url:
            t.thumbnail_url = thumbnail_url
        if tags is not None or clear_tags:
            t.tags = tags
        if framework is not None:
            t.framework = framework
        if main_branch is not None:
            t.main_branch = main_branch
        if dev_branch is not None:
            t.dev_branch = dev_branch
        if starter_branch is not None:
            t.starter_branch = starter_branch
        if starter_sha is not None:
            t.starter_sha = starter_sha
        if is_active is not None:
            t.is_active = is_active
        if is_default is not None:
            t.is_default = is_default
        if is_showcased is not None:
            t.is_showcased = is_showcased
        if showcase_rank is not None or clear_showcase_rank:
            t.showcase_rank = showcase_rank
        if deploy_status is not None:
            t.deploy_status = deploy_status
        if demo_url is not None:
            t.demo_url = demo_url
        if dev_url is not None:
            t.dev_url = dev_url
        if cf_pages_project_name is not None:
            t.cf_pages_project_name = cf_pages_project_name
        await self._db.flush()
        # ``updated_at`` is server-managed and can be expired after the UPDATE.
        # Reload it here while async database IO is still available to callers.
        await self._db.refresh(t)
        return t

    async def get_stale_platform_files(
        self, current_version: str, limit: int = 50
    ) -> list[Template]:
        """Return templates whose platform_files_version is NULL or behind."""
        from sqlalchemy import or_, update as sa_update
        result = await self._db.execute(
            select(Template)
            .where(Template.deleted_at.is_(None))
            .where(
                or_(
                    Template.platform_files_version.is_(None),
                    Template.platform_files_version != current_version,
                )
            )
            .limit(limit)
        )
        return list(result.scalars().all())

    async def set_platform_files_version(self, template_id: UUID, version: str) -> None:
        from sqlalchemy import update as sa_update
        await self._db.execute(
            sa_update(Template)
            .where(Template.id == template_id)
            .values(platform_files_version=version)
        )
        await self._db.flush()

    async def soft_delete(self, template_id: UUID) -> None:
        from datetime import datetime, timezone
        t = await self.get_by_id(template_id)
        if t:
            t.deleted_at = datetime.now(timezone.utc)
            t.is_active = False
            await self._db.flush()

    # ------------------------------------------------------------------
    # Plan access
    # ------------------------------------------------------------------
    async def get_plan_slugs(self, template_id: UUID) -> list[str]:
        result = await self._db.execute(
            select(TemplatePlanAccess.plan_slug).where(TemplatePlanAccess.template_id == template_id)
        )
        return [r for r in result.scalars().all()]

    async def replace_plan_access(self, template_id: UUID, plan_slugs: list[str]) -> None:
        """Replace the full plan-access list for a template (idempotent)."""
        await self._db.execute(
            delete(TemplatePlanAccess).where(TemplatePlanAccess.template_id == template_id)
        )
        for slug in set(plan_slugs):
            self._db.add(TemplatePlanAccess(template_id=template_id, plan_slug=slug.strip()))
        await self._db.flush()