database/available_ships.sql

   1 DROP VIEW available_ships;
   2 CREATE VIEW available_ships AS
   3 SELECT uid,username, planet, ship
   4         , COALESCE(f.amount - o.amount,f.amount) AS amount
   5         , COALESCE(fleets,3) AS fleets
   6 FROM users u JOIN (
   7         SELECT planet,ship,amount
   8         FROM (
   9                 SELECT DISTINCT ON (planet,mission) planet,mission, fid
  10                 FROM fleets f
  11                 WHERE tick <= tick()
  12                         AND name IN ('Main','Advanced Unit')
  13                         AND mission = 'Full fleet'
  14                 ORDER BY planet,mission,tick DESC, fid DESC
  15         ) f
  16                 JOIN fleet_ships fs USING (fid)
  17
  18 ) f USING (planet) LEFT OUTER JOIN (
  19         SELECT planet, ship, SUM(fs.amount) AS amount, 3 - COUNT(DISTINCT fid) AS fleets
  20         FROM fleets f
  21                 JOIN launch_confirmations USING (fid)
  22                 JOIN fleet_ships fs USING (fid)
  23         WHERE back > tick()
  24                 AND landing_tick - eta - 12 < tick()
  25         GROUP BY planet,ship
  26 ) o USING (planet,ship)
  27 WHERE COALESCE(f.amount - o.amount,f.amount) > 0
  28