platform/harness/windows.py

"""harness/windows.py — the DATE WINDOW vocabulary (CG-7, owner item 2, 2026-07-26).

A filter condition on a measure reads:

    Where [Sales] [in the last 90 days] [>] [5,000]

The middle bracket is this module. It turns a window SPEC — a small JSON object a saved view can
persist — into a concrete `(date_from, date_to)` pair of ISO dates.

WHY IT IS ITS OWN MODULE, AND WHY IT TAKES `today` AS AN ARGUMENT
-----------------------------------------------------------------------------------------------
Two engines must agree on what "this quarter" means: this one (which compiles the SQL) and
`customer-grid/windows.ts` (which renders the label the user reads and the bounds the client
engine would use). If they disagree by one day, the grid shows a number the label denies, and
nothing errors. `aios-web/verify_windows.py` holds them in lock-step over a fixed set of probe
dates, exactly as verify_filter_engine.py does for the filter tree.

`today` is a PARAMETER, never `date.today()` read inside. Three reasons, all learned:
  - a gate cannot compare two engines on "now" — the two runs are milliseconds apart and land on
    different sides of midnight roughly once every few thousand runs, which is the worst kind of
    flake because it looks like a real divergence
  - a window resolved during a request must not shift between the count query and the row query
  - the tenant's day boundary is the tenant's, not the server's

DESIGN NOTES THAT ARE EASY TO GET WRONG
-----------------------------------------------------------------------------------------------
  - Bounds are INCLUSIVE at both ends and are DATES, not timestamps. `store_query` casts both
    sides to TIMESTAMP and appends 23:59:59 to the upper bound, so a bare date is correct there;
    the client compares the 10-char ISO prefix, so a bare date is correct there too.
  - "Last N days" INCLUDES today. Airtable's "the past week" means the last 7 days up to and
    including now, not the 7 days before yesterday. Off-by-one here silently drops today's
    orders from every "recent" filter — invisible until someone asks why a sale they just
    entered is missing.
  - Week starts MONDAY (ISO 8601). Stated rather than defaulted: Python's weekday() is
    Monday=0 and JavaScript's getDay() is Sunday=0, so the two engines disagree unless one of
    them is explicitly corrected. That correction is the single most likely divergence in this
    file, and the gate probes a Sunday and a Monday for exactly that reason.
  - A `custom` window with only one bound is legal and means open-ended on the other side.
  - An UNRESOLVABLE window returns None rather than a default. A window that quietly becomes
    "all time" would widen a filter while still reporting an authoritative count.
"""
import datetime as _dt

#: Every window kind the UI may offer. Mirrors WINDOW_KINDS in customer-grid/windows.ts;
#: verify_windows.py asserts the two lists are identical, because a kind the client can emit and
#: the server cannot resolve is a filter that silently stops narrowing.
WINDOW_KINDS = (
    "all_time",
    "today",
    "yesterday",
    "this_week",
    "last_week",
    "this_month",
    "last_month",
    "this_quarter",
    "last_quarter",
    "this_year",
    "last_year",
    "ytd",
    "ytd_last_year",
    "ltm",
    "past_week",
    "past_month",
    "past_year",
    "last_n_days",
    "next_n_days",
    "custom",
)

#: Kinds that carry an integer `n`.
N_KINDS = frozenset({"last_n_days", "next_n_days"})

#: How each kind reads in a sentence. The UI renders these; they are here so the label and the
#: arithmetic cannot drift apart in the one place a user would never think to check.
WINDOW_LABELS = {
    "all_time": "all time",
    "today": "today",
    "yesterday": "yesterday",
    "this_week": "this week",
    "last_week": "last week",
    "this_month": "this month",
    "last_month": "last month",
    "this_quarter": "this quarter",
    "last_quarter": "last quarter",
    "this_year": "this year",
    "last_year": "last year",
    "ytd": "year to date",
    # "last year to date" (LYTD), not "year to date, last year": the longer phrasing CLIPPED to
    # "year to date, last ye" in the 146px window select — and this vocabulary is CLOSED, so
    # the rule for it is that it stays readable rather than that it fits. Seen in a live
    # screenshot of the owner's own comparison; every assertion in that run was green.
    "ytd_last_year": "last year to date",
    "ltm": "the last 12 months",
    "past_week": "the past week",
    "past_month": "the past month",
    "past_year": "the past year",
    "last_n_days": "the last {n} days",
    "next_n_days": "the next {n} days",
    "custom": "a custom range",
}

