Order-Manager/README.md

# Order Manager

A PyQt6 desktop app for the daily order cycle: pull today's tickets
from JIRA (8AM-3:30PM intake), then at end-of-day pull ShipStation
tracking numbers and see, at a glance, what's fulfilled, what's
cancelled, and what's still open from a previous day (carryover).
Odoo integration is file-based for now (CSV export) since the live
Odoo side is still in development.

One design note up front: **every order is one row, sourced from
JIRA.** ShipStation only exists here to generate shipping labels for
JIRA tickets (order/shipment numbers there are the same as the JIRA
ticket number), so it never creates its own rows - "Pull Tracking
Numbers" just merges tracking numbers onto the matching JIRA ticket.
There's no "source" filter because there's effectively one source.

## Setup

1. `pip install -r requirements.txt`
2. Run it once: `python main.py` (this creates `.env` from `.env.example`)
3. Click **Settings** and fill in:
   - **JIRA**: Site URL, Email, API Token, JQL (defaults to tickets
     created today - adjust to match your project/label). The
     deliverable field IDs are already defaulted from your export
     (`customfield_10573,customfield_10570` for Signify;
     `customfield_12790,customfield_13021` for Oak Street).
   - **ShipStation**: just the API Key.
   - **Companies**: SKU prefix -> company mapping (defaults to
     `SH:Signify Health,OK:Oak Street Health`).
   - **Status Tracking**: which statuses count as cancelled/fulfilled -
     defaults already match what you described (see below).
4. Click **Import from JIRA** during/after the intake window.
5. Click **Pull Tracking Numbers (ShipStation)** at end-of-day - it
   merges tracking numbers onto the matching tickets, no separate rows.
6. Check the **Dashboard** tab for Fulfilled / Cancelled / Carryover /
   Tracking Received counts.
