1 DROP FUNCTION IF EXISTS hostile_alliances(INT,INT);
2 CREATE OR REPLACE FUNCTION hostile_alliances (first INT, last INT)
3 RETURNS table(aid INT, alliance TEXT, hostile_count BIGINT, targeted BIGINT, targeted_raids BIGINT)
5 WITH hostile_alliances AS (
6 SELECT COALESCE(aid,-1) AS aid, count(*) AS hostile_count
8 JOIN incomings i USING (call)
9 JOIN current_planet_stats s USING (pid)
10 WHERE c.landing_tick BETWEEN $1 + i.eta AND $2 + i.eta
12 ), alliance_targets_1 AS (
13 SELECT COALESCE(aid,-1) AS aid, exists(
14 SELECT pid FROM raid_targets rt
15 JOIN raids r ON rt.raid = r.id
16 WHERE rt.pid = p.pid AND lc.landing_tick BETWEEN r.tick AND r.tick + r.waves - 1
18 FROM launch_confirmations lc
19 JOIN current_planet_stats p USING (pid)
20 JOIN fleets f USING (fid)
21 WHERE f.mission = 'Attack'
22 AND lc.landing_tick BETWEEN $1 + lc.eta AND $2 + lc.eta
23 ), alliance_targets AS (
24 SELECT aid, count(*) AS targeted, count(NULLIF(raid_target,false)) AS targeted_raids
25 FROM alliance_targets_1
28 SELECT aid, alliance, hostile_count, targeted, targeted_raids
29 FROM hostile_alliances
30 LEFT JOIN alliance_targets USING (aid)
31 LEFT JOIN alliances USING (aid)
32 $SQL$ LANGUAGE SQL STABLE;