DROP PROCEDURE IF EXISTS akuz.sp_create_storage_invoice_from_ttmf;

-- =============================================
-- Author:		Anatoliy Pokus
-- Create date: 2026-03-27
-- Description:	Создание накладной прихода из накладной ТТМФ
-- =============================================

CREATE OR REPLACE PROCEDURE akuz.sp_create_storage_invoice_from_ttmf(
	 "Json" text
	,"Storage" uuid
	,"InvoiceId" uuid
    ,"Mark" boolean
    ,"User" uuid
    ,"ResponsiblePerson" uuid)
LANGUAGE plpgsql
AS $$
Declare
    v_entity_invoice uuid;
    v_entity_item uuid;
    v_error boolean;
BEGIN

    SELECT EntityID INTO v_entity_invoice 
    FROM VCLib.T_ENTITIES 
    WHERE EntName = 'STORAGE_INVOICE_ADD' -- Оставлено как в оригинале, хотя логичнее STORAGE_INVOICE
    LIMIT 1;

    SELECT EntityID INTO v_entity_item 
    FROM VCLib.T_ENTITIES 
    WHERE EntName = 'INVOICE_ITEM'
    LIMIT 1;

    
	DROP TABLE IF EXISTS t_ttn;
    -- Создаем временные таблицы
    CREATE TEMP TABLE t_ttn (
        doc_id BIGINT,
        doc_num text,
        doc_date timestamp,
        doc_shipment_date timestamp,
        post_name text,
        post_id INTEGER,
        gruzo_name text,
        sender_name text,
        sender_inn text,
        store_name text,
        ttn_sum_with_nds numeric(18,2),
        ttn_sum_without_nds numeric(18,2),
        nds_sum numeric(18,2),
        dogovor_num text,
        dogovor_date timestamp,
        zayav_name text,
        is_mark boolean,
        mark_document_id uuid,
        mark_address_id text,
        supplyer_type_id INTEGER,
        markingShchema INTEGER
    );
    
	DROP TABLE IF EXISTS t_specs;
    CREATE TEMP TABLE t_specs (
        doc_id BIGINT,
        prep_id BIGINT,
        prep_name text,
        producer_name text,
        trade_name text,
        seria text,
        sgod timestamp,
        quant numeric(18,3),
        quant_in_pack INTEGER,
        nds_value INTEGER,
        price_with_nds numeric(18,2),
        sum_with_nds numeric(18,2),
        is_jnv boolean,
        supplier_pvs_id BIGINT
    );
    
	DROP TABLE IF EXISTS t_marks;
    CREATE TEMP TABLE t_marks (
        doc_id BIGINT,
        prep_id BIGINT,
        pvs_id BIGINT,
        pvs_quant numeric(18,3),
        pvs_sgtin text
    );