7. Use **All Orders** to filter by company or search (ticket #, SKU,
   status, or tracking #), and **Export Visible Orders to Odoo CSV**
   for whatever's currently filtered.

Orders are cached locally in SQLite (`orders.db`). Re-importing updates
existing tickets rather than duplicating them.

## Three tabs: Active, Cancelled, Done

The tab a ticket shows up on is decided by an **allowlist**, not a list
of every "finished" status:

- **Active Orders**: status is in `ACTIVE_STATUSES` (default just
  `Created`) - the only tickets that represent real work still to do.
- **Cancelled**: status is in `CANCELLED_STATUSES` (default `Cancelled`)
  **and it was cancelled today**. A ticket cancelled on a prior day
  falls through to Done instead - this tab is meant to be reviewed
  same-day and then filed away, not accumulate forever. Any transition
  into Cancelled also triggers a **popup** right after the import that
  caused it. Cancelled rows show in **red text** (rather than a
  background tint) wherever they appear, including if one later ages
  out into Done - it's still useful to see at a glance that a Done-tab
  row got there via cancellation rather than fulfillment.
- **Done**: everything else, automatically. This is deliberate - rather
  than maintaining a list of every status that means "finished"
  (`Waiting For Return`, `Device Return Not Needed`, and whatever your
  JIRA automation adds next, like `1st Contact Attempt`), only the
  couple of statuses that mean "not done yet" (or "cancelled today")
  are named. Anything else lands on Done with zero config changes
  needed when your workflow adds another downstream status later.

This is a filter, not a physical move - it's the same `orders` table,
split by current status (and, for Cancelled, `cancelled_at`) every time
the view refreshes, so nothing to reconcile if a status ever changes
back.

Within Done, the two fulfilled statuses (`FULFILLED_STATUS_WITH_RETURN`
/ `FULFILLED_STATUS_WITHOUT_RETURN`, defaulting to `Waiting For Return`
/ `Device Return Not Needed`) still get **highlighted green** and drive
the "Fulfilled Today" dashboard count and `fulfilled_at` timestamp -
they're the two statuses this app actually knows the meaning of, versus
other done-statuses (like `1st Contact Attempt`) which land on Done but
aren't otherwise tracked, per "it is considered done...unnecessary to
be tracked for us."

**Carryover**: tickets that didn't get closed out same-day don't need
anything special - since `ACTIVE_STATUSES` defaults to just `Created`,
every JIRA import re-checks any ticket still in that status regardless
of when it was created, so it stays "alive" and its eventual status
change gets picked up whenever it happens. The Dashboard's Carryover
count is Active tickets created before today, plus today's tickets that
arrived past the cutoff (see below) - both cases are known not to get
done today, just at different points in the day.

## Intake cutoff tracking

`INTAKE_CUTOFF_TIME` (default `15:30`, i.e. 3:30 PM) flags any ticket
created after that time on its arrival day with a checkmark in the
**Past Cutoff** column, and the Dashboard shows how many of today's
tickets arrived past cutoff. This flag expires at midnight - it means
"came in too late for today's window," not a permanent marker, so a
ticket that arrived late yesterday shows no checkmark today (it's
Carryover now, a different, already-tracked concept). While it's still
showing, it also counts toward **Carryover** immediately - not just
once the date actually rolls over - unless it still gets fulfilled the
same day despite arriving late, in which case it correctly drops out
of both.

## Table layout

Left to right: Company, Ticket #, Status, SKUs, Summary, Outgoing
Tracking, Return Tracking, Uploaded, Past Cutoff, Created. Columns
auto-size to their content on load and stay individually resizable by
dragging (not force-stretched to equal widths) - the last column
stretches to fill any remaining space. **Uploaded** shows a checkmark
once a ticket has been successfully sent via the emergency "Send to
ShipStation" action's **API** path specifically - the CSV export path
intentionally doesn't set this, since exporting a file isn't the same
as it actually being imported into ShipStation, and the app has no way
to confirm that manual step happened.

## How tracking numbers get pulled and matched

Confirmed against a real ShipStation payload from your queue (not
guessed): a shipment's `shipment_number` and `external_shipment_id`
both hold the ticket number directly (e.g. both were `"AR-160269"`).
"Pull Tracking Numbers":

1. Bulk-fetches **all** of today's labels and all shipments from the
   last `SHIPSTATION_LOOKBACK_DAYS` (a code constant in
   `shipstation_service.py`, default 7 days - wide enough to catch a
   shipment that sat "pending" for a day or two before its label was
   generated). This replaced an earlier version that looked up each
   shipment individually (one HTTP call per ticket) - the actual cause
   of the slowness, now down to a handful of bulk list calls.
2. Fetches those (and any additional pages either needs) **concurrently**
   via a PyQt `QThreadPool` - several requests in flight at once instead
   of one after another. Capped at `MAX_CONCURRENT_REQUESTS` (default 5)
   to stay well clear of any ShipStation rate limit.
3. Only after both full batches have landed does it sort/correlate
   labels to shipments to ticket numbers, entirely in memory - reading
   `tracking_number` and `is_return_label` straight off each label, and
   the ticket number off `shipment_number` (checked first) or
   `external_shipment_id`, falling back to scanning the whole payload
   with `TICKET_NUMBER_REGEX` if neither is present.

If a label's ticket number doesn't match any ticket you have locally
(e.g. JIRA hasn't been imported yet, or it's an account outlier),
you'll get a popup listing which ones didn't match, rather than the
data silently vanishing.

**Known gap, deferred on purpose:** there's a SKU for emailed labels
that doesn't go through this shipment/label flow at all - those
tickets just won't have tracking numbers pulled, which is expected for
now, not a bug. Flag it when you're ready to handle it.

## Packing: serial numbers and marking Done

Reflects your actual workflow: staff enter serial numbers (mostly via
**barcode scanner**) and mark a ticket packed as devices go into the
box - this is the beginning of what eventually becomes the Ship Sheet,
built directly into the app instead of a separate spreadsheet.

Select a ticket and click **Pack Ticket**:

- **Suggested fields, not a fixed schema.** Device types and their
  field sets vary a lot by company and by kit (confirmed against your
  real Ship Sheet - Oak Street tracks OptiPlex/Laptop/Phone, Signify
  tracks iPad/Spiro/Laptop/Phone x2), so hard-coding columns per device
  type would fight the "versatile" requirement. Instead, fields are
  suggested from keywords in the ticket's line items
  (`DEVICE_FIELD_SUGGESTIONS`) - a starting point staff can always
  extend with a custom field. A "Shipping - Return Label X" line item
  is deliberately excluded from matching (tested against this - it
  describes a label deliverable, not an actual second device).
- **Built for the scanner, not around it.** Each field's Enter key
  (which a scanner sends automatically after scanning) jumps focus to
  the next field - scan straight through a device list with zero mouse
  clicks. After the last field, focus lands on the **Packed** checkbox
  rather than auto-submitting, so finishing still takes one deliberate
  action.
- **Reopening a partially-packed ticket** preserves whatever was
  already scanned and still shows the current suggestions for what's
  left - tested explicitly.
- This data is **staff-entered, not sourced from JIRA**, and a JIRA
  re-import never touches it - confirmed the update path doesn't
  reference `serial_numbers`/`packed` at all. It's exactly the data
  the eventual end-of-day JIRA push will need, whenever that gets
  built.

**Assignee** (who's working the ticket, from JIRA) and **Shipping
Method** (the outbound label's service, from the ShipStation tracking
pull - `ups_ground` -> "Ground", etc. via `SHIPPING_METHOD_LABELS`,
confirmed against ShipStation's real UPS service codes) are also
pulled in now, matching two more Ship Sheet columns.

**One consequence worth knowing:** since packing data doesn't come
from JIRA, **Reset Local Database now also clears it** - the warning
dialog says so explicitly. Before this update, a reset was always
harmless (just a rebuildable cache); now it isn't, for this one kind
of data.

## Emergency: Send to ShipStation

For the rare case a ticket needs to skip the normal daily batch. Select
a ticket in **Active Orders**, click **Send to ShipStation
(Emergency)** in the toolbar, review the address/SKU summary shown,
then pick one:

- **Send via API Now** - calls ShipStation's API directly
  (`POST /v2/shipments` with `create_sales_order: true`) to create the
  order without leaving the app. Needs `SHIPSTATION_SIGNIFY_STORE_ID` /
  `SHIPSTATION_OAKSTREET_STORE_ID` set in Settings.
- **Export CSV Row...** - writes a single-ticket CSV matching your real
  upload template exactly (verified column-for-column against a real
  export), for manual upload if the API is down.

Both exist on purpose, per "in case the API goes down we still have an
option for CSV uploads."

**Please verify your first live send, either path.** ShipStation's own
docs say automation rules apply tags to orders "when they import based
on any criteria you set" - meaning your 90 box-packing rules should
fire automatically off the SKU/item data, same as a normal CSV import,
with no manual tagging needed from this app. That's the best read of
their documentation, but it's not something testable without your real
account - check that ShipStation packed and priced an emergency-sent
order the way a normal one would before relying on this in an actual
emergency.

One thing worth knowing: **Quantity** is always sent as `1` per line
item today, since JIRA doesn't currently give a per-deliverable
quantity. Say the word if that's ever not right.

## Keeping .env in sync as new settings get added

`.env` is gitignored on purpose (it holds real credentials and field
IDs), which means pulling new code never updates it automatically -
new settings would otherwise sit silently blank until someone noticed
a feature wasn't working (this is what caused an early version of the
emergency-send feature to have no address data - the field IDs existed
in `.env.example` but never made it into the real `.env`). Every
startup now calls `config.sync_env_with_example()`, which adds any key
present in `.env.example` but missing from `.env`, using the example's
value as the default - without touching anything you've already set.
Existing tickets in the local database still need a fresh **Import
from JIRA** to pick up newly-added fields, though, since extraction
only runs when a ticket is actually re-fetched.

## How company/SKU extraction works

Each company has its own pair of "deliverable" custom fields in JIRA
(e.g. Signify's `Deliverables` + `Hardware Needed`; Oak Street's own
versions), configurable via `JIRA_SIGNIFY_SKU_FIELDS` /
`JIRA_OAKSTREET_SKU_FIELDS`. Each deliverable value looks like `SH011:
Shipping - Return Label iPad - Physical in Box` - the app pulls out
just the `SH011` code as the SKU and keeps the description for the
summary. Company is resolved from the SKU prefix (`SH`/`OK`) via
`COMPANY_SKU_MAP`. Since these tickets' actual JIRA "Summary" field is
usually blank, the app falls back to the joined deliverable
descriptions for the Summary column when there's nothing else there.

## Resetting the local database

**Reset Local Database** in the toolbar clears every cached order (with
a confirmation first). This is always safe - JIRA is the real source of
truth, this is just a rebuildable cache - but it's worth knowing when
you'd actually need it: if a ticket transitioned status under an older,
buggy version of this app, its `fulfilled_at`/`cancelled_at` timestamp
can get stuck wrong, since those are only recalculated **at the moment
of a transition** - re-importing an already-transitioned ticket finds
"no status change" and never touches that timestamp again. A reset
clears the stale value entirely; the next Import from JIRA then stamps
everything correctly from scratch. Follow a reset with **Import from
JIRA**, and **Pull Tracking Numbers** if you rely on today's
already-pulled tracking data.

## Moving storage to your MariaDB LXC later

In Settings (or directly in `.env`), change:

```
DB_URL=mysql+pymysql://user:password@<lxc-ip>:3306/order_manager
```

and `pip install pymysql`. No code changes needed. Note: this is a
local cache rebuilt from JIRA/ShipStation, so if you change `DB_URL`
or the schema changes in a future update, it's safe to just delete
`orders.db` and re-import rather than migrate it.

## Project layout

```
main.py                        entry point
app/
  config.py                    reads/writes .env, defines the settings schema
  database.py                  SQLAlchemy engine/session (SQLite now, MariaDB later)
  models.py                    Order table - one row per JIRA ticket
  companies.py                 SKU-prefix -> company resolution
  status_rules.py              status-list parsing/matching, cancellation detection
  tracking.py                  tracking numbers -> suggested JIRA status
  queries.py                   "what tickets are still open" - used for JIRA re-checking
  workers.py                   background thread(s), save/enrich logic, dashboard stats
  services/
    base.py                    OrderService interface - implement this for new sources
    jira_service.py            JIRA REST API -> NormalizedOrder (SKU fields, company, re-check)
    shipstation_service.py     ShipStation V2 labels+shipments -> tracking numbers by ticket
    odoo_export.py             CSV export for the (in-development) Odoo import template
    __init__.py                SERVICE_REGISTRY - register new sources here
  ui/
    main_window.py             tabs, toolbar, import/export/notification wiring
    settings_dialog.py         auto-built from config.SETTINGS_SCHEMA
    widgets/
      dashboard.py             summary stat cards
      orders_table.py          sortable/filterable Qt table for orders
      order_detail_dialog.py   raw payload + tracking numbers + suggested status, per ticket
```

## Adding real Odoo API access next

1. Add settings to `app/config.py` -> `SETTINGS_SCHEMA` (Odoo group).
2. Create `app/services/odoo_service.py`. If it's push-based (you're
   sending orders to Odoo, most likely given the workflow), it doesn't
   need to subclass `OrderService` - a `push_orders(orders)` method is
   fine, called from a new toolbar action the same way
   `_on_export_clicked` calls `export_orders_to_csv`.
3. Wire it up the same way the JIRA/ShipStation buttons are.

## Emailed return labels (SH007 / OK012)

A different workflow entirely from the normal outbound-kit tickets: a
customer already has product to return, and needs a return label
emailed to them. Select a ticket in Active Orders and click **Create
Return Label**:

- Shows the ticket's **Description** (parsed from JIRA's rich-text
  format into plain text) - this is where staff note what boxes are
  needed, so it's visible without opening JIRA.
