create table if not exists tables_join(
is_test boolean default false,
date_business date,
type text,
client text,
os text,
session_id text,
user_id int,
data jsonb,
ip text,
app_version text);


UPDATE usagereport_hist t
SET products_visualized = products_visualized + sub_q.res
FROM(
SELECT count(*) AS res,
	CASE
		WHEN EXISTS (select * from test_ip t WHERE t.ip = r.ip)
		OR (r.user_email ~ '(krzysztof.obstawski|aleksander.gregorek|norbert.gregorek|jakub.kowalczyk|jakub.jedynak|devices@intiaro.com|devices@agitive.com|devicesintiaro@gmail.com)')
		THEN true
		ELSE false
	END as test,
r.event_date::date,
	(CASE
		WHEN application='Missing app data'
		THEN (select alias from clients c where c.client = r."domain")
		ELSE (select alias from clients c where c.client = r.application)
	END) as application,
r.platform
FROM combinedanalytics_19_02_2020_21_50 r
WHERE
	((r.cat='Product' AND r.act='Open')
	OR (r.cat='Product' AND r.lbl='Open')
	OR (r.act='Product' AND r.lbl='Open')
	OR (r.cat='Open' AND r.act='Product')
	OR (r.cat='Open' AND r.lbl='Product')
	OR (r.act='Open' AND r.lbl='Product'))
GROUP BY
	CASE
		WHEN EXISTS (select * from test_ip t WHERE t.ip = r.ip)
		OR (r.user_email ~ '(krzysztof.obstawski|aleksander.gregorek|norbert.gregorek|jakub.kowalczyk|jakub.jedynak|devices@intiaro.com|devices@agitive.com|devicesintiaro@gmail.com)')
		THEN true
		ELSE false
	END,
r.event_date::date, r.platform,
	CASE
		WHEN application='Missing app data'
		THEN (select alias from clients c where c.client = r."domain")
		ELSE (select alias from clients c where c.client = r.application)
	END) AS sub_q
WHERE sub_q.test = t.is_test AND sub_q.event_date::date = t.date_business AND sub_q.platform = t.os AND sub_q.application = t.client;

UPDATE usagereport_hist t
SET products_visualized = products_visualized + sub_q.res
FROM(
SELECT count(*) AS res, r.is_test, date_business, r.client, r.os
FROM tables_join r
WHERE
	((r.data->>'cat'='Product' AND r.data->>'act'='Open')
	OR (r.data->>'cat'='Product' AND r.data->>'lbl'='Open')
	OR (r.data->>'act'='Product' AND r.data->>'lbl'='Open')
	OR (r.data->>'cat'='Open' AND r.data->>'act'='Product')
	OR (r.data->>'cat'='Open' AND r.data->>'lbl'='Product')
	OR (r.data->>'act'='Open' AND r.data->>'lbl'='Product'))
GROUP BY r.is_test,  date_business, r.os,  r.client) AS sub_q
WHERE sub_q.is_test = t.is_test AND sub_q.date_business = t.date_business AND sub_q.os = t.os AND sub_q.client = t.client AND t.type = 'UnityWL';


INSERT INTO tables_join(date_business, type, client, ip , session_id, user_id, app_version, data)
SELECT (e.data->>'ts')::date, 'UnityWL', w.internalAppName, d.ip, d.session_uuid, d.user_id, d.app_version, e.data
FROM analytics_event e
inner join analytics_devicedata d on e."session_id" = d.id
inner join whitelabel w on w.id = e.white_label_id;



UPDATE tables_join
SET is_test = test_ip.is_test
FROM test_ip
WHERE tables_join.ip = test_ip.ip;



UPDATE tables_join t
SET os =
	CASE
		WHEN (t.client = 'EthanAllen' OR t.client='Ethan Allen')
		AND (t.app_version='1.8.3' OR t.app_version='1.8.8' OR t.app_version='1.8.10')
		AND t.date_business>'2019-02-13'
		THEN 'android'
		WHEN (t.client = 'EthanAllen' OR t.client='Ethan Allen')
		AND (t.app_version='1.8.4' OR t.app_version='1.8.7' OR t.app_version='1.8.9')
		AND t.date_business>'2019-02-14'
		THEN 'ios'
		ELSE u.platform
	END
FROM user_userdevice u
WHERE t.user_id = u.user_id;