START TRANSACTION;

-- CREATE INDEX fireinfo_search ON fireinfo USING gin (blob) WHERE expired_at IS NULL;
-- CREATE UNIQUE INDEX fireinfo_current ON fireinfo USING btree (profile_id) WHERE expired_at IS NULL

-- CREATE INDEX fireinfo_releases_current ON fireinfo USING hash((blob->'system'->'release')) WHERE expired_at IS NULL;
-- CREATE INDEX fireinfo_releases ON fireinfo USING hash((blob->'system'->'release'));

-- CREATE INDEX fireinfo_arches_current ON fireinfo USING hash((blob->'cpu'->'arch')) WHERE blob->'cpu'->'arch' IS NOT NULL AND expired_at IS NULL;
-- CREATE INDEX fireinfo_arches ON fireinfo USING hash((blob->'cpu'->'arch')) WHERE blob->'cpu'->'arch' IS NOT NULL;

-- CREATE INDEX fireinfo_cpu_vendors ON fireinfo USING hash((blob->'cpu'->'vendor')) WHERE blob->'cpu'->'vendor' IS NOT NULL;

-- CREATE INDEX fireinfo_hypervisor_vendors_current ON fireinfo USING hash((blob->'hypervisor'->'vendor')) WHERE expired_at IS NULL AND CAST((blob->'system'->'virtual') AS boolean) IS TRUE;

-- XXX virtual index

TRUNCATE TABLE fireinfo;

--EXPLAIN

INSERT INTO fireinfo

SELECT
	p.public_id AS profile_id,
	p.time_created AS created_at,
	(
		CASE
			WHEN p.time_valid <= CURRENT_TIMESTAMP THEN p.time_valid
			ELSE NULL
		END
	) AS expired_at,
	0 AS version,
	(
		-- Empty the profile if we don't have any data
		CASE WHEN profile_arches.arch_id IS NULL THEN NULL

		-- Otherwise do some hard work...
		ELSE
			-- CPU
			jsonb_build_object('cpu',
				jsonb_build_object(
					'arch',         arches.name,
					'bogomips',     profile_processors.bogomips,
					'speed',        profile_processors.clock_speed,

					'vendor',       processors.vendor,
					'model',        processors.model,
					'model_string', processors.model_string,
					'stepping',     processors.stepping,
					'flags',        processors.flags,
					'family',       processors.family,
					'count',        processors.core_count
				)
			)

			-- System
			|| jsonb_build_object('system',
				jsonb_build_object(
					'kernel',    kernels.name,
					'language',  profile_languages.language,
					'memory',    profile_memory.amount,
					'release',   releases.name,
					'root_size', profile_storage.amount,
					'vendor',    systems.vendor,
					'model',     systems.model,
					'virtual',   CASE WHEN hypervisors.id IS NULL THEN FALSE ELSE TRUE END
				)
			)

			-- Hypervisor
			|| CASE
				WHEN hypervisors.id IS NULL THEN jsonb_build_object()
				ELSE
					jsonb_build_object(
						'hypervisor',
						json_build_object('vendor', hypervisors.name)
					)
				END

			-- Devices
			|| jsonb_build_object('devices', devices.devices)

			-- Networks
			|| jsonb_build_object('networks',
				jsonb_build_object(
					'green',  profile_networks.has_green,
					'blue',   profile_networks.has_blue,
					'orange', profile_networks.has_orange,
					'red',    profile_networks.has_red
				)
			)
		END
	) AS blob,
	p.time_updated AS last_updated_at,
	p.private_id AS private_id,
	locations.location AS country_code

FROM fireinfo_profiles p

LEFT JOIN
	fireinfo_profiles_locations locations ON p.id = locations.profile_id

LEFT JOIN
	fireinfo_profiles_arches profile_arches ON p.id = profile_arches.profile_id

LEFT JOIN
	fireinfo_arches arches ON profile_arches.arch_id = arches.id

LEFT JOIN
	(
		SELECT
			profile_devices.profile_id AS profile_id,
			jsonb_agg(
				jsonb_build_object(
					'deviceclass', devices.deviceclass,
					'subsystem',   devices.subsystem,
					'vendor',      devices.vendor,
					'model',       devices.model,
					'sub_vendor',  devices.sub_vendor,
					'sub_model',   devices.sub_model,
					'driver',      devices.driver
				)
			) AS devices
		FROM
			fireinfo_profiles_devices profile_devices
		LEFT JOIN
			fireinfo_devices devices ON profile_devices.device_id = devices.id
		GROUP BY
			profile_devices.profile_id
	) devices ON p.id = devices.profile_id

LEFT JOIN
	fireinfo_profiles_processors profile_processors ON p.id = profile_processors.profile_id

LEFT JOIN
	fireinfo_processors processors ON profile_processors.processor_id = processors.id

LEFT JOIN
	fireinfo_profiles_kernels profile_kernels ON p.id = profile_kernels.profile_id

LEFT JOIN
	fireinfo_kernels kernels ON profile_kernels.kernel_id = kernels.id

LEFT JOIN
	fireinfo_profiles_languages profile_languages ON p.id = profile_languages.profile_id

LEFT JOIN
	fireinfo_profiles_memory profile_memory ON p.id = profile_memory.profile_id

LEFT JOIN
	fireinfo_profiles_releases profile_releases ON p.id = profile_releases.profile_id

LEFT JOIN
	fireinfo_releases releases ON profile_releases.release_id = releases.id

LEFT JOIN
	fireinfo_profiles_storage profile_storage ON p.id = profile_storage.profile_id

LEFT JOIN
	fireinfo_profiles_systems profile_systems ON p.id = profile_systems.profile_id

LEFT JOIN
	fireinfo_systems systems ON profile_systems.system_id  = systems.id

LEFT JOIN
	fireinfo_profiles_virtual profile_virtual ON p.id = profile_virtual.profile_id

LEFT JOIN
	fireinfo_hypervisors hypervisors ON profile_virtual.hypervisor_id = hypervisors.id

LEFT JOIN
	fireinfo_profiles_networks profile_networks ON p.id = profile_networks.profile_id

--WHERE
-- XXX TO FIND A PROFILE WITH DATA
--	profile_processors.profile_id IS NOT NULL

-- XXX TO FIND A VIRTUAL PROFILE
--profile_virtual.hypervisor_id IS NOT NULL

--ORDER BY
--	time_created DESC

--LIMIT 1
;

COMMIT;