- Lets you specify **any number of packages**, each with its own
  weight and dimensions - replacing the old fixed "1x1x1, 1oz dummy
  ticket" with real per-request control.
- Calls ShipStation directly (`POST /v2/labels` with
  `is_return_label: true`). Confirmed against ShipStation's own
  return-label docs: **`ship_from` is the customer and `ship_to` is
  your warehouse** - reversed from every other label this app creates,
  and easy to get backwards, so this was tested explicitly.
- `charge_event` is configurable per your risk preference:
  `on_creation` (pay immediately), `on_carrier_acceptance` (only pay if
  the customer actually ships it - needs the carrier to enable this on
  your account first, can take 3-4 weeks), or `carrier_default`.

**This app does not email the label.** Per your workflow, that
happens from ShipStation itself (Returns tab -> Other Actions -> Send
Return Label) so it goes out through your branded template - something
this app couldn't replicate anyway, since ShipStation doesn't expose
that step through its API as far as I could find. After a label is
created, the ticket's **Uploaded** checkmark is set (same flag the
emergency-send feature uses) so you can see at a glance which
return-label tickets have already had their label created.

Both companies share one physical warehouse but each has its **own
UPS account** (`SIGNIFY_RETURN_CARRIER_ID` / `OAKSTREET_RETURN_CARRIER_ID`)
- the return address is the same, just filed under the right company
name. Occasional shipments to company HQ instead of the shared
warehouse aren't handled yet - flagged for later, per your note.

**Still an assumption pending confirmation:** the service code
(`SIGNIFY_RETURN_SERVICE_CODE` / `OAKSTREET_RETURN_SERVICE_CODE`)
defaults to `ups_ground` since you gave me the carrier/account but not
a specific service level - change it in Settings if that's not right.

## Known open item

You mentioned occasionally shipping return items to a company's
headquarters instead of the shared warehouse - noted, not built yet
since you said we could tackle it later. When you're ready, this would
likely be a dropdown in the Return Label dialog (Warehouse vs. HQ)
rather than a new settings group, since the workflow is otherwise
identical.