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_fleets AS (
6 SELECT DISTINCT s.pid, COALESCE(s.aid,-1) AS aid, i.target, i.tick
8 JOIN current_planet_stats s ON s.pid = i.sender
9 JOIN current_planet_stats t ON t.pid = i.target
10 WHERE t.alliance = 'NewDawn' AND i.mission = 'Attack' AND i.tick BETWEEN $1 AND $2
12 SELECT s.pid, COALESCE(s.aid,-1) AS aid, u.pid AS target, c.landing_tick
14 JOIN incomings i USING (call)
15 join users u USING (uid)
16 JOIN current_planet_stats s ON s.pid = i.pid
17 WHERE c.landing_tick BETWEEN $1 AND $2
18 ), hostile_alliances AS (
19 SELECT COALESCE(aid,-1) AS aid, count(*) AS hostile_count
22 ), alliance_targets_1 AS (
23 SELECT COALESCE(aid,-1) AS aid, exists(
24 SELECT pid FROM raid_targets rt
25 JOIN raids r ON rt.raid = r.id
26 WHERE rt.pid = p.pid AND lc.landing_tick BETWEEN r.tick AND r.tick + r.waves - 1
28 FROM launch_confirmations lc
29 JOIN current_planet_stats p USING (pid)
30 JOIN fleets f USING (fid)
31 WHERE f.mission = 'Attack'
32 AND lc.landing_tick BETWEEN $1 AND $2
33 ), alliance_targets AS (
34 SELECT aid, count(*) AS targeted, count(NULLIF(raid_target,false)) AS targeted_raids
35 FROM alliance_targets_1
38 SELECT aid, alliance, hostile_count, targeted, targeted_raids
40 LEFT JOIN alliance_targets USING (aid)
41 LEFT JOIN hostile_alliances USING (aid)
42 WHERE hostile_count IS NOT NULL OR targeted IS NOT NULL
43 $SQL$ LANGUAGE SQL STABLE;