#! /usr/bin/env php
<?php

declare(strict_types=1);

namespace Gazelle;

/**
 * Sometimes, unit tests crash and burn and leave garbage behind in the database.
 * This program will clean out all known test entries. Any remaining entries are
 * either unknown to the unit tests, or the queries here do not identify them.
 */

require __DIR__ . '/../lib/bootstrap.php';


$pgrw = new DB\Pg(PG_RW_DSN);
$pgrw->query("
    delete from news where title ~ 'phpunit'
");
$pgrw->query("
    delete from report_auto_type where name ~ '^(ra(man|search)? test |type\d*)'
");
$pgrw->query("
    delete from report_auto_category where name in ('ReportAutoTest', 'somecategory', 'newcat')
");
$db = DB::DB();

$db->prepared_query("SHOW TABLES LIKE 'phpunit_%'");
$tableList = $db->collect(0);
foreach($tableList as $t) {
    $db->prepared_query("DROP TABLE $t");
}

$db->prepared_query("SET foreign_key_checks = 0");

// artists
$db->prepared_query("
    DELETE aa, ag
    FROM artists_alias aa
    INNER JOIN artists_group ag on (
        ag.ArtistID = aa.ArtistID
        AND ag.PrimaryAlias = aa.AliasID
    )
    WHERE aa.name REGEXP '^phpunit '
");
$db->prepared_query("
    DELETE aa, ag
    FROM artists_group ag
    INNER JOIN artists_alias aa USING (ArtistID)
    WHERE aa.name REGEXP '^phpunit\.';
");

// contests
$db->prepared_query("
    DELETE c, chbp, bp
    FROM contest c
    LEFT JOIN contest_has_bonus_pool chbp USING (contest_id)
    LEFT JOIN bonus_pool bp USING (bonus_pool_id)
    WHERE c.name LIKE 'phpunit contest %';
");

$db->prepared_query("SET foreign_key_checks = 1");

$db->prepared_query("
    DELETE FROM artists_alias aa
    WHERE NOT EXISTS (
        SELECT 1 FROM artists_group ag WHERE ag.artistid = aa.artistid
    )
");
$pgrw->query(<<<END_SQL
    delete from bookmark_artist ba where not exists(
        select 1 from relay.artists_group ag where ag."ArtistID" = ba.id_artist
    )
END_SQL);
$db->prepared_query("
    CREATE TEMPORARY TABLE orphan_similar (SimilarID int not null primary key)
");
$db->prepared_query("
    INSERT INTO orphan_similar
    SELECT DISTINCT sim.similarid
    FROM artists_similar sim
    LEFT JOIN artists_group ag USING (artistid)
    WHERE ag.artistid IS NULL;
");
$db->prepared_query("
    DELETE ss
    FROM artists_similar_scores ss
    INNER JOIN orphan_similar os USING (SimilarID)
");

// clean reports
$db->prepared_query("
    DELETE FROM reports
    WHERE Type = 'request'
        AND Reason = 'phpunit report'
");
$db->prepared_query("
    DELETE r
    FROM reports r
    LEFT JOIN users_main um ON (um.ID = r.UserID)
    WHERE um.ID IS NULL
");

// clean wiki
$db->prepared_query("
    DELETE FROM wiki_aliases WHERE Alias REGEXP '^(phpunittitle|wikieditreadable)'
");
$db->prepared_query("
    DELETE FROM wiki_revisions WHERE Title LIKE 'phpunit title %';
");
$db->prepared_query("
    DELETE FROM wiki_articles WHERE Title REGEXP '^(wiki edit readable|phpunit title) '
");

$userMan = new Manager\User();
$collageMan = new Manager\Collage();
$collageMan->requestContext()->setViewer(
    $userMan->findById(
        (int)$db->scalar('
            SELECT um.id
            FROM users_main um
            INNER JOIN permissions p on (p.ID = um.PermissionID)
            ORDER BY p.level DESC
            LIMIT 1
        ')
    )
);

// clean collages
$db->prepared_query("
    SELECT ID
    FROM collages
    WHERE Name LIKE 'phpunit %'
");
foreach ($db->collect(0) as $collageId) {
    $collage = $collageMan->findById($collageId);
    echo "collage {$collage->id} ({$collage->name()})\n";
    $collage->hardRemove();
}

// clean requests
$requestList = $pgrw->columnex("
    select id_request
    from request
    where title ~ '^phpunit '
");

$reqMan = new Manager\Request();
foreach ($requestList as $requestId) {
    $request = $reqMan->findById($requestId);
    echo "request {$request->id} ({$request->title()})\n";
    $request->remove();
}

// clean forums
$db->prepared_query("
    SELECT ID
    FROM forums
    WHERE Name LIKE 'phpunit %'
");

$forMan = new Manager\Forum();
foreach ($db->collect(0) as $forumId) {
    $forum = $forMan->findById($forumId);
    echo "forum {$forum->id} ({$forum->name()})\n";
    $db->prepared_query("
        DELETE fp, ft
        FROM forums_posts fp
        INNER JOIN forums_topics ft ON (ft.ID = fp.TopicID)
        WHERE ft.ForumID = ?
        ", $forum->id
    );
    $forum->remove();
}
$forMan->flushToc();

// clean torrents and torrent groups
$db->prepared_query("
    SELECT tg.ID as gid, t.ID as tid
    FROM torrents_group tg
    LEFT JOIN torrents t on (t.GroupID = tg.ID)
    WHERE tg.Name regexp '^(?:phpunit (?:category (?:change|new)|collfree|comment|download|feat|live in|log(?:checker)?|notify|reaper|request|riplog|seedbox|tag|tg(?:man|vote)|torman|top10|torrent(?: (?:report|xcode))?) [0-9A-Za-z]{6}|(?:php(?: split title|unit (?:artist (?:autocomp(?: (?:cache|rank))?|merge[12]|random|smart rename))|bonus(?: add)?|json|tracker)) [0-9A-Za-z]{10}|Some (?:(?:boppy|live|more) )?[0-9A-Za-z]{8} (?:contest )?songs)$'
");
$groupList   = $db->collect('gid');
$torrentList = $db->collect('tid');

$torMan = new Manager\Torrent();
foreach ($torrentList as $torrentId) {
    $torrent = $torMan->findById((int)$torrentId);
    if ($torrent) {
        echo "torrent $torrentId\n";
        try {
            $torrent->removeTorrent(null, 'garbage collection', -1);
        } catch (\Exception) {
        }
    }
}

$tgMan = new Manager\TGroup();
foreach ($groupList as $tgroupId) {
    $tgroup = $tgMan->findById($tgroupId);
    if ($tgroup instanceof TGroup) {
        try {
            echo "tgroup $torrentId ({$tgroup?->name()})\n";
            $pgrw->query('
                delete from bookmark_tgroup bt WHERE id_tgroup = $1
                ', $tgroupId
            );
            $tgroup->remove(new User(1));
        } catch (\Error|\Exception) {
        }
    }
}

// and finally, clean users

(new DB())->relaxConstraints(true);

$db->prepared_query("
    SELECT um.ID
    FROM users_main um
    WHERE um.Username regexp '^(?:(?:(?:admin|ajax|base|bb|bo(?:nus(?:add|boost|g|invite|other|r|stat|up)?)?|collage|contest|cookie|create|donor|download|error|inbox\.(?:recv|send)|forum|friend[1-3]|gift|invitee?|logchecker|mod|priv[12]|promo|riplog|session|sitelog|stats|store|tag|text|tgroup\.[anu]|topten|trk(?:fl)?|twig|u(?:[1-3]|pload|ser(?:hist)?)|wiki)\.[0-9A-Za-z]{6}|tag\.[0-9A-Za-z]{8}|(?:advs|admin|art(?:2|ist|y)|authn|backlog|blog|collfree|comment|dnu|dupip|email[1-3]?|enc\.med|feat|fls|geodist|invite|ipv4|merge|mfa|mod|nav|new(?:\.grp|s)|ord|phpunit|re(?:aper|clab|g[1-5]|lease|portg?|q)|riplog|sbox|searchrep|spec|stats|tag(?:no|2(?:no|yes))?|text|tg(?:cat|vote|man)|token|torman|torrent|trk(?:down|fl|free)?|uat|ul[0-4]|um[1-4]|uploader|use(?:arch|r)|year|xva)\.[0-9A-Za-z]{10}|notf(?:\.(new|enc))?\.[^.]+)|spm_(?:fls|mod|sysop|user)_[0-9A-Za-z]{10})$'
");
foreach ($db->collect(0) as $userId) {
    $user = $userMan->findById($userId);
    $db->prepared_query("DELETE FROM forums_topics WHERE LastPostAuthorID = ?", $userId);
    $db->prepared_query("DELETE FROM locked_accounts WHERE UserID = ?", $userId);
    if ($user) {
        try {
            echo "user {$user->label()}\n";
            $user->remove();
        } catch (\Error $e) {
            echo "   fail {$e->getMessage()}\n";
        }
    }
}

foreach (['artist', 'collage', 'request', 'tgroup'] as $type) {
    $pgrw->query(<<<END_SQL
        delete from bookmark_$type u
        where not exists (
            select 1 from relay.users_main um where um."ID" = u.id_user
        )
    END_SQL
    );
}
$pgrw->query('
    delete from forum_poll where id_forum_thread not in (select "ID" from relay.forums_topics)
');
$pgrw->query('
    delete from ip_history where id_user not in (select "ID" from relay.users_main)
');
$pgrw->query('
    delete from ip_site_history where id_user not in (select "ID" from relay.users_main)
');
$pgrw->query('
    delete from tag_reject where id_user not in (select "ID" from relay.users_main)
');
$db->prepared_query("
    DELETE tt
    FROM torrents_tags tt
    LEFT JOIN torrents_group tg ON (tg.ID = tt.GroupID)
    WHERE tg.ID IS NULL
");
$db->prepared_query("
    DELETE FROM tags
    WHERE Name REGEXP '^(ut|phpunit)\.';
");
$db->prepared_query("
    DELETE u
    FROM users_freeleeches u
    LEFT JOIN users_main um ON (um.ID = u.UserID)
    WHERE um.ID IS NULL
");
$db->prepared_query("
    DELETE u
    FROM users_history_emails u
    LEFT JOIN users_main um ON (um.ID = u.UserID)
    WHERE um.ID IS NULL
");
$db->prepared_query("
    DELETE u
    FROM user_last_access_delta u
    LEFT JOIN users_main um ON (um.ID = u.user_id)
    WHERE um.ID IS NULL
");
$db->prepared_query("
    DELETE u
    FROM users_leech_stats u
    LEFT JOIN users_main um ON (um.ID = u.UserID)
    WHERE um.ID IS NULL
");
$db->prepared_query("
    DELETE u
    FROM xbt_files_history u
    LEFT JOIN users_main um ON (um.ID = u.uid)
    WHERE um.ID IS NULL
");
$db->prepared_query("
    DELETE u
    FROM xbt_files_users u
    LEFT JOIN users_main um ON (um.ID = u.uid)
    WHERE um.ID IS NULL
");
$db->prepared_query("
    DELETE u
    FROM xbt_snatched u
    LEFT JOIN users_main um ON (um.ID = u.uid)
    WHERE um.ID IS NULL
");
