-- 
-- Returns info about apps
-- db2 "connect to FEBDB user febdbuser]"
-- db2 -f "C:\temp\appDataStatsByRecordCount.sql" -td@
-- db2 -r "C:\temp\appDataStatsByRecordCount.out" call appDataStatsByRecordCount() 
--
CREATE OR REPLACE PROCEDURE appDataStatsByRecordCount()
	LANGUAGE SQL
  	DYNAMIC RESULT SETS 1
	BEGIN
		DECLARE GLOBAL TEMPORARY TABLE SESSION.tempAppDataStats 
		(
			APP_ID CHAR(36) NOT NULL,
			TITLE VARCHAR(250) NOT NULL,
			TABLENAME VARCHAR(128),
			FORM_ID CHAR(36) NOT NULL,
			FORM_TITLE VARCHAR(250) NOT NULL,
			CREATED TIMESTAMP NULL,
			UPDATED TIMESTAMP NULL,
			STATUS VARCHAR(20) NULL,
			OWNER_EMAIL VARCHAR(250) NULL,
			RECORD_COUNT BIGINT,
			ATTACHMENT_COUNT BIGINT,
			ATTACHMENTS_SIZE BIGINT,
			MAX_RECORD_CREATED TIMESTAMP NULL,
			MAX_RECORD_UPDATED TIMESTAMP NULL
		) WITH REPLACE;

		-- load our temp table with initial app info
		-- include the dynamic table name used in APP_DATA
		INSERT INTO SESSION.tempAppDataStats
			SELECT A.UUID, A.LABEL, TABLENAME, F.FORM_ID, F.ALIAS, A.CREATED, A.UPDATED, A.STATUS, US.EMAIL, 0, 0, 0, NULL, NULL FROM FREEDOM.APPLICATIONS A
			INNER JOIN FREEDOM.UIDS U ON A.UUID = U.APP_ID AND COLUMNNAME IS NULL 
			INNER JOIN FREEDOM.FORMS F ON U.UUID = F.FORM_ID
			INNER JOIN FREEDOM.USERS US ON A.OWNER_ID = US.USER_ID;			
			
		DELETE FROM SESSION.tempAppDataStats WHERE TABLENAME NOT IN (SELECT TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA = 'APP_DATA');	
		
		BEGIN	  		
			FOR dataTable AS dataTables CURSOR FOR
				SELECT APP_ID, TABLENAME, FORM_TITLE, FORM_ID FROM SESSION.tempAppDataStats
			DO
				-- loop over each record in our temp table and collect/update stats
				-- because the table name is dynamic, we need to execute dynamic sql
				EXECUTE IMMEDIATE 'UPDATE SESSION.tempAppDataStats SET RECORD_COUNT = (SELECT COUNT(1) FROM APP_DATA."' || dataTable.TABLENAME ||
				'") WHERE SESSION.tempAppDataStats.APP_ID = ''' || dataTable.APP_ID || ''' AND SESSION.tempAppDataStats.FORM_TITLE = ''' || dataTable.FORM_TITLE || '''';	
				
				EXECUTE IMMEDIATE 'UPDATE SESSION.tempAppDataStats SET ATTACHMENT_COUNT = (SELECT COUNT(1) FROM FREEDOM.ATTACHMENTS WHERE APPLICATION_UID = ''' || dataTable.APP_ID ||
				''' AND FORM_UID = ''' || dataTable.FORM_ID || ''') WHERE SESSION.tempAppDataStats.APP_ID = ''' || dataTable.APP_ID || 
				''' AND SESSION.tempAppDataStats.FORM_TITLE = ''' || dataTable.FORM_TITLE || '''';
				
				EXECUTE IMMEDIATE 'UPDATE SESSION.tempAppDataStats SET ATTACHMENTS_SIZE = (SELECT (SUM(LENGTH(ATT.CONTENT)) / (1024*1024)) FROM FREEDOM.ATTACHMENTS AS ATT WHERE APPLICATION_UID = ''' || dataTable.APP_ID ||
				''' AND FORM_UID = ''' || dataTable.FORM_ID || ''') WHERE SESSION.tempAppDataStats.APP_ID = ''' || dataTable.APP_ID || 
				''' AND SESSION.tempAppDataStats.FORM_TITLE = ''' || dataTable.FORM_TITLE || '''';
				
				EXECUTE IMMEDIATE 'UPDATE SESSION.tempAppDataStats SET MAX_RECORD_CREATED = (SELECT MAX(CREATION_TIME) FROM APP_DATA."' || dataTable.TABLENAME ||
				'") WHERE SESSION.tempAppDataStats.APP_ID = ''' || dataTable.APP_ID || ''' AND SESSION.tempAppDataStats.FORM_TITLE = ''' || dataTable.FORM_TITLE || '''';			
				
				EXECUTE IMMEDIATE 'UPDATE SESSION.tempAppDataStats SET MAX_RECORD_UPDATED = (SELECT MAX(UPDATED) FROM APP_DATA."' || dataTable.TABLENAME ||
				'") WHERE SESSION.tempAppDataStats.APP_ID = ''' || dataTable.APP_ID || ''' AND SESSION.tempAppDataStats.FORM_TITLE = ''' || dataTable.FORM_TITLE || '''';				
			END FOR;
	    END;
	        
	    BEGIN
	    	-- return the data to the caller
		    DECLARE cur1 CURSOR WITH RETURN TO CLIENT 
				FOR SELECT APP_ID, TITLE, FORM_TITLE, TIMESTAMP_ISO(CREATED) AS CREATED, TIMESTAMP_ISO(UPDATED) AS UPDATED, STATUS, OWNER_EMAIL, RECORD_COUNT, TIMESTAMP_ISO(MAX_RECORD_CREATED) AS MAX_RECORD_CREATED, TIMESTAMP_ISO(MAX_RECORD_UPDATED) AS MAX_RECORD_UPDATED, ATTACHMENT_COUNT, ATTACHMENTS_SIZE, TABLENAME
				FROM SESSION.tempAppDataStats 		
				ORDER BY RECORD_COUNT DESC, APP_ID ASC;
		    OPEN cur1;
	    END;
	    		
	END@