SET @start_date = '2023-01-01';
#SET @end_date = '2023-08-31';
SET @end_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY);
SET @user_id = 151256;

DROP TEMPORARY TABLE IF EXISTS
	tmp_usd_rate;

CREATE TEMPORARY TABLE
	tmp_usd_rate
		(KEY (`date`))
SELECT
	e.`date`,
	AVG(e.exrate) AS exrate
FROM
	rabota_db.exchange_rate e
WHERE
	e.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
	AND e.source_cur_id = 6
	AND e.destination_cur_id = 2
GROUP BY
	e.`date`;

DROP TEMPORARY TABLE IF EXISTS
	tmp_eur_rate;

CREATE TEMPORARY TABLE
	tmp_eur_rate
		(KEY (`date`))
SELECT
	e.`date`,
	AVG(e.exrate) AS exrate
FROM
	rabota_db.exchange_rate e
WHERE
	e.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
	AND e.source_cur_id = 6
	AND e.destination_cur_id = 4
GROUP BY
	e.`date`;

DROP TEMPORARY TABLE IF EXISTS tmp_rub_rate;
CREATE TEMPORARY TABLE tmp_rub_rate (KEY (`date`))
SELECT
e.`date`,
AVG(e.exrate) AS exrate
FROM rabota_db.exchange_rate e
WHERE e.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
AND e.source_cur_id = 6
AND e.destination_cur_id = 1
GROUP BY e.`date`;

DELETE FROM
	tablo.unified_site_data