MAX_N = 3650          # ten years; a bound, not a business rule

# --- the DATE-VALUE anchors (owner item 3, 2026-07-26) ----------------------------------------
# The second half of a date CONDITION, as distinct from a measure's window:
#
#     Where [Last order] [is before] [one month ago]
#                          ^ op        ^ THIS
#
# A window answers "over what period do I sum"; an anchor answers "which single date am I
# comparing against". Both live in this module for one reason: they are the same two-engine
# contract, resolved from the same `today`, and a gate that holds one in lock-step and not the
# other would leave half the sentence free to drift.

#: Anchor modes, in the order the picker offers them. `exact` is the historical behaviour — a
#: rule with NO mode is an `exact` rule whose value is an ISO date, which is what every view
#: saved before this change carries.
ANCHOR_MODES = (
    "today",
    "yesterday",
    "one_week_ago",
    "one_month_ago",
    "n_days_ago",
    "exact",
)

#: Modes that carry NO value. ⚠ This set is load-bearing far outside this module: a rule whose
#: value is blank normally reads as INACTIVE, and an inactive rule is IGNORED — which WIDENS the
#: result under a count nobody would doubt. Every activeness check (`isRuleActive` in TS,
#: `filter_sql.is_rule_active`, and the validator's value handling) has to know these four are
#: active with an empty value.
ANCHOR_VALUE_FREE = frozenset({"today", "yesterday", "one_week_ago", "one_month_ago"})

ANCHOR_LABELS = {
    "today": "today",
    "yesterday": "yesterday",
    "one_week_ago": "one week ago",
    "one_month_ago": "one month ago",
    "n_days_ago": "{n} days ago",
    "exact": "an exact date",
}


def _iso(d):
    return d.isoformat()


def _month_start(d):
    return d.replace(day=1)


def _month_end(d):
    return _next_month(d.replace(day=1)) - _dt.timedelta(days=1)


def _next_month(d):
    return (d.replace(day=28) + _dt.timedelta(days=4)).replace(day=1)


