<?php
$_db_migration_to = '39.28'; // 8.13.31
if (!isset($migrations) || !is_object($migrations))
	die("This file cannot be executed directly");
$migrations->CheckValid($_db_migration_to);
//===========================================================================================



$migrations->Run('01_orphan_comment_attachment_cleanup.php', <<<'DB_UPDATE_FILE'
<?php

function RemoveOrphanComments(\Claromentis\Core\DAL\Db $db, string $path, int $aggregation_id, string $object_table)
{
	// Find all orphan attachments for the aggregation
	$res = $db->query('SELECT id FROM discussion_attachments WHERE discussion_id IN (SELECT id FROM discussions WHERE aggregation eq:int:aggregation_id AND object_id NOT IN (SELECT id FROM ' . $object_table . '))', $aggregation_id);
	while ($arr = $res->fetchArray())
	{
		$attachment_id = (int)$arr['id'];

		if (file_exists($path . $attachment_id . '.dat'))
			unlink($path . $attachment_id . '.dat');
		if (file_exists($path . $attachment_id . '_lg.dat'))
			unlink($path . $attachment_id . '_lg.dat');
		if (file_exists($path . $attachment_id . '_sm.dat'))
			unlink($path . $attachment_id . '_sm.dat');
	}

	// Remove all orphan comment rows
	$db->query('DELETE FROM discussion_attachments WHERE discussion_id IN (SELECT id FROM discussions WHERE aggregation eq:int:aggregation_id AND object_id NOT IN (SELECT id FROM ' . $object_table . '))', $aggregation_id);
	$db->query('DELETE FROM discussions WHERE aggregation eq:int:aggregation_id AND object_id NOT IN (SELECT id FROM ' . $object_table . ')', $aggregation_id);
}

/** @var $migrations \Claromentis\Setup\SetupFacade */
$db = $migrations->GetDb();

$attachment_path = $migrations->GetDataDir() . '/discussion/';

RemoveOrphanComments($db, $attachment_path, 21 /* AGGREGATION_NEWS */, 'news');
RemoveOrphanComments($db, $attachment_path, 120 /* AGGREGATION_BLOG */, 'blog_post');

DB_UPDATE_FILE
);


//===========================================================================================
$migrations->SetVersion('39.28');
