truncate table [tenant]_mod_organizations_storage.organizations cascade;
truncate table [tenant]_mod_orders_storage.purchase_order cascade;
truncate table [tenant]_mod_invoice_storage.invoices cascade;

CREATE OR REPLACE FUNCTION public.generate_0062_data_for_edifact_export(
scenario_prefix TEXT,
organizations_amount INTEGER,
orders_per_vendor INTEGER,
polines_per_order INTEGER,
pieces_per_order INTEGER
) RETURNS VOID AS
$$
DECLARE
orgName TEXT DEFAULT 'perf_test_vendor';
orgCodePrefix TEXT DEFAULT 'PERF_TEST_ORG';
vendor_id TEXT;
BEGIN
FOR org_counter IN 1..organizations_amount
LOOP
INSERT INTO [tenant]_mod_organizations_storage.organizations (id, jsonb)
VALUES (
public.uuid_generate_v4(),
jsonb_build_object(
'code', concat(orgCodePrefix, '_', scenario_prefix, '_', org_counter),
'erpCode', '12345',
'isVendor', true,
'name', concat(orgCodePrefix, '_', scenario_prefix, '_', org_counter),
'status', 'Active',
'metadata', jsonb_build_object(
'createdDate', '2023-02-08T00:00:00.000+0000',
'createdByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42',
'updatedDate', '2023-02-08T00:00:00.000+0000',
'updatedByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42'
)
)
)
RETURNING id INTO vendor_id;

PERFORM public.generate_0062_orders(orders_per_vendor, polines_per_order, pieces_per_order, vendor_id,1,2);
END LOOP;
END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.generate_0062_orders(
orders_per_vendor INTEGER,
polines_per_order INTEGER,
pieces_per_order INTEGER,
vendor_id TEXT,
invoices_per_organization INTEGER,
invoicelines_per_invoice INTEGER
) RETURNS VOID AS
$$
DECLARE
order_id TEXT;
poline_id TEXT;
newPoNumber INTEGER;
BEGIN
FOR order_counter IN 1..orders_per_vendor
LOOP
SELECT nextval('[tenant]_mod_orders_storage.po_number') INTO newPoNumber;

INSERT INTO [tenant]_mod_orders_storage.purchase_order (id, jsonb)
VALUES (
public.uuid_generate_v4(),
jsonb_build_object(
'id', public.uuid_generate_v4(),
'notes', jsonb_build_array(),
'acqUnitIds', jsonb_build_array(),
'approved', true,
'reEncumber', true,
'nextPolNumber', 2,
'workflowStatus', 'Pending',
'poNumber', newPoNumber,
'vendor', vendor_id,
'orderType', 'One-Time',
'metadata', jsonb_build_object(
'createdDate', '2024-10-06T00:00:00.000+0000',
'createdByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42',
'updatedDate', '2024-10-06T00:00:00.000+0000',
'updatedByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42'
)
)
)
RETURNING id INTO order_id;

PERFORM public.generate_0062_polines(order_id, polines_per_order, pieces_per_order, newPoNumber, vendor_id);
PERFORM public.generate_0062_invoices(invoices_per_organization, invoicelines_per_invoice, vendor_id, poline_id);
END LOOP;
END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.generate_0062_polines(
order_id TEXT,
polines_per_order INTEGER,
pieces_per_order INTEGER,
ponumber INTEGER,
vendor_id TEXT
) RETURNS VOID AS
$$
DECLARE
polineNumber TEXT;
holding_id UUID;
currPoNumber INTEGER;
iteration_counter INT := 1;

BEGIN
SELECT id INTO holding_id
FROM [tenant]_mod_inventory_storage.holdings_record 
WHERE permanentlocationid = '9ffb23a9-0e83-4d8e-a70d-ddf44ab67c84' 
AND instanceid = 'b94d2138-bdfc-5dea-bab9-ac99e21b7970'
LIMIT 1;

