From c843aa6e80c735c9fde563ecfae5e5233caa72b8 Mon Sep 17 00:00:00 2001
From: Michael Andreen <michael@andreen.dev>
Date: Thu, 12 Jan 2023 13:30:51 +0100
Subject: [PATCH] New mail report optimized for Discord

---
 database/r100.sql   |  1 +
 scripts/ndreport.pl | 87 ++++++++++++++++++++++++++++++++++++++++-----
 2 files changed, 79 insertions(+), 9 deletions(-)

diff --git a/database/r100.sql b/database/r100.sql
index debbc44..feebfdf 100644
--- a/database/r100.sql
+++ b/database/r100.sql
@@ -36,5 +36,6 @@ ALTER TABLE ship_stats ALTER COLUMN cloaked DROP DEFAULT, ALTER COLUMN baseeta D
 DROP TYPE race;
 DROP TYPE govs;
 
+ALTER TABLE incomings DROP CONSTRAINT incomings_call_key, ADD CONSTRAINT incomings_unique_key UNIQUE (call, pid, fleet, eta);
 
 \i functions/hostile_alliances.sql
diff --git a/scripts/ndreport.pl b/scripts/ndreport.pl
index 8f2cd3b..b93eebc 100755
--- a/scripts/ndreport.pl
+++ b/scripts/ndreport.pl
@@ -26,6 +26,7 @@ use local::lib;
 use DBI;
 use DBD::Pg qw(:pg_types);
 use CGI qw/:standard/;
+use Data::Dumper;
 
 use Email::Simple;
 use Encode;
@@ -48,29 +49,97 @@ my $email = Email::Simple->new($text);
 
 my $body =  decode('ISO-8859-1', $email->body);
 
+unless ($body =~ /Report of events in tick (\d+)/) {
+	exit 0;
+}
+
+my $tick = $1;
+
 my $c = $dbh->prepare(q{
-SELECT coords(x,y,z) FROM current_planet_stats WHERE pid = (SELECT pid FROM users WHERE username = $1
-	AND uid IN (SELECT uid FROM groupmembers WHERE gid = 'M'))
+SELECT coords(x,y,z), uid, pid
+FROM users u
+	LEFT JOIN current_planet_stats p USING (pid)
+WHERE username = $1
+	AND uid IN (SELECT uid FROM groupmembers WHERE gid = 'M')
 });
+my ($coords, $uid, $pid) = $dbh->selectrow_array($c, undef, $user);
 
-my $a = $dbh->prepare(q{
-SELECT race, $1 - tick() FROM current_planet_stats WHERE x = $2 AND y = $3 AND z = $4
+$coords //= '(no coords entered)';
+
+my $attacker = $dbh->prepare(q{
+SELECT pid, race FROM current_planet_stats WHERE x = $1 AND y = $2 AND z = $3
 });
 
 my $report = $dbh->prepare(q{INSERT INTO irc_requests (channel,uid,message) VALUES('def',$1,$2)});
 
+
+my $message = "Tick **$tick**: *$user* $coords has incs\n";
+
+my $add_inc = $dbh->prepare(q{INSERT INTO incomings (call,pid,eta,amount,fleet) VALUES($1,$2,$3,$4,$5)});
+
+my $find_call = $dbh->prepare(q{
+SELECT call, count(*) AS count
+FROM calls
+	JOIN incomings i USING (call)
+WHERE uid = $1 AND landing_tick = $2
+GROUP BY call
+});
+my $add_call = $dbh->prepare(q{
+INSERT INTO calls
+	(uid,landing_tick,info)
+	VALUES($1,$2,'')
+RETURNING call
+});
+
+my $fleetcatch = $dbh->prepare(q{
+SELECT count(*) FROM launch_confirmations WHERE uid = $1 AND back = $2
+});
+
+
+my %incs;
 while($body =~ /jumpgate from (.+?), located at (\d+):(\d+):(\d+).+?our system in tick (\d+) and appears to have (\d+)/sg){
-	my ($fleet, $x,$y,$z,$tick, $amount) = ($1,$2,$3,$4,$5,$6);
+	my ($fleet, $x,$y,$z,$landing_tick, $amount) = ($1,$2,$3,$4,$5,$6);
 
-	my ($coords) = $dbh->selectrow_array($c, undef, $user);
+	my $eta = $landing_tick - $tick;
 
-	$coords //= '(no coords entered)';
+	my ($pid, $race) = $dbh->selectrow_array($attacker,undef, $x,$y,$z);
 
-	my ($race,$eta) = $dbh->selectrow_array($a,undef, $tick,$x,$y,$z);
+	push @{$incs{$landing_tick}}, {x => $x, y => $y, z => $z, eta => $eta, amount => $amount, race => $race, fleet => $fleet, pid => $pid};
+}
 
-	$report->execute(-5,"$user has incs: $coords $x:$y:$z $fleet $race $amount $eta");
+for my $landing_tick (sort keys %incs) {
+	$message .= "\n> Landing tick: *$landing_tick*";
+	my $call;
+	if ($uid) {
+		my ($returning) = $dbh->selectrow_array($fleetcatch, undef, $uid, $landing_tick);
+		if ($returning > 0) {
+			$message .= "\n**THIS IS A POSSIBLE FLEETCATCH!**"
+		}
+		if (my $c = $dbh->selectrow_hashref($find_call, undef, $uid, $landing_tick)) {
+			$call = $c->{call};
+			$message .= "\n__Previously an addtional **$c->{count}** fleets landing on this tick__"
+		}
+		else
+		{
+			($call) = $dbh->selectrow_array($add_call, undef, $uid, $landing_tick);
+		}
+	}
+	$message .= "\n```";
+	for my $i (@{$incs{$landing_tick}}) {
+		if ($uid)
+		{
+			$add_inc->execute($call, $i->{pid}, $i->{eta}, $i->{amount}, $i->{fleet});
+		}
+		$message .= "\n$i->{x}:$i->{y}:$i->{z} $i->{race} $i->{eta} $i->{amount} $i->{fleet} "
+	}
+	$message .= "```";
 }
 
+
+#print $message;
+
+$report->execute(-5,$message);
+
 $dbh->disconnect;
 
 system 'killall','-USR1', 'ndbot.pl';
-- 
2.39.2