X-Git-Url: https://ruin.nu/git/?p=ndwebbie.git;a=blobdiff_plain;f=database%2Ffunctions%2Fhostile_alliances.sql;fp=database%2Ffunctions%2Fhostile_alliances.sql;h=370f4b2f1538bee72c1528f876978853aa57197d;hp=0c760c24939b94a93e3d6212071af56ee083796a;hb=3dc80cedd5deba53a4082992de317fbc3acf805c;hpb=23e133163ef06e0afc2000152df31c966f4929b2

diff --git a/database/functions/hostile_alliances.sql b/database/functions/hostile_alliances.sql
index 0c760c2..370f4b2 100644
--- a/database/functions/hostile_alliances.sql
+++ b/database/functions/hostile_alliances.sql
@@ -2,12 +2,22 @@ DROP FUNCTION IF EXISTS hostile_alliances(INT,INT);
 CREATE OR REPLACE FUNCTION hostile_alliances (first INT, last INT)
 RETURNS table(aid INT, alliance TEXT, hostile_count BIGINT, targeted BIGINT, targeted_raids BIGINT)
 AS $SQL$
-WITH hostile_alliances AS (
-	SELECT COALESCE(aid,-1) AS aid, count(*) AS hostile_count
+WITH hostile_fleets AS (
+	SELECT DISTINCT s.pid, COALESCE(s.aid,-1) AS aid, i.target, i.tick
+	FROM intel i
+		JOIN current_planet_stats s ON s.pid = i.sender
+		JOIN current_planet_stats t ON t.pid = i.target
+	WHERE t.alliance = 'NewDawn' AND i.mission = 'Attack' AND i.tick BETWEEN $1 AND $2
+	UNION DISTINCT
+	SELECT s.pid, COALESCE(s.aid,-1) AS aid, u.pid AS target, c.landing_tick
 	FROM calls c
 		JOIN incomings i USING (call)
-		JOIN current_planet_stats s USING (pid)
-	WHERE c.landing_tick BETWEEN $1 + i.eta AND $2 + i.eta
+		join users u USING (uid)
+		JOIN current_planet_stats s ON s.pid = i.pid
+	WHERE c.landing_tick BETWEEN $1 AND $2
+), hostile_alliances AS (
+	SELECT COALESCE(aid,-1) AS aid, count(*) AS hostile_count
+	FROM hostile_fleets
 	GROUP BY aid
 ), alliance_targets_1 AS (
 	SELECT COALESCE(aid,-1) AS aid, exists(
@@ -19,15 +29,16 @@ WITH hostile_alliances AS (
 		JOIN current_planet_stats p USING (pid)
 		JOIN fleets f USING (fid)
 	WHERE f.mission = 'Attack'
-		AND lc.landing_tick BETWEEN $1 + lc.eta AND $2 + lc.eta
+		AND lc.landing_tick BETWEEN $1 AND $2
 ), alliance_targets AS (
 	SELECT aid, count(*) AS targeted, count(NULLIF(raid_target,false)) AS targeted_raids
 	FROM alliance_targets_1
 	GROUP BY aid
 )
 SELECT aid, alliance, hostile_count, targeted, targeted_raids
-FROM hostile_alliances
+FROM alliances
 	LEFT JOIN alliance_targets USING (aid)
-	LEFT JOIN alliances USING (aid)
+	LEFT JOIN hostile_alliances USING (aid)
+WHERE hostile_count IS NOT NULL OR targeted IS NOT NULL
 $SQL$ LANGUAGE SQL STABLE;