FOR line_counter IN 1..polines_per_order
LOOP
SELECT currval('[tenant]_mod_orders_storage.po_number') INTO currPoNumber;

INSERT INTO [tenant]_mod_orders_storage.po_line (id, jsonb)
VALUES (
public.uuid_generate_v4(),
jsonb_build_object(
'id', public.uuid_generate_v4(),
'cost', jsonb_build_object(
'currency', 'USD',
'discountType', 'percentage',
'quantityElectronic', 2,
'poLineEstimatedPrice', 4,
'listUnitPriceElectronic', 2
),
'rush', false,
'alerts', jsonb_build_array(),
'claims', jsonb_build_array(),
'source', 'User',
'details', jsonb_build_object(
'productIds', jsonb_build_array(
jsonb_build_object(
'productId', '73121721',
'productIdType', 'c858e4f2-2b6b-4385-842b-60732ee14abb'
)
),
'isAcknowledged', false
),
'edition', '',
'metadata', jsonb_build_object(
'createdDate', '2024-10-06T11:09:59.181Z',
'updatedDate', '2024-10-06T11:10:48.652Z',
'createdByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42',
'updatedByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42'
),
'eresource', jsonb_build_object(
'trial', false,
'activated', false,
'accessProvider', 'c88742c3-dc2f-4589-a7cf-a90694c2a296',
'createInventory', 'Instance, Holding'
),
'isPackage', false,
'locations', jsonb_build_array(
jsonb_build_object(
'quantity', 2,
'locationId', '9ffb23a9-0e83-4d8e-a70d-ddf44ab67c84',
'quantityElectronic', 2
)
),
'publisher', 'Praeger Publishers',
'collection', false,
'instanceId', 'b94d2138-bdfc-5dea-bab9-ac99e21b7970',
'orderFormat', 'Electronic Resource',
'checkinItems', false,
'contributors', jsonb_build_array(
jsonb_build_object(
'contributor', 'Yurchenco, Henrietta',
'contributorNameTypeId', '2b94c631-fca9-4892-a730-03ee529ffe2a'
)
),
'poLineNumber', currPoNumber || '-' || iteration_counter,
'vendorDetail', jsonb_build_object(
'instructions', '',
'vendorAccount', '',
'referenceNumbers', jsonb_build_array()
),
'paymentStatus', 'Pending',
'receiptStatus', 'Pending',
'claimingActive', false,
'reportingCodes', jsonb_build_array(),
'titleOrPackage', '!Hablamos! Puerto Ricans speak / Photos. by Julia Singer.',
'automaticExport', false,
'publicationDate', '[1971]',
'purchaseOrderId', order_id,
'fundDistribution', jsonb_build_array(
jsonb_build_object(
'code', 'FYND',
'value', 100,
'fundId', '8003d427-34a3-4ad4-97cd-c1aae19f2bfc',
'distributionType', 'percentage'
)
),
'acquisitionMethod', '796596c4-62b5-4b64-a2ce-524c747afaa2',
'searchLocationIds', jsonb_build_array(
'9ffb23a9-0e83-4d8e-a70d-ddf44ab67c84'
),
'donorOrganizationIds', jsonb_build_array(),
'checkinItems', true
)
)
RETURNING id INTO polineNumber;
iteration_counter := iteration_counter + 1;

-- Call the function to generate data for titles table
PERFORM public.generate_0062_titles_data(polineNumber, concat(ponumber, '-', line_counter), pieces_per_order);
END LOOP;
END;
$$ LANGUAGE plpgsql;



