database/migrations/000.541.sql

-- View: public.v_funnel_order_rows

DROP VIEW public.v_funnel_order_rows;

CREATE OR REPLACE VIEW public.v_funnel_order_rows
 AS
 WITH discounts AS (
         SELECT s.id,
            s.order_id,
            s.discount,
            s.discount_cc,
            s.discount_code,
            s.discount_sale
           FROM ( SELECT rows.id,
                    rows.order_id,
                        CASE
                            WHEN rows.sub_type IN ('contract_customer_discount', 'discount_code') THEN 0
                            WHEN rows.type = 'refund' THEN
                                COALESCE(abs(
                                      rows.price
                                    / (SELECT price FROM order_rows WHERE id = (SELECT rows.data->>'refunded_order_row_id')::int)
                                )
                                * (rows.data ->> 'discount_amount'::text)::numeric, 0)
                            ELSE 0 - COALESCE((rows.data ->> 'discount_amount'::text)::numeric, 0::numeric)
                        END AS discount,

                        CASE
                            WHEN rows.sub_type IN ('contract_customer_discount', 'discount_code') THEN 0
                            WHEN EXISTS (
                                SELECT 1
                                  FROM order_rows s
                                 WHERE s.order_id = rows.order_id
                                   AND s.sub_type = 'contract_customer_discount'
                        ) THEN
                            CASE
                                WHEN rows.type = 'refund' THEN
                                    coalesce(abs(
                                          rows.price
                                        / (SELECT price FROM order_rows WHERE id = (SELECT rows.data->>'refunded_order_row_id')::int)
                                    )
                                    * (rows.data ->> 'discount_amount'::text)::numeric, 0)
                                ELSE 0 - COALESCE((rows.data ->> 'discount_amount'::text)::numeric, 0::numeric)
                            END
                          ELSE 0
                        END AS discount_cc,

                        CASE
                            WHEN rows.sub_type IN ('contract_customer_discount', 'discount_code') THEN 0
                            WHEN EXISTS (
                                SELECT 1
                                  FROM order_rows s
                                 WHERE s.order_id = rows.order_id
                                   AND s.sub_type = 'discount_code'
                            ) THEN
                                CASE
                                    WHEN rows.type = 'refund' THEN
                                        COALESCE(abs(
                                              rows.price
                                            / (SELECT price FROM order_rows WHERE id = (SELECT rows.data->>'refunded_order_row_id')::int)
                                        )
                                        * (rows.data ->> 'discount_amount'::text)::numeric, 0::numeric)
                                    ELSE 0 - COALESCE((rows.data ->> 'discount_amount'::text)::numeric, 0::numeric)
                                END
                            ELSE 0
                        END AS discount_code,

                        CASE
                            WHEN rows.type = 'refund'::order_row_type THEN 0::numeric
                            ELSE 0 - abs(COALESCE(( SELECT abs(rows.price) - (((rows.data -> 'applied_sale'::text) ->> 'original_unit_price_excl_vat'::text)::numeric)), 0::numeric))
                        END AS discount_sale
                   FROM order_rows rows) s
        )
 SELECT r.id,
    r.order_id,
    r.inserted,
    r.type,
    o.customer_type,
    o.market,
    pp.path,
    pp.publishing_date,
    pp.ref1,
    pp.ref2,
    COALESCE(((r.data ->> 'width_mm'::text)::numeric)::integer / 10, (r.data ->> 'width'::text)::integer) AS width,
    COALESCE(((r.data ->> 'height_mm'::text)::numeric)::integer / 10, (r.data ->> 'height'::text)::integer) AS height,
    r.sub_type,
    r.status,
    r.price,
    r.commission_amount,
    r.external_status,
    r.data ->> 'printId'::text AS print_id,
    COALESCE(r.data ->> 'paintId'::text, r.data ->> 'productId'::text) AS product_id,
    r.data ->> 'material'::text AS material,
    r.data ->> 'artNo'::text AS artno,
    r.data ->> 'designer'::text AS designer,
    (r.data ->> 'inquiryid'::text)::integer AS inquiry_id,
    d.discount,
    d.discount_cc,
    d.discount_code,
    d.discount_sale,
        CASE r.type
            WHEN 'refund'::order_row_type THEN NULL::text
            ELSE r.data ->> 'segment'::text
        END AS segment,
        CASE r.type
            WHEN 'refund'::order_row_type THEN NULL::numeric
            ELSE (r.data ->> 'segment_price'::text)::numeric
        END AS segment_price,
    r.data ->> 'reprinted_order_row_id'::text AS reprinted_order_row_id,
        CASE
            WHEN r.sub_type = ANY (ARRAY['wall_paint'::order_row_sub_type, 'paint_card_sample'::order_row_sub_type]) THEN r.data ->> 'name'::text
            ELSE NULL
        END AS paint_name,
    r.data ->> 'paintLiters'::text AS paint_liters,
    r.data ->> 'paintColor'::text AS paint_ncs
   FROM order_rows r
     JOIN orders o ON r.order_id = o.id
     LEFT JOIN "product-products" pp ON ((r.data ->> 'productId'::text)::integer) = pp.productid
     LEFT JOIN discounts d ON r.id = d.id;

GRANT SELECT ON TABLE public.v_funnel_order_rows TO funnel;