-- FUNCTION: internal.pnewdataimportid(bigint, character varying, uuid, numeric)

-- DROP FUNCTION IF EXISTS internal.pnewdataimportid(bigint, character varying, uuid, numeric);

CREATE OR REPLACE FUNCTION internal.pnewdataimportid(
	par_sourcedataimportid bigint,
	par_description character varying,
	par_executionid uuid DEFAULT NULL::uuid,
	par_noresults numeric DEFAULT 0)
    RETURNS TABLE(dataimportid bigint, description character varying) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
DECLARE
    var_TranCountOnEntry INTEGER
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the @@TRANCOUNT function. Create a user-defined function.]
    = @@TRANCOUNT
    */;
    var_DataImportID BIGINT;
    SCOPE_IDENTITY BIGINT;
	var_MaxDataImportID BIGINT;
	
	
BEGIN
    /*
    [7810 - Severity CRITICAL - PostgreSQL doesn't support the SET NOCOUNT. If need try another way to send message back to the client application.]
    SET NOCOUNT ON;
    */
    IF par_ExecutionID IS NULL THEN
        par_ExecutionID := aws_sqlserver_ext.newid();
    END IF;

    /*
    [7840 - Severity CRITICAL - DBCC migration is not currently supported. Perform a manual conversion.]
    DBCC CHECKIDENT ('internal.DataImport', reseed, 0) WITH NO_INFOMSGS;
    */
	
	PERFORM setval(pg_get_serial_sequence('internal.DataImport','dataimportid'), 1)
	from internal.DataImport;
	
    /*
    [7840 - Severity CRITICAL - DBCC migration is not currently supported. Perform a manual conversion.]
    DBCC CHECKIDENT ('internal.DataImport', reseed) WITH NO_INFOMSGS;
    */
 
	var_MaxDataImportID = (SELECT MAX(internal.DataImport.dataimportid) FROM internal.DataImport);
	PERFORM setval(pg_get_serial_sequence('internal.DataImport','dataimportid'), var_MaxDataImportID)
	from internal.DataImport;
RAISE NOTICE 'var_MaxDataImportID: %', var_MaxDataImportID;

    BEGIN
        IF var_TranCountOnEntry = 0 THEN
            /*
            [7807 - Severity CRITICAL - PostgreSQL does not support explicit transaction management in functions. Perform a manual conversion.]
            BEGIN TRAN;
            */
            BEGIN
            END;
        END IF;
		RAISE NOTICE 'par_sourcedataimportid: %', par_sourcedataimportid;
		RAISE NOTICE 'par_ExecutionID: %', par_ExecutionID;
		RAISE NOTICE 'par_Description: %',par_Description;
		
        INSERT INTO internal.dataimport (sourcedataimportid, description, dataimportstatus, createtime, starttime, completetime, message, executionid)
        SELECT
            par_sourcedataimportid,
            CASE
                WHEN par_Description IS NOT NULL THEN par_Description
-- 				ELSE 'No Description'
				ELSE COALESCE(didc.datasourcetype || ': ' || didc.datacontractdescription,'No Description')
            END, 
			'Started', CURRENT_TIMESTAMP(3), CURRENT_TIMESTAMP(3), NULL, '', par_ExecutionID
            FROM (SELECT
                1 AS ignore) AS x
            LEFT JOIN (SELECT DISTINCT (didc.dataimportid),
                didc.datasourcetype,didc.datacontractdescription
                FROM dbo.dataimportdatacontract_staging AS didc
                ) AS didc ON didc.dataimportid = par_SourceDataImportID
        RETURNING internal.dataimport.dataimportid into var_DataImportID;
--         var_DataImportID := SCOPE_IDENTITY;
RAISE NOTICE 'Test 1: %',var_DataImportID  ;

        IF par_NoResults = 0 THEN
            RETURN QUERY
            SELECT
                di.dataimportid, di.description
                FROM internal.dataimport di
                WHERE di.dataimportid = var_DataImportID;
RAISE NOTICE 'Test 2';				
        END IF;

        IF var_TranCountOnEntry = 0 THEN
            COMMIT;
        END IF;
        EXCEPTION
            WHEN OTHERS THEN
                IF var_TranCountOnEntry = 0 THEN
                    ROLLBACK;
                END IF;
                RAISE;
    END;
    -- return_code := 0;
    RETURN;
END;
$BODY$;

ALTER FUNCTION internal.pnewdataimportid(bigint, character varying, uuid, numeric)
    OWNER TO postgres;