CREATE OR REPLACE FUNCTION public.generate_0062_titles_data(
po_line_id TEXT,
po_line_number TEXT,
pieces_per_order INTEGER
) RETURNS VOID AS
$$
DECLARE
title_id UUID;
BEGIN
-- Insert data into titles table
INSERT INTO [tenant]_mod_orders_storage.titles (id, jsonb, creation_date, created_by, polineid)
VALUES (
public.uuid_generate_v4(),
jsonb_build_object(
'id', '4945c26f-6cf7-4050-93c1-0a8a25a2f4d7',
'title', '!Hablamos! Puerto Ricans speak / Photos. by Julia Singer.',
'edition', '',
'metadata', jsonb_build_object(
'createdDate', '2024-10-06T11:09:59.247Z',
'updatedDate', '2024-10-06T11:09:59.247Z',
'createdByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42',
'updatedByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42'
),
'poLineId', po_line_id,
'publisher', 'Praeger Publishers',
'acqUnitIds', jsonb_build_array(),
'instanceId', 'b94d2138-bdfc-5dea-bab9-ac99e21b7970',
'productIds', jsonb_build_array(
jsonb_build_object(
'productId', '73121721',
'productIdType', 'c858e4f2-2b6b-4385-842b-60732ee14abb'
)
),
'contributors', jsonb_build_array(
jsonb_build_object(
'contributor', 'Yurchenco, Henrietta',
'contributorNameTypeId', '2b94c631-fca9-4892-a730-03ee529ffe2a'
)
),
'poLineNumber', po_line_number,
'publishedDate', '[1971]',
'claimingActive', false,
'isAcknowledged', false
),
current_timestamp, -- Assuming creation date is the current timestamp
'created_by_user_id', -- Replace with the actual user ID
po_line_id::uuid
)
RETURNING id INTO title_id;

-- Call the function to generate data for pieces table
PERFORM public.generate_0062_pieces_data(title_id, po_line_id, pieces_per_order);
END
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION public.generate_0062_pieces_data(
title_id UUID,
polineNumber TEXT,
pieces_per_order INTEGER
) RETURNS VOID AS
$$
DECLARE
piece_counter INTEGER;
holding_id UUID;
BEGIN
SELECT id INTO holding_id
FROM [tenant]_mod_inventory_storage.holdings_record 
WHERE permanentlocationid = '9ffb23a9-0e83-4d8e-a70d-ddf44ab67c84' 
AND instanceid = 'b94d2138-bdfc-5dea-bab9-ac99e21b7970'
LIMIT 1;

FOR piece_counter IN 1..pieces_per_order
LOOP
-- Replace the following line with your actual poLineId
INSERT INTO [tenant]_mod_orders_storage.pieces VALUES (
public.uuid_generate_v4(),
jsonb_build_object(
'id', public.uuid_generate_v4(),
'format', 'Electronic',
'titleId', title_id,
'metadata', jsonb_build_object(
'createdDate', '2024-10-06T11:43:04.384Z',
'updatedDate', '2024-10-06T11:43:04.384Z',
'createdByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42',
'updatedByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42'
),
'poLineId', polineNumber,
'holdingId', 'aefd322b-9a32-4cd9-80e0-a5a3f882f2b1',
'copyNumber', piece_counter,
'supplement', true,
'displaySummary', piece_counter,
'receivingStatus', 'Expected',
'displayOnHolding', false,
'discoverySuppress', false,
'statusUpdatedDate', '2024-06-05T11:43:04.385+00:00'
),
NULL,
NULL,
'eb79cb72-0fb5-4773-a580-b1428fa724e9'::UUID, -- Replace with the actual poLineId
title_id
);
END LOOP;
END
$$ LANGUAGE plpgsql;






CREATE OR REPLACE FUNCTION public.generate_0062_invoices(
invoices_per_organization INTEGER,
invoicelines_per_invoice INTEGER,
vendor_id TEXT,
order_id TEXT
) RETURNS VOID AS
$$
DECLARE
invoice_id TEXT;
newInvoiceNumber INTEGER;
BEGIN
FOR invoice_counter IN 1..invoices_per_organization
LOOP
SELECT nextval('[tenant]_mod_invoice_storage.invoice_number') INTO newInvoiceNumber;

