<?php

function sendy_report_analysis_ensure_table()
{
	global $mysqli;

	$q = "CREATE TABLE IF NOT EXISTS `campaign_report_analyses` (
		`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
		`userID` int(11) DEFAULT NULL,
		`app` int(11) DEFAULT NULL,
		`report_type` varchar(20) DEFAULT NULL,
		`report_id` int(11) DEFAULT NULL,
		`analysis` mediumtext,
		`metrics` mediumtext,
		`score` int(3) DEFAULT NULL,
		`is_open` int(1) DEFAULT 0,
		`created` int(11) DEFAULT NULL,
		`updated` int(11) DEFAULT NULL,
		PRIMARY KEY (`id`),
		UNIQUE KEY `report_owner` (`userID`,`app`,`report_type`,`report_id`)
	) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

	mysqli_query($mysqli, $q);

	sendy_report_analysis_ensure_utf8mb4();

	$q = "SHOW COLUMNS FROM campaign_report_analyses WHERE Field = 'score'";
	$r = mysqli_query($mysqli, $q);
	if(!$r || mysqli_num_rows($r) == 0)
		mysqli_query($mysqli, 'ALTER TABLE campaign_report_analyses ADD score INT(3) DEFAULT NULL AFTER metrics');
}

function sendy_report_analysis_ensure_utf8mb4()
{
	global $mysqli;

	$r = mysqli_query($mysqli, "SHOW TABLE STATUS LIKE 'campaign_report_analyses'");
	if($r && mysqli_num_rows($r) > 0)
	{
		$row = mysqli_fetch_array($r);
		if(isset($row['Collation']) && strpos($row['Collation'], 'utf8mb4') === 0) return;
	}

	mysqli_query($mysqli, 'ALTER TABLE campaign_report_analyses CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci');
}

function sendy_report_analysis_use_utf8mb4()
{
	global $mysqli;

	if(function_exists('mysqli_character_set_name') && mysqli_character_set_name($mysqli)=='utf8mb4') return;
	mysqli_set_charset($mysqli, 'utf8mb4');
}

function sendy_report_analysis_get($report_type, $report_id, $app, $userID)
{
	global $mysqli;
	sendy_report_analysis_ensure_table();
	sendy_report_analysis_use_utf8mb4();

	$report_type = mysqli_real_escape_string($mysqli, $report_type);
	$report_id = (int)$report_id;
	$app = (int)$app;
	$userID = (int)$userID;

	$q = 'SELECT * FROM campaign_report_analyses WHERE report_type = "'.$report_type.'" AND report_id = '.$report_id.' AND app = '.$app.' AND userID = '.$userID.' LIMIT 1';
	$r = mysqli_query($mysqli, $q);
	if($r && mysqli_num_rows($r) > 0)
		return mysqli_fetch_array($r);

	return null;
}

function sendy_report_analysis_save_state($report_type, $report_id, $app, $userID, $is_open)
{
	global $mysqli;
	sendy_report_analysis_ensure_table();
	sendy_report_analysis_use_utf8mb4();

	$report_type = mysqli_real_escape_string($mysqli, $report_type);
	$report_id = (int)$report_id;
	$app = (int)$app;
	$userID = (int)$userID;
	$is_open = (int)$is_open;
	$now = time();

	$q = 'INSERT INTO campaign_report_analyses (userID, app, report_type, report_id, is_open, created, updated) VALUES ('.$userID.', '.$app.', "'.$report_type.'", '.$report_id.', '.$is_open.', '.$now.', '.$now.')
		ON DUPLICATE KEY UPDATE is_open = '.$is_open.', updated = '.$now;

	return mysqli_query($mysqli, $q);
}

function sendy_report_analysis_save_result($report_type, $report_id, $app, $userID, $analysis, $metrics, $is_open=1, $score=null)
{
	global $mysqli;
	sendy_report_analysis_ensure_table();
	sendy_report_analysis_use_utf8mb4();

	$report_type = mysqli_real_escape_string($mysqli, $report_type);
	$report_id = (int)$report_id;
	$app = (int)$app;
	$userID = (int)$userID;
	$is_open = (int)$is_open;
	$analysis = mysqli_real_escape_string($mysqli, $analysis);
	$metrics = mysqli_real_escape_string($mysqli, $metrics);
	$score_sql = is_numeric($score) ? (string)max(0, min(100, (int)$score)) : 'NULL';
	$now = time();

	$q = 'INSERT INTO campaign_report_analyses (userID, app, report_type, report_id, analysis, metrics, score, is_open, created, updated) VALUES ('.$userID.', '.$app.', "'.$report_type.'", '.$report_id.', "'.$analysis.'", "'.$metrics.'", '.$score_sql.', '.$is_open.', '.$now.', '.$now.')
		ON DUPLICATE KEY UPDATE analysis = "'.$analysis.'", metrics = "'.$metrics.'", score = '.$score_sql.', is_open = '.$is_open.', updated = '.$now;

	$r = mysqli_query($mysqli, $q);
	if(!$r) error_log('[Sendy campaign analysis] '.mysqli_error($mysqli).': in '.__FILE__.' on line '.__LINE__);
	return $r;
}

function sendy_report_analysis_score_value($score)
{
	if(!is_numeric($score)) return null;
	return max(0, min(100, (int)$score));
}

function sendy_report_analysis_score_html($score)
{
	$score = sendy_report_analysis_score_value($score);
	$score_display = $score===null ? '' : (string)$score;
	$score_width = $score===null ? 0 : $score;
	$style = $score===null ? ' style="display:none;"' : '';

	return '<div id="sendy-report-score" class="sendy-report-score"'.$style.'>
		<div class="sendy-report-score-row">
			<span class="sendy-report-score-label">'.htmlspecialchars(_('Campaign score'), ENT_QUOTES, 'UTF-8').'</span>
			<strong><span id="sendy-report-score-value">'.$score_display.'</span><small>/100</small></strong>
		</div>
		<div class="sendy-report-score-bar"><span id="sendy-report-score-bar" style="width: '.$score_width.'%;"></span></div>
	</div>';
}

function sendy_report_analysis_unique_opens($opens, $campaign_style=true)
{
	if(trim((string)$opens)=='') return array(0, 0);

	$opens_array = explode(',', $opens);
	$unique = array();

	foreach($opens_array as $open)
	{
		if(trim($open)=='') continue;
		if($campaign_style)
		{
			$parts = explode(':', $open);
			$open = isset($parts[0]) ? $parts[0] : $open;
		}
		$unique[] = $open;
	}

	return array(count(array_unique($unique)), count($unique));
}

function sendy_report_analysis_link_metrics($field, $report_id)
{
	global $mysqli;
	$links = array();
	$total_unique = 0;

	$field = $field == 'ares_emails_id' ? 'ares_emails_id' : 'campaign_id';
	$report_id = (int)$report_id;
	$all_clicks = array();

	$q = 'SELECT link, clicks FROM links WHERE '.$field.' = '.$report_id;
	$r = mysqli_query($mysqli, $q);
	if($r && mysqli_num_rows($r) > 0)
	{
		while($row = mysqli_fetch_array($r))
		{
			$clicks = trim((string)$row['clicks']);
			$click_array = $clicks=='' ? array() : array_filter(explode(',', $clicks), 'strlen');
			$unique_clicks = count(array_unique($click_array));
			$total_clicks = count($click_array);
			$all_clicks = array_merge($all_clicks, $click_array);
			$links[] = array(
				'url' => stripslashes($row['link']),
				'unique_clicks' => $unique_clicks,
				'total_clicks' => $total_clicks
			);
		}
	}

	$total_unique = count(array_unique($all_clicks));
	return array($total_unique, $links);
}

function sendy_report_analysis_country_metrics($opens)
{
	$opens = trim((string)$opens);
	$countries = array();
	$subscriber_countries = array();
	$unknown = 0;

	if($opens!='')
	{
		$opens_array = explode(',', $opens);
		foreach($opens_array as $open)
		{
			$open = trim($open);
			if($open=='') continue;

			$parts = explode(':', $open);
			$subscriber_id = isset($parts[0]) ? trim($parts[0]) : '';
			$country_code = isset($parts[1]) ? strtoupper(trim($parts[1])) : '';

			if($subscriber_id=='') continue;
			if($country_code=='')
			{
				$unknown++;
				continue;
			}

			$subscriber_countries[$subscriber_id] = $country_code;
		}
	}

	foreach($subscriber_countries as $country_code)
	{
		if(!isset($countries[$country_code])) $countries[$country_code] = 0;
		$countries[$country_code]++;
	}

	arsort($countries);
	$country_metrics = array();
	foreach($countries as $country_code => $opens_count)
	{
		$country_metrics[] = array(
			'country_code' => $country_code,
			'country' => country_code_to_country($country_code),
			'unique_opens' => $opens_count
		);
	}

	return array(
		'top_countries' => array_slice($country_metrics, 0, 10),
		'countries_detected' => count($country_metrics),
		'opens_without_country' => $unknown
	);
}

function sendy_report_analysis_subscriber_count($where)
{
	global $mysqli;
	$q = 'SELECT COUNT(*) AS total FROM subscribers WHERE '.$where;
	$r = mysqli_query($mysqli, $q);
	if($r && mysqli_num_rows($r) > 0)
	{
		$row = mysqli_fetch_array($r);
		return (int)$row['total'];
	}
	return 0;
}

function sendy_report_analysis_percent($part, $whole)
{
	$whole = (int)$whole;
	if($whole <= 0) return 0;
	return round(((int)$part / $whole) * 100, 2);
}

function sendy_campaign_report_analysis_metrics($campaign_id)
{
	global $mysqli;

	$campaign_id = (int)$campaign_id;
	$q = 'SELECT * FROM campaigns WHERE id = '.$campaign_id.' AND app = '.get_app_info('app').' AND userID = '.get_app_info('main_userID').' LIMIT 1';
	$r = mysqli_query($mysqli, $q);
	if(!$r || mysqli_num_rows($r)==0) return null;

	$row = mysqli_fetch_array($r);
	$recipients = (int)$row['recipients'];
	list($opens_unique, $opens_total) = sendy_report_analysis_unique_opens($row['opens'], true);
	list($clicks_unique, $links) = sendy_report_analysis_link_metrics('campaign_id', $campaign_id);
	$countries = sendy_report_analysis_country_metrics($row['opens']);
	$bounces = sendy_report_analysis_subscriber_count('last_campaign = '.$campaign_id.' AND bounced = 1');
	$unsubscribes = sendy_report_analysis_subscriber_count('last_campaign = '.$campaign_id.' AND unsubscribed = 1');
	$complaints = sendy_report_analysis_subscriber_count('last_campaign = '.$campaign_id.' AND complaint = 1');
	$delivered = max(0, $recipients - $bounces);

	return array(
		'report_type' => 'campaign',
		'subject' => stripslashes($row['title']),
		'from_name' => stripslashes($row['from_name']),
		'from_email' => stripslashes($row['from_email']),
		'sent_timestamp' => $row['sent'],
		'recipients' => $recipients,
		'delivered_estimate' => $delivered,
		'opens_tracking' => (int)$row['opens_tracking'],
		'links_tracking' => (int)$row['links_tracking'],
		'unique_opens' => $opens_unique,
		'total_opens' => $opens_total,
		'open_rate_percent' => sendy_report_analysis_percent($opens_unique, $delivered),
		'unique_clicks' => $clicks_unique,
		'click_rate_percent' => sendy_report_analysis_percent($clicks_unique, $delivered),
		'click_to_open_rate_percent' => sendy_report_analysis_percent($clicks_unique, $opens_unique),
		'unsubscribes' => $unsubscribes,
		'unsubscribe_rate_percent' => sendy_report_analysis_percent($unsubscribes, $delivered),
		'bounces' => $bounces,
		'bounce_rate_percent' => sendy_report_analysis_percent($bounces, $recipients),
		'complaints' => $complaints,
		'complaint_rate_percent' => sendy_report_analysis_percent($complaints, $recipients),
		'links' => array_slice($links, 0, 10),
		'country_opens' => $countries
	);
}

function sendy_autoresponder_report_analysis_metrics($ares_email_id)
{
	global $mysqli;

	$ares_email_id = (int)$ares_email_id;
	$q = 'SELECT ae.*, a.name AS autoresponder_name, a.type AS autoresponder_type, l.app AS app, l.userID AS list_userID
		FROM ares_emails ae
		LEFT JOIN ares a ON ae.ares_id = a.id
		LEFT JOIN lists l ON a.list = l.id
		WHERE ae.id = '.$ares_email_id.' AND l.app = '.get_app_info('app').' AND l.userID = '.get_app_info('main_userID').'
		LIMIT 1';
	$r = mysqli_query($mysqli, $q);
	if(!$r || mysqli_num_rows($r)==0) return null;

	$row = mysqli_fetch_array($r);
	$recipients = (int)$row['recipients'];
	list($opens_unique, $opens_total) = sendy_report_analysis_unique_opens($row['opens'], false);
	list($clicks_unique, $links) = sendy_report_analysis_link_metrics('ares_emails_id', $ares_email_id);
	$countries = sendy_report_analysis_country_metrics($row['opens']);
	$bounces = sendy_report_analysis_subscriber_count('last_ares = '.$ares_email_id.' AND bounced = 1');
	$unsubscribes = sendy_report_analysis_subscriber_count('last_ares = '.$ares_email_id.' AND unsubscribed = 1');
	$complaints = sendy_report_analysis_subscriber_count('last_ares = '.$ares_email_id.' AND complaint = 1');
	$delivered = max(0, $recipients - $bounces);

	return array(
		'report_type' => 'autoresponder',
		'subject' => stripslashes($row['title']),
		'autoresponder_name' => stripslashes($row['autoresponder_name']),
		'autoresponder_type' => $row['autoresponder_type'],
		'from_name' => stripslashes($row['from_name']),
		'from_email' => stripslashes($row['from_email']),
		'recipients' => $recipients,
		'delivered_estimate' => $delivered,
		'opens_tracking' => (int)$row['opens_tracking'],
		'links_tracking' => (int)$row['links_tracking'],
		'unique_opens' => $opens_unique,
		'total_opens' => $opens_total,
		'open_rate_percent' => sendy_report_analysis_percent($opens_unique, $delivered),
		'unique_clicks' => $clicks_unique,
		'click_rate_percent' => sendy_report_analysis_percent($clicks_unique, $delivered),
		'click_to_open_rate_percent' => sendy_report_analysis_percent($clicks_unique, $opens_unique),
		'unsubscribes' => $unsubscribes,
		'unsubscribe_rate_percent' => sendy_report_analysis_percent($unsubscribes, $delivered),
		'bounces' => $bounces,
		'bounce_rate_percent' => sendy_report_analysis_percent($bounces, $recipients),
		'complaints' => $complaints,
		'complaint_rate_percent' => sendy_report_analysis_percent($complaints, $recipients),
		'links' => array_slice($links, 0, 10),
		'country_opens' => $countries
	);
}

function sendy_report_analysis_html($analysis)
{
	$analysis = (string)$analysis;
	$analysis = strip_tags($analysis, '<p><br><strong><b><em><i><ul><ol><li><h3><h4>');
	$analysis = preg_replace('/<\s*(\/?)\s*(p|br|strong|b|em|i|ul|ol|li|h3|h4)(?:\s+[^>]*)?>/i', '<$1$2>', $analysis);
	return $analysis;
}

?>
