<?php

function sendy_content_improvements_ensure_table()
{
	global $mysqli;

	$q = "CREATE TABLE IF NOT EXISTS `email_content_analyses` (
		`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
		`userID` int(11) DEFAULT NULL,
		`app` int(11) DEFAULT NULL,
		`content_type` varchar(20) DEFAULT NULL,
		`content_id` int(11) DEFAULT 0,
		`parent_id` int(11) DEFAULT 0,
		`analysis` mediumtext,
		`source` mediumtext,
		`is_open` int(1) DEFAULT 0,
		`created` int(11) DEFAULT NULL,
		`updated` int(11) DEFAULT NULL,
		PRIMARY KEY (`id`),
		UNIQUE KEY `content_owner` (`userID`,`app`,`content_type`,`content_id`,`parent_id`)
	) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

	mysqli_query($mysqli, $q);
	sendy_content_improvements_ensure_utf8mb4();
}

function sendy_content_improvements_ensure_utf8mb4()
{
	global $mysqli;

	$r = mysqli_query($mysqli, "SHOW TABLE STATUS LIKE 'email_content_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 email_content_analyses CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci');
}

function sendy_content_improvements_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_content_improvements_get($content_type, $content_id, $parent_id, $app, $userID)
{
	global $mysqli;
	sendy_content_improvements_ensure_table();
	sendy_content_improvements_use_utf8mb4();

	$content_type = mysqli_real_escape_string($mysqli, $content_type);
	$content_id = (int)$content_id;
	$parent_id = (int)$parent_id;
	$app = (int)$app;
	$userID = (int)$userID;

	$q = 'SELECT * FROM email_content_analyses WHERE content_type = "'.$content_type.'" AND content_id = '.$content_id.' AND parent_id = '.$parent_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_content_improvements_save_state($content_type, $content_id, $parent_id, $app, $userID, $is_open)
{
	global $mysqli;
	sendy_content_improvements_ensure_table();
	sendy_content_improvements_use_utf8mb4();

	$content_type = mysqli_real_escape_string($mysqli, $content_type);
	$content_id = (int)$content_id;
	$parent_id = (int)$parent_id;
	$app = (int)$app;
	$userID = (int)$userID;
	$is_open = (int)$is_open;
	$now = time();

	$q = 'INSERT INTO email_content_analyses (userID, app, content_type, content_id, parent_id, is_open, created, updated) VALUES ('.$userID.', '.$app.', "'.$content_type.'", '.$content_id.', '.$parent_id.', '.$is_open.', '.$now.', '.$now.')
		ON DUPLICATE KEY UPDATE is_open = '.$is_open.', updated = '.$now;

	return mysqli_query($mysqli, $q);
}

function sendy_content_improvements_save_result($content_type, $content_id, $parent_id, $app, $userID, $analysis, $source, $is_open=1)
{
	global $mysqli;
	sendy_content_improvements_ensure_table();
	sendy_content_improvements_use_utf8mb4();

	$content_type = mysqli_real_escape_string($mysqli, $content_type);
	$content_id = (int)$content_id;
	$parent_id = (int)$parent_id;
	$app = (int)$app;
	$userID = (int)$userID;
	$is_open = (int)$is_open;
	$analysis = mysqli_real_escape_string($mysqli, $analysis);
	$source = mysqli_real_escape_string($mysqli, $source);
	$now = time();

	$q = 'INSERT INTO email_content_analyses (userID, app, content_type, content_id, parent_id, analysis, source, is_open, created, updated) VALUES ('.$userID.', '.$app.', "'.$content_type.'", '.$content_id.', '.$parent_id.', "'.$analysis.'", "'.$source.'", '.$is_open.', '.$now.', '.$now.')
		ON DUPLICATE KEY UPDATE analysis = "'.$analysis.'", source = "'.$source.'", is_open = '.$is_open.', updated = '.$now;

	return mysqli_query($mysqli, $q);
}

?>
