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(
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;