"""Templates domain — SQLAlchemy models.

Templates are platform-managed boilerplate apps that users can clone when creating
a new project.  Each template lives in a private GitHub repo with three standard
branches:

  main         → live demo  (tpl{ref}.{apex})
  development  → work-in-progress demo  (dev-tpl{ref}.{apex})
  starter      → boilerplate snapshot used when a user creates a project from this template

CI/CD is wired through GitHub Actions: pushes to ``main`` or ``development`` trigger
a CF Pages build; the backend receives a push webhook, polls CF Pages, and updates KV
routing.  Pushes to ``starter`` update ``template.starter_sha`` so the next user project
gets the freshest boilerplate.
"""
from __future__ import annotations

from datetime import datetime
from typing import Optional
from uuid import UUID, uuid4

import sqlalchemy as sa
from sqlalchemy import ForeignKey, String, Text
from sqlalchemy.dialects.postgresql import JSONB, UUID as PGUUID
from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy.sql import func
from sqlalchemy.types import DateTime

from src.core.database import Base

TEMPLATE_DEPLOY_STATUS = sa.Enum(
    "pending", "deploying", "live", "failed",
    name="p2hs_template_deploy_status",
    create_type=False,
)


class Template(Base):
    __tablename__ = "templates"

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True, default=uuid4)

    # Human-readable name and stable URL slug (e.g. "tpl001").
    # The ref becomes the CF Pages project_ref and the subdomain label.
    name: Mapped[str] = mapped_column(String(120), nullable=False)
    ref: Mapped[str] = mapped_column(String(63), unique=True, nullable=False)
    description: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    thumbnail_url: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    tags: Mapped[Optional[list]] = mapped_column(JSONB, nullable=True)

    # Source code — private GitHub repo (owner/repo format).
    github_repo: Mapped[str] = mapped_column(String(255), nullable=False)
    # GitHub App installation ID with access to this repo. 0 = use platform default.
    github_installation_id: Mapped[int] = mapped_column(sa.BigInteger, nullable=False, server_default="0")

    # Framework determines CF Pages build config (react_app, nuxt_app, html, …).
    framework: Mapped[str] = mapped_column(String(40), nullable=False, server_default="react_app")

    # Branch conventions — defaults match the platform standard.
    main_branch: Mapped[str] = mapped_column(String(100), nullable=False, server_default="main")
    dev_branch: Mapped[str] = mapped_column(String(100), nullable=False, server_default="development")
    starter_branch: Mapped[str] = mapped_column(String(100), nullable=False, server_default="starter")

    # Latest commit SHA on the starter branch — refreshed by push webhook.
    starter_sha: Mapped[Optional[str]] = mapped_column(String(40), nullable=True)

    # Subdomains (single DNS label, no apex).  Defaults derived from ref on creation.
    demo_subdomain: Mapped[str] = mapped_column(String(63), nullable=False)   # e.g. "tpl001"
    dev_subdomain: Mapped[str] = mapped_column(String(63), nullable=False)    # e.g. "dev-tpl001"

    # CF Pages project name (stored after first deploy; computed as pages_project_name(ref)).
    cf_pages_project_name: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)

    # Live URLs set after successful deploy.
    demo_url: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    dev_url: Mapped[Optional[str]] = mapped_column(Text, nullable=True)

    # Visibility.
    deploy_status: Mapped[str] = mapped_column(
        TEMPLATE_DEPLOY_STATUS, nullable=False, server_default="pending"
    )
    is_active: Mapped[bool] = mapped_column(sa.Boolean, nullable=False, server_default="false")
    is_default: Mapped[bool] = mapped_column(sa.Boolean, nullable=False, server_default="false")
    # Marketing visibility is deliberately independent from project-creation availability.
    is_showcased: Mapped[bool] = mapped_column(sa.Boolean, nullable=False, server_default="false")
    showcase_rank: Mapped[Optional[int]] = mapped_column(sa.Integer, nullable=True)

    # Version of platform-managed files last pushed to this repo (p2hs.platform.json,
    # .cursor/instructions.md, etc.). NULL = pre-versioning / never synced.
    platform_files_version: Mapped[Optional[str]] = mapped_column(sa.String(20), nullable=True)

    created_by: Mapped[Optional[UUID]] = mapped_column(
        PGUUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"), nullable=True
    )
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )
    deleted_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)


class TemplatePlanAccess(Base):
    """Which plan slugs may use a template.

    If a template has no rows here it is available to every plan.
    If it has rows, only users on those plans see or use it.
    """
    __tablename__ = "template_plan_access"

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True, default=uuid4)
    template_id: Mapped[UUID] = mapped_column(
        PGUUID(as_uuid=True), ForeignKey("templates.id", ondelete="CASCADE"), nullable=False
    )
    plan_slug: Mapped[str] = mapped_column(String(60), nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())

    __table_args__ = (
        sa.UniqueConstraint("template_id", "plan_slug", name="uq_template_plan_access"),
    )