#!/usr/bin/env python3
"""Upsert one order record into the `orders` table.

Stdin contract: a single JSON object with the parsed-email fields produced
by Step 4 of `check-orders/SKILL.md`, plus the pre-computed `id` (output
of `compute-order-id.py`). Required keys:

    id, source, status, description, order_date, email_message_id

Optional keys (default `None`/`null` when omitted):

    amount, currency, expected_delivery, to_address, merchant, order_number

`merchant` and `order_number` are free-text metadata the agent extracts in
Step 4 (`jbaruch/nanoclaw-orders#55`): `merchant` makes a flagged alert
identifiable, `order_number` is the structured key that pairs the
confirmation and shipment emails of one logical order. Both are normalized
to a stripped non-empty string or NULL and only set on INSERT (the
ON CONFLICT branch touches status/last_updated only).

Stdout on success: a single JSON object describing what happened —
`{"action": "inserted" | "status_updated" | "noop", "id": "..."}`. The
`status_updated` case fires when an existing row's status changed; `noop`
fires when the row already existed at the same status (idempotent re-run).

The UPSERT itself is parameter-bound via `sqlite3` so descriptions
containing apostrophes can't escape the string literal — the whole reason
this script exists rather than letting SKILL.md interpolate values into a
heredoc'd SQL statement (the JSON era used `merge-orders-db.py` for the
same reason).

Exit codes: 0 success, 1 schema/IO failure (diagnostic on stderr), 2
usage error (no JSON on stdin or missing required field).
"""

from __future__ import annotations

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

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

# Exact canonical calendar-date shape: four-digit year, zero-padded month
# and day. A fixed, fully-enumerable format, so a regex is the right guard
# (`jbaruch/coding-policy: script-delegation`). strptime then confirms the
# padded value is a real date (rejects 2026-13-40).
_CANONICAL_DATE_RE = re.compile(r"\d{4}-\d{2}-\d{2}")

REQUIRED_FIELDS = (
    "id",
    "source",
    "status",
    "description",
    "order_date",
    "email_message_id",
)


def _now_iso() -> str:
    return datetime.now(timezone.utc).isoformat()


def _optional_text(value):
    """Return a stripped non-empty string, or None.

    `merchant` and `order_number` are optional free-text metadata the
    agent extracts in Step 4 (`jbaruch/nanoclaw-orders#55`). A missing,
    non-string, or blank value is stored as NULL — indistinguishable from
    "not captured", which every reader already tolerates.
    """
    if isinstance(value, str) and value.strip():
        return value.strip()
    return None


def _normalize_expected_delivery(value, order_id: str):
    """Return a canonical `YYYY-MM-DD` `expected_delivery`, or None.

    The agent parses `expected_delivery` from free-text subjects in Step 4,
    which lets scraped non-dates ("today", "March", "overnight") through
    (`jbaruch/nanoclaw-orders#55`). The stored contract is a canonical
    calendar date: the ENTIRE stripped value must be exactly `YYYY-MM-DD`
    with zero-padded month and day. Timestamps, trailing garbage
    ("2026-04-05junk"), compact ("20260405"), non-padded ("2026-4-5"), and
    free text are all off-contract and dropped to NULL so they never feed
    the overdue check. Surrounding whitespace is stripped before the check.
    None passes through unchanged (no delivery mentioned).
    """
    if value is None:
        return None
    if isinstance(value, str):
        stripped = value.strip()
        if _CANONICAL_DATE_RE.fullmatch(stripped):
            try:
                datetime.strptime(stripped, "%Y-%m-%d")
                return stripped
            except ValueError:
                pass
    sys.stderr.write(
        f"apply-order.py: dropping non-date expected_delivery={value!r} for "
        f"order {order_id} — only a canonical YYYY-MM-DD date is stored so "
        f"the overdue check never keys off scraped free text\n"
    )
    return None


def main() -> int:
    raw = sys.stdin.read()
    if not raw.strip():
        sys.stderr.write("apply-order.py: no JSON on stdin\n")
        return 2
    try:
        order = json.loads(raw)
    except json.JSONDecodeError as exc:
        sys.stderr.write(f"apply-order.py: invalid JSON on stdin: {exc}\n")
        return 2
    if not isinstance(order, dict):
        sys.stderr.write("apply-order.py: stdin payload must be a JSON object\n")
        return 2
    missing = [k for k in REQUIRED_FIELDS if not order.get(k)]
    if missing:
        sys.stderr.write(f"apply-order.py: missing required field(s): {', '.join(missing)}\n")
        return 2

    last_updated = _now_iso()
    expected_delivery = _normalize_expected_delivery(order.get("expected_delivery"), order["id"])
    merchant = _optional_text(order.get("merchant"))
    order_number = _optional_text(order.get("order_number"))
    conn = None
    try:
        conn = sqlite3.connect(DB_PATH)
        # Resolve the PRE-existing status (if any) BEFORE the upsert, so the
        # action label we emit can distinguish "this row's status changed"
        # from "we already had this row at the same status". Doing this
        # post-upsert would mean re-reading the just-written row, which loses
        # the differentiation entirely.
        cur = conn.execute(
            "SELECT status FROM orders WHERE email_message_id = ?",
            (order["email_message_id"],),
        )
        prior = cur.fetchone()
        prior_status = prior[0] if prior else None

        conn.execute(
            """
            INSERT INTO orders (
              id, source, status, amount, currency, description, order_date,
              expected_delivery, email_message_id, to_address, flagged,
              flag_reason, last_updated, merchant, order_number
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, NULL, ?, ?, ?)
            ON CONFLICT(email_message_id) DO UPDATE SET
              status       = excluded.status,
              last_updated = excluded.last_updated
            WHERE orders.status != excluded.status
            """,
            (
                order["id"],
                order["source"],
                order["status"],
                order.get("amount"),
                order.get("currency"),
                order["description"],
                order["order_date"],
                expected_delivery,
                order["email_message_id"],
                order.get("to_address"),
                last_updated,
                merchant,
                order_number,
            ),
        )
        conn.commit()

        if prior is None:
            action = "inserted"
        elif prior_status != order["status"]:
            action = "status_updated"
        else:
            action = "noop"

        json.dump({"action": action, "id": order["id"]}, sys.stdout)
        sys.stdout.write("\n")
        return 0
    except sqlite3.Error as exc:
        sys.stderr.write(
            f"apply-order.py: SQLite error against {DB_PATH}: {exc}. "
            f"Verify the database file exists, is writable, and the "
            f"orders table is present (created by the orchestrator's "
            f"state-001 migration). If a row with the same `id` "
            f"({order['id']!r}) but a different email_message_id "
            f"already exists, this INSERT raises a PK collision; "
            f"that's an extremely-rare same-product-same-day case and "
            f"the operator should investigate the duplicate manually.\n"
        )
        return 1
    finally:
        if conn is not None:
            conn.close()


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