WHERE
	`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
	AND user_id = @user_id;

#Insert GA
INSERT INTO
	tablo.unified_site_data
(
	`date`,
	account_id,
	account_type,
	url,
	user_id,
	site_id,
	advertiser_id,
	advertiser_name,
	device,
	sessions,
	pageviews,
	users,
	content_platform
)
SELECT
	ga.`date`,
	ci.ga_view_id,
	'analytics',
	IFNULL(s.url_domain, s.url) AS uri,
	s.user_id,
	ci.site_id,
	0,
	'Google Analytics',
	CONCAT(UPPER(LEFT(ga.device_category, 1)), SUBSTR(ga.device_category, 2)) AS device,
	SUM(ga.sessions),
	SUM(ga.pageviews),
	SUM(ga.users),
	IF(data_source='(not set)' OR data_source='web', 'Web', 'AMP') AS content_platform
 FROM
	imports.`ga_unified_reporting` ga
		JOIN rabota_db.ga_custom_import ci
			ON ci.ga_import_id = ga.ga_import_id
		JOIN rabota_db.site s
			ON s.site_id = ci.site_id
WHERE
	ga.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
	AND (ci.field_group='unified_reporting_devices_users')
	AND s.`user_id` = @user_id
GROUP BY `date`,ci.ga_view_id,uri,user_id,ci.site_id,device,content_platform;

#Insert GA4 AMP
INSERT INTO
	tablo.unified_site_data
(
	`date`,
	account_id,
	account_type,
	url,
	user_id,
	site_id,
	advertiser_id,
	advertiser_name,
	device,
	sessions,
	pageviews,
	users,
	content_platform
)
SELECT
	ga.`date`,
	ci.ga_view_id,
	'analytics',
	IFNULL(s.url_domain, s.url) AS url,
	s.user_id,
	ci.site_id,
	0,
	'Google Analytics',
	'Mobile' AS device,
	SUM(ga.sessions),
	SUM(ga.pageviews),
	SUM(ga.users),
	'AMP' AS content_platform
FROM
	imports.`ga_unified_reporting` ga
		JOIN rabota_db.ga_custom_import ci
			ON ci.ga_import_id = ga.ga_import_id
		JOIN rabota_db.site s
			ON s.site_id = ci.site_id
WHERE
	ga.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
	AND ci.field_group = 'ga4_unified_reporting_no_devices'
	AND s.user_id = @user_id
GROUP BY `date`,ci.ga_view_id,url,user_id,ci.site_id,device,content_platform;

# for subtracting from total in order to get web
DROP TEMPORARY TABLE IF EXISTS tmp_ga4_amp;
CREATE TEMPORARY TABLE tmp_ga4_amp (KEY (`date`, site_id, device_category))
SELECT
	ga.`date`,
	ci.site_id,
	'mobile' AS device_category,
	SUM(ga.sessions) AS sessions,
	SUM(ga.pageviews) AS pageviews,
	SUM(ga.users) AS users
FROM
	imports.`ga_unified_reporting` ga
		JOIN rabota_db.ga_custom_import ci
			ON ci.ga_import_id = ga.ga_import_id
		JOIN rabota_db.site s
			ON s.site_id = ci.site_id
WHERE
	ga.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
	AND ci.field_group = 'ga4_unified_reporting_no_devices'
	AND s.user_id = @user_id
GROUP BY `date`, ci.site_id;

#Insert GA4 Web
INSERT INTO
	tablo.unified_site_data
(
	`date`,
	account_id,
	account_type,
	url,
	user_id,
	site_id,
	advertiser_id,
	advertiser_name,
	device,
	sessions,
	pageviews,
	users,
	content_platform
)
SELECT
	ga.`date`,
	ci.ga_view_id,
	'analytics',
	IFNULL(s.url_domain, s.url) AS url,
	s.user_id,
	ci.site_id,
	0,
	'Google Analytics',
	IF(ga.device_category = 'smart tv', 'Desktop', CONCAT(UPPER(LEFT(ga.device_category, 1)), SUBSTR(ga.device_category, 2))) AS device,
	SUM(ga.sessions) - SUM(IFNULL(gaamp.sessions, 0)),
	SUM(ga.pageviews) - SUM(IFNULL(gaamp.pageviews, 0)),
	SUM(ga.users) - SUM(IFNULL(gaamp.users, 0)),
	'Web' AS content_platform
FROM
	imports.`ga_unified_reporting` ga
		JOIN rabota_db.ga_custom_import ci
			ON ci.ga_import_id = ga.ga_import_id
		JOIN rabota_db.site s
			ON s.site_id = ci.site_id
		LEFT JOIN tmp_ga4_amp gaamp
			ON ga.date = gaamp.date AND ci.site_id = gaamp.site_id AND ga.device_category = gaamp.device_category
WHERE
	ga.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
	AND ci.field_group = 'ga4_unified_reporting_devices' 
	AND s.user_id = @user_id
GROUP BY `date`,ci.ga_view_id,url,user_id,ci.site_id,device,content_platform;

INSERT INTO
	tablo.unified_site_data
(
	`date`,
	account_id,
	account_type,
	url,
	user_id,
	site_id,
	advertiser_id,
	advertiser_name,
	device,
	requests,
	impressions,
	clicks,
	pageviews_adsense,
	adv_expense_gbp,
	partner_gain,
	partner_gain_gbp,
	content_platform,
	viewability_viewed,
	viewability_measured,

	adv_expense_usd,
	partner_gain_usd,
	adv_expense_eur,
	partner_gain_eur,
	adv_expense_rub,
	partner_gain_rub
)
SELECT
	a.`date`,
	a.client_id,
	'adsense',
	IFNULL(IFNULL(s.url_domain, s.url), a.`domain`),
	IFNULL(r.clickio_user_id, s.user_id),
	r.clickio_site_id,
	0,
	'Google AdSense',
	IF(a.platform_type = 'HighEndMobile', 'Mobile', IF(a.platform_type = 'Tablet', 'Tablet', 'Desktop')),
	SUM(a.ad_requests),
	SUM(a.impressions),
	SUM(a.clicks),
	SUM(a.page_views),
	SUM(a.earnings_gbp),
	SUM(a.earnings),
	SUM(a.earnings_gbp),
	CASE WHEN a.content_platform = 'Web Story' THEN 'Web'
	ELSE a.content_platform END AS content_platform,
	SUM(a.measurability * a.impressions * a.viewability),
	SUM(a.measurability * a.impressions),

	ROUND(SUM(a.earnings_gbp) * usd.exrate, 8),
	ROUND(SUM(a.earnings_gbp) * usd.exrate, 8),
	ROUND(SUM(a.earnings_gbp) * eur.exrate, 8),
	ROUND(SUM(a.earnings_gbp) * eur.exrate, 8),
	ROUND(SUM(a.earnings_gbp) * rub.exrate, 8),
	ROUND(SUM(a.earnings_gbp) * rub.exrate, 8)
FROM
	imports.adsense_raw_data a
		JOIN tablo.unified_adsense_domain_relation r
			ON r.`domain` = a.`domain`
			AND r.client_id = a.client_id
		LEFT JOIN rabota_db.site s
			ON s.site_id = r.clickio_site_id
		JOIN tmp_usd_rate usd
			ON usd.`date` = a.`date`
		JOIN tmp_eur_rate eur
			ON eur.`date` = a.`date`
		JOIN tmp_rub_rate rub
			ON rub.date = a.date
WHERE
	a.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
	AND r.clickio_user_id = @user_id
GROUP BY
	a.`date`,
	a.client_id,
	IFNULL(IFNULL(s.url_domain, s.url), a.`domain`),
	r.clickio_site_id,
	IF(a.platform_type = 'HighEndMobile', 'Mobile', IF(a.platform_type = 'Tablet', 'Tablet', 'Desktop')),
	CASE WHEN a.content_platform = 'Web Story' THEN 'Web'
	ELSE a.content_platform END
HAVING
	SUM(a.earnings) > 0;

# ad manager - by sites

INSERT INTO
	tablo.unified_site_data
(
	`date`,
	account_id,
	account_type,
	url,
	user_id,
	site_id,
	advertiser_id,
	advertiser_name,
	device,
	requests,
	impressions,
	clicks,
	adv_expense_gbp,
	partner_gain,
	partner_gain_gbp,
	content_platform,
	adv_expense_usd,
	partner_gain_usd,
	adv_expense_eur,
	partner_gain_eur,
	adv_expense_rub,
	partner_gain_rub,
	demand_channel,
	viewability_viewed, 
	viewability_measured
)
SELECT
	a.`date`,
	a.network_id,
	'admanager',
	IFNULL(IFNULL(s.url_domain, s.url), f.`domain`),
	s.user_id,
	r.clickio_site_id,
	0,
	'Google Ad Manager',
	IF(a.device_category_name = 'Smartphone', 'Mobile', IF(a.device_category_name = 'Tablet', 'Tablet', 'Desktop')),
	SUM(a.requests),
	SUM(a.impressions),
	SUM(a.clicks),
	SUM(a.revenue * IFNULL(e.exrate, 1)),
	SUM(a.revenue),
	SUM(a.revenue * IFNULL(e.exrate, 1)),
	IF(a.inventory_types = 'AMP', 'AMP', 'Web'),
	ROUND(SUM(a.revenue * IFNULL(e.exrate, 1)) * usd.exrate, 8),
	ROUND(SUM(a.revenue * IFNULL(e.exrate, 1)) * usd.exrate, 8),
	ROUND(SUM(a.revenue * IFNULL(e.exrate, 1)) * eur.exrate, 8),
	ROUND(SUM(a.revenue * IFNULL(e.exrate, 1)) * eur.exrate, 8),
	ROUND(SUM(a.revenue * IFNULL(e.exrate, 1)) * rub.exrate, 8),
	ROUND(SUM(a.revenue * IFNULL(e.exrate, 1)) * rub.exrate, 8),
	a.demand_channel,
	SUM(a.viewable_impressions), 
	SUM(a.measurable_impressions)
FROM
	tablo.unified_reporting_gam a
		JOIN tablo.unified_reporting_gam_site_filter f
			ON a.date = f.date
			AND a.ad_unit_id = f.ad_unit_id
			AND a.network_id = f.network_id
		JOIN tablo.unified_gam_domain_relation r
			ON r.`domain` = f.`domain`
			AND r.network_id = a.network_id
		LEFT JOIN rabota_db.site s
			ON s.site_id = r.clickio_site_id
		LEFT JOIN rabota_db.exchange_rate e
			ON a.date = e.date AND r.currency_id = e.source_cur_id AND e.destination_cur_id = 6 AND e.exchange_rate_source_id = 3
		JOIN tmp_usd_rate usd
			ON usd.`date` = a.`date`
		JOIN tmp_eur_rate eur
			ON eur.`date` = a.`date`
		JOIN tmp_rub_rate rub
			ON rub.date = a.date
WHERE
	a.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
	AND r.clickio_user_id = @user_id
GROUP BY
	a.`date`,
	a.network_id,
	IFNULL(IFNULL(s.url_domain, s.url), f.`domain`),
	r.clickio_site_id,
	IF(a.device_category_name = 'Smartphone', 'Mobile', IF(a.device_category_name = 'Tablet', 'Tablet', 'Desktop')),
	IF(a.inventory_types = 'AMP', 'AMP', 'Web'),
	a.demand_channel
#HAVING
#	SUM(a.revenue) > 0
;

DROP TEMPORARY TABLE IF EXISTS
	tmp_sites_dates;

CREATE TEMPORARY TABLE
	tmp_sites_dates
		(KEY (site_id, `date`))
SELECT DISTINCT
	site_id,
	`date`
FROM
	tablo.unified_site_data
WHERE
	`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
	AND NOT site_id IS NULL
	AND user_id = @user_id;

