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



$migrations->Run('250410_remove_corrupt_trashcan_entries.php', <<<'DB_UPDATE_FILE'
<?php

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

/**
 * See: BT-5166
 * There was a bug when moving sites or menu items in pages to the trashcan where it was possible
 * for an object to be marked as deleted multiple times. This resulted in corrupt data in the
 * pages_trashcan table in the form of multiple entries for the same site/item. There should only
 * ever be one entry in this table for an object in the trashcan, which is then removed when the
 * site/item is restored (or fully deleted)
 *
 * This migration cleans up any existing duplicate or otherwise corrupt rows in the pages_trashcan
 * table. These are:
 *  - Remove leftover entries for sites/items that have been fully deleted
 *  - Remove leftover entries for sites/items that have already been restored
 *  - Remove duplicate entries for sites/items still in the trash (keeping only the first/earliest)
 *
 */

$to_delete = [];
/**
 * Entries left over when sites/pages have been completely deleted (removed from the trashcan)
 */

// Pages menu items (pages)
$result = $db->query("
	SELECT ptc.id FROM pages_trash_can AS ptc
	LEFT JOIN pages_menu_item AS pmi ON pmi.id = ptc.obj_id
	WHERE ptc.aggregation = 145
	AND pmi.id IS NULL;
");
$to_delete = array_merge($to_delete, $result->fetchAllValues());

// Pages sites
$result = $db->query("
	SELECT ptc.id, ps.id FROM pages_trash_can AS ptc
	LEFT JOIN pages_site AS ps ON ps.id = ptc.obj_id
	WHERE ptc.aggregation = 157
	AND ps.id IS NULL;
");
$to_delete = array_merge($to_delete, $result->fetchAllValues());

// delete all those rows by id, if there are any
if (!empty($to_delete)) {
	$db->query("DELETE FROM pages_trash_can WHERE id IN in:int:trash_ids", $to_delete);
}

$to_delete = [];
/**
 * Entries left over after sites/pages have been restored
 */

// menu items
$result = $db->query("
	SELECT pt.id FROM pages_trash_can AS pt 
	LEFT JOIN pages_menu_item AS pmi ON pt.obj_id = pmi.url_id
	WHERE pt.aggregation = 145 AND pmi.is_deleted = 0
	");
$to_delete = array_merge($to_delete, $result->fetchAllValues());

// sites
$result = $db->query("
	SELECT pt.id FROM pages_trash_can AS pt LEFT JOIN pages_site AS ps ON pt.obj_id = ps.id
	WHERE pt.aggregation = 157 AND ps.is_deleted = 0
	");
$to_delete = array_merge($to_delete, $result->fetchAllValues());

// delete all those rows by id, if there are any
if (!empty($to_delete)) {
	$db->query("DELETE FROM pages_trash_can WHERE id IN in:int:trash_ids", $to_delete);
}

/**
 * Items still in the trashcan but have duplicate entries.
 *
 * In this case, we keep the earliest entry and delete the others.
 *
 * I was initially going to use `deleted_date` for picking the earliest entry. Then realised there is
 * a small but non-zero chance that an item could be marked as deleted more than once in the same second.
 * So, now it's much simpler, going by the first ID associated with the deletion as this is unique to each row
 *
 * This deletion has to be run AFTER all the deletions above have happened as the SELECT query would
 * pick up the above duplicates as well if they're still around.
 */
$result = $db->query("
	SELECT COUNT(id) AS duplicates, MIN(id) AS id, obj_id, aggregation 
	FROM pages_trash_can 
	GROUP BY obj_id, aggregation 
	HAVING COUNT(id) > 1");

while($row = $result->fetchArray()) {
	$deletion_result = $db->query("
		DELETE FROM pages_trash_can 
		WHERE obj_id eq:int:obj_id
		AND aggregation eq:int:aggregation
		AND id > int:trash_id;
	", $row['obj_id'], $row['aggregation'], $row['id']);
}

DB_UPDATE_FILE
);


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