-- adempiere.v_orders_summary — order list view used by /sales/orders (ag-grid/summary),
-- orders/summary, orders/sales/summary and orders/[id].
--
-- 2026-10-27 rewrite: the five per-order aggregates (newest shipment, order lines, shipments,
-- invoices, fulfillment fees) were whole-table GROUP BY / window subqueries LEFT JOINed to c_order,
-- so Postgres materialised them for EVERY row of m_inout / c_orderline / c_invoice / ... on every page
-- (~1.3 s per 50-row page, 3.4 s per 200). They are now LEFT JOIN LATERAL subqueries evaluated per
-- order via the c_order_id indexes (~0.1 s per page). The SELECT list below is byte-identical to the
-- previous definition (same 86 columns, same order, same types) so CREATE OR REPLACE VIEW works and
-- iDempiere's AD_Table/REST need no change.
--
-- Rules: never add a whole-table GROUP BY / window subquery here again — add a LATERAL block.
-- Apply:   node reference/sql/apply-view.cjs [--dry-run]   (backs up first, verifies, commits)
-- Restore: node reference/sql/restore-view.cjs reference/sql/backups/<file>.sql
-- Previous definition: reference/sql/v_orders_summary.rollback.sql

CREATE OR REPLACE VIEW adempiere.v_orders_summary AS
SELECT ord.ad_client_id,
    ord.ad_org_id,
    ord.c_order_id,
    ord.created,
    ord.createdby,
    ord.updated,
    ord.updatedby,
    ord.isactive,
    ord.issotrx,
    ord.docstatus,
    ord.documentno,
    ord.description,
    ord.dateordered,
    ord.grandtotal,
    ord.priorityrule,
    ord.externalorderid,
    ord.shopify_order_id,
    ord.xentral_order_id,
    ord.shopware6_order_id,
    ord.amazon_order_id,
    ord.plentyone_order_id,
    ord.jtl_order_id,
    ord.temu_order_id,
    ord.ebay_order_id,
    ord.c_bpartner_id,
    ord.c_bpartner_location_id,
    ord.bill_bpartner_id,
    ord.bill_location_id,
    ord.m_warehouse_id,
    ord.c_doctype_id,
    ord.c_doctypetarget_id,
    ord.c_currency_id,
    ord.c_ordersource_id,
    bp.name AS partner_name,
    osrc.name AS order_source_name,
    osrc.description AS order_source_description,
    osrc.marketplace AS marketplace_key,
    arl.name AS marketplace_name,
    cur.iso_code AS currency_iso,
    wh.name AS warehouse_name,
    dt.name AS doctype_name,
        CASE
            WHEN ord.description IS NOT NULL AND length(TRIM(BOTH FROM ord.description)) > 0 THEN 'Y'::text
            ELSE 'N'::text
        END AS has_description,
    loc.c_location_id AS ship_location_id,
    bpl.name AS ship_name,
    bpl.name2 AS ship_name2,
    loc.address1 AS ship_address1,
    loc.address2 AS ship_address2,
    loc.city AS ship_city,
    loc.postal AS ship_postal,
    cou.name AS ship_country,
    cou.countrycode AS ship_countrycode,
    bplbill.name AS bill_name,
    bplbill.name2 AS bill_name2,
    locbill.address1 AS bill_address1,
    locbill.address2 AS bill_address2,
    locbill.city AS bill_city,
    locbill.postal AS bill_postal,
    coubill.name AS bill_country,
    coubill.countrycode AS bill_countrycode,
        CASE
            WHEN COALESCE(bpl.name, ''::character varying)::text <> COALESCE(bplbill.name, ''::character varying)::text OR COALESCE(bpl.name2, ''::character varying)::text <> COALESCE(bplbill.name2, ''::character varying)::text OR COALESCE(loc.address1, ''::character varying)::text <> COALESCE(locbill.address1, ''::character varying)::text OR COALESCE(loc.address2, ''::character varying)::text <> COALESCE(locbill.address2, ''::character varying)::text OR COALESCE(loc.city, ''::character varying)::text <> COALESCE(locbill.city, ''::character varying)::text OR COALESCE(loc.postal, ''::character varying)::text <> COALESCE(locbill.postal, ''::character varying)::text OR COALESCE(cou.name, ''::character varying)::text <> COALESCE(coubill.name, ''::character varying)::text THEN 'Y'::text
            ELSE 'N'::text
        END AS billing_differs_from_shipping,
    ls.m_inout_id AS shipment_id,
    ls.shipment_documentno,
    ls.shipment_docstatus,
    ls.trackingno,
    ls.iscommissioned,
    ls.iscommissionedconfirmed,
    ls.shipping_date,
    ls.shipment_weight,
    COALESCE(ls.m_shipper_id, ord.m_shipper_id) AS shipper_id,
    COALESCE(ls.shipper_name, ord_sh.name) AS shipper_name,
    COALESCE(ls.shipper_cfg, ord_shcfg.name) AS shipper_cfg,
    COALESCE(ls.is_attachment_required, ord_sh.isattachmentrequired) AS is_attachment_required,
    COALESCE(ls.is_print_dina4, ord_sh.isprintdina4) AS is_print_dina4,
    ls.paket_type_name,
    COALESCE(ol_agg.total_qty, 0::numeric) AS totalqty,
    COALESCE(ol_agg.total_positions, 0::bigint) AS totalpositions,
    COALESCE(ino_agg.total_shipped_qty, 0::numeric) AS totalshippedqty,
    COALESCE(ino_agg.shipment_count, 0::bigint) AS shipment_count,
    ino_agg.shipments_summary,
        CASE
            WHEN COALESCE(ino_agg.shipment_count, 0::bigint) > 0 THEN 'Y'::text
            ELSE 'N'::text
        END AS has_shipment,
        CASE
            WHEN COALESCE(inv_agg.invoice_count, 0::bigint) > 0 THEN 'Y'::text
            ELSE 'N'::text
        END AS has_invoice,
    inv_agg.invoices_summary,
    COALESCE(ff_agg.fulfillment_fee_total, 0::numeric) AS fulfillment_fee_total,
    COALESCE(ff_agg.fee_processed, 'N'::text) AS fee_processed,
    CURRENT_DATE - ord.created::date AS days_since_created,
        CASE
            WHEN abs(EXTRACT(epoch FROM ord.dateordered - ord.created)) < 3600::numeric THEN round(abs(EXTRACT(epoch FROM ord.dateordered - ord.created)) / 60::numeric)::integer || '
      minutes'::text
            WHEN abs(EXTRACT(epoch FROM ord.dateordered - ord.created)) < 86400::numeric THEN round(abs(EXTRACT(epoch FROM ord.dateordered - ord.created)) / 3600::numeric)::integer || '
  hours'::text
            ELSE round(abs(EXTRACT(epoch FROM ord.dateordered - ord.created)) / 86400::numeric)::integer || ' days'::text
        END AS created_to_ordered_age
   FROM c_order ord
     LEFT JOIN c_bpartner bp ON bp.c_bpartner_id = ord.c_bpartner_id
     LEFT JOIN c_bpartner_location bpl ON bpl.c_bpartner_location_id = ord.c_bpartner_location_id
     LEFT JOIN c_location loc ON loc.c_location_id = bpl.c_location_id
     LEFT JOIN c_country cou ON cou.c_country_id = loc.c_country_id
     LEFT JOIN c_bpartner_location bplbill ON bplbill.c_bpartner_location_id = ord.bill_location_id
     LEFT JOIN c_location locbill ON locbill.c_location_id = bplbill.c_location_id
     LEFT JOIN c_country coubill ON coubill.c_country_id = locbill.c_country_id
     LEFT JOIN c_ordersource osrc ON osrc.c_ordersource_id = ord.c_ordersource_id
     LEFT JOIN ad_ref_list arl ON arl.ad_reference_id = 1000003::numeric AND arl.value::text = osrc.marketplace::text
     LEFT JOIN c_currency cur ON cur.c_currency_id = ord.c_currency_id
     LEFT JOIN m_warehouse wh ON wh.m_warehouse_id = ord.m_warehouse_id
     LEFT JOIN c_doctype dt ON dt.c_doctype_id = ord.c_doctype_id
     LEFT JOIN m_shipper ord_sh ON ord_sh.m_shipper_id = ord.m_shipper_id
     LEFT JOIN m_shippercfg ord_shcfg ON ord_shcfg.m_shippercfg_id = ord_sh.m_shippercfg_id
     -- newest shipment of the order (was: row_number() OVER (PARTITION BY c_order_id) over ALL of m_inout)
     LEFT JOIN LATERAL ( SELECT ino.m_inout_id,
            ino.documentno AS shipment_documentno,
            ino.docstatus AS shipment_docstatus,
            ino.trackingno,
            ino.iscommissioned,
            ino.iscommissionedconfirmed,
            ino.shipping_date,
            ino.weight AS shipment_weight,
            sh.m_shipper_id,
            sh.name AS shipper_name,
            shcfg.name AS shipper_cfg,
            sh.isattachmentrequired AS is_attachment_required,
            sh.isprintdina4 AS is_print_dina4,
            pt.name AS paket_type_name
           FROM m_inout ino
             LEFT JOIN m_shipper sh ON sh.m_shipper_id = ino.m_shipper_id
             LEFT JOIN m_shippercfg shcfg ON shcfg.m_shippercfg_id = sh.m_shippercfg_id
             LEFT JOIN m_product pt ON pt.m_product_id = ino.m_paket_type_id
          WHERE ino.c_order_id = ord.c_order_id
          ORDER BY ino.m_inout_id DESC
          LIMIT 1) ls ON true
     -- order lines (was: GROUP BY over ALL of c_orderline)
     LEFT JOIN LATERAL ( SELECT sum(ol.qtyentered) AS total_qty,
            count(ol.c_orderline_id) AS total_positions
           FROM c_orderline ol
          WHERE ol.c_order_id = ord.c_order_id) ol_agg ON true
     -- shipments + shipped qty (was: GROUP BY over ALL of m_inout x m_inoutline)
     LEFT JOIN LATERAL ( SELECT sum(miol.movementqty) AS total_shipped_qty,
            count(DISTINCT ino.m_inout_id) AS shipment_count,
            string_agg(DISTINCT (ino.m_inout_id::text || ':'::text) || ino.documentno::text, ' | '::text) AS shipments_summary
           FROM m_inout ino
             LEFT JOIN m_inoutline miol ON miol.m_inout_id = ino.m_inout_id
          WHERE ino.c_order_id = ord.c_order_id) ino_agg ON true
     -- invoices (was: GROUP BY over ALL of c_invoice)
     LEFT JOIN LATERAL ( SELECT count(DISTINCT inv.c_invoice_id) AS invoice_count,
            string_agg(DISTINCT (inv.c_invoice_id::text || ':'::text) || inv.documentno::text, ' | '::text) AS invoices_summary
           FROM c_invoice inv
          WHERE inv.c_order_id = ord.c_order_id) inv_agg ON true
     -- fulfillment fees (was: GROUP BY over ALL of cust_fulfillmentfeeline)
     LEFT JOIN LATERAL ( SELECT sum(ffl.linetotalamt) AS fulfillment_fee_total,
                CASE
                    WHEN bool_and(ffl.a_processed = 'Y'::bpchar) THEN 'Y'::text
                    ELSE 'N'::text
                END AS fee_processed
           FROM cust_fulfillmentfeeline ffl
          WHERE ffl.c_order_id = ord.c_order_id) ff_agg ON true;