def _quarter_start(d):
    return _dt.date(d.year, 3 * ((d.month - 1) // 3) + 1, 1)


def _days_in_month(y, m):
    return (_dt.date(y + (m == 12), 1 if m == 12 else m + 1, 1) - _dt.timedelta(days=1)).day


def _shift_months(d, n):
    """`d` moved `n` months, CLAMPING the day to the target month's length.

    Jan 31 back one month is Dec 31, but Mar 31 back one month is Feb 28 (or 29) — there is no
    Feb 31 to land on. Both engines must clamp identically or "the past month" differs by up to
    three days for a third of the calendar; the gate probes a 31st and a leap day for exactly it.
    """
    total = (d.year * 12 + (d.month - 1)) + n
    y, m = divmod(total, 12)
    m += 1
    return _dt.date(y, m, min(d.day, _days_in_month(y, m)))


def _parse_date(v):
    """Accept 'YYYY-MM-DD' (and tolerate a longer ISO timestamp by taking its date part)."""
    if isinstance(v, _dt.date):
        return v
    s = str(v or "").strip()[:10]
    if not s:
        return None
    try:
        return _dt.date.fromisoformat(s)
    except ValueError:
        return None


def normalize(spec):
    """Coerce an untrusted window spec to `{kind, n?, from?, to?}` or None.

    Fail-closed on the KIND (an unknown kind is not a window), tolerant on the rest — the same
    split `clean_filter_tree` uses, so one malformed window cannot cost a user their saved view.
    """
    if not isinstance(spec, dict):
        return None
    kind = spec.get("kind")
    if kind not in WINDOW_KINDS:
        return None
    out = {"kind": kind}
    if kind in N_KINDS:
        try:
            n = int(spec.get("n"))
        except (TypeError, ValueError):
            return None
        if n < 1 or n > MAX_N:
            return None
        out["n"] = n
    if kind == "custom":
        f, t = _parse_date(spec.get("from")), _parse_date(spec.get("to"))
        if f is None and t is None:
            return None                    # a custom range with no bounds is not a window
        if f is not None and t is not None and f > t:
            f, t = t, f                    # the builder cannot enforce order; the engine can
        if f is not None:
            out["from"] = _iso(f)
        if t is not None:
            out["to"] = _iso(t)
    return out


def resolve(spec, today):
    """Window spec + the tenant's today -> `(date_from, date_to)`, both inclusive ISO dates.

    Either side may be None, meaning open-ended. Returns None when the spec is not a window at
    all — callers MUST treat that as "this condition cannot be evaluated" and refuse, never as
    "no window", which would silently widen the result to all time.
    """
    spec = normalize(spec)
    if spec is None:
        return None
    kind = spec["kind"]
    d = _parse_date(today)
    if d is None:
        raise ValueError("resolve() needs an explicit `today` — see this module's docstring")

    if kind == "all_time":
        return (None, None)
    if kind == "today":
        return (_iso(d), _iso(d))
    if kind == "yesterday":
        y = d - _dt.timedelta(days=1)
        return (_iso(y), _iso(y))

    # ISO 8601: the week starts MONDAY. Python's weekday() is already Monday=0; the TS mirror
    # has to correct getDay(), which is Sunday=0. The gate probes both a Sunday and a Monday.
    if kind == "this_week":
        start = d - _dt.timedelta(days=d.weekday())
        return (_iso(start), _iso(start + _dt.timedelta(days=6)))
    if kind == "last_week":
        start = d - _dt.timedelta(days=d.weekday() + 7)
        return (_iso(start), _iso(start + _dt.timedelta(days=6)))

    if kind == "this_month":
        return (_iso(_month_start(d)), _iso(_month_end(d)))
    if kind == "last_month":
        prev = _month_start(d) - _dt.timedelta(days=1)
        return (_iso(_month_start(prev)), _iso(prev))

    if kind == "this_quarter":
        qs = _quarter_start(d)
        qe = _month_end(_dt.date(qs.year, qs.month + 2, 1))
        return (_iso(qs), _iso(qe))
    if kind == "last_quarter":
        prev_end = _quarter_start(d) - _dt.timedelta(days=1)
        qs = _quarter_start(prev_end)
        return (_iso(qs), _iso(prev_end))

    if kind == "this_year":
        return (_iso(_dt.date(d.year, 1, 1)), _iso(_dt.date(d.year, 12, 31)))
    if kind == "last_year":
        return (_iso(_dt.date(d.year - 1, 1, 1)), _iso(_dt.date(d.year - 1, 12, 31)))
    if kind == "ytd":
        # year-to-date ENDS TODAY, unlike this_year which runs to Dec 31. The distinction is the
        # whole reason both exist: comparing "this year" against last year double-counts the
        # months that have not happened yet.
        return (_iso(_dt.date(d.year, 1, 1)), _iso(d))
    if kind == "ytd_last_year":
        # SAME PERIOD last year — Jan 1 LY through today's month/day LY. This is the honest
        # partner of `ytd`, and it is not a nicety: it mirrors `core.periods.ytd_last_year`
        # EXACTLY (including the Feb 29 -> Feb 28 clamp), which is what `pool()` computes the
        # `revenue_ly` column from. `Sales[ytd] < Sales[ytd_last_year]` therefore reproduces the
        # retired `at_risk > 0` condition rather than approximating it. Comparing `ytd` against
        # `last_year` instead would pit seven months against twelve.
        return (_iso(_dt.date(d.year - 1, 1, 1)), _iso(_shift_months(d, -12)))
    if kind == "ltm":
        # the last twelve months INCLUDING today: 365 days back, both ends inclusive
        return (_iso(d - _dt.timedelta(days=364)), _iso(d))

    # "The past week/month/year" are ROLLING and end TODAY — Airtable's wording, and NOT the
    # calendar kinds above: `last_week` is the previous Monday-Sunday, `past_week` is the seven
    # days ending today. Both are offered because a user means different things by them, and
    # picking one to serve both would silently answer the other question.
    #   past_week  = the same rule as last_n_days(7) — one period back, PLUS ONE DAY, so today
    #                is included and the span is exactly 7 days rather than 8.
    #   past_year  agrees with `ltm` except across a leap day, where the calendar shift keeps the
    #              same month/day and the 364-day subtraction cannot.
    if kind == "past_week":
        return (_iso(d - _dt.timedelta(days=6)), _iso(d))
    if kind == "past_month":
        return (_iso(_shift_months(d, -1) + _dt.timedelta(days=1)), _iso(d))
    if kind == "past_year":
        return (_iso(_shift_months(d, -12) + _dt.timedelta(days=1)), _iso(d))

    if kind == "last_n_days":
        # INCLUSIVE of today — "the last 7 days" is today and the 6 before it, not the 7 before
        # yesterday. Getting this wrong drops today's orders from every recent-activity filter.
        return (_iso(d - _dt.timedelta(days=spec["n"] - 1)), _iso(d))
    if kind == "next_n_days":
        return (_iso(d), _iso(d + _dt.timedelta(days=spec["n"] - 1)))

    if kind == "custom":
        return (spec.get("from"), spec.get("to"))
    raise AssertionError(f"unhandled window kind {kind!r}")   # unreachable; WINDOW_KINDS is closed


def label(spec):
    """How a window reads in the condition sentence ('in the last 90 days')."""
    spec = normalize(spec)
    if spec is None:
        return "an invalid range"
    return WINDOW_LABELS[spec["kind"]].format(n=spec.get("n"))


def resolve_anchor(mode, value, today):
    """A date condition's right-hand side -> ONE inclusive ISO date, or None.

    `mode` is an entry of ANCHOR_MODES; None/'' means `exact`, which is what every view saved
    before anchors existed carries (op + an ISO date in `value`). `value` supplies the number for
    `n_days_ago` and the date for `exact`, and is ignored by the four value-free modes.

    Returns None when the anchor cannot be resolved — an unknown mode, a non-numeric N, a value
    that is not a date. The caller must then treat the CONDITION as unanswerable and match
    nothing. It must NOT fall through to "no condition": that widens the result while the count
    beside it still looks authoritative, which is the whole reason this returns None rather than
    a best guess (same contract as `resolve()` above).

    `n_days_ago` accepts n = 0 where a WINDOW requires n >= 1. A zero-day window is empty and
    could only be a mistake; "0 days ago" is today, which is a date a person can mean.
    """
    m = mode or "exact"
    if m not in ANCHOR_MODES:
        return None
    d = _parse_date(today)
    if d is None:
        raise ValueError("resolve_anchor() needs an explicit `today` — see this module's "
                         "docstring on why the clock is never read here")
    if m == "today":
        return _iso(d)
    if m == "yesterday":
        return _iso(d - _dt.timedelta(days=1))
    if m == "one_week_ago":
        # A week ago is a DATE (today minus 7), not the past-week RANGE. The two read almost
        # identically in English and mean different things in a comparison.
        return _iso(d - _dt.timedelta(days=7))
    if m == "one_month_ago":
        return _iso(_shift_months(d, -1))
    if m == "n_days_ago":
        s = str("" if value is None else value).strip()
        # `[0-9]` not `\d`: a Python \d matches every Unicode decimal digit, so a fullwidth
        # '３０' would resolve here and be rejected by the TS mirror's Number() — the exact
        # two-engine divergence filter_sql.to_num was rewritten to avoid.
        if not s or not all("0" <= ch <= "9" for ch in s):
            return None
        n = int(s)
        # No `n < 0` guard: the digit test above already excludes a sign, so it would be
        # unreachable. The negative control proved that — removing it changed nothing, which is
        # how dead code hides in a defensive-looking line. The CAP is the live guard.
        if n > MAX_N:
            return None
        return _iso(d - _dt.timedelta(days=n))
    parsed = _parse_date(value)                      # exact
    return None if parsed is None else _iso(parsed)


def anchor_label(mode, value):
    """How an anchor reads in the condition sentence ('one month ago', '30 days ago')."""
    m = mode or "exact"
    if m not in ANCHOR_MODES:
        return "an invalid date"
    if m == "n_days_ago":
        return ANCHOR_LABELS[m].format(n=str("" if value is None else value).strip() or "N")
    if m == "exact":
        return str("" if value is None else value).strip() or ANCHOR_LABELS[m]
    return ANCHOR_LABELS[m]