database/functions/hostile_alliances.sql

   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)
   4 AS $SQL$
   5 WITH hostile_fleets AS (
   6         SELECT DISTINCT s.pid, COALESCE(s.aid,-1) AS aid, i.target, i.tick
   7         FROM intel i
   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
  11         UNION DISTINCT
  12         SELECT s.pid, COALESCE(s.aid,-1) AS aid, u.pid AS target, c.landing_tick
  13         FROM calls c
  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
  20         FROM hostile_fleets
  21         GROUP BY aid
  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
  27                 ) AS raid_target
  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
  36         GROUP BY aid
  37 )
  38 SELECT aid, alliance, hostile_count, targeted, targeted_raids
  39 FROM alliances
  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;
  44