INSERT INTO [tenant]_mod_invoice_storage.invoices (id, jsonb)
VALUES (
public.uuid_generate_v4(),
jsonb_build_object(
'id', public.uuid_generate_v4(),
'total', 0.0,
'source', 'User',
'status', 'Approved',
'currency', 'USD',
'metadata', jsonb_build_object(
'createdDate', '2024-10-06T09:07:45.366Z',
'updatedDate', '2024-10-06T09:08:47.954Z',
'createdByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42',
'updatedByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42'
),
'subTotal', 0.0,
'vendorId', vendor_id,
'poNumbers', ARRAY[newInvoiceNumber]::TEXT[],
'acqUnitIds', ARRAY[]::UUID[],
'adjustments', ARRAY[]::JSONB[],
'invoiceDate', '2024-02-27T00:00:00.000+00:00',
'batchGroupId', '2a2cb998-1437-41d1-88ad-01930aaeadd5',
'fiscalYearId', '562bf586-11e9-4c36-98d7-3341042c856c',
'paymentMethod', 'Cash',
'accountingCode', '12345',
'folioInvoiceNo', newInvoiceNumber::TEXT,
'enclosureNeeded', false,
'vendorInvoiceNo', invoice_counter::TEXT,
'adjustmentsTotal', 0.0,
'exportToAccounting', false,
'nextInvoiceLineNumber', 2,
'chkSubscriptionOverlap', true
)
) RETURNING id INTO invoice_id;

PERFORM public.generate_0062_invoicelines(
invoicelines_per_invoice,
invoice_id,
newInvoiceNumber,
vendor_id,
order_id
);
END LOOP;
END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.generate_0062_invoicelines(
invoicelines_per_invoice INTEGER,
invoice_id TEXT,
newInvoiceNumber INTEGER,
vendor_id TEXT,
order_id TEXT
) RETURNS VOID AS
$$
DECLARE
invoiceline_id TEXT;
BEGIN
FOR invoiceline_counter IN 1..invoicelines_per_invoice
LOOP
INSERT INTO [tenant]_mod_invoice_storage.invoice_lines (id, jsonb)
VALUES (
public.uuid_generate_v4(),
jsonb_build_object(
'id', public.uuid_generate_v4(),
'total', 0.0,
'metadata', jsonb_build_object(
'createdDate', '2024-10-06T09:08:47.393Z',
'updatedDate', '2024-10-06T09:08:47.393Z',
'createdByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42',
'updatedByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42'
),
'poLineId', order_id,
'quantity', 2,
'subTotal', 0.0,
'invoiceId', invoice_id,
'adjustments', ARRAY[]::JSONB[],
'description', 'Its awesome if its working',
'accountNumber', 'libraryorders@library.tam',
'accountingCode', '12345',
'adjustmentsTotal', 0.0,
'referenceNumbers', ARRAY[]::JSONB[],
'fundDistributions', jsonb_build_array(
jsonb_build_object(
'code', 'FYND',
'value', 100.0,
'fundId', '8003d427-34a3-4ad4-97cd-c1aae19f2bfc',
'distributionType', 'percentage'
)
),
'invoiceLineNumber', invoiceline_counter,
'invoiceLineStatus', 'Approved',
'releaseEncumbrance', false
)
) RETURNING id INTO invoiceline_id;

-- You can perform additional actions if needed
END LOOP;
END
$$ LANGUAGE plpgsql;


-- CREATE sample data
-- 1 - amount of organizations to be created
-- 2 - amount of orders per organization
-- 3 - amount of polines per order
-- 4 - amount of pieces per order

SELECT public.generate_0062_data_for_edifact_export('FYNG',1,1,1,1);



-- CLEANUP

SELECT id as po_id, jsonb->>'vendor' as vendor_id, jsonb->>'poNumber' as po_number
select count(id)
FROM cs00000int_mod_orders_storage.purchase_order
where jsonb->>'workflowStatus'='Pending'

{"id":"${po_id}","approved":true,"notes":[],"poNumber":"${po_number}","orderType":"One-Time","reEncumber":true,"vendor":"${vendor_id}","workflowStatus":"Open","acqUnitIds":[],"needReEncumber":false}