"""
SiteTemplate CRUD — pure database-access layer.

All queries use SQLAlchemy 2.0 select() style and filter deleted_at.is_(None).
"""

import logging
from typing import Dict, List, Optional, Tuple

from sqlalchemy import func, select
from sqlalchemy.orm import Session

from src.apps.site_templates.models.site_template import SiteTemplate

logger = logging.getLogger(__name__)


def get_template_by_key(db: Session, template_key: str) -> Optional[SiteTemplate]:
    """Fetch an active, non-deleted template by its machine-readable key."""
    stmt = select(SiteTemplate).where(
        SiteTemplate.template_key == template_key,
        SiteTemplate.deleted_at.is_(None),
    )
    return db.execute(stmt).scalar_one_or_none()


def get_template_by_id(db: Session, id: int) -> Optional[SiteTemplate]:
    """Fetch an active, non-deleted template by primary key."""
    stmt = select(SiteTemplate).where(
        SiteTemplate.id == id,
        SiteTemplate.deleted_at.is_(None),
    )
    return db.execute(stmt).scalar_one_or_none()


def list_templates(
    db: Session,
    channel: Optional[str] = None,
    template_type: Optional[str] = None,
    is_active: Optional[bool] = None,
    search: Optional[str] = None,
    page: int = 1,
    page_size: int = 20,
) -> Tuple[List[SiteTemplate], int]:
    """
    Return a paginated list of non-deleted templates with optional filters.

    Returns:
        (items, total_count)
    """
    conditions = [SiteTemplate.deleted_at.is_(None)]

    if channel is not None:
        conditions.append(SiteTemplate.channel == channel)

    if template_type is not None:
        conditions.append(SiteTemplate.template_type == template_type)

    if is_active is not None:
        conditions.append(SiteTemplate.is_active == is_active)

    if search:
        escaped = search.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_")
        conditions.append(
            SiteTemplate.template_name.ilike(f"%{escaped}%", escape="\\")
            | SiteTemplate.template_key.ilike(f"%{escaped}%", escape="\\")
        )

    base_stmt = select(SiteTemplate).where(*conditions)

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

    items_stmt = (
        base_stmt
        .order_by(SiteTemplate.template_type.asc(), SiteTemplate.template_name.asc())
        .offset((page - 1) * page_size)
        .limit(page_size)
    )
    items = list(db.execute(items_stmt).scalars().all())

    return items, total


_UPDATABLE_FIELDS = frozenset({
    "subject",
    "body_html",
    "body_text",
    "primary_color",
    "accent_color",
    "text_color",
    "is_active",
})


def update_template(db: Session, id: int, data: dict) -> Optional[SiteTemplate]:
    """
    Apply a partial update to a template row.

    Only fields in ``_UPDATABLE_FIELDS`` are written; any other keys in *data*
    are silently ignored.  This prevents callers from overwriting protected
    columns such as ``id``, ``template_key``, ``deleted_at``, or
    ``merchant_id`` by crafting an unexpected payload.

    Args:
        id:   Primary key of the template.
        data: Dict of column-name → new-value pairs (only allowed keys applied).

    Returns:
        Updated SiteTemplate or None if not found.
    """
    template = get_template_by_id(db, id)
    if not template:
        return None

    for field, value in data.items():
        if field in _UPDATABLE_FIELDS and value is not None:
            setattr(template, field, value)

    db.flush()
    db.refresh(template)
    return template


def set_active(db: Session, id: int, is_active: bool) -> Optional[SiteTemplate]:
    """Toggle the is_active flag on a template."""
    template = get_template_by_id(db, id)
    if not template:
        return None

    template.is_active = is_active
    db.flush()
    db.refresh(template)
    return template


def get_variable_registry(db: Session) -> Dict[str, List[str]]:
    """
    Return a mapping of {template_key: variables_list} for all non-deleted templates.
    """
    stmt = select(SiteTemplate.template_key, SiteTemplate.variables).where(
        SiteTemplate.deleted_at.is_(None),
    )
    rows = db.execute(stmt).all()
    return {row.template_key: (row.variables or []) for row in rows}