/*
* This script creates views that take data from all of the other applications
* DBs and aggregates it.
*
* In particular, the first one creates a view that contains all user/role/application
* pairs (so AP knows what applications to show links for), and the second counts
* pending requests in applications (so we can show a notification number).
*
*
* To use this script, the DB names must be replaced by the local DB names.
*
* Note: the hardcoded application_code values must match the "code" column
* values in the  applications table.
*/

DROP VIEW IF EXISTS user_apps;

CREATE VIEW user_apps AS
	(
		SELECT
			DISTINCT lr_roles.service_number service_number, 'leaverequest' as application_code
		FROM leaverequest_dev.user_roles lr_roles
	)
	UNION
	(
		SELECT
			DISTINCT nar_roles.service_number service_number, 'narforecast' as application_code
		FROM nar_dev.user_roles nar_roles
	)
	UNION
	(
		SELECT
			DISTINCT eoe_roles.service_number service_number, 'endofemployment' as application_code
		FROM endofemployment_dev.user_roles eoe_roles
	)
;

DROP VIEW IF EXISTS application_alerts;

CREATE VIEW application_alerts AS
	(
		SELECT
			req.manager_service_number service_number, 'endofemployment' as application_code, COUNT(req.id) as num_alerts
		FROM endofemployment_dev.requests req
		WHERE req.status_id = 1
		GROUP BY req.manager_service_number
	)
	UNION
	(
		SELECT
			req.approver_service_number service_number, 'leaverequest' as application_code, COUNT(DISTINCT req.id) as num_alerts
		FROM
			leaverequest_dev.request_items item
			JOIN leaverequest_dev.requests req
				ON req.id = item.request_id
		WHERE item.status_id = 1 OR item.status_id = 4
		GROUP BY req.approver_service_number
	)
;

DROP VIEW IF EXISTS tablet_uploads;

CREATE VIEW tablet_uploads AS
	(
		SELECT 'leaverequest' as app_code, up.* FROM leaverequest_dev.tablet_uploads up
	)
	UNION
	(
		SELECT 'otherapp' as app_code, up.* FROM leaverequest_dev.tablet_uploads up
	)
;
