10. UI Statistics Schema Detail

flowchart LR
    subgraph STATS_SCHEMA["cw_200_stats schema"]
        direction TB
        subgraph DAILY["daily_ tables"]
            D1["daily_inventory_values"]
            D2["daily_sales_values"]
            D3["daily_pending_po_values"]
            D4["daily_expected_demand"]
        end
        subgraph WEEKLY["weekly_ tables"]
            W1["weekly_inventory_values"]
            W2["weekly_sales_values"]
            W3["weekly_pending_po_values"]
            W4["weekly_expected_demand"]
        end
        subgraph MONTHLY["monthly_ tables"]
            M1["monthly_inventory_values"]
            M2["monthly_sales_values"]
            M3["monthly_pending_po_values"]
            M4["monthly_expected_demand"]
        end
        subgraph QUARTERLY["quarterly_ tables"]
            Q1["quarterly_inventory_values"]
            Q2["quarterly_sales_values"]
            Q3["quarterly_pending_po_values"]
            Q4["quarterly_expected_demand"]
        end
    end

    subgraph COLS["Common columns"]
        direction TB
        C["location_id  (FK or NULL=global)
period_start  (date)
period_end    (date)
total_qty     (float)
total_value   (float)
product_count (int)"]
    end

    STATS_SCHEMA --- COLS

    style DAILY fill:#FFEBEE
    style WEEKLY fill:#FFF3E0
    style MONTHLY fill:#E8F5E9
    style QUARTERLY fill:#E3F2FD

16 tables in total (4 metrics x 4 granularities). Each is independently refreshable. Weekly/monthly/quarterly are derived from daily — can be recomputed at any time.

The _stats schema can be dropped and rebuilt without affecting the core cw_200 schema.

Convention — base currency: All monetary values in stats tables are stored in the client's base currency (defined in cw_global.client_config.base_currency_id). No per-row currency_id — currency conversion happens during Phase 2 aggregation.