DROP VIEW IF EXISTS akuz.v_grouped_bill_of_goods_with_price;
CREATE VIEW akuz.v_grouped_bill_of_goods_with_price AS
SELECT
    gen_random_uuid()  AS groupedbillofgoodswithprice,
    bg.ctrn            AS ctrn,
    trn.cmnn           AS cmnn,
    bg.medicationforms AS medicationforms,
    dv.value           AS doseview,
    bg.isvital         AS isvital,
    MAX(ce.price)      AS price,
    t.lpumain          AS lpu
FROM stor.t_contract_elements ce
JOIN pharm.t_bill_of_goods bg ON bg.billofgoodsid = ce.billofgoods
JOIN pharm.t_c_trn trn ON trn.ctrnid = bg.ctrn
LEFT JOIN pharm.t_c_mnn mnn ON mnn.cmnnid = trn.cmnn
LEFT JOIN pharm.s_dlo_doseview dv ON dv.id = bg.doseview
LEFT JOIN LATERAL (
    SELECT
        lpu.lpumain,
        cst.contractstoragelinkid
    FROM stor.t_contract_storage_link cst
    JOIN stor.t_supplier_contract suppliercontract
    ON cst.suppliercontract = suppliercontract.suppliercontractid
    JOIN stor.t_storage s ON s.storageid = cst.storage
    JOIN akuz.t_lpu_issue_place lpu ON lpu.issueplaceid = s.issueplace
    WHERE
          cst.suppliercontract = ce.suppliercontract
      AND suppliercontract.type_ =
          (
              SELECT
                  entityid
              FROM vclib.t_entities
              WHERE
                    entname = 'SUPPLIER_CONTRACT'
                AND schema = 'STOR'
              LIMIT 1
          )
    LIMIT 1
    ) t ON TRUE
WHERE
     t.lpumain IS NOT NULL
  OR t.contractstoragelinkid IS NULL
GROUP BY
    bg.ctrn,
    trn.cmnn,
    bg.medicationforms,
    dv.value,
    bg.isvital,
    t.lpumain;
