"""Shared recurring NextAction helpers."""

from __future__ import annotations

from typing import Any

from db import get_connection
from psycopg2.extras import RealDictCursor
from tenant_context import TenantContext

_PRACTICE_DUE_SQL = """
    SELECT
        'recurring_due' AS kind,
        re.title AS title,
        COALESCE(NULLIF(re.description, ''), 'Heutige Übung') AS summary,
        re.initiative_id,
        re.id AS recurring_id,
        NULL::uuid AS action_id,
        NULL::uuid AS backlog_item_id,
        'recurring_due' AS reason_code,
        'Übung heute erledigen' AS recommended_action
    FROM recurring_elements re
    LEFT JOIN roadmap_items ri
      ON ri.id = re.roadmap_item_id AND ri.tenant_id = re.tenant_id
    WHERE re.tenant_id = %s AND re.initiative_id = %s
      AND re.status = 'active'
      AND (re.next_due_at IS NULL OR re.next_due_at <= NOW())
      AND (re.roadmap_item_id IS NULL OR (ri.status = 'active' AND ri.item_type = 'maturity_stage'))
      AND NOT EXISTS (
          SELECT 1 FROM cadence_instances ci
          WHERE ci.recurring_element_id = re.id
            AND ci.tenant_id = re.tenant_id
            AND ci.status = 'completed'
            AND ci.completed_at >= CURRENT_DATE
      )
    ORDER BY re.next_due_at ASC NULLS FIRST, re.title
    LIMIT %s
"""


def _serialize_recurring_rows(rows) -> list[dict[str, Any]]:
    items: list[dict[str, Any]] = []
    for row in rows:
        item = dict(row)
        item["initiative_id"] = str(item["initiative_id"])
        if item.get("recurring_id"):
            item["recurring_id"] = str(item["recurring_id"])
        items.append(item)
    return items


def recurring_due_candidates(
    ctx: TenantContext, initiative_id: str, *, limit: int
) -> list[dict[str, Any]]:
    conn = get_connection()
    try:
        with conn.cursor(cursor_factory=RealDictCursor) as cur:
            cur.execute(_PRACTICE_DUE_SQL, (ctx.tenant_id, initiative_id, limit))
            return _serialize_recurring_rows(cur.fetchall())
    finally:
        conn.close()


def active_recurring_candidates(
    ctx: TenantContext, initiative_id: str, *, limit: int = 1
) -> list[dict[str, Any]]:
    """Due practices only — no fallback when already completed today."""
    return recurring_due_candidates(ctx, initiative_id, limit=limit)