lwb6/legacyworlds-server-data/db-structure/parts/040-functions/167-planet-list.sql

-- LegacyWorlds Beta 6
-- PostgreSQL database scripts
--
-- Views for empires' planet lists
--
-- Copyright(C) 2004-2012, DeepClone Development
-- --------------------------------------------------------


--
-- Basic planet information
--

CREATE VIEW emp.planets_list_basic
	AS SELECT e.name_id AS empire ,
				p.name_id AS id , n.name ,
				s.x , s.y , p.orbit ,
				p.population , ph.current / p.population::REAL AS happiness ,
				floor( pm.income )::BIGINT AS income ,
				floor( pm.upkeep )::BIGINT AS upkeep
			FROM emp.empires e
				INNER JOIN emp.planets ep ON ep.empire_id = e.name_id
				INNER JOIN verse.planets p ON p.name_id = ep.planet_id
				INNER JOIN naming.map_names n ON n.id = p.name_id
				INNER JOIN verse.planet_happiness ph ON ph.planet_id = p.name_id
				INNER JOIN verse.planet_money pm ON pm.planet_id = p.name_id
				INNER JOIN verse.systems s ON s.id = p.system_id;


--
-- Production
--

CREATE VIEW emp.planets_list_prod
	AS SELECT id ,
				verse.adjust_production( verse.get_raw_production( id , 'WORK') , happiness ) AS military_production ,
				verse.adjust_production( verse.get_raw_production( id , 'CASH') , happiness ) AS industrial_production ,
				verse.adjust_production( verse.get_raw_production( id , 'POP') , happiness ) AS growth_production ,
				verse.adjust_production( verse.get_raw_production( id , 'DEF') , happiness ) AS static_defence
			FROM emp.planets_list_basic;


--
-- Civilian investment
--

CREATE VIEW emp.planets_list_civ_invest
	AS SELECT q.planet_id AS id , sum( CASE WHEN qi.destroy THEN 0 ELSE qi.amount * qb.cost END ) - q.money AS civ_investment
		FROM verse.bld_queues q
			INNER JOIN verse.bld_items qi ON qi.queue_id = q.planet_id
			INNER JOIN tech.buildables qb ON qb.name_id = qi.building_id
		GROUP BY q.planet_id , q.money;


--
-- Military investment
--

CREATE VIEW emp.planets_list_mil_invest
	AS SELECT q.planet_id AS id , sum( qi.amount * qb.cost ) - q.money AS mil_investment
		FROM verse.mil_queues q
			INNER JOIN verse.mil_items qi ON qi.queue_id = q.planet_id
			INNER JOIN tech.buildables qb ON qb.name_id = qi.ship_id
		GROUP BY q.planet_id , q.money;


--
-- First item on civilian queues
--

CREATE VIEW emp.planets_list_civ_top
	AS SELECT qi.queue_id AS id , qi.amount AS civ_amount , qi.destroy AS civ_destroy , t.translated_string AS civ_name
		FROM verse.bld_items qi
			INNER JOIN emp.planets ep ON ep.planet_id = qi.queue_id
			INNER JOIN naming.empire_names en ON en.id = ep.empire_id
			INNER JOIN users.credentials cred ON cred.address_id = en.owner_id
			INNER JOIN defs.translations t ON t.lang_id = cred.language_id AND t.string_id = qi.building_id
		WHERE qi.queue_order = 0;


--
-- First item on military queues
--

CREATE VIEW emp.planets_list_mil_top
	AS SELECT qi.queue_id AS id , qi.amount AS mil_amount , t.translated_string AS mil_name
		FROM verse.mil_items qi
			INNER JOIN emp.planets ep ON ep.planet_id = qi.queue_id
			INNER JOIN naming.empire_names en ON en.id = ep.empire_id
			INNER JOIN users.credentials cred ON cred.address_id = en.owner_id
			INNER JOIN defs.translations t ON t.lang_id = cred.language_id AND t.string_id = qi.ship_id
		WHERE qi.queue_order = 0;


--
-- Fleets
--

CREATE VIEW emp.planets_list_fleets
	AS SELECT f.location_id AS id , ( CASE
				WHEN f.owner_id = ep.empire_id THEN 0
				WHEN f.attacking THEN 2
				ELSE 1
			END ) AS rel_type , sum( fs.power )::BIGINT AS power
		FROM fleets.fleets f
			INNER JOIN emp.planets ep ON f.location_id = ep.planet_id
			INNER JOIN fleets.stats_view fs ON fs.id = f.id
			LEFT OUTER JOIN fleets.movements fm ON fm.fleet_id = f.id
		WHERE fm IS NULL
		GROUP BY f.location_id , ( CASE
				WHEN f.owner_id = ep.empire_id THEN 0
				WHEN f.attacking THEN 2
				ELSE 1
			END );


