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_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 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(
			SELECT pid FROM raid_targets rt
			JOIN raids r ON rt.raid = r.id
			WHERE rt.pid = p.pid AND lc.landing_tick BETWEEN r.tick AND r.tick + r.waves - 1
		) AS raid_target
	FROM launch_confirmations lc
		JOIN current_planet_stats p USING (pid)
		JOIN fleets f USING (fid)
	WHERE f.mission = 'Attack'
		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 alliances
	LEFT JOIN alliance_targets USING (aid)
	LEFT JOIN hostile_alliances USING (aid)
WHERE hostile_count IS NOT NULL OR targeted IS NOT NULL
$SQL$ LANGUAGE SQL STABLE;