INSERT INTO
	tablo.unified_site_data
(
	`date`,
	account_id,
	account_type,
	url,
	user_id,
	site_id,
	advertiser_id,
	device,
	requests,
	impressions,
	clicks,
	adv_expense_gbp,
	partner_gain,
	partner_gain_gbp,
	content_platform,
	viewability_viewed,
	viewability_measured,
        external_view_count,

	adv_expense_usd,
	partner_gain_usd,
	adv_expense_eur,
	partner_gain_eur,
	adv_expense_rub,
	partner_gain_rub,

	pub_adserver_cost, 
	pub_adserver_cost_gbp,
	company_adserver_cost, 
	company_adserver_cost_gbp
)
SELECT
	npm.`date`,
	s.user_id,
	'clickio',
	IFNULL(s.url_domain, s.url),
	s.user_id,
	npm.site_id,
	npm.adv_net_id,
	IF(npm.device_type IN ('High-end mobile devices', 'Smartphone'), 'Mobile', IF(npm.device_type IN ('Tablets', 'Tablet'), 'Tablet', 'Desktop')),
	SUM(npm.external_first_request_count),
	SUM(npm.external_first_view_count),
	SUM(npm.hit_count),
	SUM(npm.adv_expense_gbp),
	SUM(npm.partner_gain),
	SUM(npm.partner_gain_gbp),
	CASE WHEN sd24.is_amp = 1 THEN 'AMP' ELSE 'Web' END,
	SUM(npm.external_viewability_viewed_impressions),
	SUM(npm.external_viewability_measured_impressions),
        SUM(npm.external_view_count),

        ROUND(SUM(npm.adv_expense_gbp) * usd.exrate, 8),
	ROUND(SUM(npm.partner_gain_gbp) * usd.exrate, 8),
	ROUND(SUM(npm.adv_expense_gbp) * eur.exrate, 8),
	ROUND(SUM(npm.partner_gain_gbp) * eur.exrate, 8),
	ROUND(SUM(npm.adv_expense_gbp) * rub.exrate, 8),
	ROUND(SUM(npm.partner_gain_gbp) * rub.exrate, 8),

	SUM(npm.`pub_adserver_cost`) AS pub_adserver_cost, 
	SUM(npm.`pub_adserver_cost_gbp`) AS pub_adserver_cost_gbp,
	SUM(npm.`company_adserver_cost`) AS company_adserver_cost, 
	SUM(npm.`company_adserver_cost_gbp`) AS company_adserver_cost_gbp