/*
 * Planet list resources information
 * ----------------------------------
 * 
 * This view is used to display the resources-related information in the
 * planet list pages. All rows in the view are ordered using the usual
 * resource ordering view.
 * 
 * FIXME: time-related constants are hardcoded.
 * FIXME: civilian and military investments are set to 0.
 * 
 * Columns:
 *		empire_id		The empire's identifier
 *		planet_id		The planet's identifier
 *		pres_income		The income for this type of resources on a period
 *							of 12h RT / one month GT.
 *		pres_upkeep		The upkeep for this type of resources on a period
 *							of 12h RT / one month GT.
 *		civ_investment	The current amount invested in the civillian build
 *							queue (FIXME: forced to 0)
 *		mil_investment	The current amount invested in the military build
 *							queue (FIXME: forced to 0)
 */
DROP VIEW IF EXISTS emp.plist_resources_view;
CREATE VIEW emp.plist_resources_view
	AS SELECT _emp_planet.empire_id , _emp_planet.planet_id ,
				_name.translated_string AS resource_name ,
				FLOOR( _pres.pres_income * 720.0 )::BIGINT AS pres_income ,
				CEIL( _pres.pres_upkeep * 720.0 )::BIGINT AS pres_upkeep ,
				0::BIGINT AS civ_investment ,
				0::BIGINT AS mil_investment
			FROM emp.planets _emp_planet
				INNER JOIN verse.planet_resources _pres
					USING ( planet_id )
				INNER JOIN naming.empire_names _emp_name
					ON _emp_name.id = _emp_planet.empire_id
				INNER JOIN users.credentials _creds
					ON _creds.address_id = _emp_name.owner_id
				INNER JOIN defs.translations _name
					ON _name.string_id = resource_name_id
						AND _name.lang_id = _creds.language_id
				INNER JOIN defs.ordered_resources_view _res_def
					USING ( resource_name_id )
			WHERE _pres.pres_income > 0
				OR _pres.pres_upkeep > 0
			ORDER BY _res_def.resource_ordering;

GRANT SELECT
	ON emp.plist_resources_view
	TO :dbuser;


--
-- Actual planet list
--

CREATE VIEW emp.planets_list
	AS SELECT e.empire , e.id , e.name , e.x , e.y , e.orbit ,
				floor( e.population )::BIGINT AS population ,
				floor( 100 * e.happiness )::INT AS happiness ,
				e.income , e.upkeep ,
				floor( p.military_production )::BIGINT AS military_production , 
				floor( p.industrial_production )::BIGINT AS industrial_production , 
				floor( p.growth_production )::BIGINT AS growth_production , 
				( CASE WHEN ci IS NULL THEN 0 ELSE floor( ci.civ_investment ) END )::BIGINT AS civ_investment ,
				ct.civ_amount , ct.civ_destroy , ct.civ_name ,
				( CASE WHEN mi IS NULL THEN 0 ELSE floor( mi.mil_investment ) END )::BIGINT AS mil_investment ,
				mt.mil_amount , mt.mil_name ,
				floor( p.static_defence )::BIGINT AS static_defence , 
				( CASE WHEN of IS NULL THEN 0 ELSE of.power END ) AS own_fleet ,
				( CASE WHEN ff IS NULL THEN 0 ELSE ff.power END ) AS friendly_fleet ,
				( CASE WHEN hf IS NULL THEN 0 ELSE hf.power END ) AS hostile_fleet ,
				b.id AS battle
			FROM emp.planets_list_basic e
				INNER JOIN emp.planets_list_prod p USING ( id )
				LEFT OUTER JOIN emp.planets_list_civ_invest ci USING ( id )
				LEFT OUTER JOIN emp.planets_list_civ_top ct USING ( id )
				LEFT OUTER JOIN emp.planets_list_mil_invest mi USING ( id )
				LEFT OUTER JOIN emp.planets_list_mil_top mt USING ( id )
				LEFT OUTER JOIN emp.planets_list_fleets of ON of.id = e.id AND of.rel_type = 0
				LEFT OUTER JOIN emp.planets_list_fleets ff ON ff.id = e.id AND ff.rel_type = 1
				LEFT OUTER JOIN emp.planets_list_fleets hf ON hf.id = e.id AND hf.rel_type = 2
				LEFT OUTER JOIN battles.battles b ON b.location_id = e.id AND b.last_tick IS NULL
			ORDER BY e.x , e.y , e.orbit;

GRANT SELECT ON emp.planets_list TO :dbuser;