#!/usr/bin/env python3
"""Auto-promote stale shipped/ordered rows to assumed_delivered.

Step 7 of check-orders SKILL.md: some senders (e.g. Chewy Autoship)
never send a delivered email — the order arrives but the status in
the orders table stays `shipped` forever and Step 9's "Overdue
delivery" rule keeps firing for the full 30-day cutoff.

A row is promoted if it matches EITHER eligibility path below.

Expected-delivery path (all three must hold):
  - status IN ('shipped', 'ordered') — only non-terminal statuses
  - expected_delivery NOT NULL AND parses as ISO date AND >= 10 days
    before today (real overdue, not slow shipping)
  - last_updated >= 10 days before now (no fresh email about this
    order in >= 10 days)

Age-ceiling path (`jbaruch/nanoclaw-orders#61`, both must hold):
  - status = 'ordered'
  - order_date parses as ISO date AND > STALE_ORDERED_CEILING_DAYS
    before today

The age-ceiling path drains the `ordered` "roach motel": an order
whose shipment/delivery email never matched (merchant mismatch, no
tracking email, digital/subscription/Kickstarter/autoship) has a NULL
`expected_delivery`, so the expected-delivery path never catches it and
it sits in `ordered` forever — re-flagged by the stuck detector every
run inside its window. Past the ceiling the order has long since arrived
or died; `assumed_delivered` is the honest terminal state. The ceiling
is aligned with `compute-stuck-orders.py`'s STUCK_ORDER_MAX_DAYS (its
stuck window's upper bound), so a row ages out of "stuck" flagging and
into a resolved status in the same pass rather than lingering silently.
Unlike the expected-delivery path this ignores `last_updated` — a
never-matched order gets no fresh emails, so its `last_updated` is its
ingestion time and staleness is already implied by the order_date age.

`expected_delivery` is sometimes a free-text string like "overnight"
rather than an ISO date. Per the original within-days.py contract, a
malformed date counts as eligible (it's been long enough that no
specific date is meaningful). Implemented here in Python rather than
a single SQL UPDATE because the malformed-date branch can't be
expressed with the SQLite `date()` builtin without a complex
fallback.

Stdout on success: a single JSON object summarising the pass:
    {"promoted": <int>, "ids": ["amazon-...", ...]}

Stderr on per-row issues (e.g. unparseable last_updated): warn line
prefixed with the order id, and the row is skipped.

Exit codes: 0 success, 1 IO/schema error.
"""

from __future__ import annotations

import json
import os
import sqlite3
import sys
from datetime import date, datetime, timezone

DB_PATH = os.environ.get("ORDERS_DB_PATH", "/workspace/store/messages.db")
DAYS_THRESHOLD = 10

# Age past which an `ordered` row auto-resolves to assumed_delivered on
# order_date alone (`#61`). Aligned with compute-stuck-orders.py's
# STUCK_ORDER_MAX_DAYS — the stuck window's upper bound — so a row leaves
# the stuck pool and lands in a resolved status in the same pass.
STALE_ORDERED_CEILING_DAYS = 90


def _expected_delivery_eligible(value) -> bool:
    """True when the value should count as overdue per Step 7.

    Defensive against non-string types (SQLite is type-permissive — a
    hand-edited row with an integer/numeric value would otherwise crash
    `.strip()` with AttributeError and abort the whole pass). NULL,
    empty strings, and non-string values all return False (ineligible)
    so the caller treats them like a missing expected_delivery.
    """
    if not isinstance(value, str):
        return False
    stripped = value.strip()
    if not stripped:
        return False
    # Try parsing as ISO date (first 10 chars). Mirrors within-days.py.
    try:
        parsed = date.fromisoformat(stripped[:10])
    except ValueError:
        # Malformed date string ("overnight", "soon", etc.) — per the
        # original SKILL.md Step 7, these count as eligible because
        # it's been long enough that no specific date is meaningful.
        # The other two conditions (status + last_updated) still
        # gate, so this isn't a blanket promotion.
        return True
    return (date.today() - parsed).days >= DAYS_THRESHOLD