FROM
	tablo.npm_site_area_device_stat npm
		JOIN tmp_sites_dates tmp
			ON tmp.site_id = npm.site_id
			AND tmp.`date` = npm.`date`
			AND npm.site_id <> 230029
		JOIN rabota_db.site s
			ON s.site_id = npm.site_id
		JOIN rabota_db.site_area_design_24 sd24
			ON sd24.site_area_id = npm.site_area_id
		JOIN tmp_usd_rate usd
			ON usd.`date` = npm.`date`
		JOIN tmp_eur_rate eur
			ON eur.`date` = npm.`date`
		JOIN tmp_rub_rate rub
			ON rub.date = npm.date
GROUP BY
	npm.`date`,
	IFNULL(s.url_domain, s.url),
	npm.site_id,
	npm.adv_net_id,
	IF(npm.device_type IN ('High-end mobile devices', 'Smartphone'), 'Mobile', IF(npm.device_type IN ('Tablets', 'Tablet'), 'Tablet', 'Desktop')),
	CASE WHEN sd24.is_amp = 1 THEN 'AMP' ELSE 'Web' END
# HAVING
#	SUM(npm.partner_gain) > 0
# for inclusion of Direct Campaigns (cmf_id: 123)
;

UPDATE
	tablo.unified_site_data u
		LEFT JOIN rabota_db.demand_source d
			ON d.cmf_system_id = u.advertiser_id
