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

namespace Gazelle;

use \sad_spirit\pg_wrapper\converters\containers\MultiRangeConverter;
use \sad_spirit\pg_wrapper\converters\datetime\TimeStampTzConverter;

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

// length of interval between two records to extend the date range
define('INTERVAL_DAYS', 8);

$db = DB::DB();
$pg = new DB\Pg(PG_RW_DSN);

$db->prepared_query("
    SELECT ID FROM users_main ORDER BY 1
");
$list = $db->collect(0);

foreach ($list as $userId) {
    $db->prepared_query('
        SELECT IP                        AS "ip",
            StartTime                    AS "begin",
            coalesce(EndTime, StartTime) AS "end",
            coalesce(
                timestampdiff(
                    day,
                    lag(EndTime) OVER (PARTITION BY IP ORDER BY EndTime),
                    StartTime
                ), 0
            ) AS day_gap_prev
        FROM users_history_ips
        WHERE UserID = ?
        ORDER BY inet_aton(IP), StartTime;
        ', $userId
    );

    $total = 0;
    $prev  = ''; // keep track of when the IP address changes
    $range = []; // date ranges of this IP
    foreach ($db->to_array(false, MYSQLI_ASSOC) as $row) {
        if ($prev and $prev != $row['ip']) {
            persist($pg, $userId, $prev, $total, $range);
            $range = [];
            $total = 0;
        }
        $gap = $row['day_gap_prev'];
        if ($gap <= INTERVAL_DAYS && count($range)) {
            // close by, extend previous range
            $range[count($range) - 1]['end'] = $row['end'];
        } else {
            // too far away or a new ip, create a new range
            $range[] = [
                'begin' => $row['begin'],
                'end'   => $row['end'],
            ];
        }
        $prev = $row['ip'];
        $total++;
    }

    if ($prev) {
        persist($pg, $userId, $prev, $total, $range);
    }
}

function persist(DB\Pg $pg, int $userId, string $ip, int $total, array $range): void {
    $pgRange = array_map(fn ($dates) => [
            'lower' => new \DateTimeImmutable($dates['begin']),
            'upper' => new \DateTimeImmutable($dates['end']),
            'lower_inc' => true,
            'upper_inc' => true,
        ],
        $range
    );
    $converter = new MultiRangeConverter(new TimeStampTzConverter());
    $seen = $converter->output($pgRange);
    unset($range, $pgRange);

    $pg->begin();
    $exists = $pg->scalarex("
        select 1
        from ip_site_history
        where id_user = $1
            and ip = $2
        ", $userId, $ip
    );
    if ($exists) {
        echo "$userId $ip append\n";
        $pg->query("
            update ip_site_history set
                seen = seen + $1::tstzmultirange,
                total = total + $2
            where id_user = $3
            and ip = $4
            ", $seen, $total, $userId, $ip
        );
    } else {
        echo "$userId $ip create\n";
        $pg->query("
            insert into ip_site_history
                   (id_user, ip, total, seen)
            values ($1,      $2, $3,    $4::tstzmultirange)
            ", $userId, $ip, $total, $seen
        );
    }
    $pg->commit();
}