-- Загрузка заголовка документа
    INSERT INTO t_ttn (
        docId, docNum, docDate, docShipmentDate, postName, postId,
        gruzoName, senderName, senderInn, storeName,
        ttnSumWithNds, ttnSumWithoutNds, ndsSum,
        dogovorNum, dogovorDate, zayavName, isMark,
        markDocumentId, markAddressId, supplyerTypeId,markingShchema
    )
    SELECT 
        ("Json"->>'docId')::BIGINT,
        "Json"->>'docNum',
        ("Json"->>'docDate')::timestamp,
        ("Json"->>'docShipmentDate')::timestamp,
        "Json"->>'postName',
        ("Json"->>'postId')::INTEGER,
        "Json"->>'gruzoName',
        "Json"->>'senderName',
        "Json"->>'senderInn',
        "Json"->>'storeName',
        ("Json"->>'ttnSumWithNds')::numeric(18,2),
        ("Json"->>'ttnSumWithoutNds')::numeric(18,2),
        ("Json"->>'ndsSum')::numeric(18,2),
        "Json"->>'dogovorNum',
        ("Json"->>'dogovorDate')::timestamp,
        "Json"->>'zayavName',
        CASE WHEN "Json"->>'isMark' IN ('1', 'true', 'True') THEN true ELSE false END,
        ("Json"->>'markDocumentId')::uuid,
        "Json"->>'markAddressId',
        ("Json"->>'supplyerTypeId')::INTEGER,
        ("Json"->>'$.markingShchema')::INTEGER;


    -- Загрузка спецификаций
    INSERT INTO t_specs (
        docId, prepId, prepName, producerName, tradeName, seria,
        sgod, quant, quantInPack, ndsValue, priceWithNds,
        sumWithNds, isJnv, supplierPvsId
    )
    SELECT 
        ("Json"->>'docId')::BIGINT,
        (elem->>'prepId')::BIGINT,
        elem->>'prepName',
        elem->>'producerName',
        elem->>'tradeName',
        elem->>'seria',
        (elem->>'sgod')::timestamp,
        (elem->>'quant')::numeric(18,3),
        (elem->>'quantInPack')::INTEGER,
        (elem->>'ndsValue')::INTEGER,
        (elem->>'priceWithNds')::numeric(18,2),
        (elem->>'sumWithNds')::numeric(18,2),
        CASE WHEN elem->>'isJnv' IN ('1', 'true', 'True') THEN true ELSE false END,
        (elem->>'supplierPvsId')::BIGINT
    FROM jsonb_array_elements("Json"->'specs') AS elem;

    if(Mark = TRUE)
    THEN
        -- Загрузка маркировки
        INSERT INTO t_marks (
            docId, prepId, pvsId, pvsQuant, pvsSgtin
        )
        SELECT 
            ("Json"->>'docId')::BIGINT,
            (spec_elem->>'prepId')::BIGINT,
            (mark_elem->>'pvsId')::BIGINT,
            1, -- Сразу устанавливаем 1, как в оригинале после update
            mark_elem->>'pvsSgtin'
        FROM jsonb_array_elements("Json"->'specs') AS spec_elem
        CROSS JOIN LATERAL jsonb_array_elements(spec_elem->'marks') AS mark_elem;

        update t_marks set pvsQuant = 1;
    end IF;

    if(exists(select * from t_ttn ttn
                where not exists(select 1
                                    from STOR.T_SUPPLIER_CONTRACT sc
                                        left join STOR.T_SUPPLIER_CONTRACT_ABR abr on abr.Abr = sc.SupplierContractAbr
                                    where sc.Number = ttn.dogovorNum and sc.Date = ttn.dogovorDate)))
    then
        v_error := true;
		RAISE EXCEPTION 'Не удалось найти договор!';
        return;
    end if;
    
    if(exists(select * from t_specs s
                where not exists(select 1
                                    from PHARM.T_BILL_OF_GOODS bg
                                    where bg.Active = 1 and bg.OtherID = s.prepId
                                    )))
    then
        v_error := true;
		RAISE EXCEPTION 'Не удалось найти препараты в справочнике!';
        return;
    end if;

    --SELECT * FROM t_ttn;
    
    --SELECT * FROM t_specs;
    
    --SELECT * FROM t_marks;
    if(v_error = false)
    then
        INSERT INTO STOR.T_STORAGE_INVOICE
	       (InvoiceID
	       ,Number
	       ,Date
	       ,State
	       ,Storage
	       ,Creator
	       ,CreateDate
	       ,HoldingUser
	       ,HoldingDate
	       ,ResponsiblePerson
	       ,PaymentType
	       ,InventoryDocument
	       ,type_
	       ,Comment
	       ,Loaded
	       ,SupplierContract
	       ,MarkSchemes
	       ,ShipmentDate
	       ,ShipmentDocumentNumber
	       ,StorageApplication)
	select "InvoiceId"
	       ,ttn.docNum
	       ,ttn.docDate
	       ,0
	       ,"Storage"
	       ,"User"
	       ,CURRENT_TIMESTAMP
	       ,null
	       ,null
	       ,"ResponsiblePerson"
	       ,sc.PaymentType
	       ,null
	       ,v_entity_invoice
	       ,null
	       ,true
	       ,sc.SupplierContractID
	       ,(case when ttn.markingShchema = 415 then 601 else null end)
	       ,ttn.docShipmentDate
	       ,ttn.docNum
	       ,null
    from t_ttn ttn
            LEFT JOIN LATERAL(
                    select sc.SupplierContractID, abr.PaymentType
                    from STOR.T_SUPPLIER_CONTRACT sc
                        left join STOR.T_SUPPLIER_CONTRACT_ABR abr on abr.Abr = sc.SupplierContractAbr
                    where sc.Number = ttn.dogovorNum and sc.Date = ttn.dogovorDate
                    limit 1
            ) sc ON TRUE;

	    INSERT INTO STOR.T_STORAGE_INVOICE_ADD
	           (InvoiceWriteOffBetweenStorage
	           ,InvoiceID
	           ,Sender)
	select  null
	       ,"InvoiceId"
	       ,null
    from t_ttn t;

        INSERT INTO STOR.T_INVOICE_ITEM
               (StorageInvoice
               ,Remains
               ,ElementID
               ,SupplierContract
               ,PrepareRemains
               ,StorageInvoiceAdd
               ,SupplierContractAbr
               ,ProducerPrice
               ,InvoiceItem
               ,MarkCheck
               ,ScanerCheck
               ,Barcode
               ,SSCC
               ,QuantityInPackage
               ,Amount
               ,LotNumber
               ,Series
               ,Price
               ,ExpirationDate
               ,Comment
               ,TotalApplicationCount
               ,GTINSerialNumber
               ,BillOfGoods
               ,PackageType
               ,TotalPackageType
               ,VAT
               ,OrdinalNumber
               ,ConversionId
               ,type_
               ,SellingPrice
               ,StorageShelf)
         select "InvoiceId"
               ,null
               ,gen_random_uuid()
               ,si.SupplierContract
               ,null
               ,"InvoiceId"
               ,sc.SupplierContractAbr
               ,null
               ,null
               ,0
               ,0
               ,null
               ,null
               ,COALESCE(tot.QuantityInPackage,1)
               ,COALESCE(m.pvsQuant, s.quant)
               ,0
               ,s.seria
               ,s.priceWithNds
               ,s.sgod
               ,null
               ,COALESCE(s.quant * COALESCE(tot.QuantityInPackage,1), s.quant)
               ,COALESCE(m.pvsSgtin, null)
               ,bg.BillOfGoodsID
               ,bg.UnitPrice
               ,bg.WriteOff
               ,(case   when s.ndsValue = 10 then 1
                        when s.ndsValue = 20 then 3
                        when s.ndsValue = 22 then 6
                        else 0 end)
               ,ROW_NUMBER() over(order by s.docid)
               ,null
               ,v_entity_item
               ,0
               ,null
        from t_specs s
            join STOR.T_STORAGE_INVOICE si on si.InvoiceID = "InvoiceId"
            left join STOR.T_SUPPLIER_CONTRACT sc on sc.SupplierContractID = si.SupplierContract
            left join t_marks m on m.prepId = s.prepId
            LEFT JOIN LATERAL(
                    select bg.BillOfGoodsID, pfU.PackageType as UnitPrice, pfU.OrdinalNumber as UnitPriceON, pfW.PackageType as WriteOff, pfW.OrdinalNumber as WriteOffON
                from PHARM.T_BILL_OF_GOODS bg
                    join PHARM.T_PRODUCT_FORM pfU on pfU.BillOfGoods = bg.BillOfGoodsID and pfU.UnitPrice = true
                    join PHARM.T_PRODUCT_FORM pfW on pfW.BillOfGoods = bg.BillOfGoodsID and pfW.WriteOff = true
                where bg.Active = true and bg.OtherID = s.prepId
                    limit 1
                ) bg  ON TRUE
                LEFT JOIN LATERAL(
                select EXP(SUM(LN(ABS(pf.Amount)))) as QuantityInPackage
                from PHARM.T_PRODUCT_FORM pf 
                where pf.BillOfGoods = bg.BillOfGoodsID
                and pf.OrdinalNumber >= bg.WriteOffON and pf.OrdinalNumber < bg.UnitPriceON
                group by pf.BillOfGoods
            ) tot  ON TRUE;
    end if;
END;
$$;