def _aged_ordered_ceiling(status, order_date) -> bool:
    """True when an `ordered` row is past STALE_ORDERED_CEILING_DAYS.

    The age-ceiling promotion path (`#61`): resolves an order that never
    matched a shipment/delivery email (so its expected_delivery is NULL
    and the expected-delivery path never fires) once it is old enough
    that it has certainly arrived or died. Only `ordered` rows qualify —
    a `shipped` row goes through the expected-delivery path or waits.

    Type-guards against SQLite's permissiveness (same rationale as
    `_expected_delivery_eligible`): a non-string or malformed order_date
    is ineligible rather than a crash — the row is left for a later pass
    once its data is sane.
    """
    if status != "ordered":
        return False
    if not isinstance(order_date, str) or not order_date.strip():
        return False
    try:
        parsed = date.fromisoformat(order_date.strip()[:10])
    except ValueError:
        return False
    return (date.today() - parsed).days > STALE_ORDERED_CEILING_DAYS


def _last_updated_eligible(value, order_id: str) -> bool:
    """True when last_updated is >= DAYS_THRESHOLD days ago.

    Defensive against NULL/non-string values: legacy rows pre-dating the
    NOT NULL constraint could still surface here if the schema is ever
    relaxed, and a hand-edited row with an integer/numeric column would
    otherwise crash `value.replace(...)` with AttributeError before the
    ValueError handler could catch it. Treat anything other than a
    non-empty string as "ineligible" — same outcome as a malformed
    ISO date — and skip with a diagnostic.
    """
    if not isinstance(value, str) or not value.strip():
        sys.stderr.write(
            f"promote-stale-shipped: order {order_id} has missing or "
            f"non-string last_updated={value!r}; skipping. To make this "
            f"row eligible for auto-promotion, set its last_updated to "
            f"an ISO-8601 timestamp at least 10 days in the past — "
            f"promotion requires the row to be stale, not fresh.\n"
        )
        return False
    try:
        # last_updated is ISO-8601 with timezone (e.g. "2026-04-02T00:00:00.000Z"
        # or "2026-04-02T00:00:00.000+00:00"). datetime.fromisoformat in 3.11+
        # accepts "Z" directly; for older, normalise.
        normalized = value.replace("Z", "+00:00")
        parsed = datetime.fromisoformat(normalized)
    except ValueError:
        sys.stderr.write(
            f"promote-stale-shipped: order {order_id} has unparseable "
            f"last_updated={value!r}; skipping\n"
        )
        return False
    if parsed.tzinfo is None:
        parsed = parsed.replace(tzinfo=timezone.utc)
    delta = datetime.now(timezone.utc) - parsed
    return delta.days >= DAYS_THRESHOLD


def main() -> int:
    now_iso = datetime.now(timezone.utc).isoformat()
    promoted_ids: list[str] = []
    conn = None
    try:
        conn = sqlite3.connect(DB_PATH)
        cur = conn.execute(
            """
            SELECT id, status, order_date, expected_delivery, last_updated
              FROM orders
             WHERE status IN ('shipped', 'ordered')
            """,
        )
        candidates = cur.fetchall()
        for order_id, status, order_date, expected_delivery, last_updated in candidates:
            # Age-ceiling path first — it stands on order_date alone and
            # does not consult last_updated, so a never-matched order with
            # no fresh emails still resolves.
            aged_out = _aged_ordered_ceiling(status, order_date)
            if not aged_out:
                if not _expected_delivery_eligible(expected_delivery):
                    continue
                if not _last_updated_eligible(last_updated, order_id):
                    continue
            conn.execute(
                """
                UPDATE orders
                   SET status = 'assumed_delivered',
                       flagged = 0,
                       flag_reason = NULL,
                       last_updated = ?
                 WHERE id = ?
                """,
                (now_iso, order_id),
            )
            promoted_ids.append(order_id)
        conn.commit()
        json.dump({"promoted": len(promoted_ids), "ids": promoted_ids}, sys.stdout)
        sys.stdout.write("\n")
        return 0
    except sqlite3.Error as exc:
        sys.stderr.write(
            f"promote-stale-shipped: SQLite error against {DB_PATH}: "
            f"{exc}. Verify the database file exists, is writable, and "
            f"the orders table is present (created by the orchestrator's "
            f"state-001 migration).\n"
        )
        return 1
    finally:
        if conn is not None:
            conn.close()


if __name__ == "__main__":
    sys.exit(main())