<commit_before><?php

    require_once __DIR__ . '/../../incl/incl.php';
    require_once __DIR__ . '/../../incl/memcache.incl.php';

    function GetUSRealms() {
        $cacheKey = 'extra:multirealm:realms2';

        $realms = MCGet($cacheKey);
        if ($realms !== false) {
            return $realms;
        }

        $sql = 'select id, slug, name, house, canonical from tblRealm where region = \'US\' and locale is not null';
        $db = DBConnect();
        $stmt = $db->prepare($sql);
        $stmt->execute();
        $result = $stmt->get_result();
        $realmList = DBMapArray($result);
        $stmt->close();

        uasort($realmList, function($a,$b){
            return strcmp($a['name'], $b['name']);
        });

        $realms = [
            'id' => $realmList,
            'house' => [],
        ];

        foreach ($realmList as $id => $row) {
            if (!is_null($row['canonical'])) {
                $realms['house'][$row['house']] = $id;
            }
        }

        MCSet($cacheKey, $realms, 86400);

        return $realms;
    }

    function GetSellerList() {
        $cacheKey = 'extra:multirealm:sellers';

        $sellers = MCGet($cacheKey);
        if ($sellers !== false) {
            return $sellers;
        }

        $sellers = [];

        $sql = <<<'EOF'
        select z2.cnt, s.name sellername, r.name realmname, s.firstseen, s.lastseen, r.house, r.slug
        from (
            select seller, count(distinct `snapshot`) cnt
            from (
                SELECT seller, item, `snapshot`
                FROM `tblSellerItemHistory` 
                where item in (128159, 127736, 127738, 127732, 127731, 127737, 127735, 127730, 127734, 127733, 127718, 128158, 127720, 127714, 127713, 127719, 127717, 127712, 127716, 127715)
            ) z1
            group by seller
            having count(distinct item) = 2
        ) z2
        left join tblSellerItemHistory h on h.seller = z2.seller and h.item not in (128159, 127736, 127738, 127732, 127731, 127737, 127735, 127730, 127734, 127733, 127718, 128158, 127720, 127714, 127713, 127719, 127717, 127712, 127716, 127715)
        join tblSeller s on s.id = z2.seller
        join tblRealm r on s.realm = r.id
        where h.seller is null
        and r.region = 'US'
        order by r.house, s.firstseen, z2.cnt desc, s.lastseen desc
EOF;

        $db = DBConnect();
        $stmt = $db->prepare($sql);
        $stmt->execute();
        $result = $stmt->get_result();
        while ($row = $result->fetch_assoc()) {
            $sellers[$row['house']][] = $row;
        }
        $result->close();
        $stmt->close();

        MCSet($cacheKey, $sellers);
    }

    function GenerateList() {
        $realms = GetUSRealms();
        $sellers = GetSellerList();

        foreach ($realms['id'] as $id => $row) {
            $canonicalId = isset($realms['house'][$row['house']]) ? $realms['house'][$row['house']] : $id;
            echo '<a name="', $row['slug'], '"></a><h3>', $row['name'], '</h3>';
            if ($canonicalId != $id) {
                echo 'See <a href="#', $realms['id'][$canonicalId]['slug'], '">', $realms['id'][$canonicalId]['name'], '</a>';
            } else {
                if (!isset($sellers[$row['house']])) {
                    echo 'None detected!';
                } else {
                    echo '<table><tr><th>Seller</th><th>First Seen</th><th>Last Seen</th><th>Confidence</th></tr>';
                    foreach ($sellers[$row['house']] as $sellerRow) {
                        echo '<tr><td><a href="/#us/', $sellerRow['slug'], '/seller/', mb_strtolower($sellerRow['sellername']), '">';
                        echo $sellerRow['sellername'], '-', $sellerRow['realmname'], '</a></td>';
                        echo '<td>', FormatDate($sellerRow['firstseen']), '</td>';
                        echo '<td>', FormatDate($sellerRow['lastseen']), '</td>';
                        echo '<td>';
                        if ($row['cnt'] < 3) {
                            echo 'Low';
                        } elseif ($row['cnt'] < 8) {
                            echo 'Medium';
                        } else {
                            echo 'High';
                        }
                        echo '</td></tr>';
                    }
                    echo '</table>';
                }
            }
            echo '<br><br>';
        }
    }

    function FormatDate($dt) {
        $dt = strtotime($dt);
        if ($dt < strtotime('Jan 1 2016')) {
            return date('M j, Y', $dt);
        }
        return date('M j', $dt);
    }