SET
	u.advertiser_name = IFNULL(d.name, 'Unknown')
WHERE
	u.account_type = 'clickio'
	AND u.date BETWEEN DATE(@start_date) AND DATE(@end_date)
	AND u.`user_id` = @user_id;

DELETE FROM
	tablo.unified_ad_unit_data
WHERE
	`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
	AND user_id = @user_id;

INSERT INTO
	tablo.unified_ad_unit_data
(
	`date`,
	account_id,
	account_type,
	url,
	user_id,
	site_id,
	ad_unit_id,
	ad_unit_name,
	advertiser_id,
	advertiser_name,
	device,
	requests,
	impressions,
	clicks,
	adv_expense_gbp,
	partner_gain,
	partner_gain_gbp,
	content_platform,
	viewability_viewed,
	viewability_measured,
	adsense_ad_format
)
SELECT
	a.`date`,
	a.client_id,
	'adsense',
	IFNULL(IFNULL(s.url_domain, s.url), a.`domain`),
	IFNULL(r.clickio_user_id, s.user_id),
	r.clickio_site_id,
	a.unit_id,
	a.unit_name,
	0,
	'Google AdSense',
	IF(a.platform_type = 'HighEndMobile', 'Mobile', IF(a.platform_type = 'Tablet', 'Tablet', 'Desktop')),
	SUM(a.ad_requests),
	SUM(a.impressions),
	SUM(a.clicks),
	SUM(a.earnings_gbp),
	SUM(a.earnings),
	SUM(a.earnings_gbp),
	CASE WHEN a.content_platform = 'Web Story' THEN 'Web'
	ELSE a.content_platform END AS content_platform,
	SUM(a.measurability * a.impressions * a.viewability),
	SUM(a.measurability * a.impressions),
	'In-page'	
FROM
	imports.adsense_adunit_raw_data a
		JOIN tablo.unified_adsense_domain_relation r
			ON r.`domain` = a.`domain`
			AND r.client_id = a.client_id
		LEFT JOIN rabota_db.site s
			ON s.site_id = r.clickio_site_id
WHERE
	a.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
	AND r.clickio_user_id = @user_id
GROUP BY
	a.`date`,
	a.client_id,
	IFNULL(IFNULL(s.url_domain, s.url), a.`domain`),
	r.clickio_site_id,
	a.unit_id,
	IF(a.platform_type = 'HighEndMobile', 'Mobile', IF(a.platform_type = 'Tablet', 'Tablet', 'Desktop')),
	CASE WHEN a.content_platform = 'Web Story' THEN 'Web'
	ELSE a.content_platform END
HAVING
	SUM(a.earnings) > 0;

INSERT INTO
	tablo.unified_ad_unit_data
(
	`date`,
	account_id,
	account_type,
	url,
	user_id,
	site_id,
	ad_unit_id,
	ad_unit_name,
	advertiser_id,
	advertiser_name,
	device,
	requests,
	impressions,
	clicks,
	adv_expense_gbp,
	partner_gain,
	partner_gain_gbp,
	content_platform,
	demand_channel,
	viewability_viewed, 
	viewability_measured
)
SELECT
	a.`date`,
	a.network_id,
	'admanager',
	IFNULL(IFNULL(s.url_domain, s.url), f.`domain`),
	s.user_id,
	r.clickio_site_id,
	a.ad_unit_id,
	a.ad_unit_name,
	0,
	'Google Ad Manager',
	IF(a.device_category_name = 'Smartphone', 'Mobile', IF(a.device_category_name = 'Tablet', 'Tablet', 'Desktop')),
	SUM(a.requests),
	SUM(a.impressions),
	SUM(a.clicks),
	SUM(a.revenue * IFNULL(e.exrate, 1)),
	SUM(a.revenue),
	SUM(a.revenue * IFNULL(e.exrate, 1)),
	IF(a.inventory_types = 'AMP', 'AMP', 'Web'),
	a.demand_channel,
	SUM(a.viewable_impressions), 
	SUM(a.measurable_impressions)
FROM
	tablo.unified_reporting_gam a
		JOIN tablo.unified_reporting_gam_site_filter f
			ON a.date = f.date
			AND a.ad_unit_id = f.ad_unit_id
			AND a.network_id = f.network_id
		JOIN tablo.unified_gam_domain_relation r
			ON r.`domain` = f.`domain`
			AND r.network_id = a.network_id
		LEFT JOIN rabota_db.site s
			ON s.site_id = r.clickio_site_id
		LEFT JOIN rabota_db.exchange_rate e
			ON a.date = e.date AND r.currency_id = e.source_cur_id AND e.destination_cur_id = 6 AND e.exchange_rate_source_id = 3
WHERE
	a.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
	AND r.clickio_user_id = @user_id
GROUP BY
	a.`date`,
	a.network_id,
	IFNULL(IFNULL(s.url_domain, s.url), f.`domain`),
	r.clickio_site_id,
	a.ad_unit_id,
	IF(a.device_category_name = 'Smartphone', 'Mobile', IF(a.device_category_name = 'Tablet', 'Tablet', 'Desktop')),
	IF(a.inventory_types = 'AMP', 'AMP', 'Web'),
	a.demand_channel
#HAVING
#	SUM(a.revenue) > 0
;

INSERT INTO
	tablo.unified_ad_unit_data
(
	`date`,
	account_id,
	account_type,
	url,
	user_id,
	site_id,
	ad_unit_id,
	advertiser_id,
	device,
	requests,
	impressions,
	clicks,
	adv_expense_gbp,
	partner_gain,
	partner_gain_gbp,
	content_platform,
	viewability_viewed,
	viewability_measured,
        external_view_count,

	pub_adserver_cost, 
	pub_adserver_cost_gbp,
	company_adserver_cost, 
	company_adserver_cost_gbp
)
SELECT
	npm.`date`,
	s.user_id,
	'clickio',
	IFNULL(s.url_domain, s.url),
	s.user_id,
	npm.site_id,
	IFNULL(asa.site_area_id, npm.site_area_id),
	npm.adv_net_id,
	IF(npm.device_type IN ('High-end mobile devices', 'Smartphone'), 'Mobile', IF(npm.device_type IN ('Tablets', 'Tablet'), 'Tablet', 'Desktop')),
	SUM(npm.external_first_request_count),
	SUM(npm.external_first_view_count),
	SUM(npm.hit_count),
	SUM(npm.adv_expense_gbp),
	SUM(npm.partner_gain),
	SUM(npm.partner_gain_gbp),
	CASE WHEN sd24.is_amp = 1 THEN 'AMP' ELSE 'Web' END,
	SUM(npm.external_viewability_viewed_impressions),
	SUM(npm.external_viewability_measured_impressions),
        SUM(npm.external_view_count),

	SUM(npm.`pub_adserver_cost`) AS pub_adserver_cost, 
	SUM(npm.`pub_adserver_cost_gbp`) AS pub_adserver_cost_gbp,
	SUM(npm.`company_adserver_cost`) AS company_adserver_cost, 
	SUM(npm.`company_adserver_cost_gbp`) AS company_adserver_cost_gbp
FROM
	tablo.npm_site_area_device_stat npm
		JOIN tmp_sites_dates tmp
			ON tmp.site_id = npm.site_id
			AND tmp.`date` = npm.`date`
			AND npm.site_id <> 230029
		LEFT JOIN rabota_db.attached_site_areas asa
			ON asa.attached_site_area_id = npm.site_area_id
		JOIN rabota_db.site s
			ON s.site_id = npm.site_id
		JOIN rabota_db.site_area_design_24 sd24
			ON sd24.site_area_id = IFNULL(asa.site_area_id, npm.site_area_id)
GROUP BY
	npm.`date`,
	IFNULL(s.url_domain, s.url),
	npm.site_id,
	IFNULL(asa.site_area_id, npm.site_area_id),
	npm.adv_net_id,
	IF(npm.device_type IN ('High-end mobile devices', 'Smartphone'), 'Mobile', IF(npm.device_type IN ('Tablets', 'Tablet'), 'Tablet', 'Desktop')),
	CASE WHEN sd24.is_amp = 1 THEN 'AMP' ELSE 'Web' END
# HAVING
#	SUM(npm.partner_gain) > 0
# for inclusion of Direct Campaigns (cmf_id: 123)
;

UPDATE
	tablo.unified_ad_unit_data u
		LEFT JOIN rabota_db.demand_source d
			ON d.cmf_system_id = u.advertiser_id
SET
	u.advertiser_name = IFNULL(d.name, 'Unknown')
WHERE
	u.account_type = 'clickio'
	AND u.date BETWEEN DATE(@start_date) AND DATE(@end_date)
	AND u.`user_id` = @user_id;

UPDATE
	tablo.unified_ad_unit_data u
		LEFT JOIN rabota_db.site_area sa
			ON sa.site_area_id = u.ad_unit_id
SET
	u.ad_unit_name = IFNULL(sa.name, 'no_ad_unit_name')
WHERE
	u.account_type = 'clickio'
	AND u.date BETWEEN DATE(@start_date) AND DATE(@end_date)
	AND u.`user_id` = @user_id;

INSERT INTO
	tablo.unified_ad_unit_data
(
	`date`,
	account_id,
	account_type,
	url,
	user_id,
	site_id,
	ad_unit_id,
	ad_unit_name,
	advertiser_id,
	advertiser_name,
	device,
	requests,
	impressions,
	clicks,
	adv_expense_gbp,
	partner_gain,
	partner_gain_gbp,
	content_platform,
	adsense_ad_format,
	viewability_viewed,
	viewability_measured
)
SELECT
	a.`date`,
	a.client_id,
	'adsense',
	IFNULL(IFNULL(s.url_domain, s.url), a.`domain`),
	IFNULL(r.clickio_user_id, s.user_id),
	r.clickio_site_id,
	0,
	'[auto ads]',
	0,
	'Google AdSense',
	IF(a.platform_type = 'HighEndMobile', 'Mobile', IF(a.platform_type = 'Tablet', 'Tablet', 'Desktop')),
	SUM(a.ad_requests),
	SUM(a.impressions),
	SUM(a.clicks),
	SUM(a.earnings_gbp),
	SUM(a.earnings),
	SUM(a.earnings_gbp),
	CASE WHEN a.content_platform = 'Web Story' THEN 'Web'
	ELSE a.content_platform END AS content_platform,
	a.ad_format_name,
	SUM(a.measurability * a.impressions * a.viewability),
	SUM(a.measurability * a.impressions)	
FROM
	imports.`adsense_raw_data` a
		JOIN tablo.unified_adsense_domain_relation r
			ON r.`domain` = a.`domain`
			AND r.client_id = a.client_id
		LEFT JOIN rabota_db.site s
			ON s.site_id = r.clickio_site_id
WHERE
	a.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
	AND a.ad_placement_name = 'Auto ads'
	AND r.clickio_user_id = @user_id
GROUP BY
	a.`date`,
	a.client_id,
	IFNULL(IFNULL(s.url_domain, s.url), a.`domain`),
	r.clickio_site_id,
	IF(a.platform_type = 'HighEndMobile', 'Mobile', IF(a.platform_type = 'Tablet', 'Tablet', 'Desktop')),
	CASE WHEN a.content_platform = 'Web Story' THEN 'Web'
	ELSE a.content_platform END,
	a.ad_format_name
HAVING
	SUM(a.earnings) > 0;

#update site_data adsense access
DROP TEMPORARY TABLE IF EXISTS tmp_mcm_access;
CREATE TEMPORARY TABLE tmp_mcm_access(KEY(account_id))
SELECT 
CONCAT('ca-', child_network_id) AS account_id
FROM rabota_db.`mcm_publisher_date_settings`
WHERE mcm_type = 'adsense'
;

#active API access
DROP TEMPORARY TABLE IF EXISTS tmp_active_api_access;
CREATE TEMPORARY TABLE tmp_active_api_access (KEY(account_id))
SELECT CONCAT("ca-", network_id) AS account_id,
google_user_settings_id 
FROM rabota_db.`adsense_custom_import`
WHERE 
google_user_settings_id NOT IN (1, 2, 3, 6, 15, 84, 9, 72, 148, 197, 463)
AND last_import_status = 'OK'
AND today_every_minute IS NOT NULL
GROUP BY CONCAT("ca-", network_id)
;

# set area_type
UPDATE tablo.unified_ad_unit_data a 
JOIN rabota_db.site_area_type sat ON sat.site_area_id = a.ad_unit_id
SET a.area_type = sat.`site_area_type_pub`		
WHERE a.`account_type` = 'clickio' AND a.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
AND a.user_id = @user_id;

#Adsense null user_id
DROP TEMPORARY TABLE IF EXISTS tmp_adsense_null;
CREATE TEMPORARY TABLE tmp_adsense_null (KEY(client_id, user_id))
SELECT
a.client_id,
b.user_id
FROM tablo.`unified_adsense_domain_relation` a
JOIN rabota_db.`site` b
ON a.clickio_site_id = b.site_id
GROUP BY a.client_id;

UPDATE tablo.`unified_site_data` a
JOIN tmp_adsense_null b
ON a.`account_id` = b.client_id
SET a.`user_id` = b.user_id
WHERE a.account_type = 'adsense' AND a.`user_id` IS NULL
AND a.`date` BETWEEN @start_date AND @end_date
AND a.user_id = @user_id;

UPDATE tablo.`unified_ad_unit_data` a
JOIN tmp_adsense_null b
ON a.`account_id` = b.client_id
SET a.`user_id` = b.user_id
WHERE a.account_type = 'adsense' AND a.`user_id` IS NULL
AND a.`date` BETWEEN @start_date AND @end_date
AND a.`user_id` = @user_id;

#Adsense account name
DROP TEMPORARY TABLE IF EXISTS tmp_adsense_name;
CREATE TEMPORARY TABLE tmp_adsense_name (KEY(client_id))
SELECT
a.client_id,
a.account_name
FROM imports.`adsense_raw_data` a
WHERE DATE >= CURDATE() - INTERVAL 30 DAY
AND a.account_name IS NOT NULL
GROUP BY a.client_id;

UPDATE tablo.`unified_site_data` a
JOIN tmp_adsense_name b
ON a.`account_id` = b.client_id
SET a.`adsense_account` = b.account_name
WHERE account_type = 'adsense'
AND a.`date` BETWEEN @start_date AND @end_date
AND a.`user_id` = @user_id;

UPDATE tablo.`unified_ad_unit_data` a
JOIN tmp_adsense_name b
ON a.`account_id` = b.client_id
SET a.`adsense_account` = b.account_name
WHERE account_type = 'adsense'
AND a.`date` BETWEEN @start_date AND @end_date
AND a.`user_id` = @user_id;

#Commercial region and publisher currency
DROP TEMPORARY TABLE IF EXISTS tmp_region_cur;
CREATE TEMPORARY TABLE tmp_region_cur (KEY(user_id))
SELECT 
a.user_id, 
a.commercial_region, 
b.code 
FROM rabota_db.`user` a
JOIN rabota_db.`currency` b
ON a.cur_id = b.currency_id;

UPDATE tablo.`unified_site_data` a
JOIN tmp_region_cur b
ON a.`user_id` = b.user_id
SET 
a.`commercial_region` = b.commercial_region,
a.`currency` = b.code
WHERE a.`date` BETWEEN @start_date AND @end_date
AND a.`user_id` = @user_id;

UPDATE tablo.`unified_ad_unit_data` a
JOIN tmp_region_cur b
ON a.`user_id` = b.user_id
SET 
a.`commercial_region` = b.commercial_region,
a.`currency` = b.code
WHERE a.`date` BETWEEN @start_date AND @end_date
AND a.`user_id` = @user_id;

# site category
UPDATE tablo.unified_site_data u
JOIN rabota_db.site s ON s.site_id = u.site_id
JOIN rabota_db.site_category c ON s.category_id = c.category_id
SET u.site_category = IFNULL(c.name, 'Unknown')
WHERE u.date BETWEEN @start_date AND @end_date
AND u.`user_id` = @user_id;