/*
Read-only diagnostic for fee quantity consistency.

Business rule:
  For every fee except Per packing, fee.quantity must equal the sum of the
  applicable net/gross quantities of its effective lots.
  The diagnostic is grouped by fee_id. Purchase/sale references are only
  displayed to help identify the contracts involved; they must not split the
  expected quantity.

Effective lots:
  - physical lots if at least one physical lot is linked to the fee;
  - otherwise virtual lots.

Quantity state:
  - fee.qt_state when filled;
  - otherwise the purchase/sale weight basis linked to the fee or lot;
  - capped at the weight basis when fee.qt_state is later by sequence;
  - fallback to the closest previous lot.qt.hist state by sequence.
*/

WITH linked_lots AS (
    SELECT
        f.id AS fee_id,
        f.mode,
        f.quantity AS fee_quantity,
        f.unit AS fee_unit,
        f.qt_state AS fee_qt_state,
        f.weight_type,
        f.line AS fee_purchase_line_id,
        f.sale_line AS fee_sale_line_id,
        f.shipment_in,
        fl.lot AS lot_id,
        l.lot_type,
        l.lot_state,
        l.lot_unit_line,
        l.line AS lot_purchase_line_id,
        l.sale_line AS lot_sale_line_id
    FROM fee_fee f
    JOIN fee_lots fl ON fl.fee = f.id
    JOIN lot_lot l ON l.id = fl.lot
    WHERE f.mode IS DISTINCT FROM 'ppack'
),
ranked_lots AS (
    SELECT
        ll.*,
        bool_or(ll.lot_type = 'physic') OVER (PARTITION BY ll.fee_id)
            AS has_physical_lot
    FROM linked_lots ll
),
effective_lots AS (
    SELECT *
    FROM ranked_lots
    WHERE
        (has_physical_lot AND lot_type = 'physic')
        OR (NOT has_physical_lot AND lot_type = 'virtual')
),
fee_context AS (
    SELECT
        el.*,
        COALESCE(fpl.purchase, lpl.purchase) AS purchase_id,
        COALESCE(fsl.sale, lsl.sale) AS sale_id,
        pp.number AS purchase_number,
        ss.number AS sale_number,
        COALESCE(fpl.unit, fsl.unit, lpl.unit, lsl.unit) AS fallback_unit,
        COALESCE(fpwb.qt_type, fswb.qt_type, lpwb.qt_type, lswb.qt_type)
            AS weight_basis_qt_state
    FROM effective_lots el
    LEFT JOIN purchase_line fpl ON fpl.id = el.fee_purchase_line_id
    LEFT JOIN sale_line fsl ON fsl.id = el.fee_sale_line_id
    LEFT JOIN purchase_line lpl ON lpl.id = el.lot_purchase_line_id
    LEFT JOIN sale_line lsl ON lsl.id = el.lot_sale_line_id
    LEFT JOIN purchase_purchase pp ON pp.id = COALESCE(fpl.purchase, lpl.purchase)
    LEFT JOIN sale_sale ss ON ss.id = COALESCE(fsl.sale, lsl.sale)
    LEFT JOIN purchase_weight_basis fpwb ON fpwb.id = (
        SELECT wb FROM purchase_purchase WHERE id = fpl.purchase)
    LEFT JOIN purchase_weight_basis fswb ON fswb.id = (
        SELECT wb FROM sale_sale WHERE id = fsl.sale)
    LEFT JOIN purchase_weight_basis lpwb ON lpwb.id = pp.wb
    LEFT JOIN purchase_weight_basis lswb ON lswb.id = ss.wb
),
target_state AS (
    SELECT
        fc.*,
        CASE
            WHEN fc.fee_qt_state IS NULL THEN fc.weight_basis_qt_state
            WHEN fc.weight_basis_qt_state IS NULL THEN fc.fee_qt_state
            WHEN fqts.sequence IS NOT NULL
                AND wbqts.sequence IS NOT NULL
                AND fqts.sequence > wbqts.sequence
                THEN fc.weight_basis_qt_state
            ELSE fc.fee_qt_state
        END AS target_qt_state
    FROM fee_context fc
    LEFT JOIN lot_qt_type fqts ON fqts.id = fc.fee_qt_state
    LEFT JOIN lot_qt_type wbqts ON wbqts.id = fc.weight_basis_qt_state
),
selected_quantities AS (
    SELECT
        ts.*,
        qh.quantity_type AS selected_qt_state,
        qh.quantity AS selected_net_quantity,
        qh.gross_quantity AS selected_gross_quantity,
        lu.category AS lot_unit_category,
        tu.category AS fee_unit_category,
        lu.factor AS lot_unit_factor,
        tu.factor AS fee_unit_factor
    FROM target_state ts
    LEFT JOIN product_uom lu ON lu.id = ts.lot_unit_line
    LEFT JOIN product_uom tu ON tu.id = COALESCE(ts.fee_unit, ts.fallback_unit)
    LEFT JOIN LATERAL (
        SELECT
            hist.quantity_type,
            hist.quantity,
            hist.gross_quantity
        FROM lot_qt_hist hist
        LEFT JOIN lot_qt_type hist_type ON hist_type.id = hist.quantity_type
        LEFT JOIN lot_qt_type target_type ON target_type.id = ts.target_qt_state
        WHERE
            hist.lot = ts.lot_id
            AND (
                (ts.target_qt_state IS NULL AND hist.quantity_type = ts.lot_state)
                OR (ts.target_qt_state IS NOT NULL AND (
                    hist.quantity_type = ts.target_qt_state
                    OR (
                        hist_type.sequence IS NOT NULL
                        AND target_type.sequence IS NOT NULL
                        AND hist_type.sequence <= target_type.sequence)))
            )
        ORDER BY
            CASE WHEN hist.quantity_type = ts.target_qt_state THEN 0 ELSE 1 END,
            hist_type.sequence DESC NULLS LAST
        LIMIT 1
    ) qh ON TRUE
),
computed_lot_quantities AS (
    SELECT
        *,
        CASE
            WHEN selected_qt_state IS NULL THEN NULL
            WHEN lot_unit_category IS NULL OR fee_unit_category IS NULL THEN NULL
            WHEN lot_unit_category <> fee_unit_category THEN NULL
            WHEN fee_unit_factor IS NULL OR fee_unit_factor = 0 THEN NULL
            WHEN weight_type = 'net'
                THEN selected_net_quantity * lot_unit_factor / fee_unit_factor
            ELSE selected_gross_quantity * lot_unit_factor / fee_unit_factor
        END AS computed_quantity
    FROM selected_quantities
),
computed_fees AS (
    SELECT
        fee_id,
        mode,
        fee_quantity,
        CASE
            WHEN count(DISTINCT purchase_id) FILTER (WHERE purchase_id IS NOT NULL) = 1
                THEN min(purchase_id)
            ELSE NULL
        END AS purchase_id,
        string_agg(DISTINCT purchase_number, ', ')
            FILTER (WHERE purchase_number IS NOT NULL) AS purchase_number,
        CASE
            WHEN count(DISTINCT sale_id) FILTER (WHERE sale_id IS NOT NULL) = 1
                THEN min(sale_id)
            ELSE NULL
        END AS sale_id,
        string_agg(DISTINCT sale_number, ', ')
            FILTER (WHERE sale_number IS NOT NULL) AS sale_number,
        bool_or(selected_qt_state IS NULL) AS has_missing_state,
        bool_or(
            lot_unit_category IS NULL
            OR fee_unit_category IS NULL
            OR lot_unit_category <> fee_unit_category
            OR fee_unit_factor IS NULL
            OR fee_unit_factor = 0) AS has_uom_issue,
        round(sum(computed_quantity)::numeric, 5) AS expected_quantity
    FROM computed_lot_quantities
    GROUP BY
        fee_id, mode, fee_quantity
)
SELECT
    'fee_quantity_mismatch' AS check_name,
    fee_id,
    purchase_id,
    purchase_number,
    sale_id,
    sale_number,
    round(COALESCE(fee_quantity, 0)::numeric, 5) AS observed_value,
    expected_quantity AS expected_value,
    round(COALESCE(fee_quantity, 0)::numeric - expected_quantity, 5) AS diff,
    'fee.quantity must equal the sum of effective fee.lots quantities' AS detail
FROM computed_fees
WHERE
    NOT has_missing_state
    AND NOT has_uom_issue
    AND round(COALESCE(fee_quantity, 0)::numeric, 5) <> expected_quantity

UNION ALL

SELECT
    'fee_quantity_missing_state' AS check_name,
    fee_id,
    purchase_id,
    purchase_number,
    sale_id,
    sale_number,
    round(COALESCE(fee_quantity, 0)::numeric, 5) AS observed_value,
    NULL::numeric AS expected_value,
    NULL::numeric AS diff,
    'no usable lot.qt.hist state was found for at least one effective fee lot' AS detail
FROM computed_fees
WHERE has_missing_state

UNION ALL

SELECT
    'fee_quantity_uom_manual_review' AS check_name,
    fee_id,
    purchase_id,
    purchase_number,
    sale_id,
    sale_number,
    round(COALESCE(fee_quantity, 0)::numeric, 5) AS observed_value,
    NULL::numeric AS expected_value,
    NULL::numeric AS diff,
    'UoM conversion cannot be inferred safely by SQL for at least one lot' AS detail
FROM computed_fees
WHERE has_uom_issue

ORDER BY check_name, fee_id;