?><!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Extra: The Undermine Journal</title>

    <link rel="stylesheet" href="https://theunderminejournal.com/css/wowhead/basic.css?1">
    <script type="text/javascript" src="https://theunderminejournal.com/js/wowhead/power.js?2"></script>
    <script type="text/javascript" src="https://theunderminejournal.com/js/wowhead/basic.js"></script>

    <style type="text/css">
        body{min-width:40em;font-size:14px;color:#333;margin:0;font-family:sans-serif}
        .main {padding: 2em}
        .story {line-height:1.6;max-width: 45em;font-size:16px;text-align:justify}
        h1,h2,h3{line-height:1.2;margin-top:0;margin-bottom:0.75em}
        h1{font-size: 150%}
    </style>
</head>
<body>
<div style="margin: 2em">
    <a href="/"><img src="../images/underminetitle.2000.trans.png" style="border: 0; background-image: url('../images/underminetitle.2000.png'); background-size: contain; background-repeat: no-repeat; max-width: 100%; max-height: 4em"></a>
    <div style="font-size: 2em; padding-left: 1em">Extra: Multirealm Corporation</div><commit_msg>tweaks after seeing live data<commit_after><?php

    require_once __DIR__ . '/../../incl/incl.php';
    require_once __DIR__ . '/../../incl/memcache.incl.php';

    function GetUSRealms() {
        $cacheKey = 'extra:multirealm:realms2';

        $realms = MCGet($cacheKey);
        if ($realms !== false) {
            return $realms;
        }

        $sql = 'select id, slug, name, house, canonical from tblRealm where region = \'US\' and locale is not null';
        $db = DBConnect();
        $stmt = $db->prepare($sql);
        $stmt->execute();
        $result = $stmt->get_result();
        $realmList = DBMapArray($result);
        $stmt->close();

        uasort($realmList, function($a,$b){
            return strcmp($a['name'], $b['name']);
        });

        $realms = [
            'id' => $realmList,
            'house' => [],
        ];

        foreach ($realmList as $id => $row) {
            if (!is_null($row['canonical'])) {
                $realms['house'][$row['house']] = $id;
            }
        }

        MCSet($cacheKey, $realms, 86400);

        return $realms;
    }

    function GetSellerList() {
        $cacheKey = 'extra:multirealm:sellers2';

        $sellers = MCGet($cacheKey);
        if ($sellers !== false) {
            return $sellers;
        }

        $sellers = [];

        $sql = <<<'EOF'
        select z2.cnt, s.name sellername, r.name realmname, s.firstseen, s.lastseen, r.house, r.slug
        from (
            select seller, count(distinct `snapshot`) cnt
            from (
                SELECT seller, item, `snapshot`
                FROM `tblSellerItemHistory` 
                where item in (128159, 127736, 127738, 127732, 127731, 127737, 127735, 127730, 127734, 127733, 127718, 128158, 127720, 127714, 127713, 127719, 127717, 127712, 127716, 127715)
            ) z1
            group by seller
            having count(distinct item) = 2
        ) z2
        left join tblSellerItemHistory h on h.seller = z2.seller and h.item not in (128159, 127736, 127738, 127732, 127731, 127737, 127735, 127730, 127734, 127733, 127718, 128158, 127720, 127714, 127713, 127719, 127717, 127712, 127716, 127715)
        join tblSeller s on s.id = z2.seller
        join tblRealm r on s.realm = r.id
        where h.seller is null
        and r.region = 'US'
        and s.firstseen > '2016-04-01'
        order by r.house, s.firstseen, z2.cnt desc, s.lastseen desc
EOF;

        $db = DBConnect();
        $stmt = $db->prepare($sql);
        $stmt->execute();
        $result = $stmt->get_result();
        while ($row = $result->fetch_assoc()) {
            $sellers[$row['house']][] = $row;
        }
        $result->close();
        $stmt->close();

        MCSet($cacheKey, $sellers);

        return $sellers;
    }

    function GenerateList() {
        $realms = GetUSRealms();
        $sellers = GetSellerList();

        foreach ($realms['id'] as $id => $row) {
            $canonicalId = isset($realms['house'][$row['house']]) ? $realms['house'][$row['house']] : $id;
            echo '<a name="', $row['slug'], '"></a><h3>', $row['name'], '</h3>';
            if ($canonicalId != $id) {
                echo '<i>See <a href="#', $realms['id'][$canonicalId]['slug'], '">', $realms['id'][$canonicalId]['name'], '</a></i><br>';
            } else {
                if (!isset($sellers[$row['house']])) {
                    echo 'None detected!';
                } else {
                    echo '<table><tr><th>Seller</th><th>First Seen</th><th>Last Seen</th><th>Confidence</th></tr>';
                    foreach ($sellers[$row['house']] as $sellerRow) {
                        echo '<tr><td><a href="/#us/', $sellerRow['slug'], '/seller/', mb_strtolower($sellerRow['sellername']), '">';
                        echo $sellerRow['sellername'], '-', $sellerRow['realmname'], '</a></td>';
                        echo '<td>', FormatDate($sellerRow['firstseen']), '</td>';
                        echo '<td>', FormatDate($sellerRow['lastseen']), '</td>';
                        echo '<td>';
                        if ($sellerRow['cnt'] < 3) {
                            echo 'Low';
                        } elseif ($sellerRow['cnt'] < 8) {
                            echo 'Medium';
                        } else {
                            echo 'High';
                        }
                        echo '</td></tr>';
                    }
                    echo '</table>';
                }
            }
            echo '<br>';
        }
    }

    function FormatDate($dt) {
        $dt = strtotime($dt);
        if ($dt < strtotime('Jan 1 2016')) {
            return date('M j, Y', $dt);
        }
        if ($dt > (time() - 86400)) {
            $hours = round((time() - $dt) / 3600);
            return "$hours hour".($hours == 1 ? '' : 's')." ago";
        }
        return date('M j', $dt);
    }

?><!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Extra: The Undermine Journal</title>

    <link rel="stylesheet" href="https://theunderminejournal.com/css/wowhead/basic.css?1">
    <script type="text/javascript" src="https://theunderminejournal.com/js/wowhead/power.js?2"></script>
    <script type="text/javascript" src="https://theunderminejournal.com/js/wowhead/basic.js"></script>

    <style type="text/css">
        body{min-width:40em;font-size:14px;color:#333;margin:0;font-family:sans-serif}
        .main {padding: 2em}
        .story {line-height:1.6;max-width: 45em;font-size:16px;text-align:justify}
        h1,h2,h3{line-height:1.2;margin-top:0;margin-bottom:0.75em}
        h1{font-size: 150%}
        table { width: 100%; border-spacing: 0 }
        table th {padding: 0.4em 0.2em 0; border-bottom: 1px solid #CCC; font-weight: normal}
        table td {padding: 0.2em 0.4em}
        tr:hover td {background-color: #EEE}
    </style>
</head>
<body>
<div style="margin: 2em">
    <a href="/"><img src="../images/underminetitle.2000.trans.png" style="border: 0; background-image: url('../images/underminetitle.2000.png'); background-size: contain; background-repeat: no-repeat; max-width: 100%; max-height: 4em"></a>
    <div style="font-size: 2em; padding-left: 1em">Extra: Multirealm Corporation</div>
{'fraction_non_alphanumeric': 0.17943848059454995, 'fraction_numerical': 0.05392237819983485, 'mean_word_length': 1.9640234948604993, 'pattern_counts': {'":': 0, '<': 127, '<?xml version=': 0, '>': 145, 'https://': 6, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3487427', 'n_tokens_mistral': 4399, 'n_tokens_neox': 3878, 'n_words': 806}